public int Excute()
{
IDbConnection conn = DataBaseManage.GetdbConnection();
int i_Return = 0;
try
{
conn.Open();
trans = conn.BeginTransaction();
Tran();
trans.Commit();
return i_Return;
}
catch (Exception ex)
{
if (trans != null) trans.Rollback();
ex.ToString();
return i_Return;
}
finally
{
if (trans != null) trans.Dispose();
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
public System.Data.IDbTransaction BeginTransaction()
{
this.needTransaction = true;
if (this.Connection.State != System.Data.ConnectionState.Open)
this.Connection.Open();
this.tran = this.Connection.BeginTransaction();
return this.tran;
}
public int LoginOutByToken(string token, System.Data.IDbTransaction dbTransaction = null)
{
var sql = $@"update [dbo].[SysLoginLog] set [LogOutDate] = getdate() where Token='{token}'";
return(TraceExecFunc(() =>
{
return Conn.Execute(sql, transaction: dbTransaction);
}, sql));
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------
public void BeginTransaction()
{
ValidateConnection();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Transaction = dbTransaction;
// return;
}
public BlockQuery(System.Data.IDbConnection con, System.Data.IDbTransaction transaction)
{
m_command = con.CreateCommand();
m_command.Transaction = transaction;
m_command.Parameters.Clear();
m_command.CommandText = @"SELECT ""VolumeID"" FROM ""Block"" WHERE ""Hash"" = ? AND ""Size"" = ? ";
m_command.AddParameters(2);
}
} // End Function ExecuteFromResource
public static int ExecuteFromResource(this System.Data.IDbConnection cnn, string resourceName
, System.Type type, object param = null
, System.Data.IDbTransaction transaction = null, int?commandTimeout = null
, System.Data.CommandType?commandType = null)
{
string sql = GetEmbeddedResource(type.Assembly, resourceName);
return(Execute(cnn, sql, param, transaction, commandTimeout, commandType));
} // End Function ExecuteFromResource
public bool UseBlock(string hash, long size, System.Data.IDbTransaction transaction)
{
m_command.Transaction = transaction;
m_command.SetParameterValue(0, hash);
m_command.SetParameterValue(1, size);
var r = m_command.ExecuteScalar();
return(r != null && r != DBNull.Value);
}
/// <summary>
/// Saves and publishes the current record and physical file.
/// </summary>
/// <param name="content">The physical file</param>
/// <param name="tx">Optional transaction</param>
/// <returns>Whether the operation succeeded or not</returns>
public virtual bool SaveAndPublish(MediaFileContent content, System.Data.IDbTransaction tx = null)
{
//var user = HttpContext.Current != null ? HttpContext.Current.User : null ;
if (Database.Identity != Guid.Empty || Application.Current.UserProvider.IsAuthenticated)
{
// Set file meta information
SetFileMeta(content);
// First get previously published record
var self = Content.GetSingle(Id, false, tx);
// Set up the dates.
LastPublished = Updated = DateTime.Now;
if (IsNew)
{
Created = Updated;
}
if (self == null)
{
Published = Updated;
}
// First save an up-to-date draft
IsDraft = true;
base.Save(content, tx, false, false);
//var draftpath = PhysicalPath;
// Now save a published version
IsDraft = false;
if (self == null)
{
IsNew = true;
}
base.Save(content, tx, false);
// Check if we have have a drafted physical file
Application.Current.MediaProvider.Publish(Id);
DeleteCache();
// Now update all pages & posts which have a reference
// to this media object.
Page.Execute("UPDATE page SET page_last_modified = @0 WHERE page_attachments LIKE @1", tx,
DateTime.Now, "%" + Id.ToString() + "%");
Post.Execute("UPDATE post SET post_last_modified = @0 WHERE post_attachments LIKE @1", tx,
DateTime.Now, "%" + Id.ToString() + "%");
// Invalidate all pages & posts which have a reference
// to this media object.
Page.Get("page_attachments LIKE @1", tx, "%" + Id.ToString() + "%").ForEach(p => p.InvalidateRecord(p));
Post.Get("post_attachments LIKE @1", tx, "%" + Id.ToString() + "%").ForEach(p => p.InvalidateRecord(p));
return(true);
}
throw new AccessViolationException("User must be logged in to save data.");
}
protected void BeginTransaction()
{
ValidateConnection();
m_oTransaction = m_oConnection.BeginTransaction();
m_oCommand.Transaction = m_oTransaction;
return;
}
private void pmUpdateRecord()
{
string strErrorMsg = "";
bool bllIsNewRow = false;
bool bllIsCommit = false;
WS.Data.Agents.cDBMSAgent objSQLHelper = new WS.Data.Agents.cDBMSAgent(App.ConnectionString, App.DatabaseReside);
this.mSaveDBAgent = new WS.Data.Agents.cDBMSAgent(App.ConnectionString, App.DatabaseReside);
this.mSaveDBAgent.AppID = App.AppID;
this.mdbConn = this.mSaveDBAgent.GetDBConnection();
try
{
this.mdbConn.Open();
this.mdbTran = this.mdbConn.BeginTransaction(IsolationLevel.ReadUncommitted);
this.pmUpdateAuthDet();
this.mdbTran.Commit();
bllIsCommit = true;
if (this.mFormEditMode == UIHelper.AppFormState.Insert)
{
KeepLogAgent.KeepLog(objSQLHelper, KeepLogType.Insert, TASKNAME, this.txtQcAppLogin.Text, "", App.FMAppUserID, App.AppUserName);
}
else if (this.mFormEditMode == UIHelper.AppFormState.Edit)
{
if (this.mstrOldCode == this.txtQcAppLogin.Text)
{
KeepLogAgent.KeepLog(objSQLHelper, KeepLogType.Update, TASKNAME, this.txtQcAppLogin.Text, "", App.FMAppUserID, App.AppUserName);
}
else
{
KeepLogAgent.KeepLogChgValue(objSQLHelper, KeepLogType.Update, TASKNAME, this.txtQcAppLogin.Text, "", App.FMAppUserID, App.AppUserName, this.mstrOldCode, this.mstrOldName);
}
}
}
catch (Exception ex)
{
if (!bllIsCommit)
{
this.mdbTran.Rollback();
}
App.WriteEventsLog(ex);
#if xd_RUNMODE_DEBUG
MessageBox.Show("Message : " + ex.Message + "\n" + ex.Source + "\n" + ex.StackTrace);
#endif
}
finally
{
this.mdbConn.Close();
}
}
public object SyncProfitCenter(dynamic Data)
{
DateTime TranDate = DateTime.Now;
DBHelper DBHelper = new DBHelper(ICON.Configuration.Database.REM_ConnectionString, null);
SAP_Interface_Log Log = ICON.Interface.Transaction.CreateSAPLog("AR_REM", "SyncProfitCenter", TranDate.ToString("yyyyMMdd_HHmmssfff"), "DATETIME", null, null, null, TranBy);
int ResponseCode = 500;
object ResponseData = null;
string ErrrorMessage = string.Empty;
try
{
string SQL = @"SELECT CompanyID,ISNULL(Value,'') DBName FROM Sys_Conf_RealEstate WHERE 1=1 AND CompanyID IS NOT NULL AND CompanyID <> '0' AND ISNULL(KEYNAME,'') = 'DBName' AND ISNULL(Value,'') <> ''";
DataTable DT_Company = DBHelper.ExecuteDataTable(SQL);
System.Data.IDbTransaction Tran = DBHelper.BeginTransaction();
try
{
string sql = "";
foreach (DataRow DR in DT_Company.Rows)
{
sql += $@"
UPDATE PJ SET PJ.ProfitCenter = '1' + PRC.PrcCode
FROM
SC_Project PJ
INNER JOIN Sys_Master_Projects MPJ ON MPJ.ProjectID = PJ.ProjectID
LEFT JOIN [{ DR["DBName"].ToString() }]..OPRC PRC ON CONVERT(NVARCHAR(50),PJ.ProjectID) COLLATE Thai_CI_AS = CONVERT(NVARCHAR(50),PRC.PrcName) COLLATE Thai_CI_AS AND Prc.DimCode = 1
WHERE 1=1 AND MPJ.CompanyID = '{ DR["CompanyID"].ToString() }'
";
}
DBHelper.ExecuteNonQuery(sql, Tran);
DBHelper.CommitTransaction(Tran);
}
catch (Exception ex)
{
DBHelper.RollbackTransaction(Tran);
throw ex;
}
ResponseCode = 200;
ResponseData = new { status = true, message = "success", data = new { TranID = Log.TranID } };
}
catch (Exception ex)
{
ErrrorMessage = ex.Message;
ResponseData = new { status = false, message = ex.Message, data = new { TranID = Log.TranID } };
}
finally
{
ICON.Interface.Transaction.UpdateSAPLog(Log.TranID, Newtonsoft.Json.JsonConvert.SerializeObject(ResponseData), ResponseCode, ErrrorMessage);
}
return(ResponseData);
}
private bool pmDeleteRow(string inRowID, string inCode, string inName, ref string ioErrorMsg)
{
bool bllIsCommit = false;
bool bllResult = false;
this.mSaveDBAgent = new WS.Data.Agents.cDBMSAgent(App.ConnectionString, App.DatabaseReside);
this.mSaveDBAgent.AppID = App.AppID;
this.mdbConn = this.mSaveDBAgent.GetDBConnection();
try
{
this.mdbConn.Open();
this.mdbTran = this.mdbConn.BeginTransaction(IsolationLevel.ReadUncommitted);
string strErrorMsg = "";
object[] pAPara = new object[1] {
inRowID
};
this.mSaveDBAgent.BatchSQLExec("delete from " + this.mstrITable + " where cWkCtrH = ?", pAPara, ref strErrorMsg, this.mdbConn, this.mdbTran);
//Delete CostLine
pAPara = new object[] { App.ActiveCorp.RowID, this.mstrRefTable, this.mstrEditRowID };
this.mSaveDBAgent.BatchSQLExec("delete from " + this.mstrCostLineTable + " where cCorp = ? and cRefTab = ? and cMasterH = ? ", pAPara, ref strErrorMsg, this.mdbConn, this.mdbTran);
pAPara = new object[1] {
inRowID
};
this.mSaveDBAgent.BatchSQLExec("delete from " + this.mstrRefTable + " where cRowID = ?", pAPara, ref strErrorMsg, this.mdbConn, this.mdbTran);
this.mdbTran.Commit();
bllIsCommit = true;
WS.Data.Agents.cDBMSAgent objSQLHelper = new WS.Data.Agents.cDBMSAgent(App.ConnectionString, App.DatabaseReside);
KeepLogAgent.KeepLog(objSQLHelper, KeepLogType.Delete, TASKNAME, inCode, inName, App.FMAppUserID, App.AppUserName);
bllResult = true;
}
catch (Exception ex)
{
ioErrorMsg = ex.Message;
bllResult = false;
if (!bllIsCommit)
{
this.mdbTran.Rollback();
}
App.WriteEventsLog(ex);
}
finally
{
this.mdbConn.Close();
}
return(bllResult);
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------
public void CommitTransaction()
{
if (dbTransaction == null)
{
throw (new Exception("BeginTransaction must be called before commit or rollback. No open transactions found"));
}
dbTransaction.Commit();
dbTransaction.Dispose();
dbTransaction = null;
}
protected void CommitTransaction()
{
if (m_oTransaction == null)
{
throw (new Exception("BeginTransaction must be called before commit or rollback. No open transactions found"));
}
m_oTransaction.Commit();
m_oTransaction.Dispose();
m_oTransaction = null;
}
/// <summary>
/// Close a specific connection if state is not already closed
/// </summary>
/// <param name="transaction"></param>
public override void CloseTransaction(System.Data.IDbTransaction transaction)
{
#region Input Validation
if (transaction == null)
{
throw new ArgumentNullException("Transaction cannot be null.", "transaction");
}
#endregion
CloseConnection(transaction.Connection);
}
public object SyncBankAccount(dynamic Data)
{
DateTime TranDate = DateTime.Now;
DBHelper DBHelper = new DBHelper(ICON.Configuration.Database.REM_ConnectionString, null);
SAP_Interface_Log Log = ICON.Interface.Transaction.CreateSAPLog("AR_REM", "SyncBankAccount", TranDate.ToString("yyyyMMdd_HHmmssfff"), "DATETIME", null, null, null, TranBy);
int ResponseCode = 500;
object ResponseData = null;
string ErrrorMessage = string.Empty;
try
{
string SQL = @"SELECT CompanyID,ISNULL(Value,'') DBName FROM Sys_Conf_RealEstate WHERE 1=1 AND CompanyID IS NOT NULL AND CompanyID <> '0' AND ISNULL(KEYNAME,'') = 'DBName' AND ISNULL(Value,'') <> ''";
DataTable DT_Company = DBHelper.ExecuteDataTable(SQL);
System.Data.IDbTransaction Tran = DBHelper.BeginTransaction();
try
{
string sql = "";
foreach (DataRow DR in DT_Company.Rows)
{
sql += $@"
UPDATE BA SET BA.GLAccountCode = SAPBA.GLAccount
FROM Sys_Master_BankAccount BA
INNER JOIN [{ DR["DBName"].ToString() }]..DSC1 SAPBA ON CONVERT(NUMERIC(18, 0), REPLACE(REPLACE(BA.BankAccount, '-', ''), ' ', '')) = CONVERT(NUMERIC(18, 0), REPLACE(REPLACE(SAPBA.Account, '-', ''), ' ', ''))
WHERE 1 = 1
";
}
DBHelper.ExecuteNonQuery(sql, Tran);
DBHelper.CommitTransaction(Tran);
}
catch (Exception ex)
{
DBHelper.RollbackTransaction(Tran);
throw ex;
}
ResponseCode = 200;
ResponseData = new { status = true, message = "success", data = new { TranID = Log.TranID } };
}
catch (Exception ex)
{
ErrrorMessage = ex.Message;
ResponseData = new { status = false, message = ex.Message, data = new { TranID = Log.TranID } };
}
finally
{
ICON.Interface.Transaction.UpdateSAPLog(Log.TranID, Newtonsoft.Json.JsonConvert.SerializeObject(ResponseData), ResponseCode, ErrrorMessage);
}
return(ResponseData);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(Dictionary <string, IDataParameter[]> pList, string strConnect, string dataType)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
//iConn.Open();
using (System.Data.IDbCommand iCmd = GetCommand())
{
iCmd.Connection = iConn;
iConn.Open();
using (System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())
{
iCmd.Transaction = iDbTran;
try
{
foreach (var item in pList)
{
iCmd.CommandText = item.Key;
if (item.Value != null)
{
for (int i = 0; i < item.Value.Length; i++)
{
iCmd.Parameters.Add(item.Value[i]);
}
}
try
{
var cnt = iCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw;
}
iCmd.Parameters.Clear();
}
iDbTran.Commit();
}
catch (System.Exception E)
{
iDbTran.Rollback();
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
}
public int ExecuteNonQuery(string commandText,System.Data.OracleClient.OracleParameter[] parameters)
{
int affectRows;
affectRows = -1;
if (this.Connection.State != System.Data.ConnectionState.Open)
this.Connection.Open();
OracleCommand Command = new OracleCommand();
if (!this.needTransaction)
tran = null;
Command.CommandType = CommandType.StoredProcedure;
PrepareCommand(Command, this.Connection, tran, CommandType.StoredProcedure, commandText);
for (int i = 0; i < parameters.Length; i++)
{
Command.Parameters.Add(parameters[i].ParameterName,parameters[i].OracleType,parameters[i].Size);
Command.Parameters[i].Direction = parameters[i].Direction;
Command.Parameters[i].Value = parameters[i].Value;
}
try
{
affectRows = Command.ExecuteNonQuery();
for (int i = 0; i < parameters.Length; i++)
{
if (parameters[i].Direction == ParameterDirection.Output || Command.Parameters[i].Direction == ParameterDirection.InputOutput)
{
parameters[i].Value = Command.Parameters[i].Value;
}
}
}
catch (Exception ex)
{
affectRows = -1;
needTransaction = false;
Errormsg_ = ex.Message;
throw ex;
return affectRows;
}
//finally
//{
// if (mustCloseConnection)
// {
// if (!needTransaction)
// {
// this.Connection.Close();
// }
// }
//}
return affectRows;
}
/// <summary>
/// Unpublishes the content with the given id.
/// </summary>
/// <param name="id">The content id</param>
/// <param name="tx">Optional transaction</param>
public static void Unpublish(Guid id, System.Data.IDbTransaction tx = null)
{
using (IDbTransaction dbTx = (tx == null ? Database.OpenTransaction() : null)) {
// Delete the published version
Content.Execute("DELETE FROM content WHERE content_draft = 0 AND content_id = @0",
tx != null ? tx : dbTx, id);
// Remove published dates
Content.Execute("UPDATE content SET content_published = NULL, content_last_published = NULL WHERE content_id = @0",
tx != null ? tx : dbTx, id);
// Now update all pages & posts which have a reference
// to this media object.
Page.Execute("UPDATE page SET page_last_modified = @0 WHERE page_attachments LIKE @1",
tx != null ? tx : dbTx, DateTime.Now, "%" + id.ToString() + "%");
Post.Execute("UPDATE post SET post_last_modified = @0 WHERE post_attachments LIKE @1",
tx != null ? tx : dbTx, DateTime.Now, "%" + id.ToString() + "%");
// Commit the transaction if it's local
if (dbTx != null)
{
dbTx.Commit();
}
// Take the published physical file and move it to draft mode.
var content = Content.GetSingle(id, true, tx);
if (content != null)
{
var draftpath = content.PhysicalPath;
// Delete the draft file
if (File.Exists(draftpath))
{
File.Delete(draftpath);
}
// Move published file to draft state
content.IsDraft = false;
if (File.Exists(content.PhysicalPath))
{
File.Move(content.PhysicalPath, draftpath);
}
content.DeleteCache();
// Invalidate record
content.InvalidateRecord(content);
}
// Invalidate all pages & posts which have a reference
// to this media object.
Page.Get("page_attachments LIKE @1", tx, "%" + id.ToString() + "%").ForEach(p => p.InvalidateRecord(p));
Post.Get("post_attachments LIKE @1", tx, "%" + id.ToString() + "%").ForEach(p => p.InvalidateRecord(p));
}
}
/// <summary>
/// Get new database connection from pool.
/// Begins a database transaction.
/// Developer can access the transaction using CurrentTransaction object
/// </summary>
public void BeginTransaction(IsolationLevel isoLevel)
{
try
{
this.dbConnection = this.DbDatabase.GetConnection();
this.dbConnection.Open();
this.dbTrx = this.dbConnection.BeginTransaction(isoLevel);
}
catch
{
throw;
}
}
public void MoveBlockToNewVolume(string hash, long size, long volumeID, System.Data.IDbTransaction tr)
{
m_moveBlockToNewVolumeCommand.SetParameterValue(0, volumeID);
m_moveBlockToNewVolumeCommand.SetParameterValue(1, hash);
m_moveBlockToNewVolumeCommand.SetParameterValue(2, size);
m_moveBlockToNewVolumeCommand.Transaction = tr;
var c = m_moveBlockToNewVolumeCommand.ExecuteNonQuery();
if (c != 1)
{
throw new Exception("Unexpected update result");
}
}
public int ExecuteNonQuery(System.Data.IDbConnection connection, System.Data.CommandType commandType, string commandText)
{
int affectRows;
affectRows = -1;
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
OracleCommand Command = new OracleCommand();
if (!this.needTransaction)
{
tran = null;
}
if (commandText.ToLower().IndexOf("declare ") == 0 || commandText.ToLower().IndexOf("begin ") == 0)
{
commandText = commandText + " ";
}
else
{
commandText = "begin " + commandText + "; end ;";
}
PrepareCommand(Command, connection, tran, CommandType.Text, commandText);
try
{
affectRows = Command.ExecuteNonQuery();
}
catch (Exception ex)
{
affectRows = -1;
needTransaction = false;
//throw ex;
Errormsg_ = ex.Message;
return(affectRows);
}
finally
{
if (mustCloseConnection)
{
if (!needTransaction)
{
connection.Close();
}
}
}
return(affectRows);
}
/// <summary>
/// Aborta la transaccion activa.
/// </summary>
public void AbortarTransaccion()
{
try
{
mTransaccion.Rollback();
}
catch (System.Exception Ex)
{
throw Ex;
}
finally
{
mTransaccion = null;
EnTranssaccion = false;
}
}
/// <summary>
/// Confirma la transaccion activa.
/// </summary>
public void TerminarTransaccion()
{
try
{
mTransaccion.Commit();
}
catch (System.Exception Ex)
{
throw Ex;
}
finally
{
mTransaccion = null;
EnTranssaccion = false;
}
}
/// <summary>
/// Ouverture d'une nouvelle transaction SQL (1 par connector)
/// </summary>
/// <param name="transactionName"></param>
/// <returns></returns>
public virtual bool BeginTransaction(string transactionName = null)
{
if (this.transaction != null)
{
return(false); // une seule transaction par connecteur
}
this.Open(); // ouverture si nécessaire
try
{
this.transaction = this.connection.BeginTransaction();
return(true);
}
catch (Exception ex)
{
throw new Exception(string.Format("Connector.BeginTransaction {0}", ex.Message), ex);
}
}
/**/
/// <summary>
/// 执行SQL语句，返回影响的记录数 、用于增删改
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SqlString)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn))
{
iConn.Open();
try
{
int rows = iCmd.ExecuteNonQuery();
return(rows);
}
catch (System.Exception E)
{
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**/
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public int ExecuteSqlTran(ArrayList SQLStringList)
{
int i = 1;
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (System.Data.IDbCommand iCmd = GetCommand())
{
iCmd.Connection = iConn;
using (System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())
{
iCmd.Transaction = iDbTran;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
iCmd.CommandText = strsql;
iCmd.ExecuteNonQuery();
}
}
iDbTran.Commit();
}
catch (System.Exception E)
{
iDbTran.Rollback();
i = -1;
return(i);
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(i);
}
}
}
}
public DataTable ExecuteReader(String sql, List <Persist> param)
{
try
{
connect.Open();
transacao = connect.BeginTransaction(IsolationLevel.ReadCommitted);
command.Connection = connect;
command.Transaction = transacao;
command.CommandText = sql;
command.Parameters.Clear();
try
{
for (int i = 0; i < param.Count; i++)
{
if (param[i].ObjValor != null)
{
DbType tipo = RetornaTipo(param[i].Type.Name.ToUpper());
parameters = command.CreateParameter();
parameters.ParameterName = "@" + i;
parameters.DbType = tipo;
parameters.Value = param[i].ObjValor == null ? DBNull.Value : param[i].ObjValor;
command.Parameters.Add(parameters);
}
}
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
transacao.Commit();
return(dt);
}
catch (Exception q)
{
transacao.Rollback();
throw new ApplicationException(q.ToString());
}
}
catch (Exception q)
{
throw new ApplicationException(q.ToString());
}
finally
{
connect.Close();
}
}
public DataTable ExcuteDataTable(DataTable srcTable, string commandText, System.Data.CommandType commandType)
{
if (this.Connection.State != System.Data.ConnectionState.Open)
{
this.Connection.Open();
}
if (!this.needTransaction)
{
tran = null;
}
if (srcTable == null)
{
srcTable = new DataTable();
}
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, this.Connection, tran, CommandType.Text, commandText);
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
da.Fill(srcTable);
da.Dispose();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw ex;
}
finally
{
if (mustCloseConnection)
{
if (!needTransaction)
{
Connection.Close();
}
}
}
return(srcTable);
}
public void TestMethon3()
{
NewCity n = new NewCity()
{
CityID = 1,
CityName = "保定",
NewName = "新北京"
};
City c = new City();
c = n;
Console.WriteLine(c.CityName);
//string _connectionString = "Data Source=.;Initial Catalog=SportsDB;Persist Security Info=True;User ID=sa;Password=11111111;Integrated Security=True";
//System.Data.IDbConnection connection = new System.Data.SqlClient.SqlConnection(_connectionString);
System.Data.IDbConnection connection = Comm.Helper.DapperHelper.Instance;
connection.Open();
System.Data.IDbTransaction transaction = connection.BeginTransaction();
List <M_User> user = new List <M_User>()
{
new M_User()
{
UserName = "******", Pwd = "111111"
},
new M_User()
{
UserName = "******", Pwd = "11111111111111111111111111111111"
},
};
try
{
connection.Insert <M_User>(user, transaction);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
public int BatchInsert <T>(List <T> modelList, System.Data.IDbTransaction trans)
{
PropertyInfo[] properties = typeof(T).GetProperties();
var table = (TableAttribute)typeof(T).GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();
DataTable dt = new DataTable(table.Name);
foreach (var property in properties)
{
var column = new DataColumn(property.Name, property.PropertyType);
dt.Columns.Add(column);
}
foreach (var model in modelList)
{
DataRow row = dt.NewRow();
foreach (var property in properties)
{
var value = property.GetValue(model, null);
row[property.Name] = value;
}
dt.Rows.Add(row);
}
using (SqlConnection con = new SqlConnection(ConnectionString))
{
try
{
con.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, (SqlTransaction)trans);
bulkCopy.DestinationTableName = table.Name;
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
return(1);
}
/// <summary>
/// Reverts the content with the given id to it's last published state.
/// </summary>
/// <param name="id">The id</param>
/// <param name="tx">Optional transaction</param>
public static void Revert(Guid id, System.Data.IDbTransaction tx = null)
{
// Get the published version of
var content = Content.GetSingle(id, tx);
if (content != null)
{
// Turn it into a draft and save it.
content.IsDraft = true;
if (content.Save(tx))
{
// Delete any possible draft version of the physical file.
Application.Current.MediaProvider.DeleteDraft(id);
// Now turn back the dates for the draft version
Content.Execute("UPDATE content SET content_updated = content_last_published WHERE content_id = @0 AND content_draft = 1", null, id);
}
}
}
private void Inicializa()
{
m_oConnection = null;
m_oCommand = null;
m_oTransaction = null;
m_sConnectionString = null;
m_nroTransaction = 0;
m_nCommandTimeout = 0;
m_nRetryConnect = 3;
m_bDisposed = false;
m_bConnected = false;
m_sProviderAssembly = null;
m_sProviderConnectionClass = null;
m_sProviderCommandBuilderClass = null;
m_eProvider = PROVIDER_TYPE.PROVIDER_NONE;
m_idConexion = 0;
m_oDataReader = null;
}
public void CommitTransaction()
{
if(m_oTransaction == null)
throw(new Exception("BeginTransaction must be called before commit or rollback. No open transactions found"));
m_oTransaction.Commit();
m_oTransaction.Dispose();
m_oTransaction = null;
}
/// <summary>
/// Comienza una nueva Transacción.
/// El primer BEGIN TRANSACTION es real, los demás son ficticios
/// </summary>
public void BeginTransaction()
{
try
{
if (m_oLog != null)
{
//m_oLog.TraceLog("Iniciando transacción...");
}
//Si no existe ninguna transaccion, realiza el BEGIN TRANSACTION real
if (m_nroTransaction == 0)
{
ValidateConnection();
m_oTransaction = m_oConnection.BeginTransaction();
m_oCommand.Transaction = m_oTransaction;
//m_oCommand.CommandTimeout = 120;
}
// Incremento nro de transacciones abiertas
m_nroTransaction++;
return;
}
catch (DataAccessException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new DataAccessException("Error inesperado al iniciar la transacción.", ex);
}
}
public void RollbackTransaction(bool bThrowError)
{
if(m_oTransaction == null)
{
if(bThrowError)
throw(new Exception("BeginTransaction must be called before commit or rollback. No open transactions found"));
}
try
{
m_oTransaction.Rollback();
}
catch
{
if(bThrowError)
throw;
}
finally
{
if(m_oTransaction != null)
m_oTransaction.Dispose();
m_oTransaction = null;
}
}
/// <summary>
/// Realiza el Rollback de las Transacciones Abiertas.
/// </summary>
/// <param name="bThrowError">Permitir o no el THROW de los errores</param>
public void RollbackTransaction(bool bThrowError)
{
if (m_oTransaction == null)
{
if (bThrowError)
throw (new DataAccessException("BeginTransaction se debe llamar antes de un commit o rollback. No se encontraron transacciones abiertas.", -35));
}
try
{
m_oTransaction.Rollback();
m_oTransaction.Dispose();
m_oTransaction = null;
}
catch (DataAccessException ex)
{
if (bThrowError)
{
throw ex;
}
}
catch (Exception ex)
{
if (bThrowError)
{
throw new DataAccessException("Error inesperado al finalizar la transacción.", ex);
}
}
finally
{
m_nroTransaction = 0;
}
}
public void BeginTransaction()
{
ValidateConnection();
m_oTransaction = m_oConnection.BeginTransaction();
m_oCommand.Transaction = m_oTransaction;
return;
}
/// <summary>
/// Realiza el COMMIT de las transacciones. Solo cuando
/// queda UNA SOLA transacción abierta, se realiza el COMMIT real.
/// </summary>
public void CommitTransaction()
{
try
{
if (m_oTransaction == null)
{
if (m_oLog != null)
{
m_oLog.TraceError("Error al realizar Commit porque no se encontraron transacciones abiertas...", m_idConexion);
}
throw (new DataAccessException("BeginTransaction se debe llamar antes de un COMMIT o ROLLBACK. No se encontraron transacciones abiertas.", -35));
}
//Si queda solo una transacción abierta
if (m_nroTransaction == 1)
{
m_oTransaction.Commit();
m_oTransaction.Dispose();
m_oTransaction = null;
}
// Decremento nro de transacciones pendientes
m_nroTransaction--;
}
catch (DataAccessException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new DataAccessException("Error inesperado al finalizar la transacción.", ex);
}
}
