从SQL返回的SQLDataAdapter更改值类型 [英] SQLDataAdapter changing value type returned from SQL

问题描述

在我的C#项目中，我正在对SQL 2014数据库执行查询以检索Employee数据，包括作为Decimal存储在数据库中的PeriodEnd(即，存储了2016年11月17日作为20161117).我要查询的数据库不是我们的产品，因此无法将类型更改为DateTime字段.

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.

这是正在执行的SQL脚本:

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

这是SqlDataAdapter通话:

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);
                }
            }
        }
    }
}

使用SQL Server Profiler，我可以看到传递给SQL的查询是:

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'

如果我直接在SQL中运行这些，则结果如下:

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

但是，由_sqlDataAdapter创建的DataTable的结果是:

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

是否有一种方法可以强制SqlDataAdapter使用结果中的数据类型?还是SQL确实确实返回了DateTime对象而不是Decimal(在SQL中将PeriodEnd列定义为decimal(9,0))?如果是这样，是否有原因和/或预防的方法?

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?

推荐答案

谢谢大家的帮助.建议先查看_cmd对象，然后将我引向解决方案.应该有一段代码可以更改数据库以使用数据库ABC.这段代码无法正常工作，因此数据库连接保持在数据库XYZ上.之所以难以解决，是因为数据库XYZ具有已定义的视图，这些视图从数据库ABC返回数据，但是PeriodEnd列为DateTime而不是decimal.我不知道这些视图，当我看到与期望值匹配的数据时，我什至没有想到要查找并确保正确分配了数据库.

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.

这篇关于从SQL返回的SQLDataAdapter更改值类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！