Create a Database during the installation of a .NET application - Version 1

Introduction

It is a common requirement to create a database during the installation of a .NET application. I have used SQL-DMO and Custom Action in .NET Set Up project to achieve this. This article will show you how to create a database, table, stored procedure, Primary Key etc. Though the table here is simple, you may create as many tables and stored procedures as you wish, in the same fashion.

Using the demo application

The demo application is provided with the required tool tips which will help you to use the application.

Using the source code

Some important functions are discussed below:

Add reference to SQL-DMO

You can do this by right clicking the project in Solution Explorer, then selecting 'Add Reference', COM components and the latest version of "Microsoft SQLDMO Object Library".

Create application and server objects in the declaration section

// Create an SQLDMO application 
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();          
// Create an Server, which resembles to your actual server
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();  
// Create Database
SQLDMO.Database nDatabase = new SQLDMO.Database();
// Create Data Files
SQLDMO.DBFile nDBFileData = new SQLDMO.DBFile();
// Create Log Files
SQLDMO.LogFile nLogFile = new SQLDMO.LogFile();

Listing the available SQL Servers in network

This function fillCmbServerList() lists all the available servers in the network. This function will not work for standalone machines:

private void fillCmbServerList()
{
   try
    {
        // Create SQL Servers Collection
        SQLDMO.NameList sqlServers = 
                      sqlApp.ListAvailableSQLServers(); 
        // Navigate through collection, one by one
        for(int i=0;i<sqlServers.Count;i++) 
        { 
            object srv = sqlServers.Item(i + 1); 
            if(srv != null) 
            { 
                this.cmbServList.Items.Add(srv);                         
            } 
        } 
        if(this.cmbServList.Items.Count > 0) 
            this.cmbServList.SelectedIndex = 0; 
        else 
            this.cmbServList.Text = 
               "<No available SQL Servers>"; 
    }
   catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Listing the available databases on the server

This code block btnData_Click lists all the available databases on a particular server. This can be useful in a LAN environment when you want to connect to an existing database instead of installing a new one:

private void btnData_Click(object sender, 
                              System.EventArgs e)
{
  try
   {
     /* Change the cursor to hour glass shape  */
 
     System.Windows.Forms.Cursor.Current=
            System.Windows.Forms.Cursors.AppStarting;

     if(rbLocal.Checked==true)
         srv.Connect(this.txtServName.Text.Trim(),
             this.txtUserName.Text,this.txtPass.Text);            
     else
         srv.Connect(this.cmbServList.Text.Trim(),
             this.txtUserName.Text,this.txtPass.Text);        
     // Navigate through each database in the 
     // server and add it to combo box

     foreach(SQLDMO.Database db in srv.Databases) 
     { 
         if(db.Name!=null) 
         this.cmbDataName.Items.Add(db.Name); 
     }

     if(this.cmbDataName.Items.Count!=0)
     cmbDataName.SelectedIndex=0;

     /* Change the cursor to default shape  */          
     System.Windows.Forms.Cursor.Current = 
           System.Windows.Forms.Cursors.Default;
   }
  catch(Exception ex)
   {
     MessageBox.Show(ex.Message);
   }
  finally
   {
     srv.DisConnect();
   }
}

Create a database on the server

This function createDB() will create a database and the associated files on a specified server:

// This function creates Database
private void createDB()
{
 strDatabaseName = txtDBName.Text.ToString();
 if (strDatabaseName == "")
 {
    MessageBox.Show("Enter the Name");
 } 
 try
 {
   // Assign a name to database
   nDatabase.Name = strDatabaseName;
   // Assign a name to datafile
   nDBFileData.Name = strDatabaseName;
   nDBFileData.PhysicalName = srv.Registry.SQLDataRoot + 
                   "\\DATA\\" + strDatabaseName + "_Data.mdf";
   nDBFileData.PrimaryFile = true;
   nDBFileData.Size = 2;

   nDBFileData.FileGrowthType = 
                    SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
   nDBFileData.FileGrowth = 1;

   //Add the DBFile object
   nDatabase.FileGroups.Item("PRIMARY").DBFiles.Add(nDBFileData);

   // Assign name to Log files
   nLogFile.Name = strDatabaseName + "Log";
   nLogFile.PhysicalName = srv.Registry.SQLDataRoot + 
                       "\\DATA\\" + strDatabaseName + "_Log.ldf";
   nLogFile.Size = 2;
   nDatabase.TransactionLog.LogFiles.Add(nLogFile);

   srv.Databases.Add(nDatabase);
 }
 catch(Exception ex)
 {
    MessageBox.Show(ex.Message);
    return;
 }
}

Create tables in the database

We will discuss two methods of creating tables:

This function tblEmployees() will create a table in the database. It will also create a primary key on one column and assign the default value to a column.

// This function creates a Table
private void tblEmployees()
{
    try
    {
        // Create a new Table
        SQLDMO.Table table = new SQLDMO.Table();
    
        // Give Name to the Table
        table.Name="Employees";
                    
        // Create Columns for tables
        // Column 1
        // Create new column
        SQLDMO.Column Col1 = new SQLDMO.Column();
        // Give name to the column
        Col1.Name="EmpNo";
        // Assign datatype to the column
        Col1.Datatype="int";
        // Mention whether NULL values are allowed or not
        Col1.AllowNulls=false;
    
        // Column 2
        SQLDMO.Column Col2 = new SQLDMO.Column();
        Col2.Name="Name";
        Col2.Datatype="varchar";
        // Decide the length of varchar datatype
        Col2.Length=50;
        Col2.AllowNulls=false;
    
        // Column 3
        SQLDMO.Column Col3 = new SQLDMO.Column();
        Col3.Name="Surname";
        Col3.Datatype="varchar";
        Col3.Length=50;
        Col3.AllowNulls=true;
    
        // Column 4
        SQLDMO.Column Col4 = new SQLDMO.Column();
        Col4.Name="isPermanent";
        Col4.Datatype="char";
        Col4.Length=10;
        // Assign default value to the column
        Col4.DRIDefault.Text=strYes; 
        Col4.AllowNulls=true;                
    
    
        // Add Columns to the table
        table.Columns.Add(Col1);
        table.Columns.Add(Col2);
        table.Columns.Add(Col3);
        table.Columns.Add(Col4);
                    
    
        // Create PRIMARY KEY
        SQLDMO.Key PK = new SQLDMO.Key();                
        PK.Clustered=true;
        PK.Type= 
           SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
        // Add Primary Key to 'EmpNo' column
        PK.KeyColumns.Add("EmpNo");
    
    
        // Add primary key to table
        table.Keys.Add(PK);
    
        // Add table to Database
        nDatabase.Tables.Add(table);

   }
   catch(Exception ex)
   {
       MessageBox.Show(ex.Message);
   }
}

Here, we will create a table from a script using the stored procedure SP_Students. We will use the ExecuteImmediate method of the database to execute the stored procedure. After successful creation of the table we will remove the stored procedure from the database. This code is written in the Install button's click event. The code is as follows:
```
// Execute the stored procedure to create table
    nDatabase.ExecuteImmediate("InsStudents",0,0);
/* Remove the stored procedure from database.
 The index starts from 1.*/
    nDatabase.StoredProcedures.Remove(2,"");
```

Create stored procedures in the database

This function SP_Employees() creates stored procedures in the database:

// This function creates a Stored Procedure
private void SP_Employees()
{
    try
    {
         // Create a Stored Procedure
         SQLDMO.StoredProcedure strProc = new SQLDMO.StoredProcedure();
         // Assign a name to stored procedure
         strProc.Name="InsEmployees";
         // Write a Stored Procedure Script and pass it as a string.
         strProc.Text="CREATE procedure InsEmployees(" + 
        "@v_EmpNo int,@v_Name varchar(50),@v_Surname varchar(50),
         @v_isPermanent char(10))"+
         "as "+
        "Begin Insert Into PersonalInfo(EmpNo,Name,Surname,isPermanent)"+
        "values (@v_EmpNo,@v_Name,@v_Surname ,@v_isPermanent) end";
    
         // Add the Stored Procedure to Database
         nDatabase.StoredProcedures.Add(strProc);
    }
    catch(Exception ex)
    {
         MessageBox.Show(ex.Message);
    }
}

