Using a stored procedure is the best way to interact with a database. I’ll use an example from the Dilithium Networks TCAN system (case.aspx).The first thing that we do is to make a new connection. Since we are using the custom DataLayer module for all database interactions, we already have the connection defined. Here, as per usual, I’ve created a new datalayer and a new connection object. All I need to do is to set the connection = to the datalayer’s connection object. Then, I make a new command object.
The SQLCommand object is a nice clean package. It has a couple item values you need to set, a couple objects you need to add and a couple functions you need to execute.
For settings, you need to give it the connection, tell it the name of the stored procedure (CommandText), and tell it that you’re sending a stored procedure, not a text sql statement (CommandType) ,
Stored procedures have parameters. In this case, we’re updating a QA Test Case’s status. So, the database needs to know which record and what the new status values are. Those are our parameters. In the Stored Procedure in SQL Server, the parameters have @ symbols in front of them and we have to duplicate the names in our code. For the sake of ease and efficiency, were’ going to use the AddWithValue method to add the parameters. All that means is that we’re going to say, “the stored procedure paramenter @xyz is equal to my program’s variable abc“. If you wanted to, you could be very specific with types and sizes, but we aren’t going to go that way unless necessary.
In the code below, you’ll see how we add the parameters to the SQLCommand object. Once we’ve done that, we can start with the actual execution. We’ve got the connection values, so we have the location of the db, the username and password. We’ve got the name of the stored procedure in the database, so we know what we are going to tell the database. We’ve got the values we are going to send to that stored procedure. That’s all we need. Now, just open the connection and use the (ExecuteNonQuery) command to send it. Then, close the connection and take the entire object out of memory by using the Dispose() command.
Dim cn As SqlConnection
Dim dl As New DataLayer
cn = dl.connection
Dim cmd As New SqlCommand
cmd.Connection = cn
cmd.CommandText = "sp_update_case_status"
cmd.CommandType = CommandType.StoredProcedure
With cmd.Parameters
.AddWithValue("@case_id", case_id)
.AddWithValue("@case_status", case_status)
.AddWithValue("@status_AT", status_AT)
.AddWithValue("@status_REG", status_REG)
.AddWithValue("@status_SVT", status_SVT)
.AddWithValue("@status_SIT", status_SIT)
End With
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
cmd.Dispose()
It isn’t really that hard. Just think of it as ordering a pizza.
- You want to order a pizza (add the name of your stored procedure)
- You get the phone number of the pizza parlor (connection string)
- You decide what toppings you want on it (parameters)
- Pick up the phone and wait for an answer (open connection)
- Place your order (execute query)
- hang up (close connection)
Now, there are a lot more things that the SQL Command can do – and better ways to do it, but this is a big step. For our company development methodology, we start by using the RunSQL() command in the datalayer. Once that’s done, we can move to using a SQLcommand which is sending the SQL statement directly from the page. Once that’s done we move to this step, where we have moved the statment over to SQL Server as a stored procedure. That’s the end. Unless we really really really need some further efficiency or other problem, that’s the finished product as far as we will ever need it.
