3. Appendices

3.1. Building a Web Project with Visual Studio.NET on Windows XP Home Edition

Visual Studio.NET allows you to build different types of projects:

To build a web application project, you normally select the [ASP.NET Web Application] type. This type of project requires a local or remote IIS web server. If you are working on a Windows XP machine, this server does not exist and cannot be installed. Therefore, you cannot create an [ASP.NET Web Application] project.

You can work around this by accepting a few minor drawbacks. Simply:

use the Cassini web server instead of the IIS server. It is available for free on the Microsoft website.
use a [Class Library] project instead of the [ASP.NET Web Application] project

Let’s create a simple project to demonstrate how to do this.

Create a [Class Library] project

Delete [Class1.vb]

Add a new item to the project, of type [Text File], and name it [demo.aspx]:

The [demo.aspx] file is recognized as a web page, and a page editor is associated with it. This editor has two panels:
[Design] for building the page graphically
[HTML] to access the page’s HTML code

Select [View/Code] to display the VB code section of the page. Nothing happens. You cannot access the page’s VB code.

Go to the [HTML] pane and enter the code that will link the [demo.aspx] page to the [demo.aspx.vb] code:

Request to view the control code associated with the page via [View Code - F7]. You will see the [demo.aspx.vb] file:

The [demo.aspx] page is now recognized as an [.aspx] web page with an associated [.aspx.vb] file.

Return to the [Design] pane of [demo.aspx] and design the following page:

The page contains text and a server component of type [Label] with the identifier [lblHeure].

Switch to the [HTML] pane. There you will find the following code:

<%@ Page codebehind="demo.aspx.vb" inherits="demo.demo" autoeventwireup="false" Language="vb" %>
ASPX demo, it is
<asp:Label id="lblHeure" runat="server"></asp:Label>

This code is incomplete from an HTML syntax perspective. Let’s complete it:

<%@ Page codebehind="demo.aspx.vb" inherits="demo.demo" autoeventwireup="false" Language="vb" %>
<html>
    <head>
        <title>demo ASPX</title></head>
    <body>
        ASPX demo, it is
        <asp:Label id="lblHeure" runat="server"></asp:Label>
    </body>
</html>

Let’s go to the [demo.aspx.vb] file to write the control code that will set the time in the [lblHeure] component. We notice that this is not recognized by IntelliSense, the code completion tool.
Close [demo.aspx] and [demo.aspx.vb] after saving them, then reopen them. Go to the code in [demo.aspx.vb]. This time, the [lblHeure] component from [demo.aspx] is recognized by IntelliSense in the code [demo.aspx.vb]. Complete the code:

Build the project by selecting [Build/Build demo]. If the build is successful, the DLL is generated in the project’s [bin] folder:

We are ready for testing. We configure the Cassini server (see next paragraph) as follows:

The target of the shortcut to Cassini is set as follows:

"E:\Program Files\Microsoft ASP.NET Web Matrix\v0.6.812\WebServer.exe" /path:"D:\temp\07-04-05\demo" /vpath:"/demo"

`E:\..\WebServer.exe`	the path to the executable
`D:\temp\07-04-05\demo`	the path to the Visual Studio web project folder
`/demo`	the associated virtual path

Launch Cassini. Its icon appears in the taskbar. Right-click on it and select the [Show details] option to verify that the web server is configured correctly:

