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 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();
}
}
}
protected 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;
}
}
public DataTable ExecuteReader(String sql)
{
try
{
connect.Open();
transacao = connect.BeginTransaction(IsolationLevel.ReadCommitted);
command.Connection = connect;
command.Transaction = transacao;
command.CommandText = sql;
try
{
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
transacao.Commit();
return(dt);
}
catch (Exception q)
{
transacao.Rollback();
throw new ApplicationException(q.ToString());
}
}
catch
{
throw;
}
finally
{
connect.Close();
}
}
public void ExecuteNonQuery(String sql)
{
try
{
connect.Open();
transacao = connect.BeginTransaction(IsolationLevel.ReadCommitted);
command.Connection = connect;
command.Transaction = transacao;
command.CommandText = sql;
try
{
command.ExecuteNonQuery();
transacao.Commit();
}
catch (Exception q)
{
transacao.Rollback();
throw new ApplicationException(q.ToString());
}
}
catch
{
throw;
}
finally
{
connect.Close();
}
}
public void SaveAppConfig()
{
if (!Database.Instance.IsConfigured)
{
configureDatabase();
}
System.Data.IDbTransaction trans = Database.Instance.Connection.BeginTransaction();
try {
Database.Instance.ExecuteNonQuery("delete from AppConfig", trans);
foreach (string key in AppConfig.Keys)
{
object exists = Database.Instance.ExecuteScalar("select count(*) from AppConfig where ConfigKey=@p0", trans, key);
int count;
if (exists != null && int.TryParse(exists.ToString(), out count) && count > 0)
{
Database.Instance.ExecuteNonQuery("update AppConfig set @p0 = @p1", trans, key, AppConfig[key]);
}
else
{
Database.Instance.ExecuteNonQuery("insert into AppConfig(ConfigKey, ConfigValue) Values(@p0, @p1)", trans, key, AppConfig[key]);
}
}
trans.Commit();
} catch (Exception ex) {
trans.Rollback();
throw ex;
} finally {
trans.Dispose();
}
}
/// <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();
}
}
}
}
}
}
/// <summary>
/// Aborta la transaccion activa.
/// </summary>
public void AbortarTransaccion()
{
try
{
mTransaccion.Rollback();
}
catch (System.Exception Ex)
{
throw Ex;
}
finally
{
mTransaccion = null;
EnTranssaccion = false;
}
}
/**/
/// <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 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 Int32 ExecuteNonQuery(String sql, List <Persist> param)
{
try
{
connect.Open();
transacao = connect.BeginTransaction(IsolationLevel.ReadCommitted);
command.Connection = connect;
command.Transaction = transacao;
command.CommandText = sql;
int retorno;
command.Parameters.Clear();
try
{
for (int i = 0; i < param.Count; i++)
{
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);
}
retorno = Convert.ToInt32(command.ExecuteScalar());
transacao.Commit();
return(retorno);
}
catch (Exception q)
{
transacao.Rollback();
throw new ApplicationException(q.ToString());
}
}
catch (Exception q)
{
throw new ApplicationException(q.ToString());
}
finally
{
connect.Close();
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(List <string> list)
{
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 < list.Count; n++)
{
string strsql = list[n].ToString();
iCmd.CommandText = strsql;
iCmd.ExecuteNonQuery();
}
iDbTran.Commit();
}
catch (System.Exception E)
{
iDbTran.Rollback();
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
}
public static string[] Build(Arguments.IBuildArguments buildArguments, Logging.ILog log = null)
{
if (log == null)
{
log = new Logging.NoLogging();
}
if (string.IsNullOrEmpty(buildArguments.ScriptPath))
{
buildArguments.ScriptPath = System.Environment.CurrentDirectory;
}
if (string.IsNullOrEmpty(buildArguments.DbProviderFactory))
{
buildArguments.DbProviderFactory = "System.Data.SqlClient";
}
if (string.IsNullOrEmpty(buildArguments.ScriptProcessor))
{
buildArguments.ScriptProcessor = typeof(SqlScriptRunner.ScriptProcessing.SqlServerScriptProcessor).FullName;
}
if (string.IsNullOrEmpty(buildArguments.VersionParser))
{
buildArguments.VersionParser = typeof(SqlScriptRunner.Versioning.VersionDateParser).AssemblyQualifiedName;
}
Type t = Type.GetType(buildArguments.VersionParser);
SqlScriptRunner.Versioning.IParseVersions versionParser =
(Activator.CreateInstance(t) as
SqlScriptRunner.Versioning.IParseVersions);
SqlScriptRunner.Versioning.Version minVersion = null;
SqlScriptRunner.Versioning.Version maxVersion = null;
if (string.IsNullOrEmpty(buildArguments.MinimumVersion))
{
minVersion = SqlScriptRunner.Versioning.Version.Min;
}
else
{
minVersion = versionParser.Parse(buildArguments.MinimumVersion);
}
if (string.IsNullOrEmpty(buildArguments.MaximumVersion))
{
maxVersion = SqlScriptRunner.Versioning.Version.Max;
}
else
{
maxVersion = versionParser.Parse(buildArguments.MaximumVersion);
}
log.Info("--------------------------------");
log.Info(string.Format("Min:{0}, Max:{1}, ScriptPath:{2}, Transactional:{4}, DryRun:{5}\r\nConnectionString:{3}", minVersion, maxVersion, buildArguments.ScriptPath, buildArguments.ConnectionString, buildArguments.Transactional, buildArguments.DryRun));
log.Info("--------------------------------");
DbConnection connection = null;
if (!buildArguments.DryRun)
{
//make sure we can connect to the database
DbProviderFactory factory = DbProviderFactories.GetFactory(buildArguments.DbProviderFactory);
connection = factory.CreateConnection();
if (connection == null)
{
throw new ArgumentException(
"Could not create a connection to the database, via the Provider Factory:" +
buildArguments.DbProviderFactory);
}
else
{
connection.ConnectionString = buildArguments.ConnectionString;
connection.Open();
}
}
SortedList <string, string> Files = SqlScriptRunner.ScriptRunner.ResolveScriptsFromPathAndVersion(buildArguments.ScriptPath, buildArguments.ScriptPattern, buildArguments.Recurse, System.Environment.CurrentDirectory, minVersion, maxVersion, versionParser);
log.Info(string.Format("Resolved:{0} files.", Files.Count));
foreach (var file in Files.Keys)
{
log.Info(file);
if (!buildArguments.DryRun)
{
try
{
log.Info("Executing");
string script = System.IO.File.ReadAllText(Files[file]);
SqlScriptRunner.ScriptRunner runner = new ScriptRunner(script, null);
if (buildArguments.Transactional)
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
System.Data.IDbTransaction transaction = null;
if (buildArguments.Transactional)
{
transaction = connection.BeginTransaction();
}
try
{
runner.Execute(connection, transaction);
if (buildArguments.Transactional)
{
transaction.Commit();
}
log.Info("Success:" + file);
}
catch (Exception e)
{
log.Info("Fail [In Trx:" + buildArguments.Transactional + "]:" + file);
log.Fatal(e);
if (buildArguments.Transactional)
{
transaction.Rollback();
}
throw;
}
}
else
{
runner.Execute(connection);
}
}
catch (Exception e)
{
if (buildArguments.BreakOnError)
{
throw;
}
else
{
log.Debug("There was an error with a script, since BreakOnError is false, we will continue.File:" + file, e);
}
}
}
}
log.Info("Done Executing");
return((from f in Files select f.Value).ToArray());
}
public void Rollback()
{
m_observers.Clear();
m_innerTransaction.Rollback();
}
public HeadCountDTO Post(HeadCountSave p)
{
using (System.Data.IDbTransaction dbTrans = Db.BeginTransaction())
{
try
{
HeadCount HeadCountItem = p.Headcount;
//If inserting
if (HeadCountItem.Id == 0)
{
HeadCountItem.CreatedBy = HeadCountItem.UpdatedBy = WindowsHelper.WindowsUserName;
HeadCountItem.CreatedDate = HeadCountItem.UpdatedDate = DateTime.Now;
HeadCountItem.Id = Convert.ToInt32(Db.GetLastInsertId());
}
// Else updating
else
{
HeadCountItem.UpdatedBy = WindowsHelper.WindowsUserName;
HeadCountItem.UpdatedDate = DateTime.Now;
}
// Save and Commit all changes to the database
Db.Save <HeadCount>(HeadCountItem);
if (HeadCountItem.Id == 0)
{
HeadCountItem.Id = Convert.ToInt32(Db.GetLastInsertId());
}
List <UploadFileDetail> UploadFileDetailsList = p.UploadFileDetailList;
Db.Delete <UploadFileDetail>(x => x.HeadCountID == HeadCountItem.Id);
foreach (UploadFileDetail item in UploadFileDetailsList)
{
Db.Insert(new UploadFileDetail
{
HeadCountID = HeadCountItem.Id,
ContentType = item.ContentType,
DisplayName = item.DisplayName,
Extension = item.Extension,
FileData = item.FileData,
FileSize = item.FileSize,
Name_File = item.Name_File,
UploadType = item.UploadType,
CreatedBy = WindowsHelper.WindowsUserName,
UpdatedBy = WindowsHelper.WindowsUserName,
CreatedDate = DateTime.Now,
UpdatedDate = DateTime.Now
});
}
dbTrans.Commit();
HeadCountList r = new HeadCountList();
r.Id = HeadCountItem.Id;
return(Get(r));
}
catch (Exception ex)
{
dbTrans.Rollback();
ErrorLogger.InsertError("Could not update HeadCount", ex);
throw new Exception("Could not update HeadCount", ex);
}
}
}