Notes

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using InventoryCSharp;
namespace InventoryCSharp.Lib
{
public class DAL
{
private static DAL instance;
protected string connectionString;
protected SqlConnection cn;
protected SqlTransaction tx;
protected bool transaction_started = false;

// constructor since its exactly same with class name
private DAL(string cs="")
{

this.connectionString = "Data Source=.\SQLEXPRESS; database=final_project; Integrated Security=True";
if (cs != "")
{
this.connectionString = cs;
}
cn = new SqlConnection(this.connectionString);
this.open();
}

public static DAL getInstance(){

if (instance == null)
{
instance = new DAL();
}
return instance;
}


// start transaction method
public void beginTransaction(){

this.tx = cn.BeginTransaction();
this.transaction_started = true;
}

public void commit()
{
this.tx.Commit();
this.transaction_started = false;
}

public void Rollback()
{
this.tx.Rollback();
}


public void setConnectionString(string cs)
{
this.connectionString = cs;
}
public void open()
{
try
{
// check connection string for empty
if (this.connectionString == "")
{
throw new Exception("Connection string is empty. Please set connection string");
}
if (this.cn.State != ConnectionState.Open)
{
this.cn.Open();
}

}
catch (Exception ex)
{
throw ex;
}

}



public object insert(string sql)
{
try
{
sql = sql.Trim();
// check the sql
if (sql.Substring(0, 6).ToUpper() != "INSERT")
{
throw new Exception("Sql must be insert sql");
}
this.executeNonQuery(sql);
return this.getLastInsertId();
//return this.executeNonQuery(sql);
}
catch (Exception ex)
{
throw ex;
}


}

// update studnet set ;
public int update(string sql)
{
sql = sql.Trim();
// check the sql
if (sql.Substring(0, 6).ToUpper() != "UPDATE")
{
throw new Exception("Sql must be update sql");
}
return this.executeNonQuery(sql);
}
public int delete(string sql)
{
try
{
sql = sql.Trim();
// check the sql
if (sql.Substring(0, 6).ToUpper() != "DELETE")
{
throw new Exception("Sql must be delete sql");
}
return this.executeNonQuery(sql);
}
catch (Exception ex)
{
throw ex;
}

}

public DataTable select(string sql)
{
sql = sql.Trim();
// check the sql
if (sql.Substring(0, 6).ToUpper() != "SELECT")
{
throw new Exception("Sql must be select sql");
}

DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();

cmd.Connection = this.cn;
cmd.CommandText = sql;
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmd;
adp.Fill(ds);
return ds.Tables[0];
}

public bool isExists(string sql)
{
DataTable dt = this.select(sql);
if (dt.Rows.Count > 0)
{
return true;
}
return false;
}


protected int executeNonQuery(string sql)
{

SqlCommand cmd = new SqlCommand();
cmd.Connection = this.cn;
cmd.CommandText = sql;
if (this.transaction_started)
{
cmd.Transaction = tx;
}
return cmd.ExecuteNonQuery();
}

protected object executeScalar(string sql)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection=this.cn;
cmd.CommandText=sql;

if (this.transaction_started)
{
cmd.Transaction = tx;
}
return cmd.ExecuteScalar();
}

public object getLastInsertId()
{
string sql = "select SCOPE_IDENTITY();";
return this.executeScalar(sql);

}


}


}