利用委托简化的数据库访问类

最新推荐文章于 2019-02-02 19:40:19 发布

weixin_34321753

最新推荐文章于 2019-02-02 19:40:19 发布

阅读量109

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/raddleoj/blog/757742

版权

2019独角兽企业重金招聘Python工程师标准>>>

好像是1年前写的

//这是接口
namespace PowerDBHelper
{
    public interface IPowerDBHelper
    {
        void AddParameter(DbParameterCollection comPara, string paramName, object paramValue);
        DataShell<int> ExecuteSql(string sqlStr, Action<DbParameterCollection> parameterMaker);
        DataShell<T> SingleDataReader<T>(string sqlStr, Action<DbParameterCollection> parameterMaker, Func<DbDataReader, T> dataConverter);
        DataShell<T> ExecuteScalar<T>(string sqlStr, Action<DbParameterCollection> parameterMaker, Func<object, T> dataConverter);
        DataShell<List<T>> ListDataReader<T>(string sqlStr, Action<DbParameterCollection> parameterMaker, Func<DbDataReader, T> dataConverter);
        DataShell<T> ExecuteStoredProcedure<T>(string storedProcedureName, Action<DbParameterCollection> parameterMaker, Func<DbParameterCollection, T> dataConverter);
    }
}

然后就是数据helper了

namespace PowerDBHelper
{
    public abstract class PowerDBHelper
    {
        public PowerDBHelper(string connStr, int comTimeout, Func<string, DbConnection> connectionMaker)
        {
            ConnStr = connStr;
            CommTimeOut = comTimeout;
        }
        public Func<string, DbConnection> ConnectionMaker { get; protected set; }
        public string ConnStr
        {
            get; protected set;
        }
        public int CommTimeOut
        {
            get; protected set;
        }

        #region --基本数据库方法，委托版本--
        private void DefaultCommandType(DbCommand com)
        {
            com.CommandType = CommandType.Text;
        }
        /// <summary>
        /// 核心执行sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlStr"></param>
        /// <param name="parameterMaker"></param>
        /// <param name="executeCommand"></param>
        /// <returns></returns>
        private DataShell<T> CoreExecute<T>(string sqlStr, Action<DbParameterCollection> parameterMaker, Func<DbCommand, T> executeCommand, Action<DbCommand> setCommandType = null
        )
        {
            if (ConnectionMaker == null)
                throw new Exception("no connection");
            DataShell<T> result = DataShell<T>.CreateFail(default(T));
            setCommandType = setCommandType == null ? DefaultCommandType : setCommandType;
            string sql = sqlStr;
            try
            {
                if(string.IsNullOrWhiteSpace(ConnStr))
                    throw new Exception("no connection string");
                using (DbConnection scon = ConnectionMaker(ConnStr))
                {
                    using (DbCommand scom = scon.CreateCommand())
                    {
                        scom.CommandText = sql;
                        setCommandType(scom);
                        scom.CommandTimeout = CommTimeOut;
                        scom.Parameters.Clear();
                        if (parameterMaker != null)
                            parameterMaker(scom.Parameters);
                        scon.Open();
                        if (executeCommand != null)
                        {
                            result.Data = executeCommand(scom);
                            result.Success = true;
                        }
                        scon.Close();
                        scom.Dispose();
                    }
                    scon.Dispose();
                }
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Msg = ex.Message;
                result.CurrentException = ex;
            }
            finally
            {
                
            }
            return result;
        }
        /// <summary>
        /// 执行一般sql语句
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="parameterMaker">参数变量委托</param>
        /// <returns>返回执行影响条数</returns>
        public DataShell<int> ExecuteSql(string sqlStr, Action<DbParameterCollection> parameterMaker)
        {
            return CoreExecute<int>(sqlStr, parameterMaker, (com) => { return com.ExecuteNonQuery(); });
        }
        /// <summary>
        /// 执行单个数据对象提取
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlStr"></param>
        /// <param name="parameterMaker">参数变量委托</param>
        /// <param name="dataConverter"></param>
        /// <returns></returns>
        public DataShell<T> SingleDataReader<T>(string sqlStr, Action<DbParameterCollection> parameterMaker, Func<DbDataReader, T> dataConverter)
        {
            return CoreExecute<T>(sqlStr, parameterMaker, (com) => {
                var data_reader = com.ExecuteReader(CommandBehavior.CloseConnection);
                T t = default(T);
                if (data_reader.Read())
                {
                    if (dataConverter != null)
                        t = dataConverter(data_reader);
                }
                data_reader.Close();
                return t;
            });
        }
        /// <summary>
        /// 执行首行首字段内容提取
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlStr"></param>
        /// <param name="parameterMaker">参数变量委托</param>
        /// <param name="dataConverter"></param>
        /// <returns></returns>
        public DataShell<T> ExecuteScalar<T>(string sqlStr, Action<DbParameterCollection> parameterMaker, Func<object, T> dataConverter)
        {
            return CoreExecute<T>(sqlStr, parameterMaker, (com) => {
                T t = default(T);
                if (dataConverter != null)
                    t = dataConverter(com.ExecuteScalar());
                return t;
            });
        }
        /// <summary>
        /// 执行列表对象提取
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlStr"></param>
        /// <param name="parameterMaker">参数变量委托</param>
        /// <param name="dataConverter"></param>
        /// <returns></returns>
        public DataShell<List<T>> ListDataReader<T>(string sqlStr, Action<DbParameterCollection> parameterMaker, Func<DbDataReader, T> dataConverter)
        {
            return CoreExecute<List<T>>(sqlStr, parameterMaker, (com) => {
                List<T> itemList = new List<T>();
                var data_reader = com.ExecuteReader(CommandBehavior.CloseConnection);
                while (data_reader.Read())
                {
                    T t = default(T);
                    if (dataConverter != null)
                    {
                        t = dataConverter(data_reader);
                        itemList.Add(t);
                    }
                }
                data_reader.Close();
                return itemList;
            });
        }

