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:
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):
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
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.