/// <summary>
/// Lấy về DataReader
/// </summary>
/// <param name="sqlQuery">Câu truy vấn lấy dữ liệu</param>
/// <param name="sqlConn">Đối tượng kết nối cần sử dụng</param>
/// <param name="paramArr">Mảng chứa các tham số truyền cho câu truy vấn </param>
/// <param name="sqlCT">Kiểu truy vấn: Text hoặc Stored Procedure</param>
/// <returns>Đối tượng DataReader chứa dữ liệu cần lấy</returns>
public SqlDataReader ExecuteReader(string sqlQuery, SqlParameter[] paramArr, CommandType sqlCT)
{
if ((_OpenConnection() == false)) // Open Connection
{
return(null);
}
//Tạo đối tượng dbCommand với câu lệnh SQL truyền vào và thiết lập tham số
SqlCommand sqlCmd = CreateDbCommand(sqlQuery, paramArr, sqlCT);
try
{
/*Log before execute*/
CLog.LogSQL(GetParamInfo(), sqlQuery);
m_DataReader = sqlCmd.ExecuteReader();
sqlCmd.Parameters.Clear();
sqlCmd.Dispose();
}
catch (Exception ex)
{
/*Log error execute*/
CLog.LogError(GetParamInfo(), ex.Message);
m_DataReader = null;
throw ex;
}
finally
{
_CloseConnection();
}
return(m_DataReader);
}
/// <summary>
/// Thực hiện các thao tác Insert, Update và Delete dữ liệu trong CSDL
/// với các câu lệnh truy vấn SQL có sử dụng tham số
/// </summary>
/// <param name="sqlQuery">Câu lệnh truy vấn SQL</param>
/// <param name="paramArr">Mảng tham số truyền vào cho câu lệnh SQL</param>
/// <param name="sqlCT" >Kiểu Store Procedure hay kiểu Text</param>
public bool ExecuteNonQuery(string sqlQuery, SqlParameter[] paramArr, CommandType sqlCT)
{
bool functionReturnValue = false;
if ((_OpenConnection() == false)) // Open Connection
{
return(false);
}
//Tạo đối tượng dbCommand với câu lệnh SQL truyền vào và thiết lập tham số
SqlCommand sqlCmd = CreateDbCommand(sqlQuery, paramArr, sqlCT);
try
{
/*Log before execute*/
CLog.LogSQL(GetParamInfo(), sqlQuery);
//Thực thi câu lệnh truy vấn
sqlCmd.ExecuteNonQuery();
sqlCmd.Parameters.Clear();
functionReturnValue = true;
}
catch (SqlException ex)
{
/*Log error execute*/
CLog.LogError(GetParamInfo(), ex.Message);
functionReturnValue = false;
throw ex;
}
finally {
_CloseConnection();
}
return(functionReturnValue);
}
/// <summary>
/// Thuc hien 1 store procedure (co tham so) tra ve 1 DataReader
/// Input:
/// 1. spName: ten store
/// 2. myParamArray() : danh sach cac tham so
/// Output: DataReader
/// </summary>
/// <param name="SPname">sp_SELECT</param>
/// <returns></returns>
public SqlDataReader GetDataReaderFromSP(string SPname)
{
if ((_OpenConnection() == false)) // Open Connection
{
return(null);
}
m_Command.Connection = m_Connection;
m_Command.CommandText = SPname;
m_Command.CommandType = CommandType.StoredProcedure;
try
{
CLog.LogSQL(CBase.GetDeepCaller(), SPname + this.GetParamInfo()); // log SQL
DateTime dtBegin = DateTime.Now; // duration
m_DataReader = m_Command.ExecuteReader();
this.m_dblDuration = DateTime.Now.Subtract(dtBegin).TotalMilliseconds; // duration
m_Command.Parameters.Clear();
return(m_DataReader);
}
catch (Exception ex)
{
CLog.LogError(CBase.GetDeepCaller(), CBase.GetDetailError(ex));
return(null);
}
finally
{
//_CloseConnection(); // _CloseConnection thi error, phai goi CloseConnection tu ben ngoai (Caller)
}
}
/// <summary>
/// Thuc hien 1 câu lệnh sql tra ve 1 DataReader
/// Input:
/// 1. câu lệnh sql
/// Output: DataReader
/// </summary>
/// <param name="SQL">select * from tbl_test</param>
/// <returns></returns>
public SqlDataReader GetDataReaderFromQuery(string myQuery)
{
if ((_OpenConnection() == false)) // Open Connection
{
return(null);
}
m_Command.Connection = m_Connection;
m_Command.CommandText = myQuery;
m_Command.CommandType = CommandType.Text;
try
{
CLog.LogSQL(CBase.GetDeepCaller(), myQuery); // log SQL
DateTime dtBegin = DateTime.Now; // duration
m_DataReader = m_Command.ExecuteReader();
this.m_dblDuration = DateTime.Now.Subtract(dtBegin).TotalMilliseconds; // duration
return(m_DataReader);
}
catch (Exception ex)
{
CLog.LogError(CBase.GetDeepCaller(), CBase.GetDetailError(ex));
return(null);
}
finally
{
//_CloseConnection(); // _CloseConnection thi error, phai goi CloseConnection tu ben ngoai (Caller)
}
}
/// <summary>
/// Thuc hien 1 cau query tra ve 1 dataset
/// Input:
/// 1. Ten store procedure
/// Output: dataset
/// </summary>
/// <returns></returns>
public DataSet GetDatasetFromSP(string SPname)
{
DataSet functionReturnValue = null;
if ((_OpenConnection() == false)) // Open Connection
{
return(null);
}
DataSet ds = new DataSet();
try
{
CLog.LogSQL(CBase.GetDeepCaller(), SPname + this.GetParamInfo()); // log SQL
m_Command.Connection = m_Connection;
m_Command.CommandText = SPname;
m_Command.CommandType = CommandType.StoredProcedure;
DateTime dtBegin = DateTime.Now; // duration
m_DataAdapter = new SqlDataAdapter(m_Command);
m_DataAdapter.Fill(ds);
this.m_dblDuration = DateTime.Now.Subtract(dtBegin).TotalMilliseconds; // duration
m_Command.Parameters.Clear();
functionReturnValue = ds;
}
catch (Exception ex)
{
functionReturnValue = null;
CLog.LogError(CBase.GetDeepCaller(), CBase.GetDetailError(ex));
}
finally
{
_CloseConnection(); // Close Connection
}
return(functionReturnValue);
}
/// <summary>
/// ExecuteSP
/// Thuc hien 1 store procedure Insert, Update, Delete
/// Input: 1. spName: ten store
/// 2. myParamArray() : mang cac tham so truyen va
/// Output: Boolean (thanh cong hay khong ?)
/// </summary>
/// <param name="SPname">sp_DELETE_ALL</param>
/// <returns></returns>
public bool ExecuteSP(string SPname)
{
bool functionReturnValue = false;
if ((_OpenConnection() == false)) // Open Connection
{
return(false);
}
m_Command.Connection = m_Connection;
m_Command.CommandText = SPname;
m_Command.CommandType = CommandType.StoredProcedure;
try
{
CLog.LogSQL(CBase.GetDeepCaller(), SPname + this.GetParamInfo()); // log SQL
DateTime dtBegin = DateTime.Now; // duration
m_Command.ExecuteNonQuery();
this.m_dblDuration = DateTime.Now.Subtract(dtBegin).TotalMilliseconds; // duration
m_Command.Parameters.Clear();
functionReturnValue = true;
}
catch (Exception ex)
{
functionReturnValue = false;
CLog.LogError(CBase.GetDeepCaller(), CBase.GetDetailError(ex));
}
finally
{
_CloseConnection(); // Close Connection
}
return(functionReturnValue);
}
//===============================================================================================================
#region Execute
/// <summary>
/// ExecuteQuery
/// Thuc hien 1 cau lenh Insert, Update, Delete, hay 1 procedure = cach viet cau lenh "exec sp_fgsd"
/// Input: cau lenh sql
/// Output: Boolean (thanh cong hay khong ?)
/// </summary>
/// <param name="SQL">delect * from tbl_test</param>
/// <param name="SQL">exec sp_fgsd</param>
/// <returns></returns>
public bool ExecuteQuery(string myQuery)
{
bool functionReturnValue = false;
if ((_OpenConnection() == false)) // Open Connection
{
return(false);
}
m_Command.CommandTimeout = this.m_CommandTimeout;
m_Command.Connection = m_Connection;
m_Command.CommandText = myQuery;
m_Command.CommandType = CommandType.Text;
try
{
CLog.LogSQL(CBase.GetDeepCaller(), myQuery); // log SQL
DateTime dtBegin = DateTime.Now; // duration
m_Command.ExecuteNonQuery();
this.m_dblDuration = DateTime.Now.Subtract(dtBegin).TotalMilliseconds; // duration
functionReturnValue = true;
}
catch (Exception ex)
{
functionReturnValue = false;
CLog.LogError(CBase.GetDeepCaller(), CBase.GetDetailError(ex));
}
finally
{
_CloseConnection(); // Close Connection
}
return(functionReturnValue);
}
/// <summary>
/// Thực thi các câu lệnh SQL
/// </summary>
/// <param name="sqlQuery">Câu lệnh SQL</param>
/// <param name="paramArr">Mảng chứa các tham số trong câu lệnh</param>
/// <param name="sqlCT" >Kiểu Store Procedure hay kiểu text</param>
/// <returns>Đối tượng DataSet chứa kết quả truy vấn</returns>
public DataSet ExecuteQuery(string sqlQuery, SqlParameter[] paramArr, CommandType sqlCT)
{
DataSet functionReturnValue = null;
if (_OpenConnection() == false) // Open Connection
{
return(null);
}
DataSet sqlDS123 = new DataSet();
//Tạo đối tượng dbCommand với câu lệnh SQL truyền vào và thiết lập tham số
SqlCommand sqlCmd = CreateDbCommand(sqlQuery, paramArr, sqlCT);
try
{
/*Log before execute*/
CLog.LogSQL(GetParamInfo(), sqlQuery);
sqlCmd.CommandTimeout = m_CommandTimeout;
m_DataAdapter = new SqlDataAdapter();
m_DataAdapter.SelectCommand = sqlCmd;
m_DataAdapter.Fill(sqlDS123);
sqlCmd.Parameters.Clear();
functionReturnValue = sqlDS123;
}
catch (Exception ex)
{
/*Log error execute*/
CLog.LogError(GetParamInfo(), ex.Message);
functionReturnValue = null;
throw ex;
}
finally {
_CloseConnection();
}
return(functionReturnValue);
}
/// <summary>
/// Thuc hien 1 cau query tra ve 1 dataset
/// Input: 1.cau lenh Sql
/// Output: dataset
/// </summary>
/// <param name="SQL">select * from tbl_1 select * from tbl_2</param>
/// <returns></returns>
public DataSet GetDatasetFromQuery(string myQuery)
{
DataSet functionReturnValue = null;
if ((_OpenConnection() == false)) // Open Connection
{
return(null);
}
DataSet ds = new DataSet();
try
{
CLog.LogSQL(CBase.GetDeepCaller(), myQuery); // log SQL
m_Command.Connection = m_Connection;
m_Command.CommandText = myQuery;
m_Command.CommandType = CommandType.Text;
DateTime dtBegin = DateTime.Now; // duration
m_DataAdapter = new SqlDataAdapter(m_Command);
m_DataAdapter.Fill(ds);
this.m_dblDuration = DateTime.Now.Subtract(dtBegin).TotalMilliseconds; // duration
functionReturnValue = ds;
}
catch (Exception ex)
{
functionReturnValue = null;
CLog.LogError(CBase.GetDeepCaller(), CBase.GetDetailError(ex));
}
finally
{
_CloseConnection(); // Close Connection
}
return(functionReturnValue);
}