Dapper 基本使用
来源：互联网 发布：昆明行知中学校花 编辑：程序博客网 时间：2024/06/02 05:05
项目右键 Manage NuGet Packages for Solution
搜索Dapper -> Install
using System;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;namespace Dapper.Repository{ public class DapperDemo { public static string ConnectionString { get { string _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(); return _connectionString; } } public SqlConnection OpenConnection() { SqlConnection connection = new SqlConnection(ConnectionString); connection.Open(); return connection; } /// <summary> /// 添加 /// </summary> /// <returns></returns> public bool Add() { int row = 0; ED_Data model = new ED_Data(); model.TableName = "123"; model.DataKey = "123"; model.FieldName = "123"; model.Value = "123"; model.Reference = "123"; model.Branch = 1; model.InActive = false; model.Updated = DateTime.Now; string query = "INSERT INTO ED_Data(TableName,DataKey,FieldName,Value,Reference,Branch,InActive,Updated) VALUES (@TableName,@DataKey,@FieldName,@Value,@Reference,@Branch,@InActive,@Updated)"; using (IDbConnection conn = OpenConnection()) { row = conn.Execute(query, model); } if (row > 0) return true; else return false; } /// <summary> /// 修改 /// </summary> /// <returns></returns> public int Update() { int row = 0; ED_Data model = new ED_Data(); model.TableName = "123"; model.DataKey = "123"; model.Updated = DateTime.Now; using (IDbConnection conn = OpenConnection()) { const string query = "UPDATE ED_Data SET DataKey=@DataKey,Updated=@Updated WHERE TableName=@TableName"; row = conn.Execute(query, model); } return row; } /// <summary> /// 删除 /// </summary> /// <returns></returns> public int Delete() { int row = 0; ED_Data model = new ED_Data(); model.TableName = "123"; using (IDbConnection conn = OpenConnection()) { const string query = "DELETE FROM ED_Data WHERE TableName=@TableName"; row = conn.Execute(query, model); } return row; } /// <summary> /// 查询一条数据 /// </summary> /// <param name="columnCatId"></param> /// <returns>ED_Data</returns> public ED_Data GetModel(string TableName) { using (IDbConnection conn = OpenConnection()) { const string query = "SELECT * FROM ED_Data WHERE TableName = @TableName"; return conn.Query<ED_Data>(query, new { tableName = TableName }).SingleOrDefault<ED_Data>(); } } /// <summary> /// 查询list集合 /// </summary> /// <returns>List</returns> public List<ED_Data> GetED_DataList() { using (IDbConnection conn = OpenConnection()) { const string query = "SELECT * FROM ED_Data"; return conn.Query<ED_Data>(query, null).ToList(); } } /// <summary> /// 事务处理 /// 删除 /// </summary> /// <param name="cat"></param> /// <returns></returns> public int DeleteColumnCatAndColumn(ED_Data cat) { try { using (IDbConnection conn = OpenConnection()) { string delete1 = "DELETE FROM ED_Data WHERE TableName=@TableName"; string delete2 = "DELETE FROM ED_Data WHERE TableName=@TableName"; IDbTransaction transaction = conn.BeginTransaction(); int row = conn.Execute(delete1, new { TableName = cat.TableName }, transaction, null, null); row += conn.Execute(delete2, new { TableName = cat.TableName }, transaction, null, null); transaction.Commit(); return row; } } catch (Exception) { throw; } } /// <summary> /// 执行存储过程 /// </summary> public void ExecuteStoredProcedure() { try { DynamicParameters para = new DynamicParameters(); para.Add("@param1", 1); para.Add("@param2", 2); using (IDbConnection conn = OpenConnection()) { int row = conn.Execute("存储过程名称", para, null, null, CommandType.StoredProcedure); } } catch (Exception) { throw; } } /// <summary> /// 批量添加 /// </summary> public void InsertBatch() { try { string sqlStr = "INSERT INTO ED_Data(TableName,DataKey,FieldName,Value,Reference,Branch,InActive,Updated) VALUES (@TableName,@DataKey,@FieldName,@Value,@Reference,@Branch,@InActive,@Updated)"; using (IDbConnection conn = OpenConnection()) { conn.Execute(sqlStr, new[] { new { TableName = "user1", DataKey = "a", FieldName = "name", Value = "000001", Reference = "", Branch = "", InActive = "", Updated = DateTime.Now }, new { TableName = "user2", DataKey = "b", FieldName = "age", Value = "000002", Reference = "", Branch= "", InActive = "", Updated = DateTime.Now }, new { TableName = "user3", DataKey = "c", FieldName = "phone", Value = "000003", Reference= "", Branch= "", InActive= "", Updated= DateTime.Now }, }, null, null, null); } } catch (Exception) { throw; } } }}using System;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;namespace Dapper.Repository{ public class ED_Data { public string TableName { get; set; } public string DataKey { get; set; } public string FieldName { get; set; } public string Value { get; set; } public string Reference { get; set; } public int Branch { get; set; } public bool InActive { get; set; } public DateTime Updated { get; set; } }}Base基类
using Dapper.CoreLibrary;using Dapper.Entity;using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Reflection;using System.Text;using System.Transactions;namespace Dapper.Repository{ public abstract class RepositoryBase<T> : DbConnectionFactory, IRepositoryBase<T> where T : IEntityBase<T> { public RepositoryBase(IDbConnection db) : base(db) { } /// <summary> /// /// </summary> /// <param name="model"></param> /// <returns></returns> public virtual int Add(T model) { int result = 0; try { var ps = model.GetType().GetProperties(); List<string> @colms = new List<string>(); List<string> @params = new List<string>(); foreach (var p in ps) { if (!p.CustomAttributes.Any(x => x.AttributeType == typeof(PrimaryKeyAttribute)) && !p.CustomAttributes.Any(x => x.AttributeType == typeof(DBIgnoreAttribute))) { @colms.Add(string.Format("[{0}]", p.Name)); @params.Add(string.Format("@{0}", p.Name)); } } var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2}); SELECT @@IDENTITY;", typeof(T).Name, string.Join(", ", @colms), string.Join(", ", @params)); result = _conn.ExecuteScalar<int>(sql, model); } catch (Exception ex) { throw; } return result; } /// <summary> /// /// </summary> /// <param name="listModel"></param> public virtual void Add(List<T> listModel) { try { using (var scope = new TransactionScope()) { listModel.ForEach(model => { var ps = model.GetType().GetProperties(); List<string> @colms = new List<string>(); List<string> @params = new List<string>(); foreach (var p in ps) { if (!p.CustomAttributes.Any(x => x.AttributeType == typeof(PrimaryKeyAttribute)) && !p.CustomAttributes.Any(x => x.AttributeType == typeof(DBIgnoreAttribute))) { @colms.Add(string.Format("[{0}]", p.Name)); @params.Add(string.Format("@{0}", p.Name)); } } var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2}); SELECT @@IDENTITY;", typeof(T).Name, string.Join(", ", @colms), string.Join(", ", @params)); _conn.ExecuteScalar<int>(sql, model); }); scope.Complete(); } } catch (Exception ex) { } } /// <summary> /// /// </summary> /// <param name="model"></param> /// <returns></returns> public virtual int AddWithGuid(T model) { int result = 0; try { var ps = model.GetType().GetProperties(); List<string> @colms = new List<string>(); List<string> @params = new List<string>(); foreach (var p in ps) { if (!p.CustomAttributes.Any(x => x.AttributeType == typeof(DBIgnoreAttribute))) { @colms.Add(string.Format("[{0}]", p.Name)); @params.Add(string.Format("@{0}", p.Name)); } } var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2});", typeof(T).Name, string.Join(", ", @colms), string.Join(", ", @params)); result = _conn.Execute(sql, model); } catch (Exception ex) { throw; } return result; } /// <summary> /// /// </summary> /// <param name="model"></param> public virtual void Update(T model) { PropertyInfo pkInfo = null; var ps = model.GetType().GetProperties(); List<string> @params = new List<string>(); foreach (var p in ps) { if (p.CustomAttributes.Any(x => x.AttributeType == typeof(DBIgnoreAttribute))) { continue; } if (p.CustomAttributes.Any(x => x.AttributeType == typeof(PrimaryKeyAttribute))) { pkInfo = p; } else { @params.Add(string.Format("[{0}]=@{0}", p.Name)); } } var sql = string.Format("UPDATE [{0}] SET {1} WHERE [{2}] = @{2}", typeof(T).Name, string.Join(", ", @params), pkInfo.Name); _conn.Execute(sql, model); } /// <summary> /// /// </summary> /// <param name="listModel"></param> public virtual void Update(List<T> listModel) { using (var scope = new TransactionScope()) { listModel.ForEach(model => { PropertyInfo pkInfo = null; var ps = model.GetType().GetProperties(); List<string> @params = new List<string>(); foreach (var p in ps) { if (p.CustomAttributes.Any(x => x.AttributeType == typeof(DBIgnoreAttribute))) { continue; } if (p.CustomAttributes.Any(x => x.AttributeType == typeof(PrimaryKeyAttribute))) { pkInfo = p; } else { @params.Add(string.Format("[{0}] = @{0}", p.Name)); } } var sql = string.Format("UPDATE [{0}] SET {1} WHERE [{2}] = @{2}", typeof(T).Name, string.Join(", ", @params), pkInfo.Name); _conn.Execute(sql, model); }); scope.Complete(); } } /// <summary> /// /// </summary> /// <param name="primaryValue">主键ID</param> /// <param name="tableName">表名</param> /// <returns></returns> public virtual T GetModel(string primaryValue, string tableName = "") { try { string primaryWhere = string.Empty; var ps = typeof(T).GetProperties(); if (string.IsNullOrEmpty(tableName)) { tableName = typeof(T).Name; } var primary = ps.Single(p => p.CustomAttributes.FirstOrDefault(c => c.AttributeType == typeof(PrimaryKeyAttribute)) != null); primaryWhere = (string.Format("[{0}] = @primarykey", primary.Name)); var sql = string.Format("SELECT * FROM [{0}] WHERE {1}", tableName, primaryWhere); return _conn.Query<T>(sql, new { primarykey = primaryValue }).FirstOrDefault(); } catch (Exception) { throw; } } /// <summary> /// /// </summary> /// <param name="strWhere">where条件</param> /// <param name="tableName">表名</param> /// <returns></returns> public virtual T GetModelQuery(string strWhere, string tableName = "") { try { var sql = string.Format("SELECT * FROM [{0}] WHERE {1}", tableName, strWhere); return _conn.Query<T>(sql, new { where = strWhere }).FirstOrDefault(); } catch (Exception) { throw; } } /// <summary> /// 根据主键删除 /// </summary> /// <param name="primaryValue"></param> public virtual void Delete(string primaryValue) { try { string primaryWhere = string.Empty; var ps = typeof(T).GetProperties(); var primary = ps.Single(p => p.CustomAttributes.FirstOrDefault(c => c.AttributeType == typeof(PrimaryKeyAttribute)) != null); var sql = string.Format("DELETE FROM [{0}] WHERE {1} = @primarykey", typeof(T).Name, primary.Name); _conn.Execute(sql, new { primarykey = primaryValue }); } catch (Exception) { throw; } } /// <summary> /// /// </summary> /// <param name="strWhere"></param> public void DeleteStrWhere(string strWhere) { try { var sql = string.Format("DELETE FROM [{0}] WHERE {1}", typeof(T).Name, strWhere); _conn.Execute(sql); } catch (Exception) { throw; } } /// <summary> /// /// </summary> /// <param name="strWhere"></param> /// <param name="tableName"></param> /// <returns></returns> public virtual List<T> GetList(string strWhere, string tableName = "") { try { if (string.IsNullOrEmpty(tableName)) tableName = typeof(T).Name; var sql = string.Format("SELECT * FROM [{0}] " + (strWhere == "" ? "" : " WHERE " + " {1} "), tableName, strWhere); return _conn.Query<T>(sql).ToList(); } catch (Exception) { throw; } } /// <summary> /// /// </summary> /// <param name="param"></param> /// <returns></returns> public virtual PagerListResult<List<T>> GetPageList(PagerRequestParam param) { PagerListResult<List<T>> result = null; List<T> list = new List<T>(); int pageTotal = 1; int recordTotal = 0; int startIndex = 1; int endIndex = param.PageSize; try { if (param.PageIndex - 1 > 0) { startIndex = (param.PageIndex - 1 <= 0 ? 1 : param.PageIndex - 1) * param.PageSize + 1; endIndex = param.PageIndex * param.PageSize; } if (string.IsNullOrEmpty(param.TableName)) param.TableName = typeof(T).Name; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT * FROM ( "); strSql.Append(" SELECT ROW_NUMBER() OVER ("); if (!string.IsNullOrEmpty(param.OrderBy)) { strSql.Append("ORDER BY T." + param.OrderBy); } else { strSql.Append("ORDER BY T.ID DESC"); } strSql.Append(")AS Row, T.* FROM " + param.TableName + " T "); if (!string.IsNullOrEmpty(param.StrWhere)) { strSql.Append(" WHERE " + param.StrWhere); } strSql.Append(" ) TT"); strSql.AppendFormat(" WHERE TT.Row BETWEEN {0} AND {1}", startIndex, endIndex); list = _conn.Query<T>(strSql.ToString(), param.StrWhere).ToList(); if (list.Count > 0) { recordTotal = this.GetRecordCount(param.StrWhere, param.TableName); pageTotal = PagerRequestParam.Tool.PageTotal(param); } result = new PagerListResult<List<T>>(list, pageTotal, recordTotal); } catch (Exception ex) { result = new PagerListResult<List<T>>(ex); } return result; } /// <summary> /// 事务处理 /// Demo /// </summary> /// <returns></returns> public int DeleteTransaction() { try { const string delete1 = "DELETE FROM ED_Data WHERE TableName=@TableName"; const string delete2 = "DELETE FROM ED_Data WHERE TableName=@TableName"; IDbTransaction transaction = _conn.BeginTransaction(); int row = _conn.Execute(delete1, new { TableName = "user" }, transaction, null, null); row += _conn.Execute(delete2, new { TableName = "customer" }, transaction, null, null); transaction.Commit(); return row; } catch (Exception) { throw; } } /// <summary> /// 获取记录数 /// </summary> /// <param name="strWhere">Where条件</param> /// <returns></returns> public virtual int GetRecordCount(string strWhere, string tableName = "") { int count = 0; try { if (string.IsNullOrEmpty(tableName)) tableName = typeof(T).Name; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT COUNT(1) FROM " + tableName); if (!string.IsNullOrEmpty(strWhere)) { strSql.Append(" WHERE " + strWhere); } count = _conn.ExecuteScalar<int>(strSql.ToString()); } catch (Exception) { throw; } return count; } }} 0 0
- Dapper的基本使用
- Dapper的基本使用
- Dapper 基本使用
- Dapper 基本使用
- Dapper的基本使用
- Dapper的基本使用
- Dapper的基本使用
- Dapper使用
- C# Dapper 基本使用 增删改查事务等
- Dapper.NET使用
- Dapper的使用
- Dapper
- Dapper
- Dapper
- dapper linux 下使用wxPython
- .NETCore使用Dapper操作MySQL
- c#——Dapper基本用法
- Dapper.NET使用入门(一)【LINQ2Dapper】
- js实现页面跳转重定向的几种方式
- Javascript鼠标拖动面板的制作【思路及注意点】
- Spark doBulkLoad数据进入hbase
- Android 浅尝Tinker微信热修复
- centos7 为服务器设置静态内网IP
- Dapper 基本使用
- c++ 各种求min/max方法效率测试
- 第二本第六章上机3制作聚美优品美容热点产品列表
- GObject 学习笔记汇总---2
- PHP 开发APP 接口
- Java基本三：反射
- 二维码生成、扫描、图片识别（Zxing）
- codevs 1001 舒适的路线
- CODE[VS] 2171 Tyvj P1035 棋盘覆盖
