Express Essentials: Connecting Visual Studio 2005 to SQL Server Express

SQL Server Express can be a back-end database not only for purchased programs, such as Microsoft Office, but also for custom applications that you build. In "Using SQL Server Express with Remote Applications" (www.sqlmag.com/articles/index.cfm?articleid=97200 ), I explained that to connect a networked client to SQL Server Express, you need to enable networked connections on the SQL Server Express system and install the SQL Server client libraries on the networked client system. There are a few choices of library for networked clients, but the best one is the small-footprint SQL Native Client that was introduced with SQL Server 2005.

When you're developing custom applications for SQL Server Express, you use either one of the Microsoft Visual Studio 2005 Express editions or the full-featured Visual Studio 2005 Professional Edition. Corporate and professional developers tend to use Visual Studio Pro. However, because SQL Server Express is a free product, many part-time developers instead choose a version of Visual Studio Express. So let's look at how you can connect Visual Basic (VB) 2005 Express Edition to SQL Server 2005 Express. (You can find out more about and download all the Visual Studio Express products at msdn2.microsoft.com/en-us/express/default.aspx .)

After installing SQL Server Express, Visual Studio Express, and any prerequisites, you need to start VB Express and start a new project. For example purposes, I'll connect a Windows application to SQL Server Express. After starting VB Express, select Windows Application from the list of new project templates and give the project a name--I'll use MyVBDB--and click OK to create a new VB project and open a blank Forms designer.

To add a connection to SQL Server Express, select the Data, Add New Data Source menu option; this action starts the Data Source Configuration Wizard and displays the Choose a Data Source Type dialog. Select Database and click Next to display the Choose Your Data Connection dialog. If you've never connected VB Express to SQL Server Express before, your list of available connections will be blank. Click the New Connection button, then navigate to the directory in which your SQL Server .mdf data files are stored--by default, the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData directory. Choose the .mdf file that you want to use. For instance, to connect to the sample Northwind database, select NORTHWIND.MDF and click OK, then Next.

At this point you have a connection to the SQL Server Express database. In the next edition of Express Essentials, I'll show you how to use that connection to link objects on your VB form with tables in the SQL Server Express database.