public void RecordsAreNotSavedwhenLoadDataSetStoredProcCommandAndTransactionRollback()
{
OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest");
DataSet dsCountry = new DataSet();
DbCommand dbAddCountry = db.GetStoredProcCommand("AddCountryListAll");
db.AddInParameter(dbAddCountry, "vCountryCode", DbType.String, "SCO");
db.AddInParameter(dbAddCountry, "vCountryName", DbType.String, "Scotland");
DbCommand dbAddCountry1 = db.GetStoredProcCommand("AddCountryListAll");
db.AddInParameter(dbAddCountry1, "vCountryCode", DbType.String, "SCO");
db.AddInParameter(dbAddCountry1, "vCountryName", DbType.String, "Scotland");
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
db.LoadDataSet(dbAddCountry, dsCountry, "Country", transaction);
db.LoadDataSet(dbAddCountry1, dsCountry, "Country", transaction);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
string countryName = (string)db.ExecuteScalar(CommandType.Text, "select countryName from Country where CountryCode='SCO'");
Assert.IsNull(countryName);
}
/// <summary>
/// 功能：执行事物 (返回结果List 0 影响行数 1 执行出现错误的SQL )
/// 日期：2013-05-15
/// </summary>
/// <param name="dic">SQL列表</param>
/// <param name="dbT">库类型</param>
/// <returns></returns>
public static List <object> ExecuteDBTransaction(Dictionary <string, List <ParameterEntity> > dic, DataBaseType dbT)
{
List <object> obj = new List <object>();
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
int result = 0;
string errorSQL = "";
using (DbConnection conn = oraDb.CreateConnection())
{
DbTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
foreach (string item in dic.Keys)
{
if (!string.IsNullOrEmpty(item))
{
errorSQL = item;
DbCommand cmd = oraDb.GetSqlStringCommand(item);
if (dic[item] != null && dic[item].Count > 0)
{
foreach (ParameterEntity par in dic[item])
{
oraDb.AddInParameter(cmd, par.Name, par.DbType, par.Value);
}
}
if (oraDb.ExecuteNonQuery(cmd, tran) >= 0)
{
result++;
}
else
{
tran.Rollback();
result = 0;
obj.Add(result);
obj.Add(errorSQL);
return(obj);
}
}
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw (e);
result = 0;
obj.Add(result);
obj.Add(errorSQL);
return(obj);
}
obj.Add(result);
obj.Add(errorSQL);
return(obj);
}
}
/// <summary>
/// Oracle 연결이 성공하지 못하는 경우가 많아, 재시도 횟수 만큼 간격을 두고 연결을 시도합니다.
/// Oracle DB의 Process 수를 기본(40)에서 100 이상으로 늘려주면 연결이 성공할 확률이 높습니다.
/// </summary>
/// <param name="db">DAAB OracleProvider 인스턴스</param>
/// <param name="tryCount">연결 실패 시, 재 시도 횟수</param>
/// <returns>새로 연결된 <see cref="OracleConnection"/>의 인스턴스, 만약 연결에 실패했다면 null을 반환합니다.</returns>
public static OracleConnection OpenConnection(this OracleDatabase db, int tryCount)
{
// NOTE: Oracle의 경우 Connection이 연결 안 되는 에러(ORA-12519) 가 자주 발생한다. 그래서, 시간 Time을 두고, 재시도하도록 하였다.
// HINT: http://forums.oracle.com/forums/thread.jspa?messageID=1145120,
// HINT: http://kyeomstar.tistory.com/160
if (IsDebugEnabled)
{
log.Debug("Oracle Database를 연결하려고 합니다...");
}
OracleConnection connection = null;
for (var i = 0; i < tryCount; i++)
{
var count = i + 1;
try {
if (IsDebugEnabled)
{
log.Debug("OracleConnection을 생성하고, 비동기 방식으로 Open합니다. 시도 횟수=[{0}]", (i + 1));
}
connection = (OracleConnection)db.CreateConnection();
connection.Open();
//Task.Factory.FromAsync(connection.BeginOpen,
// connection.EndOpen,
// null)
// .WaitAsync(TimeSpan.FromSeconds(15));
if (connection.State == ConnectionState.Open)
{
if (IsDebugEnabled)
{
log.Debug("OracleConnection을 연결했습니다!!!");
}
return(connection);
}
}
catch (AggregateException age) {
age.Handle(ex => {
HandleConnectionError(db, ex, count);
return(true);
});
}
catch (Exception ex) {
HandleConnectionError(db, ex, count);
}
}
Guard.Assert(connection != null && connection.State != ConnectionState.Closed, "Oracle Connection을 열지 못했습니다!!!");
return(connection);
}
public static String GetConnectStr()
{
//String connectionString = "Data Source=ORACLE82;User id=his;Password=h7j1y4s5g;";
NameValueCollection app = System.Configuration.ConfigurationManager.AppSettings;
String ls_db = app["dbservername"];
String ls_user = app["predbuser"];
String ls_password = app["predbpwd"];
String userName = app["userName"];
String connectionString = GenConnectString(ls_db, ls_user, ls_password);
List <User> list_user = new List <User>();
OracleDatabase db = new OracleDatabase(connectionString);
#region 获取数据
using (DbConnection dbConnection = db.CreateConnection())
{
dbConnection.Open();
string connStr = @"select dbusername,dbuserpasswd from vi_dbuser";
DbCommand cmd = db.GetSqlStringCommand(connStr);
//db.AddInParameter(cmd, ":id", DbType.String, "12");
#region 12
//List<DbParameter> ListPara;
//DbParameter para = factory.CreateParameter();
//para.ParameterName = "@xm";
//para.Size = 10;
//para.DbType = DbType.String;
//para.Value = "郑成城";
#endregion
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
User user = new User();
user.UserName = f_passwd2(reader.GetString(0), 2).ToLower().Trim();
user.UserPassword = f_passwd2(reader.GetString(1), 2);
list_user.Add(user);
}
}
}
#endregion
foreach (User user in list_user)
{
if (user.UserName == userName)
{
return(GenConnectString(ls_db, user.UserName, user.UserPassword));
}
}
return("无");
}
public void CanExecuteCommandWithEmptyPackages()
{
ConnectionStringSettings data = ConfigurationManager.ConnectionStrings["OracleTest"];
OracleDatabase oracleDatabase = new OracleDatabase(data.ConnectionString);
DbConnection connection = oracleDatabase.CreateConnection();
Assert.IsNotNull(connection);
Assert.IsTrue(connection is OracleConnection);
connection.Open();
DbCommand cmd = oracleDatabase.GetSqlStringCommand("Select * from Region");
cmd.CommandTimeout = 0;
}
public void CanExecuteCommandWithEmptyPackages()
{
ConnectionStringSettings data = view.GetConnectionStringSettings("OracleTest");
OracleDatabase oracleDatabase = new OracleDatabase(data.ConnectionString);
DbConnection connection = oracleDatabase.CreateConnection();
Assert.IsNotNull(connection);
Assert.IsTrue(connection is OracleConnection);
connection.Open();
DbCommand cmd = oracleDatabase.GetSqlStringCommand("Select * from Region");
cmd.CommandTimeout = 0;
}
public void CanConnectToOracleAndExecuteAReader()
{
ConnectionStringSettings data = view.GetConnectionStringSettings("OracleTest");
OracleDatabase oracleDatabase = (OracleDatabase)assembler.Assemble(data.Name, data, configurationSource);
DbConnection connection = oracleDatabase.CreateConnection();
Assert.IsNotNull(connection);
Assert.IsTrue(connection is OracleConnection);
connection.Open();
DbCommand cmd = oracleDatabase.GetSqlStringCommand("Select * from Region");
cmd.CommandTimeout = 0;
}
public void Test1()
{
ConnectionStringSettings data = GetConnectionString("DbWithOracleAuth");
OracleDatabase db = new OracleDatabase(data.ConnectionString);
using (DbConnection dbConnection = db.CreateConnection())
{
dbConnection.Open();
DbCommand command = db.GetSqlStringCommand("");
using (DbTransaction trans = dbConnection.BeginTransaction())
{
db.ExecuteNonQuery(command, trans);
}
}
}
public void CanConnectToOracleAndExecuteAReader()
{
OracleDatabase oracleDatabase =
EnterpriseLibraryContainer.CreateDefaultContainer(configurationSource)
.GetInstance <OracleDatabase>("OracleTest");
DbConnection connection = oracleDatabase.CreateConnection();
Assert.IsNotNull(connection);
Assert.IsTrue(connection is OracleConnection);
connection.Open();
DbCommand cmd = oracleDatabase.GetSqlStringCommand("Select * from Region");
cmd.CommandTimeout = 0;
}
public static DbTransaction BeginTransaction(DataBaseType dbT)
{
try
{
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
DbConnection conn = oraDb.CreateConnection();
DbTransaction tran = null;
conn.Open();
tran = conn.BeginTransaction();
return(tran);
}
catch (Exception e)
{
throw e;
}
}
public void CanExecuteCommandWithEmptyPackages()
{
ConnectionStringSettings data = ConfigurationManager.ConnectionStrings["OracleTest"];
OracleDatabase oracleDatabase = new OracleDatabase(data.ConnectionString);
DbConnection connection = oracleDatabase.CreateConnection();
Assert.IsNotNull(connection);
Assert.IsTrue(connection is OracleConnection);
connection.Open();
DbCommand cmd = oracleDatabase.GetSqlStringCommand("Select * from Region");
cmd.CommandTimeout = 0;
cmd.Connection = connection;
using (cmd.ExecuteReader()) { };
connection.Close();
}
/// <summary>
/// 根据SQL执行,带参数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns>object</returns>
public int ExecuteBySql(StringBuilder sql, SqlParam[] param)
{
int num = 0;
try
{
dbCommand = this.GetInstance().GetSqlStringCommand(Replace(sql.ToString()));
DbCommon.AddInParameter(db, dbCommand, param);
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
dbTransaction = connection.BeginTransaction();
try
{
num = db.ExecuteNonQuery(dbCommand, dbTransaction);
dbTransaction.Commit();
}
catch (Exception e)
{
dbTransaction.Rollback();
num = -1;
DbLog.WriteException(e);
}
finally
{
connection.Close();
connection.Dispose();
dbTransaction.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
return(num);
}
public void RecordIsUpdatedWhenUsingStoredProcCommandWithParameterAndCommit()
{
OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest");
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
int initialCount = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, "select count(countrycode) from Country"));
db.ExecuteDataSet(transaction, "AddCountryListAll", new object[] { "TEMP", "Temporary", "" });
db.ExecuteDataSet(transaction, "DeleteCountryListAll", new object[] { "TEMP", "" });
transaction.Commit();
int finalCount = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, "select count(countrycode) from Country"));
Assert.IsTrue(finalCount == initialCount);
}
catch (Exception e)
{
transaction.Rollback();
Assert.Fail("Transaction Rolled Back : " + e.Message);
}
}
}