Introduction
This 3 article series will cover inserting data into SQL Server, without the help of Visual Studio’s drag and drop controls and configuration wizards. A special emphasis will be placed on its application within a tiered environment.
Part 1 – Part 2 – Part 3
Part 3 – Executing the Insert
Now that we’ve covered creating a stored procedure and creating a connection to the database, we’re about to get to the most important part of this article – performing the actual insert. I’ll be discussing ADO.NET in general, as well as some ways you can take what you’ve learned in this article and expand on it in a much larger environment.
ADO.NET Objects
For the purpose of this article, I’ll be using a simple SqlCommand object to perform the insert, but I wanted to take a minute and talk about the SqlDataAdapter and the SqlCommand object and how they are related. Typically, if you’re used to doing things through the Designer in Studio, you are probably using SqlDataAdapters. I’ve found that I very rarely use SqlDataAdapters, for a couple of reasons. To start, although it has improved significantly in .NET 2.0, the DataAdapter requires a lot of overhead and can be significantly slower than using a simple SqlCommand object. Also, why would I create a SqlDataAdapter when all I need is its SelectCommand or InsertCommand? They’re just simple SqlCommand objects. When I’m creating an application that needs to run quickly, or a method that is going to be called repeatedly, I try to make it as lean as possible.
The SqlDataAdapter definitely has its place though. If I need several records in memory at the same time, or need to loop through data, it’s invaluable. I also tend to use it when I need to pass a sortable object to my UI tier. I find it much easier to pass a Typed Dataset instead of writing a ton of custom code. It is also helps when reading data in an XML form.
Now that we’ve covered a bit about the DataAdapter, let’s cover the SqlCommand object in depth.
The SqlCommand Object
As I mentioned above, the SqlCommand object is the basis of the SqlDataAdapter. Any interaction with a database takes place through a Command object. Now, let’s cover a few of the basic properties on the SqlCommand object.
- CommandText – Specifies the command to execute
- CommandType – Specifies the type of command in the CommandText property – StoredProcedure or Text
- CommandTimeout – Allows you to specify how long it will wait for your command to finish.
You’ll find that the CommandType property is one that you will set frequently as StoredProcedure isn’t the default value. You will see this later in the article.
Of the functions that you will see, the Execute methods are the most frequently used.
- ExecuteNonQuery – Use this method when you’re not retrieving records from the database. This is commonly used for running Inserts, Updates and Deletes.
- ExecuteReader – This provides you with a SqlDataReader, which you’ll use when your procedure is returning records.
- ExecuteScalar – Use this when your stored procedure will be returning you a single value.
- ExecuteXmlReader – Use this when you’ll be retrieving data in the form of XML or need to export in an XML form.
Executing the Stored Procedure
Let’s get down to some code. Here, you’ll see that we’ve created a SqlCommand object, passed it the SqlConnection that we created in Part 2, the name of our stored procedure. We’ve also indicated that the command is a StoredProcedure, not straight SQL.
Dim cInsert As New SqlCommand("insert_customer", scInsert)
cInsert.CommandType = CommandType.StoredProcedure
From here, we need to feed our values to the stored procedure. We will do this through the Parameters collection. Below, you will find two of our input parameters, as well as the creation of our Output parameter. I’ve dealt with the parameters in two ways, and for good reason. I only use the input parameters once, so there’s no need to take up space in my code if I don’t need to access them again. That’s where the AddWithValue method comes in handy. The output parameter will need to be accessed later, so I declare the parameter first. I also have to change the Direction on the parameter, since this value will be returned, not sent to the stored procedure.
cInsert.Parameters.AddWithValue("@CompanyName", CompanyName)
cInsert.Parameters.AddWithValue("@ContactName", ContactName)
Dim spPrimaryKey As New SqlParameter("@Key", SqlDbType.Int)
spPrimaryKey.Direction = ParameterDirection.Output
cInsert.Parameters.Add(spPrimaryKey)
Now that we’ve set up our parameters, we’re ready to execute our command, right? No – we need to open our connection first. Since we’re using the SqlCommand object directly, we’re not dealing with the ADO.NET disconnected model anymore. We have to explicitly open and close our connection. Once we’ve opened our connection, we can now run our ExecuteNonQuery method to run the insert. Once the procedure has been executed, you can use the Value property of the Primary Key parameter to retrieve the key of the inserted Customer record. In the example below, I’ve done a simple trace, but the typical approach would be to return the value to the calling method. Also, since connections to databases frequently throw exceptions, I’ve wrapped the whole execute in a Try Catch block.
Try
scInsert.Open()
cInsert.ExecuteNonQuery()
Trace.WriteLine(spPrimaryKey.Value.ToString())
Catch ex As Exception
'Include exception handling here
Finally
scInsert.Close()
End Try
Tiered Development
I’ve taken a very simplistic approach in these articles, covering the main concepts. In an N-Tier system, much of this should be broken up into separate functions to assist in reusability. All of the code that I’ve included here would be within the Data tier, insulating your Business tier from any database access. I typically create functions to create Connections, Commands and DataAdapters within a module in the Data tier since these are functions that will be called repeatedly. The insert commands that you’ve seen above are typically found in an Insert method that returns a Primary Key if necessary.
Where do I go from here?
Hopefully you’ve seen that interacting with databases without the help of the Visual Studio Designer isn’t that difficult. Learning how things work at a lower layer is important in becoming an efficient programmer, and is absolutely necessary when you want to logically separate application tiers. This should be a good starting point for anyone interested in starting an N-Tier application and how data access is accomplished. For more information on building N-Tier applications, I suggest this Microsoft whitepaper.