/// <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); }