Click here to Skip to main content
13,045,001 members (44,692 online)
Click here to Skip to main content
Add your own
alternative version

Stats

153K views
4.1K downloads
172 bookmarked
Posted 27 Aug 2005

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

, 2 Sep 2005
Rate this:
Please Sign up or sign in to vote.
This application can be used to create databases, tables, stored procedures during the installation of .NET applications.

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:

  1. 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);
       }
    }
  2. 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

  1. 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);
        }
    }
  2. 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?

  1. In Solution Explorer, right click on your set up project and select View --> Custom Actions:

  2. The Custom Actions Window will open:

  3. Now right click on Install and click "Add Custom Action":

  4. In the Dialog Box that will be opened, double click on the Application Folder:

  5. In the Dialog Box that will be opened, Click on the "Add File" button:

  6. 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:

  7. The Custom Actions Editor will look like this:

  8. 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

  • 3rd 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

Share

About the Author

*****

Comments and Discussions

 
GeneralGreat job! Pin
jith_9127-Jun-11 22:06
memberjith_9127-Jun-11 22:06 
GeneralNot all clients know server pass for their machine Pin
asder436-Mar-09 7:56
memberasder436-Mar-09 7:56 
Generalgood work Pin
Donsw19-Dec-08 10:17
memberDonsw19-Dec-08 10:17 
GeneralRoll Back Installation Pin
ganeshgopug10-Oct-08 1:50
memberganeshgopug10-Oct-08 1:50 
GeneralSerial key / Registration key Pin
divyesh14325-Jun-08 19:06
memberdivyesh14325-Jun-08 19:06 
GeneralRead a stored procedure into vb.net 200 rogram Pin
dino62412-Jan-08 16:04
memberdino62412-Jan-08 16:04 
QuestionMySql DB installer Pin
Shantanu Behera25-Aug-07 8:41
memberShantanu Behera25-Aug-07 8:41 
AnswerRe: MySql DB installer Pin
DParker7-Aug-11 9:20
memberDParker7-Aug-11 9:20 
GeneralTo connect my program to a database Pin
mikobi18-Aug-07 0:00
membermikobi18-Aug-07 0:00 
GeneralAdding/Attach new user to sql server Pin
yachitha23-Apr-07 1:53
memberyachitha23-Apr-07 1:53 
GeneralCreate UID/PWD and permission for DB Pin
Arun Yodyana6-Nov-05 21:35
memberArun Yodyana6-Nov-05 21:35 
QuestionCan i use the DB installer with MSDE Pin
reddymade120-Oct-05 8:33
memberreddymade120-Oct-05 8:33 
GeneralFor oracle Pin
siva_218131-Aug-05 2:59
membersiva_218131-Aug-05 2:59 
QuestionAccess Databases Pin
Anonymous30-Aug-05 21:55
memberAnonymous30-Aug-05 21:55 
AnswerRe: Access Databases Pin
Mukund Pujari30-Aug-05 23:16
memberMukund Pujari30-Aug-05 23:16 
AnswerRe: Access Databases Pin
Frank Samjeske30-Aug-05 23:33
memberFrank Samjeske30-Aug-05 23:33 
QuestionError message Pin
The Beast30-Aug-05 21:07
memberThe Beast30-Aug-05 21:07 
AnswerRe: Error message Pin
Mukund Pujari30-Aug-05 21:29
memberMukund Pujari30-Aug-05 21:29 
GeneralRe: Error message Pin
The Beast30-Aug-05 21:37
memberThe Beast30-Aug-05 21:37 
GeneralRe: Error message Pin
ssembo10-Oct-05 19:52
memberssembo10-Oct-05 19:52 
AnswerRe: Error message Pin
JAINPR23-Sep-05 23:04
memberJAINPR23-Sep-05 23:04 
GeneralRe: Error message Pin
Mukund Pujari23-Sep-05 23:34
memberMukund Pujari23-Sep-05 23:34 
GeneralRe: Error message Pin
jainpr24-Sep-05 4:52
memberjainpr24-Sep-05 4:52 
QuestionSQL-Script Pin
Frank Samjeske30-Aug-05 21:04
memberFrank Samjeske30-Aug-05 21:04 
AnswerRe: SQL-Script Pin
Mukund Pujari30-Aug-05 21:21
memberMukund Pujari30-Aug-05 21:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170713.1 | Last Updated 3 Sep 2005
Article Copyright 2005 by Mukund Pujari
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid