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 }