从c#执行SQL服务器存储过程

I have problems executing my stored procedure in C# console application and I don't know what the problem is. Could you please take a look?

在c#控制台应用程序中执行存储过程有问题，我不知道问题出在哪里。你能看一下吗?

string path="";

StringBuilder sb = new StringBuilder();
StringBuilder sqlErrorMessages = new StringBuilder("Sql Exception:\n");

try
{ 
    SqlConnection conn = new SqlConnection("Data Source=DESKTOP-M3IMRLE\\SQLEXPRESS; Initial Catalog = db2; Integrated security=true");

    Console.WriteLine("Enter path : ");
    path = Console.ReadLine();
    conn.Open();

    SqlCommand cmd = new SqlCommand();

    SqlCommand command = new SqlCommand("EXECUTE main.mainproc @path='" + path + "'", conn);

    if(command!=null)
    { 
        Console.WriteLine("JSON loaded");
    }  

    conn.Close();
}
catch(SqlException ex)
{
    sqlErrorMessages.AppendFormat("Message: {0}\n", ex.Message);
}

2 个解决方案

#1

You could execute a stored procedure giving its name to the SqlCommand constructor and flagging the CommandType as a stored procedure.
Parameters are loaded in the Parameters collection of the command:

您可以执行一个将名称赋予SqlCommand构造函数的存储过程，并将CommandType标记为存储过程。在命令的参数集合中加载参数:

SqlCommand cmd = new SqlCommand("mainproc", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@path", SqlDbType.NVarChar).Value = path;
cmd.ExecuteNonQuery();

The final call to ExecuteNonQuery runs your stored procedure, but it is intended for procedures that runs an INSERT/UPDATE or DELETE commands, or in other words, commands that don't return data.

对ExecuteNonQuery的最后一次调用运行存储过程，但是它用于运行插入/更新或删除命令的过程，或者换句话说，不返回数据的命令。

If your stored procedure is expected to return one or more records then you need more code:

如果存储过程需要返回一个或多个记录，则需要更多代码:

....
SqlDataReader reader = cmd.ExecuteReader();

while(reader.Read())
{
   // Get data from the first field of the current record assuming it is a string
   string data = reader[0].ToString();
}

The ExecuteReader method starts retrieving your data with the Read call and then continue until there are records to read. Inside the loop you can retrieve the fields data indexing the reader instance (and converting the object value to the appropriate type)

ExecuteReader方法开始使用Read调用检索数据，然后继续，直到有要读取的记录。在循环内部，您可以检索字段数据索引阅读器实例(并将对象值转换为适当的类型)

#2

You haven't called ExecuteNonQuery on your SqlCommand

您还没有调用SqlCommand上的ExecuteNonQuery

command.ExecuteNonQuery()

#1