Ado.net Provider Summary

Ado.Net Provider Summary

//=======================================================================================

Data Set Operation

using System.Data;
using System.Data.SqlClient;
using DotDataMapper.AdoProvider.Interface.DataSet;
using DotDataMapper.AdoProvider.Providers.IoriAdoProvider.Base;

namespace DotDataMapper.AdoProvider.Providers.IoriAdoProvider.DataSet
{
    public class IoriAdoProviderDataSet : IoriAdoProviderBase, IAdoProviderDataSet
    {
        public IoriAdoProviderDataSet(string connStr) : base(connStr) { }

        /// <summary>
        /// return a data set 
        /// </summary>
        /// <param name="procedureName">name of store procedure</param>
        /// <param name="spParam">parameters of store procedure </param>
        /// <returns>data set </returns>
        public System.Data.DataSet ExecuteDataSetSp(string procedureName,
            SqlParameter[] spParam = null)
        {
            var conn = OpenConnection();
            var ds = ExecuteDataSetSp(procedureName, conn, null, spParam);
            CloseCurrentConnection();
            return ds;
        }

        public System.Data.DataSet ExecuteDataSetSpNewTxn(string procedureName, SqlParameter[] spParam = null)
        {
            var conn = OpenConnection();
            var transaction = NewTransaction();
            var ret = ExecuteDataSetSp(procedureName, conn, transaction, spParam);
            transaction.Commit();
            CloseCurrentConnection();
            return ret;
        }

        public System.Data.DataSet ExecuteDataSetSp(string procedureName, SqlConnection conn, SqlParameter[] spParam = null)
        {
            return ExecuteDataSetSp(procedureName, conn, null, spParam);
        }

        /// <summary>
        /// Remember to close connection outside manually , and commit transaction if have 
        /// </summary>
        /// <param name="procedureName">the procedure name</param>
        /// <param name="conn">current connection to db</param>
        /// <param name="transaction">if have , pass transaction object</param>
        /// <param name="spParam">params for this procedure</param>
        /// <returns>data set</returns>
        public System.Data.DataSet ExecuteDataSetSp(string procedureName, SqlConnection conn, SqlTransaction transaction,
                                        SqlParameter[] spParam = null)
        {
            var ds = new System.Data.DataSet();
            try
            {
                using (var command = new SqlCommand(procedureName, conn) { CommandType = CommandType.StoredProcedure }
              )
                {
                    if (spParam != null)
                        command.Parameters.AddRange(spParam);

                    using (var oAdapter = new SqlDataAdapter { SelectCommand = command })
                    {
                        if (transaction != null)
                            oAdapter.SelectCommand.Transaction = transaction;

                        oAdapter.Fill(ds);
                    }
                }
                return ds;
            }
            catch
            {
                if (transaction != null)
                    transaction.Rollback();

                throw;
            }
        }
    }
}

//=======================================================================================

List

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;
using DotDataMapper.AdoProvider.Interface.List;
using DotDataMapper.CrossCut.Helper;
using DotDataMapper.AdoProvider.Providers.IoriAdoProvider.Base;

namespace DotDataMapper.AdoProvider.Providers.IoriAdoProvider.List
{
    public class IoriAdoProviderList : IoriAdoProviderBase, IAdoProviderList
    {

        #region constructor

        public IoriAdoProviderList(string connStr) : base(connStr) { }

        #endregion

        public IEnumerable<ExpandoObject> ExecuteListSp(string procedureName, SqlParameter[] spParam = null)
        {
            var conn = OpenConnection();
            var ret = ExecuteList(procedureName, conn, null, CommandType.StoredProcedure, spParam);
            CloseCurrentConnection();
            return ret;
        }

        public IEnumerable<ExpandoObject> ExecuteListSpNewTxn(string procedureName, SqlParameter[] spParam = null)
        {
            var conn = OpenConnection();
            var transaction = NewTransaction();
            var ret = ExecuteList(procedureName, conn, transaction, CommandType.StoredProcedure, spParam);
            transaction.Commit();
            CloseCurrentConnection();
            return ret;
        }

