using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient; namespace Dapper
{
public class b_base
{
public IDbConnection Connection = null;
public b_base()
{
Connection = new SqlConnection(ConfigurationManager.AppSettings["dapper"]);
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.Data;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using Dapper; namespace Dapper
{
public class crud:b_base
{
public crud()
{ } #region 手写Sql插入数据
/// <summary>
/// 手写Sql插入数据
/// </summary>
public int InsertWithSql()
{
using (var conn = Connection)
{
string _sql ="INSERT INTO t_department(departmentname,introduce,[enable])VALUES('应用开发部SQL','应用开发部主要开始公司的应用平台',1)";
conn.Open();
return conn.Execute(_sql);
}
}
#endregion #region 实体插入数据
/// <summary>
/// 实体插入数据
/// </summary>
public int? InsertWithEntity()
{
using (var conn = Connection)
{
var _entity = new t_department { departmentname = "应用开发部ENTITY", introduce = "应用开发部主要开始公司的应用平台"};
conn.Open();
return conn.Insert(_entity);
}
}
#endregion #region 在IDBconnection中使用事务
/// <summary>
/// 在IDBconnection中使用事务
/// </summary>
/// <returns></returns>
public bool InsertWithTran()
{
using (var conn = Connection)
{
int _departmentid = 0, _employeeid = 0,_rnum=0;
var _departmentname = new t_department { departmentname = "应用开发部ENTITY", introduce = "应用开发部主要开始公司的应用平台" };
var _employee = new t_employee {displayname = "Micro",email ="1441299@qq.com",loginname ="Micro",password = "66778899",mobile = "123456789"};
conn.Open();
var _tran=conn.BeginTransaction();
try
{
_departmentid=conn.Insert(_departmentname, transaction: _tran).Value;
++_rnum;
_employeeid = conn.Insert(_employee, transaction: _tran).Value;
++_rnum;
conn.Insert(new t_derelation { departmentid = _departmentid, employeeid = _employeeid }, transaction: _tran);
++_rnum;
_tran.Commit();
}
catch
{
_rnum = 0;
_tran.Rollback();
}
return _rnum > 0;
}
}
#endregion #region 在存储过程中使用事务
/// <summary>
/// 在存储过程中使用事务
/// </summary>
/// <returns></returns>
public bool InsertWithProcTran()
{
var _parameter = new DynamicParameters();
_parameter.Add("departmentname","外网开发部门");
_parameter.Add("introduce","外网开发部门负责外部网站的更新");
_parameter.Add("displayname","夏季冰点");
_parameter.Add("loginname","Micro");
_parameter.Add("password","123456789");
_parameter.Add("mobile","1122334455");
_parameter.Add("email","123456789@qq.com");
using (var _conn = Connection)
{
_conn.Open();
return
_conn.Query<bool>("p_Insertdata", _parameter, commandType: CommandType.StoredProcedure)
.FirstOrDefault();
}
}
#endregion #region 查询所有员工信息方法一
/// <summary>
/// 查询所有员工信息方法一
/// </summary>
/// <returns></returns>
public IEnumerable<t_employee> GetemployeeListFirst()
{
string _sql = "SELECT * FROM t_employee";
using (var _conn = Connection)
{
_conn.Open();
return _conn.Query<t_employee>(_sql);
}
}
#endregion #region 查询所有员工信息方法二
/// <summary>
/// 查询所有员工信息
/// </summary>
/// <returns></returns>
public IEnumerable<t_employee> GetemployeeListSecond()
{
using (var _conn = Connection)
{
_conn.Open();
return _conn.GetList<t_employee>();
}
}
#endregion #region 获取某位员工的信息方法一
/// <summary>
/// 获取某位员工的信息方法一
/// </summary>
/// <param name="employeeid"></param>
/// <returns></returns>
public t_employee GetemployeeFirst(int employeeid)
{
string _sql = "SELECT * FROM t_employee where employeeid=@pemployeeid";
using (var _conn = Connection)
{
_conn.Open();
return _conn.Query<t_employee>(_sql, new { pemployeeid = employeeid }).FirstOrDefault();
}
}
#endregion #region 获取某位员工的信息方法二
/// <summary>
/// 获取某位员工的信息方法二
/// </summary>
/// <param name="employeeid"></param>
/// <returns></returns>
public t_employee GetemployeetSecond(int employeeid)
{
using (var _conn = Connection)
{
_conn.Open();
return _conn.Get<t_employee>(employeeid);
}
}
#endregion #region 获取某位员工的信息方法三
/// <summary>
/// 获取某位员工的信息方法三
/// </summary>
/// <param name="employeeid"></param>
/// <returns></returns>
public t_employee Getemployeethird(int pemployeeid)
{
using (var _conn = Connection)
{
_conn.Open();
return _conn.GetList<t_employee>(new { employeeid = pemployeeid }).FirstOrDefault();
}
}
#endregion #region 多表查询(获取部门&员工信息)
/// <summary>
/// 多表查询(获取部门&员工信息)
/// </summary>
public void GetMultiEntity()
{
string _sql = "SELECT * FROM t_department AS a;SELECT * FROM t_employee AS a";
using (var _conn = Connection)
{
var _grid = _conn.QueryMultiple(_sql);
var _department = _grid.Read<t_department>();
var _employee = _grid.Read<t_employee>();
}
}
#endregion #region 父子关系查询
/// <summary>
/// 父子关系查询
/// </summary>
public IEnumerable<t_department> GetPCEntity()
{
string _sql = "SELECT * FROM t_department AS a;SELECT * FROM t_employee AS a;SELECT * FROM t_derelation;";
using (var _conn = Connection)
{
var _grid = _conn.QueryMultiple(_sql);
var _department = _grid.Read<t_department>();
var _employee = _grid.Read<t_employee>();
var _derelation = _grid.Read<t_derelation>();
foreach (var tDepartment in _department)
{
tDepartment.ListEmployees = _employee.Join(_derelation.Where(v => v.departmentid == tDepartment.departmentid), p => p.employeeid, r => r.employeeid, (p, r) => p);
}
return _department;
}
}
#endregion #region 简单分页查询
/// <summary>
/// 分页查询
/// </summary>
/// <param name="pstart"></param>
/// <param name="pend"></param>
/// <returns></returns>
public IEnumerable<t_employee> GetPaging(int pstart=0,int pend=5)
{
string _sql = "SELECT * FROM (SELECT a.*, ROW_NUMBER() OVER (ORDER BY a.employeeid) rownum FROM t_employee as a ) b WHERE b.rownum BETWEEN @start AND @end ORDER BY b.rownum";
using (var _conn = Connection)
{
return _conn.Query<t_employee>(_sql, new {start = pstart, end = pend});
}
}
#endregion #region 通用分页
/// <summary>
/// 通用分页
/// </summary>
/// <returns></returns>
public int GetPaging()
{
////实际开发可以独立出来处理/////////////
var _ppaging = new p_PageList<t_employee>();
_ppaging.Tables = "t_employee";
_ppaging.OrderFields = "employeeid asc";
///////////////////////////////////////
var _dy = new DynamicParameters();
_dy.Add("Tables", _ppaging.Tables);
_dy.Add("OrderFields", _ppaging.OrderFields);
_dy.Add("TotalCount",dbType:DbType.Int32,direction: ParameterDirection.Output);
using (var _conn= Connection)
{
_conn.Open();
_ppaging.DataList=_conn.Query<t_employee>("p_PageList", _dy, commandType: CommandType.StoredProcedure);
}
_ppaging.TotalCount = _dy.Get<int>("TotalCount");
return _ppaging.PageCount;
}
#endregion #region 存储过程Demo
/// <summary>
/// 存储过程Demo
/// </summary>
public Tuple<string,string> ProceDemo()
{
int employeeid = 1;
var _mobile = "";
var _dy = new DynamicParameters();
_dy.Add("employeeid", employeeid);
_dy.Add("displayname", string.Empty, dbType: DbType.String, direction: ParameterDirection.Output);
using (var _conn = Connection)
{
_conn.Open();
_mobile= _conn.Query<string>("p_Procedemo", _dy, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
return Tuple.Create(_mobile, _dy.Get<string>("displayname"));
}
#endregion }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration; namespace Dapper
{
class Program
{
static void Main(string[] args)
{ //父子表测试
StringBuilder builder = new StringBuilder();
var dlist = new crud().GetPCEntity();
foreach (var de in dlist)
{
builder.AppendLine(de.departmentid + "---->" + de.departmentname);
var elist = de.ListEmployees;
foreach (var em in elist)
{
builder.AppendLine(em.displayname + "---->" + em.email);
}
}
Console.Write(builder.ToString());
Console.Read();
}
}
}
CREATE PROCEDURE p_PageList
@Tables VARCHAR(200),
@Fields VARCHAR(500) = '*',
@OrderFields VARCHAR(100),
@Where VARCHAR(100) = NULL,
@PageIndex INT = 1 ,
@PageSize INT = 20,
@GroupBy VARCHAR(100) = NULL,
@TotalCount INT = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(4000);
DECLARE @PageCount INT;
IF (@GroupBy = '' OR @GroupBy IS NULL)
BEGIN
SET @sql = 'select @RecordCount = count(*) from ' + @Tables
IF (@Where <> '' AND @Where IS NOT NULL)
SET @sql = @sql + ' where ' + @Where
END
ELSE
BEGIN
SET @sql = 'select @Recordcount=count(*) from(select 1 as total from ' +
@Tables IF (@Where <> '' AND @Where IS NOT NULL)
SET @sql = @sql + ' where ' + @Where SET @sql = @sql + ' group by ' + @GroupBy + ') as t'
END
EXEC sp_executesql @sql,
N'@RecordCount int OUTPUT',
@TotalCount OUTPUT SELECT @PageCount = CEILING((@TotalCount + 0.0) / @PageSize)
SET @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderFields
+ ') as rowId,' + @Fields + ' from ' + @Tables IF (@Where <> '' AND @Where IS NOT NULL)
SET @sql = @sql + ' where ' + @Where IF (@GroupBy <> '' AND @GroupBy IS NOT NULL)
SET @sql = @sql + ' group by ' + @GroupBy IF @PageIndex <= 0
SET @PageIndex = 1 IF @PageIndex > @PageCount
SET @PageIndex = @PageCount DECLARE @StartRecord INT,
@EndRecord INT SELECT @StartRecord = (@PageIndex -1) * @PageSize + 1,
@EndRecord = @StartRecord + @PageSize - 1 SET @Sql = @Sql + ') as ' + @Tables + ' where rowId between ' + CONVERT(VARCHAR(50), @StartRecord)
+ ' and ' + CONVERT(VARCHAR(50), @EndRecord) EXEC (@Sql)
---------------------------------------------------
SET NOCOUNT OFF;
END
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration; namespace Dapper
{
class Program
{
private static readonly string connString = ConfigurationManager.AppSettings["northwind"].ToString();
static void Main(string[] args)
{
/*
using (var cn = new SqlConnection(ConfigurationManager.AppSettings["northwind"]))
{
//查询类别为2的商品
var list = cn.Query(
"SELECT * FROM Products WHERE CategoryID=@catg", new { catg = 2 });
foreach (var item in list)
{
Console.WriteLine("{0}.{1}({2})",
item.ProductID, item.ProductName, item.QuantityPerUnit);
}
}
*/ using (var cn = new SqlConnection(connString))
{
//1) 将SELECT結果转换成指定的类别(属性和字段名要保持一致)
//2) 直接传数字组合作为WHERE IN比對参数
// =>自动转换成WHERE col in (@arg1,@arg2,@arg3)
var list = cn.Query<SimpProduct>(
"SELECT * FROM Products WHERE CategoryID IN @catgs",
new { catgs = new int[] { 1, 4 } });
foreach (var item in list)
{
Console.WriteLine("{0}.{1} category:{2}",
item.ProductID, item.ProductName, item.CategoryID);
}
} using (var cn = new SqlConnection(connString))
{
//1) 可执行SQL命令，支持参数
//2) 提供多组参数，可批量执行，适合批量插入，修改，删除操作
cn.Execute(@"INSERT INTO Test VALUES (@username, @userage)",
new[] {
new { username = "张三", userage = 20 },
new { username = "李四", userage = 25 }
});
}
Console.Read();
}
} public class SimpProduct
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public int CategoryID { get; set; }
}
}
//书和书评是1---n的关系。（沿用Entity Framework的实体类，virtual表示延迟加载，此处忽略） //书
public class Book
{
public Book()
{
Reviews = new List<BookReview>();
}
public int Id { get; set; }
public string Name { get; set; }
public virtual List<BookReview> Reviews { get; set; }
public override string ToString()
{
return string.Format("[{0}]------《{1}》", Id, Name);
}
} //书评
public class BookReview
{
public int Id { get; set; }
public int BookId { get; set; }
public virtual string Content { get; set; }
public virtual Book AssoicationWithBook { get; set; }
public override string ToString()
{
return string.Format("{0})--[{1}]\t\"{3}\"", Id, BookId, Content);
}
} //基本的增删改查操作
//由于Dapper ORM的操作实际上是对IDbConnection类的扩展，所有的方法都是该类的扩展方法。所以在使用前先实例化一个IDBConnection对象。 IDbConnection conn = new SqlConnection(connString); Insert Book book = new Book();
book.Name="C#本质论";
string query = "INSERT INTO Book(Name)VALUES(@name)";
//对对象进行操作
conn.Execute(query, book);
//直接赋值操作
conn.Execute(query, new {name = "C#本质论"}); update string query = "UPDATE Book SET Name=@name WHERE id =@id";
conn.Execute(query, book); delete string query = "DELETE FROM Book WHERE id = @id";
conn.Execute(query, book);
conn.Execute(query, new { id = id }); query string query = "SELECT * FROM Book";
//无参数查询，返回列表，带参数查询和之前的参数赋值法相同。
conn.Query<Book>(query).ToList(); //返回单条信息
string query = "SELECT * FROM Book WHERE id = @id";
book = conn.Query<Book>(query, new { id = id }).SingleOrDefault(); 数据库表对应关系操作 //查询图书时，同时查找对应的书评，并存在List中。实现1--n的查询操作
string query = "SELECT * FROM Book b LEFT JOIN BookReview br ON br.BookId = b.Id WHERE b.id = @id";
Book lookup = null;
//Query<TFirst, TSecond, TReturn>
var b = conn.Query<Book, BookReview, Book>(query,
(book, bookReview) =>
{
//扫描第一条记录，判断非空和非重复
if (lookup == null || lookup.Id != book.Id)
lookup = book;
//书对应的书评非空，加入当前书的书评List中，最后把重复的书去掉。
if (bookReview != null)
lookup.Reviews.Add(bookReview);
return lookup;
}, new { id = id }).Distinct().SingleOrDefault();
return b; //1--1操作
BookReview br;
string query = "SELECT * FROM BookReview WHERE id = @id";
using (conn)
{
br = conn.Query<BookReview, Book, BookReview>(query,
(bookReview, book) =>
{
bookReview.AssoicationWithBook = book;
return bookReview;
}, new { id = id }).SingleOrDefault();
return br;
} 事务操作 using (conn)
{
//开始事务
IDbTransaction transaction = conn.BeginTransaction();
try
{
string query = "DELETE FROM Book WHERE id = @id";
string query2 = "DELETE FROM BookReview WHERE BookId = @BookId";
conn.Execute(query2, new { BookId = id }, transaction, null, null);
conn.Execute(query, new { id = id }, transaction, null, null);
//提交事务
transaction.Commit();
}
catch (Exception ex)
{
//出现异常，事务Rollback
transaction.Rollback();
throw new Exception(ex.Message);
}
}
Dapper-Extensions基本使用 //实体类
DemoEntity entity = new DemoEntity(); //根据实体主键删除
this.Delete<DemoEntity>(entity); //根据主键ID删除
this.Delete<DemoEntity>(1); //增加
this.Insert<DemoEntity>(entity); //更新
bool result = this.Update<DemoEntity>(entity); //根据主键返回实体
entity = this.GetById<DemoEntity>(1); //返回 行数
this.Count<DemoEntity>(new { ID = 1 }); //查询所有
IEnumerable<DemoEntity> list = this.GetAll<DemoEntity>(); IList<ISort> sort = new List<ISort>();
sort.Add(new Sort { PropertyName = "ID", Ascending = false }); //条件查询
list = this.GetList<DemoEntity>(new { ID = 1, Name = "123" }, sort); //orm 拼接条件 查询
IList<IPredicate> predList = new List<IPredicate>();
predList.Add(Predicates.Field<DemoEntity>(p => p.Name, Operator.Like, "不知道%"));
predList.Add(Predicates.Field<DemoEntity>(p => p.ID, Operator.Eq, 1));
IPredicateGroup predGroup = Predicates.Group(GroupOperator.And, predList.ToArray()); list = this.GetList<DemoEntity>(predGroup); //分页查询
long allRowsCount = 0;
this.GetPageList<DemoEntity>(1, 10, out allRowsCount, new { ID = 1 }, sort); //Dapper测试 ////新增数据
//RBAC.Model.SY_ADMIN model = new RBAC.Model.SY_ADMIN();
//model.UserName = "16bb21";
//model.RealName = "张三";
//int id = RBAC.Bll.SY_ADMIN.Insert(model);
//str += string.Format("数据新增成功,ID={0}", id); ////修改数据
//RBAC.Model.SY_ADMIN model2 = new RBAC.Model.SY_ADMIN();
//model2.UserID = 10111;
//model2.UserName = "li5551222211";
//model2.RealName = "李1四";
//bool flag = RBAC.Bll.SY_ADMIN.Update(model2);
//if (flag)
//{
// str += string.Format("更新成功");
//}
//else
//{
// str += string.Format("更新失败，无此数据");
//} ////删除（一条数据）
//bool flag = RBAC.Bll.SY_ADMIN.Delete(10065);
//if (flag)
//{
// str += string.Format("删除成功");
//}
//else
//{
// str += string.Format("删除失败，无此数据");
//} ////获取一条数据
//var model = RBAC.Bll.SY_ADMIN.Get(10075);
//if (model == null)
//{
// str += string.Format("用户ID{0}的数据不存在", 10075);
//}
//else
//{
// str += string.Format("用户名{0}密码{1}真实姓名{2}", model.UserName, model.UserPwd, model.RealName);
//} ////获取一组数据
//var pgMain = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };
//var pg1 = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
//pg1.Predicates.Add(Predicates.Field<RBAC.Model.SY_ADMIN>(f => f.RealName, Operator.Eq, "张三")); //var pg2 = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };
//pg2.Predicates.Add(Predicates.Field<RBAC.Model.SY_ADMIN>(f => f.UserName, Operator.Like, "%bb%")); //pgMain.Predicates.Add(pg1);
//pgMain.Predicates.Add(pg2); ///*
// * 自动生成sql语句
// * SELECT * FROM [SY_ADMIN] WHERE ((([SY_ADMIN].[RealName] = @RealName_0)) OR (([SY_ADMIN].[UserName] LIKE @UserName_1)))
//*/
//var list = RBAC.Bll.SY_ADMIN.Get(pgMain);
//foreach (var item in list)
//{
// str += string.Format("{0},用户名{1},密码{2},真实姓名{3}<br/>", item.UserID, item.UserName, item.UserPwd, item.RealName);
//} //获取分页数据
int totalRecord = 0;
int totalPage = 0;
var list = RBAC.Bll.SY_ADMIN.GetPage("*", "userID asc", 1, 2,
"", out totalRecord, out totalPage);
foreach (var item in list)
{
str += string.Format("{0},用户名{1},密码{2},真实姓名{3}-{4}-{5}<br/>", item.UserID, item.UserName, item.UserPwd, item.RealName, totalRecord, totalPage);
} ////扩展自定义方法
//var list = RBAC.Bll.SY_ADMIN.GetAllByExt2();
//foreach (var item in list)
//{
// str += string.Format("{0},用户名{1},密码{2},真实姓名{3}<br/>", item.UserID, item.UserName, item.UserPwd, item.RealName);
//} ////事务处理
//using (TransactionScope scope = new TransactionScope())
//{
// //主程序区域
// scope.Complete();
//} using Dapper;
using DapperExtensions; //Install-Package DapperExtensions
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text; namespace RBAC.Dal
{
public partial class SY_ADMIN
{
public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; #region 新增数据
/// <summary>
/// 新增数据
/// </summary>
/// <param name="model">实体类</param>
/// <returns></returns>
public int Insert(Model.SY_ADMIN model)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
int a = conn.Insert(model);
conn.Close();
return a;
}
}
#endregion #region 更新数据
/// <summary>
/// 更新数据
/// </summary>
/// <param name="model">实体类</param>
/// <returns></returns>
public bool Update(Model.SY_ADMIN model)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
Model.SY_ADMIN model1 = conn.Get<Model.SY_ADMIN>(model.UserID);
if (model1 != null)
{
if (null != model.UserName) { model1.UserName = model.UserName ; }
if (null != model.UserPwd) { model1.UserPwd = model.UserPwd ; }
if (null != model.SafeCode) { model1.SafeCode = model.SafeCode ; }
if (null != model.RealName) { model1.RealName = model.RealName ; }
if (null != model.CreateDate) { model1.CreateDate = model.CreateDate ; }
if (null != model.LastDate) { model1.LastDate = model.LastDate ; }
if (null != model.LastIP) { model1.LastIP = model.LastIP ; }
if (null != model.CurrIP) { model1.CurrIP = model.CurrIP ; }
if (null != model.LoginCount) { model1.LoginCount = model.LoginCount ; }
if (null != model.GroupID) { model1.GroupID = model.GroupID ; }
var a = conn.Update(model1);
conn.Close();
return a;
}
else
{
conn.Close();
return false;
}
}
}
#endregion #region 删除数据
/// <summary>
/// 删除数据
/// </summary>
/// <param name="id">主键</param>
/// <returns></returns>
public bool Delete(int id)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
Model.SY_ADMIN model = new Model.SY_ADMIN();
model.UserID = id;
bool a = conn.Delete(model);
conn.Close();
return a;
}
} /// <summary>
/// 删除数据
/// </summary>
/// <param name="model">实体类条件</param>
/// <returns></returns>
public bool Delete(Model.SY_ADMIN model)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
StringBuilder sqlStr = new StringBuilder();
if (null != model.UserName) { sqlStr.Append(" AND [UserName] = @UserName "); }
if (null != model.UserPwd) { sqlStr.Append(" AND [UserPwd] = @UserPwd "); }
if (null != model.SafeCode) { sqlStr.Append(" AND [SafeCode] = @SafeCode "); }
if (null != model.RealName) { sqlStr.Append(" AND [RealName] = @RealName "); }
if (null != model.CreateDate) { sqlStr.Append(" AND [CreateDate] = @CreateDate "); }
if (null != model.LastDate) { sqlStr.Append(" AND [LastDate] = @LastDate "); }
if (null != model.LastIP) { sqlStr.Append(" AND [LastIP] = @LastIP "); }
if (null != model.CurrIP) { sqlStr.Append(" AND [CurrIP] = @CurrIP "); }
if (null != model.LoginCount) { sqlStr.Append(" AND [LoginCount] = @LoginCount "); }
if (null != model.GroupID) { sqlStr.Append(" AND [GroupID] = @GroupID "); } string sql = string.Format("DELETE FROM [{0}] WHERE 1=1 {1}",
"SY_ADMIN",
sqlStr.ToString().TrimEnd(','));
var a = conn.Execute(sql,
new { UserName = model.UserName,UserPwd = model.UserPwd,SafeCode = model.SafeCode,RealName = model.RealName,CreateDate = model.CreateDate,LastDate = model.LastDate,LastIP = model.LastIP,CurrIP = model.CurrIP,LoginCount = model.LoginCount,GroupID = model.GroupID,UserID = model.UserID });
conn.Close();
return a > 0;
}
}
#endregion #region 获取数据
/// <summary>
/// 获取数据(一条)
/// </summary>
/// <param name="id">主键</param>
/// <returns></returns>
public Model.SY_ADMIN Get(int id)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var a = conn.Get<Model.SY_ADMIN>(id);
conn.Close();
return a;
}
} /// <summary>
/// 获取数据(实体类条件)
/// </summary>
/// <param name="model">实体类</param>
/// <returns></returns>
public IList<Model.SY_ADMIN> Get(PredicateGroup pg)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var a = conn.GetList<Model.SY_ADMIN>(pg).ToList();
conn.Close();
return a;
}
} /// <summary>
/// 获取数据(全部)
/// </summary>
/// <returns></returns>
public IList<Model.SY_ADMIN> GetAll()
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var a = conn.GetList<Model.SY_ADMIN>().ToList();
conn.Close();
return a;
}
}
#endregion #region 获取分页数据
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="fields">字段,如*或逗号分隔</param>
/// <param name="orderField">排序,如id desc</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页条数</param>
/// <param name="whereStr">条件</param>
/// <param name="totalRecord">总记录数</param>
/// <param name="totalPage">总页数</param>
/// <returns></returns>
public IList<Model.SY_ADMIN> GetPage(string fields, string orderField, int pageIndex,
int pageSize, string whereStr, out int totalRecord, out int totalPage)
{
using (var conn = new SqlConnection(connectionString))
{
totalRecord = conn.ExecuteScalar<int>(string.Format("SELECT count(1) FROM SY_ADMIN where 1=1 {0}", whereStr));
totalPage = (totalRecord % pageSize != 0) ? (totalRecord / pageSize + 1) : totalRecord / pageSize;
string sql = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) AS ROWID, "
+ "{0} FROM {2} where 1=1 {3} ) AS t WHERE ROWID BETWEEN {4} AND {5}",
fields,
orderField,
"SY_ADMIN",
whereStr,
(pageIndex - 1) * pageSize + 1,
pageIndex * pageSize);
var a = conn.Query<Model.SY_ADMIN>(sql).ToList();
conn.Close();
return a;
}
}
#endregion
}
}
class Program
{
//项目中建议尽量用强类型，虽然麻烦点，但后期好维护
static void Main(string[] args)
{
string connStr = "Data Source=.;Initial Catalog=DapperDB;User ID=sa;Password=xxx"; #region 强类型
//public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open(); //无参查询
//var qqModeList = conn.Query<QQModel>("select Id,Name,Count from QQModel");
//带参查询
var qqModeList = conn.Query<QQModel>("select Id,Name,Count from QQModel where Id in @id and Count>@count", new { id = new int[] { 1, 2, 3, 4, 5, 6 }, count = 1 });
foreach (var item in qqModeList)
{
Console.WriteLine(item.Id + " " + item.Name + " " + item.Count);
}
}
#endregion #region 动态类型
//逆天动态类型用的比较多[可能是MVC ViewBag用多了]
////public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)
//using (SqlConnection conn = new SqlConnection(connStr))
//{
// conn.Open();
// var qqModeList = conn.Query("select Id,Name,Count from QQModel").ToList();
// foreach (var item in qqModeList)
// {
// Console.WriteLine(item.Id + " " + item.Name + " " + item.Count);
// }
//}
#endregion #region 多映射
//using (SqlConnection conn = new SqlConnection(connStr))
//{
// string sqlStr = @"select A.Id,A.Title,S.SeoKeywords from Article A
// inner join SeoTKD S on A.SeoId=S.Id
// where A.Id in @ids";
// conn.Open();
// var articleList = conn.Query(sqlStr, new { ids = new int[] { 41, 42, 43, 44, 45, 46, 47, 48 } });
// foreach (var item in articleList)
// {
// Console.WriteLine(item.Id + " | " + item.SeoKeywords + " | ：" + item.Title);
// }
//}
#endregion #region 多返回值
//using (SqlConnection conn = new SqlConnection(connStr))
//{
// string sqlStr = @"select Id,Title,Author from Article where Id = @id
// select * from QQModel where Name = @name
// select * from SeoTKD where Status = @status";
// conn.Open();
// using (var multi = conn.QueryMultiple(sqlStr, new { id = 11, name = "打代码", status = 99 }))
// {
// //multi.IsConsumed reader的状态 ，true 是已经释放
// if (!multi.IsConsumed)
// {
// //注意一个东西，Read获取的时候必须是按照上面返回表的顺序 （article，qqmodel，seotkd）
// //强类型
// var articleList = multi.Read<Temp>();//类不见得一定得和表名相同
// var QQModelList = multi.Read<QQModel>();
// var SeoTKDList = multi.Read<SeoTKD>(); // //动态类型
// //var articleList = multi.Read();
// //var QQModelList = multi.Read();
// //var SeoTKDList = multi.Read(); // #region 输出
// foreach (var item in QQModelList)
// {
// Console.WriteLine(item.Id + " " + item.Name + " " + item.Count);
// }
// foreach (var item in SeoTKDList)
// {
// Console.WriteLine(item.Id + " | " + item.SeoKeywords);
// }
// foreach (var item in articleList)
// {
// Console.WriteLine(item.Author);
// }
// #endregion
// } // }
//}
#endregion #region 增删改等
//using (SqlConnection conn = new SqlConnection(connStr))
//{
// conn.Open();
// //增
// int count = conn.Execute("insert into Article values(@title,@content,@author,961,1,2,2,N'2015-11-23 11:06:36.553',N'2015-11-23 11:06:36.553',N'5,103,113',91,N'3,5,11',0,N'/Images/article/16.jpg')", new { title = "Title1", content = "TContent1", author = "毒逆天" }); // //改
// //int count = conn.Execute("update Article set Title=@title where Id=@id", new { title = "么么哒", id = 274 });
// if (count > 0)
// {
// Console.WriteLine(count + "条操作成功");
// }
//}
#endregion #region 存储过程
////查询
//using (SqlConnection conn = new SqlConnection(connStr))
//{
// conn.Open();
// //参数名得和存储过程的变量名相同（参数可以跳跃传，键值对方式即可）
// //动态类型
// //var list = conn.Query("usp_test", new { aId = 11 }, commandType: CommandType.StoredProcedure);
// //强类型
// var list = conn.Query<TitleAndKeyWords>("usp_test", new { aId = 11 }, commandType: CommandType.StoredProcedure);
// foreach (var item in list)
// {
// Console.WriteLine(item.Id + " | " + item.SeoKeywords + " | ：" + item.Title);
// }
//} ////插入
//using (SqlConnection conn = new SqlConnection(connStr))
//{
// conn.Open();
// int count = conn.Execute("usp_insertArticle", new { title = "Title11", content = "TContent1", author = "毒逆天" }, commandType: CommandType.StoredProcedure);
// if (count > 0)
// {
// Console.WriteLine(count + "条操作成功");
// }
//} ////更新
//using (SqlConnection conn = new SqlConnection(connStr))
//{
// conn.Open();
// int count = conn.Execute("usp_updateArticle", new { id = 276, title = "Dapper使用" }, commandType: CommandType.StoredProcedure);
// if (count > 0)
// {
// Console.WriteLine(count + "条操作成功");
// }
//}
#endregion Console.ReadKey();
}
}
