SQL Server 2005 Express Edition – Part 10

Working with User Instances using Visual Basic 2005 Express Edition

In the previous installment of our series dedicated to SQL Server 2005
Express Edition, we have demonstrated unique behavior of user instances by
observing operational characteristics of the SSEUtil administrative utility.
While our presentation was sufficient to ensure a basic understanding of their
features as well as explain their management methodology, it did not offer the
level of detail that is required when implementing applications with embedded
databases that support XCopy deployment (and depend on user instance
technology). In this article, we will address this shortcoming by providing
more insight into the design and distribution of applications that belong to
this category.

Throughout this article, we will be relying on functionality available in
Visual Basic 2005 Express Edition, which is a freely downloadable, extremely
easy to use development environment, available from the Visual Studio
Express Developer Center
section of the MSDN Web site (you will also find
there a link to its Service Pack 1). This product is of particular interest to
us since it was designed specifically to interact with SQL Server 2005 Express
Edition (as a matter of fact, if its instance is not detected on the local
system during initial discovery, you will be given an option to include it in
the installation process), with its databases operating in the user instance
mode.

Once you complete the download of a small bootstrap program (VBSetup.exe at
2.95MB in size), the installation wizard will start by displaying the Welcome
page that gives you a brief description of the software (presenting it as a
friendly tool for building a variety of Windows-based applications) and
requests your permission to submit to Microsoft anonymous feedback documenting
any issues you might encounter during the setup process. After you accept the
End User License Agreement, you will also have an option of incorporating in
your installation MSDN Express Library (keep in mind, however, that this
triggers a sizeable, 248 MB download, so you might want to postpone it unless
you actually have near term plans to take advantage of it) as well as (as mentioned
earlier) SQL Server 2005 Express Edition (providing that it is not already
present). Following the next step, at which you designate the location of a target
folder, Visual Basic 2005 Express Edition files (total of 36 MB) are downloaded
and installed on the local computer (note that you should also apply Service
Pack 1 and all relevant Windows Update patches in order to improve security and
stability of your system). Remember to register the product within the 30-day
period (which can be done via the Register Product… option on the Help menu).
Registration, in addition to legitimizing your right to free usage of the
software, also gives you a number of side benefits, including extra development
components, images, and icons that enhance the appearance or functionality of
your code, as well as access to numerous educational resources.

While it is fairly straightforward to leverage Visual Basic 2005 Express
Edition to create a new database, define its structure, and populate it (prior
to developing an application that manipulates its content in a desired manner),
for the sake of time, we will use one of the predefined database samples
available on the CodePlex
Web site. To minimize duration of the download, we will choose the lightweight
edition of AdventureWorks database published as a Windows Installer executable
at about 2MB in size (ensure that you select the SQL Server 2005 SP2-level
version). Once you execute it, you will be prompted to provide an arbitrary
location where the MDF and LDF files should be extracted. At this point, we are
ready to implement our sample application. Start by launching Microsoft Visual
Basic 2005 Express Edition, initiate a new project (using the New Project…
option in the File menu) based on the Windows Application template and assign
to it a descriptive name. Next, in the Solution Explorer window, right-click on
the icon representing the project and choose Add -> Existing Item from the
context sensitive menu. By pointing to the earlier extracted AdventureWorksLT_Data.mdf
file in the Add Existing Item dialog box, you will automatically trigger Data
Source Configuration Wizard. On its "Choose Your Database Objects"
page, expand the Tables or Views node and select one of the available entries
(we will work with vProductAndDescription view), assign an arbitrary dataset
name or accept the default one (in our case set to value
AdventureWorksLT_DataDataSet) and click on the Finish command button to
finalize our choice. This should result in both AdventureWorksLT_Data.mdf and a
definition of the newly created data set (in the form of .xsd XML schema
definition file) appearing in the list of project components in the Solution
Explorer window. You will also find the dataset with the underlying view and
its columns in the Data Sources window.

Drag the vProductAndDescription entry from the Data Sources window onto the
default Form1 that was automatically created when your Windows Application
template-based project was initiated. This will automatically result in the creation
of the DataGridView Task, which gives you a tabular view of the underlying
data. Adjust its size so all columns are visible and use the handle appearing
in the upper right corner to display the task menu, from where it is possible
to make further modifications. Available options allow you to add new and
remove or edit existing columns, "Enable Adding", "Enable
Editing", or "Enable Deleting" of data, as well as "Enable
Column Reordering" (your choice would depend on end-user requirements).
This step completes development of our very rudimentary application, which uses
the DataGridView task bound to its form to provide access to data presented by the
vProductAndDescription view in the manner you just configured. To ensure that
your effort will be properly protected, save the project to a folder of your
choosing. Among the files stored there, you will find AdventureWorksLT_Data.mdf
and AdventureWorksLT_Data_log.ldf files.

Even prior to launching the application, you have the option of previewing
data exposed by the DataGridView (and the vProductAndDescription view). Note
that when you invoke this option (from the menu of the DataGridView Task or
vProductAndDescription entry in the Data Sources window), the target database
will automatically load within a user instance that uses your local SQL Server
2005 Express Edition installation as its parent. This behavior follows the
procedure we have described earlier,
which relies on appropriate format of the connection string that dictates how
database connectivity is established. In our case, the string is contained in
the app.config file (which resides in the same folder as the project files) and
is enclosed within the <connectionStrings>
section, which takes the following format (note the User Instance=True part):

<connectionStrings>
<add name="ProjectName.My.MySettings.AdventureWorksLT_DataConnectionString"
connectionString="Data Source=.SQLEXPRESS; AttachDbFilename=|DataDirectory|
AdventureWorksLT_Data.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

where ProjectName
is the name you assigned to the current Visual Basic 2005 Express Edition
project, SQLEXPRESS is the
name of the parent instance of SQL Server 2005 Express Edition that hosts the
user instance, and AdventureWorksLT_Data.mdf
is the name of the database used by our application that gets autoattached to
the user instance. As expected, launching the application from within Visual
Basic 2005 Express Edition (which you can accomplish by pressing the F5 key,
choosing Start Debugging from its Debug menu, or pressing the green arrow in
the toolbar) will have the same effect as far as user instance-specific
behavior is concerned.

You can easily verify that the new user instance and the target database are
loaded by employing methods described in our previous article. As before (when
testing these characteristics with SSEUtil), once you launch Windows Task
Manager, you should notice another sqlsrvr.exe process running in the security
context of the currently logged on user (the same one that invoked the Visual
Basic 2005 Express Edition and created the project we just described). To get
more detailed information about the underlying database engine and its
components, you need to first obtain its full named pipe. This (as we explained
in our previous article) can be obtained by running SSEUtil -childlist from the Command
Prompt or by querying dynamic management view sys.dm_os_child_instances of its
parent via T-SQL. Equipped with this information, you can establish a
connection to the instance using SQL Server Management Studio Express, manage
its configuration, and access its data via a standard graphical interface.

In the next article of this series, we will take a closer look at software
deployment options available in Visual Basic 2005 Express Edition, focusing on
the capabilities specific to embedded database applications intended for SQL
Server 2005 Express Edition users.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles