By Cute


2009-08-11 15:04:26 8 Comments

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?

11 comments

@Salim Proctor 2017-04-24 13:18:34

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;

    }

@Greg R Taylor 2017-02-20 05:24:22

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

Also has a bunch of other features you might like.

@Maddy 2018-03-26 18:52:12

this does not work. Is this outdated? I could not find the method Procedure.

@Greg R Taylor 2018-03-27 02:28:56

I've updated the example, thanks for letting me know.

@Johnny 2016-09-24 20:26:20

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

@PeerNet 2016-10-03 00:07:54

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

@Johnny 2017-05-03 19:12:29

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

@zakaria kasmi 2017-04-06 02:25:39

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

@Shreevardhan 2016-07-01 16:00:36

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

@Mehrdad Afshari 2009-08-11 15:06:31

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

@Remus Rusanu 2011-09-14 16:53:05

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

@Mehrdad Afshari 2011-09-14 18:25:49

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.

@Dani 2011-10-19 13:22:50

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?

@Mehrdad Afshari 2011-10-20 02:34:10

@Dani Yes. Just add the parameters to the Parameters collection of the SqlCommand object.

@SearchForKnowledge 2014-05-13 13:38:38

Will I be able to send in the DROPDOWN value option in the SP?

@jackncoke 2014-11-24 15:15:03

How do you handle a Single Quote being passed into your method?

@Mike de Klerk 2016-05-02 09:51:54

@MehrdadAfshari I bet the redundant conn.Close(); call will result in a CA2202: Do not dispose objects multiple times code violation.

@Szybki 2017-06-22 10:49:45

@MikedeKlerk I believe not, because while Dispose() may call Close(), calling Close() doesn't mean the object is disposed of. Besides as it's said in SqlConnection.Close() summary: "This is the preferred method of closing any open connection".

@stackoverfloweth 2017-09-22 19:20:43

Should be marked as answer

@B N 2018-01-04 05:54:38

@MehrdadAfshari:I need to create console application for sending emails on some basis. any example or helpful links.

@Muflix 2018-07-10 11:58:55

Why is new SqlCommand in using construct ?

@Vaibhav Bhootna 2013-12-24 19:48:14

Calling Store Procedure in C#

SqlCommand cmd = new SqlCommand("StoreProcedureName",con);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@value",txtValue.Text);
int rowAffected=cmd.ExecuteNonQuery();

@Bhaskar 2009-08-11 15:11:38

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:

@TruMan1 2012-03-14 03:46:29

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

@abc 2011-06-29 10:46:51

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

@Dewfy 2009-08-11 15:18:18

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

@BlackTigerX 2009-08-11 15:07:21

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

}}

@Cute 2009-08-11 15:23:02

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

@marc_s 2009-08-11 15:53:38

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?

@Cute 2009-08-11 16:09:39

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

@BlackTigerX 2009-08-11 18:43:21

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

@marc_s 2009-08-12 04:45:10

@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

@marc_s 2009-08-12 12:04:24

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

@BlackTigerX 2009-08-12 16:53:40

and you might want to save your self some typing and potential errors, use using instead of try..finally

Related Questions

Sponsored Content

26 Answered Questions

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

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

23 Answered Questions

[SOLVED] How do you give a C# Auto-Property a default value?

42 Answered Questions

[SOLVED] How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 980938 View
  • 1696 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

38 Answered Questions

16 Answered Questions

[SOLVED] Select columns from result set of stored procedure

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

25 Answered Questions

63 Answered Questions

[SOLVED] How do I calculate someone's age in C#?

  • 2008-07-31 23:40:59
  • Jeff Atwood
  • 524150 View
  • 1743 Score
  • 63 Answer
  • Tags:   c# .net datetime

1 Answered Questions

Sponsored Content