How to execute a stored procedure within C# program

Question

I want to execute this stored procedure from a C# program.

I have written the following stored procedure in a SqlServer query window and saved it as stored1:

use master 
go
create procedure dbo.test as

DECLARE @command as varchar(1000), @i int
SET @i = 0
WHILE @i < 5
BEGIN
Print 'I VALUE ' +CONVERT(varchar(20),@i)
EXEC(@command)
SET @i = @i + 1
END

EDITED:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace AutomationApp
{
    class Program
    {
        public void RunStoredProc()
        {
            SqlConnection conn = null;
            SqlDataReader rdr  = null;

            Console.WriteLine("\nTop 10 Most Expensive Products:\n");

            try
            {
                conn = new SqlConnection("Server=(local);DataBase=master;Integrated Security=SSPI");
                conn.Open();
                SqlCommand cmd = new SqlCommand("dbo.test", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                rdr = cmd.ExecuteReader();
                /*while (rdr.Read())
                {
                    Console.WriteLine(
                        "Product: {0,-25} Price: ${1,6:####.00}",
                        rdr["TenMostExpensiveProducts"],
                        rdr["UnitPrice"]);
                }*/
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World");
            Program p= new Program();
            p.RunStoredProc();      
            Console.Read();
        }
    }
}

This displays the exception Cannot find the stored procedure dbo.test. Do I need to provide the path? If yes, in which location should the stored procedures be stored?

Wroughtup

You are better off to use a database other than master even for testing. This is a system database and you will cause problems eventually. In SQL 2012 it wont let me create a table there. It will conversely allow me to create a sproc. :/ – Model

Answers notwithstanding: have you checked if your sp was actually created with the name you gave(dbo.test)? I don't know what would happen if a non-dbo user tries to create dbo.test... would it be created as non-dbo.test? – Gregoriagregorian

@obayhan This question was asked 2 years before the one you claim it is a possible duplicate of. Please, in future, mark the most recent question as the duplicate. – Surd

Answer 1

using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("ProcedureName", conn) { 
                           CommandType = CommandType.StoredProcedure }) {
   conn.Open();
   command.ExecuteNonQuery();
}

Moorwort

You can even get rid of the conn.Close, is implied by the Dispose – Incommensurable

That's true for this case. I like to have matching Open and Close calls. If you say, refactor the connection object out as a field in the future and remove the using statement, you might accidentally forget to add Close and end up with an open connection. – Moorwort

How would you do this if the stored proc needed parameters? just add the parameters to the command object with the same names and types? – Outlast

@Outlast Yes. Just add the parameters to the Parameters collection of the SqlCommand object. – Moorwort

Answer 2

using (SqlConnection conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI")) {
    conn.Open();

    // 1.  create a command object identifying the stored procedure
    SqlCommand cmd  = new SqlCommand("CustOrderHist", conn);

    // 2. set the command object so it knows to execute a stored procedure
    cmd.CommandType = CommandType.StoredProcedure;

    // 3. add parameter to command, which will be passed to the stored procedure
    cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

    // execute the command
    using (SqlDataReader rdr = cmd.ExecuteReader()) {
        // iterate through results, printing each to console
        while (rdr.Read())
        {
            Console.WriteLine("Product: {0,-35} Total: {1,2}",rdr["ProductName"],rdr["Total"]);
        }
    }
}

Here are some interesting links you could read:

• http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx
• http://www.c-sharpcorner.com/UploadFile/dclark/InsOutsinCS11302005072332AM/InsOutsinCS.aspx
• http://www.codeproject.com/KB/cs/simplecodeasp.aspx
• http://msdn.microsoft.com/en-us/library/ms171921(VS.80).aspx

Undermanned

You should really use "using" keyword. Push that open/close responsibility to the framework. – Tieshatieup

Definition of SqlCommand: public sealed class SqlCommand : System.Data.Common.DbCommand, ICloneable, IDisposable. Put it into using statement will help cleaning up. – Pashalik

Answer 3

Calling stored procedure in C#:

SqlCommand cmd = new SqlCommand("StoredProcedureName",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@value",txtValue.Text);

con.Open();
int rowAffected = cmd.ExecuteNonQuery();
con.Close();

Preparator

Answer 4

using (SqlConnection sqlConnection1 = new SqlConnection("Your Connection String")) {
using (SqlCommand cmd = new SqlCommand()) {
  Int32 rowsAffected;

  cmd.CommandText = "StoredProcedureName";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Connection = sqlConnection1;

  sqlConnection1.Open();

  rowsAffected = cmd.ExecuteNonQuery();

}}

Isfahan

I am worried about how the cmd.CommandText = "Stored1" interpretes my stored procedure.I dont know. – Wroughtup

The "CommandText" must be set to the NAME of the stored procedure, which is then executed from C# as if you had executed "exec StoredProcedureName" in SSMS - or what are you worried about? – Ottavia

How can i give the storedprocedure name for the above stored procedure can u plz tell me?? – Wroughtup

so, first, you would have to create the stored procedure, in the case of the code you have, you would need to add: "create procedure dbo.NameOfYourStoredProcedureHere as" at the beginning – Isfahan

@Cute: if you have this as a stored procedure, you MUST have a name! The name used in the "CREATE PROCEDURE (procedurename)" call. If you do not have that, then you do not have a stored procedure (but just a batch of T-SQL statements) and then you cannot use the "CommandType = StoredProcedure", obviusly – Ottavia

Your stored procedure is not returning anything - use .ExecuteNonQuery() (as the answer here says) instead of .ExecuteReader() – Ottavia

Answer 5

This is code for executing stored procedures with and with out parameters via reflection. Do note that the objects property names need to match the parameters of the stored procedure.

private static string ConnString = ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString;
    private SqlConnection Conn = new SqlConnection(ConnString);

    public void ExecuteStoredProcedure(string procedureName)
    {
        SqlConnection sqlConnObj = new SqlConnection(ConnString);

        SqlCommand sqlCmd = new SqlCommand(procedureName, sqlConnObj);
        sqlCmd.CommandType = CommandType.StoredProcedure;

        sqlConnObj.Open();
        sqlCmd.ExecuteNonQuery();
        sqlConnObj.Close();
    }

    public void ExecuteStoredProcedure(string procedureName, object model)
    {
        var parameters = GenerateSQLParameters(model);
        SqlConnection sqlConnObj = new SqlConnection(ConnString);

        SqlCommand sqlCmd = new SqlCommand(procedureName, sqlConnObj);
        sqlCmd.CommandType = CommandType.StoredProcedure;

        foreach (var param in parameters)
        {
            sqlCmd.Parameters.Add(param);
        }

        sqlConnObj.Open();
        sqlCmd.ExecuteNonQuery();
        sqlConnObj.Close();
    }

    private List<SqlParameter> GenerateSQLParameters(object model)
    {
        var paramList = new List<SqlParameter>();
        Type modelType = model.GetType();
        var properties = modelType.GetProperties();
        foreach (var property in properties)
        {
            if (property.GetValue(model) == null)
            {
                paramList.Add(new SqlParameter(property.Name, DBNull.Value));
            }
            else
            {
                paramList.Add(new SqlParameter(property.Name, property.GetValue(model)));
            }
        }
        return paramList;

    }

Gravy

Answer 6

SqlConnection conn = null;
SqlDataReader rdr  = null;
conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
conn.Open();

// 1.  create a command object identifying
//     the stored procedure
SqlCommand cmd  = new SqlCommand("CustOrderHist", conn);

// 2. set the command object so it knows
//    to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;

// 3. add parameter to command, which
//    will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

// execute the command
rdr = cmd.ExecuteReader();

// iterate through results, printing each to console
while (rdr.Read())
{
    Console.WriteLine("Product: {0,-35} Total: {1,2}", rdr["ProductName"], rdr["Total"]);
}

Aikido

Answer 7

By using Ado.net

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace PBDataAccess
{
    public class AddContact
    {   
        // for preparing connection to sql server database   

        private SqlConnection conn; 

        // for preparing sql statement or stored procedure that 
        // we want to execute on database server

        private SqlCommand cmd; 

        // used for storing the result in datatable, basically 
        // dataset is collection of datatable

        private DataSet ds; 

        // datatable just for storing single table

        private DataTable dt; 

        // data adapter we use it to manage the flow of data
        // from sql server to dataset and after fill the data 
        // inside dataset using fill() method   

        private SqlDataAdapter da; 


        // created a method, which will return the dataset

        public DataSet GetAllContactType() 
        {



    // retrieving the connection string from web.config, which will 
    // tell where our database is located and on which database we want
    // to perform opearation, in this case we are working on stored 
    // procedure so you might have created it somewhere in your database. 
    // connection string will include the name of the datasource, your 
    // database name, user name and password.

        using (conn = new SqlConnection(ConfigurationManager.ConnectionString["conn"]
        .ConnectionString)) 

                {
                    // Addcontact is the name of the stored procedure
                    using (cmd = new SqlCommand("Addcontact", conn)) 

                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                    // here we are passing the parameters that 
                    // Addcontact stored procedure expect.
                     cmd.Parameters.Add("@CommandType",
                     SqlDbType.VarChar, 50).Value = "GetAllContactType"; 

                        // here created the instance of SqlDataAdapter
                        // class and passed cmd object in it
                        da = new SqlDataAdapter(cmd); 

                        // created the dataset object
                        ds = new DataSet(); 

                        // fill the dataset and your result will be
                        stored in dataset
                        da.Fill(ds); 
                    }                    
            }  
            return ds;
        }
}

****** Stored Procedure ******

CREATE PROCEDURE Addcontact
@CommandType VARCHAR(MAX) = NULL
AS
BEGIN
  IF (@CommandType = 'GetAllContactType')
  BEGIN
    SELECT * FROM Contacts
  END
END

Picard

Your comment lines are broken, also if you can give stored procedure in your comment that would good for us. – Deposition

have added the stored procedure in code, check it out. – Picard

Answer 8

this is an example of a stored procedure that returns a value and it's execution in c#

CREATE PROCEDURE [dbo].[InsertPerson]   
-- Add the parameters for the stored procedure here  
@FirstName nvarchar(50),@LastName nvarchar(50),  
@PersonID int output  
AS  
BEGIN  
    insert [dbo].[Person](LastName,FirstName) Values(@LastName,@FirstName)  

    set @PersonID=SCOPE_IDENTITY()  
END  
Go  


--------------
 // Using stored procedure in adapter to insert new rows and update the identity value.  
   static void InsertPersonInAdapter(String connectionString, String firstName, String lastName) {  
      String commandText = "dbo.InsertPerson";  
      using (SqlConnection conn = new SqlConnection(connectionString)) {  
         SqlDataAdapter mySchool = new SqlDataAdapter("Select PersonID,FirstName,LastName from [dbo].[Person]", conn);  

         mySchool.InsertCommand = new SqlCommand(commandText, conn);  
         mySchool.InsertCommand.CommandType = CommandType.StoredProcedure;  

         mySchool.InsertCommand.Parameters.Add(  
             new SqlParameter("@FirstName", SqlDbType.NVarChar, 50, "FirstName"));  
         mySchool.InsertCommand.Parameters.Add(  
             new SqlParameter("@LastName", SqlDbType.NVarChar, 50, "LastName"));  

         SqlParameter personId = mySchool.InsertCommand.Parameters.Add(new SqlParameter("@PersonID", SqlDbType.Int, 0, "PersonID"));  
         personId.Direction = ParameterDirection.Output;  

         DataTable persons = new DataTable();  
         mySchool.Fill(persons);  

         DataRow newPerson = persons.NewRow();  
         newPerson["FirstName"] = firstName;  
         newPerson["LastName"] = lastName;  
         persons.Rows.Add(newPerson);  

         mySchool.Update(persons);  
         Console.WriteLine("Show all persons:");  
         ShowDataTable(persons, 14); 

Teheran

Answer 9

Using Dapper. so i added this i hope anyone help.

public void Insert(ProductName obj)
        {
            SqlConnection connection = new SqlConnection(Connection.GetConnectionString());
            connection.Open();
            connection.Execute("ProductName_sp", new
            { @Name = obj.Name, @Code = obj.Code, @CategoryId = obj.CategoryId, @CompanyId = obj.CompanyId, @ReorderLebel = obj.ReorderLebel, @logo = obj.logo,@Status=obj.Status, @ProductPrice = obj.ProductPrice,
                @SellingPrice = obj.SellingPrice, @VatPercent = obj.VatPercent, @Description=obj.Description, @ColourId = obj.ColourId, @SizeId = obj.SizeId,
                @BrandId = obj.BrandId, @DisCountPercent = obj.DisCountPercent, @CreateById =obj.CreateById, @StatementType = "Create" }, commandType: CommandType.StoredProcedure);
            connection.Close();
        }

Shumpert

It is best to use using before the connection declaration, or put the Close in a finally block. Otherwise, if an exception occurs, the connection will never be closed. – Punke

Answer 10

No Dapper answer here. So I added one

using Dapper;
using System.Data.SqlClient;

using (var cn = new SqlConnection(@"Server=(local);DataBase=master;Integrated Security=SSPI"))
    cn.Execute("dbo.test", commandType: CommandType.StoredProcedure);

Erle

Answer 11

Please check out Crane (I'm the author)

https://www.nuget.org/packages/Crane/

SqlServerAccess sqlAccess = new SqlServerAccess("your connection string");
var result = sqlAccess.Command().ExecuteNonQuery("StoredProcedureName");

Also has a bunch of other features you might like.

Shonna

How do you return multiple datasets with Crane ? My stored proc returns 3 datasets, and I am only interested in the second dataset. But Crane only returns me the first one. – Jingoism

Answer 12

You mean that your code is DDL? If so, MSSQL has no difference. Above examples well shows how to invoke this. Just ensure

CommandType = CommandType.Text

Plastid

Answer 13

Most Simple and straight forward..

SqlCommand cmd = new SqlCommand("StoredProcedureName",con); // Just like you declared it
cmd.CommandType = CommandType.StoredProcedure; // an attribute related to the object
cmd.Parameters.AddWithValue("@value",txtValue.Text); // Parameter name and text source 

con.Open();
int rowAffected = cmd.ExecuteNonQuery();
con.Close();

Lifesize

Answer 14

What I made, in my case I wanted to show procedure's result in dataGridView:

using (var command = new SqlCommand("ProcedureNameHere", connection) {
            // Set command type and add Parameters
            CommandType = CommandType.StoredProcedure,
            Parameters = { new SqlParameter("@parameterName",parameterValue) }
        }) 
        {
            // Execute command in Adapter and store to dataset
            var adapter = new SqlDataAdapter(command);
            var dataset = new DataSet();
            adapter.Fill(dataset);
            // Display results in DatagridView
            dataGridView1.DataSource = dataset.Tables[0];
        }

Materialist