SQLDataAdapter changing value type returned from SQL

Question

In my C# project, I am executing a query against a SQL 2014 database to retrieve Employee data, including a PeriodEnd which is stored in the database as a Decimal (i.e., Nov 17, 2016 is stored as 20161117). The database I am querying is not our product so I cannot change the type to a DateTime field.

Here is the SQL script being executed:

SELECT DISTINCT
    e.EMPLOYEE as EmpNo,
    ch.PEREND As PeriodEnd,
    ch.PRPOSTSTAT
FROM UPEMPL e
    INNER JOIN UPCHKH ch 
        ON e.EMPLOYEE = ch.EMPLOYEE
WHERE 
    ch.PEREND = @PERIODEND

Here is the SqlDataAdapter call:

ExecuteSqlCommandScript(String sqlScript, List<SqlParams> sqlParams)
{
    . . . (setup SqlConnection info)

    using (SqlConnection _conn = new SqlConnection(connectionString))
    {
        using (SqlCommand _cmd = new SqlCommand())
        {
            _cmd.CommandText = sqlScript;
            _cmd.Connection = _conn;
            _cmd.Connection.Open();

            // add SqlParameters to SQL command
            if (sqlParams != null)
            {
                _cmd.Parameters.AddRange(sqlParams.ToArray());
            }

            using (SqlDataAdapter _sqlDataAdapter = new SqlDataAdapter(_cmd))
            {
               try
               {
                    // Save Table info to Results object
                    DataSet _dataSet = new DataSet();
                    _sqlDataAdapter.Fill(_dataSet); 

                    SqlResult _result = new SqlResult();
                    _result.DataSet = _dataSet;
                    _result.TableCount = _dataSet.Tables.Count;

                    this.Results.Add(_result);
                }
            }
        }
    }
}

Using SQL Server Profiler I can see the query passed to SQL is:

exec sp_executesql N'
SELECT DISTINCT
    e.EMPLOYEE as EmpNo,
    ch.PEREND As PeriodEnd,
    ch.PRPOSTSTAT
FROM UPEMPL e
    INNER JOIN UPCHKH ch 
        ON e.EMPLOYEE = ch.EMPLOYEE
WHERE 
    ch.PEREND = @PERIODEND 
',N'@PERIODEND nvarchar(8)',@PERIODEND=N'20161101'

If I run this directly in SQL these are the reults:

However, the results of the DataTable created by the _sqlDataAdapter is:

Is there a way to force SqlDataAdapter to use the data type as it is in the results? Or is it possible that SQL is indeed returning a DateTime object instead of a Decimal (the PeriodEnd column is defined in SQL as decimal(9,0))? If so is there a reason for this and/or a way to prevent it?

Answer 1

Try and add the column type explicitly when you are creating the dataset , by adding datacolumn of type decimal to the datatable. My guess is that since you are not explicitly specifying the column type, .net is deriving it from the value.

Answer 2

Thank you all for your help in this. It was the suggestion to look at the _cmd object that lead me to the solution. There was a piece of code that was supposed to change the database to use the database ABC. This piece of code was not working properly and so the database connection stayed on database XYZ. What made this so difficult to resolve is that database XYZ has views defined that return data from databse ABC, but with the PeriodEnd column as a DateTime and not a decimal. I did not know about these views and when I saw data that matched what I expected, I didn't even think to look and make sure the database was correctly assigned.