Make your ASP.NET application install it's own Database

Introduction

Make your ASP.NET application install it's own Database

Clieck here to Download sourcecode

Sample screenshot

This article explains you that how we have to configure that application installs its own database.

In this article I will explains you that by taking an application called “SetUpDataBase”

In this application I have been created form called “DatabaseSetUp.aspx” in this form I am asking user to enter all SQL Server credentials like SQL SERVER Name, Database name and SQL Server authentication it includes username and password.

As soon as User Enter the All SQL SERVER credentials after clicking the INSTALL button It executes the method called CreateDatabase();

In This Method first I will check for Database what user enters already exists or not.

If database already exists I will display the Error message other wise will create new database in SQL Server.

string strSQL ="IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '" + txtSqlDataBase.Text + "') SELECT 1 as EXTANT ELSE SELECT 0 as EXTANT";

SqlCommand sqlCmd1 = new SqlCommand(strSQL,sqlConn);

sqlCmd1.CommandType = CommandType.Text;

//sqlConn.Open();

int i = (int)sqlCmd1.ExecuteScalar();

if(i==1)

{

rorMsg.Text ="DataBase Not Configured Sucessfully Contact DataBase Administrator";

return;

}

else

{

ErrorMsg.Text="";

strSQL = "CREATE DATABASE " + txtSqlDataBase.Text;

SqlCommand sqlCmd = new SqlCommand(strSQL,sqlConn);

try

{

sqlCmd.ExecuteNonQuery();

}

catch

{

ErrorMsg.Text ="DataBase Not Configured Sucessfully Contact DataBase Administrator";

return;

}

After creating the Database I will call one more method called CreateStructureNData().

In this method I will read the script files in side the _SQLDB Folder by using StreamReader.

StreamReader sr1 = new StreamReader(RootDirectory +this.cSqlDataFile);

string strSQL1 = sr1.ReadToEnd();

SqlCommand cmd1 = new SqlCommand(strSQL1,sql1);

cmd1.CommandType = CommandType.Text;

try

{

cmd1.ExecuteNonQuery();

}

catch(Exception ex)

{

ErrorMsg.Text =ex.Message;

return false;

}

sr1.Close();

After creating Database and its Objects I will updates the web.config file by using the method called Configfilesetup ().

In this method I update the SQL connection string as Follows,

public void Configfilesetup()

{

try

{

XmlConfig.Load(Server.MapPath("~/Web.config"));

XmlNode node = XmlConfig.SelectSingleNode("//appSettings");

node.ChildNodes.Item(1).Attributes.Item(1).Value = txtSqlServer.Text;

node.ChildNodes.Item(2).Attributes.Item(1).Value = txtSqlPort.Text;

node.ChildNodes.Item(3).Attributes.Item(1).Value = txtSqlDataBase.Text;

node.ChildNodes.Item(4).Attributes.Item(1).Value = txtSqlUserName.Text;

node.ChildNodes.Item(5).Attributes.Item(1).Value = TxtPassword.Text;

///////////

//System.IO.FileInfo

System.IO.FileInfo sFileInfo = new System.IO.FileInfo(Server.MapPath("~/Web.config"));

sFileInfo.Attributes = FileAttributes.Normal;

XmlConfig.Save(Server.MapPath("~/Web.config"));

sFileInfo.Attributes = FileAttributes.ReadOnly;

//////////

}

catch(Exception ex)

{

ErrorMsg.Text = ex.Message;

}

In Page_Load () if already web.config files holds database connection settings I will fill all data into the form by using following code.

if(!IsPostBack)

{

// Put user code to initialize the page here

XmlConfig.Load(Server.MapPath("~/Web.config"));

XmlNode node = XmlConfig.SelectSingleNode("//appSettings");

txtSqlServer.Text = node.ChildNodes.Item(1).Attributes.Item(1).Value ;

txtSqlPort.Text = node.ChildNodes.Item(2).Attributes.Item(1).Value;

txtSqlDataBase.Text = node.ChildNodes.Item(3).Attributes.Item(1).Value ;

txtSqlUserName.Text = node.ChildNodes.Item(4).Attributes.Item(1).Value;

TxtPassword.Text = node.ChildNodes.Item(5).Attributes.Item(1).Value;

}

In web.config file we need to capture SQL Server connection credentials as follows,

<appSettings>

    <!—Connection string-->

    <add key="SqlServer" value=" " />

    <add key="SqlServerPort" value="" />

    <add key="SqlDatabase" value=" " />

    <add key="SqlUserName" value=" " />

    <add key="SqlUserPassword" value=" " />

</appSettings>

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