SqlDb
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
using System.Collections;
using System.Configuration;
namespace SNSCT
{
public class SqlDb
{
public System.Collections.ArrayList fieldName = new System.Collections.ArrayList();
public System.Collections.ArrayList fieldValue = new System.Collections.ArrayList();
public int affectedRecords = 0;
private SqlConnection con;
private bool mbIsTransactionPending, mbIsDisposed;
private SqlTransaction mtraCurrentTransaction;
public SqlDb()
{
con = new SqlConnection();
//undo_Comment_For_Testing_Jayesh_19_Dec_2013
con.ConnectionString = "Data Source=P03\\SQLEXPRESS;Initial catalog = SNSCT;User Id=sa;Password=softex"; //ConfigurationManager.AppSettings["LocalConnectionString"].ToString();
mtraCurrentTransaction = null;
mbIsTransactionPending = false;
mbIsDisposed = false;
}
#region ...DB Operations...
/// <summary>
/// Implements the IDispose' method Dispose.
/// </summary>
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
/// <summary>
/// Implements the Dispose functionality.
/// </summary>
protected virtual void Dispose(bool bIsDisposing)
{
// Check to see if Dispose has already been called.
if (!mbIsDisposed)
{
if (bIsDisposing)
{
// Dispose managed resources.
if (mtraCurrentTransaction != null)
{
mtraCurrentTransaction.Dispose();
mtraCurrentTransaction = null;
}
if (con != null)
{
// closing the connection will abort (rollback) any pending transactions
con.Close();
con.Dispose();
con = null;
}
}
}
mbIsDisposed = true;
}
/// <summary>
/// Opens the connection object.
/// </summary>
/// <returns>True, if succeeded, otherwise an exception is thrown.</returns>
/// <exception cref="System.InvalidOperationException">
/// Cannot open a connection without specifying a data source or server.<br/>
/// or<br/>
/// The connection is already open.<br/>
/// </exception>
/// <exception cref="System.Data.SqlClient.SqlException">
/// A connection-level error occurred while opening the connection.
/// </exception>
public bool OpenConnection()
{
try
{
if ((con.State & ConnectionState.Open) > 0)
{
// it's already open.
throw new InvalidOperationException("Connection is already open.");
}
con.Open();
mbIsTransactionPending = false;
return (true);
}
catch (Exception ex)
{
// bubble exception
throw ex;
}
}
public bool OpenConnection(string constring)
{
try
{
con.ConnectionString = constring;
if ((con.State & ConnectionState.Open) > 0)
{
// it's already open.
throw new InvalidOperationException("Connection is already open.");
}
con.Open();
mbIsTransactionPending = false;
return (true);
}
catch (Exception ex)
{
// bubble exception
throw ex;
}
}
/// <summary>
/// Starts a new ADO.NET transaction using the open connection object of this class.
/// </summary>
/// <returns>True, if transaction is started correctly, otherwise an exception is thrown.</returns>
/// <exception cref="System.InvalidOperationException">
/// Transaction is already pending.<br/>
/// or<br/>
/// Connection is not open.
/// </exception>
public bool BeginTransaction()
{
try
{
if (mbIsTransactionPending)
{
// no nested transactions allowed.
throw new InvalidOperationException("Already transaction pending. Nesting not allowed");
}
if ((con.State & ConnectionState.Open) == 0)
{
// no open connection
throw new InvalidOperationException("Connection is not open.");
}
// begin the transaction and store the transaction object.
mtraCurrentTransaction = con.BeginTransaction();
mbIsTransactionPending = true;
return (true);
}
catch (Exception ex)
{
// bubble error
throw ex;
}
}
/// <summary>
/// Starts a new ADO.NET transaction using the open connection object of this class
/// with the specified transaction locking behaviour.
/// </summary>
/// <param name="enuIso">Transaction locking behaviour for the connection.</param>
/// <returns>True, if transaction is started correctly, otherwise an exception is thrown.</returns>
/// <exception cref="System.InvalidOperationException">
/// Transaction is already pending.<br/>
/// or<br/>
/// Connection is not open.
/// </exception>
public bool BeginTransaction(IsolationLevel enuIso)
{
try
{
if (mbIsTransactionPending)
{
// no nested transactions allowed.
throw new InvalidOperationException("Already transaction pending. Nesting not allowed");
}
if ((con.State & ConnectionState.Open) == 0)
{
// no open connection
throw new InvalidOperationException("Connection is not open.");
}
// begin the transaction and store the transaction object.
mtraCurrentTransaction = con.BeginTransaction(enuIso);
mbIsTransactionPending = true;
return (true);
}
catch (Exception ex)
{
// bubble error
throw ex;
}
}
/// <summary>
/// Commits a pending transaction on the open connection object of this class.
/// </summary>
/// <returns>True, if commit was succesful, or an exception is thrown.</returns>
/// <exception cref="System.InvalidOperationException">
/// No transaction pending.<br/>
/// or<br/>
/// Connection is not open.
/// </exception>
/// <exception cref="System.Exception">
/// An error occurred while trying to commit the transaction.
/// </exception>
public bool CommitTransaction()
{
try
{
if (!mbIsTransactionPending)
{
// no transaction pending
throw new InvalidOperationException("No transaction pending.");
}
if ((con.State & ConnectionState.Open) == 0)
{
// no open connection
throw new InvalidOperationException("Connection is not open.");
}
// commit the transaction
mtraCurrentTransaction.Commit();
mbIsTransactionPending = false;
mtraCurrentTransaction.Dispose();
mtraCurrentTransaction = null;
return (true);
}
catch (Exception ex)
{
// bubble error
throw ex;
}
}
/// <summary>
/// Rolls back a pending transaction on the open connection object of this class.
/// </summary>
/// <returns>True, if rollback was succesful, or an exception is thrown.</returns>
/// <exception cref="System.InvalidOperationException">
/// No transaction pending.<br/>
/// or<br/>
/// Connection is not open.
/// </exception>
/// <exception cref="System.Exception">
/// An error occurred while trying to rollback the transaction.
/// </exception>
public bool RollbackTransaction()
{
try
{
if (!mbIsTransactionPending)
{
// no transaction pending
throw new InvalidOperationException("No transaction pending.");
}
if ((con.State & ConnectionState.Open) == 0)
{
// no open connection
throw new InvalidOperationException("Connection is not open.");
}
// rollback the transaction
mtraCurrentTransaction.Rollback();
mbIsTransactionPending = false;
mtraCurrentTransaction.Dispose();
mtraCurrentTransaction = null;
return (true);
}
catch (Exception ex)
{
// bubble error
throw ex;
}
}
/// <summary>
/// Closes the open connection. A pending transaction is aborted.
/// </summary>
/// <returns>True, if close was succesful, False if connection was already closed,
/// or an exception is thrown when an error occurs</returns>
/// <exception cref="System.Data.SqlClient.SqlException">
/// A connection-level error occurred while closing the connection.
/// </exception>
public bool CloseConnection()
{
return (CloseConnection(false));
}
public SqlConnection getDBConnection()
{
return (con);
}
/// <summary>
/// Closes the open connection. Depending on <i>bCommitPendingTransactions</i>, a pending
/// transaction is commited, or aborted.
/// </summary>
/// <param name="bCommitPendingTransaction">Flag for what to do when a transaction is still pending.
/// True will commit the current transaction, false will abort (rollback) the complete
/// current transaction.</param>
/// <returns>True, if close was succesful, False if connection was already closed, or
/// an exception is thrown when an error occurs</returns>
/// <exception cref="System.Data.SqlClient.SqlException">
/// A connection-level error occurred while closing the connection.
/// </exception>
public bool CloseConnection(bool bCommitPendingTransaction)
{
try
{
if ((con.State & ConnectionState.Open) == 0)
{
// no open connection
return (false);
}
if (mbIsTransactionPending)
{
if (bCommitPendingTransaction)
{
// commit the pending transaction
mtraCurrentTransaction.Commit();
}
else
{
// rollback the pending transaction
mtraCurrentTransaction.Rollback();
}
mbIsTransactionPending = false;
mtraCurrentTransaction.Dispose();
mtraCurrentTransaction = null;
}
// close the connection
con.Close();
return (true);
}
catch (Exception ex)
{
// bubble error
throw ex;
}
}
#endregion
#region Class Property Declarations
/// <summary>
/// Returns current transaction.
/// </summary>
public SqlTransaction CurrentTransaction
{
get
{
return (mtraCurrentTransaction);
}
}
/// <summary>
/// Returns a flag specifies whether transaction is pending.
/// </summary>
public bool IsTransactionPending
{
get
{
return (mbIsTransactionPending);
}
}
/// <summary>
/// Returns connection object.
/// </summary>
public IDbConnection DBConnection
{
get
{
return (con);
}
}
/// <summary>
/// Sets the connection string.
/// </summary>
public string ConnectionString
{
set
{
con.ConnectionString = value;
}
get
{
return con.ConnectionString;
}
}
#endregion
#region ...Methods...
public void setCmbFill(System.Windows.Forms.ComboBox cmb, string Qry)
{
cmb.Items.Clear();
DataTable dt = getTable(Qry);
for (int r = 0; r < dt.Rows.Count; r++)
{
cmb.Items.Add(dt.Rows[r][0].ToString().Trim());
}
cmb.SelectedIndex = 0;
}
public void setListFill(System.Windows.Forms.ListBox lst, string Qry)
{
lst.Items.Clear();
DataTable dt = getTable(Qry);
for (int r = 0; r < dt.Rows.Count; r++)
{
lst.Items.Add(dt.Rows[r][0].ToString().Trim());
}
}
//public void SetColumnSortMode( DataGridView dataGridView, DataGridViewColumnSortMode sortMode)
//{
//}
public DataTable getTable_(string Qry)
{
SqlDataAdapter ada = new SqlDataAdapter(Qry, con);
DataTable dt = new DataTable();
try
{
ada.Fill(dt);
}
catch (Exception ex) { }
return dt;
}
public DataTable getTable(string Qry)
{
SqlDataAdapter da = new SqlDataAdapter(Qry, con);
DataSet ds = new DataSet();
da.SelectCommand.Transaction = mtraCurrentTransaction;
try
{
da.Fill(ds);
ds.Tables.Add();
return ds.Tables[0];
}
catch (System.Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message, "Connection error !!", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
return null;
}
finally
{
}
}
public SqlDataReader getRow(string Qry)
{
//OpenConnection();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(Qry, con);
myReader = myCommand.ExecuteReader();
myCommand.Dispose();
return myReader;
}
public string GetValue(string Qry)
{
string retStr = "";
DataTable dt = new DataTable();
dt = getTable(Qry);
if (dt.Rows.Count > 0)
{
retStr = dt.Rows[0][0].ToString().Trim();
}
return retStr;
}
public int getColumnInt(string Qry)
{
int intColumn = 0;
DataTable dt = getTable(Qry);
if (dt.Rows.Count > 0)
{
intColumn = Convert.ToInt32(dt.Rows[0][0].ToString());
}
return intColumn;
}
public string getColumnString(string Qry)
{
string strColumn = "";
DataTable dt = getTable(Qry);
if (dt.Rows.Count > 0)
{
strColumn = dt.Rows[0][0].ToString().Trim();
}
return strColumn;
}
public double getColumnDouble(string Qry)
{
double dblColumn = 0;
DataTable dt = getTable(Qry);
if (dt.Rows.Count > 0)
{
dblColumn = Convert.ToDouble(dt.Rows[0][0].ToString());
}
return dblColumn;
}
public bool isConnectionOK()
{
SqlConnection Cnn;
SqlDataAdapter da;
DataSet ds;
try
{
Cnn = new SqlConnection(con.ConnectionString);
Cnn.Open();
da = new SqlDataAdapter("select getDate()", Cnn);
ds = new DataSet();
da.Fill(ds);
ds.Clear();
da.Dispose();
Cnn.Close();
return true;
}
catch (System.Exception ee)
{
System.Windows.Forms.MessageBox.Show(ee.ToString());
}
finally
{
}
return false;
}
public int Execute(string Qry)
{
SqlCommand cmd = new SqlCommand(Qry, con);
cmd.Transaction = mtraCurrentTransaction;
int r = cmd.ExecuteNonQuery();
return r;
}
public int Execute_(string Qry)
{
int r = 0;
try
{
con.Open();
SqlCommand cmd = new SqlCommand(Qry, con);
r = cmd.ExecuteNonQuery();
con.Close();
return r;
}
catch (Exception ex)
{
}
return r;
}
public int Execute_Img(string Qry,byte[] img)
{
int r = 0;
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = Qry;
cmd.Parameters.AddWithValue("@image", img);
r = cmd.ExecuteNonQuery();
con.Close();
return r;
}
catch (Exception ex)
{
}
return r;
}
public void setFieldDataClear()
{
fieldName.Clear();
fieldValue.Clear();
}
public void setFieldData(string fName, DateTime fValue) { setFieldData(fName, fValue.ToString("yyyy-MM-dd HH:mm"), "'"); }
public void setFieldData(string fName, int fValue) { setFieldData(fName, fValue.ToString(), ""); }
public void setFieldData(string fName, bool fValue) { setFieldData(fName, fValue.ToString(), ""); }
public void setFieldData(string fName, decimal fValue) { setFieldData(fName, fValue.ToString(), ""); }
public void setFieldData(string fName, double fValue) { setFieldData(fName, fValue.ToString(), ""); }
public void setFieldData(string fName, string fValue) { setFieldData(fName, fValue, "'"); }
public void setFieldData(string fName, string fValue, string Sign)
{
fieldName.Add(fName);
fieldValue.Add(Sign + fValue + Sign);
}
public int getInsert(string tableName)
{
string q1 = "";
string q2 = "";
foreach (string nam in fieldName)
{
if (q1 != "") q1 += ", ";
q1 += nam;
}
foreach (string val in fieldValue)
{
if (q2 != "") q2 += ", ";
q2 += val;
}
fieldName.Clear();
fieldValue.Clear();
return Execute("Insert into " + tableName + "(" + q1 + ") values(" + q2 + ")");
}
public int getUpdate(string tableName, string wherCondition)
{
string q = "";
for (int n = 0; n < fieldName.Count; n++)
{
if (q != "") q += ", ";
q += fieldName[n].ToString() + "=" + fieldValue[n].ToString();
}
fieldName.Clear();
fieldValue.Clear();
return Execute("Update " + tableName + " set " + q + " where " + wherCondition);
}
public string getTimeString(string Seconds)
{
int TimeSeconds = 0;
try
{
TimeSeconds = int.Parse(Seconds);
}
catch { }
return getTimeString(TimeSeconds);
}
public string getTimeString(int Seconds)
{
if (Seconds < (60 * 60))
{
return (Seconds / 60).ToString() + ":" + (Seconds % 60).ToString("00");
}
return TimeSpan.FromSeconds((double)Seconds).ToString();
}
public string getTitleCase(string txt)
{
try
{
System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en");
return ci.TextInfo.ToTitleCase(txt.Trim());
}
catch
{
return txt;
}
}
public int getInt(object strValue)
{
if (strValue == null) return 0;
try
{
return int.Parse(strValue.ToString());
}
catch { }
return 0;
}
public double getDouble(object strValue)
{
if (strValue == null) return 0;
try
{
return double.Parse(strValue.ToString());
}
catch { }
return 0;
}
public string getSysDate()
{
string strQry = "SELECT CONVERT(VARCHAR(20), GETDATE(), 1)";
string strSysDate = GetValue(strQry);
return strSysDate;
}
public string getSysDate_NEW()
{
string strQry = "SELECT CONVERT(VARCHAR(20), GETDATE(), 103)";
string strSysDate = GetValue(strQry);
return strSysDate;
}
public string getSysDateOnly()
{
string strQry = "SELECT CONVERT(VARCHAR(20), GETDATE(), 101)";
string strSysDate = GetValue(strQry);
return strSysDate;
}
public string getSysDateTime()
{
string Query = " SELECT GETDATE() ";
return GetValue(Query);
}
public string getBetweenString(DateTime dt1, DateTime dt2)
{
return string.Format("cast('{0} 00:00:00' as datetime) and cast('{1} 23:59:59' as datetime)"
, dt1.ToString("yyyy-MM-dd"), dt2.ToString("yyyy-MM-dd"));
}
public string tableManipulation(string tableName, string command, string columnName, string dataType, int size)
{
return tableManipulation(tableName, command, columnName, dataType, size, "");
}
public string tableManipulation(string tableName, string command, string columnName, string dataType, int size, string defaltValue)
{
string QuerydataType = "";
dataType = dataType.ToLower().Trim();
command = command.ToLower().Trim();
if (dataType == "varchar" || dataType == "char" || dataType == "nchar")
{
QuerydataType = dataType + " (" + size + ") ";
}
else
{
QuerydataType = dataType;
}
string strQuery = "alter table " + tableName + " ";
if (command == "alter")
{
strQuery += command + " column " + columnName + " " + QuerydataType;
}
else if (command == "drop")
{
strQuery += command + " column " + columnName;
}
else if (command == "add")
{
strQuery += command + " " + columnName + " " + QuerydataType + " DEFAULT '0'";
}
affectedRecords = 0;
try
{
affectedRecords = Execute(strQuery);
}
catch (Exception ee) { return "Error : \t" + ee.Message + ",\t Query - > " + strQuery; }
strQuery = "update {0} set {1}={2} where {1} is null";
if (dataType == "varchar" || dataType == "char" || dataType == "nchar")
{
Execute(String.Format(strQuery, tableName, columnName, "'" + defaltValue + "'"));
}
else if (dataType == "bit" || dataType == "int" || dataType == "money" || dataType == "decimal" || dataType == "numeric")
{
if (defaltValue == "") defaltValue = "0";
Execute(String.Format(strQuery, tableName, columnName, defaltValue));
}
return strQuery;
}
#endregion
/// <summary>
/// Hashim
/// </summary>
public static void RestoreDatabase(string database_name, string path)
{
}
/// <summary>
/// Hashim
/// </summary>
public int BulkCopy(DataTable dt, string DestinationTableName)
{
int updated = 0;
try
{
SqlBulkCopy sbc = new SqlBulkCopy(con);
sbc.DestinationTableName = DestinationTableName;
sbc.WriteToServer(dt);
updated = 1;
}
catch { updated = 0; }
return updated;
}
/// <summary>
/// Hashim 01-12-2010
/// </summary>
public void WriteToConfigFile(string Key,string Value)
{
//System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(Application.ExecutablePath);
//config.AppSettings.Settings[Key].Value = Value;
//config.Save(ConfigurationSaveMode.Modified);
//ConfigurationManager.RefreshSection("appSettings");
}
public void BackupDb()
{
if (!System.IO.Directory.Exists("C:\\Cube_DB_Backup"))
System.IO.Directory.CreateDirectory("C:\\Cube_DB_Backup");
SqlDb.BackUpDatabase("CubeHomeTrans", "C:\\Cube_DB_Backup\\"
, "Backup_" + System.DateTime.Now.ToString() + "__" + "CubeHomeTrans");
}
public static void BackUpDatabase(string database_name, string path, string filename)
{
filename = filename.Replace("/", "_");
filename = filename.Replace(":", "_");
filename = filename.Replace(" ", "_");
path += filename;
string Query = " BACKUP DATABASE " + database_name + " TO DISK = N'" + path + "' "
+ " WITH NOFORMAT, INIT, NAME = N'" + database_name + "-Full Database Backup' "
+ " , SKIP, NOREWIND, NOUNLOAD, STATS = 10";
SqlDb db = new SqlDb();
db.Execute_(Query);
}
}
}
