Page 241 - Open Soource Technologies 304.indd

Unit 10: Database Fundamentals and Connectivity Option

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 we can simply ship SQL Server database as
an .mdf file with application. We don’t need to do anything to install the database. we just put
the path to the database in the connection string, and we 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, we can see that the path to the database is specified through the AttachDBFileName
value in the connection string. We’ll also notice that this connection string is not using the typical
SQL OLE-DB provider. When we install SQL Server 2005 Express, it installs a new OLE-DB
provider, called the SQL Native Client, that gives we 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, we can retrieve recordsets, perform updates, and do any other database operations,
exactly as we would normally do with SQL Server.

Get Started with SQL Server 2005 Express.

10.5.4 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. We are probably familiar with the situation in
which we are looping through a recordset, and, based on the information in the recordset, we
need to open another connection to the database and retrieve additional information. Because of
key enhancements made to SQL Server, we no longer need to open multiple connections to the
same database. Multiple Active Result Sets (MARS) lets we work with multiple recordsets
through a single connection. Consider the code in Listing 2.

LOVELY PROFESSIONAL UNIVERSITY 235