Migrating the Employee Database to SQL Server 2000

By default, the Employee management system uses a Microsoft Access 2002 database. The following steps demonstrate how to migrate the EmployeeDatabase.mdb Microsoft Access 2002 database to Microsoft SQL Server 2000. Perform these steps if you would prefer to (or have no choice but to) run the EMS sample application using SQL Server 2000 as the back-end database. In particular, if you would like to run the example presented in Chapter 6, which shows how to call a SQL Server stored procedure named IsValidUser, complete the steps shown in the “Add the IsValidUser stored procedure to the database” section. Finally, you’ll need to update the EMS code, as shown later in this section, to have the EMS sample application use the SQL Server database.

1. In the Windows Start menu, select Enterprise Manager from the Microsoft SQL Server group. The SQL Server Enterprise Manager will appear.

2. Connect to a SQL server database, and expand the Databases folder in the left-hand pane, right-click Databases, and select Import Data from All Tasks as shown in the following illustration.

   

3. You’re greeted by the Data Transformation Services Import/Export Wizard as shown in the next illustration.

   

4. Click Next, and select Microsoft Access as the data source from the Data Source drop-down list.

5. Click the Browse button for the File Name field, and select the EmployeeDatabase.mdb file installed with the sample files for this book. If you accepted the defaults during installation of the samples, the database will be in C:\Microsoft Press\VBNETSEC\EmployeeDatabase.mdb.

6. Click Next, and select <New> from the database drop-down list (near the bottom of the dialog box). A dialog box requesting the name of the SQL Server database will be shown.

7. Enter EmployeeDatabase as the name of the database, and click OK.

8. Click Next, and select the Copy Table(s) and View(s) From The Source Database option.

9. Click Next, and click the Select All button to select all available database tables—Employee, EmployeeRole, Role, and AuditTrail.

10. Click Next twice to get to the finish page, and click Finish to migrate the Access 2002 EMS database to SQL Server 2000.

11. You should receive a message box telling you that three tables were successfully copied from Microsoft Access to Microsoft SQL Server.

12. Click OK, click Done, and you’re indeed done!

13. Press F5 to refresh the list of available databases, and you should see the EmployeeDatabase in the list of databases.

Add the IsValidUser stored procedure to the database

The following steps show how to add the IsValidUser stored procedure (presented in Chapter 6) to the SQL Server EMS EmployeeDatabase.

1. Run SQL Server Enterprise Manager (as shown in the previous steps).

2. Expand the EmployeeDatabase folder.

3. Right-click Stored Procedures, and select New Stored Procedure.

4. Replace the sample stored procedure text, which is

   CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS

   with the following text:

   CREATE PROCEDURE IsValidUser 
    @username VarChar(50)
   AS
    SELECT *
    FROM employee
    WHERE @username = UserName
   GO

5. Click OK, and you should see IsValidUser displayed in the stored procedures list.

6. Exit SQL Server Enterprise Manager.

Modify the EMS to use the SQL Server database

You can change the EMS sample code to use the recently created SQL Server database (created using the steps shown previously) by following these steps.

1. Run Visual Basic .NET, and open EMS.sln.

2. Open MainModule.vb.

3. Change the following line of code from

   Public G_CONNECTIONSTRING As String = ACCESS_CONNECTIONSTRING

   to

   Public G_CONNECTIONSTRING As String = SQLSERVER_CONNECTIONSTRING