        public DataShell<T> ExecuteStoredProcedure<T>(string storedProcedureName, Action<DbParameterCollection> parameterMaker, Func<DbParameterCollection, T> dataConverter)
        {
            return CoreExecute<T>(storedProcedureName, parameterMaker, (com) => {
                com.ExecuteNonQuery();
                return dataConverter(com.Parameters);
            }, (com)=> { com.CommandType = CommandType.StoredProcedure; });
        }

        public DataShell<T> ReturnOrThrowException<T>(DataShell<T> result)
        {
            if (result.Success == true)
                return result;
            else if (result.CurrentException != null)
                throw result.CurrentException;
            else
                throw new Exception(result.Msg);
        }
        #endregion
    }
}

用法：

namespace PowerDBHelper
{
    public class OraclePowerDBHelper : PowerDBHelper, IPowerDBHelper
    {
        public OraclePowerDBHelper(string connStr, int comTimeOut) : base(connStr, comTimeOut, null)
        {
            ConnectionMaker = (connstr) => { return new OracleConnection(connstr); };
        }

        public void AddParameter(DbParameterCollection comPara, string paramName, object paramValue)
        {
            comPara.Add(new OracleParameter(paramName, paramValue));
        }
    }
}

namespace PowerDBHelper
{
    public class MSSQLPowerDBHelper : PowerDBHelper, IPowerDBHelper
    {
        public MSSQLPowerDBHelper(string connStr, int comTimeOut) : base(connStr, comTimeOut, null)
        {
            ConnectionMaker = (connstr) => { return new SqlConnection(connstr); };
        }

        public void AddParameter(DbParameterCollection comPara, string paramName, object paramValue)
        {
            comPara.Add(new SqlParameter(paramName, paramValue));
        }
        

    }
}

所用数据外壳

namespace PowerDBHelper
{
    public class DataShell<T>
    {
        public DataShell()
        {
            CurrentTime = DateTime.Now;
        }
        public static DataShell<T> CreateSuccess(T data)
        {
            return Create(true, string.Empty, data);
        }
        public static DataShell<T> CreateFail(T data)
        {
            return Create(false, string.Empty, data);
        }
        public static DataShell<T> Create(bool success, string msg, T data)
        {
            return new DataShell<T>() { Success = success, Msg = msg, Data = data };
        }
        public bool Success { get; set; }
        public string Msg { get; set; }
        public DateTime CurrentTime { get; set; }
        public Exception CurrentException { get; set; }
        public T Data { get; set; }
    }
}

实例

string connstr = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
string sql = "insert into user_login_fail_log(user_phs,user_ip,log_text) values(@user_phs, @user_ip, @log_text)";
PowerDBHelper.MSSQLPowerDBHelper msdb = new PowerDBHelper.MSSQLPowerDBHelper(connstr, 30);
var result = msdb.ExecuteSql(sql, (comp) => { msdb.AddParameter(comp, "@user_phs", phs); msdb.AddParameter(comp, "@user_ip", ip); msdb.AddParameter(comp, "@log_text", log_text); });
if (result.Success == false)
{
    throw result.CurrentException;
}

注释不多！

娱乐娱乐！

转载于:https://my.oschina.net/raddleoj/blog/757742