C# 学习和使用SlqSugar 三

这篇文章主要是介绍和学习在C#中如何使用SqlSugar,记录一下笔记，感兴趣的可以参考一下，如果写的有什么不对的，欢迎指正

前言

前面两篇文章已经简单介绍了关于SqlSugar的一些创建和使用情况【上一篇地址(学习和使用Sqlsugar 二)。】本文详细记录一下关于SqlSugar的一些简单的查询用法，本文只涉及单表查询。

准备工作

数据库的连接等一些内容前两篇已经写过了，这里就不详细写了，感兴趣的可以去看一下前面两篇文章。这里把我使用的表结构和数据直接放上来。

CREATE TABLE [dbo].[Students](
	[StudentId] [int] IDENTITY(100000,1) NOT NULL,
	[StudentName] [varchar](20) NOT NULL,
	[Gender] [char](2) NOT NULL,
	[Birthday] [smalldatetime] NOT NULL,
	[StudentIdNo] [numeric](18, 0) NOT NULL,
	[CardNo] [varchar](20) NOT NULL,
	[Age] [int] NOT NULL,
	[PhoneNumber] [varchar](50) NULL,
	[StudentAddress] [varchar](500) NULL,
	[ClassId] [int] NOT NULL,
 CONSTRAINT [pk_StudentId] PRIMARY KEY CLUSTERED 
(
	[StudentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [uq_CardNo] UNIQUE NONCLUSTERED 
(
	[CardNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [uq_StudentIdNo] UNIQUE NONCLUSTERED 
(
	[StudentIdNo] 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

ALTER TABLE [dbo].[Students] ADD  CONSTRAINT [df_StudentAddress]  DEFAULT ('地址不详') FOR [StudentAddress]
GO

ALTER TABLE [dbo].[Students]  WITH CHECK ADD  CONSTRAINT [fk_classId] FOREIGN KEY([ClassId])
REFERENCES [dbo].[StudentClass] ([ClassId])
GO

ALTER TABLE [dbo].[Students] CHECK CONSTRAINT [fk_classId]
GO

ALTER TABLE [dbo].[Students]  WITH CHECK ADD  CONSTRAINT [ck_Age] CHECK  (([Age]>=(18) AND [Age]<=(35)))
GO

ALTER TABLE [dbo].[Students] CHECK CONSTRAINT [ck_Age]
GO

ALTER TABLE [dbo].[Students]  WITH CHECK ADD  CONSTRAINT [ck_StudentIdNo] CHECK  ((len([StudentIdNo])=(18)))
GO

ALTER TABLE [dbo].[Students] CHECK CONSTRAINT [ck_StudentIdNo]
GO

数据的话，10条数据都是编的

SqlSugar基本查询

1.查询整表的数据

var studentlist = dbCline.Queryable<Students>().ToList();
//select * from Students
foreach (var student in studentlist) 
{
    Console.WriteLine("学号：" + student.StudentId + ",姓名：" + student.StudentName);
}

2.加WITH(NOLOCK)查询

{
     //查询的时候加了with（nolock）下面两种方式都可以，
     //还有一种方式是通过全局的方式去设置，在创建链接的时候MoreSettings  这个对象有个IsWithNoLockQuery属性，设置为true后默认查询就会带WITH（NOLOCK）
     var studentlist = dbCline.Queryable<Students>().With("WITH(NOLOCK)").ToList();
     //select * from Students WITH(NOLOCK)
     var getAllNoLock = dbCline.Queryable<Students>().With(SqlWith.NoLock).ToList();
    foreach (var student in studentlist) 
    {
        Console.WriteLine("学号：" + student.StudentId + ",姓名：" + student.StudentName);
    }
}

3.查询一个表里面的某几个字段

var studentlist = dbCline.Queryable<Students>().Select((n) => new { n.StudentId, n.StudentName, n.Age}).ToList();
//select StudentId，StudentName，Age from Students

4.查询第一条数据

{
    //查询第一行数据 ，如果没有，就返回为null
    //select top 1 * from Students
    var student = dbCline.Queryable<Students>().First();
    Console.WriteLine("学号：" + student.StudentId + ",姓名：" + student.StudentName);

    //查询StudentId>100002
    //select top 1 * from Students where StudentId>100002
    var student1 = dbCline.Queryable<Students>().First(n=>n.StudentId>100002);
    Console.WriteLine("学号：" + student1.StudentId + ",姓名：" + student1.StudentName);
}

6.查询前几条数据

{
    //查询前5条   select top 5 * from Students
    var student = dbCline.Queryable<Students>().Take(5).ToList();
    Console.WriteLine("条数：" + student.Count);
}

7.计数count

{
    //查询数据条数   select count(1) from Students
    var count = dbCline.Queryable<Students>().Count();
    Console.WriteLine("条数：" + count);
    //查询数据条数   select count(1) from Students where Age>22
    var count1 = dbCline.Queryable<Students>().Count(n=>n.Age>22);
    Console.WriteLine("条数：" + count1);
}

8.条件查询where

8.1

{
    //查询班级为1的学生信息
    //select * from Students where ClassId='1'
    var studentlist = dbCline.Queryable<Students>().Where(n=>n.ClassId==1).ToList();

   //查询班级为1的学生信息
   //select * from Students where ClassId='1' and Gender='男'
   var studentlist1 = dbCline.Queryable<Students>().Where(n => n.ClassId == 1&& n.Gender=="男").ToList();
   //查询null     select * from Students where StudentAddress is null
   var studentlist5 = dbCline.Queryable<Students>().Where(n => n.StudentAddress==null).ToList();

}

8.2模糊查询

{
    //模糊查询
    //select * from Students where StudentAddress like '%不%'
    //如果是'不%'，则对应的是StartsWith,如果是'%不' EndsWith
    var studentlist2 = dbCline.Queryable<Students>().Where(n => n.StudentAddress.Contains("不")).ToList();
    var studentlist3 = dbCline.Queryable<Students>().Where(n => n.StudentAddress.StartsWith("不")).ToList();
    var studentlist4 = dbCline.Queryable<Students>().Where(n => n.StudentAddress.EndsWith("不")).ToList();
}

9.判断是否存在Any

{
    //判断是否存在数据,   SELECT 1 FROM [Students]  WHERE ( [Age] > @25 )   下面两种方式都可以
    var studentlist = dbCline.Queryable<Students>().Where(n => n.Age > 25).Any();   //false
    Console.WriteLine("是否存在年龄》25岁的：" + studentlist);
    var studentlist1 = dbCline.Queryable<Students>().Any(n => n.Age > 24);  //true
    Console.WriteLine("是否存在年龄》24岁的：" + studentlist1);
}

10.in

{
    //查询1,2班的数据
    //SELECT [StudentId],[StudentName],[Gender],[Birthday],[StudentIdNo],[CardNo],[Age],[PhoneNumber],[StudentAddress],[ClassId] FROM [Students]  WHERE  ([ClassId] IN (1,2))
    int[] classids = new int[] { 1, 2 };//班级代码
    var studentlist1 = dbCline.Queryable<Students>().Where(n => classids.Contains(n.ClassId)).ToList();
    //查询名字叫 "王小虎", "李铭" 的学生信息 通过in方式
    //SELECT [StudentId],[StudentName],[Gender],[Birthday],[StudentIdNo],[CardNo],[Age],[PhoneNumber],[StudentAddress],[ClassId] FROM [Students]  WHERE  ([StudentName] IN ('王小虎','李铭'))
    string[] studentname = new string[] { "王小虎", "李铭" };
    var studentlist2 = dbCline.Queryable<Students>().Where(n => studentname.Contains(n.StudentName)).ToList();
}

11.动态拼接查询

OrIF和AndIF是条件符合才会拼接进去的

{
    string psex = "男";
    string name = "小";
    var exp = Expressionable.Create<Students>();
    exp.OrIF(!string.IsNullOrWhiteSpace(psex), n =>n.Gender== psex);//.OrIf 是条件成立才会拼接OR  如果psex不为空，则加入这个条件
    exp.OrIF(!string.IsNullOrWhiteSpace(name), n => n.StudentName.Contains(name));
    exp.Or(n => n.StudentAddress.Contains("地址"));//拼接OR
    exp.And(n => n.ClassId==1);//拼接and
    var list = dbCline.Queryable<Students>().Where(exp.ToExpression()).ToList();
    //SELECT [StudentId],[StudentName],[Gender],[Birthday],[StudentIdNo],[CardNo],[Age],[PhoneNumber],[StudentAddress],[ClassId]
    //FROM [Students]  WHERE ((([StudentName] like '%小%') OR ([StudentAddress] like '%地址%') ) AND ( [ClassId] = 1 )) 
}

12.排序

{
    //SELECT [StudentId],[StudentName],[Gender],[Birthday],[StudentIdNo],[CardNo],[Age],[PhoneNumber],[StudentAddress],[ClassId] FROM [Students] ORDER BY [ClassId] ASC
    var studentlist1 = dbCline.Queryable<Students>().OrderBy(n=>n.ClassId).ToList();//默认是asc升序
    var studentlist2 = dbCline.Queryable<Students>().OrderByDescending(n => n.ClassId).ToList();//desc 降序   OrderBy(n => n.ClassId,OrderByType.Desc) 这种方式也可以设置降序
    dbCline.Queryable<Students>().OrderBy(n => n.ClassId,OrderByType.Desc).ToList();
}

13.直接用SQL查询

{
    var list=dbCline.Ado.SqlQuery<Students>(@"select  * from Students where StudentName=@name", new { name = "王一二" });
}

14.WhereIF

这个语句里面那个name的条件不符合，则条件里面就没有加进去

{
    //whereif  前面条件成立的情况下，才会加入这个条件
    string name = "";
    int age = 24;
    var studentlist = dbCline.Queryable<Students>().Where(u => u.ClassId == 1)
    .WhereIF(!string.IsNullOrWhiteSpace(name), u => u.StudentName.Contains(name))
    .WhereIF(age > 0, u => u.Age > age).ToList();
}

本文讲了一些查询的基础用法，希望对大家有所帮助，之后在会在写一些多表查询，复杂查询的内容。
以上就是本文的全部内容，希望对大家有所帮助.