在c#中调用带参数的存储过程 [英] Call a stored procedure with parameter in c#

问题描述

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

I'm able to delete, insert and update in my program and I try to do an insert by calling a created stored procedure from my database.

我制作的这个按钮插入效果很好.

This button insert I made works well.

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) 的两个参数.我在谷歌上搜索了一些很好的例子，但没有发现任何有趣的东西.

This is the start of the button that calls the procedure named sp_Add_contact to add a contact. The two parameters for sp_Add_contact(@FirstName,@LastName). I searched on google for some good examples but found nothing interesting.

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

推荐答案

这与运行查询几乎相同.在您的原始代码中，您正在创建一个命令对象，将其放在 cmd 变量中，并且永远不要使用它.但是，在这里，您将使用它而不是 da.InsertCommand.

It's pretty much the same as running a query. In your original code you are creating a command object, putting it in the cmd variable, and never use it. Here, however, you will use that instead of da.InsertCommand.

此外，对所有一次性对象使用 using，以确保它们被正确处理:

Also, use a using for all disposable objects, so that you are sure that they are disposed properly:

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

这篇关于在c#中调用带参数的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！