using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using Microsoft.Practices.EnterpriseLibrary.Data;

using System.Data.Common;

using System.Data.SqlClient;

using log4net;

​

namespace CommonLibrary.DataAccess

{

    public class BaseDataAccess

    {

        private String dataBaseConfigName = null;

        private ILog logger;

​

        protected BaseDataAccess(String dataBase)

        {

            this.dataBaseConfigName = dataBase;

            this.logger = LogManager.GetLogger("CommonLibrary");

​

        }

        protected BaseDataAccess()

        {

            this.logger = LogManager.GetLogger("CommonLibrary");

        }

​

        protected Database GetDB()

        {

                if (String.IsNullOrEmpty(dataBaseConfigName))

                    return DatabaseFactory.CreateDatabase();

                else

                    return DatabaseFactory.CreateDatabase(dataBaseConfigName);

        }

​

        protected DbCommand CreateCommand(Database db, String spName, params object[] parameters)

        {

            if (parameters == null)

                return db.GetStoredProcCommand(spName);

            else

                return db.GetStoredProcCommand(spName, parameters);

​

        }

​

        public IDataReader GetReader(string sql, params object[] parameters)

        {

            Database db = this.GetDB();

            DbCommand dbCommand = this.CreateCommand(db, sql, parameters);

​

            foreach (SqlParameter parameter in dbCommand.Parameters)

            {

                if (parameter.SqlDbType != SqlDbType.Structured)

                {

                    continue;

                }

                string name = parameter.TypeName;

                int index = name.IndexOf(".");

                if (index == -1)

                {

                    continue;

                }

                name = name.Substring(index + 1);

                if (name.Contains("."))

                {

                    parameter.TypeName = name;

                }

            }

​

            return db.ExecuteReader(dbCommand);

        }

        public IDataReader GetSqlReader(string sqlString)

        {

            Database db = this.GetDB();

            DbCommand dbCommand = db.GetSqlStringCommand(sqlString);

            return db.ExecuteReader(dbCommand);

        }

​

        public IDataReader GetReader(string sql)

        {

            Database db = this.GetDB();

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteReader(dbCommand);

        }

​

        /// <summary>

        /// returns a DataTable by executing given store procedure with parameters

        /// </summary>

        /// <param name="spName">stored procedure name to be executed</param>

        /// <param name="parameters">parameters to be passed to DB</param>

        /// <returns></returns>

        protected DataTable LoadTable(String spName, params object[] parameters)

        {

            if (parameters == null)

                return LoadTable(spName);

            // Executes the given stores procedures which does not have any params

​

            using (IDataReader dataReader = GetReader(spName, parameters))

            {

                DataTable tempTable = new DataTable();

                tempTable.Load(dataReader, System.Data.LoadOption.OverwriteChanges);

                return tempTable;

            }         

        }

​

        protected DataTable LoadTable(String spName)

        {

            // Executes the given stores procedures which does not have any params

​

​

            DataTable tempTable = new DataTable();

            using (IDataReader dataReader = GetReader(spName))

            {

                tempTable.Load(dataReader, System.Data.LoadOption.OverwriteChanges);

            }

​

            return tempTable;

​

​

        }

​

        protected DataTable LoadTableWithSql(String sqlString)

        {

            // Executes the given stores procedures which does not have any params

​

​

            DataTable tempTable = new DataTable();

            using (IDataReader dataReader = GetSqlReader(sqlString))

            {

                tempTable.Load(dataReader, System.Data.LoadOption.OverwriteChanges);

            }

​

            return tempTable;

​

​

        }

​

        protected int ExecuteNonQuery(String spName, params object[] parameters)

        {

            Database myDB = GetDB();

            DbCommand dbCommand = CreateCommand(myDB, spName, parameters);

            return (int)myDB.ExecuteNonQuery(dbCommand);

        }

​

        protected int ExecuteNonQuery(String spName, TimeSpan timeout, params object[] parameters)

