|
|
 |
A common interaction with an MSSQL database is to retrieve all records with a given attribute. A classic example is to retrieve all products with a given category ID. Usually, you'll use a stored procedure that expects a single parameter, the category ID. To accomplish this task, you need to follow the following recipe:
- Create an instance of connection to the database, a
SqlConnection object.
Create an instance of an SQL command, a SqlCommand object.
Mark the command as a stored procedure.
Create a parameter instance object.
Set the parameter instance object's Value parameter.
Add the parameter instance object to the SQL command object, created above.
Execute the command.
Return the SqlDataReader result to the caller.
Let's take an example. The ProductsDB.js file includes the method GetProducts(categoryID: int). As its name implies, this method retrieves all products belonging to categoryID. It follows the recipe above, calling the stored procedure ProductsByCategory. This stored procedure expects a single parameter, CategoryID. It returns a table of all products with the given CategoryID. Here is the code:
public function GetProducts(categoryID: int) : SqlDataReader {
var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
var myCommand : SqlCommand = new SqlCommand("ProductsByCategory", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
var parameterCategoryID : SqlParameter = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
parameterCategoryID.Value = categoryID;
myCommand.Parameters.Add(parameterCategoryID);
myConnection.Open();
var result : SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
People who read this tip also read these tips:
Look for similar tips by subject:
|
