Using ADO.NET with Parameterized Stored Procedures
ADO.NET class that deals with input and output stored procedure parameters is DbCommand. This shouldn’t come as a big surprise—DbCommand is responsible for executing commands on the database, so it makes sense that it should also deal with their parameters. (Remember that DbCommand is just a base class for “real” command objects, such as SqlCommand.)
Using Input Parameters
When adding an input parameter to a command object, you need to specify the parameter’s name, data type, and value. The DbCommand object stores its parameters in a collection named Parameters, which contains DbParameter objects. Each DbParameter instance represents a parameter.
Given that you have a DbCommand object named comm, the following code snippet creates a DbParameter object for the command using the CreateParameter method, sets its properties, and adds the parameter to the command’s Parameters collection.
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@DepartmentID";
param.Value = value;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
The command’s CreateParameter method always returns a parameter object type specific to the data provider you’re using, so the DbParameter object will actually reference a SqlParameter instance if you’re using SQL Server, and so on.
Another important property of DbParameter is size, which is good to set for data types that don’t have fixed values, such as VarChar. For numerical columns, specify the parameter size in bytes. For columns that store strings (such as Char, VarChar, or even Text), specify the size in number of characters. Longer strings are automatically truncated to the size specified for the parameter.
Using Output Parameters
Output stored procedure parameters behave like Out parameters in C#. They are much like return values, in that you set their value in the stored procedure and read it from the calling function after executing the procedure. Output parameters are especially useful when you have more return values, when you want to return non-integer data, or when you prefer to keep using the return value for indicating executing success (or for some other purpose).
The code that creates an output parameter is as follows:
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@HowManyProducts";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
This is almost the same as the code for the input parameter, except instead of supplying a value for the parameter, you set its Direction property to ParameterDirection.Output. This tells the command that @HowManyProducts is an output parameter.
Not original,copied from, :Beginning ASP.NET E-Commerce in C# From Novice to Professional
