SqlSugar操作笔记创建数据表
特性
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
/// 学习连接：www.donet5/Doc/1/1181
/// </summary>
class Services
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));
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>
/// 数据查询
param name
/
// </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);//分页查询
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();
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。