        public IEnumerable<ExpandoObject> ExecuteListSp(string procedureName, SqlConnection conn, SqlParameter[] spParam = null)
        {
            return ExecuteList(procedureName, conn, null, CommandType.StoredProcedure, spParam);
        }

        public IEnumerable<ExpandoObject> ExecuteListSp(string procedureName, SqlConnection conn, SqlTransaction transaction,
                                         SqlParameter[] spParam = null)
        {
            return ExecuteList(procedureName, conn, transaction, CommandType.StoredProcedure, spParam);
        }

        public IEnumerable<ExpandoObject> ExecuteListSql(string sql, SqlParameter[] parameters = null)
        {
            var conn = OpenConnection();
            var ret = ExecuteList(sql, conn, null, CommandType.Text, parameters);
            CloseCurrentConnection();
            return ret;
        }

        public IEnumerable<ExpandoObject> ExecuteListSqlNewTxn(string sql, SqlParameter[] parameters = null)
        {
            var conn = OpenConnection();
            var transaction = NewTransaction();
            var ret= ExecuteList(sql, conn, transaction, CommandType.Text, parameters);
            transaction.Commit();
            CloseCurrentConnection();
            return ret;
        }

        public IEnumerable<ExpandoObject> ExecuteListSql(string sql, SqlConnection conn, SqlParameter[] parameters = null)
        {
            return ExecuteList(sql, conn, null, CommandType.Text, parameters);
        }

        public IEnumerable<ExpandoObject> ExecuteListSql(string sql, SqlConnection conn, SqlTransaction transaction, SqlParameter[] parameters = null)
        {
            return ExecuteList(sql, conn, transaction, CommandType.Text, parameters);
        }

        private IEnumerable<ExpandoObject> ExecuteList(string commandText, SqlConnection conn, SqlTransaction transaction, CommandType type, SqlParameter[] parameters = null)
        {
            try
            {
                using (var command = new SqlCommand(commandText, conn) { CommandType = type })
                {
                    if (parameters != null)
                        command.Parameters.AddRange(parameters);

                    if (transaction != null)
                        command.Transaction = transaction;

                    var dataReader = command.ExecuteReader();

                    return MapperHelper.DataRowToExpandoList(dataReader);
                }
            }
            catch
            {
                if (transaction != null)
                    transaction.Rollback();

                throw;
            }
        }
    }
}

//======================================================================================

Reader

using System.Data;
using System.Data.SqlClient;
using DotDataMapper.AdoProvider.Interface.Reader;
using DotDataMapper.AdoProvider.Providers.IoriAdoProvider.Base;

namespace DotDataMapper.AdoProvider.Providers.IoriAdoProvider.Reader
{
    public class IoriAdoProviderReader : IoriAdoProviderBase, IAdoProviderReader
    {
        public IoriAdoProviderReader(string connStr) : base(connStr) { }


        public IDataReader ExecuteReaderSp(string procedureName, SqlConnection conn, SqlParameter[] spParam = null)
        {
            return ExecuteReader(procedureName, conn, null, CommandType.StoredProcedure, spParam);
        }

        public IDataReader ExecuteReaderSp(string procedureName, SqlConnection conn, SqlTransaction transaction,
                                           SqlParameter[] spParam = null)
        {
            return ExecuteReader(procedureName, conn, transaction, CommandType.StoredProcedure, spParam);
        }

        public IDataReader ExecuteReaderSql(string sql, SqlConnection conn, SqlParameter[] parameters = null)
        {
            return ExecuteReader(sql, conn, null, CommandType.Text, parameters);
        }

