Stored Procedures
Writing code that generates correct SQL code for calling stored procedures, or with UPDATE and INSERT statements can often be a real pain - especially when worrying about escaping strings, or getting date formats correct. Using the SqlCommand/OleDbCommand object allows us to provide a SQL statement with variables waiting to be filled - and we can then set the value of these variables seperately in our code, and let ADO.NET worry about sending it to the database correctly! First, we create the SqlCommand object as usual. If we are calling a stored procedure, then we just need to provide its name.
Calling stored procedure in C#
Create connectionString variable for sql and assign it to sqlconnection object.
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(”SP_Insert”,conn);
//where SP_Insert is the name of the stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// tell the command that this is a stored procedure
We specify the parameters by adding SqlParameter/OleDbParameter objects to the Command object’s Parameters property. The constructor we’ll use here accepts a string for the parameters name, and an OleDbType or SqlDbType for the data type. We then add this new Parameter object to the collection, and set its Value property:
cmd.Parameters.Add(New SqlParameter(”@fname”,SqlDBType.Varchar(255)));
cmd.Parameters.Add(New SqParameter(”@lname”,SqlDBType.Varchar(255)));
cmd.Parameters[”@fname”].Value = “Gary”;
cmd.Parameters[”@lname”].Value = “Harwell”;
cmd.ExecuteNonQuery();
Creating the stored procedure in sqlserver 2005
create procedure SP_Insert
@fname varchar(max),@lname varchar(maX)
as insert into tbluser values(@fname,@lname)
For altering the procedures alter keyword is used in place of create.
