创建数据表
创建数据表
生成实体类
添加数据
更新数据
删除数据
分页查询
特性
1.实体类实例
class Student
{
/// <summary>
/// 主键ID
/// </summary>
[SugarColumn(IsIdentity = true, IsPrimaryKey = true)]
public int id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
2.SqlSugar实用类
/// <summary>
/// SqlSugar实用类
/// 使用该类时需要在包管理器中 安装:sqlSugarCore
/// 学习连接:https://www.donet5.com/Doc/1/1181
/// </summary>
class Services
{
private static Services _instance = null;
public static Services GetInstance => _instance ?? (_instance = new Services());
private SqlSugarClient db;
public SqlSugarClient Db => db;
/// <summary>
/// 在构造函数中连接数据库
/// </summary>
private Services()
{
//连接本地数据库(服务器名称)
string sqlConn = "Data Source=.;";
//数据库名称
sqlConn += "Initial Catalog = TestTable;";
//连接成功后是否保存密码信息",True表示保存,False表示不保存
sqlConn += "Persist Security Info = True;";
//用户名/密码
sqlConn += "User ID = sa;Password = Aa123456;";
//min pool size :连接池最小尺寸,首次连接池创建连接数量,max pool size:连接池最大尺寸
sqlConn += "Max Pool Size = 512;";
db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = sqlConn,//连接符字串
DbType = DbType.SqlServer,//该项报发生冲突错误时,移除命名空间引入的冲突对象
IsAutoCloseConnection = true
});
}
/// <summary>
/// 生成实体类
/// </summary>
/// <param name="objectNames">表名,生成实体类的名称</param>
/// <param name="path">实体类生成路径</param>
/// <param name="strNameSpace">实体类命名空间</param>
public void MCreateClassFile(string objectNames, string path, string strNameSpace)
{
//生成 当前连接数据库所有数据表 的实体类
db.DbFirst.CreateClassFile(path, strNameSpace);
//生成 数据表名为objectNames参数值的数据表 的实体类
db.DbFirst.Where(objectNames).CreateClassFile(path, strNameSpace);
//生成 数据表名转换为小写后 以t开头的数据表 的实体类
db.DbFirst.Where(it => it.ToLower().StartsWith("t")).CreateClassFile(path, strNameSpace);
//生成 数据表名为objectNames参数值的数据表 的实体类并包含特性
db.DbFirst.Where(objectNames).IsCreateAttribute().CreateClassFile(path, strNameSpace);
//生成 数据表名为objectNames参数值的数据表 的实体类并包含默认值
db.DbFirst.IsCreateDefaultValue().CreateClassFile(path, strNameSpace);
}
/// <summary>
/// 创建数据表
/// </summary>
public void CreatTable()
{
//Student 为实体类类名
//创建GUID主键的数据表时,主键类型设置为Guid即可,无需添加特性,数据库中id类型表示为:uniqueidentifier
db.CodeFirst.InitTables(typeof(Student));
}
/// <summary>
/// 向数据表添加数据
/// </summary>
public void InsertTable()
{
Student stu = new Student();
stu.Name = "张三";
stu.Age = 20;
//1.添加实例对象
db.Insertable(stu).ExecuteCommand();
//2.添加lst集合
List<Student> lst = new List<Student>();
lst.Add(stu);
lst.Add(stu);
lst.Add(stu);
lst.Add(stu);
lst.Add(stu);
db.Insertable(lst).ExecuteCommand();
//3.将2条数据插入一起执行
db.Insertable<Student>(new Student() { Name = "a" }).AddQueue();
db.Insertable<Student>(new Student() { Name = "b" }).AddQueue();
db.SaveQueues();
}
/// <summary>
/// 数据查询
/// </summary>
public void SelectTable()
{
//1.条件查询
List<Student> lstStudent = db.Queryable<Student>().Where(it => it.Name != null).ToList();
//2.单表分页查询
int pageIndex = 2;//第pageIndex页
int pageSize = 5;//每页的数据量
int totalCount = 0;//总数据条数
var page = db.Queryable<Student>().ToDataTablePage(pageIndex, pageSize, ref totalCount);
//3.二次查询
var getAll = db.Queryable<Student>()
.Select(it => new
{
NewId = it.id,
NewName = it.Name,
NewAge = it.Age
})
.MergeTable()//将上面的操作变成一个表 mergetable
.Select(it => new
{
id = it.NewId,
name = it.NewName
}).Where(it => it.id % 2 == 0)
.ToList();//查询mergetable表里面的字段
//4.条件拼接查询 + 分页查询
var query = db.Queryable<Student>().Where(it => it.id % 3 == 0);
query.Where(it => it.Name.Length > 2);
query.Where(it => it.Age == 24 || it.Age == 25);
var table = query.ToDataTablePage(pageIndex, pageSize, ref totalCount);//分页查询
//5.查询结果合并
var q1 = db.Queryable<Student>().Where(it => it.id > 1000 && it.id < 1005).Select(it => (object)new { it.id });
var q2 = db.Queryable<Student>().Where(it => it.id > 1015 && it.id < 1020).Select(it => (object)new { it.id });
var dt = db.Union(q1, q2).Select<Student>().ToDataTable();
//6.去重查询
dt = db.Queryable<Student>().PartitionBy(it => it.Age).Take(1).ToDataTable();
}
/// <summary>
/// 数据修改
/// </summary>
public void UpdateTable()
{
//1.更新Name和Age,条件为Name为“张张三”并且age为5
var t1 = db.Updateable<Student>()
.SetColumns(it => new Student() { Name = "张三", Age = 25 })
.Where(it => it.Name == "张张三" && it.Age == 5)
.ExecuteCommand();
Student student = db.Queryable<Student>().First(t => t.id == 10);//获取符合条件的第一条数据
student.Age = 30;//修改查询得到的实例对象的age值
//2.实例对象 根据条件修改
db.Updateable(student).Where(t => t.id == 10).ExecuteCommand();
//3. 根据主键更新数据
db.Updateable(student).ExecuteCommand();
}
/// <summary>
/// 删除数据
/// </summary>
public void DeleteTable()
{
//1.删除所有数据
db.Deleteable<Student>().ExecuteCommand();
//2.根据条件删除
db.Deleteable<Student>().Where(it => it.id % 2 == 0).ExecuteCommand();
}
}