C#调用存储过程

Call stored procedure in C#

提问by JohnMalcom

I want to call the stored procedure using C#.

我想使用 C# 调用存储过程。

I would like to create a shorthand for the procedure call.

我想为过程调用创建一个简写。

I still do not want to re-define the connection and open it. How do I create a method - I still did not open connection to the database?

我仍然不想重新定义连接并打开它。如何创建方法 - 我仍然没有打开与数据库的连接？

I use the following code:

我使用以下代码：

SqlConnection conn = null;
SqlDataReader rdr  = null;

conn = new SqlConnection("");
conn.Open();

SqlCommand cmd  = new SqlCommand("Procedure", conn);
cmd.CommandType = CommandType.StoredProcedure;

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
}

采纳答案by mafue

FlyingStreudel's answer is good, but I've adapted that code to make this version that demonstrates best practices (links at the bottom.) You can also use Microsoft's Enterprise Library which will give you robust Data Access classes.

FlyingStreudel 的回答很好，但我已经修改了该代码来制作这个演示最佳实践的版本（底部的链接）。您还可以使用 Microsoft 的企业库，该库将为您提供强大的数据访问类。

private string _connectionString = "yourconnectionstring"; // from web.config, or wherever you store it
public static SqlDataReader executeProcedure(string commandName, 
                                         Dictionary<string, object> params)
{
    SqlConnection conn = new SqlConnection(_connectionString);
    conn.Open();
    SqlCommand comm = conn.CreateCommand();
    comm.CommandType = CommandType.StoredProcedure;
    comm.CommandText = commandName;
    if (params != null)
    {
        foreach(KeyValuePair<string, object> kvp in params)
            comm.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
    }
    return comm.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}

used, like so:

使用，像这样：

Dictionary<string, object> paras = new Dictionary<string, object>();
paras.Add("user_name", "Timmy");
paras.Add("date", DateTime.Now);
using(SqlDataReader results = executeProcedure("sp_add_user", paras))
{
    while (results.Read())
    {
        //do something with the rows returned
    }
}

References:

参考：

How Microsoft use Connections in Enterprise Library

Microsoft 如何使用企业库中的连接

Keeping an SqlConnection open is 'foo bar'

保持 SqlConnection 打开是 'foo bar'

Returning a data reader from a class

从类返回数据读取器

回答by Kai

using (SqlConnection sqlConnection1 = new SqlConnection("Your Connection String")) {
using (SqlCommand cmd = new SqlCommand()) {
  Int32 rowsAffected;

  cmd.CommandText = "StoredProcedureName";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Connection = sqlConnection1;

  sqlConnection1.Open();

  rowsAffected = cmd.ExecuteNonQuery();

}}

回答by David W

If you're looking to reuse this kind of code, one possible solution is to wrap this kind of a method (one that executes a stored procedure and returns results) into a generic data access layer of your application. The variations you'd have to consider are for procedures not returning results, or expecting parameters.

如果您希望重用此类代码，一种可能的解决方案是将此类方法（执行存储过程并返回结果的方法）包装到应用程序的通用数据访问层中。您必须考虑的变化是不返回结果或期望参数的过程。

You could, for example, wrap this shell code as an ExecuteProcedure() that expects a connection string back to the database.

例如，您可以将此 shell 代码包装为一个 ExecuteProcedure()，它需要一个连接字符串返回到数据库。

There are myriad other ways to accomplish this kind of task, so you need to determine what would be the best option suited to your particular requirements.

有无数其他方法可以完成此类任务，因此您需要确定最适合您的特定要求的选项。

回答by scottm

You can wrap this code and take the procedure as a parameter. Something like this:

您可以包装此代码并将过程作为参数。像这样的东西：

public SqlCommand GetData(string procedure)
{
  var conn = new SqlConnection(connectionString);
  var cmd = new SqlCommand(procedure, conn);

  cmd.CommandType = CommandType.StoredProcedure;
  conn.Open();
  return cmd;
}

The only problem with this method is that you are not properly disposing resources and are relying on the caller to do so.

此方法的唯一问题是您没有正确处理资源，而是依赖调用者来处理。

回答by FlyingStreudel

I have no idea if I understand what you are asking or not, but do you mean something like:

我不知道我是否明白你在问什么，但你的意思是这样的：

public static SqlReader executeProcedure(SqlConnection oConn, string commandName, Dictionary<string, object> params)
{
    SqlCommand comm = oConn.CreateCommand();
    comm.CommandType = CommandType.StoredProcedure;
    comm.CommandText = commandName;
    if (params != null)
        foreach(KeyValuePair<string, object> kvp in params)
            comm.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
    return comm.ExecuteReader();
}

An example of use might be

一个使用示例可能是

Dictionary<string, object> paras = new Dictionary<string, object>();
paras.Add("user_name", "Timmy");
paras.Add("date", DateTime.Now);
SqlReader results = executeProcedure(oConn, "sp_add_user", paras);
while (results.Read())
{
    //do something with the rows returned
}
results.Close();