/// <summary>
/// Lấy dữ liệu từ câu lệnh chứa trong thuộc tính Command
/// </summary>
/// <returns></returns>
private DataSet getData() //Get data with SQLcommand
{
Connect();
trans = conn.BeginTransaction();
DataSet ds = new DataSet();
cmd.Connection = conn;
cmd.CommandText = Command;
cmd.CommandType = Commandtype;
cmd.Transaction = trans;
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd);
try
{
adapter.Fill(ds);
trans.Commit();
Disconnect();
return ds;
}
catch (NpgsqlException ex)
{
Error = ex.Message;
trans.Rollback();
}
return null;
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
PostgreSqlClient.NpgsqlConnection pgConnection = null;
PostgreSqlClient.NpgsqlTransaction pgTransaction = null;
try
{
// Create a new connection.
using (pgConnection = new PostgreSqlClient.NpgsqlConnection(connectionString))
{
// Open the connection.
pgConnection.Open();
// Start a new transaction.
pgTransaction = pgConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new PostgreSqlClient.NpgsqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.PostgreSqlDataType, commandText), pgConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = pgTransaction;
if (values != null)
{
foreach (PostgreSqlClient.NpgsqlParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
pgTransaction.Commit();
// Close the database connection.
pgConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (pgTransaction != null)
{
pgTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (pgConnection != null)
{
pgConnection.Close();
}
}
}
/// <summary>
/// thực thi SQL, thường dùng cho các dòng lệnh MAX,AVG,COUNT,.....
/// </summary>
/// <returns>Object</returns>
private object executeScalar()// Execute a command return only 1 value
{
Connect();
trans = conn.BeginTransaction();
cmd.CommandText = Command;
cmd.CommandType = Commandtype;
cmd.Transaction = trans;
cmd.Connection = conn;
object obj = null;
try
{
obj = cmd.ExecuteScalar();
trans.Commit();
}
catch (NpgsqlException ex)
{
trans.Rollback();
if (ex.Code == "23505")
{
if(ex.BaseMessage.Contains("quyet_dinh"))
throw new Exception("Mã quyết định đã tồn tại, xin vui lòng kiểm tra lại.");
else if(ex.BaseMessage.Contains("ma_nv"))
throw new Exception("Mã nhân viên đã tồn tại, xin vui lòng kiểm tra lại.");
}
throw new Exception(ex.Message);
}
Disconnect();
return obj;
}
/// <summary>
/// Thực Thi các câu lệnh SQL (thường là UPDATE)
/// </summary>
/// <returns>giá trị các dòng có ảnh hưởng</returns>
private int executeNonQuery()// Execute a command without return
{
Connect();
trans = conn.BeginTransaction();
cmd.CommandText = Command;
cmd.CommandType = Commandtype;
cmd.Connection = conn;
cmd.Transaction = trans;
int rowEffect = 0;
try
{
rowEffect = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (NpgsqlException ex)
{
Error = ex.Message;
trans.Rollback();
}
Disconnect();
return rowEffect;
}
/// <summary>
/// Lấy Dữ Liệu Từng Dòng
/// </summary>
/// <returns>IDataReader</returns>
private IDataReader getDataReader() // Đọc dữ liệu dạng Datareader
{
Connect();
trans = conn.BeginTransaction();
NpgsqlDataReader datard = null;
try
{
cmd.CommandText = Command;
cmd.CommandType = Commandtype;
cmd.Transaction = trans;
datard = cmd.ExecuteReader(CommandBehavior.CloseConnection);
trans.Commit();
}
catch (NpgsqlException ex)
{
Error = ex.Message;
trans.Rollback();
}
// Disconnect();
return datard;
}
public static void ReleaseTransaction(ref NpgsqlTransaction pPgsTrans)
{
try
{
pPgsTrans.Commit();
pPgsTrans.Dispose();
pPgsTrans = null;
GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ReleaseTransaction");
}
}
public void commit()
{
trans.Commit();
trans = con.BeginTransaction(IsolationLevel.Chaos);
}
