Sample code ado.net call store procedure

Example code ado.net call store procedure C#

Table1 have id and name columns and have procedure follow

CREATE PROCEDURE [dbo].[INSERT]
   @id varchar(50)
   ,@name varchar(50)
AS
BEGIN
   SET NOCOUNT ON
   insert into table1 values(@id, @name)
END

Sample code c# call this procedure

static void Main(string[] args)
       {
           Console.WriteLine("Hello world");
           Console.ReadLine();

           string strconn = @"Data Source = host; Initial Catalog = dbname; User ID = user;Password=password;" + " MultipleActiveResultSets=True";
           SqlConnection conn = new SqlConnection(strconn);
           SqlCommand cmd = new SqlCommand();
           cmd.Connection = conn; // set connection
           cmd.CommandType = CommandType.StoredProcedure; // set type
           //cmd.CommandType = CommandType.Text;// default
           cmd.CommandText = "INSERT";// store procedure name
           //cmd.CommandText = "insert into table1 values('id', 'name')";

           SqlParameter p1 = new SqlParameter("@id", "2");
           // create param for pass to procedure
           SqlParameter p2 = new SqlParameter("@name", "b");

           cmd.Parameters.Add(p1);
           // pass param to procedure
           cmd.Parameters.Add(p2);

           conn.Open();
           int result = cmd.ExecuteNonQuery();
           conn.Close();
         
           cmd.Dispose();
           conn.Dispose();

           Console.WriteLine("Excuted.");
           Console.WriteLine("Result: " + result);
           Console.ReadLine();
       }

• in procedure insert if we set nocount on this procedure won't return effect of insert statement
• if we remove set nocount in this procedure when we execute sqlcommand we can get return effect from procedure is -1
  

Related:

• http://juuier.blogspot.com/2009/05/stored-procedures-optimization-tips.html
• http://juuier.blogspot.com/2009/05/should-be-use-store-procedure.html
• http://juuier.blogspot.com/2008/11/store-procedure-sqlserver.html