此例子是使用LINQ2Dapper封装
Model实体类
public class DataType
{
public int DataTypeId { get; set; }
public string Name { get; set; }
public bool IsActive { get; set; }
public DateTime? Created { get; set; }
}
public class Document
{
public int DocumentId { get; set; }
public int FieldId { get; set; }
public string Name { get; set; }
public DateTime? Created { get; set; }
}
public class Field
{
public int FieldId { get; set; }
public int DataTypeId { get; set; }
public string Name { get; set; }
}
上下文
public class DataContext
{
private readonly SqlConnection _connection;
private Linq2Dapper<DataType> _dataTypes;
private Linq2Dapper<Document> _documents;
private Linq2Dapper<Field> _fields;
public DataContext(string connectionString) : this(new SqlConnection(connectionString))
{
}
public DataContext(SqlConnection connection)
{
_connection = connection;
}
public Linq2Dapper<DataType> DataTypes
{
get { return _dataTypes ?? (_dataTypes = CreateObject<DataType>()); }
}
public Linq2Dapper<Field> Fields
{
get { return _fields ?? (_fields = CreateObject<Field>()); }
}
public Linq2Dapper<Document> Documents
{
get { return _documents ?? (_documents = CreateObject<Document>()); }
}
private Linq2Dapper<T> CreateObject<T>()
{
return new Linq2Dapper<T>(_connection);
}
}
Linq2DapperShould.cs辅助类
public class Linq2DapperShould
{
private static string ConnectionString
{
get
{
return
@"Data Source=.\SQLINSTANCE;Initial Catalog=DapperDemo;Persist Security Info=True;User ID=sa;Password=123456;";
}
}
public void SelectAllRecords()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>().ToList();
Console.WriteLine("SelectAllRecords:" + results.Count);
}
}
public void SelectAllRecords2()
{
var cntx = new DataContext(ConnectionString);
var results = cntx.DataTypes.Where(x => x.Name == "text").ToList();
Console.WriteLine("SelectAllRecords2:" + results.Count);
}
public void SelectSpeedTest()
{
for (int i = 0; i < 500; i++)
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>().ToList();
Console.WriteLine("SelectSpeedTest:" + results.Count);
}
}
}
public void JoinWhere()
{
var cntx = new DataContext(ConnectionString);
var results = (from d in cntx.DataTypes
join a in cntx.Fields on d.DataTypeId equals a.DataTypeId
where a.DataTypeId == 1
select d).ToList();
Console.WriteLine("JoinWhere:" + results.Count);
}
public void MultiJoinWhere()
{
var cntx = new DataContext(ConnectionString);
var results = (from d in cntx.DataTypes
join a in cntx.Fields on d.DataTypeId equals a.DataTypeId
join b in cntx.Documents on a.FieldId equals b.FieldId
where a.DataTypeId == 1 && b.FieldId == 1
select d).ToList();
Console.WriteLine("MultiJoinWhere:" + results.Count);
}
public void WhereContains()
{
using (var cn = new SqlConnection(ConnectionString))
{
var r = (from a in cn.Query<DataType>()
where new[] {"text", "int", "random"}.Contains(a.Name)
orderby a.Name
select a).ToList();
Console.WriteLine("WhereContains:" + r.Count);
}
}
public void WhereEquals()
{
using (var cn = new SqlConnection(ConnectionString))
{
foreach (var item in new[] {"text", "int"})
{
var results = cn.Query<DataType>(x => x.Name == item).ToList();
Console.WriteLine("WhereEquals:" + results.Count);
}
}
}
public void Top1Statement()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>().FirstOrDefault(m => !m.IsActive);
Console.WriteLine("Top1Statement:" + results.Name);
}
}
public void Top10A()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>().Take(5).ToList();
Console.WriteLine("Top10A:" + results.Count);
}
}
public void Top10B()
{
const int topCount = 10;
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>().Take(topCount).ToList();
Console.WriteLine("Top10B:" + results.Count);
}
}
public void Top10C()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
for (int topCount = 1; topCount < 5; topCount++)
{
var results = cn.Query<DataType>().Take(topCount).ToList();
Console.WriteLine("Top10C:" + results.Count);
}
}
}
public void Distinct()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>().Distinct().ToList();
Console.WriteLine("Distinct:" + results.Count);
}
}
public void OrderBy()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>().OrderBy(m => m.Name).ToList();
Console.WriteLine("OrderBy:" + results.Count);
}
}
public void OrderByAndThenBy()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>().OrderBy(m => m.Name).ThenBy(m => m.DataTypeId).ToList();
Console.WriteLine("OrderByAndThenBy:" + results.Count);
}
}
public void OrderByWithTop()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>().OrderBy(m => m.Name).ThenBy(m => m.DataTypeId).Take(5).ToList();
Console.WriteLine("OrderByWithTop:" + results.Count);
}
}
public void WhereSimpleEqual()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => m.Name == "text").ToList();
Console.WriteLine("WhereSimpleEqual:" + results.Count);
Console.WriteLine("WhereSimpleEqual:" + results[0].Name);
}
}
public void WhereSimpleEqualWithoutParameter()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => m.DataTypeId == m.DataTypeId).ToList();
Console.WriteLine("WhereSimpleEqualWithoutParameter:" + results.Count);
}
}
public void WhereIsNullOrEmpty()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => String.IsNullOrEmpty(m.Name)).ToList();
Console.WriteLine("WhereIsNullOrEmpty:" + results.Count);
}
}
public void WhereNotIsNullOrEmpty()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => !String.IsNullOrEmpty(m.Name)).ToList();
Console.WriteLine("WhereNotIsNullOrEmpty:" + results.Count);
}
}
public void WhereHasValue()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => m.Created.HasValue).ToList();
Console.WriteLine("WhereHasValue:" + results.Count);
}
}
public void WhereNotHasValue()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => !m.Created.HasValue).ToList();
Console.WriteLine("WhereNotHasValue:" + results.Count);
}
}
public void WhereLike()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => m.Name.Contains("te")).ToList();
Console.WriteLine("WhereLike:" + results.Count);
}
}
public void WhereEndsWith()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => m.Name.StartsWith("te")).ToList();
Console.WriteLine("WhereEndsWith:" + results.Count);
}
}
public void WhereStartsWith()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => m.Name.EndsWith("xt")).ToList();
Console.WriteLine("WhereStartsWith:" + results.Count);
}
}
public void WhereEndsWithAndComparison()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results =
cn.Query<DataType>(m => m.Name.StartsWith("te", StringComparison.OrdinalIgnoreCase)).ToList();
Console.WriteLine("WhereEndsWithAndComparison:" + results.Count);
}
}
public void WhereStartsWithAndComparison()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results =
cn.Query<DataType>(m => m.Name.EndsWith("xt", StringComparison.OrdinalIgnoreCase)).ToList();
Console.WriteLine("WhereStartsWithAndComparison:" + results.Count);
}
}
public void WhereNotLike()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => !m.Name.Contains("te")).ToList();
Console.WriteLine("WhereNotLike:" + results.Count);
}
}
public void WhereNotEndsWith()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => !m.Name.StartsWith("te")).ToList();
Console.WriteLine("WhereNotEndsWith:" + results.Count);
}
}
public void WhereNotStartsWith()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => !m.Name.EndsWith("xt")).ToList();
Console.WriteLine("WhereNotStartsWith:" + results.Count);
}
}
public void TwoPartWhereAnd()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => m.Name == "text" && m.Created.HasValue).ToList();
Console.WriteLine("TwoPartWhereAnd:" + results.Count);
}
}
public void TwoPartWhereOr()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results = cn.Query<DataType>(m => m.Name == "text" || m.Name == "int").ToList();
Console.WriteLine("TwoPartWhereOr:" + results.Count);
}
}
public void MultiPartWhereAndOr()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results =
cn.Query<DataType>(m => m.Name == "text" && (m.Name == "int" || m.Created.HasValue)).ToList();
Console.WriteLine("MultiPartWhereAndOr:" + results.Count);
}
}
public void MultiPartWhereAndOr2()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var results =
cn.Query<DataType>(m => m.Name != "text" && (m.Name == "int" || m.Created.HasValue)).ToList();
Console.WriteLine("MultiPartWhereAndOr2:" + results.Count);
}
}
public void Single()
{
using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var result = cn.Query<DataType>().Single(m => m.Name == "text");
Console.WriteLine("Single:" + result.Name);
}
}
}
Program.cs主程序:
internal class Program
{
private static void Main(string[] args)
{
Linq2DapperShould entity = new Linq2DapperShould()
entity.SelectAllRecords()
entity.SelectAllRecords2()
entity.JoinWhere()
entity.MultiJoinWhere()
entity.MultiPartWhereAndOr()
entity.MultiPartWhereAndOr2()
entity.OrderBy()
entity.OrderByAndThenBy()
entity.OrderByWithTop()
entity.Top10A()
entity.Top10B()
entity.Top10C()
//entity.Top1Statement()
entity.TwoPartWhereAnd()
entity.TwoPartWhereOr()
entity.WhereContains()
entity.WhereEndsWith()
entity.WhereEndsWithAndComparison()
entity.WhereEquals()
entity.WhereHasValue()
entity.WhereIsNullOrEmpty()
entity.WhereLike()
entity.WhereNotEndsWith()
entity.WhereNotHasValue()
entity.WhereNotIsNullOrEmpty()
entity.WhereNotLike()
entity.WhereNotStartsWith()
//entity.WhereSimpleEqual()
entity.WhereSimpleEqualWithoutParameter()
entity.WhereStartsWith()
entity.WhereStartsWithAndComparison()
//entity.SelectSpeedTest()
//entity.Single()
//...
Console.ReadKey()
}
}
数据库sql脚本:
USE [DapperDemo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Field](
[FieldId] [int] IDENTITY(1,1) NOT NULL,
[DataTypeId] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Created] [datetime] NOT NULL,
CONSTRAINT [PK_Field] PRIMARY KEY CLUSTERED
(
[FieldId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Document] Script Date: 09/08/2016 16:51:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Document](
[DocumentId] [int] IDENTITY(1,1) NOT NULL,
[FieldId] [int] NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[Created] [datetime] NOT NULL,
CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED
(
[DocumentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DataType] Script Date: 09/08/2016 16:51:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataType](
[DataTypeId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](128) NOT NULL,
[IsActive] [bit] NOT NULL,
[Created] [datetime] NOT NULL,
CONSTRAINT [PK_DataType] PRIMARY KEY CLUSTERED
(
[DataTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
运行结果:
