For what it's worth, I enjoy using Stored Procedures. I know there have been quite a few debates over the last year regarding myths and facts involving them, but I would rather not get into that right now. Instead, I'm going to show a few stored procedure examples.
Stored Procedures allow you to store all database-related queries and other transactions inside of the database. Some argue about the actual benefits, but either way, they have a place in the database world.
Before I continue, you must know that stored procedures accept three types of parameters: input, output and return. Input parameters take data from the outside world into the stored procedure. Try to think of a standard function in any programming language and the parameters they accept. Those parameters are similiar to stored procedure input parameters.
In my first example, I'm going to create a simple stored procedure that INSERTs a record in a table.
AddUser.sql
-----------------------------------------------------------------
Create Procedure dbo.AddUser
(
@username varchar(50),
@first_name varchar(50),
@last_name varchar(50)
)
AS
insert into tblUsers(username,first_name,last_name) values(@username,@first_name,@last_name)
In this example, my stored procedure, AddUser, is owned by the dbo and takes three input parameters: @username,@first_name, and @last_name. In addition, it inserts these values into the tblUsers table.
Since the SQL statement is inside of the procedure, you won't need to execute the same INSERT command inside of your .NET. Instead, you will call the stored procedure and specify the parameter values. But first, lets create our connection and command objects.
SqlConnection conn = new SqlConnection("connection_string_here;");
SqlCommand cmd = new SqlCommand("AddUser",conn);
cmd.CommandType = CommandType.StoredProcedure;
The code above creates our connection object, conn, and sets the connection string. Depending on your database information, you will need to change that. After that, our command object, cmd, is created. For the purpose of this example, the SqlCommand constructor takes two parameters: the text for the sql command and a connection instance. Because our stored procedure is called “AddUser”, we specify that in our first parameter and type conn in our second parameter. You'll also notice that I have specified a commandtype. This tells ADO.NET that we are trying to execute a stored procedure, not a standard ad-hoc query. If you forgot that portion, you would get an error because AddUser is not a valid SQL command.
Next, we need to specify our parameters. If you didn't specify any parameters in your stored procedure, you wouldn't need to write the following lines of code. However, since I did, I need to specify the parameters or else my application would complain.
SqlParameter param =
new SqlParameter(
"@username",SqlDbType.VarChar);
param.Value = "sbauer";
SqlParameter param2 = new SqlParameter("@first_name",SqlDbType.VarChar);
param2.Value = "Shane";
SqlParameter param3 = new SqlParameter("@last_name",SqlDbType.VarChar);
param3.Value = "Bauer";
cmd.Parameters.Add(param);
cmd.Parameters.Add(param2);
cmd.Parameters.Add(param3);
In the piece of code above, I have created three SqlParameter objects (one for each parameter). In the constructor for each one, I've specified the parameter name, and the database data type. As you can tell, our parameter name and data type match what it is in stored procedure. After we initiate each object, I then had to specify the value. I simply used hard-coded strings, such as “Shane” and “Bauer”, but you can use variables as well.
Once you have created each parameter, you must put each parameter inside of the command object's parameters collection. To do this, you simply do: cmd.Parameters.Add(YOUR_PARAM_OBJ_HERE).
Once you have added your parameters to the collection, all you need to do is open the connection, execute the command and close the connection. Because our command doesn't return a resultset, you can use the ExecuteNonQuery() method of the SqlCommand object.
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Assuming you have setup the database correctly, your command should execute.
If you want to know more technical details about stored procedures, check out a few sites on the web. My goal was to give a brief overview of stored procedures, while showing you how to use them in .NET. Tomorrow, I will explain Output and Return value parameters.
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5