Dapper.net 的简单封装

Dapper.net 的简单应用

配置操作

---

Web.config配置

<appSettings>
    //注：本人使用mysql
    <add key="strconn" value="server=localhost;user id=***;password=***;database=数据库;Allow User Variables=True" />
</appSettings>

---

语法操作

基础语法封装

using Dapper;
using MyModel;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Linq.Expressions;

namespace MyDAL
{
    /// <summary>
    /// 数据操作类
    /// </summary>
    public class SqlConfig
    {
        //获取连接字符串
        public readonly static  string sqlconnct = System.Configuration.ConfigurationManager.AppSettings["strconn"];
        //初始化连接对象
        public MySqlConnection conn = null;
        public SqlConfig()
        {
            conn = new MySqlConnection(sqlconnct);
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private void OpenConnect()
        {
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private void CloseConnect()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 查询语句，必要形参sql语句或存储过程名称，后面参数用于扩展可以不写，若两边有参数中间用null占位
        /// </summary>
        /// <typeparam name="T">强类型的类</typeparam>
        /// <param name="sql">sql执行语句或存储过程名称</param>
        /// <param name="parameter">sql参数，可匿名类型，可对象类型</param>
        /// <param name="transaction">执行事务</param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns>对象集合</returns>
        public IEnumerable<T> GetInfoList<T>(string sql, object parameter = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            try
            {
                OpenConnect();
                //可以让结果转换成其他集合形式 例：list、array等集合，方法： ToList<>、ToArray<>
                IEnumerable<T> result = conn.Query<T>(sql, parameter, transaction, buffered, commandTimeout, commandType);
                CloseConnect();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 插入、更新或删除语句，必要形参sql语句或存储过程名称，后面参数用于扩展可以不写，若两边有参数中间用null占位
        /// </summary>
        /// <typeparam name="T">强类型的类</typeparam>
        /// <param name="sql">sql执行语句或存储过程名称</param>
        /// <param name="parameter">sql参数，可匿名类型，可对象类型</param>
        /// <param name="transaction">执行事务</param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns>成功：true;失败：false</returns>
        public bool UpdateSql(string sql, object parameter = null, IDbTransaction transaction = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            try
            {
                OpenConnect();
                int result = conn.Execute(sql, parameter, transaction, commandTimeout, commandType);
                CloseConnect();
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据条件获取数据库表中列表数量,必要形参sql,后面参数用于扩展可以不写，若两边有参数中间用null占位
        /// </summary>
        /// <param name="sql">sql执行语句或存储过程名称</param>
        /// <param name="parameter">sql参数，可匿名类型，可对象类型</param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public int GetInfoCounts(string sql, object parameter = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            try
            {
                OpenConnect();
                //注意：sql语句应该是这种形式 select count(*) as rows from table
                int result = conn.Query<int>(sql, parameter, transaction, buffered, commandTimeout, commandType).First() ;
                CloseConnect();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

---

示例操作

查询结果示例

//转化成lsit集合形式
//T 强类型类
string sqlList="select * from table";
List<T> list = SqlConfig对象实例.GetInfoList(sqlList)<T>.ToList();

查询数量示例

//查询数量
string sqlCount = string.Format("select count(*) as rows from {0} ", table)
int counts = SqlConfig对象实例.GetInfoCounts(sqlCount);

插入更新删除

//插入 
//注意 user是强类型类 包含下面带@的字段属性，名称一样会进行自动填充
//参数也可以是匿名类型new {Name=.,Password=.,.....}会自动填充
string sql = "insert into userinfo(Name,Password,Phone,Address,Status) values(@Name,@Password,@Phone,@Address,@Status)";
bool result = SqlConfig对象实例.UpdateSql(sql,user);
//更新
string sql = "update  userinfo set name=@Name,password=@Password,phone=@Phone,address=@Address,status=@Status where id=@ID";
bool result = SqlConfig对象实例.UpdateSql(sql,user);
//删除
string sql = string.Format("delete  from userinfo where id in ({0})",ids);
bool result = SqlConfig对象实例.UpdateSql(sql);

下一章介绍：

自己构建的项目框架使用的 easyui+.net mvc +Autofac+dapper.net 的分页操作