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