Using DBFactory Classes

public class DataAccess
{
private string _DBProviderName;
public string DBProviderName
{
get { return _DBProviderName; }
set { _DBProviderName = value; }
}
private string _ConnectionString;
public string ConnectionString
{
get { return _ConnectionString; }
set { _ConnectionString = value; }
}
private DbProviderFactory _dpf;
private DbProviderFactory GetDBProviderFactory()
{
return _dpf;
}
public string GetParameterChar( )
{
string ret=string.Empty;
switch (_DBProviderName)
{
case "System.Data.OracleClient":
ret = ":";
break;
case "System.Data.SqlClient":
ret = "@";
break;
}
return ret;
}
public string GetDbType()
{
string ret = string.Empty;
switch (_DBProviderName)
{
case "System.Data.OracleClient":
ret = "Oracle";
break;
case "System.Data.SqlClient":
ret = "SQL";
break;
}
return ret;
}
public string GetDBIdColumn(string columnName)
{
string ret = string.Empty;
switch (_DBProviderName)
{
case "System.Data.OracleClient":
ret = columnName + ",";
break;
case "System.Data.SqlClient":
ret = "";
break;
}
return ret;
}
public string GetDBSequence(string columnName,out decimal idValue,DbTransaction trans)
{
string ret = string.Empty;
idValue = 0;
switch (_DBProviderName)
{
case "System.Data.OracleClient":
DataAccess db = new DataAccess();
DbCommand cmd = db.GetDBCommand();
DataTable dt = null;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT SEQ_" + columnName + ".NEXTVAL FROM DUAL";
try
{
dt = db.GetDataTableTrans(cmd, trans);
idValue = decimal.Parse(dt.Rows[0][0].ToString());
ret = idValue.ToString() + ",";
}
catch
{
ret = "";
idValue = 0;
}
break;
case "System.Data.SqlClient":
ret = "";
idValue = 0;
break;
}
return ret;
}
public string GetDBIdentity(string columnName, out decimal idValue, DbTransaction trans)
{
string ret = string.Empty;
idValue = 0;
switch (_DBProviderName)
{
case "System.Data.OracleClient":
ret = "";
idValue = 0;
break;
case "System.Data.SqlClient":
DataAccess db = new DataAccess();
DbCommand cmd = db.GetDBCommand();
DataTable dt = null;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT @@IDENTITY";
try
{
dt = db.GetDataTableTrans(cmd, trans);
idValue = decimal.Parse(dt.Rows[0][0].ToString());
ret = idValue.ToString();
}
catch
{
ret = "";
idValue = 0;
}
break;
}
return ret;
}
public DataAccess(string ProviderName, string ConnectionString)
{
_DBProviderName = ProviderName;
_ConnectionString = ConnectionString;
if (DbProviderFactories.GetFactoryClasses().Select("InvariantName='" + _DBProviderName + "'").Length == 0)
{
throw new Exception("Invalid .NET Data Provider specification: " + _DBProviderName);
return;
}
_dpf = DbProviderFactories.GetFactory(_DBProviderName);
}
public DataAccess()
{
_DBProviderName = global::Global.Utility.Properties.Settings.Default.ProviderName;
_ConnectionString = global::Global.Utility.Properties.Settings.Default.ConnectionString;
if (DbProviderFactories.GetFactoryClasses().Select("InvariantName='" + _DBProviderName + "'").Length == 0)
{
throw new Exception("Invalid .NET Data Provider specification: " + _DBProviderName);
return;
}
_dpf = DbProviderFactories.GetFactory(_DBProviderName);
}
private DataTable GetDataTable(string sqlSELECT)
{
DataTable dt = new DataTable();
DbDataAdapter da = GetDBDataAdapter();
DbCommand cmd = GetDBCommand();
switch (GetDbType())
{
case "Oracle":
sqlSELECT = sqlSELECT.Replace('@', ':');
break;
}
cmd.CommandText = sqlSELECT;
cmd.CommandType = CommandType.Text;
//SONRADAN EKLENDİ HATALI İŞLEMLERE HANGİ KULLANICIN SEBEP OLDUĞUNU TESPİT ETMEK İÇİN
//SORUNLAR GİDERİLDİKTEN SONRA SİLİNEBİLİR! - 28.09.2011 KADİR
decimal idKullanici = 0;
try
{
idKullanici = Convert.ToDecimal(HttpContext.Current.Session["UserId"].ToString());
}
catch
{
idKullanici = 0;
}
cmd.CommandText += " /*USERID = " + idKullanici.ToString() + "*/ ";
//END
da.SelectCommand = cmd;
try
{
da.Fill(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message + "-->" + sqlSELECT);
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Dispose();
da.Dispose();
}
return dt;
}
public DataTable GetDataTableTrans(DbCommand cmd,DbTransaction trans)
{
//SONRADAN EKLENDİ HATALI İŞLEMLERE HANGİ KULLANICIN SEBEP OLDUĞUNU TESPİT ETMEK İÇİN
//SORUNLAR GİDERİLDİKTEN SONRA SİLİNEBİLİR! - 28.09.2011 KADİR
decimal idKullanici = 0;
try
{
idKullanici = Convert.ToDecimal(HttpContext.Current.Session["UserId"].ToString());
}
catch
{
idKullanici = 0;
}
cmd.CommandText += " /*USERID = " + idKullanici.ToString() + "*/ ";
//END
DataTable dt = new DataTable();
DbDataAdapter da = GetDBDataAdapter();
DbConnection cn = null;
switch (GetDbType())
{
case "Oracle":
cmd.CommandText = cmd.CommandText.Replace('@', ':');
break;
}
if (trans == null)
{
cn = GetDBConnection();
cn.Open();
}
else
{
cn = trans.Connection;
cmd.Transaction = trans;
}
cmd.CommandTimeout = 0;
cmd.Connection = cn;
da.SelectCommand = cmd;
try
{
da.Fill(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message + "-->" + cmd.CommandText);
}
finally
{
if (trans == null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
cmd.Dispose();
da.Dispose();
}
return dt;
}
public DataSet GetDataSetTrans(DbCommand cmd, DbTransaction trans)
{
//SONRADAN EKLENDİ HATALI İŞLEMLERE HANGİ KULLANICIN SEBEP OLDUĞUNU TESPİT ETMEK İÇİN
//SORUNLAR GİDERİLDİKTEN SONRA SİLİNEBİLİR! - 28.09.2011 KADİR
decimal idKullanici = 0;
try
{
idKullanici = Convert.ToDecimal(HttpContext.Current.Session["UserId"].ToString());
}
catch
{
idKullanici = 0;
}
cmd.CommandText += " /*USERID = " + idKullanici.ToString() + "*/ ";
//END
DbDataAdapter da = GetDBDataAdapter();
DbConnection cn = null;
DataSet ds = new DataSet();
switch (GetDbType())
{
case "Oracle":
cmd.CommandText = cmd.CommandText.Replace('@', ':');
break;
}
if (trans == null)
{
cn = GetDBConnection();
cn.Open();
}
else
{
cn = trans.Connection;
cmd.Transaction = trans;
}
cmd.Connection = cn;
cmd.CommandTimeout = 0;
da.SelectCommand = cmd;
try
{
da.Fill(ds);
}
catch (Exception ex)
{
throw new Exception(ex.Message + "-->" + cmd.CommandText);
}
finally
{
if (trans == null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
cmd.Dispose();
da.Dispose();
}
return ds;
}
public long SQLExecuteTrans(DbCommand cmd, DbTransaction trans)
{
//SONRADAN EKLENDİ HATALI İŞLEMLERE HANGİ KULLANICIN SEBEP OLDUĞUNU TESPİT ETMEK İÇİN
//SORUNLAR GİDERİLDİKTEN SONRA SİLİNEBİLİR! - 28.09.2011 KADİR
decimal idKullanici = 0;
try
{
idKullanici = Convert.ToDecimal(HttpContext.Current.Session["UserId"].ToString());
}
catch
{
idKullanici = 0;
}
cmd.CommandText += " /*USERID = " + idKullanici.ToString() + "*/ ";
//END
DbConnection cn = null;
long result = 0;
switch (GetDbType())
{
case "Oracle":
cmd.CommandText = cmd.CommandText.Replace('@', ':');
break;
}
if (trans == null)
{
cn = GetDBConnection();
//cmd.Connection.ConnectionString = "Data Source=10.86.77.161;Initial Catalog=KARTAL2010PROD;Persist Security Info=True;User ID=sa;Password=sqlKartal123123";
cn.Open();
}
else
{
cn = trans.Connection;
cmd.Transaction = trans;
}
cmd.Connection = cn;
try
{
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message + "-->" + cmd.CommandText);
}
finally
{
if (trans == null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
cmd.Dispose();
}
return result;
}
public void SQLExecute(string sql)
{
DbCommand cmd = GetDBCommand();
switch (GetDbType())
{
case "Oracle":
sql = sql.Replace('@', ':');
break;
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
//SONRADAN EKLENDİ HATALI İŞLEMLERE HANGİ KULLANICIN SEBEP OLDUĞUNU TESPİT ETMEK İÇİN
//SORUNLAR GİDERİLDİKTEN SONRA SİLİNEBİLİR! - 28.09.2011 KADİR
decimal idKullanici = 0;
try
{
idKullanici = Convert.ToDecimal(HttpContext.Current.Session["UserId"].ToString());
}
catch
{
idKullanici = 0;
}
cmd.CommandText += " /*USERID = " + idKullanici.ToString() + "*/ ";
//END
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message + "-->" + sql);
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Dispose();
}
}
public long SQLExecute(string sql, DbTransaction trans)
{
DbConnection cn = null;
long result = 0;
DbCommand cmd = GetDBCommand();
switch (GetDbType())
{
case "Oracle":
sql = sql.Replace('@', ':');
break;
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
//SONRADAN EKLENDİ HATALI İŞLEMLERE HANGİ KULLANICIN SEBEP OLDUĞUNU TESPİT ETMEK İÇİN
//SORUNLAR GİDERİLDİKTEN SONRA SİLİNEBİLİR! - 28.09.2011 KADİR
decimal idKullanici = 0;
try
{
idKullanici = Convert.ToDecimal(HttpContext.Current.Session["UserId"].ToString());
}
catch
{
idKullanici = 0;
}
cmd.CommandText += " /*USERID = " + idKullanici.ToString() + "*/ ";
//END
if (trans == null)
{
cn = GetDBConnection();
cn.Open();
}
else
{
cn = trans.Connection;
cmd.Transaction = trans;
}
cmd.Connection = cn;
try
{
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message + "-->" + cmd.CommandText);
}
finally
{
if (trans == null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
cmd.Dispose();
}
return result;
}
public DataRow GetDataRow(DbCommand cmd, DbTransaction trans)
{
//SONRADAN EKLENDİ HATALI İŞLEMLERE HANGİ KULLANICIN SEBEP OLDUĞUNU TESPİT ETMEK İÇİN
//SORUNLAR GİDERİLDİKTEN SONRA SİLİNEBİLİR! - 28.09.2011 KADİR
decimal idKullanici = 0;
try
{
idKullanici = Convert.ToDecimal(HttpContext.Current.Session["UserId"].ToString());
}
catch
{
idKullanici = 0;
}
cmd.CommandText += " /*USERID = " + idKullanici.ToString() + "*/ ";
//END
DataTable dt = GetDataTableTrans(cmd,trans);
if (dt.Rows.Count > 0)
{
return dt.Rows[0];
}
else
{
return null;
}
}
public string GetValue(DbCommand cmd, DbTransaction trans)
{
//SONRADAN EKLENDİ HATALI İŞLEMLERE HANGİ KULLANICIN SEBEP OLDUĞUNU TESPİT ETMEK İÇİN
//SORUNLAR GİDERİLDİKTEN SONRA SİLİNEBİLİR! - 28.09.2011 KADİR
decimal idKullanici = 0;
try
{
idKullanici = Convert.ToDecimal(HttpContext.Current.Session["UserId"].ToString());
}
catch
{
idKullanici = 0;
}
cmd.CommandText += " /*USERID = " + idKullanici.ToString() + "*/ ";
//END
DbConnection cn = null;
string result;
if (trans == null)
{
cn = GetDBConnection();
cn.Open();
}
else
{
cn = trans.Connection;
cmd.Transaction = trans;
}
cmd.Connection = cn;
try
{
result = cmd.ExecuteScalar().ToString() + "";
}
catch (Exception ex)
{
throw new Exception(ex.Message + "-->" + cmd.CommandText);
}
finally
{
if (trans == null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
cmd.Dispose();
}
return result;
}
public DbConnection GetDBConnection()
{
DbConnection dbConn = GetDBProviderFactory().CreateConnection();
dbConn.ConnectionString = _ConnectionString;
return dbConn;
}
public DbCommand GetDBCommand()
{
DbCommand dbCmd = GetDBProviderFactory().CreateCommand();
dbCmd.Connection = GetDBConnection();
return dbCmd;
}
public DbDataAdapter GetDBDataAdapter()
{
DbDataAdapter dbAdap = GetDBProviderFactory().CreateDataAdapter();
return dbAdap;
}
public DbParameter GetDBParameter()
{
DbParameter dbAdap = GetDBProviderFactory().CreateParameter();
return dbAdap;
}
public DbTransaction GetTransaction()
{
DbConnection dbConn = GetDBProviderFactory().CreateConnection();
dbConn.ConnectionString = _ConnectionString;
dbConn.Open();
DbTransaction dbTrans = dbConn.BeginTransaction();
return dbTrans;
}
}