By FrankSharp


2011-09-24 23:07:09 8 Comments

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.

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 comments

@Greg R Taylor 2017-02-20 05:35:51

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

@Sudhakar Rao 2017-02-21 00:56:27

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

@Trisped 2018-08-16 17:42:53

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.

@user6916720 2016-10-03 16:24:29

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

@Ravi Gadag 2011-09-24 23:19:10

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

@George Stocker 2014-11-18 13:31:32

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.

@Rainhider 2015-06-16 17:18:11

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

@Benjamin Gruenbaum 2015-11-14 21:26:16

Enjoy worse performnace and potential SQL injection issues

@Rahul Nikate 2015-04-10 06:15:45

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

@David 2015-05-12 06:32:47

Do you have a link or source to the claim that cmd.Parameters.Add is deprecated?

@Rahul Nikate 2015-05-12 06:42:48

@TonyG 2015-05-20 16:52:30

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.

@Rango 2015-05-21 07:27:49

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

@Rango 2015-05-21 07:31:14

AddWithValue has just the same functionality than Add with Object, but it's not the preferred way. Both need to infer the type.

@TonyG 2015-05-21 15:09:54

You're absolutely right, @TimSchmelter. My reading of the text was flawed. Thanks for the correction. I'm writing some new code where I'll be using Add(). And I'll change my upvote on this Answer to a downvote, as Rahul Nikate was as mistaken as I was.

@Rahul Nikate 2015-05-21 15:19:20

@TonyG My answer is specific about Add(String parameterName, Object value) and not about all overloads of Add() method. So It's not fair to downvote my answer. Thank you

@Rahul Nikate 2015-05-21 15:22:55

@TonyG I've changed my answer as per Tim Schmelter's advise

@Rahul Nikate 2015-05-21 15:24:43

@TimSchmelter Thank you for your advise. I've edited my answer.

@Guffa 2011-09-24 23:16:45

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

@M009 2013-04-16 15:16:37

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

@Guffa 2013-04-16 18:36:21

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

@M009 2013-04-17 15:46:43

Actually I figured it out, first you have to use the "cmd" to declare and initialize a SqlDataAdapter as: [SqlDataAdapter da = new SqlDataAdapter(cmd);], then [da.SelectCommand = cmd;], and finally [da.Fill(ds);] where "ds" is a dataset that you have previously declared.. Then you can use this dataset to read data anyway you desire :)

@Guffa 2013-04-17 21:35:52

@M009: Yes, that is another way to do the same thing. The data adapter uses ExecuteReader.

@Guffa 2014-11-13 17:19:09

@volearix: Regarding your suggested edit; There is no "standard C# bracketing format", and all brackets that are not strictly needed should not be removed.

@Robert Achmann 2014-11-25 03:23:07

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.

@Guffa 2014-11-25 09:08:03

@RobertAchmann: I am vwry well aware of that, but didn't think to comment on it at the time. You should direct that comment to the OP, as thats where the procedure name comes from.

@Robert Achmann 2014-11-25 16:42:26

@Guffa - 'above' should have been 'above in OP' - I didn't mean to direct this at you... This was the accepted answer, so I posted my comment here - sorry, I'll put this under the OP next time... :)

@Dylan Czenski 2016-05-06 15:33:13

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

@Guffa 2016-05-10 08:37:27

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

@Dylan Czenski 2016-05-11 14:55:53

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

@Guffa 2016-05-12 14:22:14

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

@KeplerIO 2017-06-02 23:04:42

Hello all, this answer just helped me out. My only question is: does writing your stored procedure name as a parameter in the SqlCommand constructor automatically resolve your stored procedure in the DB without having to tell the C# app what it is somehow?

Related Questions

Sponsored Content

61 Answered Questions

[SOLVED] What is the difference between String and string in C#?

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

26 Answered Questions

27 Answered Questions

[SOLVED] How do I enumerate an enum in C#?

9 Answered Questions

[SOLVED] What are the correct version numbers for C#?

10 Answered Questions

[SOLVED] Calling the base constructor in C#

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

296 Answered Questions

[SOLVED] Hidden Features of C#?

  • 2008-08-12 16:32:24
  • Serhat Ozgel
  • 658849 View
  • 1476 Score
  • 296 Answer
  • Tags:   c# hidden-features

24 Answered Questions

[SOLVED] Cast int to enum in C#

  • 2008-08-27 03:58:21
  • lomaxx
  • 1173287 View
  • 2847 Score
  • 24 Answer
  • Tags:   c# enums casting

1 Answered Questions

[SOLVED] Exception is System.Data.SqlClient.SqlException: Incorrect syntax near '9988'

Sponsored Content