This function SP_Students() is a stored procedure which creates the table Students:

private void SP_Students()
{
   try
   {
      // Create a Stored Procedure
       SQLDMO.StoredProcedure strProc = 
                   new SQLDMO.StoredProcedure();
      // Assign a name to stored procedure
       strProc.Name="InsStudents";
      // Write a Stored Procedure Script and 
      // pass it as a string.
       strProc.Text="CREATE procedure InsStudents as begin
       create table Students(Name Varchar(50),
                                 Surname Varchar(50)) end";
      // Add the Stored Procedure to Database
       nDatabase.StoredProcedures.Add(strProc);
                
   }
   catch(Exception ex)
   {
       MessageBox.Show(ex.Message);
   }
}

How to use this application in Set Up projects?

In Solution Explorer, right click on your set up project and select View --> Custom Actions:
The Custom Actions Window will open:
Now right click on Install and click "Add Custom Action":
In the Dialog Box that will be opened, double click on the Application Folder:
In the Dialog Box that will be opened, Click on the "Add File" button:
In the Dialog Box that will be opened, navigate to DBI.exe file in the Release folder of your application. Select the .exe file, other dependencies will be added automatically:
The Custom Actions Editor will look like this:
Select the just added DBI.exe and press F4 or open the Properties Window. Set the InstallerClass property to False as shown in figure. By default InstallerClass property is set to True:

Other considerations

It is possible that the above code might give an error saying "QueryInterface for interface SQLDMO.NameList failed". If such exceptions occur, it means that the server should be updated with the latest service pack (above SP 2).

History

3^rd Sept, 2005 - First update, using scripts for creating a table.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here