        {

            Database myDB = GetDB();

            DbCommand dbCommand = CreateCommand(myDB, spName, parameters);

            dbCommand.CommandTimeout = (int)timeout.TotalSeconds;

            return (int)myDB.ExecuteNonQuery(dbCommand);

        }

​

        protected string ExecuteQuery(String spName, params object[] parameters)

        {

            object k = ExecuteScalar(spName, parameters);

            if (k != null)

                return k.ToString();

​

            return null;

​

        }

​

        protected object ExecuteScalar(String spName, params object[] parameters)

        {

​

            Database myDB = GetDB();

​

            //DbCommand dbCommand = CreateCommand(myDB, spName, parameters);

​

            return myDB.ExecuteScalar(spName, parameters);

​

        }

        protected object ExecuteScalarFromSql(String sqlString)

        {

​

            Database myDB = GetDB();

​

            DbCommand dbCommand = myDB.GetSqlStringCommand(sqlString);

​

            return myDB.ExecuteScalar(dbCommand);

​

        }

​

        protected DataSet LoadDataSet(String spName, params object[] parameters)

        {

            // Executes the given stores procedures which does not have any params

​

            Database myDB = GetDB();

​

            DbCommand dbCommand = CreateCommand(myDB, spName, parameters);

​

            DataSet temp = myDB.ExecuteDataSet(dbCommand);

​

            myDB = null;

            return temp;

​

        }

​

        protected bool BulkInsert(string tableName, DataTable dt)

        {

            SqlConnection connection = null;

            bool flag = true;

            try

            {

                Database myDB = GetDB();

​

                connection = myDB.CreateConnection() as SqlConnection;

                SqlBulkCopy bulkCopy =

                new SqlBulkCopy

                (

                connection,

                SqlBulkCopyOptions.TableLock |

               SqlBulkCopyOptions.FireTriggers |

                SqlBulkCopyOptions.UseInternalTransaction,

                null

                );

​

                // set the destination table name

                bulkCopy.DestinationTableName = tableName;

                connection.Open();

​

                // write the data in the "dataTable"

                bulkCopy.WriteToServer(dt);

            }

            catch (Exception ex)

            {

                logger.Error(ex);

                flag = false;

            }

            finally

            {

                connection.Close();

            }

​

            return flag;

        }

​

        protected bool BulkInsert(string tableName, DataTable dt, SqlConnection connection)

        {

            try

            {

                //Database myDB = GetDB();

​

                SqlBulkCopy bulkCopy = new SqlBulkCopy

                      (

                      connection,

                      SqlBulkCopyOptions.FireTriggers, 

                      null

                      );

                // set the destination table name

                bulkCopy.DestinationTableName = tableName;

                if (connection.State != ConnectionState.Open)

                {

                    connection.Open();

                }

​

                // write the data in the "dataTable"

                bulkCopy.WriteToServer(dt);

            }

            catch (Exception ex)

            {

                logger.Error(ex);

                return false;

            }

​

            return true;

        }

​

​

​

        public DbConnection GetConnection(string sp)

        {

            Database db = this.GetDB();

            DbCommand dbCommand = this.CreateCommand(db, sp);

            db.ExecuteNonQuery(dbCommand);

            return dbCommand.Connection;

        }

    }

}

----------------------------------------------

​

using System.Xml;

​

namespace CommonLibrary.ServiceClients

{

    public class InventoryServiceClient : IInventoryServiceClient

    {

        private const String MESSAGEID = "MessageId";

        private const String NS = "http://www.lcwaikiki.com";

​

        private InventoryCache cacheManager;

        private ServiceContract.Services.IInventoryService serviceClient;

​

        private static volatile InventoryServiceClient instance;

        private static object syncRoot = new Object();

​

​

​

        private bool LocalCacheEnabled = false;

        private ILog logger;

​

