Using SQL Server 2005 Express from Visual Basic 6

Scott Swigart
Swigart Consulting LLC

May 2006

Applies to:
   Microsoft Visual Basic 6
   Microsoft Visual Basic 2005
   Microsoft SQL Server 2005
   Microsoft SQL Server 2005 Express
   Microsoft Desktop Storage Engine

Summary: Microsoft Desktop Storage Engine (MSDE) has been replaced with a new product called SQL Server 2005 Express, which addresses several limitations of MSDE and can be used with Visual Basic 6. (8 printed pages)

Click here to download the code sample for this article.

Contents

Overview
Getting Started with SQL Server 2005 Express
Connecting from Visual Basic 6
Multiple Recordsets, One Connection
But Wait, There's More
Security
Conclusion
Resources

Overview

Visual Basic developers have long used Access databases as a solution for local storage of application data. Access made it very simple to ship a database with your application, and connecting to the database was no more complicated than putting the path to the database in the connection string. For more complex scenarios, developers could scale up to the Microsoft Desktop Storage Engine (MSDE). This provided developers with SQL Server functionality and reliability, but at the expense of ease of use. With MSDE, you had to attach databases before they could be connected to. When the application shut down, the database file was still in use by the MSDE engine, making uninstall and reinstall more complicated. MSDE also did not provide any administrative interface, so any administration of the database had to be done through command line tools. In addition, MSDE was difficult to deploy with an application.

As part of the effort to develop Visual Studio 2005 and SQL Server 2005, Microsoft is addressing all these limitations of MDSE, replacing it with a new product called SQL Server 2005 Express.

While information about SQL Server 2005 Express has so far been targeted at .NET Framework developers, this article will show how SQL Server 2005 Express is a boon for Visual Basic 6 development as well. SQL Server 2005 Express will provide the developer power of SQL Server, but also the ease of use of access, by letting you connect directly to a database file on disk. Best of all, it's completely free, and can be redistributed with your application.

Getting Started with SQL Server 2005 Express

If you want to work with SQL Server 2005 Express, the first step is to download and install it. SQL Server 2005 Express is part of the family of Express products that can be found at http://lab.msdn.microsoft.com/express/. Prior to installing SQL Server 2005 Express, you will also need to download and install the Microsoft .NET Framework 2.0.

Unlike MSDE, SQL Express also includes an administrative tool known as Express Manager, which lets you create databases and execute queries (see Figure 1).

Figure 1. Using Express Manager to create a sample database

Connecting from Visual Basic 6

To start working with Express, a simple user interface was created that uses an MSHFlexGrid to display data (see Figure 2).

Figure 2. Using grids to display data

Then, a few simple lines of code (see Listing 1) can be written that populate the grid from a SQL Server database, which is connected to by specifying the path to its .mdf file.

Listing 1. Using Visual Basic 6 to retrieve information from SQL Server 2005 Express 2005 by file path

    Dim cn As ADODB.Connection
    Set cn = New Connection
    cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "AttachDBFileName=" & App.Path & "\northwnd.mdf;Data Source=server1\sqlexpress"
    cn.Open
    
    Dim rs As Recordset
    Set rs = New Recordset
    rs.Open "Select * from orders", cn
    Set MSHFlexGrid1.DataSource = rs

This is revolutionary functionality. This means that you can simply ship your SQL Server database as an .mdf file with your application. You don't need to do anything to install the database. You just put the path to the database in the connection string, and you can select, insert, update, delete, call stored procedures, and access any other database functionality. This is also great functionality for Web applications, which can now just keep a copy of the .mdf file in the Web application directory, and use it as easily as an Access database.

In Listing 1, you can see that the path to the database is specified through the AttachDBFileName value in the connection string. You'll also notice that this connection string is not using the typical SQL OLE-DB provider. When you install SQL Server 2005 Express, it installs a new OLE-DB provider, called the SQL Native Client, that gives you access to some of the new SQL Server 2005 functionality—specifically, the ability to attach to a database by file name. This is specified in the connection string with the statement Provider=SQLNCLI. Once the connection string is specified, you can retrieve recordsets, perform updates, and do any other database operations, exactly as you would normally do with SQL Server.

Multiple Recordsets, One Connection

Another key feature of SQL Server 2005, and SQL Server 2005 Express, is the ability to have multiple result sets off of a single connection. You are probably familiar with the situation in which you are looping through a recordset, and, based on the information in the recordset, you need to open another connection to the database and retrieve additional information. Because of key enhancements made to SQL Server, you no longer need to open multiple connections to the same database. Multiple Active Result Sets (MARS) lets you work with multiple recordsets through a single connection. Consider the code in Listing 2.

Listing 2. Using multiple active recordsets from Visual Basic 6

    '
    ' Connect to the database
    '
    Dim cn As ADODB.Connection
    Set cn = New Connection
    cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "AttachDBFileName=" & App.Path & "\northwnd.mdf;Data Source=server1\sqlexpress"
    cn.Open
    
    '
    ' Get all the orders
    '
    Dim rsOrders As Recordset
    Set rsOrders = New Recordset
    rsOrders.Open "Select * from orders", cn
    Do While Not rsOrders.EOF
        '
        ' If the order matches some custom business logic then get the details for
        ' that order, without opening a new connection.
        '
        If SomeBusinessLogic(rsOrders("CustomerID")) Then
            Dim rsDetails As Recordset
            Set rsDetails = New Recordset
            '
            ' Open a new recordset using the same connection. Normally it's not
            ' possible to have two recordsets simultaniously using the same
            ' connection, but MARS makes this possible
            '
            rsDetails.Open "Select sum(quantity * unitprice) as total " & _
                "from [order details] " & _
                "where OrderID=" & rsOrders("OrderID"), _
                cn
            grandTotal = grandTotal + rsDetails("total")
        End If
        rsOrders.MoveNext
    Loop
    
    lblTotalOrders = grandTotal

