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