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
section, which takes the following format (note the User
<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>
is the name you assigned to the current Visual Basic 2005 Express Edition
SQLEXPRESS is the
name of the parent instance of SQL Server 2005 Express Edition that hosts the
user instance, and
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.