DevCity.NET - http://devcity.net
ADO.NET for Beginners Part Three
http://devcity.net/Articles/301/1/article.aspx
David Jeavons
I have been programming database applications for almost 12 years starting out with VB3 and using every major version since then. I currently work for a retailer analysis company writing ASP.NET code for our ever evolving web site. I also enjoy helping other programmers with Visual Basic and .NET and am a moderator at vbCity. 
by David Jeavons
Published on 1/12/2007
 

The final part of this series builds on the lessons learned in the first two parts.   It will show you how to achieve the same functionality using an SQL Server database. The code provided will work for both SQL Server 2000 and 2005 and will demonstrate the use of stored procedures to retrieve and modify data in the Address Book database.


The Address Book database

Before we start, we will need to create a new database on our SQL Server to work with. The database is called AddressBook and will contain one table with the same basic structure as the Access database used in the previous two articles. At the end of this article is a download link for the AddressBook database file which is compatible with SQL Server 2005. If you download this file then you can simply attach this database to your instance of SQL Server using the SQL Server Management Studio. If you are using SQL Server 2000 or SQL Server Express then the following is the structure of the Contacts table that you will need to create:

 

Field Name Field Type Size
ContactID int  
Title Char 15
FirstName Char 60
LastName Char 60
AddressLine1 Char 75
AddressLine2 Char 75
AddressLine3 Char 75
Town Char 75
County Char 75
Postcode Char 10
Tel_Home Char 25
Tel_Work Char 25
Mobile Char 25
Fax Char 25
Email Char 100

 

The ContactID field should be set as the primary key and also have the Identity (Is Identity for SQL Server 2005) property set to Yes and the Identity Increment and Identity Seed values set to 1.   (They should be at these settings by default).

 

Next we will need to create some stored procedures that will be responsible for managing the database for us. There will be five stored procedures used in this example to retrieve, add, update and delete records from the Contacts table.

 

If you are using the downloaded database then the stored procedures have already been created, otherwise copy the following stored procedure creation statements and run them against the Address Book database. If you are using SQL Server 2000 then this can be done using the Query Analyzer tool, otherwise, use the Management Studio to execute these statements:

 

USE [AddressBook]

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[Contacts_AllContacts_Select]

As

SELECT ContactID, Title, FirstName, LastName, AddressLine1, AddressLine2,

AddressLine3, Town, County, Postcode, Tel_Home, Tel_Work, Mobile, Fax, Email

FROM Contacts

 

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[Contacts_Contact_Select]

@ContactID int

As

SELECT ContactID, Title, FirstName, LastName, AddressLine1, AddressLine2,

AddressLine3, Town, County, Postcode, Tel_Home, Tel_Work, Mobile, Fax, Email

FROM Contacts

WHERE Contacts.ContactID = @ContactID

 

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[Contacts_Delete]

@ContactID int

As

DELETE FROM Contacts WHERE ContactID=@ContactID

 

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[Contacts_Insert]

@Title Char(15),

@FirstName Char(60),

@LastName Char(60)

As

INSERT INTO Contacts (Title, FirstName, LastName)

VALUES

(@Title, @FirstName, @LastName)

 

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[Contacts_Update]

@ContactID int,

@Title Char(15),

@FirstName Char(60),

@LastName Char(60)

As

UPDATE Contacts Set Title=@Title, FirstName=@FirstName, LastName=@LastName

WHERE ContactID=@ContactID

 

Note that the Contacts_Update and Contacts_Insert stored procedures only modify the first three fields in the Contacts table. This is to fit in with the first two articles in only concerning itself with a few of the fields in order to keep this article simple and for you to gain more understanding by adding to these stored procedures if you wish to at a later time.

 

Now that we have the database and stored procedures ready, we can move on to modifying the code that we created in the previous two articles to make use of the SQL Server database. At this point, I highly recommend that you create a copy of this project so that you may compare the differences at a later time.

 

The Address Book Program

If we cast our minds back to part one of this series, you will remember that we discussed the various objects and the namespaces where they reside in order to work with databases. In particular, we studied the System.Data.OleDb namespace and the objects contained within to connect to a Microsoft Access database and manage the data. Now that we are moving on to Microsoft SQL Server we will want to utilise a different set of objects that reside in the System.Data.SqlClient namespace.  It is feasible to continue to use the OleDb objects but they are not optimized for use with Microsoft SQL Server whereas the SqlClient objects are specifically designed to work with Microsoft SQL Server and therefore you should use these to gain optimum performance.

 

Fortunately, the objects that we will be using have the same methods as their OleDb counterparts we have already used and therefore do not require much tweaking to change them over to the SQL Server equivalents and have a working program. This is what we will do now.

 

The first change we will make is to replace the Imports statement that we have used in both the main and ManageData forms to the following:

 

    1         Imports System.Data.SqlClient

 

As soon as you have done this, you should see a number of errors appear in the Error List view (if you haven’t got this view available, select the View menu followed by Error List). The errors are caused because we have removed the OleDb namespace and therefore, the compiler cannot find the location of all of the OleDb objects we have used. This is quite handy for us however as we can now use this Error List to determine what and where we need to make code changes.

 

