public static DataSet RunFreeTextQuery(string query, string connectionString,
ICollection <SqlParameter> sqlParameters = null, bool withTransaction = false)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
SqlTransaction t = null;
if (withTransaction)
{
t = connection.BeginTransaction(IsolationLevel.Serializable);
}
using (var command = new SqlCommand(query, connection, t))
{
command.CommandType = CommandType.Text;
if (sqlParameters != null)
{
command.Parameters.AddRange(sqlParameters.ToArray());
}
var da = new SqlDataAdapter(command);
var ds = new DataSet();
da.Fill(ds);
t?.Commit();
return(ds);
}
}
}
protected async Task DoInsertOrUpdateTestAsync(ISqlBulkHelper <TestElement> sqlBulkIdentityHelper, SqlTransaction transaction)
{
List <TestElement> testData = TestHelpers.CreateTestData(10);
var results = await sqlBulkIdentityHelper.BulkInsertOrUpdateAsync(
testData,
TestHelpers.TestTableName,
transaction
);
transaction?.Commit();
//ASSERT Results are Valid...
Assert.IsNotNull(results);
//We Sort the Results by Identity Id to ensure that the inserts occurred in the correct
// order with incrementing ID values as specified in the original Data!
//This validates that data is inserted as expected for Identity columns so that it can
// be correctly sorted by Incrementing Identity value when Queried (e.g. ORDER BY Id)
var resultsSorted = results.OrderBy(r => r.Id).ToList();
Assert.AreEqual(resultsSorted.Count(), testData.Count);
var i = 0;
foreach (var result in resultsSorted)
{
Assert.IsNotNull(result);
Assert.IsTrue(result.Id > 0);
Assert.AreEqual(result.Key, testData[i].Key);
Assert.AreEqual(result.Value, testData[i].Value);
i++;
}
}
public override void Commit()
{
ThrowIfDisposed();
if (!_ownsTransaction && _transaction != null)
{
throw new InvalidOperationException(Messages.CommitExternalTransaction);
}
_transaction?.Commit();
}
public void Dispose()
{
if (transaction?.Connection != null)
{
transaction?.Commit();
}
connection?.Close();
transaction?.Dispose();
connection?.Dispose();
}
public void CloseConnection(bool rollback = false)
{
if (rollback)
{
transaction?.Rollback();
}
else
{
transaction?.Commit();
}
conn.Close();
}
public void Dispose()
{
if (_errorOccured)
{
_sqlTransaction?.Rollback();
}
else
{
_sqlTransaction?.Commit();
}
_sqlTransaction?.Dispose();
_sqlBulkCopy.Close();
_sqlBulkCopy?.Close();
_sqlConnection?.Close();
_sqlConnection?.Dispose();
}
public static ReturnedSaveFuncInfo CommitTran(this SqlTransaction tr, SqlConnection cn = null)
{
var ret = new ReturnedSaveFuncInfo();
try
{
tr?.Commit();
}
catch (Exception ex)
{
ret.AddReturnedValue(ex);
WebErrorLog.ErrorInstence.StartErrorLog(ex);
}
finally
{
ret.AddReturnedValue(CloseConnection(cn));
}
return(ret);
}
/// <summary>
/// 表批量写入
/// 根据行数据 RowState 状态新增、修改
/// </summary>
/// <param name="dt">数据表（Namespace=SchemaName，TableName=TableName）</param>
/// <param name="sqlEmpty">查询空表脚本，默认*，可选列，会影响数据更新的列</param>
/// <param name="dataAdapter">执行前修改（命令行脚本、超时等信息）</param>
/// <param name="openTransaction">开启事务，默认开启</param>
/// <returns></returns>
public int BulkBatchSQLServer(DataTable dt, string sqlEmpty = null, Action <SqlDataAdapter> dataAdapter = null, bool openTransaction = true)
{
return(SafeConn(() =>
{
var connection = (SqlConnection)Connection;
SqlTransaction transaction = openTransaction ? (SqlTransaction)(Transaction = connection.BeginTransaction()) : null;
var cb = new SqlCommandBuilder();
if (string.IsNullOrWhiteSpace(sqlEmpty))
{
var sntn = SqlSNTN(dt.TableName, dt.Namespace, SharedEnum.TypeDB.SQLServer);
sqlEmpty = SqlEmpty(sntn);
}
cb.DataAdapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand(sqlEmpty, connection, transaction)
};
cb.ConflictOption = ConflictOption.OverwriteChanges;
var da = new SqlDataAdapter
{
InsertCommand = cb.GetInsertCommand(true),
UpdateCommand = cb.GetUpdateCommand(true)
};
da.InsertCommand.CommandTimeout = 300;
da.UpdateCommand.CommandTimeout = 300;
//执行前修改
dataAdapter?.Invoke(da);
var num = da.Update(dt);
transaction?.Commit();
return num;
}));
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static int ExecuteSqlTran(System.Collections.Generic.List <CommandInfo> cmdList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
int count = 0;
//循环
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
return(0);
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
return(0);
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
return(0);
}
continue;
}
int val = cmd.ExecuteNonQuery();
count += val;
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
return(0);
}
cmd.Parameters.Clear();
}
trans.Commit();
return(count);
}
catch
{
trans.Rollback();
throw;
}
}
}
}
/// <summary> /// 処理をコミットする /// </summary> public void Commit() => SqlTransaction?.Commit();
protected void GrdDatos_RowCommand(object sender, GridViewCommandEventArgs e)
{
Idioma = (DataTable)ViewState["TablaIdioma"];
PerfilesGrid();
if (e.CommandName.Equals("AddNew"))
{
string VbDesc, VBQuery;
VbDesc = (GrdDatos.FooterRow.FindControl("TxtNomPP") as TextBox).Text.Trim();
if (VbDesc == String.Empty)
{
DataRow[] Result = Idioma.Select("Objeto= 'MstrMens06'");
foreach (DataRow row in Result)
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + row["Texto"].ToString() + "');", true);
} //Debe ingresar una descripción')", true);
return;
}
Cnx.SelecBD();
using (SqlConnection sqlCon = new SqlConnection(Cnx.GetConex()))
{
sqlCon.Open();
using (SqlTransaction Transac = sqlCon.BeginTransaction())
{
VBQuery = "EXEC SP_TablasIngenieria 14,@Desc,@US,'','','','','','','INSERT',0,0,0,0,0,@ICC,'01-01-1','02-01-1','03-01-1'";
using (SqlCommand SC = new SqlCommand(VBQuery, sqlCon, Transac))
{
SC.Parameters.AddWithValue("@Desc", VbDesc);
SC.Parameters.AddWithValue("@US", Session["C77U"].ToString());
SC.Parameters.AddWithValue("@ICC", Session["!dC!@"]);
try
{
var Mensj = SC.ExecuteScalar();
if (!Mensj.ToString().Trim().Equals(""))
{
DataRow[] Result = Idioma.Select("Objeto= '" + Mensj.ToString().Trim() + "'");
foreach (DataRow row in Result)
{
Mensj = row["Texto"].ToString().Trim();
}
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + Mensj + "');", true);
Transac.Rollback();
return;
}
Transac.Commit();
BindData(TxtBusqueda.Text);
}
catch (Exception ex)
{
Transac.Rollback();
DataRow[] Result = Idioma.Select("Objeto= 'MensErrIng'");
foreach (DataRow row in Result)
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + row["Texto"].ToString() + "');", true);
} //Error en el ingreso')", true);
Cnx.UpdateErrorV2(Session["C77U"].ToString(), ViewState["PFileName"].ToString().Trim(), "INSERT", ex.StackTrace.Substring(ex.StackTrace.Length - 300, 300), ex.Message, Session["77Version"].ToString(), Session["77Act"].ToString());
}
}
}
}
}
}
public void Commit()
{
_sqltransaction?.Commit();
}
public virtual void LoadItems(BulkLoadContext context, IEnumerable <BulkLoadItem> items)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
if (items == null)
{
throw new ArgumentNullException(nameof(items));
}
items = OnItemProcessing.Execute(items, (p, itms) => p.Process(context, itms));
var db = Factory.GetDatabase(context.Database, true);
var connectionString = ConfigurationManager.ConnectionStrings[db.ConnectionStringName].ConnectionString;
var infoMessageHandler = new SqlInfoMessageEventHandler((s, e) => OnSqlInfoMessage(context, s, e));
using (var conn = new SqlConnection(connectionString))
{
var sqlContext = new BulkLoadSqlContext(conn, typeof(BulkLoader));
SqlTransaction transaction = null;
try
{
conn.InfoMessage += infoMessageHandler;
conn.Open();
BulkItemsAndFieldsReader itemAndFieldReader;
if (!StageData(context, sqlContext, items, out itemAndFieldReader))
{
return;
}
if (context.StageDataWithoutProcessing)
{
context.Log.Info("Data to import is staged in database, no processing done.");
context.StageSucceeded(Stage.Load);
return;
}
if (itemAndFieldReader.ReadItemCount > 0)
{
LookupIds(context, sqlContext, itemAndFieldReader);
if (!ValidateAndPrepareData(context, sqlContext))
{
return;
}
sqlContext.Transaction = transaction = conn.BeginTransaction();
MergeData(context, sqlContext, itemAndFieldReader);
}
OnTransactionCommitting.Execute(p => p.Process(context, sqlContext));
// After this point, there's no use in keeping the transaction arround,
// because we cannot sync everything transactionally (e.g. indexes, publshing, ...)
// Be aware that after this point the process may halt and not everything is consistent.
// We mitigate this inconsistency with crash recovery, see below.
transaction?.Commit();
transaction = null;
// Allow clearing caches before raising event so that reading the item API in event will result in fresh reads.
OnItemsLoading.Execute(p => p.Process(context, sqlContext, context.ItemChanges));
// Databases are now entirely in sync.
context.OnDataLoaded?.Invoke(context);
Event.RaiseEvent("bulkloader:dataloaded", context);
// Execute post processors.y.
var itemChanges = GetChanges(context, sqlContext);
OnItemsLoaded.Execute(p => p.Process(context, sqlContext, itemChanges));
context.StageSucceeded(Stage.Load);
}
catch (Exception ex)
{
transaction?.Rollback();
context.StageFailed(Stage.Load, ex.Message);
}
finally
{
conn.InfoMessage -= infoMessageHandler;
}
}
}
/// <summary>
/// 执行Sql和Oracle滴混合事务
/// </summary>
/// <param name="list">SQL命令行列表</param>
/// <param name="oracleCmdSqlList">Oracle命令行列表</param>
/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
public static int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo myDE in list)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (isHave)
{
//引发事件
myDE.OnSolicitationEvent();
}
}
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
//return 0;
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
//return 0;
}
continue;
}
int val = cmd.ExecuteNonQuery();
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
//return 0;
}
cmd.Parameters.Clear();
}
string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
if (!res)
{
tx.Rollback();
throw new Exception("Oracle执行失败");
// return -1;
}
tx.Commit();
return(1);
}
catch (System.Data.SqlClient.SqlException e)
{
tx.Rollback();
throw e;
}
catch (Exception e)
{
tx.Rollback();
throw e;
}
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.daikuan_repay model)
{
using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
#region 添加主表数据====================
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into daikuan_repay(");
strSql.Append("daikuan_id,amount,add_time)");
strSql.Append(" values (");
strSql.Append("@daikuan_id,@amount,@add_time)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@daikuan_id", SqlDbType.Int, 4),
new SqlParameter("@amount", SqlDbType.Decimal, 9),
new SqlParameter("@add_time", SqlDbType.DateTime)
};
parameters[0].Value = model.daikuan_id;
parameters[1].Value = model.amount;
parameters[2].Value = model.add_time;
//添加主表数据
object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), parameters); //带事务
model.id = Convert.ToInt32(obj);
#endregion
#region 添加图片相册====================
if (model.albums != null)
{
StringBuilder strSql3;
foreach (Model.daikuan_repay_albums modelt in model.albums)
{
strSql3 = new StringBuilder();
strSql3.Append("insert into daikuan_repay_albums(");
strSql3.Append("daikuan_id,thumb_path,original_path,remark,link_url)");
strSql3.Append(" values (");
strSql3.Append("@daikuan_id,@thumb_path,@original_path,@remark,@link_url)");
SqlParameter[] parameters3 =
{
new SqlParameter("@daikuan_id", SqlDbType.Int, 4),
new SqlParameter("@thumb_path", SqlDbType.NVarChar, 255),
new SqlParameter("@original_path", SqlDbType.NVarChar, 255),
new SqlParameter("@remark", SqlDbType.NVarChar, 500),
new SqlParameter("@link_url", SqlDbType.NVarChar, 200)
};
parameters3[0].Value = model.id;
parameters3[1].Value = modelt.thumb_path;
parameters3[2].Value = modelt.original_path;
parameters3[3].Value = modelt.remark;
parameters3[4].Value = modelt.link_url;
DbHelperSQL.GetSingle(conn, trans, strSql3.ToString(), parameters3); //带事务
}
}
#endregion
trans.Commit();
}
catch
{
trans.Rollback();
return(0);
}
}
}
return(model.id);
}
void restoreBackupTable(BackupTable bkup, DataAccessObject dao, int totalCount, ref int currentCount)
{
SqlTable table = new SqlTable(bkup.TableName);
int progressCount = 0;
SqlTransaction transaction = new SqlTransaction(dao);
int transCount = 0;
foreach (object[] row in bkup.Data) {
currentCount++;
progressCount++;
if (progressCount > 103) {
if (WFContext.BreakExecution) return;
WFContext.Description = "Inserting record " + currentCount + " of " + totalCount + " to \"" + table.TableName + "\"...";
WFContext.SetProgress(currentCount, totalCount);
progressCount = 0;
}
SqlQuery q = new SqlQuery(dao);
q.Insert(table);
int f = -1;
foreach (object value in row) {
switch ((DataValueType)bkup.FieldTypes[++f]) {
case DataValueType.BooleanType: q.Values(new SqlFieldBoolean(bkup.FieldNames[f], table), (bool)value); break;
case DataValueType.DateTimeType: q.Values(new SqlFieldDateTime(bkup.FieldNames[f], table), (DateTime)value); break;
case DataValueType.FloatType: q.Values(new SqlFieldFloat(bkup.FieldNames[f], table), (double)value); break;
case DataValueType.IntegerType: q.Values(new SqlFieldInteger(bkup.FieldNames[f], table), (int)value); break;
case DataValueType.ShortStringType: q.Values(new SqlFieldShortString(bkup.FieldNames[f], table), (string)value); break;
case DataValueType.LongStringType: q.Values(new SqlFieldLongString(bkup.FieldNames[f], table), (string)value); break;
default: break;
}
}
q.ExecuteNonQuery();
transCount++;
if (transCount > 1000) {
transaction.Commit();
transaction = new SqlTransaction(dao);
transCount = 0;
}
}
if (transCount > 0) {
transaction.Commit();
}
}
public static void Main()
{
//SQL Server 전용
string connString = @"Data Source=localhost\OJC;Initial Catalog=test;Integrated Security=true";
String sqlSelect = "SELECT * FROM emp";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
// start the transaction
SqlTransaction tran = conn.BeginTransaction();
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, conn);
DataSet ds = new DataSet("EMPLOYEES");
// load data from the data source into the DataSet
da.SelectCommand.Transaction = tran;
da.Fill(ds, "EMP");
Console.WriteLine("EMP TABLE 레코드 건수 : " + ds.Tables["EMP"].Rows.Count);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
// associate transaction with the data adapter command objects
da.DeleteCommand = cb.GetDeleteCommand();
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand.Transaction = tran;
da.InsertCommand.Transaction = tran;
da.UpdateCommand.Transaction = tran;
// modify
ds.Tables["EMP"].Rows[0]["ename"] = "0길동";
ds.Tables["EMP"].Rows[1]["ename"] = "1길동";
//delete
Console.WriteLine("삭제 대상 : " + ds.Tables["EMP"].Rows[9]["ename"]);
ds.Tables["EMP"].Rows[9].Delete();
//insert
DataRow r = ds.Tables["EMP"].NewRow();
r[0] = 1119; r[1] = "JCLEE";
ds.Tables["EMP"].Rows.Add(r);
try
{
ds.AcceptChanges();
Console.WriteLine(ds.GetXml());
da.Update(ds, "EMP");
// commit if successful
tran?.Commit();
Console.WriteLine("Commit OK~");
}
catch (Exception e)
{
Console.WriteLine(e);
tran?.Rollback();
}
finally
{
conn?.Close();
}
}
public bool GuardarGuiasRecepcionadasPorColectora()
{
using (SqlConnection conexion = new SqlConnection())
{
SqlTransaction transaccion = null;
conexion.ConnectionString = Config.ConnectionString;
try
{
conexion.Open();
transaccion = conexion.BeginTransaction();
try
{
if (this.ReciboRecepcionDetalle.Count > 0)
{
if (Guardar(transaccion))
{
if (GuardarNroReciboRecepcionUtilizado(transaccion))
{
foreach (IReciboRecepcionDetalle rdetalle in this.ReciboRecepcionDetalle)
{
try
{
Config.Conexion.EjecutarResultadoUnico(transaccion, "ReciboRecepcionGuiaINS", rdetalle.GuiaID, Utiles.BaseDatos.IntToSql(rdetalle.ObservacionID), this.ReciboRecepcionID);
}
catch (Exception ex)
{
transaccion.Rollback();
return(false);
}
}
}
else
{
transaccion.Rollback();
return(false);
}
}
else
{
transaccion.Rollback();
return(false);
}
}
else
{
return(true);
}
}
catch (Exception ex)
{
transaccion.Rollback();
return(false);
}
transaccion.Commit();
}
catch (Exception ex)
{
transaccion.Rollback();
throw ex;
}
return(true);
}
}
public void Commit()
{
transaction?.Commit();
transaction = null;
}
public void Commit()
{
_transaction?.Commit();
_connection?.Close();
}
public void Commit()
{
transakcija?.Commit();
}
private void ExecuteFiles()
{
bool retry = true;
int previousFailures = this.Files.Length;
ApplicationException lastException = null;
using (SqlConnection sqlConnection = this.CreateConnection(this.ConnectionString))
{
sqlConnection.Open();
while (retry)
{
int errorNo = 0;
ITaskItem[] failures = new ITaskItem[this.Files.Length];
var failedScripts = new List <ITaskItem>();
foreach (ITaskItem fileInfo in this.Files)
{
this.LogTaskMessage(MessageImportance.High, string.Format(CultureInfo.CurrentCulture, "Execute: {0}", fileInfo.ItemSpec));
try
{
this.LogTaskMessage(MessageImportance.Low, "Loading {0}.", new[] { fileInfo.ItemSpec });
string sqlCommandText = this.SubstituteParameters(this.LoadScript(fileInfo.ItemSpec)) + Environment.NewLine;
string[] batches = Splitter.Split(sqlCommandText);
this.LogTaskMessage(MessageImportance.Low, "Split {0} into {1} batches.", new object[] { fileInfo.ItemSpec, batches.Length });
SqlTransaction sqlTransaction = null;
SqlCommand command = sqlConnection.CreateCommand();
if (this.UseTransaction)
{
sqlTransaction = sqlConnection.BeginTransaction();
}
try
{
int batchNum = 1;
foreach (string batchText in batches)
{
sqlCommandText = batchText.Trim();
if (sqlCommandText.Length > 0)
{
command.CommandText = sqlCommandText;
command.CommandTimeout = this.CommandTimeout;
command.Connection = sqlConnection;
command.Transaction = sqlTransaction;
this.LogTaskMessage(MessageImportance.Low, "Executing Batch {0}", new object[] { batchNum++ });
this.LogTaskMessage(MessageImportance.Low, sqlCommandText);
command.ExecuteNonQuery();
}
}
sqlTransaction?.Commit();
}
catch
{
sqlTransaction?.Rollback();
throw;
}
this.OnScriptFileExecuted(new ExecuteEventArgs(new FileInfo(fileInfo.ItemSpec)));
}
catch (SqlException ex)
{
fileInfo.SetMetadata("ErrorMessage", ex.Message);
failedScripts.Add(fileInfo);
lastException = new ApplicationException(string.Format(CultureInfo.CurrentUICulture, "{0}. {1}", fileInfo.ItemSpec, ex.Message), ex);
if (!this.Retry && !this.IgnoreScriptErrors)
{
throw lastException;
}
failures[errorNo] = fileInfo;
errorNo++;
this.OnScriptFileExecuted(new ExecuteEventArgs(new FileInfo(fileInfo.ItemSpec), ex));
}
}
if (!this.Retry)
{
retry = false;
}
else
{
if (errorNo > 0)
{
this.Files = new ITaskItem[errorNo];
for (int i = 0; i < errorNo; i++)
{
this.Files[i] = failures[i];
}
if (this.Files.Length >= previousFailures && !this.IgnoreScriptErrors)
{
throw lastException;
}
previousFailures = this.Files.Length;
}
else
{
retry = false;
}
}
this.FailedScripts = failedScripts.ToArray();
}
}
}
public int ReportPopulation(DataSet data, Guid guid, int currentTick)
{
try
{
if (data == null || guid == Guid.Empty)
{
InstallerInfo.WriteEventLog("Report", "Suspect: " + Context.Request.ServerVariables["REMOTE_ADDR"].ToString());
}
// We need to check for blacklisted species, even if the client has been throttled. If this gets
// too demanding, think about caching the results
bool foundBlacklisted = false;
using (SqlConnection connection = new SqlConnection(ServerSettings.SpeciesDsn))
{
connection.Open();
SqlCommand command = new SqlCommand("TerrariumCheckSpeciesBlacklist", connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter nameParameter = command.Parameters.Add("@Name", SqlDbType.VarChar, 255);
DataTable table = data.Tables["History"];
if (table != null)
{
foreach (DataRow row in table.Rows)
{
nameParameter.Value = Convert.ToString(row["SpeciesName"]);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
if (1 == Convert.ToInt32(reader["Blacklisted"]))
{
foundBlacklisted = true;
break;
}
}
reader.Close();
}
}
if (foundBlacklisted == true)
{
return((int)ReturnCode.OrganismBlacklisted);
}
}
if (Throttle.Throttled(
Context.Request.ServerVariables["REMOTE_ADDR"].ToString(),
"ReportPopulation3Mins"))
{
return((int)ReturnCode.Success);
}
if (Throttle.Throttled(
Context.Request.ServerVariables["REMOTE_ADDR"].ToString(),
"ReportPopulation12Hour"))
{
return((int)ReturnCode.Success);
}
Throttle.AddThrottle(
Context.Request.ServerVariables["REMOTE_ADDR"].ToString(),
"ReportPopulation3Mins",
1,
DateTime.Now.AddMinutes(3)
);
if (_lastGuid.ContainsKey(Context.Request.ServerVariables["REMOTE_ADDR"].ToString()))
{
if (((Guid)_lastGuid[Context.Request.ServerVariables["REMOTE_ADDR"].ToString()]) != guid)
{
Throttle.AddThrottle(
Context.Request.ServerVariables["REMOTE_ADDR"].ToString(),
"ReportPopulation12Hour",
1,
DateTime.Now.AddHours(12)
);
_lastGuid[Context.Request.ServerVariables["REMOTE_ADDR"].ToString()] = guid;
return((int)ReturnCode.Success);
}
}
_lastGuid[Context.Request.ServerVariables["REMOTE_ADDR"].ToString()] = guid;
DateTime contactTime = DateTime.UtcNow;
DataTable historyTable = data.Tables["History"];
foreach (DataRow row in historyTable.Rows)
{
// Set correcttime to false on all rows that aren't the current tick because they are data that
// is old, but couldn't get to the server when it was fresh. Only the data from the current tick
// actually happened right now
if ((int)row["TickNumber"] != currentTick)
{
row["CorrectTime"] = 0;
}
row["ContactTime"] = contactTime;
}
bool blackListedPeers = false;
bool validRecord = true;
int totalPopulation = 0;
using (SqlConnection myConnection = new SqlConnection(ServerSettings.SpeciesDsn))
{
myConnection.Open();
// Declare all parameters, commands, etc.. right here
SqlTransaction transaction = myConnection.BeginTransaction();
SqlCommand lastContact;
SqlParameter parmGuid;
SqlParameter parmLastContact;
SqlParameter parmLastTick;
SqlParameter parmReturnVal;
lastContact = new SqlCommand("TerrariumTimeoutReport", myConnection, transaction);
lastContact.CommandType = CommandType.StoredProcedure;
parmGuid = lastContact.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier, 16);
parmGuid.Value = guid;
parmLastContact = lastContact.Parameters.Add("@LastContact", SqlDbType.DateTime, 8);
parmLastContact.Value = contactTime;
parmLastTick = lastContact.Parameters.Add("@LastTick", SqlDbType.Int, 4);
parmLastTick.Value = currentTick;
parmReturnVal = lastContact.Parameters.Add("@ReturnCode", SqlDbType.Int, 4);
parmReturnVal.Direction = ParameterDirection.Output;
try
{
lastContact.ExecuteNonQuery();
if (((int)parmReturnVal.Value) != 0)
{
if ((int)parmReturnVal.Value == 1)
{
return((int)ReturnCode.NodeTimedOut);
}
else if ((int)parmReturnVal.Value == 2)
{
return((int)ReturnCode.NodeCorrupted);
}
else
{
InstallerInfo.WriteEventLog("Report", "Unknown return value from TerrariumTimeoutReport");
if (reportingAllFailedPerformanceCounter != null)
{
reportingAllFailedPerformanceCounter.Increment();
}
return((int)ReturnCode.ServerDown);
}
}
}
catch (Exception e)
{
if (e is SqlException)
{
InstallerInfo.WriteEventLog("Report", "Suspect: " + Context.Request.ServerVariables["REMOTE_ADDR"].ToString() + "\n\r" +
"Sql Error Number: " + ((SqlException)e).Number + "\r\n" + e.ToString());
}
else
{
InstallerInfo.WriteEventLog("Report", "Suspect: " + Context.Request.ServerVariables["REMOTE_ADDR"].ToString() + "\n\r" + e.ToString());
InstallerInfo.WriteEventLog("Report", "Unknown return value from TerrariumTimeoutReport");
}
if (reportingAllFailedPerformanceCounter != null)
{
reportingAllFailedPerformanceCounter.Increment();
}
}
// Update the history data
SqlCommand insertHistory = new SqlCommand("TerrariumInsertHistory", myConnection, transaction);
insertHistory.CommandType = CommandType.StoredProcedure;
parmGuid = insertHistory.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier, 16);
SqlParameter parmSpeciesName = insertHistory.Parameters.Add("@SpeciesName", SqlDbType.VarChar, 255);
SqlParameter parmContactTime = insertHistory.Parameters.Add("@ContactTime", SqlDbType.DateTime, 8);
SqlParameter parmClientTime = insertHistory.Parameters.Add("@ClientTime", SqlDbType.DateTime, 8);
SqlParameter parmCorrectTime = insertHistory.Parameters.Add("@CorrectTime", SqlDbType.TinyInt, 1);
SqlParameter parmTickNumber = insertHistory.Parameters.Add("@TickNumber", SqlDbType.Int, 4);
SqlParameter parmPopulation = insertHistory.Parameters.Add("@Population", SqlDbType.Int, 4);
SqlParameter parmBirthCount = insertHistory.Parameters.Add("@BirthCount", SqlDbType.Int, 4);
SqlParameter parmTeleportedToCount = insertHistory.Parameters.Add("@TeleportedToCount", SqlDbType.Int, 4);
SqlParameter parmStarvedCount = insertHistory.Parameters.Add("@StarvedCount", SqlDbType.Int, 4);
SqlParameter parmKilledCount = insertHistory.Parameters.Add("@KilledCount", SqlDbType.Int, 4);
SqlParameter parmTeleportedFromCount = insertHistory.Parameters.Add("@TeleportedFromCount", SqlDbType.Int, 4);
SqlParameter parmErrorCount = insertHistory.Parameters.Add("@ErrorCount", SqlDbType.Int, 4);
SqlParameter parmTimeoutCount = insertHistory.Parameters.Add("@TimeoutCount", SqlDbType.Int, 4);
SqlParameter parmSickCount = insertHistory.Parameters.Add("@SickCount", SqlDbType.Int, 4);
SqlParameter parmOldAgeCount = insertHistory.Parameters.Add("@OldAgeCount", SqlDbType.Int, 4);
SqlParameter parmSecurityViolationCount = insertHistory.Parameters.Add("@SecurityViolationCount", SqlDbType.Int, 4);
SqlParameter parmBlackListed = insertHistory.Parameters.Add("@BlackListed", SqlDbType.Int, 4);
parmBlackListed.Direction = ParameterDirection.Output;
if (data.Tables["History"].Rows.Count > 600)
{
validRecord = false;
}
else
{
foreach (DataRow dr in data.Tables["History"].Rows)
{
if (((int)dr["TickNumber"]) != currentTick)
{
continue;
}
totalPopulation += ((int)dr["Population"]);
if (totalPopulation > 600 || ((int)dr["Population"]) > 340 || ((int)dr["Population"]) < 0)
{
validRecord = false;
}
parmGuid.Value = dr["Guid"];
parmSpeciesName.Value = dr["SpeciesName"];
parmContactTime.Value = dr["ContactTime"];
parmClientTime.Value = dr["ClientTime"];
parmCorrectTime.Value = dr["CorrectTime"];
parmTickNumber.Value = dr["TickNumber"];
parmPopulation.Value = dr["Population"];
parmBirthCount.Value = dr["BirthCount"];
parmTeleportedToCount.Value = dr["TeleportedToCount"];
parmStarvedCount.Value = dr["StarvedCount"];
parmKilledCount.Value = dr["KilledCount"];
parmTeleportedFromCount.Value = dr["TeleportedFromCount"];
parmErrorCount.Value = dr["ErrorCount"];
parmTimeoutCount.Value = dr["TimeoutCount"];
parmSickCount.Value = dr["SickCount"];
parmOldAgeCount.Value = dr["OldAgeCount"];
parmSecurityViolationCount.Value = dr["SecurityViolationCount"];
insertHistory.ExecuteNonQuery();
if (parmBlackListed.Value != null && parmBlackListed.Value != System.DBNull.Value && ((int)parmBlackListed.Value) == 1)
{
blackListedPeers = true;
}
}
}
if (validRecord)
{
transaction.Commit();
}
else
{
transaction.Rollback();
}
}
if (reportingAllPerformanceCounter != null)
{
reportingAllPerformanceCounter.Increment();
}
if (blackListedPeers)
{
return((int)ReturnCode.OrganismBlacklisted);
}
return((int)ReturnCode.Success);
}
catch (Exception e)
{
if (e is SqlException)
{
InstallerInfo.WriteEventLog("Report", "Suspect: " + Context.Request.ServerVariables["REMOTE_ADDR"].ToString() + "\n\r" +
"Sql Error Number: " + ((SqlException)e).Number + "\r\n" + e.ToString());
}
else
{
InstallerInfo.WriteEventLog("Report", "Suspect: " + Context.Request.ServerVariables["REMOTE_ADDR"].ToString() + "\n\r" +
e.ToString());
}
if (reportingAllFailedPerformanceCounter != null)
{
reportingAllFailedPerformanceCounter.Increment();
}
// Return success instead of ServerDown because, if the server is getting hammered, Success will tell clients to
// stop retrying, where ServerDown will tell them to keep doing it.
return((int)ReturnCode.Success);
}
}
private void ExecuteText()
{
using (SqlConnection sqlConnection = this.CreateConnection(this.ConnectionString))
using (SqlCommand command = new SqlCommand(this.SubstituteParameters(this.Sql), sqlConnection))
{
command.CommandTimeout = this.CommandTimeout;
this.LogTaskMessage(MessageImportance.High, string.Format(CultureInfo.CurrentCulture, "Execute: {0}", command.CommandText));
sqlConnection.Open();
SqlTransaction sqlTransaction = null;
try
{
if (this.UseTransaction)
{
sqlTransaction = sqlConnection.BeginTransaction();
command.Transaction = sqlTransaction;
}
switch (this.TaskAction)
{
case ExecuteTaskAction:
command.ExecuteNonQuery();
break;
case ExecuteScalarTaskAction:
var result = command.ExecuteScalar();
this.ScalarResult = result.ToString();
break;
case ExecuteReaderTaskAction:
ArrayList rows = new ArrayList();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ITaskItem rowItem = new TaskItem(reader[0].ToString());
for (int i = 0; i < reader.FieldCount; i++)
{
rowItem.SetMetadata(reader.GetName(i), reader[i].ToString());
}
rows.Add(rowItem);
}
}
this.ReaderResult = new ITaskItem[rows.Count];
for (int i = 0; i < rows.Count; i++)
{
this.ReaderResult[i] = (ITaskItem)rows[i];
}
break;
case ExecuteRawReaderTaskAction:
using (SqlDataReader rawreader = command.ExecuteReader())
{
this.RawReaderResult = string.Empty;
while (rawreader.Read())
{
string resultRow = string.Empty;
for (int i = 0; i < rawreader.FieldCount; i++)
{
resultRow += rawreader[i] + " ";
}
this.RawReaderResult += resultRow + Environment.NewLine;
}
}
break;
}
sqlTransaction?.Commit();
TimeSpan s = DateTime.Now - this.timer;
this.LogTaskMessage(MessageImportance.Low, string.Format(CultureInfo.CurrentCulture, "Execution Time: {0} seconds", s.TotalSeconds));
this.timer = DateTime.Now;
}
catch
{
sqlTransaction?.Rollback();
throw;
}
}
}
public void Commit()
{
_currentTransaction?.Commit();
}
protected void Button1_Click(object sender, EventArgs e)
{
//-============= CATATAN PENTING SEKALI YA :-) ================= --
// Kalau dalam stored procedure menggunakan transaction maka
// Coding juga sama
//-====================================================== --
// ---- Validation -----
if (this.TbBiayaOnline.Text == "" || this.TbBiayaOnline.Text == "0")
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Biaya Tidak Boleh Kosong');", true);
return;
}
if (this.DLCicilanOl.SelectedValue != "1" && this.DLCicilanOl.SelectedValue != "2")
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Pilih Cicilan');", true);
return;
}
string CS = ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
con.Open();
SqlTransaction trans = con.BeginTransaction();
try
{
SqlCommand cmdUpdate = new SqlCommand("SpUpdateUnpaid", con);
cmdUpdate.Transaction = trans;
cmdUpdate.CommandType = System.Data.CommandType.StoredProcedure;
cmdUpdate.Parameters.AddWithValue("@billingNo", this.LbBillOnline.Text);
cmdUpdate.Parameters.AddWithValue("@NewAmount", Convert.ToInt32(TbBiayaOnline.Text));
cmdUpdate.Parameters.AddWithValue("@cicilan", this.DLCicilanOl.SelectedValue);
cmdUpdate.ExecuteNonQuery();
trans.Commit();
trans.Dispose();
cmdUpdate.Dispose();
con.Close();
// Klick button
BtnCariOffline_Click(this, null);
// clear Billing number
this.LbNoBill.Text = "";
this.TBNoBill.Text = "";
this.DLCicilanOl.SelectedIndex = 0;
//hide update panel
this.PanelUpdateOnline.Enabled = false;
this.PanelUpdateOnline.Visible = false;
//BtnCariOffline_Click(this, null);
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Update Success');", true);
}
catch (Exception)
{
PanelUpdateOnline.Enabled = false;
PanelUpdateOnline.Visible = false;
trans.Rollback();
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Update Gagal');", true);
}
}
}
public bool InsertWorkingSheets(ref string ErrorMsg, ref object WorkingSheetID, ref object WorkingSheetCode, object DateNote, object PackingPlanID, object PrdID, object PackingTypeID, object CompanyID, object Quantity, object DateTarget, object Memo, object MakerPsnID)
{
bool flag = false;
ErrorMsg = string.Empty;
SqlParameter[] arParams = new SqlParameter[11];
arParams[0] = new SqlParameter("@WorkingSheetID", SqlDbType.BigInt);
arParams[0].Direction = ParameterDirection.InputOutput;
arParams[1] = new SqlParameter("@WorkingSheetCode", SqlDbType.VarChar);
arParams[1].Size = 50;
arParams[1].Direction = ParameterDirection.InputOutput;
arParams[2] = new SqlParameter("@DateNote", SqlDbType.DateTime);
arParams[3] = new SqlParameter("@PackingPlanID", SqlDbType.BigInt);
arParams[4] = new SqlParameter("@PrdID", SqlDbType.Int);
arParams[5] = new SqlParameter("@PackingTypeID", SqlDbType.Int);
arParams[6] = new SqlParameter("@CompanyID", SqlDbType.Int);
arParams[7] = new SqlParameter("@Quantity", SqlDbType.Decimal);
arParams[7].Precision = 18;
arParams[7].Scale = 4;
arParams[8] = new SqlParameter("@DateTarget", SqlDbType.DateTime);
arParams[9] = new SqlParameter("@Memo", SqlDbType.VarChar);
arParams[9].Size = 200;
arParams[10] = new SqlParameter("@MakerPsnID", SqlDbType.Int);
arParams[0].Value = WorkingSheetID;
arParams[1].Value = WorkingSheetCode;
arParams[2].Value = DateNote;
arParams[3].Value = PackingPlanID;
arParams[4].Value = PrdID;
arParams[5].Value = PackingTypeID;
arParams[6].Value = CompanyID;
arParams[7].Value = Quantity;
arParams[8].Value = DateTarget;
arParams[9].Value = Memo;
arParams[10].Value = MakerPsnID;
SqlTransaction DBTransaction = null;
try
{
if (this.sqlConn.State == System.Data.ConnectionState.Closed)
{
this.sqlConn.Open();
}
DBTransaction = this.sqlConn.BeginTransaction();
SqlHelper.ExecuteNonQuery(DBTransaction, CommandType.StoredProcedure, "packing.InsertWorkingSheets", arParams);
WorkingSheetID = arParams[0].Value;
WorkingSheetCode = arParams[1].Value;
DBTransaction.Commit();
flag = true;
}
catch (SqlException ex)
{
ErrorMsg = ex.Message; //返回错误信息
flag = false;
DBTransaction.Rollback(); //--回退事务
}
finally
{
this.sqlConn.Close();
}
return(flag);
}
public void Complete()
{
_transaction?.Commit();
}
/// <summary>
/// //on row command of the items, mainly now 1. delete specfic row of slected item id in the secondary table
/// 2. update sum total amount in primary table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void gvItems_Edit_RowCommand(object sender, GridViewCommandEventArgs e)
{
//if (e.CommandName == "Delete")
//instead of using command name =delete, using amount value
if (e.CommandName != "")
{
//Label lblAmount = (Label) gvItems_Edit.FindControl(e.
double amount = Convert.ToDouble(e.CommandName);
string[] MyArraryOfCommand = e.CommandArgument.ToString().Split(new char[] { ',' });
string receivedItemID = MyArraryOfCommand[0];
string ItemID = MyArraryOfCommand[1];
SqlTransaction tr = null;
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
//this will execute first
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM [ReceivedItemsDetails] WHERE [ReceivedItemID] = @ReceivedItemID";
cmd.Parameters.AddWithValue("@ReceivedItemID", receivedItemID);
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = "update ReceivedItemsOTEO set totalamount = totalamount-" + amount + " where receiveditemsoteoid =@receiveditemsoteoid";
//cmd2.Parameters.AddWithValue("@amount",);
cmd2.Parameters.AddWithValue("@receiveditemsoteoid", OTEOID);
SqlCommand cmd3 = conn.CreateCommand();
cmd3.CommandText = "SELECT COUNT (*) FROM ReceivedItemsDetails INNER JOIN ReceivedItemsOTEO ON ReceivedItemsDetails.ReceivedItemsOTEOID = ReceivedItemsOTEO.ReceivedItemsOTEOID WHERE (ReceivedItemsDetails.ReceivedItemsOTEOID = @ReceivedItemsOTEOID)";
cmd3.Parameters.AddWithValue("@ReceivedItemsOTEOID", OTEOID);
//SqlCommand cmd4 = conn.CreateCommand();
//cmd4.CommandText = "DELETE FROM [Itemsratesecondary] WHERE [ItemID] = @ItemID and OTEO=@receiveditemsoteoid";
//cmd4.Parameters.AddWithValue("@ItemID", ItemID);
// cmd4.Parameters.AddWithValue("@receiveditemsoteoid", OTEOID);
try
{
using (conn)
{
conn.Open();
tr = conn.BeginTransaction();
cmd.Transaction = tr;
cmd2.Transaction = tr;
// cmd4.Transaction = tr;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
// cmd4.ExecuteNonQuery();
tr.Commit();
int count = Convert.ToInt32(cmd3.ExecuteScalar());
if (count < 1)
{
_btnDelete.Visible = true;
}
}
}
catch
{
tr.Rollback();
throw;
}
finally
{
conn.Close();
}
GetRecievedItemsDetails(OTEOID);
gvItems_Edit.DataBind();
}
}
public void Commit()
{
Transaction?.Commit();
}
public void SaveChanges()
{
_transaction?.Commit();
}