In this case, a recordset is retrieved that contains all the orders (rsOrders). Based on some custom business logic, the details for certain orders need to be retrieved. You can see that an additional recordset known as rsDetails is opened to retrieve the details, using the same connection as rsOrders, while rsOrders is still open and being used. In the past, this wouldn't have been possible. This allows you to create a single global connection object for your application, and to use this throughout your application for all access to a given database.

But Wait, There's More

SQL Server 2005 Express also includes a number of innovations that you won't find in SQL Server 2000. A good list of the T-SQL enhancements can be found in "T-SQL Enhancements in SQL Server 2005," but this article will explore two that are likely to be of great use to most developers.

VARCHAR(MAX)

In previous versions of SQL Server, you would use a VARCHAR column to store text. The problem was that a VARCHAR column was limited to 8000 characters. If you needed to store more than that, you had to use the TEXT type, and TEXT was significantly harder to work with. SQL Server 2005 (and SQL Server 2005 Express) support a new data type known as VARCHAR(MAX). This type can be treated exactly like a VARCHAR, and it can store 2 GB of data per row.

For binary data, SQL Server 2005 Express also includes a VARBINARY(MAX) type, which replaces the IMAGE type.

Common Table Expressions

It's common, when working with data, to need to create intermediate results. These can show up in the form of temporary tables, or views. Often, this intermediate information is needed only while a given calculation is being done, and it can then be discarded.

With SQL Server 2005 Express, a new mechanism for these kinds of scenarios is the common table expression (CTE). Simply put, a CTE lets you treat the results of a select statement as a table. To look at a concrete example, consider the CTE in Listing 3, which returns the region with the most territories.

Listing 3. Common table expression (CTE) with SQL Server 2005

WITH CountTerritories(RegionID, TerritoryCount) AS
(
    SELECT 
        RegionID, 
        count(*) AS TerritoryCount
    FROM Territories 
    GROUP BY RegionID
)
SELECT * FROM CountTerritories WHERE TerritoryCount = 
( 
    SELECT max(TerritoryCount) FROM CountTerritories 
)

Here, CountTerritories is created as a sort of temporary view that's used just for this single select statement. Once created, it can be queried against in the subsequent SELECT statement, as though it were a table.

Common Language Runtime Integration

The details of this are beyond the scope of this article, but it's also worth noting that with SQL Server 2005, you can build DLLs that run inside of SQL Server. This lets you implement your triggers, stored procedures, user-defined functions, and so, using languages such as Visual Basic .NET or Visual C# .NET. For more information, see "Using CLR Integration in SQL Server 2005."

Security

A final consideration for SQL Server 2005 Express is security. SQL Server 2005 Express is designed to be an application database, not a server database. The typical usage is to provide storage of application data on the desktop or laptop of the user. For this reason, by default, you can not make a remote connection to SQL Server 2005 Express. This, by itself, makes SQL Server 2005 Express safe from a whole host of attacks, such as SQL Slammer types of viruses.

Conclusion

When SQL Server 2005 Express ships, it will be the best database you can use for many scenarios. While you can't use it for production code today, now is the time to start ensuring that you will be able to use this excellent, free database when it ships. If you're currently using MSDE, migrating to SQL Server 2005 Express will be trivial. You can simply take the existing .mdf file for your MSDE database, ship the file with your application, and connect to the file by putting the file path in the connection string. It's likely that no other changes will be needed. However, you may want to make some changes (for example, using MARS instead of multiple connections) to streamline your code.

If you are currently using Access, I won't say that you have to move to SQL Server 2005 Express, just because. Here, you have to analyze a number of factors, including:

• How much data are your users storing in the database?
• Would you benefit from stored procedures, user-defined functions, triggers, and so on?
• Would SQL Server 2005 Express offer a performance improvement?
• Do you need greater security for the data than Access provides?
• Does the access data get corrupted, so that you see more reliable storage as significant?

I'm all for keeping a tool that's doing the job, but if you've felt that you're bumping up against the limits of Access, and felt that MSDE would cause too much pain for the gain, SQL Server 2005 Express may be the answer you're looking for.

Resources

• Visual Studio 2005 Express Products
• Microsoft .NET Framework 2.0
• T-SQL Enhancements in SQL Server 2005
• Using CLR Integration in SQL Server 2005
• Sqlexpress's Weblog
• VS Data Team's Weblog
• SQL Server Developer Center
• Visual Basic Developer Center

About the author

Scott Swigart spends his time consulting, authoring, and speaking about converging and emerging technologies. With development experience going back over 15 years, and by staying in constant contact with future software development technologies, Scott is able to help organizations get the most out of today's technology while preparing to leverage the technology of tomorrow. Scott is also the author of several .NET books, a certified Microsoft trainer (MCT) and developer (MCSD), and a Microsoft MVP. Feel free to contact the Scott at scott@swigartconsulting.com, or check out his latest musings at blog.swigartconsulting.com.

© Microsoft Corporation. All rights reserved.