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. If we are using a normal SQL statement, then we'll prefix the parameter names with @ (as they are in Stored Procedures).
[C#]
// stored procedure
SqlCommand com = new SqlCommand("UpdateUser",sqlConn);
// tell the command that this is a stored procedure!
com.CommandType = CommandType.StoredProcedure;
/*
we'll assume the Stored Procedure takes the following form at the server:
ALTER PROCEDURE UpdateUser(@id AS INTEGER, @username AS VARCHAR(30)) AS
UPDATE users SET username=@username WHERE userId=@id
*/
or
// normal SQL statement
OleDbCommand com = new OleDbCommand("UPDATE users SET username=@username WHERE userId=@id",sqlConn);
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:
[VB]
' add a parameter @username to the command, and set its value to the string "James"
com.Parameters.Add(New SqlParameter("@username", OleDbType.VarChar)).Value = "James"
' add a parameter @id to the command, and set its value to 1
com.Parameters.Add(New SqlParameter("@id", OleDbType.Integer)).Value = 1
' we can now execute the command...
com.ExecuteNonQuery()
[C#]
// add a parameter @username to the command, and set its value to the string "James"
com.Parameters.Add(new SqlParameter("@username",OleDbType.VarChar)).Value = "James";
// add a parameter @id to the command, and set its value to 1
com.Parameters.Add(new SqlParameter("@id",OleDbType.Integer)).Value = 1;
// we can now execute the command...
com.ExecuteNonQuery();
Stored Procedure Return Values
If your stored procedure uses the RETURN statement, or has parameters marked as out , then you can also use the Parameters collection to retrieve these values after a query has been executed.
To get the value of a RETURN statement, you need to do the following:
[C#]
// add a new parameter, with any name we want - its for our own use only
SqlParameter sqlParam = com.Parameters.Add("@ReturnValue", SqlDbType.Int);
// set the direction flag so that it will be filled with the return value
myParm.Direction = ParameterDirection.ReturnValue;
Then, after the stored procedure has been executed,
int returnValue = (int)com.Parameters["@ReturnValue"].Value
will retrieve the value that was set.
Now suppose you had a stored procedure that has an output parameter - maybe something like the following:
CREATE PROCEDURE AddUser @UserName VarChar(30), @Identity int OUT AS ...
then again, we can use
[C#]
// add a new parameter, we need to get the name right this time!
SqlParameter sqlParam = com.Parameters.Add("@Identity", SqlDbType.Int);
// set the direction flag so that it will be filled with the return value
myParm.Direction = ParameterDirection.Output;
and after the execution of the query, com.Parameters["@Identity"].Value will contain the value that was set in the stored procedure. If you need any further info, see
Input and Output Parameters, and Return Values on MSDN.
Comments