/// <summary>
/// Transaction 結束
/// </summary>
/// <param name="trans">交易物件</param>
/// <param name="isCommit"></param>
public void TransactionEnd(bool isCommit = true)
{
if (_trans == null)
{
return;
}
try
{
if (isCommit)
{
_trans.Commit();
}
else
{
_trans.Rollback();
}
}
catch (Exception ex)
{
_trans.Rollback();
throw ex;
}
finally
{
_trans.Dispose();
_trans = null;
}
}
public static bool cadEntrega(CL_Entrega objEntrega, string token, string post, string situac, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
Conn.Open();
NpgsqlTransaction transaction = Conn.BeginTransaction();
try
{
string sql = "INSERT INTO entregas (e_awb, e_dataenco, e_datastat, e_status, e_diasprev, e_clicod, e_situac, e_id, e_identreg, e_modelo) " +
"VALUES " +
"(@e_awb, @e_dataenco, @e_datastat, @e_status, @e_diasprev, @e_clicod, @e_situac, @e_id, @e_identreg, @e_modelo)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, Conn, transaction);
cmd.Parameters.AddWithValue("e_awb", objEntrega.e_awb);
cmd.Parameters.AddWithValue("e_dataenco", objEntrega.e_dataenco);
cmd.Parameters.AddWithValue("e_datastat", objEntrega.e_datastat);
cmd.Parameters.AddWithValue("e_status", objEntrega.e_status);
cmd.Parameters.AddWithValue("e_diasprev", objEntrega.e_diasprev);
cmd.Parameters.AddWithValue("e_clicod", objEntrega.e_clicod);
cmd.Parameters.AddWithValue("e_situac", objEntrega.e_situac);
cmd.Parameters.AddWithValue("e_id", objEntrega.e_id);
cmd.Parameters.AddWithValue("e_identreg", objEntrega.e_idEntregador);
cmd.Parameters.AddWithValue("e_modelo", objEntrega.e_modelo);
cmd.ExecuteScalar();
if (DB_Umov.sincronizaApp(token, "schedule", post))
{
if (DB_Umov.attSituac(situac, con))
{
transaction.Commit();
return(true);
}
else
{
transaction.Rollback();
return(false);
}
}
else
{
transaction.Rollback();
return(false);
}
}
catch (Exception ex)
{
ex.ToString();
transaction.Rollback();
return(false);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
private clsResponse _ExecuteSQL(string pSQL, bool pWithTransaction, int pCommandTimeout)
{
clsResponse response = new clsResponse();
string msg = "";
////OleDbCommand command = new OleDbCommand();
NpgsqlCommand command = new NpgsqlCommand();
int count = 0;
try
{
if (pWithTransaction)
{
myTransaction_ = myConnection_.BeginTransaction();
}
if (pCommandTimeout > 0)
{
command.CommandTimeout = pCommandTimeout;
}
else
{
// 2018/01/13
command.CommandTimeout = 300;
}
command.Connection = myConnection_;
command.Transaction = myTransaction_;
command.CommandText = pSQL;
command.CommandType = CommandType.Text;
count = command.ExecuteNonQuery();
response.xSetReturn("-- ResultCount", count.ToString());
if (pWithTransaction)
{
myTransaction_.Commit();
}
}
catch (Exception ex)
{
if (pWithTransaction) myTransaction_.Rollback();
msg = "SQLの実行に失敗しました。" + Environment.NewLine;
msg += "(" + myDBInfo_.xDBName + ")" + "(" + pSQL + ")" + Environment.NewLine;
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
//////// MethodBase.GetCurrentMethod().Name, msg);
}
finally
{
if (command != null)
{
command.Dispose();
command = null;
}
}
return response;
}
public static bool ExecuteNonQuerysw(IList <string> sql)
{
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
using (NpgsqlTransaction tran = connection.BeginTransaction()) //开始数据库事务。即创建一个事务对象tran
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = connection;
cmd.Transaction = tran; //获取或设置将要其执行的事务
try
{
for (var i = 0; i < sql.Count; i++)
{
cmd.CommandText = sql[i];
int m = cmd.ExecuteNonQuery();
if (m < 1)
{
tran.Rollback();
return(false);
}
}
tran.Commit();
return(true);
}
catch (Exception ex)
{
return(false);
tran.Rollback();//如果执行不成功，发送异常，则执行rollback方法，回滚到事务操作开始之前。
}
finally
{
connection.Dispose();
}
}
}
//try
//{
// using (NpgsqlCommand cmd = connection.CreateCommand())
// {
// cmd.CommandText = sql;
// connection.Open();
// return Convert.ToInt32(cmd.ExecuteScalar());
// }
//}
//catch (Exception ex)
//{
// throw ex;
//}
//finally
//{
// connection.Dispose();
//}
}
}
public override bool TransactionRollback()
{
if (_transaction == null || _transaction.IsCompleted)
{
return(false);
}
_transaction.Rollback();
return(true);
}
public bool SaveFdReceipt(FDReceiptDTO ObjFdReceiptDTO, string ConnectionString, out string OUTReceiptid)
{
bool Issaved = false;
StringBuilder sbInsert = new StringBuilder();
bool IsAccountSaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (Savegenaralreceipt(ObjFdReceiptDTO, trans, out OUTReceiptid))
{
IsAccountSaved = true;
}
else
{
trans.Rollback();
return(IsAccountSaved);
}
string Receiptno = OUTReceiptid;
if (string.IsNullOrEmpty(ObjFdReceiptDTO.pRecordid.ToString()) || ObjFdReceiptDTO.pRecordid == 0)
{
sbInsert.Append("INSERT INTO public.fd_receipt(fd_receiptt_date,member_id, fd_account_id,deposit_type, instalment_amount, received_amount, mode_of_receipt,receipt_no,narration,status)VALUES('" + FormatDate(ObjFdReceiptDTO.pReceiptdate) + "'," + ObjFdReceiptDTO.pMemberid + "," + ObjFdReceiptDTO.pFdaccountid + ",'" + ObjFdReceiptDTO.pDeposittype + "'," + ObjFdReceiptDTO.pInstalmentamount + "," + ObjFdReceiptDTO.pReceivedamount + ",'" + ObjFdReceiptDTO.pModeofreceipt + "','" + Receiptno + "','" + ObjFdReceiptDTO.pNarration + "'," + ObjFdReceiptDTO.pStatus + ");");
}
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
if (ObjFdReceiptDTO.pModeofreceipt == "CASH")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "SELECT FN_PROMOTOR_SALARY_JV(" + ObjFdReceiptDTO.pFdaccountid + ",'" + FormatDate(ObjFdReceiptDTO.pReceiptdate) + "')");
}
trans.Commit();
Issaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
/// <summary>
/// 트랜잭션 롤백
/// </summary>
public void Rollback()
{
_trans.Rollback();
_trans = null;
if (_conn.State != System.Data.ConnectionState.Closed)
{
_conn.Close();
}
}
public void SequencialTransaction()
{
_conn.Open();
NpgsqlTransaction t = _conn.BeginTransaction();
t.Rollback();
t = _conn.BeginTransaction();
t.Rollback();
}
public bool SaveMemberReceipt(MemberReceiptDTO ObjMemberReceiptDTO, string ConnectionString, out string OUTReceiptid)
{
bool Issaved = false;
StringBuilder sbInsert = new StringBuilder();
bool IsAccountSaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (Savegenaralreceipt(ObjMemberReceiptDTO, trans, out OUTReceiptid))
{
IsAccountSaved = true;
}
else
{
trans.Rollback();
return(IsAccountSaved);
}
string Receiptno = OUTReceiptid;
ObjMemberReceiptDTO.pReceiptno = Receiptno;
if (string.IsNullOrEmpty(ObjMemberReceiptDTO.pRecordid.ToString()) || ObjMemberReceiptDTO.pRecordid == 0)
{
sbInsert.Append("insert into Member_receipt (member_id, receipt_date, received_amount, mode_of_receipt, receipt_no, narration, status) values (" + ObjMemberReceiptDTO.pMemberid + ", '" + FormatDate(ObjMemberReceiptDTO.pReceiptdate) + "', " + ObjMemberReceiptDTO.pReceivedamount + ", '" + ObjMemberReceiptDTO.pModeofreceipt + "', '" + ObjMemberReceiptDTO.pReceiptno + "', '" + ObjMemberReceiptDTO.pNarration + "', true);");
}
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
trans.Commit();
Issaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
private bool ExecuteSQLFromFile(string filename, string schema, bool transaction)
{
if (!File.Exists(filename))
{
Info = "Error." + Environment.NewLine +
"No se ha encontrado el fichero " + filename + ". ";
return(false);
}
string[] _lines = File.ReadAllLines(filename);
string lines = string.Empty;
if (schema != string.Empty)
{
lines = "SET search_path TO '" + schema + "';";
}
for (int i = 0; i < _lines.Length; i++)
{
if (!_lines[i].Trim().StartsWith("--"))
{
lines += _lines[i];
}
}
try
{
if (transaction)
{
_trans = _conn.BeginTransaction();
}
NpgsqlCommand command = new NpgsqlCommand(lines, _conn);
command.ExecuteNonQuery();
if (transaction)
{
_trans.Commit();
}
}
catch (Exception ex)
{
Info = Environment.NewLine + Environment.NewLine +
ex.Message + Environment.NewLine + Environment.NewLine +
"File: " + filename + Environment.NewLine + Environment.NewLine +
"Query: " + lines;
if (transaction)
{
_trans.Rollback();
}
return(false);
}
return(true);
}
public string nuevoArticulo(Articulo articulo, Stockcs stock, string schema)
{
string retorno = null;
List <Articulo> listArticulos = listaArticulos(schema);
bool existe = validarObjetoExistente(listArticulos, articulo.modelo.nombre, articulo.color.nombre, articulo.talle.nombre);
if (existe)
{
retorno = "El articulo ya existe";
return(retorno);
}
NpgsqlConnection conexion = null;
NpgsqlCommand cmd = null;
NpgsqlTransaction tran = null;
try
{
conexion = Conexion.getInstance().ConexionDB();
cmd = new NpgsqlCommand("logueo.spnuevoarticulo", conexion);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("parm_idmodelo", articulo.modelo.id);
cmd.Parameters.AddWithValue("parm_idtalle", articulo.talle.id);
cmd.Parameters.AddWithValue("parm_idcolor", articulo.color.id);
cmd.Parameters.AddWithValue("parm_preciomay", articulo.precio_may);
cmd.Parameters.AddWithValue("parm_preciomin", articulo.precio_min);
cmd.Parameters.AddWithValue("parm_codbarra", articulo.cod_barra);
cmd.Parameters.AddWithValue("parm_schema", schema);
conexion.Open();
tran = conexion.BeginTransaction();
Object id_articulo = cmd.ExecuteScalar();
articulo.id = Convert.ToInt32(id_articulo);
if (!StockDAO.getInstance().nuevoStock(stock, schema, conexion))
{
tran.Rollback();
return("Error al crear stock");
}
}
catch (Exception e)
{
tran.Rollback();
}
finally
{
}
tran.Commit();
conexion.Close();
retorno = "Nuevo articulo añadido correctamente";
return(retorno);
}
public bool LinkRegion(UUID regionID, int estateID)
{
string deleteSQL = "delete from estate_map where \"RegionID\" = :RegionID";
string insertSQL = "insert into estate_map values (:RegionID, :EstateID)";
using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
{
conn.Open();
NpgsqlTransaction transaction = conn.BeginTransaction();
try
{
using (NpgsqlCommand cmd = new NpgsqlCommand(deleteSQL, conn))
{
cmd.Transaction = transaction;
cmd.Parameters.AddWithValue("RegionID", regionID.Guid);
cmd.ExecuteNonQuery();
}
using (NpgsqlCommand cmd = new NpgsqlCommand(insertSQL, conn))
{
cmd.Transaction = transaction;
cmd.Parameters.AddWithValue("RegionID", regionID.Guid);
cmd.Parameters.AddWithValue("EstateID", estateID);
int ret = cmd.ExecuteNonQuery();
if (ret != 0)
{
transaction.Commit();
}
else
{
transaction.Rollback();
}
return(ret != 0);
}
}
catch (Exception ex)
{
m_log.Error("[REGION DB]: LinkRegion failed: " + ex.Message);
transaction.Rollback();
}
}
return(false);
}
public bool SaveWorkGroupinRole(WorkGroupinrolesDTO _WorkGroupinrolesDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO tblmstgroup (groupname,groupdescription,issystemgroup,statusid,createdby,createddate) VALUES ('" + ManageQuote(_WorkGroupinrolesDTO.pGroupname).Trim().ToUpper() + "', '', 'N', " + Convert.ToInt32(Status.Active) + ", " + _WorkGroupinrolesDTO.pCreatedby + ", current_timestamp); ");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public string RunNpgsqlTransaction(string sql)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (NpgsqlConnection pgConnection = new NpgsqlConnection(constr))
{
pgConnection.Open();
// Start a local transaction
NpgsqlTransaction myTrans = pgConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
NpgsqlCommand pgCommand = pgConnection.CreateCommand();
try
{
pgCommand.CommandText = sql;
pgCommand.ExecuteNonQuery();
myTrans.Commit();
islem = "0";
Console.WriteLine("işlem tamam");
}
catch (Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Hata oluştu işlem geri alındı");
islem = e.ToString();
}
finally
{
pgCommand.Dispose();
myTrans.Dispose();
}
}
return(islem);
}
protected static bool ExecuteQuery(NpgsqlCommand cmd)
{
bool _pass = false;
NpgsqlConnection trxconn = null;
if (OpenConnectionTrx(ref trxconn))
{
NpgsqlTransaction sqlTrans = trxconn.BeginTransaction();
cmd.Connection = trxconn;
cmd.Transaction = sqlTrans;
try
{
cmd.ExecuteNonQuery();
sqlTrans.Commit();
_pass = true;
}
catch (Exception ex)
{
sqlTrans.Rollback();
CloseConnectionTrx(ref trxconn);
throw (new Exception(ex.Message, ex));
}
}
CloseConnectionTrx(ref trxconn);
return(_pass);
}
public override async Task <bool> Excluir(CondicoesPagamento condicaoPagamento)
{
using (var conexao = GetCurrentConnection())
{
conexao.Open();
NpgsqlTransaction transaction = conexao.BeginTransaction();
try
{
string sql = @"DELETE FROM condicoesParcela WHERE codigoCondicaoPagamento = @codigo;
DELETE FROM condicoesPagamento WHERE codigo = @codigo;";
NpgsqlCommand command = new NpgsqlCommand(sql, conexao);
command.Parameters.AddWithValue("@codigo", condicaoPagamento.codigo);
var result = await command.ExecuteNonQueryAsync();
return(result == 1 ? true : false);
}
catch
{
transaction.Rollback();
throw new Exception("Não foi possível excluir a condição de pagamento");
}
finally
{
conexao.Close();
}
}
}
public override async Task <bool> Excluir(Locacoes locacao)
{
using (var conexao = GetCurrentConnection())
{
conexao.Open();
NpgsqlTransaction transaction = conexao.BeginTransaction();
try
{
string sql = @"UPDATE locacoes SET status = @status, dtalteracao = @dtAlteracao WHERE codigo = @codigo";
NpgsqlCommand command = new NpgsqlCommand(sql, conexao);
command.Parameters.AddWithValue("@status", locacao.status);
command.Parameters.AddWithValue("@dtAlteracao", locacao.dtAlteracao);
command.Parameters.AddWithValue("@codigo", locacao.codigo);
var result = await command.ExecuteNonQueryAsync();
await CancelarContasReceber(conexao, locacao);
transaction.Commit();
return(result == 1 ? true : false);
}
catch
{
transaction.Rollback();
throw new Exception("Não foi possivel cancelar a locação");
}
finally
{
conexao.Close();
}
}
}
public bool ExecuteTran(List <string> sql)
{
using (con = new NpgsqlConnection(DbConnectstring))
{
con.Open();
NpgsqlTransaction tran = con.BeginTransaction();
NpgsqlCommand cmd = new NpgsqlCommand()
{
Connection = con
};
try
{
foreach (string temp in sql)
{
cmd.CommandText = temp;
cmd.ExecuteNonQuery();
}
tran.Commit();
return(true);
}
catch (Exception ex)
{
tran.Rollback();
MessageBox.Show(ex.Message, "数据库事务", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
/// <summary>
/// 執行指令(For PostgreSQL)
/// </summary>
/// <param name="SQL">SQL語法</param>
/// <returns></returns>
public static bool _ExecuteCommand(string SQL)
{
NpgsqlTransaction transaction = null;
try
{
conn.Open();
transaction = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = conn;
command.CommandText = SQL;
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
conn.Close();
return(false);
}
finally
{
conn.Close();
}
return(true);
}
/// <summary>Cria um novo item e atribui à uma requisição</sumary>
public ObjResult Post(ItemProgress progress, int user_id, int item_id, int item_status_id)
{
connection = db.GetCon();
connection.Open();
NpgsqlTransaction transaction = connection.BeginTransaction();
ItemProgressDao dao = new ItemProgressDao(connection, transaction);
ObjResult result = new ObjResult();
try
{
progress.SetId(dao.Post(progress, user_id, item_id, item_status_id));
transaction.Commit();
result.Success();
result.SetData(progress.GetId());
}
catch (Exception e)
{
transaction.Rollback();
throw new Exception(e.ToString());
}
finally
{
connection.Close();
db.Close();
}
return(result);
}
/// <summary>Atualiza um item</sumary>
public ObjResult Update(int item_progress_id, ItemProgress progress)
{
connection = db.GetCon();
connection.Open();
NpgsqlTransaction transaction = connection.BeginTransaction();
ItemProgressDao dao = new ItemProgressDao(connection, transaction);
ObjResult result = new ObjResult();
try
{
item_progress_id = dao.Update(item_progress_id, progress);
transaction.Commit();
result.Success();
result.SetData(item_progress_id);
}
catch (Exception e)
{
transaction.Rollback();
throw new Exception(e.ToString());
}
finally
{
connection.Close();
db.Close();
}
return(result);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
public void ExecuteSqlTran(Hashtable SQLStringList)
{
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
using (NpgsqlTransaction trans = conn.BeginTransaction())
{
NpgsqlCommand cmd = new NpgsqlCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
NpgsqlParameter[] cmdParms = (NpgsqlParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit();
}
}
catch
{
trans.Rollback();
throw;
}
}
}
}
public bool RegisterDriving(int year, int week, string driverId, int tripId)
{
NpgsqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);
try
{
using (PreparedStatement preparedStatement = new PreparedStatement(connection))
{
preparedStatement.Transaction = transaction;
preparedStatement.CommandText =
"insert into driving (year, week, driver_id, trip_id) " +
"values (@year, @week, @driver_id, @trip_id)";
preparedStatement.AddParameter("year", year, NpgsqlDbType.Integer);
preparedStatement.AddParameter("week", week, NpgsqlDbType.Integer);
preparedStatement.AddParameter("driver_id", driverId, NpgsqlDbType.Varchar);
preparedStatement.AddParameter("trip_id", tripId, NpgsqlDbType.Integer);
preparedStatement.Execute();
}
}
catch
{
transaction.Rollback();
return(false);
}
transaction.Commit();
return(true);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(ArrayList SQLStringList)
{
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = conn;
NpgsqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (NpgsqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
public string GetNewCartonId()
{
string newid = "";
using (con = new NpgsqlConnection(DbConnectstring))
{
con.Open();
string sql0 = "LOCK TABLE t_carton IN ACCESS EXCLUSIVE MODE";
string sql1 = "select MAX(carton_id) from t_carton where create_date >='" + DateTime.Now.ToString("yyyy/MM/dd 00:00:00") + "'";
NpgsqlTransaction tran = con.BeginTransaction();
try
{
NpgsqlCommand cmd = new NpgsqlCommand(sql0, con);
cmd.ExecuteNonQuery();
cmd = new NpgsqlCommand(sql1, con);
//运行标量cmd.ExecuteScalar(),获取select的值
newid = cmd.ExecuteScalar().ToString();
if (newid == string.Empty)
{
newid = DateTime.Now.ToString("yyyyMMdd") + "C" + "001";
}
else
{
string tmp = "00" + (int.Parse(newid.Substring(9, 3)) + 1).ToString();
newid = newid.Substring(0, 8) + "C" + tmp.Substring(tmp.Length - 3, 3);
}
tran.Commit();
}
catch
{
tran.Rollback();
}
}
return(newid);
}
/// <summary>
/// Removes all tables from the configuration database.
/// </summary>
private void ClearBase()
{
transferControl.ThrowIfCancellationRequested();
transferControl.WriteLine();
transferControl.WriteMessage(ExtensionPhrases.ClearBase);
NpgsqlTransaction trans = null;
try
{
trans = conn.BeginTransaction();
progressTracker.SubtaskCount = project.ConfigDatabase.AllTables.Length;
foreach (IBaseTable baseTable in project.ConfigDatabase.AllTables)
{
transferControl.ThrowIfCancellationRequested();
transferControl.WriteMessage(string.Format(ExtensionPhrases.DeleteTable, baseTable.Name));
string sql = $"DROP TABLE IF EXISTS {GetBaseTableName(baseTable)} CASCADE";
new NpgsqlCommand(sql, conn, trans).ExecuteNonQuery();
progressTracker.SubtaskIndex++;
}
trans.Commit();
progressTracker.TaskIndex++;
}
catch
{
trans?.Rollback();
throw;
}
}
public bool SaveRoleSubModule(RolesubmodulesDTO _RolesubmodulesDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
_ = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText: "INSERT INTO tblmstmodules (modulename,moduledescription,parentmoduleid,parentmodulename,statusid,createdby,createddate) VALUES ('" + ManageQuote(_RolesubmodulesDTO.pSubmodulename).Trim() + "', '', " + _RolesubmodulesDTO.pModuleId + ", '" + ManageQuote(_RolesubmodulesDTO.pModulename).Trim() + "'," + Convert.ToInt32(Status.Active) + ", " + _RolesubmodulesDTO.pCreatedby + ", current_timestamp); ");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
/// <summary>
/// Executes the data set.
/// </summary>
/// <param name="storedProcName">Name of the stored proc.</param>
/// <param name="paramsValues">The params values.</param>
/// <returns></returns>
/// <exception cref="System.Exception">Error while running stored procedure + storedProc</exception>
public DataSet ExecuteDataSet(NpgsqlCommand Command)
{
DataSet dataset = new DataSet();
OpenConnection();
Command.Connection = this.Connection;
using (Transaction = Command.Connection.BeginTransaction())
{
Command.Transaction = Transaction;
try
{
NpgsqlDataAdapter adpt = new NpgsqlDataAdapter(Command);
adpt.Fill(dataset);
}
catch (Exception)
{
Transaction.Rollback();
CloseConnection();
throw new Exception("Error while running stored procedure " + Command.CommandText);
}
Transaction.Commit();
CloseConnection();
return(dataset);
}
}
/// <summary>
/// Creates foreign keys for the configuration database tables.
/// </summary>
private void CreateForeignKeys()
{
transferControl.ThrowIfCancellationRequested();
transferControl.WriteLine();
transferControl.WriteMessage(ExtensionPhrases.CreateFKs);
NpgsqlTransaction trans = null;
try
{
trans = conn.BeginTransaction();
progressTracker.SubtaskCount = project.ConfigDatabase.AllTables.Length;
foreach (IBaseTable baseTable in project.ConfigDatabase.AllTables)
{
transferControl.ThrowIfCancellationRequested();
transferControl.WriteMessage(string.Format(ExtensionPhrases.CreateTableFKs, baseTable.Name));
foreach (TableRelation relation in baseTable.DependsOn)
{
string sql = GetBaseForeignKeyDDL(relation);
new NpgsqlCommand(sql, conn, trans).ExecuteNonQuery();
}
progressTracker.SubtaskIndex++;
}
trans.Commit();
progressTracker.TaskIndex++;
}
catch
{
trans?.Rollback();
throw;
}
}
/// <summary>
/// Deletes an existing Tour in the database.
/// </summary>
/// <param name="id">
/// The id of the Tour which should be deleted.
/// </param>
/// <returns>
/// Returns a tuple.
/// On success a tuple with the true as item1 and a empty string as item2 is returned.
/// On failure item1 is false and item2 will contain the error message.
/// </returns>
public (bool, string) DeleteTour(int id)
{
NpgsqlTransaction?transaction = null;
try
{
using var conn = Connection();
transaction = BeginTransaction(conn);
if (transaction == null)
{
return(false, "Could not start new transaction");
}
using var cmd = new NpgsqlCommand(@"
DELETE from tour WHERE id=@p0",
conn);
cmd.Parameters.AddWithValue("p0", NpgsqlDbType.Integer, id);
cmd.ExecuteNonQuery();
transaction.Commit();
return(true, string.Empty);
}
catch (Exception ex)
{
logger.Log(LogLevel.Warning, ex.StackTrace);
transaction?.Rollback();
return(false, "Internal server error");
}
}