Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  Data Programming  »  ADO.NET  »  ADO.NET for Beginners Part Three
ADO.NET for Beginners Part Three
by David Jeavons | Published  01/12/2007 | ADO.NET | Rating:
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. 

View all articles by David Jeavons...
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.

Sponsored Links