Dapper老司机教学

    xiaoxiao2021-11-13  71

    1.新建一个类库:

    2.引用Nuget包  我使用的是   <package id="Dapper" version="1.50.2" targetFramework="net45" />   <package id="DapperExtensions" version="1.4.4" targetFramework="net45" />

    3.

    新建dbconfig数据库连接类:(也可以写到App.config中)

       private readonly string sqlconnection = "Data Source=.;Initial Catalog=FSHouseSpiderData;Integrated Security=True";

    这是数据库连接字符串

        public SqlConnection OpenConnection()         {             SqlConnection connection = new SqlConnection(sqlconnection);             connection.Open();             return connection;         }

    这个是数据库打开方法

    4.拷贝我的通用类CommonHelp.cs

        public class CommonHelp : dbConfig     {         #region comm         /// <summary>         /// 设置更新值Str         /// </summary>         /// <param name="updFiledValue"></param>         /// <returns></returns>         private string GetSetValuesStr(Dictionary<string, string> updFiledValue)         {             string retStr = string.Empty;             foreach (var item in updFiledValue)             {                 retStr = retStr + " [" + item.Key + "] =@" + item.Key + ",";             }             if (!String.IsNullOrEmpty(retStr))             {                 retStr = retStr.TrimEnd(',');             }             return retStr;         }         /// <summary>         /// 设置条件值Str(防注入啊)         /// </summary>         /// <param name="conditionFiledValue"></param>         /// <returns></returns>         private string GetSetConditionsStr(Dictionary<string, string> conditionFiledValue)         {             string retStr = string.Empty;             foreach (var item in conditionFiledValue)             {                 retStr = retStr + " [" + item.Key + "] =@" + item.Value + " and ";             }             if (!String.IsNullOrEmpty(retStr))             {                 retStr = retStr.Substring(0, retStr.LastIndexOf(" and "));             }             return retStr;         }         /// <summary>         /// 设置条件值Str防止注入         /// </summary>         /// <param name="conditionFiledValue"></param>         /// <returns></returns>         private DynamicParameters GetSetParam(Dictionary<string, string> conditionFiledValue)         {             var p = new DynamicParameters();             foreach (var item in conditionFiledValue)             {                 p.Add(item.Key, item.Key);             }             return p;         }         #endregion         #region 增         /// <summary>         /// 新增实体表         /// </summary>         /// <param name="obj"></param>         /// <returns></returns>         public long AddNewEntity<T>(object obj) where T : class,new()         {                        long id = 0;             try             {                 using (IDbConnection conn = OpenConnection())                 {                     id = conn.Insert<T>((T)obj);                 }             }             catch (Exception ex)             {             }             return id;         }         /// <summary>         /// 新增实体表         /// </summary>         /// <param name="obj"></param>         /// <returns></returns>         public string AddNewEntityString<T>(object obj) where T : class,new()         {             string id = "";             try             {                 using (IDbConnection conn = OpenConnection())                 {                     id = conn.Insert<T>((T)obj);                 }             }             catch (Exception ex)             {             }             return id;         }         #endregion         #region 删         /// <summary>         /// 通过id删除某一条信息         /// </summary>         /// <param name="tableName"></param>         /// <param name="id"></param>         /// <returns></returns>         public bool DeleteInfoById(string tableName, long id)         {             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string sqlStr = @"DELETE FROM " + tableName + " WHERE Id=@id";                     int n = conn.Execute(sqlStr, new { id = id });                     return n > 0;                     //使用事物                     //IDbTransaction transaction = conn.BeginTransaction();                     //int row = conn.Execute(sqlStr, new { id = id }, transaction, null, null);                     //row += conn.Execute(sqlStr, new { id = id }, transaction, null, null);                     //transaction.Commit();                 }             }             catch (Exception ex)             {                 //log                 return false;             }         }         /// <summary>         /// 通过某个属性删除某一条信息         /// </summary>         /// <param name="tableName"></param>         /// <param name="field"></param>         /// <param name="values"></param>         /// <returns></returns>         public bool DeleteInfoByConditionField(string tableName, string field, string values)         {             bool flag = true;             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string sqlStr = @"DELETE " + tableName + " WHERE " + field + "=@values";                     int n = conn.Execute(sqlStr, new { values = values });                     return n > 0;                 }             }             catch (Exception ex)             {                 flag = false;             }             return flag;         }         /// <summary>         /// 根据多个条件删除         /// </summary>         /// <param name="tableName"></param>         /// <param name="conditionFiledValue"></param>         /// <returns></returns>         public bool DeleteInfoByConditionFields(string tableName, Dictionary<string, string> conditionFiledValue)         {             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string strSetConditions = this.GetSetConditionsStr(conditionFiledValue);                     string sqlStr = @"DELETE FROM [" + tableName + "] where " + strSetConditions;                     int n = conn.Execute(sqlStr, this.GetSetParam(conditionFiledValue));                     return n > 0;                 }             }             catch (Exception ex)             {                 return false;             }         }         /// <summary>         /// 根据sql删除         /// </summary>         /// <param name="tableName"></param>         /// <param name="sql"></param>         /// <returns></returns>         public bool DeleteTableBySql(string tableName, string sql)         {             bool bl = true;             try             {                 using (IDbConnection conn = OpenConnection())                 {                     conn.Execute(sql);                     conn.Close();                 }             }             catch (Exception ex)             {                 bl = false;             }             return bl;         }         #endregion         #region 改         /// <summary>         /// 更新实体表         /// </summary>         /// <param name="obj"></param>         /// <returns></returns>         public bool UpdateEntity(object obj)         {             bool flag = false;             try             {                 using (IDbConnection conn = OpenConnection())                 {                                  }             }             catch (Exception ex)             {                 flag = false;             }             return flag;         }         /// <summary>         /// 更新某个字段         /// </summary>         /// <param name="tableName">表名称</param>         /// <param name="field">字段</param>         /// <param name="values">字段值</param>         /// <returns></returns>         public bool UpdateInfoOneAttribute(string tableName, string field, string values, string conditionFied, string conditionValues)         {             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string sqlStr = @"update " + tableName + " set " + field + "=" + values + " where " + conditionFied + "=" + conditionValues;                     bool result = conn.Update(sqlStr);                     conn.Close();                     return result;                 }             }             catch (Exception ex)             {                 return false;             }         }         /// <summary>         /// 根据多个条件,更新多个值         /// </summary>         /// <param name="tableName"></param>         /// <param name="updFiledValue"></param>         /// <param name="conditionFiledValue"></param>         /// <returns></returns>         public bool UpdateInfoByAttributes(string tableName, Dictionary<string, string> updFiledValue, Dictionary<string, string> conditionFiledValue)         {             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string strSetValues = this.GetSetValuesStr(updFiledValue);                     string strSetConditions = this.GetSetConditionsStr(conditionFiledValue);                     string sqlStr = @"update [" + tableName + "] set " + strSetValues + " where " + strSetConditions;                     foreach (var item in updFiledValue)                     {                         conditionFiledValue.Add(item.Key, item.Value);                     }                     int result = conn.Execute(sqlStr, GetSetParam(conditionFiledValue));                     conn.Close();                     return result > 0;                 }             }             catch (Exception ex)             {                 return false;             }         }         /// <summary>         /// 根据sql更新         /// </summary>         /// <param name="tableName"></param>         /// <param name="sql"></param>         /// <returns></returns>         public bool UpDateTableBySql(string tableName, string sql)         {             bool bl = true;             try             {                 using (IDbConnection conn = OpenConnection())                 {                     conn.Execute(sql);                     conn.Close();                 }             }             catch (Exception ex)             {                 bl = false;             }             return bl;         }         #endregion         #region 查         /// <summary>         /// 通过id获得某个表的所有信息         /// </summary>         /// <param name="tableName"></param>         /// <param name="id"></param>         /// <returns></returns>         public IList<T> GetEntytyInfoById<T>(string tableName, long id) where T : new()         {             IList<T> comList = new List<T>();             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string sql = @"select * from [" + tableName + "]";                     if (id > 0)                     {                         sql = @"select * from [" + tableName + "]   where Id=@id";                     }                     if (id > 0)                     {                         comList = conn.Query<T>(sql, new { id = id }).ToList();                     }                     else                     {                         comList = conn.Query<T>(sql).ToList();                     }                     conn.Close();                 }             }             catch (Exception ex)             {                 //log             }             return comList;         }         /// <summary>         /// 通过某个字段获得表的所有信息         /// </summary>         /// <param name="tableName"></param>         /// <param name="id"></param>         /// <returns></returns>         public IList<T> GetEntytyInfoByQuery<T>(string tableName, string field, string values) where T : new()         {             IList<T> comList = new List<T>();             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string sql = @"select * from [" + tableName + "]   where [" + field + "] =@field order by " + field + " desc";                     comList = conn.Query<T>(sql, new { field = values }).ToList();                     conn.Close();                 }             }             catch (Exception ex)             {                 //log             }             return comList;         }         /// <summary>         /// 根据Id 获得实体         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="Id"></param>         /// <returns></returns>         public T GetEntityById<T>(string tableName, long Id) where T : new()         {             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string sql = @"select * from [" + tableName + "]   where Id =@id order by Id desc";                     T p = conn.Query<T>(sql, new { id = Id }).SingleOrDefault();                     conn.Close();                     return p;                 }             }             catch (Exception ex)             {                 return new T();             }         }         /// <summary>         /// 根据Id查询实体类         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="tableName"></param>         /// <param name="Id"></param>         /// <returns></returns>         public T GetEntityByConditionField<T>(string tableName, string field, string values) where T : new()         {             IList<T> comList = new List<T>();             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string sql = @"select * from [" + tableName + "]   where " + field + "=@field";                     T p = conn.Query<T>(sql, new { field = field }).SingleOrDefault();                     conn.Close();                     return p;                 }             }             catch (Exception ex)             {                 //             }             if (comList.Count > 0)             {                 return comList[0];             }             else             {                 return new T();             }         }         /// <summary>         /// 根据sql 查询         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="sql"></param>         /// <returns></returns>         public IList<T> GetEntytyInfoBySQL<T>(string sql) where T : new()         {             IList<T> comList = new List<T>();             try             {                 using (IDbConnection conn = OpenConnection())                 {                     comList = conn.Query<T>(sql).ToList();                     conn.Close();                 }             }             catch (Exception ex)             {                 //log             }             return comList;         }         /// <summary>         /// 获得实体类的单个值         /// </summary>         /// <param name="tableName">表名称</param>         /// <param name="selectValue">要查询的值</param>         /// <param name="field">条件字段</param>         /// <param name="values">条件值</param>         /// <returns></returns>         public string GetEntyOneValue(string tableName, string selectValue, string field, string values)         {             string str = string.Empty;             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string sql = @"select " + selectValue + " cs from [" + tableName + "]   where " + field + "=@field";                     str = conn.QueryFirst(sql, new { field = values });                     conn.Close();                 }             }             catch (Exception ex)             {                 //log             }             return str;         }         /// <summary>         /// 查询表中数据         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="tableName"></param>         /// <param name="orderby"></param>         /// <param name="desc"></param>         /// <returns></returns>         public IList<T> GetEntityInfoList<T>(string tableName)         {             return this.GetEntityInfoList<T>(tableName, string.Empty, true);         }         /// <summary>         /// 查询表中数据         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="tableName"></param>         /// <param name="orderby"></param>         /// <param name="desc"></param>         /// <returns></returns>         public IList<T> GetEntityInfoList<T>(string tableName, string orderby, bool desc)         {             IList<T> comList = new List<T>();             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string sql = @"select * from [" + tableName + "]";                     if (!String.IsNullOrEmpty(orderby))                     {                         sql = @" select * from [" + tableName + "]   Order by [" + orderby + "] ";                         if (desc == true)                         {                             sql = sql + " Desc";                         }                     }                     comList = conn.Query<T>(sql).ToList();                     conn.Close();                 }             }             catch (Exception ex)             {                 //log             }             return comList;         }         /// <summary>         /// 根据字段按照In查询         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="tableName"></param>         /// <param name="filed"></param>         /// <param name="inValues"></param>         /// <returns></returns>         public IList<T> GetEntityInfoByFiledIn<T>(string tableName, string filed, string inValues) where T : new()         {             IList<T> comList = new List<T>();             try             {                 using (IDbConnection conn = OpenConnection())                 {                     string sql = @"select * from [" + tableName + "]   where [" + filed + "] in (" + inValues + ") order by id desc";                     comList = conn.Query<T>(sql).ToList();                     conn.Close();                 }             }             catch (Exception ex)             {                 //log             }             return comList;         }         /// <summary>         /// 获得实体类的单个值--sql中必须将要查到的值重命名为cs         /// </summary>         /// <returns></returns>         public string GetEntyOneValue(string sql, string tableName)         {             string str = string.Empty;             try             {                 using (IDbConnection conn = OpenConnection())                 {                     str = conn.QueryFirst(sql);                     conn.Close();                 }             }             catch (Exception ex)             {                 //log             }             return str;         }         #endregion     }

    转载请注明原文地址: https://ju.6miu.com/read-678240.html

    最新回复(0)