Microsoft Enterprise Library - 1.0

public void RecordIsInsertedWhenExecutingExecuteReaderWithTransactionStoredProcParamsAndCallBack()
        {
            Database db = new DatabaseProviderFactory(base.ConfigurationSource).Create("DefaultSqlAsync");
            DbCommand command1 = new SqlCommand();
            AsyncCallback cb = new AsyncCallback(EndExecuteReaderCallBack);
            DbAsyncState state;
            DbTransaction transaction;
            DataTable dt = new DataTable();
            object[] paramsArray = { "BONAP" };

            DataSet ds = db.ExecuteDataSet("CustOrdersOrders", paramsArray);
            int originalCount = ds.Tables[0].Rows.Count;
            try
            {
                using (SqlConnection connection = new SqlConnection(db.ConnectionString))
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();

                    // Execute the NonQuery insert statement synchronously.                    
                    command1.CommandType = CommandType.Text;
                    command1.CommandText = @"INSERT INTO [Northwind].[dbo].[Orders]
                                                   ([CustomerID]
                                                   ,[EmployeeID]
                                                   ,[OrderDate]
                                                   ,[RequiredDate]
                                                   ,[ShippedDate]
                                                   ,[ShipVia]
                                                   ,[Freight]
                                                   ,[ShipName]
                                                   ,[ShipAddress]
                                                   ,[ShipCity]
                                                   ,[ShipRegion]
                                                   ,[ShipPostalCode]
                                                   ,[ShipCountry])
                                             VALUES
                                                   ('BONAP'
                                                   ,1
                                                   ,GETDATE()
                                                   ,GETDATE()
                                                   ,NULL
                                                   ,2
                                                   ,10
                                                   ,'TestShip'
                                                   ,'TestShip address'
                                                   ,'Marseille'
                                                   ,NULL
                                                   ,'13008'
                                                   ,'France')";
                    db.ExecuteNonQuery(command1, transaction);
                    // Get the records using overloaded BeginExecuteReader with stored proc and params Asynchronously with DBTransaction.                                        
                    state = BeginExecuteReader(db, "CustOrdersOrders", transaction, cb, paramsArray);
                    dt.Load((IDataReader)state.State);

                    transaction.Commit();

                    Assert.AreEqual<int>(originalCount + 1, dt.Rows.Count);
                    Assert.AreEqual<ConnectionState>(ConnectionState.Open, state.AsyncResult.Connection.State);
                }
            }

public string UpdateSC(SubComponentSavedInfo UpdateSCObject, string LoginID, Int64 DisputeID, int UpdateID)
        {
            string strSPNm = "dbo.dt3_web_UpdateSC";
            string NewUpdateID = "";
            using (DbCommand ObjDbCommand = objDB.GetStoredProcCommand(strSPNm))
            {
                objDB.AddInParameter(ObjDbCommand, "@SONo", DbType.String, UpdateSCObject.SoNo);
                objDB.AddInParameter(ObjDbCommand, "@SOEBD", DbType.String, UpdateSCObject.SoEBD);
                objDB.AddInParameter(ObjDbCommand, "@ChkSOFollowUpDate", DbType.String, UpdateSCObject.ChkSoFollowUpDate);
                objDB.AddInParameter(ObjDbCommand, "@ChkSOBilledDate", DbType.String, UpdateSCObject.ChkSOBilledDate);
                objDB.AddInParameter(ObjDbCommand, "@SpiritAdjustmentURL", DbType.String, UpdateSCObject.SpiritAdjUrl);
                objDB.AddInParameter(ObjDbCommand, "@DeMinimisInd", DbType.String, UpdateSCObject.DeminimisInd);
                objDB.AddInParameter(ObjDbCommand, "@DisputeTypeInd", DbType.String, UpdateSCObject.DisputeTypeInd);
                objDB.AddInParameter(ObjDbCommand, "@UpdateID", DbType.Int32, UpdateID);
                objDB.AddOutParameter(ObjDbCommand, "@NewUpdateID", DbType.String, Int32.MaxValue);
                objDB.ExecuteNonQuery(ObjDbCommand);
                NewUpdateID = Convert.ToString(objDB.GetParameterValue(ObjDbCommand, "NewUpdateID"));
            }
            return NewUpdateID;
        }

