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






4.25/5 (38 votes)
Aug 27, 2005
3 min read

185477

4337
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:
- 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 theExecuteImmediate
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 toFalse
as shown in figure. By defaultInstallerClass
property is set toTrue
:
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.