        public IDataReader ExecuteReaderSql(string sql, SqlConnection conn, SqlTransaction transaction,
                                            SqlParameter[] parameters = null)
        {
            return ExecuteReader(sql, conn, transaction, CommandType.Text, parameters);
        }

        public IDataReader ExecuteReader(string commandText, SqlConnection conn, SqlTransaction transaction,
            CommandType type, SqlParameter[] parameters = null)
        {
            try
            {
                using (var command = new SqlCommand(commandText, conn) { CommandType = type })
                {
                    if (transaction != null)
                        command.Transaction = transaction;
                    if (parameters != null)
                        command.Parameters.AddRange(parameters);

                    return command.ExecuteReader();
                }
            }
            catch
            {
                if (transaction != null)
                    transaction.Rollback();

                throw;
            }
        }


    }
}

//=======================================================================================

Non Querry

using System.Data;
using System.Data.SqlClient;
using DotDataMapper.AdoProvider.Interface.NonQuery;
using DotDataMapper.AdoProvider.Providers.IoriAdoProvider.Base;

namespace DotDataMapper.AdoProvider.Providers.IoriAdoProvider.NonQuery
{
    public class IoriAdoProviderNonQuery : IoriAdoProviderBase, IAdoProviderNonQuery
    {
        public IoriAdoProviderNonQuery(string connStr) : base(connStr) { }

        public int ExecuteNonQuerySp(string procedureName, SqlParameter[] parameters = null)
        {
            var conn = OpenConnection();
            var ret = ExecuteNonQuery(procedureName, conn, null, CommandType.StoredProcedure, parameters);
            CloseCurrentConnection();
            return ret;
        }

        public int ExecuteNonQuerySpNewTxn(string procedureName, SqlParameter[] parameters = null)
        {
            var conn = OpenConnection();
            var transaction = NewTransaction();
            var ret = ExecuteNonQuery(procedureName, conn, transaction, CommandType.StoredProcedure, parameters);
            transaction.Commit();
            CloseCurrentConnection();
            return ret;
        }

        public int ExecuteNonQuerySp(string procedureName, SqlConnection conn, SqlParameter[] parameters = null)
        {
            return ExecuteNonQuery(procedureName, conn, null, CommandType.StoredProcedure, parameters);
        }

        public int ExecuteNonQuerySp(string procedureName, SqlConnection conn, SqlTransaction transaction,
                                     SqlParameter[] parameters = null)
        {
            return ExecuteNonQuery(procedureName, conn, transaction, CommandType.StoredProcedure, parameters);
        }

        public int ExecuteNonQuerySql(string sql, SqlParameter[] parameters = null)
        {
            var conn = OpenConnection();
            var ret = ExecuteNonQuery(sql, conn, null, CommandType.Text, parameters);
            CloseCurrentConnection();
            return ret;
        }

        public int ExecuteNonQuerySqlNewTxn(string sql, SqlParameter[] parameters = null)
        {
            var conn = OpenConnection();
            var transaction = NewTransaction();
            var ret = ExecuteNonQuery(sql, conn, transaction, CommandType.Text, parameters);
            transaction.Commit();
            CloseCurrentConnection();
            return ret;
        }

        public int ExecuteNonQuerySql(string sql, SqlConnection conn, SqlParameter[] parameters = null)
        {
            return ExecuteNonQuery(sql, conn, null, CommandType.Text, parameters);
        }

        public int ExecuteNonQuerySql(string sql, SqlConnection conn, SqlTransaction transaction, SqlParameter[] parameters = null)
        {
            return ExecuteNonQuery(sql, conn, transaction, CommandType.Text, parameters);
        }

        private int ExecuteNonQuery(string commandText, SqlConnection conn, SqlTransaction transaction, CommandType type,
                                      SqlParameter[] parameters = null)
        {
            try
            {
                using (var oCommand = new SqlCommand(commandText, conn) { CommandType = type })
                {
                    if (transaction != null)
                        oCommand.Transaction = transaction;

                    if (parameters != null)
                        oCommand.Parameters.AddRange(parameters);

                    return oCommand.ExecuteNonQuery();
                }

            }
            catch
            {
                if (transaction != null)
                    transaction.Rollback();

                throw;
            }
        }
    }
}

