Dapper.net的使用

最近项目比较忙，很久没有更新简书了，周末抽个时间，将最近项目中用到的一些框架进行整理。

Dapper.net是一个开源的ORM框架，使用方法非常简单，速度也很快。

使用方法很简单，如下，可以将查询出来的结果转换成List实体，很方便。
<pre>
List<ModelBase> list;
using (var conn = GetSqlConnection())
{
list = conn.Query<ModelBase>("select * from t").ToList();
}
</pre>

在项目的实际使用过程中，我对它进行了一些简单的封装，网上已经有很多的开源DapperNet的扩展，不过我还是自己实现了一个。有Query，UPdate，Add和Execute方法。
<pre>
public class DapperNetExt
{

    private string _SqlConnString;
    /// <summary>  
    /// 获取连接字符串  
    /// </summary>  
    /// <returns></returns>  
    protected IDbConnection GetSqlConnection()
    {

        if (string.IsNullOrEmpty(_SqlConnString))
        {
            _SqlConnString = ConfigurationManager.AppSettings["SqlConn"];
        }

        return new SqlConnection(_SqlConnString);
    }

    /// <summary>
    /// 查询
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="query"></param>
    /// <returns></returns>
    protected List<T> Query<T>(string query)
    {
        List<T> list;
        using (var conn = GetSqlConnection())
        {
            list = conn.Query<T>(query).ToList();
        }
        return list;
    }

    protected List<T> Query<T>(string query, DynamicParameters dp, CommandType commandtype)
    {
        List<T> list;
        using (var conn = GetSqlConnection())
        {
            list = conn.Query<T>(query, dp, null, true, null, commandtype).ToList();
        }
        return list;
    }

    protected DataSet Query(string query)
    {
        DataSet ds = new DataSet();
        using (var conn = GetSqlConnection())
        {
            SqlConnection sqlconn = conn as SqlConnection;
            SqlDataAdapter da = new SqlDataAdapter(query, sqlconn);
            da.Fill(ds);
        }
        return ds;
    }

    protected object ExecuteScalar(string query)
    {
        object obj;
        using (var conn = GetSqlConnection())
        {
            obj = conn.ExecuteScalar(query);
        }
        return obj;
    }

    /// <summary>
    /// 执行
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    protected int Execute(string sql)
    {
        int result = 0;
        using (var conn = GetSqlConnection())
        {
            result = conn.Execute(sql);
        }

        return result;
    }

    protected int Execute(string sql, object obj)
    {
        int result = 0;
        using (var conn = GetSqlConnection())
        {
            result = conn.Execute(sql, obj);
        }

        return result;
    }

    protected int Add<T>(T obj, string keyFiled = null)
    {
        return Add<T>(new List<T>() { obj }, keyFiled);
    }

    protected int Add<T>(List<T> obj, string keyFiled = null)
    {

        Type t = obj.FirstOrDefault().GetType();
        string tableName = t.Name;
        PropertyInfo[] ps = t.GetProperties(BindingFlags.Public | BindingFlags.Instance);

        string tmpsql = " insert into " + tableName;

        string tmpSqlPara = " ( ";
        string tmpSqlValue = " values ( ";

        foreach (var item in ps)
        {
            if (keyFiled != null)
            {
                if (item.Name == keyFiled)
                {
                    continue;
                }
            }
            tmpSqlPara += item.Name + ",";
            tmpSqlValue += "@" + item.Name + ",";
        }

        tmpSqlPara = tmpSqlPara.Substring(0, tmpSqlPara.Length - 1);
        tmpSqlPara += " ) ";

        tmpSqlValue = tmpSqlValue.Substring(0, tmpSqlValue.Length - 1);
        tmpSqlValue += " ) ";

        //if (keyFiled != null)
        //{
        tmpsql += tmpSqlPara;
        //}
        tmpsql += tmpSqlValue;

        return Execute(tmpsql, obj);
    }

    protected int Update<T>(T obj, string keyFiled)
    {
        return Update<T>(new List<T>() { obj }, keyFiled);
    }

    protected int Update<T>(List<T> obj, string keyFiled)
    {

        Type t = obj.FirstOrDefault().GetType();
        string tableName = t.Name;
        PropertyInfo[] ps = t.GetProperties(BindingFlags.Public | BindingFlags.Instance);

        string tmpsql = " update " + tableName;

        string tmpSqlPara = " set  ";
        string tmpSqlwhere = " where " + keyFiled + "=@" + keyFiled;

        foreach (var item in ps)
        {
            if (keyFiled != null)
            {
                if (item.Name == keyFiled)
                {
                    continue;
                }
            }
            tmpSqlPara += item.Name + "=@" + item.Name + ",";
        }

        tmpSqlPara = tmpSqlPara.Substring(0, tmpSqlPara.Length - 1);


        tmpsql += tmpSqlPara + tmpSqlwhere;

        return Execute(tmpsql, obj);
    }
}

</pre>

使用的时候，只需写一行代码就可以了。
<pre>
List<ModelBase> list = Query<ModelBase>("select * from t");
</pre>