Call a stored procedure with parameter in c#

Question

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

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

Aphoristic

Just an extra bit of info - you should not name your application stored procedures with an sp_ prefix, like above with sp_Add_contact. the sp_ prefix is a system stored proc naming convention, that, when SQL sees it, will search through all system stored procedures first before any application or user space stored procs. As a matter of performance, if you care about that in your application, the sp_ prefix will degrade your response times. – Ondrej

Answer 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.

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

Ferrite

but if this procedure returns data, how can I catch it in C#? – Turino

@M009: Then you use ExecuteReader or ExecuteScalar to call it. – Ferrite

does the parameters' case matter? are @FirstName and @FIRSTNAME the same? – Gao

@DylanChen: That depends on the database settings. The default setting is that identifiers are not case sensetive. – Ferrite

@Ferrite do you mean the settings in SQL Server/SQL Developer, or in Visual Studio's Server Explorer? – Gao

@DylanChen: It's the collation setting of the database that determines whether identifiers are case sensetive. – Ferrite

Answer 2

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

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

Acanthopterygian

AddWithValue is a bad idea; SQL Server doesn't always use the correct length for nvarchar or varchar, causing an implicit conversion to occur. It's better to specify the parameter's length explicitly, and then add the value separately using parameter.Value = txtfirstname. – Bohr

I'm curious, what's an example of such incorrect "implicit conversion"? (One can learn much by seeing things fail.) – Shifty

Answer 3

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

There is no difference in terms of functionality. The reason they deprecated Add(String parameterName, Object value) in favor of AddWithValue(String parameterName, Object value) is to give more clarity.

Microsoft Documentation

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

Diaghilev

The comment about Add being deprecated is valid, and invalidates the accepted answer. Quote: "AddWithValue replaces the Add method .. that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload... Use AddWithValue whenever you want to add a parameter by specifying its name and value." msdn.microsoft.com/en-us/library/… Corrected syntax of example by @RahulNikate. – Carbarn

@TonyG: that's not true, the accepted answer uses the preferred overload of Add which is also not deprecated. AddWithValue is also not the best way since it infers the type of the parameter from the paramater value. This often leads to bad execution plans or incorrect conversions. It also doesn't validate the parameter in the first place(f.e. type if Datetime but you pass a String). You can see here that only Add which takes an Object as second argument is deprecated. – Breastwork

Answer 4

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("StoredProcedureName");

Amboise

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

Demurrer

Parameter is missing, but IMHO best answer, if you include command.parameters.AddSqlParameter("@FirstName", SqlDbType.VarChar).Value ="myText" and if (connection.State != ConnectionState.Closed) connection.Close(); . – Hailey

Answer 6

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

Mosemoseley

Your answer does not use using blocks which is a best practice. Also, there should be a try catch block to deal with any exceptions. – Enterectomy

Answer 7

Here is my technique I'd like to share. Works well so long as your clr property types are sql equivalent types eg. 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();
        }
    }
}

Bouillon

Answer 8

A reusable stored procedure calling method:

public static DataTable GetValFromStoredProcedure(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.GetValFromStoredProcedure(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"));
    }
}

Pucida