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:

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.