public void ValuesAreReadWhenUsingInnerReader()
         {
            Database db = DatabaseFactory.CreateDatabase("OracleTest");
            const string sqlCommand = "select * from Customers where customerId ='BLAUS'";
            DbCommand dbCommandWrapper = db.GetSqlStringCommand(sqlCommand);
            //IDataReader dataReader = db.ExecuteReader(CommandType.Text, sqlCommand);
            using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper))
            {
                using (OracleDataReader orareader = ((OracleDataReaderWrapper)dataReader).InnerReader)
                {
                    Assert.IsNotNull(orareader);
                    int n = orareader.GetOrdinal("customerId");
                    while (dataReader.Read())
                    {
                        string str1 = orareader.GetOracleValue(n).ToString();
                        string str = ((string)dataReader[0]).Trim();
                        Assert.AreNotSame(str1, str);
                    }
                }
            }
        }
  

public void AllRecordsAreReturnedWhenExecutingExecuteXmlReaderWithSqlCommandAndWithCallBack()
        {
            DbAsyncState state;
            DataTable dt = new DataTable();
            DbCommand command = new SqlCommand();
            DbCommand command1 = new SqlCommand();
            AsyncCallback cb = new AsyncCallback(EndExecuteXmlReaderCallBack);
            int rowCount = 0;
            SqlDatabase db = new DatabaseProviderFactory(base.ConfigurationSource).Create("DefaultSqlAsync") as SqlDatabase;

            //Get the row count synchronously.
            command1.CommandType = CommandType.Text;
            command1.CommandText = "SELECT count(*) FROM Employees";
            int originalCount = (int)db.ExecuteScalar(command1);

            // Get the data Asynchronously.
            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT * FROM Employees FOR XML AUTO, XMLDATA";
            state = BeginExecuteXmlReader(db, command, cb);

        }

public void ResultsAreReturnedWhenExecutingAsyncStoredProcAccessor()
        {
            Database db = new DatabaseProviderFactory(base.ConfigurationSource).Create("DefaultSqlAsync");
            DataAccessor<TopTenProduct> accessor = db.CreateSprocAccessor<TopTenProduct>("Ten Most Expensive Products");
            AsyncCallback cb = new AsyncCallback(EndExecuteAccessor<TopTenProduct>);
            DbAsyncState state = new DbAsyncState(db, accessor);
            IAsyncResult result = accessor.BeginExecute(cb, state);
            state.AutoResetEvent.WaitOne(new TimeSpan(0, 0, 15));
            IEnumerable<TopTenProduct> resultSet = (IEnumerable<TopTenProduct>)state.State;

            object[] paramsArray = { };
            DataSet ds = db.ExecuteDataSet("Ten Most Expensive Products", paramsArray);
            Assert.IsNull(state.Exception);
            Assert.AreEqual<int>(ds.Tables[0].Rows.Count, resultSet.Count());
        }

public void TestUpdateDatasetMethod()
        {
            SqlCeDatabase db = (SqlCeDatabase)database;
            DataSet dataSet = new DataSet();
            db.UpdateDataSet(dataSet, "Region", "insert into region values (99, 'Midwest')", "UPDATE region SET RegionDescription=@RegionDescription WHERE RegionID=@RegionId",
                                 "Delete from Region where RegionId = 99", "Incremental");
        }
   

public void CanExecuteXmlQueryThroughTransaction()
        {
            string insertString = "insert into region values (99, 'Midwest')";
            DbCommand insertCommand = sqlDatabase.GetSqlStringCommand(insertString);

            string queryString = "Select * from Region for xml auto";
            SqlCommand sqlCommand = sqlDatabase.GetSqlStringCommand(queryString) as SqlCommand;

            string actualOutput = "";

            using (DbConnection connection = sqlDatabase.CreateConnection())
            {
                connection.Open();
                using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
                {
                    sqlDatabase.ExecuteNonQuery(insertCommand, transaction.Transaction);

                    XmlReader results = sqlDatabase.ExecuteXmlReader(sqlCommand, transaction.Transaction);
                    results.MoveToContent();
                    for (string value = results.ReadOuterXml(); value != null && value.Length != 0; value = results.ReadOuterXml())
                    {
                        actualOutput += value;
                    }
                    results.Close();
                }
            }
            
        }

