在C#中调用带参数的存储过程

Question

我能够在程序中删除、插入和更新数据，并尝试通过调用从数据库中创建的存储过程来进行插入。

这个我制作的插入按钮运行良好。

private void btnAdd_Click(object sender, EventArgs e)
{
        SqlConnection con = new SqlConnection(dc.Con);
        SqlCommand cmd = new SqlCommand("Command String", con);
        
        da.InsertCommand = new SqlCommand("INSERT INTO tblContacts VALUES (@FirstName, @LastName)", con);
        da.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
        da.InsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;

        con.Open();
        da.InsertCommand.ExecuteNonQuery();
        con.Close();

        dt.Clear();
        da.Fill(dt);
    } 

这是一个按钮的开始，用于调用名为sp_Add_contact的过程来添加联系人。 sp_Add_contact(@FirstName,@LastName)有两个参数。我在谷歌上搜索了一些好的示例，但没有找到什么有趣的内容。

private void button1_Click(object sender, EventArgs e)
{
        SqlConnection con = new SqlConnection(dc.Con);
        SqlCommand cmd = new SqlCommand("Command String", con);
        cmd.CommandType = CommandType.StoredProcedure;

        ???
        
        con.Open();
        da. ???.ExecuteNonQuery();
        con.Close();

        dt.Clear();
        da.Fill(dt);
    }

- FrankSharp

---

额外提供一点信息 - 你不应该给你的应用程序存储过程命名以sp_前缀，就像上面的sp_Add_contact一样。sp_前缀是系统存储过程命名约定，当SQL看到它时，它会首先搜索所有系统存储过程，然后再搜索任何应用程序或用户空间存储过程。就性能而言，如果你在应用程序中关心这个问题，sp_前缀将降低响应时间。 - Robert Achmann

Answer 1

这基本上与运行查询相同。在您的原始代码中，您正在创建一个命令对象，将其放入cmd变量中，但从未使用它。然而，在这里，您将使用它来替换da.InsertCommand。

此外，对于所有可处理的对象，请使用using，以确保它们被正确处理：

private void button1_Click(object sender, EventArgs e) {
  using (SqlConnection con = new SqlConnection(dc.Con)) {
    using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
      cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;

      con.Open();
      cmd.ExecuteNonQuery();
    }
  }
}

- Guffa

---

如果这个过程返回数据，我该如何在C#中捕获它？ - MA9H

然еҗҺдҪ дҪҝз”ЁExecuteReaderжҲ–ExecuteScalarжқҘи°ғз”Ёе®ғгҖӮ - Guffa

@M009：是的，这是另一种实现相同功能的方法。数据适配器使用“ExecuteReader”。 - Guffa

@DylanChen：这取决于数据库设置。默认设置是标识符不区分大小写。 - Guffa

@DylanChen：数据库的排序设置决定了标识符是否区分大小写。 - Guffa

Answer 2

你需要添加参数，因为存储过程需要它来执行

