Database connection Class

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Collections ;
using System.IO;
using System.ComponentModel;
using System.Net;
using System.Windows.Forms;
using System.Data;
using System.Configuration;
using System.Xml;
//Below namespace is for execute sql-file by programatically
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
namespace Project_NameSpace.Classes
{
class clsDataOperation
{
public string ErrorMessage = "",strDatabase = "", strUsername = "", strPassword = "", strAppName = "", strServer = "", strDbAutMod = "";
public string strToCreateDbName="";
private SqlConnection conn;
private SqlCommand comm=new SqlCommand();
public void SetAppConfigData()
{
strDatabase = ConfigurationSettings.AppSettings["database"].ToString();
strUsername = ConfigurationSettings.AppSettings["username"].ToString();
strPassword = ConfigurationSettings.AppSettings["password"].ToString();
strAppName = ConfigurationSettings.AppSettings["Application"].ToString();
strServer = ConfigurationSettings.AppSettings["server"].ToString();
strDbAutMod = ConfigurationSettings.AppSettings["Mode"].ToString();
}
public bool CheckDataBase()
{
bool bRet = false;
if (OpenMasterDbConn())//Check Master Database connection open or not
{
SetAppConfigData();
strToCreateDbName = strDatabase;//Set string database name to create
string cmdText = "select * from dbo.sysdatabases where name='" + strDatabase + "'";
DataSet ds = ExecMasterDbQry(cmdText, "Database");
if (ds.Tables[0].Rows.Count > 0)
{
bRet = true;
}

CloseConnection();
return bRet;
}
return bRet;
}
public bool CreateDatabase(string strDbScript)
{
bool bRet = false;
try
{
OpenMasterDbConn();

string cmdText1 = "create database " + strToCreateDbName;
ExecNonQuery(cmdText1, "Database");
string cmdText = strDbScript;
string qry = "use " + strToCreateDbName;
conn.Open();
comm.CommandText = qry;
comm.ExecuteNonQuery();
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(cmdText);
CloseConnection();
}
catch
{
if (CheckDataBase())
{
OpenMasterDbConn();
conn.Open();
comm.CommandText = "drop "+strToCreateDbName;
comm.ExecuteNonQuery();
conn.Close();
CloseConnection();
//Server server = new Server();
//string abc=server.ActiveDirectory.UserData.ToString();

}
bRet = false;
}
return bRet;
}
public void ExecNonQuery(string qry, string tablename)
{
try
{
OpenMasterDbConn();
comm.CommandText = qry;
comm.CommandType = CommandType.Text;
comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
//this.ErrorMessage = "SqlException: " + ex.Message + " // SqlErrorNumber:- " + ex.Number;
//string strXml = "<" + tablename + ">100" + this.ErrorMessage + "";
//StringReader oSr = new StringReader(strXml);
//ds.ReadXml(oSr);
//return ds;
}
catch (Exception ex)
{
this.ErrorMessage = ex.Message;
}
CloseConnection();

}
public void OpenDbConn()
{
try
{
//SetAppConfigData();
//Create_Database_IfNotExists();
//---Check Database exists or Not----------------
//---If Not exits than created automatically-----
//-----------------------------------------------
//conn = new SqlConnection(connstr);

// Changes Made By: Preeti B. Mathur
// Date: 07-02-2009
// START

string strConnectionString = EncDec.Decrypt(ConfigurationSettings.AppSettings["ConnString"].ToString(), "DatabaseConnectionString");
//conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnString"].ToString());
conn = new SqlConnection(strConnectionString);
// Changes Made By: Dhaval Chauhan
// Date: 20-02-2009
// START
//conn = new SqlConnection(connstr);
// END
conn.Open();
comm.Connection = conn;
}
catch
{
MessageBox.Show("Cannot connect database", "Connection failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool OpenMasterDbConn()
{
bool blnRes = false;
try
{
SetAppConfigData();
strDatabase = "master";
//Create_Database_IfNotExists();
//---Check Database exists or Not----------------
//---If Not exits than created automatically-----
//-----------------------------------------------
string connstr;
if (strDbAutMod == "sql")
{
connstr = "Persist Security Info=False;" + "User ID=" + strUsername + ";PWD=" + strPassword + ";Initial Catalog=" + strDatabase + ";Data Source=" + strServer + ";";
}
else
{
connstr = "Data Source=" + strServer + ";Initial Catalog=" + strDatabase + ";Integrated Security=True";
}
// Changes Made By: Preeti B. Mathur
// Date: 07-02-2009
// START
// conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnString"].ToString());
conn = new SqlConnection(connstr);
//"Persist Security Info=False;User ID=sa;PWD=sa123;Initial Catalog=master;Data Source=it-dhaval\\sqlexpress;"
// Changes Made By: dhaval chauhan
// Date: 07-02-2009
// START
//conn = new SqlConnection(connstr);
// END

conn.Open();
comm.Connection = conn;
blnRes = true;
}
catch
{
MessageBox.Show("Cannot connect database", "Connection failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
blnRes = false;
}
return blnRes;
}
public void AddParameter(string ParamName, SqlDbType Datatype, string Value, int ParamSize)
{
SqlParameter Param = new SqlParameter(ParamName, Datatype, ParamSize);
Param.Value = Value;
Param.Direction = ParameterDirection.Input;
comm.Parameters.Add(Param);
}
public void AddParameter(string ParamName,SqlDbType Datatype,byte[] Value,int ParamSize)
{
SqlParameter Param = new SqlParameter(ParamName, Datatype, ParamSize);
Param.Value = Value;
Param.Direction = ParameterDirection.Input;
comm.Parameters.Add(Param);
}
public void AddParameter(string ParamName, SqlDbType Datatype, int Value)
{
SqlParameter Param = new SqlParameter(ParamName, Datatype);
Param.Value = Value;
Param.Direction = ParameterDirection.Input;
comm.Parameters.Add(Param);
}
public DataSet ExecProc(string strProcedurename,string strTablename)
{
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter();
OpenDbConn();
try
{
comm.CommandText = strProcedurename;
comm.CommandType = CommandType.StoredProcedure;
adp.SelectCommand = comm;
adp.Fill(ds, strTablename);
}
catch (SqlException ex)
{
this.ErrorMessage = "SqlException: " + ex.Message + " // SqlErrorNumber:- " + ex.Number;
string strXml = "<" + strTablename + ">100" + this.ErrorMessage + "";
StringReader oSr = new StringReader(strXml);
ds.ReadXml(oSr);
return ds;
}
CloseConnection();
return ds;
}
public void CloseConnection()
{
conn.Close();
}
public DataSet ExecQry(string qry,string tablename)
{
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter();
try
{
OpenDbConn();
comm.CommandText = qry;
comm.CommandType = CommandType.Text;
adp.SelectCommand = comm;
adp.Fill(ds, tablename);
}
catch (SqlException ex)
{
this.ErrorMessage = "SqlException: " + ex.Message + " // SqlErrorNumber:- " + ex.Number;
string strXml = "<" + tablename + ">100" + this.ErrorMessage + "";
StringReader oSr = new StringReader(strXml);
ds.ReadXml(oSr);
return ds;
}
catch (Exception ex)
{
this.ErrorMessage = ex.Message;
}
CloseConnection();
return ds;
}
public DataSet ExecMasterDbQry(string qry, string tablename)
{
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter();
try
{
OpenMasterDbConn();
comm.CommandText = qry;
comm.CommandType = CommandType.Text;
adp.SelectCommand = comm;
adp.Fill(ds, tablename);
}
catch (SqlException ex)
{
this.ErrorMessage = "SqlException: " + ex.Message + " // SqlErrorNumber:- " + ex.Number;
string strXml = "<" + tablename + ">100" + this.ErrorMessage + "";
StringReader oSr = new StringReader(strXml);
ds.ReadXml(oSr);
return ds;
}
catch (Exception ex)
{
this.ErrorMessage = ex.Message;
}
CloseConnection();
return ds;
}

}
}