public class Database
{
#region Private Var
private DbProviderFactory factory;
private DbConnection conn;
private DbTransaction trans;
private bool transFlag;
#endregion
#region Private Function
private void InitFactory()
{
if (String.IsNullOrEmpty(this.ProviderName)) { throw new Exception("Attribute ProviderName can't be empty"); }
if (String.IsNullOrEmpty(this.ConnectionString)) { throw new Exception("Attribute ConnectionString can't be empty"); }
factory = DbProviderFactories.GetFactory(this.ProviderName);
conn = factory.CreateConnection();
conn.ConnectionString = this.ConnectionString;
}
#endregion
#region Attributes
public string ConnectionString { get; set; }
public string ProviderName { get; set; }
public bool GlobalTransaction { get { return transFlag; } }
public DbProviderFactory Factory { get { return factory; } }
#endregion
#region Constructor
public Database()
{
}
public Database(string connectionString)
{
this.ConnectionString = connectionString;
}
public Database(string connectionString, string providerName)
: this(connectionString)
{
this.ProviderName = providerName;
this.InitFactory();
}
#endregion
#region Public Function
#region Open & Close
public void Open()
{
try
{
if (factory == null) { this.InitFactory(); }
conn.Open();
}
catch (Exception ex) { throw ex; }
}
public void Close()
{
try { if (conn != null) { conn.Close(); } }
catch (Exception ex) { throw ex; }
}
#endregion
#region Transaction
public void BeginTransaction()
{
try
{
this.Open();
trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
transFlag = true;
}
catch (Exception ex) { throw ex; }
}
public void CommitTransaction()
{
try
{
trans.Commit();
transFlag = false;
}
catch (Exception ex) { throw ex; }
}
public void RollbackTransaction()
{
try
{
trans.Rollback();
transFlag = false;
}
catch (Exception ex) { throw ex; }
}
#endregion
#region Execute
public int Execute(string sqlText)
{
return Execute(sqlText, null);
}
public int Execute(string sqlText, IDataParameter[] parameterArray)
{
return Execute(new string[] { sqlText }, new IDataParameter[][] { parameterArray });
}
public int Execute(string[] sqlArray, IDataParameter[][] parameterArray)
{
try
{
int resultRows = 0;
if (sqlArray.Length != parameterArray.Length) { throw new Exception("Parameter length is inconsistent"); }
if (conn.State != ConnectionState.Open) { conn.Open(); }
if (!transFlag || trans == null) { trans = conn.BeginTransaction(IsolationLevel.ReadCommitted); }
DbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
for (int i = 0; i < sqlArray.Length; i++)
{
if (parameterArray[i] != null) { cmd.Parameters.AddRange(parameterArray[i]); }
cmd.CommandText = sqlArray[i];
resultRows += cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (!transFlag) { trans.Commit(); }
return resultRows;
}
catch (Exception ex)
{
if (trans != null) { trans.Rollback(); }
throw ex;
}
finally { if (!transFlag) { conn.Close(); trans = null; } }
}
#endregion
#region DataSet
public void DataSet(DataSet ds, string sqlText)
{
DataSet(ds, sqlText, null);
}
public void DataSet(DataSet ds, string sqlText, IDataParameter[] parameterArray)
{
DataSet(ds, sqlText, parameterArray, null);
}
public void DataSet(DataSet ds, string[] sqlArray, IDataParameter[][] parameterArray)
{
DataSet(ds, sqlArray, parameterArray, null);
}
public void DataSet(DataSet ds, string sqlText, IDataParameter[] parameterArray, string tableName)
{
DataSet(ds, new string[] { sqlText }, new IDataParameter[][] { parameterArray }, tableName == null ? null : new string[] { tableName });
}
public void DataSet(DataSet ds, string[] sqlArray, IDataParameter[][] parameterArray, string[] nameList)
{
try
{
if (parameterArray != null && sqlArray.Length != parameterArray.Length) { throw new Exception("Parameter length([sqlArray] and [parameterArray]) is inconsistent"); }
if (nameList != null && sqlArray.Length != nameList.Length) { throw new Exception("Parameter length([sqlArray] and [nameList]) is inconsistent"); }
if (conn.State != ConnectionState.Open) { this.Open(); }
if (trans == null) { trans = conn.BeginTransaction(IsolationLevel.ReadCommitted); }
DbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
DbDataAdapter da = factory.CreateDataAdapter();
da.SelectCommand = cmd;
for (int i = 0; i < sqlArray.Length; i++)
{
if (parameterArray != null && parameterArray[i] != null) { cmd.Parameters.AddRange(parameterArray[i]); }
cmd.CommandText = sqlArray[i];
string tableName = "Table" + i.ToString();
if (nameList != null && !String.IsNullOrEmpty(nameList[i])) { tableName = nameList[i]; }
da.Fill(ds, tableName);
cmd.Parameters.Clear();
}
if (!transFlag) { trans.Commit(); }
}
catch (Exception ex)
{
if (trans != null) { trans.Rollback(); }
throw ex;
}
finally { if (!transFlag) { conn.Close(); trans = null; } }
}
#endregion
#region Create DB Item
public DbParameter CreateDbParameter(string parameterName, object value)
{
DbParameter parameter = factory.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = value;
return parameter;
}
#endregion
#endregion
}