Outline:
- Why Use Stored Procedures?
- Creating a Stored Procedure
- Calling a Stored Procedure
- Specifying Parameters
- Data Retrieval
- Inserting Data Using Parameters
Why Use Stored Procedures?
There are several advantages of using stored procedures instead of standard SQL. First, stored procedures allow a lot more flexibility offering capabilities such as conditional logic. Second, because stored procedures are stored within the DBMS, bandwidth and execution time are reduced. This is because a single stored procedure can execute a complex set of SQL statements. Third, SQL Server pre-compiles stored procedures such that they execute optimally. Fourth, client developers are abstracted from complex designs. They would simply need to know the stored procedure's name and the type of data it returns.
Creating a Stored Procedure
Enterprise Manager provides an easy way to create stored procedures. First, select the database to create the stored procedure on. Expand the database node, right-click on "Stored Procedures" and select "New Stored Procedure...". You should see the following:
CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS |
CREATE PROCEDURE [dbo].[GetProducts] AS |
CREATE PROCEDURE [dbo].[GetProducts] AS SELECT ProductID, ProductName FROM Products |
Now that we have created a stored procedure, we will examine how to call it from within a C# application.
Calling a Stored Procedure
A very nice aspect of ADO.NET is that it allows the developer to call a stored procedure in almost the exact same way as a standard SQL statement.
1. Create a new C# Windows Application project.
2. From the Toolbox, drag and drop a DataGrid onto the Form. Resize it as necessary.
3. Double-click on the Form to generate the Form_Load event handler. Before entering any code, add "using System.Data.SqlClient" at the top of the file.
Enter the following code:
private void Form1_Load(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=localhost;Database=Northwind;Integrated Security=SSPI"); SqlCommand command = new SqlCommand("GetProducts", conn); SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet ds = new DataSet(); adapter.Fill(ds, "Products"); this.dataGrid1.DataSource = ds; this.dataGrid1.DataMember = "Products"; } |
Specifying Parameters
Most of the time, especially when using non-queries, values must be supplied to the stored procedure at runtime. For instance, a @CategoryID parameter can be added to our GetProducts procedure in order to specify to retrieve only products of a certain category. In SQL Server, parameters are specified after the procedure name and before the AS clause.
CREATE PROCEDURE [dbo].[GetProducts] (@CategoryID int) AS SELECT ProductID, ProductName FROM Products WHERE CategoryID = @CategoryID |
CREATE PROCEDURE [dbo].[SomeProcedure] (
@Param1 int,
@Param2 varchar(50),
@Param3 varchar(50)
) AS
...
|
SELECT ProductID, ProductName FROM Products WHERE CategoryID = 1 |
exec GetProducts X |
SqlConnection conn = new SqlConnection("Data Source=localhost;Database=Northwind;Integrated Security=SSPI"); SqlCommand command = new SqlCommand("GetProducts", conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1; SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet ds = new DataSet(); adapter.Fill(ds, "Products"); this.dataGrid1.DataSource = ds; this.dataGrid1.DataMember = "Products"; |
command.Parameters.Add("@CategoryID", SqlDbType.Int);
command.Parameters["@CategoryID"].Value = 1;
|
command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1;
|
Data Retrieval
Data Retrieval with stored procedures is the same (surprise!) as if using standard SQL. You can wrap a DataAdapter around the Command object or you can use a DataReader to fetch the data one row at a time. The previous examples have already illustrated how to use a DataAdapter and fill a DataSet. The following example shows usage of the DataReader:
SqlConnection conn = new SqlConnection("Data Source=localhost;Database=Northwind;Integrated Security=SSPI"); SqlCommand command = new SqlCommand("GetProducts", conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1; conn.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader["ProductName"]); } conn.Close(); |
Inserting Data Using Parameters
Using other SQL statements such as INSERT, UPDATE or DELETE follow the same procedure. First, create a stored procedure that may or may not accept parameters, and then call the stored procedure from within the code supply the necessary values if parameters are needed. The following example illustrates how to insert a new user in a users table that has a username and password field.
CREATE PROCEDURE [dbo].[InsertUser] (
@Username varchar(50),
@Password varchar(50)
) AS
INSERT INTO Users VALUES(@Username, @Password)
|
string username = ... // get username from user string password = ... // get password from user SqlConnection conn = new SqlConnection("Data Source=localhost;Database=MyDB;Integrated Security=SSPI"); SqlCommand command = new SqlCommand("InsertUser", conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@Username", SqlDbType.VarChar).Value = username; command.Parameters.Add("@Password", SqlDbType.VarChar).Value = password; conn.Open(); int rows = command.ExecuteNonQuery(); conn.Close(); |
if (rows == 1)
{
MessageBox.Show("Create new user SUCCESS!");
}
else
{
MessageBox.Show("Create new user FAILED!");
}
|
DELETE FROM Products WHERE ProductID > 50 |
Conclusion
Stored procedures offer developers a lot of flexibility with many features not available using standard SQL. ADO.NET allows us to use stored procedures in our applications seamlessly. The combination of these two allows us to create very powerful appliations rapidly.