using (SqlConnection con = new SqlConnection(dc.Con))
{
    using (SqlCommand cmd = new SqlCommand("SP_ADD", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@FirstName", txtfirstname.Text);
        cmd.Parameters.AddWithValue("@LastName", txtlastname.Text);
        con.Open();
        cmd.ExecuteNonQuery();
    }            
}

- Ravi Gadag

---

AddWithValue 不是一个好的选择；SQL Server 不总是使用正确的长度来处理 nvarchar 或 varchar，这会导致隐式转换发生。最好显式地指定参数的长度，然后使用 parameter.Value = txtfirstname 单独添加值。 - George Stocker

我很好奇，这种不正确的“隐式转换”的例子是什么？（通过看到事情失败，人们可以学到很多东西。） - FloverOwe

Answer 3

cmd.Parameters.Add(String parameterName, Object value) 已不推荐使用。请改用 cmd.Parameters.AddWithValue(String parameterName, Object value)。

Add(String parameterName, Object value)已经被弃用，使用 AddWithValue(String parameterName, Object value)

从功能上来看没有区别。他们弃用了 cmd.Parameters.Add(String parameterName, Object value)，而支持 AddWithValue(String parameterName, Object value)，是为了更加清晰易懂。 这里提供了同样的MSDN参考

private void button1_Click(object sender, EventArgs e) {
  using (SqlConnection con = new SqlConnection(dc.Con)) {
    using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.AddWithValue("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
      cmd.Parameters.AddWithValue("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;

      con.Open();
      cmd.ExecuteNonQuery();
    }
  }
}

- Rahul Nikate

---

关于Add方法被弃用的评论是有效的，并且使得已接受的答案无效。引用：“AddWithValue替换了Add方法...该方法采用字符串和对象作为参数的重载已被弃用，因为可能会与SqlParameterCollection.Add重载产生歧义...每当您想要通过指定其名称和值来添加参数时，请使用AddWithValue。” https://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlparametercollection.addwithvalue%28v=vs.110%29.aspx@RahulNikate的示例语法已经纠正。 - TonyG

@TonyG说的不是真的，被接受的答案使用了Add的首选重载，这一重载也没有被弃用。AddWithValue也不是最佳方式，因为它会从参数值推断参数类型，这常常导致糟糕的执行计划或错误的转换。而且，它首先不验证参数（例如，如果参数类型为Datetime但你传递了一个String），你可以在此处看到：https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.add(v=vs.110).aspx 只有以对象作为第二个参数的Add方法已经被弃用。 - Tim Schmelter

你说得完全正确，@TimSchmelter。我对文本的理解是有误的。感谢您的纠正。我正在编写一些新代码，将使用Add()。我会将我的赞改为踩，因为Rahul Nikate和我一样都犯了错误。 - TonyG

@TonyG 我的回答只涉及 Add(String parameterName, Object value) 方法，而不是所有 Add() 方法的重载。因此，对我的回答进行负评是不公平的。谢谢。 - Rahul Nikate

Answer 4

作为一种替代方案，我有一个库可以让处理过程变得更容易：https://www.nuget.org/packages/SprocMapper/

SqlServerAccess sqlAccess = new SqlServerAccess("your connection string");
    sqlAccess.Procedure()
         .AddSqlParameter("@FirstName", SqlDbType.VarChar, txtFirstName.Text)
         .AddSqlParameter("@FirstName", SqlDbType.VarChar, txtLastName.Text)
         .ExecuteNonQuery("StoredProcedureName");

- Greg R Taylor

Answer 5

public void myfunction(){
        try
        {
            sqlcon.Open();
            SqlCommand cmd = new SqlCommand("sp_laba", sqlcon);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            sqlcon.Close();
        }
}

- user6916720

---

参数缺失，但在我看来最好的答案是，如果您包括 command.parameters.AddSqlParameter("@FirstName", SqlDbType.VarChar).Value ="myText" 和 if (connection.State != ConnectionState.Closed) connection.Close();。 - Tzwenni

Answer 6

.NET数据提供程序由一系列用于连接数据源、执行命令和返回记录集的类组成。ADO.NET中的Command对象提供了多个Execute方法，可用于以各种方式执行SQL查询。

存储过程是一个预编译的可执行对象，包含一个或多个SQL语句。在许多情况下，存储过程接受输入参数并返回多个值。如果编写存储过程以接受它们，则可以提供参数值。以下是一个带有输入参数的示例存储过程：

  CREATE PROCEDURE SPCOUNTRY
  @COUNTRY VARCHAR(20)
  AS
  SELECT PUB_NAME FROM publishers WHERE COUNTRY = @COUNTRY
  GO

上述存储过程接受一个国家名称(@COUNTRY VARCHAR(20))作为参数，并返回来自输入国家的所有出版商。一旦CommandType设置为StoredProcedure，您可以使用Parameters集合定义参数。

  command.CommandType = CommandType.StoredProcedure;
  param = new SqlParameter("@COUNTRY", "Germany");
  param.Direction = ParameterDirection.Input;
  param.DbType = DbType.String;
  command.Parameters.Add(param);

上述代码从C#应用程序向存储过程传递国家参数。

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection connection ;
            SqlDataAdapter adapter ;
            SqlCommand command = new SqlCommand();
            SqlParameter param ;
            DataSet ds = new DataSet();

            int i = 0;

            connetionString = "Data Source=servername;Initial Catalog=PUBS;User ID=sa;Password=yourpassword";
            connection = new SqlConnection(connetionString);

            connection.Open();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SPCOUNTRY";

            param = new SqlParameter("@COUNTRY", "Germany");
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            command.Parameters.Add(param);

            adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);

            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                MessageBox.Show (ds.Tables[0].Rows[i][0].ToString ());
            }

            connection.Close();
        }
    }
}

- Sudhakar Rao

---

你的答案没有使用 using 块，这是最佳实践。此外，应该有一个 try catch 块来处理任何异常。 - Trisped

Answer 7

我想分享我的技巧。只要您的clr属性类型与sql相应类型匹配，例如bool -> bit，long -> bigint，string -> nchar/char/varchar/nvarchar，decimal -> money，就可以很好地工作。

public void SaveTransaction(Transaction transaction) 
{
    using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString))
    {
        using (var cmd = new SqlCommand("spAddTransaction", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (var prop in transaction.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
                cmd.Parameters.AddWithValue("@" + prop.Name, prop.GetValue(transaction, null));
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

- Matstar

Answer 8

一个可重复使用的存储过程调用方法：

public static DataTable GetValFromStoreProc(SqlParameter[] parameters, string storedProcName, SqlConnection conn)
{
    DataTable dt = new DataTable();
    using (DataTable MyDT = new DataTable())
    {
        try
        {
            using (SqlConnection dbconn = conn)
            {
                dbconn.Open();
                try
                {
                    using (SqlDataReader MyDR = DB.ExecuteStoredProcReader(storedProcName, parameters, dbconn))
                    {
                        if (MyDR != null)
                        {                                                                      
                            dt.Load(MyDR);                                                                        
                        }
                    }
                }
                catch (Exception ex)
                {
                    string aaa = ex.Message;
                }
            }
            return dt;
        }
        catch (Exception ex)
        {
            string aaaa = ex.Message;
            return dt;
        }
    }
}

//Usage
var dt = AppLogic.GetValFromStoreProc(new[] { new SqlParameter("SKU", VendorFullSKU) }, "xtr_CheckBackorderedForATP", DB.dbConn());                
DataTableReader dataReader = dt.CreateDataReader();
var quan = -1;
if (dataReader != null)
{
    //only first top record
    if (dataReader.Read())
    {
        quan = dataReader.GetInt32(dataReader.GetOrdinal("Quan"));
    }
}

- estinamir