public int ExecuteSqlTran(List <string> SQLStringList)
{
int result;
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString))
{
sqlConnection.Open();
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
sqlCommand.Connection = sqlConnection;
System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
sqlCommand.Transaction = sqlTransaction;
try
{
int num = 0;
for (int i = 0; i < SQLStringList.Count; i++)
{
string text = SQLStringList[i];
if (text.Trim().Length > 1)
{
sqlCommand.CommandText = text;
num += sqlCommand.ExecuteNonQuery();
}
}
sqlTransaction.Commit();
result = num;
}
catch
{
sqlTransaction.Rollback();
result = 0;
}
}
return(result);
}
public static bool ExecuteQueryTrans(string cmdtext, params SqlParameter[] parameters)
{
int i = 0;
using (SqlConnection conn = new SqlConnection(connStr))
{
System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();
cm.Connection = conn;
conn.Open();
System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
try
{
cm.CommandText = cmdtext;
cm.Parameters.AddRange(parameters);
cm.Transaction = trans;
i = cm.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
conn.Close();
trans.Dispose();
conn.Dispose();
}
}
return(i > 0);
}
/// <summary>
/// Выполнение набора команд в одной транзакции
/// </summary>
/// <param name="commands">Список команд для выполнения</param>
/// <param name="level">Уровень изоляции транзакции</param>
/// <exception cref="System.ArgumentNullException"></exception>
/// <exception cref="System.ArgumentException"></exception>
/// <exception cref="System.InvalidOperationException"></exception>
/// <exception cref="System.Data.SqlClient.SqlException"></exception>
/// <exception cref="System.Exception"></exception>
public void ExecuteTransaction(List <SqlCommand> commands, System.Data.IsolationLevel level = System.Data.IsolationLevel.RepeatableRead)
{
if (commands == null)
{
throw new ArgumentNullException("commands");
}
System.Data.SqlClient.SqlTransaction transaction = null;
try
{
connection.Open();
// Start a local transaction.
transaction = connection.BeginTransaction(level);
foreach (SqlCommand command in commands)
{
//command.Transaction = transaction;
ExecuteTransaction(command, transaction);
}
// Attempt to commit the transaction.
// connection.Close();
transaction.Commit();
}
catch (SqlException ex)
{
// Attempt to roll back the transaction.
transaction.Rollback();
throw ex;
}
finally
{
connection.Close();
}
}
public void ExecuteSqlTran(Hashtable SQLStringList)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString))
{
sqlConnection.Open();
using (System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
{
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
try
{
foreach (DictionaryEntry dictionaryEntry in SQLStringList)
{
string cmdText = dictionaryEntry.Key.ToString();
System.Data.SqlClient.SqlParameter[] cmdParms = (System.Data.SqlClient.SqlParameter[])dictionaryEntry.Value;
DbHelperSQLP.PrepareCommand(sqlCommand, sqlConnection, sqlTransaction, cmdText, cmdParms);
int num = sqlCommand.ExecuteNonQuery();
sqlCommand.Parameters.Clear();
}
sqlTransaction.Commit();
}
catch
{
sqlTransaction.Rollback();
throw;
}
}
}
}
public void Import(string connectionString, StatbookModel statbook, bool assumeATeams)
{
_connection = new SqlConnection(connectionString);
try
{
_connection.Open();
_transaction = _connection.BeginTransaction();
// insert leagues
LeagueGateway leagueGateway = new LeagueGateway(_connection, _transaction);
var leagues = leagueGateway.GetAllLeagues();
League homeLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.HomeTeam.LeagueName.ToLower());
League awayLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.AwayTeam.LeagueName.ToLower());
int maxID = leagues.Select(l => l.ID).Max();
if(homeLeague == null)
{
homeLeague = leagueGateway.GetLeague(maxID + 1, statbook.HomeTeam.LeagueName, statbook.Date, false);
maxID++;
}
if(awayLeague == null)
{
awayLeague = leagueGateway.GetLeague(maxID + 1, statbook.AwayTeam.LeagueName, statbook.Date, false);
maxID++;
}
// insert teams
TeamGateway teamGateway = new TeamGateway(_connection, _transaction);
Team homeTeam, awayTeam;
if (assumeATeams)
{
homeTeam = teamGateway.GetATeam(homeLeague.ID);
awayTeam = teamGateway.GetATeam(awayLeague.ID);
}
else
{
homeTeam = teamGateway.GetTeam(statbook.HomeTeam.Name, homeLeague.ID, "A", false);
awayTeam = teamGateway.GetTeam(statbook.AwayTeam.Name, awayLeague.ID, "A", false);
}
// insert bout
BoutGateway boutGateway = new BoutGateway(_connection, _transaction);
if(!boutGateway.DoesBoutExist(homeTeam.ID, awayTeam.ID, statbook.Date))
{
Bout bout = boutGateway.GetBout(homeTeam.ID, awayTeam.ID, statbook.Date);
BoutDataImport(statbook, bout, homeTeam, awayTeam);
}
else
{
// bout already exists
Console.WriteLine(string.Format("Bout between {0} and {1} on {2} already exists.", homeTeam.Name, awayTeam.Name, statbook.Date));
}
_transaction.Commit();
}
finally
{
_connection.Close();
}
}
public int DeleteCopy(LIB.CopyDTO copy, SqlTransaction trans)
{
bool isInScopeCreated = false;
int rs;
if (trans == null)
{
isInScopeCreated = true;
trans = ConnectionManager.Con.BeginTransaction("CAT_CRE_TRANSACT");
}
CopyDAO dao = new CopyDAO();
rs = dao.DeleteCopy(copy, trans);
if (isInScopeCreated)
{
if (rs == 0)
trans.Rollback();
else
trans.Commit();
}
return rs;
}
public DataTable getVector(string SQL)
{
DataTable dt = new DataTable();
try
{
if (_DBType.Equals("SQL"))
{
tx_Sql = cn_Sql.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn_Sql;
cmd.CommandText = SQL;
cmd.CommandType = CommandType.Text;
cmd.Transaction = tx_Sql;
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
tx_Sql.Commit();
return dt;
}
}
catch (Exception e)
{
}
return null;
}
public static int InsertBySQL(List <string> SQL)
{
System.Data.SqlClient.SqlConnection oracleConnection = new System.Data.SqlClient.SqlConnection(constr);
System.Data.SqlClient.SqlCommand oracleCommand = new System.Data.SqlClient.SqlCommand();
oracleCommand.Connection = oracleConnection;
int rowsAffected = 0;
oracleConnection.Open();
System.Data.SqlClient.SqlTransaction tran = oracleConnection.BeginTransaction();
try
{
foreach (string s in SQL)
{
oracleCommand.Transaction = tran;
oracleCommand.CommandText = s;
rowsAffected += oracleCommand.ExecuteNonQuery();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw new NotSupportedException(ex.Message);
}
finally
{
oracleConnection.Close();
}
return(rowsAffected);
}
public void ExecuteNonQuery(SqlCommand sqlCmd)
{
using (SqlConnection sqlConn = new SqlConnection())
{
try
{
sqlConn.ConnectionString = strSqlConnectionString;
if (sqlConn.State != ConnectionState.Open)
{
sqlConn.Open();
}
sqlTrans = sqlConn.BeginTransaction("ExecuteNonQueryTransaction");
sqlCmd.Connection = sqlConn;
sqlCmd.Transaction = sqlTrans;
sqlCmd.ExecuteNonQuery();
sqlTrans.Commit();
}
catch (SqlException exSql)
{
SqlException ex = exSql;
try
{
if (sqlTrans != null)
{
sqlTrans.Rollback("ExecuteNonQueryTransaction");
}
}
catch (SqlException exRollback)
{
ex = exRollback;
}
throw ex;
}
}
}
// /// <summary>
// /// Método para dar de baja lógicamente a una zona de un tarifario.
// /// </summary>
// /// <returns>Retorna un booleano indicando el éxito de la operacion.</returns>
// public bool Eliminar()
// {
// using (SqlConnection conexion = new SqlConnection())
// {
// SqlTransaction transaccion = null;
// conexion.ConnectionString = Config.ConnectionString;
//
// try
// {
//
// conexion.Open();
// transaccion = conexion.BeginTransaction();
// Config.Conexion.EjecutarSinResultados(transaccion,"TarifarioFleteZonaDEL", this.TarifarioFleteZonaID);
// //Se instancia un objeto de TarifarioFlete para por utilizar el metodo validar (Bety)
// TarifarioFlete oTari= new TarifarioFlete();
// oTari.TarifarioFleteID=this.TarifarioFleteID;
// oTari.ValidarTarifario(transaccion);
// transaccion.Commit();
// return true;
// }
// catch(Exception)
// {
// transaccion.Rollback();
// return false;
// }
// }
// }
public bool Eliminar(int usuarioID, System.Data.SqlClient.SqlTransaction transaccion)
{
using (SqlConnection conexion = new SqlConnection())
{
//SqlTransaction transaccion = null;
conexion.ConnectionString = Config.ConnectionString;
try
{
conexion.Open();
transaccion = conexion.BeginTransaction();
Config.Conexion.EjecutarSinResultados(transaccion, "TarifarioFleteZonaDEL", this.TarifarioFleteZonaID, usuarioID);
//Se instancia un objeto de TarifarioFlete para por utilizar el metodo validar (Bety)
TarifarioFlete oTari = new TarifarioFlete();
oTari.TarifarioFleteID = this.TarifarioFleteID;
oTari.ValidarTarifario(transaccion);
transaccion.Commit();
return(true);
}
catch (Exception)
{
transaccion.Rollback();
return(false);
}
}
}
public void addunit()
{
tr = Conn.BeginTransaction();
try
{
sb = new StringBuilder();
sb.Append("INSERT INTO UNIT (UnitId,UnitName,UnitNameEng)");
sb.Append(" VALUES (@UnitId,@UnitName,@UnitNameEng)");
string sqlAdd;
sqlAdd = sb.ToString();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@UnitId", SqlDbType.NVarChar).Value = txtUnitId.Text.Trim();
com.Parameters.Add("@UnitName", SqlDbType.NVarChar).Value = txtUnitName.Text.Trim();
com.Parameters.Add("@UnitNameEng", SqlDbType.NVarChar).Value = txtUnitNameEng.Text.Trim();
com.ExecuteNonQuery();
tr.Commit();
}
catch
{
MessageBox.Show("คุณป้อนรหัสหน่วยนับสินค้าซ้ำ !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
tr.Rollback();
}
}
private void toolStripButtonSave_Click(object sender, EventArgs e)
{
string lsDealerNo = "";
Validate(); //Validate form so all data sets are updated with field values
DealerbindingSource.EndEdit();
lsDealerNo = DEALERcomboBox.Text.ToString().Trim();
tableAdapConn = new System.Data.SqlClient.SqlConnection();
tableAdapConn.ConnectionString = IAC2018SQL.Properties.Settings.Default.IAC2010SQLConnectionString;
tableAdapConn.Open();
dEALERTableAdapter.Connection = tableAdapConn;
tableAdapTran = dEALERTableAdapter.BeginTransaction();
dEALERTableAdapter.Transaction = tableAdapTran;
try
{
dEALERTableAdapter.Update(iacDataSet.DEALER.Rows[DealerbindingSource.Position]);
tableAdapTran.Commit();
}
catch (System.Data.SqlClient.SqlException ex)
{
tableAdapTran.Rollback();
MessageBox.Show("This is a Microsoft SQL Server database error: " + ex.Message.ToString());
}
catch (System.InvalidOperationException ex)
{
tableAdapTran.Rollback();
MessageBox.Show("Invalid Operation Error: " + ex.Message.ToString());
}
catch (Exception ex)
{
tableAdapTran.Rollback();
MessageBox.Show("General Exception Error: " + ex.Message.ToString());
}
finally
{
dEALERTableAdapter.UnlockRecord(iacDataSet.DEALER.Rows[DealerbindingSource.Position].Field <String>("DEALER_ACC_NO"));
tableAdapConn.Close();
tableAdapConn = null;
tableAdapTran = null;
toolStripButtonSave.Enabled = false;
if (lbAddFlag)
{
lbAddFlag = false;
iacDataSet.DEALER.Clear();
StartUpConfiguration();
}
iacDataSet.AcceptChanges();
Program.gsKey = lsDealerNo;
DEALERcomboBox.Text = lsDealerNo;
if (lbEdit)
{
lbEdit = false;
SetViewMode();
}
}
}
public int CreatePaySalary(PaySalary newPaySalary)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbPaySalary(payID,psnCode,payStartDate,payEndDate,period,salary,revenceAll,netIncome,total,pSalary,pOt,pWork,ppSo,psLa,psLast,psLess,psReam,psIns,psPenalty)");
sb.Append(" VALUES (@payID,@psnCode,@payStartDate,@payEndDate,@period,@salary,@revenceAll,@netIncome,@total,@pSalary,@pOt,@pWork,@ppSo,@psLa,@psLast,@psLess,@psReam,@psIns,@psPenalty)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@payID", SqlDbType.NVarChar).Value = newPaySalary.payID;
comm.Parameters.Add("@psnCode", SqlDbType.NVarChar).Value = newPaySalary.psnCode;
comm.Parameters.Add("@payStartDate", SqlDbType.NVarChar).Value = newPaySalary.payStartDate;
comm.Parameters.Add("@payEndDate", SqlDbType.NVarChar).Value = newPaySalary.payEndDate;
comm.Parameters.Add("@period", SqlDbType.NVarChar).Value = newPaySalary.period;
comm.Parameters.Add("@salary", SqlDbType.NVarChar).Value = newPaySalary.salary;
comm.Parameters.Add("@revenceAll", SqlDbType.NVarChar).Value = newPaySalary.revenceAll;
comm.Parameters.Add("@netIncome", SqlDbType.NVarChar).Value = newPaySalary.netIncome;
comm.Parameters.Add("@total", SqlDbType.NVarChar).Value = newPaySalary.total;
comm.Parameters.Add("@pSalary", SqlDbType.NVarChar).Value = newPaySalary.pSalary;
comm.Parameters.Add("@pOt", SqlDbType.NVarChar).Value = newPaySalary.pOt;
comm.Parameters.Add("@pWork", SqlDbType.NVarChar).Value = newPaySalary.pWork;
comm.Parameters.Add("@ppSo", SqlDbType.NVarChar).Value = newPaySalary.ppSo;
comm.Parameters.Add("@psLa", SqlDbType.NVarChar).Value = newPaySalary.psLa;
comm.Parameters.Add("@psLast", SqlDbType.NVarChar).Value = newPaySalary.psLast;
comm.Parameters.Add("@psLess", SqlDbType.NVarChar).Value = newPaySalary.psLess;
comm.Parameters.Add("@psReam", SqlDbType.NVarChar).Value = newPaySalary.psReam;
comm.Parameters.Add("@psIns", SqlDbType.NVarChar).Value = newPaySalary.psIns;
comm.Parameters.Add("@psPenalty", SqlDbType.NVarChar).Value = newPaySalary.psPenalty;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public static DataSet ExecuteDataset(SqlCommand command, SqlConnection connection, SqlTransaction transaction)
{
bool mustCloseConnection = false;
#region Check
if (connection == null) throw new ArgumentNullException("connection");
if (command == null) throw new ArgumentNullException("command");
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
#endregion
command.Connection = connection;
command.Transaction = transaction;
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
DataSet retval = new DataSet();
if (transaction == null)
{
da.Fill(retval);
}
else
{
try
{
da.Fill(retval);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
if (mustCloseConnection)
connection.Close();
return retval;
}
}
public void Commit()
{
if (myTrans != null)
{
myTrans.Commit();
myTrans = null;
CloseCon();
}
}
/// <summary>
/// コミット
/// </summary>
/// <remarks></remarks>
public void Commit()
{
if (_trans != null)
{
_trans.Commit();
_logger.Info("Commit:" + _trans.ToString());
_trans = null;
}
}
public static void CommitTransaccion(SqlTransaction tran)
{
try
{
tran.Commit();
}
catch (Exception ex)
{ throw new Exception("CommitTransaccion - DataLibrary", ex); }
}
public static void CommitTransaction()
{
Transaction.Commit();
Transaction = null;
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
}
public returnval merge(returnval val)
{
if (val.rows == null && val.childs == null)
{
SD.DataTable DataTable;
using (SDSc.SqlConnection SqlConnection = new SDSc.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["moodleConnectionString1"].ConnectionString)) {
DataTable = new SD.DataTable();
(new SDSc.SqlDataAdapter(new SDSc.SqlCommand()
{
CommandText = string.Format(
"SELECT {0}{1}{2};"
, selectcols.Replace("@currentuser", "1")
, dbname == null ? "" : string.Format(" FROM {0}", dbname)
, whereparam == null ? "" : string.Format(" WHERE {0}", whereparam
.Replace("@currentuser", "1")
.Replace("@currentlogin", "1"))
),
Connection = SqlConnection
})).Fill(DataTable);
};
val.rows = new List <object[]>();
foreach (SD.DataRow DataRow in DataTable.Rows)
{
val.rows.Add(DataRow.ItemArray);
}
}
else
{
using (SDSc.SqlConnection SqlConnection = new SDSc.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["moodleConnectionString1"].ConnectionString)) {
SqlConnection.Open();
SDSc.SqlTransaction SqlTransaction = SqlConnection.BeginTransaction();
try {
merge(val, SqlTransaction);
if (val.childs != null)
{
foreach (returnval subtable in val.childs)
{
thistables.table(subtable.name).merge(subtable, SqlTransaction);
if (subtable.childs != null)
{
foreach (returnval subtable2 in subtable.childs)
{
thistables.table(subtable2.name).merge(subtable2, SqlTransaction);
}
}
}
}
SqlTransaction.Commit();
} catch (Exception e) {
SqlTransaction.Rollback();
throw e;
}
}
}
return(val);
}
public int CreateLabel(Label newLabel)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO Label(Id,PartName,SerialNo,PrintTime,Line,Ckt,Model,OilType,OilWeight,Status,Run,MM)");
sb.Append(" VALUES (@Id,@PartName,@SerialNo,@PrintTime,@Line,@Ckt,@Model,@OilType,@OilWeight,@Status,@Run,@MM)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@Id", SqlDbType.NVarChar).Value = newLabel.Id;
comm.Parameters.Add("@PartName", SqlDbType.NVarChar).Value = newLabel.PartName;
comm.Parameters.Add("@SerialNo", SqlDbType.NVarChar).Value = newLabel.SerialNo;
comm.Parameters.Add("@PrintTime", SqlDbType.DateTime).Value =newLabel.PrintTime;
comm.Parameters.Add("@Line", SqlDbType.NVarChar).Value = newLabel.Line;
comm.Parameters.Add("@Ckt", SqlDbType.NVarChar).Value = newLabel.Ckt;
comm.Parameters.Add("@Model", SqlDbType.NVarChar).Value = newLabel.Model;
comm.Parameters.Add("@OilType", SqlDbType.NVarChar).Value = newLabel.OilType;
comm.Parameters.Add("@OilWeight", SqlDbType.NVarChar).Value = newLabel.OilWeight;
comm.Parameters.Add("@Status", SqlDbType.NVarChar).Value = newLabel.Status;
comm.Parameters.Add("@Run", SqlDbType.NVarChar).Value = newLabel.Run;
comm.Parameters.Add("@MM", SqlDbType.NVarChar).Value = newLabel.MM;
comm.ExecuteNonQuery();
tr.Commit();
logger.Fatal(" Save Complate ");
result = 1;
}
catch (Exception ex)
{
logger.Fatal(" Save Not complate "+ex.Message);
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
/// <summary>執行 Transaction</summary>
/// <param name="listSQL">欲執行交易的 ArrayList (內含 SQL 指令)</param>
/// <param name="sqlConn">連線物件</param>
/// <returns>Transaction是否成功</returns>
/// <remarks></remarks>
public static bool raiseTransaction(ArrayList listSQL, System.Data.SqlClient.SqlConnection sqlConn)
{
if (listSQL == null)
{
return(true);
}
if (listSQL.Count == 0)
{
return(true);
}
if (sqlConn == null)
{
sqlConn = createConnection();
}
System.Data.SqlClient.SqlTransaction sqlTrans = null;
if (!(sqlConn.State == ConnectionState.Open))
{
sqlConn.Open();
}
System.Data.SqlClient.SqlCommand cmd = sqlConn.CreateCommand();
StringBuilder strSQL = new StringBuilder("");
sqlTrans = sqlConn.BeginTransaction();
try
{
cmd.Transaction = sqlTrans;
for (int i = 0; i <= listSQL.Count - 1; i++)
{
if (!string.IsNullOrEmpty(listSQL[i].ToString()))
{
strSQL.AppendLine(listSQL[i].ToString());
}
}
cmd.CommandText = strSQL.ToString();
cmd.ExecuteNonQuery();
sqlTrans.Commit();
return(true);
}
catch (Exception Ex)
{
if ((sqlTrans != null))
{
sqlTrans.Rollback();
}
//Message.alertMessage("C0002", null, Ex.Message.ToString(), null);
return(false);
}
finally
{
if (!(sqlConn.State == ConnectionState.Closed))
{
sqlConn.Close();
}
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (txtUnitSaleName.Text.Trim() == "")
{
MessageBox.Show("กรุณาป้อนชื่อหน่วยก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtUnitSaleName.Focus();
return;
}
if (txtUnitSaleNameEng.Text.Trim() == "")
{
MessageBox.Show("กรุณาป้อนชื่อหน่วยก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtUnitSaleNameEng.Focus();
return;
}
if (MessageBox.Show("คุณต้องการเพิ่มหน่วยนับสินค้าใหม่ ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
tr = Conn.BeginTransaction();
try
{
sb = new StringBuilder();
sb.Append("INSERT INTO UNITSALE (UnitSaleId,UnitSaleName,UnitSaleNameEng)");
sb.Append(" VALUES (@UnitSaleId,@UnitSaleName,@UnitSaleNameEng)");
string sqlAdd;
sqlAdd = sb.ToString();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@UnitSaleId", SqlDbType.NVarChar).Value = txtUnitSaleId.Text.Trim();
com.Parameters.Add("@UnitSaleName", SqlDbType.NVarChar).Value = txtUnitSaleName.Text.Trim();
com.Parameters.Add("@UnitSaleNameEng", SqlDbType.NVarChar).Value = txtUnitSaleNameEng.Text.Trim();
com.ExecuteNonQuery();
tr.Commit();
MessageBox.Show("เพิ่มหน่วยนับสินค้าใหม่ เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearAllData();
ShowData();
FormatdgvUnitSaleList();
UnitSaleId();
}
catch
{
MessageBox.Show("คุณป้อนรหัสหน่วยนับสินค้าซ้ำ !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
tr.Rollback();
}
}
txtUnitSaleId.Focus();
}
public int CreateInjectionCabinetLabel(InjectionPanalLabels newInjectionCabinetLabel)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO injection_panal_labels(barcode,part,date,serial,shift,rev,matterial_no,lot_no,color_no,machine_no,mold_no,user_create_name,create_date,status)");
sb.Append(" VALUES (@barcode,@part,@date,@serial,@shift,@rev,@matterial_no,@lot_no,@color_no,@machine_no,@mold_no,@user_create_name,@create_date,@status)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@barcode", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.BarCode;
comm.Parameters.Add("@part", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.Part;
comm.Parameters.Add("@date", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.date;
comm.Parameters.Add("@serial", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.Serial;
comm.Parameters.Add("@shift", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.Shift;
comm.Parameters.Add("@rev", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.Rev;
comm.Parameters.Add("@matterial_no", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.MatterialNo;
comm.Parameters.Add("@lot_no", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.LotNo;
comm.Parameters.Add("@color_no", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.ColorNo;
comm.Parameters.Add("@machine_no", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.MachineNo;
comm.Parameters.Add("@mold_no", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.MoldNo;
comm.Parameters.Add("@user_create_name", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.UserCreateName;
comm.Parameters.Add("@create_date", SqlDbType.DateTime).Value = newInjectionCabinetLabel.CreateDate;
comm.Parameters.Add("@status", SqlDbType.NVarChar).Value = newInjectionCabinetLabel.Status;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public void CloseInsertConnectionWithTransaction(bool TrnsOk)
{
if (TrnsOk)
{
InsertTransaction.Commit();
}
else
{
InsertTransaction.Rollback();
}
InsertConnection.Close();
}
public int CreateFinishingLabel(FinshingLabel newFinshingLabel)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO finishing_labels(barcode,complete_no,rev,place_name,product_barcode_date,product_text_date,line,shift,serial,print_date,user_create_name,status)");
sb.Append(" VALUES (@barcode,@complete_no,@rev,@place_name,@product_barcode_date,@product_text_date,@line,@shift,@serial,@print_date,@user_create_name,@status)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@barcode", SqlDbType.NVarChar).Value = newFinshingLabel.Barcode;
comm.Parameters.Add("@complete_no", SqlDbType.NVarChar).Value = newFinshingLabel.CompleteNo;
comm.Parameters.Add("@rev", SqlDbType.NVarChar).Value = newFinshingLabel.Rev;
comm.Parameters.Add("@place_name", SqlDbType.NVarChar).Value = newFinshingLabel.PlaceName;
comm.Parameters.Add("@product_barcode_date", SqlDbType.NVarChar).Value = newFinshingLabel.ProductBarCodeDate;
comm.Parameters.Add("@product_text_date", SqlDbType.NVarChar).Value = newFinshingLabel.ProductTextDate;
comm.Parameters.Add("@line", SqlDbType.NVarChar).Value = newFinshingLabel.Line;
comm.Parameters.Add("@shift", SqlDbType.NVarChar).Value = newFinshingLabel.Shift;
comm.Parameters.Add("@serial", SqlDbType.NVarChar).Value = newFinshingLabel.Serial;
comm.Parameters.Add("@print_date", SqlDbType.DateTime).Value = newFinshingLabel.PrintDate;
comm.Parameters.Add("@user_create_name", SqlDbType.NVarChar).Value = newFinshingLabel.UserCreateName;
comm.Parameters.Add("@status", SqlDbType.NVarChar).Value = newFinshingLabel.Status ;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public int CreateMillin(Millin newMillin)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbMillin(psnCode,cause,mFormat,note,mdateStart,mhurStart,mdateEnd,mhurEnd,mdateAll,mhurAll,mless,mtime)");
sb.Append(" VALUES (@psnCode,@cause,@mFormat,@note,@mdateStart,@mhurStart,@mdateEnd,@mhurEnd,@mdateAll,@mhurAll,@mless,@mtime)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@psnCode", SqlDbType.NVarChar).Value = newMillin.psnCode;
comm.Parameters.Add("@cause", SqlDbType.NVarChar).Value = newMillin.cause;
comm.Parameters.Add("@mFormat", SqlDbType.NVarChar).Value = newMillin.mFormat;
comm.Parameters.Add("@note", SqlDbType.NVarChar).Value = newMillin.note;
comm.Parameters.Add("@mdateStart", SqlDbType.NVarChar).Value = newMillin.mdateStart;
comm.Parameters.Add("@mhurStart", SqlDbType.NVarChar).Value = newMillin.mhurStart;
comm.Parameters.Add("@mdateEnd", SqlDbType.NVarChar).Value = newMillin.mdateEnd;
comm.Parameters.Add("@mhurEnd", SqlDbType.NVarChar).Value = newMillin.mhurEnd;
comm.Parameters.Add("@mdateAll", SqlDbType.NVarChar).Value = newMillin.mdateAll;
comm.Parameters.Add("@mhurAll", SqlDbType.NVarChar).Value = newMillin.mhurAll;
comm.Parameters.Add("@mless", SqlDbType.NVarChar).Value = newMillin.mless;
comm.Parameters.Add("@mtime", SqlDbType.NVarChar).Value = newMillin.mtime;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public int CreatePringtingLabel(PrintingLabels newPrintingLabels)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO printing_labels(barcode,part,product_date,serial,shift,rev,color_no,process,mold_no,user_create_name,create_date,status)");
sb.Append(" VALUES (@barcode,@part,@product_date,@serial,@shift,@rev,@color_no,@process,@mold_no,@user_create_name,@create_date,@status)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@barcode", SqlDbType.NVarChar).Value = newPrintingLabels.BarCode;
comm.Parameters.Add("@part", SqlDbType.NVarChar).Value = newPrintingLabels.Part;
comm.Parameters.Add("@product_date", SqlDbType.NVarChar).Value = newPrintingLabels.ProductDate;
comm.Parameters.Add("@serial", SqlDbType.NVarChar).Value = newPrintingLabels.Serial;
comm.Parameters.Add("@shift", SqlDbType.NVarChar).Value = newPrintingLabels.Shift;
comm.Parameters.Add("@rev", SqlDbType.NVarChar).Value = newPrintingLabels.Rev;
comm.Parameters.Add("@color_no", SqlDbType.NVarChar).Value = newPrintingLabels.ColorNo;
comm.Parameters.Add("@process", SqlDbType.NVarChar).Value = newPrintingLabels.Process;
comm.Parameters.Add("@mold_no", SqlDbType.NVarChar).Value = newPrintingLabels.Mold;
comm.Parameters.Add("@user_create_name", SqlDbType.NVarChar).Value = newPrintingLabels.UserCreateName;
comm.Parameters.Add("@create_date", SqlDbType.DateTime).Value = newPrintingLabels.CreateDate;
comm.Parameters.Add("@status", SqlDbType.NVarChar).Value = newPrintingLabels.Status;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public string ClearAllUploadedFeeByZyh(int zyh)
{
string hr = string.Empty;
StringBuilder sb = null;
int iHr = 0;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(GSettings.HisConnStr);
DCCbhisDataContext hisDb = new DCCbhisDataContext(conn);
System.Data.SqlClient.SqlTransaction trans = null;
try
{
var _NhPersonInfo = GetNhPersonInfoByZyh(zyh);
if (_NhPersonInfo == null)
{
throw new Exception("获取农合患者信息错误！");
}
sb = new StringBuilder(256);
iHr = NhLocalWrap.DeleteFeeList(
string.Format("{0}$${1}", _NhPersonInfo.OrganCode, _NhPersonInfo.AccountYear),
_NhPersonInfo.CoopMedCode,
_NhPersonInfo.AiIDNo,
int.Parse(_NhPersonInfo.DiagNo),
sb
);
if (iHr < 0)
{
throw new Exception(sb.ToString());
}
conn.Open();
trans = conn.BeginTransaction();
hisDb.Transaction = trans;
hisDb.ExecuteCommand("delete from wynhfeelist where zyh={0}", zyh);
hisDb.ExecuteCommand("update jzd set sb_upload=0 where zyh={0}", zyh);
trans.Commit();
}
catch (System.Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
hr = ex.Message;
}
finally{
conn.Close();
}
return(hr);
}
public void deletesupplier()
{
if (MessageBox.Show("คุณต้องการลบผู้ขาย ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
tr = Conn.BeginTransaction();
try
{
sb = new StringBuilder();
sb.Append("DELETE SUPPLIER");
sb.Append(" WHERE (SupplierId=@SupplierId)");
string sqlDelete;
sqlDelete = sb.ToString();
com.CommandText = sqlDelete;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@SupplierId", SqlDbType.NVarChar).Value = txtSupplierID.Text.Trim();
int result;
result = com.ExecuteNonQuery();
if (result == 0)
{
tr.Rollback();
MessageBox.Show("ข้อมูลที่ต้องการลบไม่ถูกต้อง !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtSupplierID.SelectAll();
}
else
{
tr.Commit();
//MessageBox.Show("ลบชื่อหน่วยนับสินค้า เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
// ClearAllData();//Clear ข้อมูลจาก textbox
ShowData();
FormatDgvSupplierList();
btnAdd.Enabled = true;
btnEdit.Enabled = false;
btnDelete.Enabled = false;
}
}
catch (Exception ex)
{
tr.Rollback();
MessageBox.Show("เกิดข้อผิดพลาด เนื่องจาก " + ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
public void deletecompany()
{
//if (MessageBox.Show("คุณต้องการลบชื่อบริษัท ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
//{
tr = Conn.BeginTransaction();
try
{
sb = new StringBuilder();
sb.Append("DELETE Company");
sb.Append(" WHERE (CompanyId=@CompanyID)");
string sqlDelete;
sqlDelete = sb.ToString();
com.CommandText = sqlDelete;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@CompanyID", SqlDbType.NVarChar).Value = txtCompanyId.Text.Trim();
int result;
result = com.ExecuteNonQuery();
if (result == 0)
{
tr.Rollback();
MessageBox.Show("ข้อมูลที่ต้องการลบไม่ถูกต้อง !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtCompanyId.SelectAll();
}
else
{
tr.Commit();
//MessageBox.Show("ลบชื่อลูกค้า เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
ShowData();
FormatCompanyList();
btnAdd.Enabled = true;
btnEdit.Enabled = false;
btnDelete.Enabled = false;
}
}
catch (Exception ex)
{
tr.Rollback();
MessageBox.Show("เกิดข้อผิดพลาด เนื่องจาก " + ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//}
}
/// <summary>
/// commit the passed transaction
/// </summary>
/// <param name="transaction">transaction to be committed</param>
public static void CommitTransaction(SqlTransaction transaction)
{
SqlConnection connection = transaction.Connection;
try
{
transaction.Commit();
}
finally
{
connection.Close();
connection.Dispose();
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Adds(Model.WxMessageModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Wx_messageinfo(");
strSql.Append("[MsgType],Contents,PicUrl,PShow,Title,Description,Url,HUrl,GroupCode,CreateTime,Author,OrderId)");
strSql.Append(" values (");
strSql.Append("@MsgType,@Contents,@PicUrl,@PShow,@Title,@Description,@Url,@HUrl,@GroupCode,@CreateTime,@Author,@OrderId)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@MsgType", model.MsgType),
new SqlParameter("@Contents", model.Contents),
new SqlParameter("@PicUrl", model.PicUrl),
new SqlParameter("@PShow", model.PShow),
new SqlParameter("@Title", model.Title),
new SqlParameter("@Description", model.Description),
new SqlParameter("@Url", model.Url),
new SqlParameter("@HUrl", model.HUrl),
new SqlParameter("@GroupCode", model.GroupCode),
new SqlParameter("@CreateTime", model.CreateTime),
new SqlParameter("@Author", model.Author),
new SqlParameter("@OrderId", model.OrderId)
};
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction())
{
try
{
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql.ToString(), parameters);
//return i;
string a = SqlHelper.ExecuteDataTable(trans, CommandType.Text, "select @@IDENTITY").Rows[0][0].ToString();
trans.Commit();
return(Convert.ToInt32(a));
}
catch (Exception)
{
trans.Rollback();
return(0);
}
}
}
}
public static ResultadoTransaccion ActualizarNaviera(Int64 id, string nombre, string relacionPuertos)
{
resTransaccion = new ResultadoTransaccion();
try
{
//Abrir Conexion
conn = BaseDatos.Conexion();
//Crear Transaccion
transaction = conn.BeginTransaction();
//Actualizar
//objParams = SqlHelperParameterCache.GetSpParameterSet(BaseDatos.Conexion(), "SP_A_CLIENTES_DIRECCION");
objParams = SqlHelperParameterCache.GetSpParameterSet(conn, "SP_U_PAPERLESS_NAVIERA");
objParams[0].Value = id;
objParams[1].Value = nombre;
SqlCommand command = new SqlCommand("SP_U_PAPERLESS_NAVIERA", conn, transaction);
command.Parameters.AddRange(objParams);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
transaction.Commit();
resTransaccion.Estado = Enums.EstadoTransaccion.Aceptada;
resTransaccion.Accion = Enums.AccionTransaccion.Actualizar;
resTransaccion.ObjetoTransaccion = id;
//Registrar Actividad
//LogActividadUsuarios log = new LogActividadUsuarios(cuenta.GetType().ToString(), cuenta.Id, Enums.TipoActividadUsuario.Edito, Base.Usuario.UsuarioConectado.Usuario);
//LogActividades.clsLogActividadUsuariosADO.GuardaActividad(log);
CreaRelacionPuertos(id, relacionPuertos, conn);
resTransaccion.Descripcion = "Se actualizó naviera '" + nombre + "'.";
}
catch (Exception ex)
{
transaction.Rollback();
Log.EscribirLog(ex.Message);
resTransaccion.Estado = Enums.EstadoTransaccion.Rechazada;
resTransaccion.Descripcion = ex.Message;
}
finally
{
conn.Close();
}
return resTransaccion;
}
public int CreateWorkProcess(WorkProcess newWorkProcess)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbWorkprocess(psnCode,psnFullName,wpDate,wpLast,wpOt,wplacking,wpmsalary,wpmOt,wpOrther)");
sb.Append(" VALUES (@psnCode,@psnFullName,@wpDate,@wpLast,@wpOt,@wplacking,@wpmsalary,@wpmOt,@wpOrther)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@psnCode", SqlDbType.NVarChar).Value = newWorkProcess.psnCode;
comm.Parameters.Add("@psnFullName", SqlDbType.NVarChar).Value = newWorkProcess.psnFullName;
comm.Parameters.Add("@wpDate", SqlDbType.NVarChar).Value = newWorkProcess.wpDate;
comm.Parameters.Add("@wpLast", SqlDbType.NVarChar).Value = newWorkProcess.wpLast;
comm.Parameters.Add("@wpOt", SqlDbType.NVarChar).Value = newWorkProcess.wpOt;
comm.Parameters.Add("@wplacking", SqlDbType.NVarChar).Value = newWorkProcess.wplacking;
comm.Parameters.Add("@wpmsalary", SqlDbType.NVarChar).Value = newWorkProcess.wpmsalary;
comm.Parameters.Add("@wpmOt", SqlDbType.NVarChar).Value = newWorkProcess.wpmOt;
comm.Parameters.Add("@wpOrther", SqlDbType.NVarChar).Value = newWorkProcess.wpOrther;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
/// <summary>
/// 批量插入
/// </summary>
private static void BulkCopy( SqlConnection connection, SqlTransaction transaction, string destTableName,
DataTable source, int batchSize, SqlBulkCopyOptions options ) {
using(
var sqlBulkCopy = new SqlBulkCopy( connection, options, transaction ) {
BulkCopyTimeout = 600,
NotifyAfter = source.Rows.Count,
BatchSize = batchSize,
DestinationTableName = destTableName
} ) {
foreach( DataColumn column in source.Columns )
sqlBulkCopy.ColumnMappings.Add( column.ColumnName, column.ColumnName );
sqlBulkCopy.WriteToServer( source );
transaction.Commit();
}
}
//query
public void MS_ExecuteNonQuery(string sqlcmd)
{
try
{
MS_Connection();
st = scon.BeginTransaction();
new SqlCommand(sqlcmd, scon, st).ExecuteNonQuery();
st.Commit();
}
catch (SqlException sqlError)
{
st.Rollback();
throw sqlError;
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
var id = StudentByMatricNumber.GetMatricNumber(txtSearch.Text);
var r = MessageBox.Show(@"Are you sure you want to DELETE? DELETING this record, deletes records in RELATED tables",
@"Nacoss eVoting System",
MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2,
MessageBoxOptions.DefaultDesktopOnly);
if (r == DialogResult.Yes)
{
using (_cnn = new SqlConnection(NacossVotingSystem.Properties.Settings.Default.DBConn))
{
_cnn.Open();
_myTransaction = _cnn.BeginTransaction();
using (_cmd = new SqlCommand("spDeleteCandidateById", _cnn))
{
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.Transaction = _myTransaction;
_cmd.Parameters.AddWithValue("@candidateID", id);
var getCandidateById = new GetAllRecordById();
var deleteById = getCandidateById.GetCandidatesById(txtSearch.Text);
try
{
if (deleteById)
{
_cmd.ExecuteNonQuery();
_myTransaction.Commit();
MessageBox.Show(@"Record Successfully deleted", @"Nacoss eVoting System");
ClearAll();
notifyIcon1.BalloonTipIcon = ToolTipIcon.Info;
notifyIcon1.BalloonTipTitle = @"Delete Data";
notifyIcon1.BalloonTipText = @"Record deleted successfully";
notifyIcon1.ShowBalloonTip(3000);
}
else
MessageBox.Show(@"Record not found in the Database", @"Nacoss eVoting System");
}
catch (SqlException ex)
{
MessageBox.Show(@"The transaction rollbacked due to " +ex.Message);
_myTransaction.Rollback();
}
}
}
}
}
public int CreateLoan(Loan newLoan)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbRoan(psnCode,LID,lmoney,term,lowmoney,loandate,reasonloan,complete)");
sb.Append(" VALUES (@psnCode,@LID,@lmoney,@term,@lowmoney,@loandate,@reasonloa,@complete)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@psnCode", SqlDbType.NVarChar).Value = newLoan.person.psnCode;
comm.Parameters.Add("@LID", SqlDbType.NVarChar).Value = newLoan.ID;
comm.Parameters.Add("@lmoney", SqlDbType.NVarChar).Value = newLoan.lmoney;
comm.Parameters.Add("@term", SqlDbType.NVarChar).Value = newLoan.term;
comm.Parameters.Add("@lowmoney", SqlDbType.NVarChar).Value = newLoan.lowmoney;
comm.Parameters.Add("@loandate", SqlDbType.NVarChar).Value = newLoan.loandate;
comm.Parameters.Add("@reasonloa", SqlDbType.NVarChar).Value = newLoan.reasonLoan;
comm.Parameters.Add("@complete", SqlDbType.NVarChar).Value ="0";
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public static void CommitTransactionAndDisposeConnection(SqlTransaction transaction)
{
var connection = transaction.Connection;
using (connection)
{
using (transaction)
{
transaction.Commit();
}
try
{
connection.Close();
}
catch (Exception) { }
}
}
public void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString))
{
sqlConnection.Open();
using (System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
{
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
try
{
int num = 0;
foreach (DictionaryEntry dictionaryEntry in SQLStringList)
{
string cmdText = dictionaryEntry.Key.ToString();
System.Data.SqlClient.SqlParameter[] array = (System.Data.SqlClient.SqlParameter[])dictionaryEntry.Value;
System.Data.SqlClient.SqlParameter[] array2 = array;
for (int i = 0; i < array2.Length; i++)
{
System.Data.SqlClient.SqlParameter sqlParameter = array2[i];
if (sqlParameter.Direction == System.Data.ParameterDirection.InputOutput)
{
sqlParameter.Value = num;
}
}
DbHelperSQLP.PrepareCommand(sqlCommand, sqlConnection, sqlTransaction, cmdText, array);
int num2 = sqlCommand.ExecuteNonQuery();
array2 = array;
for (int i = 0; i < array2.Length; i++)
{
System.Data.SqlClient.SqlParameter sqlParameter = array2[i];
if (sqlParameter.Direction == System.Data.ParameterDirection.Output)
{
num = Convert.ToInt32(sqlParameter.Value);
}
}
sqlCommand.Parameters.Clear();
}
sqlTransaction.Commit();
}
catch
{
sqlTransaction.Rollback();
throw;
}
}
}
}
public int CreateCompleteNumber(CompleteNumber newCompleteNumber)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO complete_numbers(complete_no,part_name,model_id,scan_step,date_create,user_create_name,status)");
sb.Append(" VALUES (@complete_no,@part_name,@model_id,@scan_step,@date_create,@user_create_name,@status)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@complete_no", SqlDbType.NVarChar).Value = newCompleteNumber.CompleteNo;
comm.Parameters.Add("@part_name", SqlDbType.NVarChar).Value = newCompleteNumber.PartName;
comm.Parameters.Add("@model_id", SqlDbType.NVarChar).Value = newCompleteNumber.ModelNo;
comm.Parameters.Add("@scan_step", SqlDbType.NVarChar).Value = newCompleteNumber.ScanStep;
comm.Parameters.Add("@date_create", SqlDbType.NVarChar).Value = newCompleteNumber.DateCretate;
comm.Parameters.Add("@user_create_name", SqlDbType.NVarChar).Value = newCompleteNumber.UserCreateName;
comm.Parameters.Add("@status", SqlDbType.NVarChar).Value = newCompleteNumber.Status;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public void AddToLibrary()
{
try
{
using (var connection = new C.SqlConnection(
"Server=tcp:igs.database.windows.net,1433;Database=IGS_database;User ID=Glonfindel;Password=Karol1995!;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
))
{
connection.Open();
C.SqlTransaction transaction = connection.BeginTransaction("AddToLib");
using (C.SqlCommand cmd = new C.SqlCommand("IF NOT EXISTS (SELECT * FROM UserLibrary WHERE idUser = @userID AND idGame = @gameID) INSERT INTO UserLibrary VALUES (@userID, @gameID)"))
{
try
{
cmd.Connection = connection;
cmd.Transaction = transaction;
cmd.Parameters.Add("@userID", SqlDbType.Int).Value = Login.user.ID;
cmd.Parameters.Add("@gameID", SqlDbType.Int).Value = this.ID;
cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.GetType().ToString());
MessageBox.Show(ex.Message);
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
MessageBox.Show(ex2.GetType().ToString());
MessageBox.Show(ex2.Message);
}
}
};
}
}
catch
(C.SqlException e)
{
MessageBox.Show(e.Message);
}
}
private void toolStripButtonDelete_Click(object sender, EventArgs e)
{
string lsDealerNo = "";
Validate(); //Validate form so all data sets are updated with field values
lsDealerNo = DEALERcomboBox.Text.ToString().Trim();
tableAdapConn = new System.Data.SqlClient.SqlConnection();
tableAdapConn.ConnectionString = IAC2018SQL.Properties.Settings.Default.IAC2010SQLConnectionString;
tableAdapConn.Open();
dEALERTableAdapter.Connection = tableAdapConn;
tableAdapTran = dEALERTableAdapter.BeginTransaction();
dEALERTableAdapter.Transaction = tableAdapTran;
try
{
dEALERTableAdapter.DeleteQuery(lsDealerNo);
tableAdapTran.Commit();
}
catch (System.Data.SqlClient.SqlException ex)
{
tableAdapTran.Rollback();
MessageBox.Show("The has been a Microsoft SQL Server Database Error: " + ex.Message.ToString());
}
catch (System.InvalidOperationException ex)
{
tableAdapTran.Rollback();
MessageBox.Show("Invalid Operation Error: " + ex.Message.ToString());
}
catch (System.Exception ex)
{
tableAdapTran.Rollback();
MessageBox.Show("General Exception Error: " + ex.Message.ToString());
}
finally
{
iacDataSet.AcceptChanges();
tableAdapConn.Close();
tableAdapConn = null;
tableAdapTran = null;
toolStripButtonDelete.Enabled = false;
toolStripButtonEdit.Enabled = false;
iacDataSet.DEALER.Clear();
Program.gsKey = null;
StartUpConfiguration();
}
}
private void cmdOk_Click(object sender, EventArgs e)
{
try
{
SqlConnection cnSQL = new SqlConnection(MyModules.strConnect);
SqlCommand cmSQL = new SqlCommand();
cmSQL.Connection = cnSQL;
cnSQL.Open();
System.Data.SqlClient.SqlTransaction myTrans = cnSQL.BeginTransaction();
cmSQL.Transaction = myTrans;
int i = 0;
cmSQL.CommandText = "DELETE FROM PyramidScale";
cmSQL.CommandType = CommandType.Text;
cmSQL.ExecuteNonQuery();
cmSQL.CommandText = "UPDATE SystemParameters SET PyramidLevel=" + Convert.ToInt16(numPyramidLevel.Value) + ", TotalPyramidPercent=" + Convert.ToDouble(numPercent.Value) + ",PercentDrawable=" + Convert.ToDouble(numdrawable.Value);
cmSQL.CommandType = CommandType.Text;
cmSQL.ExecuteNonQuery();
for (i = 0; i < dGrid.RowCount; i++)
{
cmSQL.Parameters.Clear();
cmSQL.CommandText = "InsertPyramidScale";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@PyramidLevel", dGrid[0, i].Value);
cmSQL.Parameters.AddWithValue("@PyramidPercent", dGrid[1, i].Value);
cmSQL.ExecuteNonQuery();
}
myTrans.Commit();
cnSQL.Close();
MyModules.PyramidLevel = Convert.ToInt16(numPyramidLevel.Value);
MyModules.PyramidPercent = Convert.ToDouble(numPercent.Value);
MyModules.PercentDrawable = Convert.ToDouble(numdrawable.Value);
MessageBox.Show("Update Successfull!", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public int CreateBoxNumber(BoxNumber newBoxNumber)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO box_numbers(complete_no,quantity,suplier,date_create,user_create_name,status)");
sb.Append(" VALUES (@complete_no,@quantity,@suplier,@date_create,@user_create_name,@status)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@complete_no", SqlDbType.NVarChar).Value = newBoxNumber.CompleteNo;
comm.Parameters.Add("@quantity", SqlDbType.NVarChar).Value = newBoxNumber.Quantity;
comm.Parameters.Add("@suplier", SqlDbType.NVarChar).Value = newBoxNumber.Suplier;
comm.Parameters.Add("@date_create", SqlDbType.NVarChar).Value = newBoxNumber.DateCreate;
comm.Parameters.Add("@user_create_name", SqlDbType.NVarChar).Value = newBoxNumber.UserCreateName;
comm.Parameters.Add("@status", SqlDbType.NVarChar).Value = newBoxNumber.Status;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public static ResultadoTransaccion GuardarDefinicionCredito(clsCreditoCliente ObjCredito)
{
ResultadoTransaccion res = new ResultadoTransaccion();
try
{
//Abrir Conexion
conn = BaseDatos.Conexion();
//Crear Transaccion
transaction = conn.BeginTransaction();
//Registrar Llamada Telefonica
objParams = SqlHelperParameterCache.GetSpParameterSet(conn, "SP_N_DIRECCION_CREDITO_CLIENTE");
objParams[0].Value = ObjCredito.Id;
objParams[1].Value = ObjCredito.ObjCuenta.Id;
objParams[2].Value = ObjCredito.ObjMoneda.Id;
objParams[3].Value = ObjCredito.MontoLineaCredito;
SqlCommand command = new SqlCommand("SP_N_DIRECCION_CREDITO_CLIENTE", conn);
command.Transaction = transaction;
command.Parameters.AddRange(objParams);
command.CommandType = CommandType.StoredProcedure;
ObjCredito.Id = Convert.ToInt32(command.ExecuteScalar());
//Ejecutar transaccion
transaction.Commit();
res.Estado = Enums.EstadoTransaccion.Aceptada;
res.Descripcion = "Se registró el crédito del cliente correctamente";
}
catch (Exception ex)
{
transaction.Rollback();
Log.EscribirLog(ex.Message);
res.Descripcion = ex.Message;
res.ArchivoError = "clsControlCreditoAdo.cs";
res.MetodoError = "GuardarDefinicionCredito";
res.Estado = Enums.EstadoTransaccion.Rechazada;
}
finally
{
conn.Close();
}
return res;
}
private void btnCommit_Click(object sender, EventArgs e)
{
if (rtbRemark.Text.Trim() == "")
{
MessageBox.Show("กรุณาป้อนเหตุผลในการแก้ไข !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
rtbRemark.Focus();
return;
}
else
{
tr = Conn.BeginTransaction();
try
{
string sqlSave;
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO EditLogSale (SaleHeadId,Detail,DateTime,UserName ");
sb.Append("VALUES (@SaleHeadId,@Detail,@DateTime,@UserName)");
sqlSave = sb.ToString();
com.Parameters.Clear();
com.Transaction = tr;
com.CommandText = sqlSave;
com.Parameters.Add("@SaleHeadId", SqlDbType.NVarChar).Value = lblSaleId.Text.Trim();
com.Parameters.Add("@Detail", SqlDbType.NVarChar).Value = rtbRemark.Text.Trim();
com.Parameters.Add("@DateTime", SqlDbType.DateTime).Value = DateTime.Now;
com.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = userId;
com.ExecuteNonQuery();
MessageBox.Show(sqlSave);
tr.Commit();
}
catch (Exception ex)
{
MessageBox.Show("ไม่สามารถบันทึกรายการลูกค้าได้ค่ะ เนื่องจาก " + ex.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning);
tr.Rollback();
}
//SalePos7CSK.frmEditSalePos7CSK frm = new KTCERP.SalePos7CSK.frmEditSalePos7CSK();
//frm.setSaleId = lblSaleId.Text;
//frm.ShowDialog();
}
}
protected void ButtonAddUser_Click(object sender, EventArgs e)
{
try
{
strSQLAddNewuser = "******";
strSQLAddNewuser += "(salutationID, firstName, lastName, logonName, passwd)";
strSQLAddNewuser += " VALUES ";
strSQLAddNewuser += " (1, 'Angel', 'Alferez', 'aalferez', '12345')";
connection.Open();
transaction = connection.BeginTransaction();
command.Transaction = transaction;
//Add user to table
command = new SqlCommand(strSQLAddNewuser, connection, transaction);
command.ExecuteNonQuery();
//select user from table
strSQLSelectUser = "******";
command = new SqlCommand(strSQLSelectUser, connection, transaction);
sqlDataAdapter = new SqlDataAdapter(command);
sqlDataAdapter.Fill(dataSet);
int PeopleID = (int)dataSet.Tables[0].Rows[0]["PeopleID"];
//Insert role in table
strSQLAddRole = "INSERT INTO roles ";
strSQLAddRole += "( PeopleID, roleTypeID, RoleActive )";
strSQLAddRole += "VALUES";
strSQLAddRole += "( " + PeopleID + ", 1, 1)";
command = new SqlCommand(strSQLAddRole, connection, transaction);
command.ExecuteNonQuery();
command = new SqlCommand("SELECT * FROM People", connection, transaction);
sqlDataAdapter = new SqlDataAdapter(command);
dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet);
GridViewUsers.DataSource = dataSet;
GridViewUsers.DataBind();
transaction.Commit();
}
catch { }
}
public static bool ExecuteNonQuery(System.Data.SqlClient.SqlCommand[] sqlCmdArray)
{
if (Connection1.State == ConnectionState.Closed)
{
Connection1.Open();
}
Transaction = Connection.BeginTransaction();
for (int i = 0; i < sqlCmdArray.Length; i++)
{
if (!ExecuteNonQuery(sqlCmdArray[i]))
{
Transaction.Rollback();
Connection1.Close();
return(false);
}
}
Transaction.Commit();
Connection1.Close();
return(true);
}
public void Close()
{
if (sqlConnection != null)
{
if (sqlTransaction != null)
{
try
{
sqlTransaction.Commit();
}
catch (Exception)
{
}
sqlTransaction = null;
}
sqlConnection.Close();
sqlConnection.Dispose();
sqlConnection = null;
}
}
/// <summary>
/// 事务处理
/// </summary>
/// <param name="SqlList"></param>
/// <returns></returns>
public bool DoBatchWork(SortedList SqlList)
{
System.Data.SqlClient.SqlConnection conn;
System.Data.SqlClient.SqlCommand dbCommand;
System.Data.SqlClient.SqlTransaction tempTransaction = null;
int i;
string tempSql;
conn = new SqlConnection(mConnectInfo);
dbCommand = new SqlCommand();
dbCommand.Connection = conn;
try
{
conn.Open(); //建立数据库连接
tempTransaction = conn.BeginTransaction();
for (i = 0; i <= SqlList.Count - 1; i++)
{
tempSql = SqlList.GetByIndex(i).ToString();
dbCommand.Transaction = tempTransaction;
dbCommand.CommandText = tempSql;
dbCommand.ExecuteNonQuery();
}
//提交事务
tempTransaction.Commit();
return(true);
}
catch (System.Data.SqlClient.SqlException e)
{
tempTransaction.Rollback();
throw new Exception(e.Message);
}
finally
{
conn.Close();
}
}
protected void btnReviseForApp_Click(object sender, System.EventArgs e)
{
Label label = (Label)base.Master.FindControl("lbltotalinfo");
this.cn.Open();
System.Data.SqlClient.SqlTransaction transaction = this.cn.BeginTransaction();
try
{
if (this.grdForApproval.Rows.Count > 0)
{
for (int i = 0; i < this.grdForApproval.Rows.Count; i = (int)(i + 1))
{
CheckBox box = (CheckBox)this.grdForApproval.Rows[i].FindControl("chkSelectforApp");
if (box.Checked)
{
string str = this.grdForApproval.Rows[i].Cells[3].Text.Trim();
string[] strArray = new string[] { "@PONO", "@revuser" };
string[] strArray2 = new string[] { str.Trim(), this.Session["Uid"].ToString() };
this._mc.MC_Save_nodt_tr("Sp_Smt_BOM_PORevise", this.cn, transaction, strArray, strArray2);
}
}
}
transaction.Commit();
label.Text = ("Revised Successfully");
//this.bindforapproval();
//this.bindapprove();
//this.bindrevised();
}
catch (System.Exception)
{
transaction.Rollback();
label.Text = ("Unable to Revise");
}
finally
{
this.cn.Close();
}
}
bool IDBOptionBySqlSentence.ExecuteSqlTran(string[] sqlStrs)
{
bool check = true;
SqlConnection conn = new SqlConnection(((IDBOptionBySqlSentence)this).ConnectionString);
SqlCommand cmd = new SqlCommand();
using (conn)
{
conn.Open();
System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
using (trans)
{
try
{
cmd.Connection = conn;
cmd.Transaction = trans;
foreach (string str in sqlStrs)
{
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
check = false;
trans.Rollback();
}
finally
{
cmd.Dispose();
conn.Close();
}
}
}
return(check);
}
protected void btncheckout_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection();
var conString = System.Configuration.ConfigurationManager.ConnectionStrings["GoDaddySQL"];
conn.ConnectionString = conString.ConnectionString;
conn.Open();
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
comm.Connection = conn;
string sql = "";
//create the transaction
System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
comm.Transaction = trans;
sql = "update huber_orders set ordercomplete = 'true' where orderid = @orderid";
var result = "";
comm.CommandText = sql;
comm.Parameters.AddWithValue("@orderid", Session["OrderId"]);
try
{
comm.ExecuteNonQuery();
//we are pretending that a cc starting with 1 is a bad CC# and the CC transaction fails
if (cc.Text.IndexOf("1") == 0)
{
trans.Rollback();
lblError.Text = "Your transaction failed!";
}
else
{
lblError.Text = "Your transaction succeeded!";
trans.Commit();
sql = "";
//it is a post back. This means they clicked the search button.
// 0 1 2 3 4
sql = "select huber_products.productid, quantity, image, price, description from huber_products inner join huber_orderlines on huber_products.productid = huber_orderlines.productid where orderid = @orderid";
comm.Parameters.Clear();
comm.Parameters.AddWithValue("@orderid", Session["orderid"].ToString());
comm.CommandText = sql;
System.Data.SqlClient.SqlDataReader dr;
dr = comm.ExecuteReader();
string messagebody = "";
while (dr.Read())
{
messagebody += dr.GetValue(1).ToString() + " " + dr.GetValue(3).ToString() + " " + dr.GetValue(4).ToString() + "<br />";
}
DeVry.Huber.Mail.HuberMessage message = new DeVry.Huber.Mail.HuberMessage();
message.Send(messagebody, "*****@*****.**", "*****@*****.**", "Your order details");
}
}
catch (Exception ex)
{
trans.Rollback();
}
}
public void Commit()
{
Tr.Commit();
}
private void cmdOk_Click(object sender, EventArgs e)
{
try
{
//if (lblAction.Text == "New Record")
//{
// tCode1.Text = FetchNextNo();
//}
if (string.IsNullOrEmpty(tRefNo.Text.Trim(' ')) || string.IsNullOrEmpty(tClientsDetails.Text.Trim(' ')) || string.IsNullOrEmpty(tServiceDetails.Text.Trim(' ')) || string.IsNullOrEmpty(tServiceValue.Text.Trim(' ')) || string.IsNullOrEmpty(tAmountPaid.Text.Trim(' ')) || string.IsNullOrEmpty(tReward.Text.Trim(' ')))
{
MessageBox.Show("Incomplete relevant data", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (Convert.ToDouble(tServiceValue.Text) != Convert.ToDouble(tAmountPaid.Text) + Convert.ToDouble(tReward.Text))
{
MessageBox.Show("Amount Payable do not correspond with actual payment ", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (Convert.ToDouble(tReward.Text) > Convert.ToDouble(tRewardBalance.Text))
{
MessageBox.Show("Drawal on Reward more than available !!!", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (Convert.ToDouble(tReward.Text) > Convert.ToDouble(tRewardBalance.Text) * (MyModules.PercentDrawable / 100))
{
MessageBox.Show("Reward overdrawn !!!", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
SqlConnection cnSQL = new SqlConnection(MyModules.strConnect);
SqlCommand cmSQL = new SqlCommand();
cmSQL.Connection = cnSQL;
cnSQL.Open();
System.Data.SqlClient.SqlTransaction myTrans = cnSQL.BeginTransaction();
cmSQL.Transaction = myTrans;
// Dim drSQL As SqlDataReader
if (Action == AppAction.Add)
{
FetchNextNo();
string Telephone = MyModules.GetIt4Me(tClientsDetails.Text, " (");
cmSQL.Parameters.Clear();
cmSQL.CommandText = "InsertServices";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@Sn", tTransNo.Text);
cmSQL.Parameters.AddWithValue("@TransDate", dtpDate.Text);
cmSQL.Parameters.AddWithValue("@RefNo", tRefNo.Text);
cmSQL.Parameters.AddWithValue("@Name", tClientsDetails.Text.ToString().Substring(Telephone.Length + 2, tClientsDetails.Text.ToString().Length - Telephone.Length - 3));
cmSQL.Parameters.AddWithValue("@Telephone", Telephone);
cmSQL.Parameters.AddWithValue("@ServiceRendered", tServiceDetails.Text);
cmSQL.Parameters.AddWithValue("@ValueOfService", Convert.ToDouble(tServiceValue.Text));
cmSQL.Parameters.AddWithValue("@AmountPaid", Convert.ToDouble(tAmountPaid.Text));
cmSQL.Parameters.AddWithValue("@AmountChargedFromReward", Convert.ToDouble(tReward.Text));
cmSQL.Parameters.AddWithValue("@Username", MyModules.sysUserName);
cmSQL.ExecuteNonQuery();
for (int t = 0; t <= lvList.Items.Count - 1; t++)
{
cmSQL.Parameters.Clear();
cmSQL.CommandText = "InsertRewards";
cmSQL.Parameters.AddWithValue("@SourceRefNo", tTransNo.Text);
cmSQL.Parameters.AddWithValue("@ClientRefNo", lvList.Items[t].Text);
cmSQL.Parameters.AddWithValue("@Reward", lvList.Items[t].SubItems[3].Text);
cmSQL.Parameters.AddWithValue("@RewardPercent", lvList.Items[t].SubItems[2].Text);
cmSQL.Parameters.AddWithValue("@ClientDetails", lvList.Items[t].SubItems[4].Text);
cmSQL.Parameters.AddWithValue("@CourtesyRefNo", tRefNo.Text);
cmSQL.Parameters.AddWithValue("@CourtesyClientDetails", tClientsDetails.Text.ToString());
cmSQL.ExecuteNonQuery();
}
}
if (Action == AppAction.Edit)
{
cmSQL.Parameters.Clear();
cmSQL.CommandText = "DeleteServices";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@Sn", tTransNo.Text);
cmSQL.ExecuteNonQuery();
cmSQL.Parameters.Clear();
cmSQL.CommandText = "DeleteRewards";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@SourceRefNo", tTransNo.Text);
cmSQL.ExecuteNonQuery();
//cmSQL.Parameters.Clear();
//cmSQL.CommandText = "DeleteSales";
//cmSQL.CommandType = CommandType.StoredProcedure;
//cmSQL.Parameters.AddWithValue("@OrderNo", tTransNo.Text);
//cmSQL.ExecuteNonQuery();
string Telephone = MyModules.GetIt4Me(tClientsDetails.Text, " (");
cmSQL.Parameters.Clear();
cmSQL.CommandText = "InsertServices";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@Sn", tTransNo.Text);
cmSQL.Parameters.AddWithValue("@TransDate", dtpDate.Text);
cmSQL.Parameters.AddWithValue("@RefNo", tRefNo.Text);
cmSQL.Parameters.AddWithValue("@Name", tClientsDetails.Text.ToString().Substring(Telephone.Length + 2, tClientsDetails.Text.ToString().Length - Telephone.Length - 3));
cmSQL.Parameters.AddWithValue("@Telephone", Telephone);
cmSQL.Parameters.AddWithValue("@ServiceRendered", tServiceDetails.Text);
cmSQL.Parameters.AddWithValue("@ValueOfService", Convert.ToDouble(tServiceValue.Text));
cmSQL.Parameters.AddWithValue("@AmountPaid", Convert.ToDouble(tAmountPaid.Text));
cmSQL.Parameters.AddWithValue("@AmountChargedFromReward", Convert.ToDouble(tReward.Text));
cmSQL.Parameters.AddWithValue("@Username", MyModules.sysUserName);
cmSQL.ExecuteNonQuery();
for (int t = 0; t <= lvList.Items.Count - 1; t++)
{
cmSQL.Parameters.Clear();
cmSQL.CommandText = "InsertRewards";
cmSQL.Parameters.AddWithValue("@SourceRefNo", tTransNo.Text);
cmSQL.Parameters.AddWithValue("@ClientRefNo", lvList.Items[t].Text);
cmSQL.Parameters.AddWithValue("@Reward", lvList.Items[t].SubItems[3].Text);
cmSQL.Parameters.AddWithValue("@RewardPercent", lvList.Items[t].SubItems[2].Text);
cmSQL.Parameters.AddWithValue("@ClientDetails", lvList.Items[t].SubItems[4].Text);
cmSQL.Parameters.AddWithValue("@CourtesyRefNo", tRefNo.Text);
cmSQL.Parameters.AddWithValue("@CourtesyClientDetails", tClientsDetails.Text.ToString());
cmSQL.ExecuteNonQuery();
}
}
if (Action == AppAction.Delete)
{
if (MessageBox.Show("The selected record would be Deleted Parmanently....Continue(y/n)?", MyModules.strApptitle, MessageBoxButtons.YesNo, MessageBoxIcon.Information) != DialogResult.Yes)
{
return;
}
cmSQL.Parameters.Clear();
cmSQL.CommandText = "DeleteServices";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@Sn", tTransNo.Text);
cmSQL.ExecuteNonQuery();
cmSQL.Parameters.Clear();
cmSQL.CommandText = "DeleteRewards";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@SourceRefNo", tTransNo.Text);
cmSQL.ExecuteNonQuery();
cmSQL.Parameters.Clear();
cmSQL.CommandText = "DeleteSales";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@OrderNo", tTransNo.Text);
cmSQL.ExecuteNonQuery();
try {
cmSQL.Parameters.Clear();
cmSQL.CommandText = "Delete FROM SalonFinance WHERE EnteredBy='" + tTransNo.Text + "'";
cmSQL.CommandType = CommandType.Text;
cmSQL.ExecuteNonQuery();
}
catch
{
}
}
myTrans.Commit();
cmSQL.Connection.Close();
cmSQL.Dispose();
cnSQL.Close();
cnSQL.Dispose();
mnuNew_Click(sender, e);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
protected void UpdateSearchResultSet(System.Collections.Specialized.IOrderedDictionary newValues)
{
Logging.Logger.Instance.Info(String.Format("Updating Result Set"));
string connectionString = SPContext.Current.Site.RootWeb.Properties[SusDeb.DOI.Common.Utilities.eCaseConstants.PropertyBagKeys.ECASE_DB_CONNECTION_STRING];
long searchRowIdentity;
using (SqlConnection conn = new SqlConnection(connectionString))
{
Logging.Logger.Instance.Info(String.Format("Updating Saved Search Result Set. Opening database connection to: {0}", connectionString));
conn.Open();
using (System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction())
{
//Insert the Saved Search Results Parent Entry
using (SqlCommand updateCommand = new System.Data.SqlClient.SqlCommand())
{
updateCommand.Connection = conn;
updateCommand.Transaction = trans;
updateCommand.CommandType = CommandType.StoredProcedure;
updateCommand.CommandText = "CreateSavedSearchResult";
updateCommand.Parameters.AddWithValue("@Id", newValues["Id"]);
updateCommand.Parameters.AddWithValue("@Name", newValues["Name"]);
updateCommand.Parameters.AddWithValue("@Description", newValues["Description"]);
updateCommand.Parameters.AddWithValue("@Query", newValues["OriginalQuery"]);
updateCommand.Parameters.AddWithValue("@Owner", newValues["Owner"]);
searchRowIdentity = (Int64)updateCommand.ExecuteScalar();
}
using (System.Data.SqlClient.SqlDataAdapter permsAdapter = new System.Data.SqlClient.SqlDataAdapter())
{
//for permissions, always remove all items and then add them back
using (System.Data.SqlClient.SqlCommand permsDeleteCommand = new System.Data.SqlClient.SqlCommand())
{
permsDeleteCommand.Connection = conn;
permsDeleteCommand.Transaction = trans;
permsDeleteCommand.CommandText = @"DELETE FROM SavedSearchResultPermissions
WHERE SavedSearchResultsId = @SavedSearchResultsId
";
permsDeleteCommand.Parameters.Add("@SavedSearchResultsId", System.Data.SqlDbType.BigInt);
permsDeleteCommand.Parameters["@SavedSearchResultsId"].Value = searchRowIdentity;
permsDeleteCommand.ExecuteNonQuery();
}
using (System.Data.SqlClient.SqlCommand permsInsertCommand = new System.Data.SqlClient.SqlCommand())
{
permsInsertCommand.Connection = conn;
permsInsertCommand.Transaction = trans;
permsInsertCommand.CommandText = @"INSERT INTO SavedSearchResultPermissions
(
SavedSearchResultsId, PermissionName
)
VALUES
(
@SavedSearchResultsId, @PermissionName
)";
permsInsertCommand.Parameters.Add("@SavedSearchResultsId", System.Data.SqlDbType.BigInt);
permsInsertCommand.Parameters["@SavedSearchResultsId"].Value = searchRowIdentity;
permsInsertCommand.Parameters.Add("@PermissionName", System.Data.SqlDbType.NVarChar, 100, "PermissionName");
string[] shareWithAccounts = newValues["ShareWith"].ToString().Split(',');
foreach (string account in shareWithAccounts)
{
permsInsertCommand.Parameters["@PermissionName"].Value = account.ToString();
permsInsertCommand.ExecuteNonQuery();
}
}
}
trans.Commit();
}
}
}
/// <summary>
/// 提交
/// </summary>
public void Commit()
{
trans.Commit();
}
private void CmdInsert_Click(object sender, EventArgs e)
{
try
{
SqlConnection cnSQL = new SqlConnection(MyModules.strConnect);
SqlCommand cmSQL = new SqlCommand();
cmSQL.Connection = cnSQL;
if (string.IsNullOrEmpty(tState.Text.Trim(' ')) || string.IsNullOrEmpty(tLGA.Text.Trim(' ')))
{
MessageBox.Show("Incomplete data", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
cnSQL.Open();
System.Data.SqlClient.SqlTransaction myTrans = null;
if (string.IsNullOrEmpty(Convert.ToString(tState.Tag).Trim(' ')))
{
cmSQL.CommandText = "InsertBank";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@State", tState.Text);
cmSQL.Parameters.AddWithValue("@LGA", tLGA.Text);
cmSQL.ExecuteNonQuery();
}
else
{
myTrans = cnSQL.BeginTransaction();
cmSQL.Transaction = myTrans;
cmSQL.Parameters.Clear();
cmSQL.CommandText = "DeleteState";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@State", tState.Tag);
cmSQL.Parameters.AddWithValue("@LGA", tLGA.Tag);
cmSQL.ExecuteNonQuery();
cmSQL.Parameters.Clear();
cmSQL.CommandText = "InsertState";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@State", tState.Text);
cmSQL.Parameters.AddWithValue("@LGA", tLGA.Text);
cmSQL.ExecuteNonQuery();
myTrans.Commit();
}
cmSQL.Dispose();
cnSQL.Close();
oLoad();
//tState.Text = "";
tLGA.Text = "";
}
catch (Exception ex)
{
if (Microsoft.VisualBasic.Information.Err().Number == 5)
{
MessageBox.Show("Pls. selected an entry to Edit", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show(ex.Message.ToString(), MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
