调用c#中带有参数的存储过程

I can do a delete, insert and update in my program and I try to do an insert by call a created stored procedure from my database.

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

This a button insert I make work 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);
    } 

This is the start of the button to call 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 example but I found nothing interesting.

这是调用名为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);
    }

7 个解决方案

#1

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.

它与运行查询非常相似。在原始代码中，您正在创建一个命令对象，并将其放入cmd变量中，并且永远不会使用它。然而，在这里，您将使用它而不是da.InsertCommand。

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

#2

You have to add parameters since it is needed for the SP to execute

您必须添加参数，因为执行SP是必需的

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

#3

cmd.Parameters.Add(String parameterName, Object value) is deprecated now. Instead use cmd.Parameters.AddWithValue(String parameterName, Object value)

cmd.Parameters。现在不赞成添加(String parameterName, Object value)。而不是使用cmd.Parameters。parameterName AddWithValue(字符串、对象值)

Add(String parameterName, Object value) has been deprecated. Use AddWithValue(String parameterName, Object value)

添加(字符串参数名，对象值)已被弃用。使用AddWithValue(String parameterName, Object value)

There is no difference in terms of functionality. The reason they deprecated the cmd.Parameters.Add(String parameterName, Object value) in favor of AddWithValue(String parameterName, Object value) is to give more clarity. Here is the MSDN reference for the same

在功能上没有区别。他们弃用cmd.Parameters的原因。添加(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();
    }
  }
}

#4

As an alternative, I have a library that makes it easy to work with procs: https://www.nuget.org/packages/SprocMapper/

作为一种替代方法，我有一个使使用procs变得容易的库: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("StoreProcedureName");

#5

Adding the parameters separately gave me issues, so I did this and it worked great:

单独添加参数会给我带来问题，所以我做了这个，效果很好:

 string SqlQ = string.Format("exec sp_Add_contact '{0}', '{1}'", txtFirstName.Text, txtLastName.Text);
 using (SqlConnection con = new SqlConnection(dc.Con)) {
   using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {


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

#6

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

#7

The .NET Data Providers consist of a number of classes used to connect to a data source, execute commands, and return recordsets. The Command Object in ADO.NET provides a number of Execute methods that can be used to perform the SQL queries in a variety of fashions.

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

A stored procedure is a pre-compiled executable object that contains one or more SQL statements. In many cases stored procedures accept input parameters and return multiple values . Parameter values can be supplied if a stored procedure is written to accept them. A sample stored procedure with accepting input parameter is given below :

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

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

The above stored procedure is accepting a country name (@COUNTRY VARCHAR(20)) as parameter and return all the publishers from the input country. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters.

上面的存储过程接受一个国家名(@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);

The above code passing country parameter to the stored procedure from C# application.

上面的代码将国家参数从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();
        }
    }
}