调用存储过程产生不正确的空数据表 [英] Calling a stored procedure results in an incorrectly empty DataTable

问题描述

我有一个正常运作的存储过程时，手动执行它返回正确的数据。有几排中的输出数据。但是，下面的code我总是导致无数据行被添加到数据表。

I have a functioning stored procedure which returns the correct data when executed manually. There are several rows of data in the output. However, the following code I have is always resulting in no rows of data being added to the DataTable.

        var commandString = string.Format(@"EXEC MyStoredProcedure {0}", SomeParameter);

        var dataTable = new DataTable();
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();

            using (var adapter = new SqlDataAdapter(commandString, ConnectionString))
            {
                using (new SqlCommandBuilder(adapter))
                {
                    adapter.Fill(dataTable);
                    adapter.Update(dataTable);
                }
            }
        }

        var result = (from DataRow row in dataTable.Rows
            select new MyModelClass
            {
                SomeString = (string) row["SomeString"],
                SomeValue = (string) row["SomeValue"],

            }).ToList();

        Debug.WriteLine("Results: " + result.Count);

我不知道为什么code是导致没有行的数据。我在哪里的问题呢？我怀疑这是因为我有一个不正确的认识，如何数据表的作品。我应该如何解决code？

I am not sure why the code is resulting in no rows of data. Where am I going wrong? I suspect it is because I have an incorrect understanding of how DataTable works. How should I fix the code?

推荐答案

基本上，你的code应该是这个样子：

Basically, your code should look something like this:

string ConnectionString = "<Your connection string here>";
string procedureName = "<your stored procedure name here>";
string ParamName = "@<Parameter name>"; // NOTE: the '@' is INSIDE the string!
DataSet ds = new DataSet();

using (var connection = new SqlConnection(ConnectionString))
{
    var cmd = new SqlCommand(procedureName, connection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(ParamName, SqlDbType.Int).Value = 5;

    using (var adapter = new SqlDataAdapter(cmd))
    { 
        adapter.Fill(ds);
    }
}

这篇关于调用存储过程产生不正确的空数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！