C #에서 매개 변수를 사용하여 저장 프로 시저를 호출하십시오.

C #에서 매개 변수를 사용하여 저장 프로 시저를 호출하십시오.

---

프로그램에서 삭제, 삽입 및 업데이트를 수행 할 수 있으며 데이터베이스에서 생성 된 저장 프로 시저를 호출하여 삽입을 시도합니다.

이 버튼 삽입은 잘 작동합니다.

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). 좋은 예를 찾기 위해 Google을 검색했지만 흥미로운 것은 없습니다.

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.

또한 using모든 일회용 물체에 a 를 사용하여 올바르게 폐기하십시오.

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

---

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.Text);
        cmd.Parameters.AddWithValue("@LastName", txtlastname.Text);
        con.Open();
        cmd.ExecuteNonQuery();
    }            
}

---

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

Add(String parameterName, Object value) has been deprecated. Use 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

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

---

As an alternative, I have a library that makes it easy to work with 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");

---

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

---

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.

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 :

  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.

  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.

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

---

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

참고URL : https://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp