How To Create a sql database using c# sql server management objects

DbInstall Manager.s


using System;using System.IO;using System.Data.SqlClient;using System.Data;using Dbparameter;using
Microsoft.SqlServer.Management.Smo;namespace
{
DbInstallerpublic class DBInstallerManager{

DataConn ConF =

{
public String Param;new DataConn(); public void CreateDatabase(DatabaseParam DBParam)try{
ConF.DataconnNew(DBParam.ServerName,

sqlCreateDBQuery =
"", DBParam.UserName, DBParam.PassWord);String sqlCreateDBQuery;" CREATE DATABASE " + DBParam.DatabaseName + " ON PRIMARY "+ " (NAME = '" + DBParam.DatabaseName + "', "+ " FILENAME = '" + DBParam.DataPathName + "', "+ " SIZE = " + DBParam.DataFileSize + ","+ " MAXSIZE = " + DBParam.DataMaxSize + ","+ " FILEGROWTH =" + DBParam.DataFileGrowth + ") "+ " LOG ON (NAME ='" + DBParam.LogFileName + "', "+ " FILENAME = '" + DBParam.LogPathName + "', "+ " SIZE = " + DBParam.LogFileSize + ", "+ " MAXSIZE = " + DBParam.LogMaxSize + ", "+
Param = DBParam.DatabaseName;
SqlCommand myCommand =

ConF.con.Open();
myCommand.ExecuteNonQuery();
" FILEGROWTH =" + DBParam.LogFileGrowth + ") ";new SqlCommand(sqlCreateDBQuery, ConF.con);if (ConF.con.State == ConnectionState.Closed)try{
//ConF.DataconnNew(DBParam.ServerName, DBParam.DatabaseName, DBParam.UserName, DBParam.PassWord);alterDb(DBParam.UserName);
SqlConnection.ClearAllPools();
}

{

}
catch (System.Exception ex)throw ex;finally{
ConF.con.Close();
}

}

{

}
}

{
return;catch (Exception ex)throw ex;public void alterDb(String Mdbuserid)try{

sqlCreateDBQuery =
String sqlCreateDBQuery;" Alter DATABASE " + Param + " Set Recovery Simple "+
SqlCommand CMDCreateuser =
SqlCommand cmdGrantSelect =
SqlCommand myCommand =

ConF.con.Open();
myCommand.ExecuteNonQuery();
}

{

}
}

{
Server server=
" Alter DATABASE " + Param + " SET AUTO_SHRINK OFF ";new SqlCommand("Create User " + Mdbuserid + " WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo] ", ConF.con);new SqlCommand("GRANT SELECT,create xml Schema Collection,INSERT,Alter,Delete,Execute,Update " + " TO " + Mdbuserid + " ;", ConF.con);new SqlCommand(sqlCreateDBQuery, ConF.con);if (ConF.con.State == ConnectionState.Closed)catch (Exception ex)throw ex;public void ExecuteScript(string[] sqlscriptPath, string dbname, string instancename, string userid, string password,System.Windows.Forms.Label ProgressStatus)null;try{
ProgressStatus.Text =
ProgressStatus.Refresh();
ConF.DataconnNew(instancename, dbname, userid, password);

server =
server.ConnectionContext.BeginTransaction();

{
ProgressStatus.Text =
ProgressStatus.Refresh();
System.Windows.Forms.Application.DoEvents();


ProgressStatus.Text =
ProgressStatus.Refresh();
System.Windows.Forms.Application.DoEvents();
server.ConnectionContext.ExecuteNonQuery(script);
}
"Establishing database connection...";new Server(new Microsoft.SqlServer.Management.Common.ServerConnection(ConF.con));for (Int32 ilp = 0; ilp < sqlscriptPath.Length; ilp++)"Loading script in to memory...";FileInfo file = new FileInfo(sqlscriptPath[ilp] );string script = file.OpenText().ReadToEnd();"Executing script.. " + sqlscriptPath[ilp];//file.OpenText().Close();//SqlConnection conn = new SqlConnection(ConF.con.ConnectionString);

//ConF.con.BeginTransaction(IsolationLevel.ReadUncommitted, "scriptExe");ProgressStatus.Text =
ProgressStatus.Refresh();
server.ConnectionContext.CommitTransaction();
SqlConnection.ClearAllPools();
}

{

{
"Committing the changes...";catch (Exception ex)if (server != null)try{
ProgressStatus.Text =
ProgressStatus.Update();
server.ConnectionContext.RollBackTransaction();
}
"Roll back the changes...";catch{
}
}

}
}

{
throw ex;public void deleteDatabase(string instancename,string dbName, string uid, string password)try{
ConF.DataconnNew(instancename,
SqlCommand sqlcmd =
"master", uid, password);new SqlCommand("DROP DATABASE " + dbName, ConF.con);try{
sqlcmd.ExecuteNonQuery();
}

ConF.con.Close();
}

{

}
}
}
}


//////Database param.cs



using System;namespace
{
Dbparameterpublic class DatabaseParam{




public string ServerName;public string DatabaseName;public string UserName;public String PassWord;// public string DataFileName;








}
}



/////// DataConn.cs



using System;using System.Data.SqlClient;using System.Data;using
System.Runtime.InteropServices;namespace
{
DbInstallerclass DataConn{




{
public String Param;public SqlConnection con=null;public String SConString = null;public void DataconnNew(String Msrvr, String SrvrDb, String Usr, String Pwrd)try{
SConString =
con =
con.ConnectionString = SConString;

con.Open();
}

{

}
"Data Source=" + Msrvr + ";Initial Catalog=" + SrvrDb + "; User Id=" + Usr + " ;Password=" + Pwrd + ";Trusted_Connection=False;";new SqlConnection();if (con.State == ConnectionState.Closed)catch (Exception ex)throw ex;//finally//{// Marshal.ReleaseComObject(con);// GC.Collect();//}}


}
}
public string DataPathName;public string DataFileSize="30336KB";public string DataMaxSize="UNLIMITED";public string DataFileGrowth="10%";public string LogFileName;public string LogPathName;public string LogFileSize="1280KB";public string LogMaxSize= "2048GB";public string LogFileGrowth= "10%";
catch {}catch (Exception)throw;

Comments