//=======================================================================================

Single Obj

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Text;
using DotDataMapper.AdoProvider.Interface.SingleObj;
using DotDataMapper.CrossCut.Helper;
using DotDataMapper.AdoProvider.Providers.IoriAdoProvider.Base;

namespace DotDataMapper.AdoProvider.Providers.IoriAdoProvider.SingleObj
{
    public class IoriAdoProviderSingleObj : IoriAdoProviderBase, IAdoProviderSingleObj
    {
        public IoriAdoProviderSingleObj(string connStr) : base(connStr) { }


        public ExpandoObject ExecuteSingleObjSp(string procedureName, SqlParameter[] parameters = null)
        {
            var conn = OpenConnection();
            var ret = ExecuteSingleObj(procedureName, conn, null, CommandType.StoredProcedure, parameters);
            CloseCurrentConnection();
            return ret;
        }

        public ExpandoObject ExecuteSingleObjSpNew(string procedureName, SqlParameter[] parameters = null)
        {
            var conn = OpenConnection();
            var transaction = NewTransaction();
            var ret = ExecuteSingleObj(procedureName, conn, transaction, CommandType.StoredProcedure, parameters);
            transaction.Commit();
            CloseCurrentConnection();
            return ret;
        }

        public ExpandoObject ExecuteSingleObjSp(string procedureName, SqlConnection conn, SqlParameter[] parameters = null)
        {
            return ExecuteSingleObj(procedureName, conn, null, CommandType.StoredProcedure, parameters);
        }

        public ExpandoObject ExecuteSingleObjSp(string procedureName, SqlConnection conn, SqlTransaction transaction,
                                                SqlParameter[] parameters = null)
        {
            return ExecuteSingleObj(procedureName, conn, transaction, CommandType.StoredProcedure, parameters);
        }

        public ExpandoObject ExecuteSingleObjSql(string sql, SqlParameter[] parameters = null)
        {
            var conn = OpenConnection();
            var ret = ExecuteSingleObj(sql, conn, null, CommandType.Text, parameters);
            CloseCurrentConnection();
            return ret;
        }


        public ExpandoObject ExecuteSingleObjSqlNewTxn(string sql, SqlParameter[] parameters = null)
        {
            var conn = OpenConnection();
            var transaction = NewTransaction();
            var ret = ExecuteSingleObj(sql, conn, transaction, CommandType.Text, parameters);
            transaction.Commit();
            CloseCurrentConnection();
            return ret;
        }

        public ExpandoObject ExecuteSingleObjSql(string sql, SqlConnection conn, SqlParameter[] parameters = null)
        {
            return ExecuteSingleObj(sql, conn, null, CommandType.Text, parameters);
        }

        public ExpandoObject ExecuteSingleObjSql(string sql, SqlConnection conn, SqlTransaction transaction,
                                                 SqlParameter[] parameters = null)
        {
            return ExecuteSingleObj(sql, conn, transaction, CommandType.Text, parameters);
        }

        private ExpandoObject ExecuteSingleObj(string commandText, SqlConnection conn, SqlTransaction transaction,
                                               CommandType type, SqlParameter[] parameters = null)
        {
            try
            {
                using (var command = new SqlCommand(commandText, conn) { CommandType = type })
                {
                    if (parameters != null)
                        command.Parameters.AddRange(parameters);

                    if (transaction != null)
                        command.Transaction = transaction;

                    var dataReader = command.ExecuteReader();
                    return MapperHelper.DataRowToExpandoObj(dataReader);
                }

            }
            catch
            {
                if (transaction != null)
                    transaction.Rollback();

                throw;
            }
        }
    }
}

//=======================================================================================