A Closer Look at ADO.NET: The Command Object - Creating and Executing a Stored Procedure
(Page 5 of 5 )
We will create a stored procedure in the pubs database to count the records of the jobs table (using the statement SELECT COUNT(*) FROM JOBS) and see how we can use it. To create a stored procedure, open SQL Server Enterprise Manager and navigate to the pubs database, then expand it and right click on Stored procedures. Choose New Stored procedure.
Our stored procedure is very simple, we just count how many records are in the table. In the next article we will talk about how to create a parameterized stored procedure and how to use the SqlParameter object. Click on OK after you type the stored procedure as in the above figure. Now Let's take a look at the code that calls this stored procedure:
using System;
// reference to the namespace that contains most
// of the classes that form the ADO.NET Architecture
using System.Data;
// referene to the namespace of the SQL Server .NET Data provider
using System.Data.SqlClient;
namespace AdoApp
{
class Class1
{
static void Main(string[] args)
{
SqlConnection SqlConn1 = new SqlConnection();
SqlConn1.ConnectionString = "Server=(local);Database=pubs;User ID=sa;Password=;";
// creating and initializing the SqlCommand Object
SqlCommand SqlComm1 = new SqlCommand();
SqlComm1.Connection = SqlConn1;
SqlComm1.CommandType = CommandType.StoredProcedure;
SqlComm1.CommandText = "CountRecords";
try
{
SqlConn1.Open();
Console.WriteLine("Calling the database stored procedure returns :" + SqlComm1.ExecuteScalar());
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// here we call the Close() method to close the Connection
SqlConn1.Close();
Console.ReadLine();
}
}
}
}
The only pieces of code that we have changed are the CommandType and CommandText properties of the SqlComm1 object. To execute a stored procedure you need to tell the command object about it through the assignment of the enumeration value CommandType.StoredProcedure to the CommandType property, and the assignment of the stored procedure name to the property CommandText. Because the stored procedure returns a scalar value, we must use the ExecuteScalar() method in order to get the value. If we used the ExecuteNonQuery() method, we will not get an error but we will get -1 because no records in the jobs table have been affected by executing the stored procedure. In the next article we are going to talk about SQL Server Stored Procedure and Parameterized stored procedure calls.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 40 
