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.