using System;
using System.Collections.Generic;
using System.Text;
using System.Security.Cryptography;
using System.IO;
namespace Project_NameSpace.Classes
{
public class EncDec
{
public EncDec()
{ }
public void Dispose()
{
GC.Collect();
GC.SuppressFinalize(this);
GC.WaitForPendingFinalizers();
//GC.GetTotalMemory(true);
}
///# byte[] Encrypt(byte[] clearData, byte[] Key, byte[] IV) - encrypts a byte array with a key and an IV and returns a byte array;
///# string Encrypt(string clearText, string Password) - encrypts a string with a password and returns a string;
///# byte[] Encrypt(byte[] clearData, string Password) - encrypts a byte array with a password and returns a byte array;
///# void Encrypt(string fileIn, string fileOut, string Password) - encrypts a file with a password and writes the encrypted bytes into another file.
///
// Encrypt a byte array into a byte array using a key and an IV
public static byte[] Encrypt(byte[] clearData, byte[] Key, byte[] IV)
{
// Create a MemoryStream to accept the encrypted bytes
MemoryStream ms = new MemoryStream();
// Create a symmetric algorithm. We are going to use Rijndael because it is strong and
// available on all platforms.
// You can use other algorithms, to do so substitute the
// next line with something like
// TripleDES alg = TripleDES.Create();
Rijndael alg = Rijndael.Create();
// Now set the key and the IV.
// We need the IV (Initialization Vector) because
// the algorithm is operating in its default
// mode called CBC (Cipher Block Chaining).
// The IV is XORed with the first block (8 byte)
// of the data before it is encrypted, and then each
// encrypted block is XORed with the
// following block of plaintext.
// This is done to make encryption more secure.
// There is also a mode called ECB which does not need an IV,
// but it is much less secure.
alg.Key = Key;
alg.IV = IV;
// Create a CryptoStream through which we are going to be
// pumping our data.
// CryptoStreamMode.Write means that we are going to be
// writing data to the stream and the output will be written
// in the MemoryStream we have provided.
CryptoStream cs = new CryptoStream(ms,
alg.CreateEncryptor(), CryptoStreamMode.Write);
// Write the data and make it do the encryption
cs.Write(clearData, 0, clearData.Length);
// Close the crypto stream (or do FlushFinalBlock).
// This will tell it that we have done our encryption and
// there is no more data coming in,
// and it is now a good time to apply the padding and
// finalize the encryption process.
cs.Close();
// Now get the encrypted data from the MemoryStream.
// Some people make a mistake of using GetBuffer() here,
// which is not the right way.
byte[] encryptedData = ms.ToArray();
return encryptedData;
}
// Encrypt a string into a string using a password
// Uses Encrypt(byte[], byte[], byte[])
public static string Encrypt(string clearText, string Password)
{
// First we need to turn the input string into a byte array.
byte[] clearBytes =
System.Text.Encoding.Unicode.GetBytes(clearText);
// Then, we need to turn the password into Key and IV
// We are using salt to make it harder to guess our key
// using a dictionary attack -
// trying to guess a password by enumerating all possible words.
PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,
0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});
// Now get the key/IV and do the encryption using the
// function that accepts byte arrays.
// Using PasswordDeriveBytes object we are first getting
// 32 bytes for the Key
// (the default Rijndael key length is 256bit = 32bytes)
// and then 16 bytes for the IV.
// IV should always be the block size, which is by default
// 16 bytes (128 bit) for Rijndael.
// If you are using DES/TripleDES/RC2 the block size is
// 8 bytes and so should be the IV size.
// You can also read KeySize/BlockSize properties off
// the algorithm to find out the sizes.
byte[] encryptedData = Encrypt(clearBytes,
pdb.GetBytes(32), pdb.GetBytes(16));
// Now we need to turn the resulting byte array into a string.
// A common mistake would be to use an Encoding class for that.
//It does not work because not all byte values can be
// represented by characters.
// We are going to be using Base64 encoding that is designed
//exactly for what we are trying to do.
return Convert.ToBase64String(encryptedData);
}
/// Encrypt bytes into bytes using a password
/// Uses Encrypt(byte[], byte[], byte[])
///
public static byte[] Encrypt(byte[] clearData, string Password)
{
// We need to turn the password into Key and IV.
// We are using salt to make it harder to guess our key
// using a dictionary attack -
// trying to guess a password by enumerating all possible words.
PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,
0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});
// Now get the key/IV and do the encryption using the function
// that accepts byte arrays.
// Using PasswordDeriveBytes object we are first getting
// 32 bytes for the Key
// (the default Rijndael key length is 256bit = 32bytes)
// and then 16 bytes for the IV.
// IV should always be the block size, which is by default
// 16 bytes (128 bit) for Rijndael.
// If you are using DES/TripleDES/RC2 the block size is 8
// bytes and so should be the IV size.
// You can also read KeySize/BlockSize properties off the
// algorithm to find out the sizes.
return Encrypt(clearData, pdb.GetBytes(32), pdb.GetBytes(16));
}
// Encrypt a file into another file using a password
public static void Encrypt(string fileIn,
string fileOut, string Password)
{
// First we are going to open the file streams
FileStream fsIn = new FileStream(fileIn,
FileMode.Open, FileAccess.Read);
FileStream fsOut = new FileStream(fileOut,
FileMode.OpenOrCreate, FileAccess.Write);
// Then we are going to derive a Key and an IV from the
// Password and create an algorithm
PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,
0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});
Rijndael alg = Rijndael.Create();
alg.Key = pdb.GetBytes(32);
alg.IV = pdb.GetBytes(16);
// Now create a crypto stream through which we are going
// to be pumping data.
// Our fileOut is going to be receiving the encrypted bytes.
CryptoStream cs = new CryptoStream(fsOut,
alg.CreateEncryptor(), CryptoStreamMode.Write);
// Now will will initialize a buffer and will be processing
// the input file in chunks.
// This is done to avoid reading the whole file (which can
// be huge) into memory.
int bufferLen = 4096;
byte[] buffer = new byte[0];
int bytesRead;
do
{
// read a chunk of data from the input file
bytesRead = fsIn.Read(buffer, 0, bufferLen);
// encrypt it
cs.Write(buffer, 0, bytesRead);
} while (bytesRead != 0);
// close everything
// this will also close the unrelying fsOut stream
cs.Close();
fsIn.Close();
}
// Decrypt a byte array into a byte array using a key and an IV
public static byte[] Decrypt(byte[] cipherData,
byte[] Key, byte[] IV)
{
// Create a MemoryStream that is going to accept the
// decrypted bytes
MemoryStream ms = new MemoryStream();
// Create a symmetric algorithm.
// We are going to use Rijndael because it is strong and
// available on all platforms.
// You can use other algorithms, to do so substitute the next
// line with something like
// TripleDES alg = TripleDES.Create();
Rijndael alg = Rijndael.Create();
// Now set the key and the IV.
// We need the IV (Initialization Vector) because the algorithm
// is operating in its default
// mode called CBC (Cipher Block Chaining). The IV is XORed with
// the first block (8 byte)
// of the data after it is decrypted, and then each decrypted
// block is XORed with the previous
// cipher block. This is done to make encryption more secure.
// There is also a mode called ECB which does not need an IV,
// but it is much less secure.
alg.Key = Key;
alg.IV = IV;
// Create a CryptoStream through which we are going to be
// pumping our data.
// CryptoStreamMode.Write means that we are going to be
// writing data to the stream
// and the output will be written in the MemoryStream
// we have provided.
CryptoStream cs = new CryptoStream(ms,
alg.CreateDecryptor(), CryptoStreamMode.Write);
// Write the data and make it do the decryption
cs.Write(cipherData, 0, cipherData.Length);
// Close the crypto stream (or do FlushFinalBlock).
// This will tell it that we have done our decryption
// and there is no more data coming in,
// and it is now a good time to remove the padding
// and finalize the decryption process.
cs.Close();
// Now get the decrypted data from the MemoryStream.
// Some people make a mistake of using GetBuffer() here,
// which is not the right way.
byte[] decryptedData = ms.ToArray();
return decryptedData;
}
// Decrypt a string into a string using a password
// Uses Decrypt(byte[], byte[], byte[])
public static string Decrypt(string cipherText, string Password)
{
// First we need to turn the input string into a byte array.
// We presume that Base64 encoding was used
byte[] cipherBytes = Convert.FromBase64String(cipherText);
// Then, we need to turn the password into Key and IV
// We are using salt to make it harder to guess our key
// using a dictionary attack -
// trying to guess a password by enumerating all possible words.
PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65,
0x64, 0x76, 0x65, 0x64, 0x65, 0x76});
// Now get the key/IV and do the decryption using
// the function that accepts byte arrays.
// Using PasswordDeriveBytes object we are first
// getting 32 bytes for the Key
// (the default Rijndael key length is 256bit = 32bytes)
// and then 16 bytes for the IV.
// IV should always be the block size, which is by
// default 16 bytes (128 bit) for Rijndael.
// If you are using DES/TripleDES/RC2 the block size is
// 8 bytes and so should be the IV size.
// You can also read KeySize/BlockSize properties off
// the algorithm to find out the sizes.
byte[] decryptedData = Decrypt(cipherBytes,
pdb.GetBytes(32), pdb.GetBytes(16));
// Now we need to turn the resulting byte array into a string.
// A common mistake would be to use an Encoding class for that.
// It does not work
// because not all byte values can be represented by characters.
// We are going to be using Base64 encoding that is
// designed exactly for what we are trying to do.
return System.Text.Encoding.Unicode.GetString(decryptedData);
}
// Decrypt bytes into bytes using a password
// Uses Decrypt(byte[], byte[], byte[])
public static byte[] Decrypt(byte[] cipherData, string Password)
{
// We need to turn the password into Key and IV.
// We are using salt to make it harder to guess our key
// using a dictionary attack -
// trying to guess a password by enumerating all possible words.
PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,
0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});
// Now get the key/IV and do the Decryption using the
//function that accepts byte arrays.
// Using PasswordDeriveBytes object we are first getting
// 32 bytes for the Key
// (the default Rijndael key length is 256bit = 32bytes)
// and then 16 bytes for the IV.
// IV should always be the block size, which is by default
// 16 bytes (128 bit) for Rijndael.
// If you are using DES/TripleDES/RC2 the block size is
// 8 bytes and so should be the IV size.
// You can also read KeySize/BlockSize properties off the
// algorithm to find out the sizes.
return Decrypt(cipherData, pdb.GetBytes(32), pdb.GetBytes(16));
}
// Decrypt a file into another file using a password
public static void Decrypt(string fileIn,
string fileOut, string Password)
{
// First we are going to open the file streams
FileStream fsIn = new FileStream(fileIn,
FileMode.Open, FileAccess.Read);
FileStream fsOut = new FileStream(fileOut,
FileMode.OpenOrCreate, FileAccess.Write);
// Then we are going to derive a Key and an IV from
// the Password and create an algorithm
PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,
0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});
Rijndael alg = Rijndael.Create();
alg.Key = pdb.GetBytes(32);
alg.IV = pdb.GetBytes(16);
// Now create a crypto stream through which we are going
// to be pumping data.
// Our fileOut is going to be receiving the Decrypted bytes.
CryptoStream cs = new CryptoStream(fsOut,
alg.CreateDecryptor(), CryptoStreamMode.Write);
// Now will will initialize a buffer and will be
// processing the input file in chunks.
// This is done to avoid reading the whole file (which can be
// huge) into memory.
int bufferLen = 4096;
byte[] buffer = new byte[0];
int bytesRead;
do
{
// read a chunk of data from the input file
bytesRead = fsIn.Read(buffer, 0, bufferLen);
// Decrypt it
cs.Write(buffer, 0, bytesRead);
} while (bytesRead != 0);
// close everything
cs.Close(); // this will also close the unrelying fsOut stream
fsIn.Close();
}
}
}
Thursday, June 11, 2009
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 + " " + tablename + "> ";
//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 + " " + strTablename + "> ";
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 + " " + tablename + "> ";
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 + " " + tablename + "> ";
StringReader oSr = new StringReader(strXml);
ds.ReadXml(oSr);
return ds;
}
catch (Exception ex)
{
this.ErrorMessage = ex.Message;
}
CloseConnection();
return ds;
}
}
}
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 = "
//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 = "
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 = "
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 = "
StringReader oSr = new StringReader(strXml);
ds.ReadXml(oSr);
return ds;
}
catch (Exception ex)
{
this.ErrorMessage = ex.Message;
}
CloseConnection();
return ds;
}
}
}
Subscribe to:
Posts (Atom)