Let’s start by changing all OleDbConnection objects to SqlConnection objects. This can be done with a simple find and replace by using the Find and Replace tool in Visual Studio 2005.   To access this, select Quick Replace from the Edit Menu and in the “Find What” field enter OleDbConnection and in the “Replace With” field enter SqlConnection. Also ensure that the “Look In” drop down list states Current Project and then press the “Replace All” button. Now we will do the same for the following:

 

Find What Replace With
OleDbDataAdapter SqlDataAdapter
OleDbCommand SqlCommand
OleDbDataReader SqlDataReader

Unfortunately, we cannot run the project just yet as we still have to modify the connection string for the SqlConnection objects in order to point to the new database on your instance of Microsoft SQL Server.  Now might be a good time to note that we really should have used a configuration file to store the connection string instead of littering it throughout the program as it would have made this change very simple.   Hindsight is a great thing. So, we will just have to get our hands dirty and find each routine that currently specifies a connection string (actually, I will list the routines below):

Form Routine
Main RetrieveContacts
Main btnDelete_Click
ManageData btnSave_Click
ManageData RetrieveContactDetails

The type of connection string that we will use to connect to Microsoft SQL Server depends on how your server is setup. The most basic connection string simply states the name of the server to connect to and which database to use along with the credentials required to access the server. Note that the connection string can state whether to use Windows Authentication or SQL Server Authentication (an example of both follows). For simplicity, I am assuming that you are using an instance of SQL Server that is installed on the same machine as Visual Studio and therefore is known as the Local instance. If this is not the case then you will need to modify the Server value in the following connection strings to point to the name of the machine where the Microsoft SQL Server resides.

Specifying user credentials (SQL Server Authentication):

Data Source=(Local);Initial Catalog=AddressBook;User=username;Password=password

 

Windows Authentication:

Data Source=(Local);Initial Catalog=AddressBook;Integrated Security=SSPI

 

For more examples of connection strings for Microsoft SQL Server please refer too: Carl Prothman's connection strings 

 

Once you know which connection string you require, you will need to modify each of the routines listed above to use the new connection string. Once this is done, you should be able to run the program in its current state and all should work as before.

 

Converting the Inline SQL Statements to Stored Procedures

Most of what we have done to this point is pretty self explanatory and doesn’t really impact much on the code that we currently have. However, there is one final step in converting this project and that is to change all of the current inline SQL statements that we currently use to calls to the Stored Procedures that we created earlier.

 

The first routine that we will change is the RetrieveContacts routine on the main form. This is the easiest of all of the changes as the stored procedure that returns the data from the Contacts table does not require any parameters to be passed, i.e. it does not utilise any WHERE clauses in its original SQL Statement.  The only change that you will need to make in this routine is to change the constructor for the SqlDataAdapter which currently looks like:

 

    1         Dim adapter As New SqlDataAdapter("SELECT * FROM Contacts", conn)

 

Instead of using the inline Select statement, we will now change this for the name of the Stored Procedure that is responsible for retrieving the data, namely Contacts_AllContacts_Select. This line of code should now look like:

 

    1         Dim adapter As New SqlDataAdapter("Contacts_AllContacts_Select", conn)

 

As no parameters are required for this stored procedure we can simply state the name of the stored procedure as the CommandText property of the SqlDataAdapter object. If you run the program now you will see that the DataGrid is populated with any contacts that are currently in the Contacts table.

 

The next routine to change in the main form is the click event of the delete button (btnDelete_Click). The code change here however will be a little different to that above as in order to delete a contact we need to specify the contact ID to be deleted. The current SQL statement does this by providing the Contact ID value as part of the WHERE clause, however we now want to pass this contact ID value as a parameter to the Stored Procedure that will delete the contact for us. In order to do this we need to change the routine a little. If you remove the code within the btnDelete_Click event and paste the following in:

 

    1         If dgContacts.CurrentRowIndex > -1 Then

    2 

    3             Dim connection As New SqlConnection("Data Source=(Local);Initial Catalog=AddressBook;Integrated Security=SSPI")

    4             Dim command As New SqlCommand("Contacts_Delete", connection)

    5 

    6             command.CommandType = CommandType.StoredProcedure

    7             command.Parameters.AddWithValue("@ContactID", CType(dgContacts.Item(dgContacts.CurrentRowIndex, 0), Int32))

    8 

    9             'Open the connection to the database and execute

   10             'the SQL Statement

   11             connection.Open()

   12             command.ExecuteNonQuery()

   13             connection.Close()

   14 

   15             'Update the DataGrid to show new changes

   16             '(if applicable)

   17             RetrieveContacts()

   18 

   19         Else

   20 

   21             MessageBox.Show("Please select a row for deleting.", "No Row Selected", MessageBoxButtons.OK, MessageBoxIcon.Information)

   22 

   23         End If

 

The main points of interest in this routine are lines 4 - 7.  The remainder work as before.

 