Using a browser, request the page we built by entering the URL [http://localhost/demo/demo.aspx]. We get the following result:

We have successfully created a web application in Visual Studio using:

a [class library] project
the Cassini web server

We now know how to build web applications on computers that do not have the IIS server, such as Windows XP Home Edition machines.

3.2. Where can you find the Cassini web server?

To work with Microsoft’s .NET platform, you can use the Cassini web server. It is available through a product called [WebMatrix], which is a free web development environment for .NET platforms available at the URL:

Follow the product installation steps carefully:

download and install the .NET platform (1.1 as of March 2004)
download and install WebMatrix
Download and install MSDE (Microsoft Data Engine), which is a limited version of SQL Server.

Once the installation is complete, the [WebMatrix] product is available in the installed programs:

The [ASP.NET] Web Matrix link launches the ASP.NET development IDE:

The [Class Browser] link launches a tool for exploring .NET classes:

To test the installation, let’s launch [WebMatrix]:

Upon initial startup, [WebMatrix] prompts you for the new project’s specifications. This is its default configuration. You can configure it so that this dialog box does not appear at startup. You can then access it via the [File/New File] option. [WebMatrix] allows you to create templates for various web applications. Above, we specified in (1) that we wanted to create an [ASP.NET Page] application, which is a web page. In (2), we specify the folder where this web page will be placed. In (3), we enter the page’s name. It must have the .aspx extension. Finally, in (4), we specify that we want to work with the VB.NET language; [WebMatrix] also supports the C# and J# languages. Once this is done, [WebMatrix] displays an editing page for the [demo1.aspx] file. We enter the following code there:

The [Design] tab allows you to "design" the web page you want to build. This works similarly to a Windows application development IDE.
The graphical design of the web page in [Design] will generate HTML code in the [HTML] tab
The web page may contain controls that generate events requiring a response, such as a button. These events will be handled by VB.NET code placed in the [Code] tab
Ultimately, the demo1.aspx file is a text file combining HTML code and VB.NET code, resulting from the graphical design created in [Design], the HTML code added manually in [HTML], and the VB.NET code placed in [Code]. The entire file is available in the [All] tab.
An experienced ASP.NET developer can build the demo1.aspx file directly using a text editor without the aid of any IDE.

Let’s select the [All] option. We can see that [WebMatrix] has already generated some code:

<%@ Page Language="VB" %>
<script runat="server">

    ' Insert page code here
    '

</script>
<html>
<head>
</head>
<body>
    <form runat="server">
        <!-- Insert content here -->
    </form>
</body>
</html>

We won't attempt to explain this code here. We'll transform it as follows:

<html>
<head>
    <title>ASP.NET Demo </title>
</head>
<body>
    It is <% =Date.Now.ToString("hh:mm:ss") %>
</body>
</html>

The code above is a mix of HTML and VB.NET code. It has been placed within the <% ... %> tags. To run this code, we use the [View/Start] option. [WebMatrix] then launches the Cassini web server if it is not already running

You can accept the default values offered in this dialog box and select the [Start] option. The web server is then active. [WebMatrix] will then launch the default browser on the machine it is running on and request the URL http://localhost:8080/demo1.aspx:

It is possible to use the Cassini server outside of [WebMatrix]. The server executable is located at <WebMatrix>\<version>\WebServer.exe, where <WebMatrix> is the [WebMatrix] installation directory and <version> is its version number:

Open a Command Prompt window and navigate to the Cassini server folder:

E:\Program Files\Microsoft ASP.NET Web Matrix\v0.6.812>dir
...
05/29/2003  11:00               53 248 WebServer.exe
...

Let's run [WebServer.exe] without any parameters:

E:\Program Files\Microsoft ASP.NET Web Matrix\v0.6.812>webserver

We get a help window:

The [WebServer] application, also known as the Cassini web server, accepts three parameters:

/port: port number of the web service. Can be any number. The default value is 80
/path: the physical path to a folder on the disk
/vpath: the virtual folder associated with the preceding physical folder. Note that the syntax is not /path=path but /vpath:path, contrary to what is stated in the [Example] in the help panel above.

Let’s place the file [demo1.aspx] in the following folder:

Let’s associate the virtual folder [/webmatrix] with the physical folder [d:\data\devel\webmatrix]. The web server could be started as follows:

E:\Program Files\Microsoft ASP.NET Web Matrix\v0.6.812>webserver /port:100 /path:"d:\data\devel\webmatrix" /vpath:"/webmatrix"

The Cassini server is now active, and its icon appears in the taskbar. If you double-click it:

You will see the server's startup settings. You also have the option to stop [Stop] or restart [Restart] the web server. If you click the [Root URL] link, the root of the server's web directory tree will open in a browser:

Follow the [demos] link:

then the [demo1.aspx] link:

We can see that if the physical folder P=[d:\data\devel\webmatrix] has been mapped to the virtual folder V=[/webmatrix] and the server is running on port 100, the web page [demo1.aspx], which is physically located in [P\demos], will be accessible locally via the URL [http://localhost:100/V/demos/demo1.aspx].

To avoid having to use a DOS window to launch the Cassini server, you can create a shortcut to the server executable with properties similar to the following:

`Target`	"C:\Program Files\Microsoft ASP.NET Web Matrix\v0.6.812\WebServer.exe" /port:80 /path:"D:\data\serge\work\2004-2005\aspnet\webarticles-010405\version3\web" /vpath:"/webarticles"
`Start in`	"C:\Program Files\Microsoft ASP.NET Web Matrix\v0.6.812"

3.3. Where can I find Spring?

The main Spring website is [http://www.springframework.org/]. This is the site for the Java version. The .NET version currently under development (April 2005) is available at [http://www.springframework.net/].

The download site is on [SourceForge]:

Once you have downloaded the zip file above, unzip it:

In this document, we have only used the contents of the [bin] folder:

In a Visual Studio project using Spring, you must always do two things:

place the files above in the project’s [bin] folder
add a reference to the [Spring.Core.dll] assembly to the project

3.4. Where can you find NUnit?

The main NUnit website is [http://www.nunit.org/]. The version available in April 2005 is 2.2.0:

Download this version and install it. The installation creates a folder containing the graphical testing interface:

The interesting part is in the [bin] folder:

The arrow above points to the graphical testing utility. The installation also added new items to the Visual Studio assembly repository, which we will explore now.

Let’s create the following Visual Studio project:

The class being tested is in [Person.vb]:

Public Class Person

    ' private fields
    Private _name As String
    Private _age As Integer

    ' default constructor
    Public Sub New()
    End Sub

    ' Properties associated with private fields
    Public Property name() As String
        Get
            Return _name
        End Get
        Set(ByVal Value As String)
            _name = Value
        End Set
    End Property

    Public Property age() As Integer
        Get
            Return _age
        End Get
        Set(ByVal Value As Integer)
            _age = Value
        End Set
    End Property

    ' identity string
    Public Overrides Function tostring() As String
        Return String.Format("[{0},{1}]", name, age)
    End Function

    ' init method
    Public Sub init()
        Console.WriteLine("Initializing person {0}", Me.ToString)
    End Sub

    ' close method
    Public Sub close()
        Console.WriteLine("destroy person {0}", Me.ToString)
    End Sub

End Class

The test class is in [NunitTestPersonne-1.vb]:

Imports System
Imports NUnit.Framework

<TestFixture()> _
 Public Class NunitTestPerson

    ' object under test
    Private person1 As Person

    <SetUp()> _
    Public Sub init()
        ' Create an instance of Person
        person1 = New Person
        ' log
        Console.WriteLine("setup test")
    End Sub

    <Test()> _
    Public Sub demo()
        ' screen log
        Console.WriteLine("start test")
        ' initialize person1
        With person1
            .name = "paul"
            .age = 10
        End With
        ' tests
        Assert.AreEqual("paul", person1.name)
        Assert.AreEqual(10, person1.age)
        ' screen log
        Console.WriteLine("end of test")
    End Sub

    <TearDown()> _
    Public Sub destroy()
        ' monitoring
        Console.WriteLine("teardown test")
    End Sub

End Class

Several things to note:

methods are assigned attributes such as <Setup()>, <TearDown()>, ...
for these attributes to be recognized, the following must be true:
- the project references the assembly [nunit.framework.dll]
- the test class imports the namespace [NUnit.Framework]

The reference is added by right-clicking on [References] in the Solution Explorer:

The assembly [nunit.framework.dll] should be in the list if the [NUnit] installation was successful. Simply double-click the assembly to add it to the project:

Once this is done, the test class [NunitTestPersonne] must import the [NUnit.Framework] namespace:

Imports NUnit.Framework

The attributes of the test class [NunitTestPersonne] should then be recognized.

The <Test()> attribute designates a method to be tested
The <Setup()> attribute designates the method to be executed before each method being tested
The <TearDown()> attribute designates the method to be executed after each method being tested
The Assert.AreEqual method allows you to test the equality of two entities. There are many other methods of the Assert.xx type.
The NUnit utility stops the execution of a tested method as soon as an [Assert] method fails and displays an error message. Otherwise, it displays a success message.

Let’s configure our project to generate a DLL:

The generated DLL will be named [nunit-demos-1.dll] and will be placed by default in the project’s [bin] folder. Let’s build our project. We get the following in the [bin] folder:

Now let's launch the NUnit graphical testing utility. Remember that it is located in <Nunit>\bin and is named [nunit-gui.exe]. <Nunit> refers to the [Nunit] installation folder. The following interface appears:

Let’s use the [File/Open] menu option to load the [nunit-demos-1.dll] DLL from our project:

[Nunit] can automatically detect the test classes contained in the loaded DLL. Here, it finds the [NunitTestPersonne] class. It then displays all the methods of the class that have the <Test()> attribute. The [Run] button allows you to run the tests on the selected object. If this is the [NunitTestPersonne] class, all displayed methods are tested. You can test a specific method by selecting it and clicking [Run]. Let’s run the class:

A successful test on a method is indicated by a green dot next to the method in the left window. A failed test is indicated by a red dot.

The [Console.Out] window on the right shows the screen output produced by the tested methods. Here, we wanted to follow the progress of a test:

setup test
test start
end of test
test teardown

Line 1 shows that the <Setup()> attribute method is executed before the test
Lines 2–3 are generated by the [demo] method being tested (see the code above)
Line 4 shows that the <TearDown()> attribute method is executed after the test

3.5. Where can I find the Firebird DBMS?

The main Firebird website is [http://firebird.sourceforge.net/]. The downloads page offers the following links (April 2005):

You will download the following items:

`firebird-win32`	the DBMS for Windows
`firebird-net-provider`	a class library for .NET applications that allows access to the DBMS without using an ODBC driver.
`firebird-ODBC-driver`	the Firebird ODBC driver

Install these components. The DBMS is installed in a folder with contents similar to the following:

The binaries are in the [bin] folder:

`fbguard.exe`	allows you to start/stop the DBMS
`isql.exe`	command-line client for managing databases

Note that by default, the DBMS administrator is named [SYSDBA] and the password is [masterkey]. Menus have been added to [Start]:

The [Firebird Guardian] option allows you to start/stop the DBMS. After startup, the DBMS icon remains in the Windows taskbar:

To create and manage Firebird databases using the command-line client [isql.exe], you must read the documentation included with the product in the [doc] folder. A faster way to work with Firebird is to use a graphical client. One such client is IB-Expert, described in the following section.

3.6. Where can I find IB- Expert?

The main Firebird website is [http://www.ibexpert.com/]. The downloads page offers the following links:

Select the free version [Personal Edition]. Once it has been downloaded and installed, you will have a folder similar to the following:

The executable file is [ibexpert.exe]. A shortcut is usually available in the [Start] menu:

Once launched, IBExpert displays the following window:

Use the [Database/Create Database] option to create a database:

`Server`	can be [local] or [remote]. Here, our server is on the same machine as [IBExpert]. So we choose [local]
`Database`	Use the [folder] button in the dropdown to select the database file. Firebird stores the entire database in a single file. This is one of its advantages. You can transfer the database from one computer to another by simply copying the file. The [.gdb] suffix is added automatically.
`Username`	SYSDBA is the default administrator for current Firebird distributions
`Password`	masterkey is the password for the SYSDBA administrator in current Firebird distributions
`Dialect`	the SQL dialect to use
`Register Database`	If this box is checked, IBExpert will display a link to the database after it has been created

If, when clicking the [OK] button to create the database, you see the following warning:

it means you haven't started Firebird. Start it. A new window will appear:

Server version

[IBExpert] can manage various DBMSs derived from Interbase. Select the version of Firebird you have installed

Once this new window is confirmed by clicking [Register], you will see the following result:

To access the created database, simply double-click its link. IBExpert then displays a tree view providing access to the database properties:

Let’s create a table. Right-click on [Tables] and select the [New Table] option. The table properties definition window appears:

Let’s start by naming the table [ARTICLES] using the input field [1]:

Use the input field [2] to define a primary key [ID]:

A field is made a primary key by double-clicking the [PK] (Primary Key) field. Let’s add fields using the [3] button:

Until we have "compiled" our definition, the table is not created. Use the [Compile] button above to finalize the table definition. IBExpert prepares the SQL queries to generate the table and asks for confirmation:

Interestingly, IBExpert displays the SQL queries it has executed. This allows you to learn both the SQL language and any proprietary SQL dialect that may be used. The [Commit] button validates the current transaction, while [Rollback] cancels it. Here, we accept it by clicking [Commit]. Once this is done, IBExpert adds the created table to our database tree:

By double-clicking on the table, we can access its properties:

The [Constraints] panel allows us to add new integrity constraints to the table. Let’s open it:

We see the primary key constraint we created. We can add other constraints:

foreign keys [Foreign Keys]
field integrity constraints [Checks]
field uniqueness constraints [Uniques]

Note that:

the fields [ID, PRICE, CURRENTSTOCK, MINIMUMSTOCK] must be >0
the [NAME] field must be non-empty and unique

Open the [Checks] panel and right-click in the constraint definition area to add a new constraint:

Let’s define the desired constraints:

Note above that the constraint [NAME<>''] uses two single quotes, not double quotes. Compile these constraints using the [Compile] button above:

Once again, IBExpert demonstrates its user-friendliness by displaying the SQL queries it has executed. Now let’s move to the [Constraints/Unique] panel to specify that the name must be unique:

Let’s define the constraint:

Let’s compile it. Once that’s done, open the [DDL] panel for the [ARTICLES] table:

This panel displays the SQL code for generating the table with all its constraints. You can save this code in a script to run it later:

SET SQL DIALECT 3;
SET NAMES NONE;
CREATE TABLE ARTICLES (
    ID            INTEGER NOT NULL,
    NAME           VARCHAR(20) NOT NULL,
    PRICE          DOUBLE PRECISION NOT NULL,
    CURRENTSTOCK   INTEGER NOT NULL,
    MINIMUM_STOCK  INTEGER NOT NULL
);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_ID check (ID>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_PRICE check (PRICE>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_CURRENT_STOCK check (CURRENT_STOCK > 0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_STOCKMINIMUM check (STOCKMINIMUM>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_NAME check (NAME<>'');
ALTER TABLE ARTICLES ADD CONSTRAINT UNQ_NAME UNIQUE (NAME);
ALTER TABLE ARTICLES ADD CONSTRAINT PK_ARTICLES PRIMARY KEY (ID);

It is now time to enter data into the [ARTICLES] table. To do this, use its [Data] panel:

Data is entered by double-clicking on the input fields of each row in the table. A new row is added using the [+] button, and a row is deleted using the [-] button. These operations are performed within a transaction that is committed using the [Commit Transaction] button. Without this commit, the data will be lost.

IBExpert allows you to run SQL queries via the [Tools/SQL Editor] option or [F12]. This gives you access to an advanced SQL query editor where you can run queries. They are saved, so you can revisit a query you’ve already run. Here’s an example:

We execute the SQL query using the [Execute] button above. We get the following result:

We’ll stop our demonstrations here. The IBExpert-Firebird combination proves to be excellent for learning databases.

3.7. Installing and using an ODBC driver for [Firebird]

3.8. Installing the driver

The [firebird-odbc-provider] link on the [Firebird] downloads page (section 3.5) provides access to an ODBC driver. Once installed, it appears in the list of installed ODBC drivers.

3.9. Create an ODBC data source

Launch the tool [Start -> Settings -> Configuration Tool -> Administrative Tools -> ODBC Data Sources]:

The following window appears:

Click [Add] to add a new system data source (in the [System DSN] pane) that we will associate with the Firebird database we created in the previous section:

First, we need to specify the ODBC driver to use. Above, we select the driver for Firebird and then click [Finish]. The Firebird ODBC driver wizard then takes over:

We fill in the various fields:

`DSN [demo-odbc-firebird]`	the DSN name of the ODBC source—can be anything
`Database [D:\..\DBARTICLES.GDB]`	the name of the Firebird database to use—use [Browse] to select the corresponding .gbd file
`Database Account [SYSDBA]`	the username to use to connect to the database
`Password [masterkey]`	the password associated with this username

The [Test connection] button allows you to verify the validity of the information you have entered. Before using it, start the [Firebird] DBMS:

Confirm the ODBC wizard by clicking [OK] as many times as necessary

3.10. Test the ODBC source

There are various ways to verify that an ODBC source is working properly. Here, we will use Excel:

Use the [Data -> External Data -> Create Query] option above. This opens the first window of the data source definition wizard. The [Databases] pane lists the ODBC sources currently defined on the machine:

Select the ODBC source [odbc-firebird-articles] that we just created and proceed to the next step by clicking [OK]:

This window lists the tables and columns available in the ODBC source. We’ll select the entire table:

Proceed to the next step by clicking [Next]:

This step allows us to filter the data. Here, we won’t filter anything and will proceed to the next step:

This step allows us to sort the data. We won’t do that and will move on to the next step:

The last step asks what we want to do with the data. Here, we export it to Excel:

Here, Excel asks where we want to place the retrieved data. We place it in the active sheet starting from cell A1. The data is then retrieved in the Excel sheet:

There are other ways to test the validity of an ODBC source. For example, you can use the free OpenOffice suite available at [http://www.openoffice.org]. Here is an example using OpenOffice:

An icon on the left side of the OpenOffice window provides access to data sources. The interface then changes to display a data source management area:

One data source is predefined: the [Bibliography] source. Right-clicking on the data sources area allows you to create a new one using the [Manage Data Sources] option:

A [Data Source Management] wizard allows you to create data sources. Right-clicking on the data source area allows you to create a new one using the [New Data Source] option:

`Name [odbc-firebird-articles]`	Any name. Here we have used the name of the ODBC source
`DB Type [odbc]`	OpenOffice supports various database types via JDBC, ODBC, or directly (MySQL, Dbase, etc.). For our example, select ODBC
`Data Source URL`	The button to the right of the input field gives us access to the list of ODBC sources on the machine. We select the source [odbc-firebird-articles]

We move to the [ODBC] panel to define the user under whose credentials the connection will be made:

Username [sysdba]

The owner of the ODBC source

Go to the [Tables] panel. You will be prompted for the password. Here, it is [masterkey]:

Click [OK]. The list of tables in the ODBC source is then displayed:

You can select the tables to be displayed in the [OpenOffice] document. Here, we select the [ARTICLES] table and click [OK]. The data source definition is complete. It then appears in the list of data sources for the active document:

You can drag the [ARTICLES] table from above into the [OpenOffice] document using the mouse:

3.11. Connection string for a Firebird ODBC source

Launch Visual Studio and open the Server Explorer [View/Server Explorer]:

Right-click on [Data Connection] and select the [Add Connection] option:

In the [Provider] panel, specify that you want to use an ODBC source (see above), then switch to the [Connection] panel:

`Use data source name [demo-odbc-firebird]`	Select the ODBC source from the dropdown menu. The one you just created should appear. If necessary, use [Refresh] to refresh the list of ODBC sources.
`User name [SYSDBA]`	username to use to connect to the database
`Password [masterkey]`	The password associated with this username

Here again, a [Test Connection] button allows you to verify the validity of the information:

Confirm the wizard by clicking [OK]. The data source then appears in the [Server Explorer] window in Visual Studio:

By double-clicking the [ARTICLES] table, you can access the table's data:

If we right-click on the [Firebird Server D:\temp\... ] link and select the [Properties] option, we can access the connection properties:

The [ConnectString] is an important property to know because the .NET code needs it to open a connection to the database. Here, the connection string is:

Provider=MSDASQL.1;Persist Security Info=False;User ID=SYSDBA;Data Source=demo-odbc-firebird;Extended Properties="DSN=demo-odbc-firebird;Driver=Firebird/InterBase(r) driver;Dbname=D:\temp\07-04-05\firebird\DBARTICLES.GDB;CHARSET=NONE;UID=SYSDBA"

Many elements of this connection string have default values. The following connection string will suffice:

"DSN=demo-odbc-firebird;UID=SYSDBA;PASSWORD=masterkey"

This concludes our overview of the [Firebird] ODBC driver.

3.12. Where can you find the MSDE ?

MSDE is the free version of Microsoft's SQL Server database management system. It can be found at the URL [http://www.microsoft.com/sql/msde/downloads/download.asp]:

Download the installation file, then install the DBMS by double-clicking the downloaded executable. A window will ask for the installation folder. The title is misleading. This is a temporary folder that can be deleted later:

Carefully read the [ReadmeMSDE2000A.htm] file. The installer is [setup.exe] above. It runs from the command line so that you can pass parameters to it. The main ones are as follows:

`Parameter`	Description
`SAPWD="StrongPassword"`	Specifies a strong password to assign to the administrator login "sa".
`INSTANCENAME="InstanceName"`	Defines the instance name. If INSTANCENAME is not specified, the installer installs a default instance.

Other parameters often used to customize an installation are:

`Parameter`	Description
`DISABLENETWORKPROTOCOLS=n`	Specifies whether the instance will accept network connections from applications running on other computers. By default, or if you specify DISABLENETWORKPROTOCOLS=1, the installer configures the instance to reject network connections. Specify DISABLENETWORKPROTOCOLS=0 to enable network connections.
`SECURITYMODE=SQL`	Specifies that the instance should be installed in mixed mode, meaning that the instance supports both Windows authentication and SQL authentication for connections
`DATADIR="data_folder_path"`	Specifies the folder where the installer installs the system databases, error logs, and installation scripts. The value specified for data_folder_path must end with a backslash (\). For a default instance, the installer appends MSSQL\ to the specified value. For a named instance, the installer appends MSSQL$InstanceName\, where InstanceName is the value specified via the INSTANCENAME parameter. The installer creates three folders at the specified location: a Data folder, a Log folder, and a Script folder.
`TARGETDIR="executable_folder_path"`	Specifies the folder in which the installer installs the MSDE 2000 executable files. The value specified for executable_folder_path must end with a backslash (\). For a default instance, the installer appends MSSQL\Binn to the specified value. For a named instance, the installer appends MSSQL$InstanceName\Binn, where InstanceName is the value specified via the INSTANCENAME parameter.

After reading the installation recommendations above, navigate to the folder where the installation files were extracted and enter the following DOS command (using the DBMS without a network):

dos>setup INSTANCENAME="MSDE140405" SECURITYMODE=SQL SAPWD="azerty"

INSTANCENAME="MSDE140405" - this will be the name of our MSDE instance. You can install multiple instances.
SECURITYMODE=SQL - the DBMS will run in mixed authentication mode. This allows you to connect to MSDE in two ways:
- with a Windows administrator account
- with an MSDE account—a username and password are then required. This is the mode to use in a program that connects to a database.
SAPWD="azerty" - this will be the password for the DBMS user [sa]. The user [sa] has administrative rights on the DBMS.

To use the DBMS on a network, you would issue the following command:

dos>setup INSTANCENAME="MSDE140405" SECURITYMODE=SQL SAPWD="azerty" DISABLENETWORKPROTOCOLS=0

The installation program is minimalistic and completes without any output... However, you can verify that the DBMS has been installed via the [Start Menu -> Control Panel -> Add or Remove Programs] option:

The installation is normally performed in C:\Program Files\Microsoft SQL Server\MSSQL$instanceName:

In the [LOG] folder within the installation directory, you will find the log file for the DBMS installation phase. It contains an important piece of information: the name of the MSDE instance:

2005-04-14 08:14:29.37 spid4     The server name is “PORTABLE1_TAHE\MSDE140405”.

It is important to know this name because all DBMS clients will need it. If these logs are missing, you can find the name of an MSDE server, which is [windows_machine\MSDE_instance_name]. The machine name is available in several places. For example:

Right-click [My Computer] on the desktop, select [Properties], then the [Computer Name] tab:

We still don’t know how to start the MSDE server. A shortcut has normally been placed in [Start/Startup].

If you look at the properties of this shortcut, you will find that the target is as follows:

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmangr.exe" /n

In the [C:\Program Files\Microsoft SQL Server] folder, there are subfolders:

MSSQL$MSDE140405 is the folder for the MSDE instance we just installed.
MSSQL is the folder for a previous MSDE instance. Since it has no name, we call it the default instance.
The [80] folder is a shared folder for the various installed MSDE instances. The [sqlmangr.exe] target of the shortcut that launches an MSDE instance is located in the [80\Tools\Binn] folder.

Let’s launch MSDE via the shortcut in [Start -> Programs -> Startup]. Almost nothing happens except that an icon appears in the taskbar:

Double-click this icon:

The MSDE server shown here is the default server

[PORTABLE1_TAHE] on the machine. Remember that

the MSDE server we installed is named

[PORTABLE1_TAHE\MSDE140405]. We change the

server name in the appropriate field:

If everything goes well, the [MSDE140405] instance should be launched:

We can perform an initial check. In the same folder as [sqlmangr.exe], there is a console client [osql.exe] that allows you to connect to an MSDE server and execute SQL commands. During installation, we assigned the password [azerty] to the administrator [sa] of our MSDE server. Using the console client, we will connect to the newly installed server. If we run the command [osql -?], the list of possible parameters is displayed:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn>osql -?
usage: osql
  [-U login ID]
  [-P password]
  [-S server]
  [-H hostname]
  [-E trusted connection]
  [-d use database name]
  [-l connection timeout]
  [-t query timeout]
  [-h headers]
  [-s column separator]
  [-w column width]
  [-a packet size]
  [-e echo output]
  [-I Enable tagged identifiers]
  [-L server list]
  [-c command end]           [-D ODBC DSN name]
  [-q "cmdline query"]
  [-Q "cmdline query" and exit]
  [-n remove numbering]
  [-m error level]
  [-r send messages to stderr]
  [-V severity level]
  [-i input file]
  [-o output file]
  [-p print statistics]  [-b abort command batch after error]

  [-X[1] disables commands [and exits with a warning]]
  [-O Use Old ISQL behavior disables the following]
      <EOF> batch processing of statements
      Automatic scaling of the console width
      Wide messages
      Default error level of -1 instead of 1
  [-? syntax description]

Start the server [MSDE140405] as described above, then in a command prompt window, use [osql] to connect to the server [portable1_tahe\msde140405] under the identity [sa, azerty]:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn>OSQL.EXE -U sa -S portable1_tahe\msde140405 -P azerty
1>

The prompt [1>] indicates that [osql] is waiting for a command. We are successfully connected. To use [osql] correctly, you should consult the MSDE documentation. It is available in various formats (PDF, HTML Help, etc.). This documentation is quite extensive. Generally, it is preferable to use a graphical client to work with an MSDE database. This is what is suggested a little further on. To exit [osql], use the [exit] command:

1> exit

We will now see how to create databases on the newly installed MSDE server. Before that, we will briefly introduce a tool called [MSDE Manager] that allows you to change the authentication mode of an MSDE server. In fact, if you install such a server using the default installation options, the server’s authentication mode is set to [Windows authentication]. This type of authentication only allows users identified on the Windows machine (possibly via a domain). For a VB.NET program that wants to connect to a database to use its content, this mode is impractical. It is even worse for Java applications that access the DBMS via a JDBC driver. In such cases, mixed authentication is preferred, as it accepts username/password pairs defined in the DBMS in addition to the previous authentication method. The [MSDE Manager] tool allows you to perform this operation.

3.13. Where can I find MSDE Manager?

[MSDE Manager] is an administration tool for the MSDE DBMS. It can be found at the URL [http://www.valesoftware.com/].

We download the free version by following the link above:

The trial version has a short lifespan. This is fine since we will only use it for a single, specific task. Download and install the product. A shortcut will be placed on the desktop. Use it to launch MSDE Manager. After the initial windows, you will arrive at this one:

Start the MSDE140405 server
You must be logged in to the Windows machine as an administrator
Right-click the [SQL Server Group] link and select the [New SQL Server Registration] option:

The following properties page appears:

`Server Name`	portable1_tahe\msde140405 - name of the MSDE instance you want to connect to
`Connection`	Windows Authentication - this mode is always available and allows a Windows machine administrator to connect to the MSDE server
`Server Group`	Select the only server group listed [SQL Server Group]

Once [OK] is clicked, the property tree for the MSDE140405 server is displayed:

We could start creating databases. We won’t do that because we’ll be using another product, a clone of the IBExpert product we’ve already covered. We’ll simply change the MSDE authentication mode. Right-click on the MSDE140405 server above and select the [Design] option:

We get the following information window:

The [General] tab provides information about the MSDE server you are connected to. The [Security] page is the one we are interested in:

Here, you must ensure that the MSDE authentication mode is set to [SQL Server and Windows]. This will allow you to connect to MSDE in two ways:

with a Windows administrator account—which is what was done here
with an MSDE account—a username and password are then required. This is the mode to use in a program that connects to a DBMS database.

We confirm this selection and exit MSDE Manager. We will no longer need it. To create MSDE databases, we will use another tool: EMS MS SQL Manager.

3.14. Where can you find EMS MS SQL Manager?

EMS MS SQL Manager is a graphical tool for working with the Microsoft SQL Server DBMS and, therefore, MSDE. It is very similar to the IB-Expert tool described earlier. It is available at the URL [http://sqlmanager.net/] (April 2005):

The site offers administration tools for many DBMSs. Follow the [MS SQL Manager] link:

Above, we select the lightweight version of the product. Download and install it. You will have a folder similar to the following:

The executable is [MsManager.exe]. A shortcut is normally available in the [Start] menu:

Once launched, MS SQL Manager displays the following window:

Let’s start by registering the MSDE server we want to work with using the [Database/Register Host] option:

Comments:

Step 1 - As mentioned, MSDE supports two authentication modes: Windows and SQL Server. In [Windows] mode, the Windows machine’s accounts are used. In [SQL Server] mode, the DBMS accounts are used. [SQL Server] can operate in [Windows] mode or in mixed [Windows, SQL Server] mode. The [Windows] authentication mode is always available. The mixed authentication mode, however, is not always active. We have seen how to enable it using MSDE Manager. Above, the connection was made using an administrator account.
Step 2 - Once authentication is successful, the default MSDE databases are displayed. Above, all of them were selected.

Comments:

Step 3: Administration options for the selected databases can be chosen. Here, the default options were retained.
Step 4: We register the MSDE server using the [Register] button

The MSDE server then appears in the database explorer:

Let’s use the [Database/Create Database] option to create a database:

Step 2:

When this information page appears, the [dbarticles] database has been created. You can verify this by clicking the [Test Connect] button. In the [Database alias] field, you can enter whatever you like. Here, we have entered:

the database name
the name of the MSDE server on which it is located
the user [admarticles] who will own this database and their password [mdparticles]. This user has not yet been created but will be soon.

Step 3:

Click the [Register] button to register the new database in [MS SQL Server]. After registration, the [admarticles] database appears in the list of databases. Double-clicking on it displays its property tree.

Let’s create a new login that will be the administrator of the [admarticles] database.

Select the [Tools/Login Manager] option:

We can see that two logins are already defined:
- [BUILTIN\Administrators]: This login uses Windows authentication. It represents the administrators of the Windows machine on which the MSDE server is located
- sa: This login uses SQL authentication. By default, it is the administrator of the MSDE server. Note that here, as configured during the installation of the MSDE DBMS, its password is [azerty].

Right-click on the logins area and add a new login:

A form appears where we define the characteristics of the new login:

Login Name: admarticles
Password: mdparticles

Once the [OK] button is clicked, MS Manager displays the SQL queries it will execute:

The SQL language shown above is Transact-SQL, the SQL language used by MSDE. We execute this code by clicking [OK]

The new login is added to the list of logins:

In the [dbarticles] database properties window, right-click on [users] to create a user with permissions on the [dbarticles] database:

The following window then appears:

In the [Login] drop-down list, you’ll see the list of existing logins. Select the [admarticles] login.
In [Name], enter a username. Multiple users can be associated with the same login. Also, in MSDE, creating a user first requires creating a login. The [User] panel now looks like this:

Now let’s move on to the [Member Of] panel, which will allow us to define our user’s permissions:

I am not a regular MSDE user and I am unsure of the exact meaning of each of the roles listed in the left pane. The [db_owner] role is tempting (owner = owner). We will therefore select it for our user [admarticles]:

We confirm our selections by clicking the [Compile] button above. The SQL queries executed are as follows:

We compile them by clicking [OK]. We now have a user for the [dbarticles] database:

Now let’s create a table. Right-click on [Tables] and select the [New Table] option. This opens the table properties definition window:

Let’s start by naming the table [ARTICLES] using the [Table Name] input field. Next, move to the [Fields] panel:

define the following fields:

Until we have "compiled" our definition, the table is not created. Use the [Compile] button above to finalize the table definition. [MS SQL Manager] prepares the SQL queries to generate the table and asks for confirmation:

Interestingly, [MS SQL Manager] displays the SQL queries it has executed. This allows you to learn the Transact-SQL language. The [Commit] button validates the current transaction, while [Rollback] cancels it. Here, we accept it by clicking [Commit]. Once this is done, [MS SQL Manager] adds the created table to our database tree:

By double-clicking on the table, we can access its properties:

The [Checks] panel allows us to add new integrity constraints to the table. For the [ARTICLES] table, we will create the following constraints:

the fields [ID, PRICE, CURRENTSTOCK, MINIMUMSTOCK] must be >=0
the [NAME] field must not be empty

In the [Checks] panel, right-click on the blank area to add a new constraint [New check]:

The constraint editing sheet looks like this:

Name: name of the constraint

Table: table on which the constraint applies

Definition: constraint expression

The constraint is compiled using the [Compile] button above.

Once again, [MS SQL Manager] displays the executed SQL commands:

We validate them using the [Commit] button (not shown). If we return to the [Checks] panel for the [ARTICLES] table, the new constraint appears:

We define the other constraints in the same way to finally obtain the following list:

Once this is done, open the [DDL] panel for the [ARTICLES] table:

This panel displays the Transact-SQL code for creating the table with all its constraints. You can save this code to a script to run it later:

CREATE TABLE [ARTICLES] (
  [id] int NOT NULL,
  [name] varchar(20) COLLATE French_CI_AS NOT NULL,
  [price] float(53) NOT NULL,
  [currentStock] int NOT NULL,
  [minStock] int NOT NULL,
  CONSTRAINT [ARTICLES_uq] UNIQUE ([name]),
  PRIMARY KEY ([id]),
  CONSTRAINT [ARTICLES_ck_id] CHECK ([id] > 0),
  CONSTRAINT [ARTICLES_ck_name] CHECK ([name] <> ''),
  CONSTRAINT [ARTICLES_ck_price] CHECK ([price] >= 0),
  CONSTRAINT [ARTICLES_ck_current_stock] CHECK ([current_stock] >= 0),
  CONSTRAINT [ARTICLES_ck_minimum_stock] CHECK ([minimum_stock] >= 0)
)
ON [PRIMARY]
GO

It is now time to enter some data into the [ARTICLES] table. To do this, use its [Data] panel:

The [+] button adds a row, and the [-] button removes one. Data is entered by simply typing into the input fields of each row in the table. A row is validated using the [Post Edit] button below:

Let’s create two items:

[MS SQL Manager] allows you to run SQL queries via the [Tools/Show SQL Editor] option or [F12]. This gives you access to an advanced SQL query editor where you can run queries. Queries are saved, so you can revisit a query you’ve already run. Here’s an example:

Execute the SQL query using the [Execute] button above. You will get the following result:

We'll stop our demonstrations here. The [MS SQL Manager - MSDE] combination, much like the [IBExpert - Firebird] combination, is also excellent for learning about databases.

3.15. Creating an ODBC Source [MSDE]

The ODBC driver for SQL Server is normally installed by default on Windows machines.

Launch the tool [Start -> Settings -> Configuration Tools -> Administrative Tools -> ODBC Data Sources]:

The following window appears:

Click [Add] to add a new system data source (in the [System DSN] pane) that we will associate with the MSDE database we created in the previous section:

First, we need to specify the ODBC driver to use. Above, we select the driver for [SQL Server] and then click [Finish]. The [SQL Server] ODBC Driver Wizard then takes over:

We fill in the various fields:

`Name [odbc-msde-articles]`	the name of the ODBC source—can be anything
`Description`	can be anything
`SQLMap Server portable_tahe\msde140405`	Name of the MSDE server containing the ODBC source data

Click [Next] to provide additional information:

Fill in the various fields:

`SQL Server Authentication`	Specify that you will connect to the ODBC data source using a username registered on the MSDE server
`Login ID [admarticles]`	User login
`Password [mdparticles]`	User password

Note that this is the first time we are using the user (admarticles, mdparticles) created in a previous section. Click [Next] again to proceed to the next screen:

We fill in the various fields:

Change database...

We select the database [dbarticles] as the default database for the user [admarticles]

Click [Next] to proceed to the next screen:

We accept the default values and click [Finish]. A summary of the characteristics of the ODBC source that will be created is provided:

The [Test Data Source] button allows us to verify the validity of our information. Verify that MSDE is running, then test the connection:

We are now certain that the [admarticles, mdparticles] pair is recognized.

For further testing, the reader can follow the procedure explained in section 3.10.

3.16. Connection string to an MSDE database

Launch Visual Studio and open the Server Explorer [View/Server Explorer]:

Right-click on [Data Connection] and select the [Add Connection] option:

In the [Provider] pane, specify that you want to use a SQL Server source, then switch to the [Connection] pane. Note that here we are not using an ODBC driver.

`Server name [portable1_tahe\msde140405]`	Name of the MSDE server you are connecting to
`Username [admarticles]`	username to use to connect to the database
`Password [mdparticles]`	the password associated with this username
`Database [dbarticles]`	The database you want to work with

A [Test Connection] button allows you to verify the validity of the information:

Confirm the wizard by clicking [OK]. Curiously, a new window asks for the connection details:

enter them again and click [OK]. The data source then appears in the [Server Explorer] window in Visual Studio:

by double-clicking on the [ARTICLES] table, you can access the table’s data:

If we right-click the link [portable1_tahe\msde140405.dbarticles.admarticles] in the [Server Explorer] pane and select the [Properties] option, we can view the connection properties:

The [ConnectString] is an important property to know because .NET code needs it to open a connection to the database. Here, the connection string is:

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=admarticles;Initial Catalog=dbarticles;Data Source=portable1_tahe\msde140405;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PORTABLE1_TAHE;Use Encryption for Data=False;Tag with column collation when possible=False

Many elements of this connection string have default values. The following connection string will suffice:

"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=admarticles;Initial Catalog=dbarticles;Data Source=portable1_tahe\msde140405;PASSWORD=mdparticles"