C#ORM框架SqlSugar安装及使用（以MySql、sqlite为示例）

目录

一、安装

二、使用（Mysql）

1、获取连接字符串

2、获取一个SqlSugarClient实例

3、构建一个表模型（名字不同，可通过SugarTable特性指定表）

4、使用SqlSugar框架命令操作数据库（增删改查）

二、使用（sqlite）

1、配置

2、创建表

3、增删改查

4、以导航方式增加 

5、以导航方式更新

6、以导航方式查询

---

一、安装

        通过NuGet包管理器搜索SqlSugar（MySql还要安装MySql.Data、Newtonsoft.Json）包并安装。

二、使用（Mysql）

1、获取连接字符串

示例代码：

        public static string GetConStr()        {            var builder = new MySqlConnectionStringBuilder()            {                Server = "localhost",                UserID = "root",                Password = "123456",                Database = "test"            };            return builder.ConnectionString;        }

2、获取一个SqlSugarClient实例

示例代码：

        public static SqlSugarClient GetSql()        {            string _connectstr = GetConStr();            SqlSugarClient _client = new (new ConnectionConfig            {                ConnectionString = _connectstr,                DbType = DbType.MySql,                IsAutoCloseConnection = true,                InitKeyType = InitKeyType.Attribute            });            return _client;        }

3、构建一个表模型（名字不同，可通过SugarTable特性指定表）

示例代码：

    [SugarTable("teacher")]    class Student    {         public int ID { get; set; }        public string Name { get; set; }            }

4、使用SqlSugar框架命令操作数据库（增删改查）

示例代码：

        public static void Main()        {            SqlSugarClient client = GetSql();            try            {                client.Ado.CheckConnection();            }            catch (Exception ex)            {                Console.WriteLine(ex.ToString());            }            //SqlSugarClient?  myclient= GetSqlSugarClient;            Student stu = new() { ID = 2, Name = "lisi" };            //var res = client.Queryable().Where(it => (it.ID.Equals(stu.ID))&&it.Name=="刘老师").First();            var res = client.Queryable().Where(it =>it.Name.Contains("老师")).First();            //var res = client.Queryable().First(it => (it.ID.Equals(stu.ID)) && it.Name == stu.Name);            Console.WriteLine(res.Name);            List res2=client.Queryable().ToList();            foreach (Student student in res2)                Console.WriteLine(student.Name);        }

二、使用（sqlite）

sqlite官网下载sqlite及其配套工具

1、配置

            var fd = new OpenFileDialog();            fd.InitialDirectory = Directory.GetCurrentDirectory();            fd.Title = "选择数据库";            fd.ShowDialog();            if (!string.IsNullOrEmpty(fd.FileName))            {                _scope = new SqlSugarScope(new ConnectionConfig()                {                    ConnectionString = $"data source={fd.FileName}",                    DbType = DbType.Sqlite,                    IsAutoCloseConnection = true,                });            }

2、创建表

 _scope.CodeFirst.InitTables(); _scope.CodeFirst.InitTables();

3、增删改查

_scope.Insertable(new Student() { Id = 9, Score = 99, Name = "Auston", SchoolId = 0 }).ExecuteCommand();         //增_scope.Deleteable().Where(m => m.Id == 0).ExecuteCommand();                     //删_scope.Updateable(new Student() { Id = 3, Score = 65, Name = "Kimi" }).ExecuteCommand();   //根据主键更改var list = _scope.Queryable().ToList();                      //查

4、以导航方式增加 

List list = new List(){    new Student() { Id = SnowFlakeSingle.Instance.NextId(), Score = 99, Name = "Auston7" },    new Student() { Id = SnowFlakeSingle.Instance.NextId(), Score = 59, Name = "Tom7"},    new Student() { Id = SnowFlakeSingle.Instance.NextId(), Score = 100, Name = "kimi7" }};List list8 = new List(){    new Student() { Id =SnowFlakeSingle.Instance.NextId(), Score = 99, Name = "Auston8" },    new Student() { Id = SnowFlakeSingle.Instance.NextId(), Score = 59, Name = "Tom8"},    new Student() { Id = SnowFlakeSingle.Instance.NextId(), Score = 100, Name = "kimi8" }};List Schools = new List(){    new School() { Id=7,Name="NiuDun",Students=list},    new School() { Id=8,Name="FuDan",Students=list8}};_scope.InsertNav(Schools).Include(i => i.Students).ExecuteCommand();      //插入导航

5、以导航方式更新

List list = new List(){    new Student() { Id = SnowFlakeSingle.instance.NextId(), Score = 50, Name = "Auston7" },    new Student() { Id = SnowFlakeSingle.instance.NextId(), Score =39, Name = "Tom7"},};List Schools = new List(){    new School() { Id=7,Name="JianQiao",Students=list},};_scope.UpdateNav(Schools).Include(m=>m.Students).ExecuteCommand();

6、以导航方式查询

//Data.Add(DateTime.Now.ToString("yyyy-MM-dd H:mm:ss"));var schools = _scope.Queryable().Includes(m =>m.Students).OrderBy(i=>i.Id,OrderByType.Desc).ToList();//var stus = schools[3].Students;//var stus8 = schools[4].Students;//foreach (var student in stus) { Data.Add($"\t{student.SchoolId} {student.Id} {student.Name} : {student.Score}"); }//foreach (var student in stus8) { Data.Add($"\t{student.SchoolId} {student.Id}{student.Name} : {student.Score}"); }