On line 4 we create a new command object and specify the name of the stored procedure to run and which connection object to use.

 

Line 6 notifies the command object that the CommandText (notably Contacts_Delete) refers to a stored procedure and not an SQL string.

 

Now for the important parameter; the Command object has a parameters collection which has methods for adding and removing parameters from this collection.

 

Line 7 uses the AddWithValue method to add a new parameter to the collection and the syntax specifies the name of the parameter and the value that you want to assign to that parameter. As you can see above we have stated that the parameter’s name is @ContactID and the value is the value of the first column of the selected row in the DataGrid.

 

There are other methods for adding a parameter to the Parameters collection, such as specifying the name, data type and value as follows:

 

    1             command.Parameters.Add("@ContactID", SqlDbType.Int).Value = CType(dgContacts.Item(dgContacts.CurrentRowIndex, 0), Int32)

 

But I find the AddWithValue method easier to work with, especially if you find that at a later date you change the definition of a column (data type or size). With the AddWithValue method you will not need to worry about modifying any parameter code.

 

If we now move onto the ManageData form we can modify the last two routines to make use of the stored procedures that Add and Update data in the Contacts table. The first routine we will change is the btnSave_Click routine. First, replace the existing code with the code below:

 

    1         Dim conn As New SqlConnection("Data Source=(Local);Initial Catalog=AddressBook;Integrated Security=SSPI")

    2         Dim command As New SqlCommand()

    3 

    4         command.Connection = conn

    5 

    6         command.Parameters.AddWithValue("@Title", txtTitle.Text)

    7         command.Parameters.AddWithValue("@FirstName", txtFirstName.Text)

    8         command.Parameters.AddWithValue("@LastName", txtLastName.Text)

    9 

   10         'Determine if we are adding a new record or editing an

   11         'existing record based on the value of the _contactID

   12         'variable

   13         If _contactID = 0 Then

   14             command.CommandText = "Contacts_Insert"

   15         Else

   16             command.CommandText = "Contacts_Update"

   17             command.Parameters.AddWithValue("@ContactID", _contactID)

   18         End If

   19 

   20         command.CommandType = CommandType.StoredProcedure

   21 

   22         'Open the connection to the database and execute the

   23         'SQL Statement

   24         conn.Open()

   25 

   26         'Dim command As New SqlCommand(sql, conn)

   27         command.ExecuteNonQuery()

   28 

   29         conn.Close()

   30 

   31         'Close the form.

   32         Me.Close()

 

The code above works similarly to the previous version we used. However, instead of creating two different SQL Statements based on whether we were updating an existing record or creating a new one, this code simply calls a different procedure. You will also note that we add the common parameters to the Command’s Parameter collection in lines 6 – 8 and only if we are updating an existing record do we add a further parameter on line 17.

 

The final routine to replace is the RetrieveContactDetails routine:

 

    1         'Retrieves a record from the Contacts table based

    2         'on the _contactID value.

    3         Dim conn As New SqlConnection("Data Source=(Local);Initial Catalog=AddressBook;Integrated Security=SSPI")

    4 

    5         Dim command As New SqlCommand("Contacts_Contact_Select", conn)

    6 

    7         command.CommandType = CommandType.StoredProcedure

    8         command.Parameters.AddWithValue("@ContactID", _contactID)

    9 

   10         'Open the connection to the database

   11         conn.Open()

   12 

   13         'Use the Command object's ExecuteReader method which

   14         'will return an SqlDataReader object that we can use

   15         'for forward only access through the returned data.

   16         Dim contactReader As SqlDataReader = command.ExecuteReader

   17 

   18         'There should only be one row returned due to the WHERE

   19         'clause retrieving a specific ContactID. So initiate the Read

   20         'method of the contactReader and read the field values:

   21         contactReader.Read()

   22         txtTitle.Text = contactReader("Title").ToString

   23         txtFirstName.Text = contactReader("FirstName").ToString

   24         txtLastName.Text = contactReader("LastName").ToString

   25 

   26         'Close the connection to the database

   27         conn.Close()

 

Again, this routine is very similar to the previous routine, the only real changes to this routine are found on lines 5 – 8 where the command is created and the @ContactID parameter is added to the parameters collection of the command object.

 

At this point, the program should now work exactly as it did before but now we are targeting a Microsoft SQL Server database and using Stored Procedures to manage the data in the Contacts table.

 

Summary

We have now finished converting our existing project to use a Microsoft SQL Server database.    I think you will agree that the difference between the Microsoft Access version and the new version is not that great, especially when you consider that we could have omitted the use of Stored Procedures and continued to use the inline SQL statements. However, the benefit of using Stored Procedures certainly makes this exercise worthwhile; it especially comes in handy when you need to modify some of your queries as you do not need to modify your code, only the Stored Procedures which saves you from having to recompile your programs.

 

So we are now at the end of this series of articles. I certainly hope that it has given you enough information to start working with databases in your programs and to explore other areas and techniques of ADO.NET that will aid you in your database work.