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);
}
public void CanGetExtraInformation()
{
ConnectionStringSettings data
= new ConnectionStringSettings(name, "connection string;");
OracleConnectionData oracleConnectionData = new OracleConnectionData();
oracleConnectionData.Name = name;
oracleConnectionData.Packages.Add(new OraclePackageData("package1", "pref1"));
oracleConnectionData.Packages.Add(new OraclePackageData("package2", "pref2"));
OracleConnectionSettings oracleConnectionSettings = new OracleConnectionSettings();
oracleConnectionSettings.OracleConnectionsData.Add(oracleConnectionData);
configurationSource.Add(OracleConnectionSettings.SectionName, oracleConnectionSettings);
OracleDatabase database = (OracleDatabase)assembler.Assemble(data.Name, data, configurationSource);
Assert.IsNotNull(database);
Assert.AreSame(typeof(OracleDatabase), database.GetType());
// can't access the packages - must resort to side effect
DbCommand dBCommand = database.GetStoredProcCommand(OracleTestStoredProcedureInPackageWithTranslation);
Assert.AreEqual((object)OracleTestTranslatedStoredProcedureInPackageWithTranslation, dBCommand.CommandText);
}
public static Boolean ExecuteStoredProc(string storedProcedureName)
{
bool results;
try
{
string instance = ApplicationConfiguration.Instance.DefaultInstance;
var db = new OracleDatabase(GetConnectStringFromInstance(instance));
System.Data.Common.DbCommand cmd = db.GetStoredProcCommand(storedProcedureName);
if (cmd != null)
{
var records = db.ExecuteNonQuery(cmd);
results = records == 1;
}
else
{
results = false;
}
}
catch (Exception ex)
{
results = false;
LogError(ex.Message, "ExecuteStoredProc");
}
return(results);
}
/// <summary>
/// 执行数据库语句支持存储过程 2013-07-01
/// </summary>
/// <param name="sql"></param>
/// <param name="parList"></param>
/// <param name="dbT"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, List <ParameterEntity> parList, DataBaseType dbT, CommandType cmdType)
{
try
{
DbCommand cmd;
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
if (cmdType == CommandType.StoredProcedure)
{
cmd = oraDb.GetStoredProcCommand(sql);
}
else
{
cmd = oraDb.GetSqlStringCommand(sql);
}
cmd.CommandType = cmdType;
if (parList != null && parList.Count > 0)
{
foreach (ParameterEntity par in parList)
{
oraDb.AddInParameter(cmd, par.Name, par.DbType, par.Value);
}
}
return(oraDb.ExecuteNonQuery(cmd));
}
catch (Exception e)
{
//return 0;
throw e;
}
}
public void DataIsLoadedWhenLoadDataSetUsingStoredProcCommandAndMultipleTables()
{
OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest");
DataSet ds = new DataSet();
OracleCommand dbCommand = (OracleCommand)db.GetStoredProcCommand("GetCustomersAndProductsView");
db.AddParameter(dbCommand, "cur_OUT", OracleType.Cursor, 100, ParameterDirection.Output, true, 1, 1, null, DataRowVersion.Default, null);
db.AddParameter(dbCommand, "cur_Products", OracleType.Cursor, 100, ParameterDirection.Output, true, 1, 1, null, DataRowVersion.Default, null);
db.LoadDataSet(dbCommand, ds, new string[] { "Customers", "Products" });
int rows = ds.Tables["Customers"].Rows.Count;
int columns = ds.Tables["Customers"].Columns.Count;
Assert.IsTrue(rows == dsCustomers.Tables[0].Rows.Count);
Assert.IsTrue(columns == dsCustomers.Tables[0].Columns.Count);
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
Assert.AreEqual(dsCustomers.Tables[0].Rows[i][j].ToString().Trim(), ds.Tables["Customers"].Rows[i][j].ToString().Trim());
}
}
rows = ds.Tables["Products"].Rows.Count;
columns = ds.Tables["Products"].Columns.Count;
Assert.IsTrue(rows == dsProducts.Tables[0].Rows.Count);
Assert.IsTrue(columns == dsProducts.Tables[0].Columns.Count);
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
Assert.AreEqual(dsProducts.Tables[0].Rows[i][j].ToString().Trim(), ds.Tables["Products"].Rows[i][j].ToString().Trim());
}
}
}
public void CanCreateOracleDatabaseFromFactory()
{
var factory = new DatabaseProviderFactory(new SystemConfigurationSource(false).GetSection);
OracleDatabase createdObject = (OracleDatabase)factory.Create("OracleTest");
Assert.IsNotNull(createdObject);
Assert.AreEqual(@"server=entlib;", createdObject.ConnectionStringWithoutCredentials);
// can do the configured package mapping?
Assert.AreEqual(DatabaseFactoryFixture.OracleTestTranslatedStoredProcedureInPackageWithTranslation,
createdObject.GetStoredProcCommand(
DatabaseFactoryFixture.OracleTestStoredProcedureInPackageWithTranslation).CommandText);
Assert.AreEqual(DatabaseFactoryFixture.OracleTestStoredProcedureInPackageWithoutTranslation,
createdObject.GetStoredProcCommand(
DatabaseFactoryFixture.OracleTestStoredProcedureInPackageWithoutTranslation).CommandText);
}
public void CanCreateOracleDatabaseFromContainer()
{
IUnityContainer container = new UnityContainer();
container.AddExtension(new EnterpriseLibraryCoreExtension());
container.AddNewExtension <DataAccessBlockExtension>();
OracleDatabase createdObject = container.Resolve <OracleDatabase>("OracleTest");
Assert.IsNotNull(createdObject);
Assert.AreEqual(@"server=entlib;", createdObject.ConnectionStringWithoutCredentials);
Assert.AreEqual(DatabaseWithObjectBuildUperFixture.OracleTestTranslatedStoredProcedureInPackageWithTranslation,
createdObject.GetStoredProcCommand(
DatabaseWithObjectBuildUperFixture.OracleTestStoredProcedureInPackageWithTranslation).CommandText);
Assert.AreEqual(DatabaseWithObjectBuildUperFixture.OracleTestStoredProcedureInPackageWithoutTranslation,
createdObject.GetStoredProcCommand(
DatabaseWithObjectBuildUperFixture.OracleTestStoredProcedureInPackageWithoutTranslation).CommandText);
}
/// <summary>
/// 执行数据库语句支持存储过程,含输出参数 2013-07-01
/// </summary>
/// <param name="sql">存储过程名称</param>
/// <param name="parList">参数</param>
/// <param name="outParIdName">out 参数1名称</param>
/// <param name="outParIdValue">out 参数1值（必须是int/long）</param>
/// <param name="outParName">out 参数2名称</param>
/// <param name="outParValue">out 参数2值 </param>
/// <param name="dbT">数据库</param>
/// <param name="cmdType">sql类型</param>
/// <returns></returns>
public static bool ExecuteNonQueryWithOutResult(string sql, List <ParameterEntity> parList, string outParIdName, out string outParIdValue, string outParName, out string outParValue, DataBaseType dbT, CommandType cmdType)
{
try
{
DbCommand cmd;
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
if (cmdType == CommandType.StoredProcedure)
{
cmd = oraDb.GetStoredProcCommand(sql);
}
else
{
cmd = oraDb.GetSqlStringCommand(sql);
}
cmd.CommandType = cmdType;
if (parList != null && parList.Count > 0)
{
foreach (ParameterEntity par in parList)
{
oraDb.AddInParameter(cmd, par.Name, par.DbType, par.Value);
}
}
if (!string.IsNullOrEmpty(outParIdName))
{
oraDb.AddOutParameter(cmd, outParIdName, DbType.Int64, 1024);
}
if (!string.IsNullOrEmpty(outParName))
{
oraDb.AddOutParameter(cmd, outParName, DbType.String, 1024);
}
oraDb.ExecuteNonQuery(cmd);
if (!string.IsNullOrEmpty(outParIdName))
{
outParIdValue = cmd.Parameters[outParIdName].Value.ToString();
}
else
{
outParIdValue = "";
}
if (!string.IsNullOrEmpty(outParName))
{
outParValue = cmd.Parameters[outParName].Value.ToString();
}
else
{
outParValue = "";
}
return(true);
}
catch (Exception e)
{
outParValue = e.Message;
throw e;
}
}
public void GuidIsPassedAsPrameterAndReturnedAsOutput()
{
OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest");
Guid myVal = new Guid("33333333333333333333333333444444");
string spName = "sp_GUIDTEST";
DbCommand dbCommand = db.GetStoredProcCommand(spName);
db.AddInParameter(dbCommand, "vGuidInput", DbType.Guid);
db.SetParameterValue(dbCommand, "vGuidInput", myVal);
db.AddParameter(dbCommand, "vGuidOutput", DbType.Guid, ParameterDirection.Output, "", DataRowVersion.Current, null);
db.ExecuteNonQuery(dbCommand);
Guid outputVal = (Guid)db.GetParameterValue(dbCommand, "vGuidOutput");
Assert.IsTrue(myVal.Equals(outputVal));
}
public void ParametersAreAddedWhenUsingDbCommand()
{
OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest");
DbCommand dbCmd = db.GetStoredProcCommand("AddCountryInvalidSP");
db.AddInParameter(dbCmd, "Param", DbType.Int32, 10);
db.AddInParameter(dbCmd, "Param", DbType.Int32, 20);
Assert.AreEqual(2, dbCmd.Parameters.Count);
Assert.AreEqual(10, (int)db.GetParameterValue(dbCmd, "Param"));
DbCommand dbCmd1 = db.GetSqlStringCommand("select * from InvalidType");
Guid guid = Guid.NewGuid();
db.AddInParameter(dbCmd1, "Param1", DbType.Guid, guid);
Assert.AreEqual(guid, (Guid)db.GetParameterValue(dbCmd1, "Param1"));
DbCommand dbCmd2 = db.GetSqlStringCommand("select * from InvalidType");
db.AddInParameter(dbCmd2, "Param3", DbType.Boolean, true);
Assert.AreEqual(true, (bool)db.GetParameterValue(dbCmd2, "Param3"));
}