protected override void Act()
        {
            var asyncResult = database.BeginExecuteReader("Ten Most Expensive Products", null, null);
            var reader = database.EndExecuteReader(asyncResult);
            reader.Dispose();
        }

public class WhenAsynchronouslyInvokingExecuteScalarOnSproc : AsynchronousConnectionContext
    {
        IAsyncResult asyncResult;

        protected override void Arrange()
        {
            base.Arrange();

            asyncResult = database.BeginExecuteScalar("Ten Most Expensive Products", null, null);
        }
}

public class WhenAsynchronouslyInvokingBeginExecuteNonQueryOnSproc : AsynchronousConnectionContext
    {
        IAsyncResult asyncResult;

        protected override void Arrange()
        {
            base.Arrange();

            asyncResult = database.BeginExecuteNonQuery("Ten Most Expensive Products", null, null);
        }
    }

public class WhenAsynchronouslyInvokingExecuteXmlReader : AsynchronousConnectionContext
    {
        private readonly Barrier barrier = new Barrier(2);
        private XmlReader reader;

        protected override void Arrange()
        {
            base.Arrange();

            string queryString = "Select * from Region for xml auto, xmldata";
            SqlCommand sqlCommand = database.GetSqlStringCommand(queryString) as SqlCommand;
            database.BeginExecuteXmlReader(sqlCommand,
                ar =>
                {
                    barrier.Await();
                    reader = database.EndExecuteXmlReader(ar);
                    barrier.Await();
                }, null);
        }

public static void DoExecuteResultSetOracleSample(Database db)
        {
            
            db.DoExecuteNonQuery("insert into Region values (99, 'Midwest');");
        }

public static void LoadTestData(Database database)
        {
            SqlCeDatabase db = (SqlCeDatabase)database;

            db.LoadDataSet("insert into Region values (99, 'Midwest');");
        }

public void SimplifiedExecuteScalarWithParameters()
    {
        string sql = "select count(*) from region where regionId=" + db.BuildParameterName("regionId");
        int result = (int)db.ExecuteScalarSql(sql, db.CreateParameter("regionId", DbType.Int32, 0, 2));
        Assert.AreEqual(1, result);
    }

public void AlternateSqlStatementWithParameters()
        {
            string sql = "insert into region values ({0}, {1})";
            sql = String.Format(sql, db.BuildParameterName("regionId"), db.BuildParameterName("description"));

            DbParameter[] parameters = new DbParameter[]
                {
                    db.CreateParameter("regionId", DbType.Int32, 0, 99),
                    db.CreateParameter("description", DbType.String, 20, "test value")
                };
            db.ExecuteNonQuerySql(sql, parameters);
        }

public int GetIssuersCount(string cusip, string name, string ticker)
        {
            LogHelper lLogger = new LogHelper();
            int resultvalues = 0;
            try
            {
                if (!string.IsNullOrEmpty(cusip))
                    _sqlDB.AddInParameter(dbCommand, "@Cusip", SqlDbType.Char, cusip);

                if (!string.IsNullOrEmpty(name))
                    _sqlDB.AddInParameter(dbCommand, "@Name", SqlDbType.VarChar, name);

                if (!string.IsNullOrEmpty(ticker))
                    _sqlDB.AddInParameter(dbCommand, "@Ticker", SqlDbType.VarChar, ticker);

                resultvalues = (int)_sqlDB.ExecuteScalar(dbCommand);
            }
            catch (Exception ex)
            {
                lLogger.LogError(ex.ToString());

                throw new ApplicationException(Properties.Settings.Default.DBExceptionUserFriendlyMessage);
            }

            return resultvalues;
        }