        private InventoryServiceClient()

        {

            string clientCacheEnabled = System.Configuration.ConfigurationManager.AppSettings["InventoryServiceClientCacheEnabled"];

            if (!string.IsNullOrEmpty(clientCacheEnabled))

            {

                Boolean.TryParse(clientCacheEnabled, out LocalCacheEnabled);

            }

​

            if (this.serviceClient == null)

            {

                this.serviceClient = ServiceProvider.ServiceProvider.GetInventoryService();

            }

            cacheManager = new InventoryCache();

            this.logger = LogManager.GetLogger(GetType());

        }

​

​

        private InventoryServiceClient(IEndpointBehavior theBehavior)

        {

            if (this.serviceClient == null)

            {

                this.serviceClient = ServiceProvider.ServiceProvider.GetInventoryService(theBehavior);

            }

            cacheManager = new InventoryCache();

            this.logger = LogManager.GetLogger(GetType());

        }

​

        private InventoryServiceClient(bool reset)

        {

            this.serviceClient = ServiceProvider.ServiceProvider.GetInventoryService(reset);

            this.logger = LogManager.GetLogger(GetType());

        }

​

        public static void Reset()

        {

            lock (syncRoot)

            {

                instance = null;

                instance = new InventoryServiceClient(true);

            }

        }

​

​

​

        public static InventoryServiceClient GetInstance()

        {

            /*if (instance == null)

            {

                lock (syncRoot)

                {

                    if (instance == null)*/

            instance = new InventoryServiceClient();

            /*}

        }*/

            return instance;

        }

​

        public static InventoryServiceClient GetInstance(IEndpointBehavior theBehavior)

        {

            return new InventoryServiceClient(theBehavior);

        }

​

        public ProductList GetProducts(byte productGroupId, byte genderId, Int16 categoryId, Int32 pageSize, Int32 pageIndex, List<Filter> filters,

            ProductOrderEnum orderby, string culture, FilteringTypeEnum filteringType, ECommerceWebContext context, byte baseProductGroupId, HashSet<int> optionIds = null)

        {

            ProductList productList = null;

​

            string cacheKey = string.Empty;

            bool useLocalCache = this.LocalCacheEnabled && (optionIds == null || !optionIds.Any());

            if (useLocalCache)

            {

                cacheKey = cacheManager.GetKey(productGroupId, genderId, categoryId, pageSize, pageIndex, filters, orderby, culture, filteringType, context.RegionId, baseProductGroupId);

                productList = cacheManager.GetProductList(cacheKey);

                if (productList != null)

                    return productList;

            }

            CatalogQueryRequest cqr = new CatalogQueryRequest

            {

                Culture = culture,

                ProductGroupID = productGroupId,

                GenderID = genderId,

                CategoryID = categoryId,

                PageSize = pageSize,

                PageIndex = pageIndex,

                Filters = filters,

                ProductOrder = orderby,

                FilteringType = filteringType,

                ApplicationId = context.ApplicationId,

                IsOperator = context.IsOperator,

                RegionId = context.RegionId,

                BaseProductGroupId = baseProductGroupId,

                ABSettings = context.AbSettings,

                OptionIds = optionIds

            };

​

            DateTime startedAt = DateTime.Now;

​

            CatalogQueryResponse response = serviceClient.GetProducts(cqr);

​

            TimeSpan ts = DateTime.Now.Subtract(startedAt);

            if (ts.TotalSeconds > 3)

                logger.Warn(String.Format("inventoryServiceClient.GetProducts-1 execution time higher than 3 secs TotalMs:{0}", ts.TotalMilliseconds));

​

            if (response != null)

            {

                productList = response.ProductList;

                if (useLocalCache && productList != null && productList.ProductSummaryList != null && productList.ProductSummaryList.Count > 0)

                {

                    cacheManager.SetProducts(cacheKey, productList);

                }

            }

​

            return productList;

        }

​