Tuesday, May 5, 2009

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:

No comments:

Post a Comment