Home > DeveloperSection > Forums > How to execute a stored procedure within C# program
Takeshi Okada
Takeshi Okada

Total Post:89

Points:629
Posted on    January-29-2014 12:02 AM

 C# C# 
Ratings:


 1 Reply(s)
 734  View(s)
Rate this:

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


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?



Pravesh Singh

Total Post:411

Points:2881
Posted on    January-29-2014 12:55 AM

Hi Takeshi,

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

}


Don't want to miss updates? Please click the below button!

Follow MindStick