public IDataReader ExecDataReader(string strSQL, CommandType cmdType, ParamCollection parameterArray)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection();
try
{
PrepareAll(ref cmd, ref conn, strSQL, cmdType, parameterArray);
return(cmd.ExecuteReader());
}
catch (Exception ex)
{
if ((!IsInTransaction()) && (conn != null))
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
}
GenericExceptionHandler(ex, strSQL, parameterArray);
}
return(null);
}
private void ODPExceptionHandler(SqlException ex, string SQLCommandText, ParamCollection Params)
{
//SqlInfoMessageEventArgs sqler = default(SqlInfoMessageEventArgs);
StringBuilder sb = new StringBuilder();
throw new Exception(ex.ToString());
}
public DataAccess(string ConnectionStringItem)
{
_isolationLevel = IsolationLevel.ReadCommitted;
_connString = GetConnectionString(ConnectionStringItem);
_commandText = string.Empty;
_commandType = CommandType.Text;
_parameters = new ParamCollection();
}
public Int32 ExecNonQuery(string strSQL, CommandType cmdType, ParamCollection parameterArray)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection();
Int32 intRowAff = 0;
Int32 intCounter;
try
{
PrepareAll(ref cmd, ref conn, strSQL, cmdType, parameterArray);
intRowAff = cmd.ExecuteNonQuery();
if (intRowAff == -1)
{
intRowAff = 1;
}
intCounter = -1;
foreach (ParamStruct objParam in parameterArray)
{
intCounter += 1;
if (objParam.Direction == ParameterDirection.InputOutput || objParam.Direction == ParameterDirection.Output || objParam.Direction == ParameterDirection.ReturnValue)
{
parameterArray[intCounter].Value = ((IDataParameter)cmd.Parameters[objParam.ParamName]).Value;
}
}
return(intRowAff);
}
catch (Exception ex)
{
GenericExceptionHandler(ex, strSQL, parameterArray);
}
finally
{
if ((!IsInTransaction()) && (conn != null))
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
return(-1);
}
public DataTable ExecDataTable(string strSQL, CommandType cmdType, ParamCollection parameterArray)
{
DataTable tb = new DataTable();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
try
{
PrepareAll(ref cmd, ref conn, strSQL, cmdType, parameterArray);
//Fill_DataSet(ref cmd, ref ds);
da.SelectCommand = cmd;
da.Fill(ds);
if (ds != null && ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
else
{
ds = null;
}
}
catch (Exception ex)
{
GenericExceptionHandler(ex, strSQL, parameterArray);
}
finally
{
if ((!IsInTransaction()) && (conn != null))
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
da.Dispose();
if (ds != null)
{
ds.Dispose();
}
}
return(null);
}
private void GenericExceptionHandler(Exception ex, string SQLCommandText, ParamCollection Params)
{
ODPExceptionHandler((SqlException)ex, SQLCommandText, Params);
}
protected SqlCommand GetCommand(string strSQL, CommandType cmdType, ParamCollection ParameterArray)
{
SqlCommand cmd = new SqlCommand();
Int32 i = default(Int32);
Int32 j = default(Int32);
SqlParameter[] para_SP = null;
if (cmdType == CommandType.StoredProcedure)
{
para_SP = Get_SPParameterSet(strSQL);
}
if (para_SP != null)
{
//Duyệt từng tham số từ store procedure
//Duyệt tham số từ code
//Kiểm tra tham số giữa store và code có trùng tên không
//Nếu có gán dữ liệu cho tham số store là dữ liệu tham số code
//Ngược lại default nothing
for (i = 0; i <= para_SP.Length - 2; i++)
{
SqlParameter ps_SP = para_SP[i];
for (j = 0; j <= ParameterArray.Count - 1; j++)
{
ParamStruct ps_Code = ParameterArray[j];
if (ps_SP.ParameterName.ToUpper() == ps_Code.ParamName.ToUpper())
{
if (System.DBNull.Value == ps_Code.Value)
{
ps_SP.Value = null;
}
else
{
ps_SP.Value = ps_Code.Value;
}
break;
}
else
{
ps_SP.Value = null;
}
}
IDbDataParameter pm = GetParameter(ps_SP.ParameterName, ps_SP.Direction, ps_SP.Value, ps_SP.DbType, ps_SP.Size);
cmd.Parameters.Add(pm);
}
}
else
{
if (((ParameterArray != null)) && ParameterArray.Count > 0)
{
for (i = 0; i <= ParameterArray.Count - 1; i++)
{
ParamStruct ps = ParameterArray[i];
IDbDataParameter pm = GetParameter(ps.ParamName, ps.Direction, ps.Value, ps.DataType, ps.Size);
cmd.Parameters.Add(pm);
}
}
}
cmd.CommandType = cmdType;
cmd.CommandText = strSQL;
return(cmd);
}
public void PrepareAll(ref SqlCommand cmd, ref SqlConnection conn, string strSQL, CommandType cmdType, ParamCollection parameterArray)
{
if (!IsInTransaction())
{
conn = GetConnection(ConnectionString);
cmd = GetCommand(strSQL, cmdType, parameterArray);
cmd.Connection = conn;
conn.Open();
}
else
{
cmd = GetCommand(strSQL, cmdType, parameterArray);
cmd.Connection = _conn;
cmd.Transaction = _trans;
}
}
