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

一、安装

    通过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<Student>().Where(it => (it.ID.Equals(stu.ID))&&it.Name=="刘老师").First();
            var res = client.Queryable<Student>().Where(it =>it.Name.Contains("老师")).First();
            //var res = client.Queryable<Student>().First(it => (it.ID.Equals(stu.ID)) && it.Name == stu.Name);
            Console.WriteLine(res.Name);
            List<Student> res2=client.Queryable<Student>().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<Student>();
 _scope.CodeFirst.InitTables<School>();

3、增删改查

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

4、以导航方式增加

List<Student> list = new List<Student>()
{
    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<Student> list8 = new List<Student>()
{
    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<School> Schools = new List<School>()
{
    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<Student> list = new List<Student>()
{
    new Student() { Id = SnowFlakeSingle.instance.NextId(), Score = 50, Name = "Auston7" },
    new Student() { Id = SnowFlakeSingle.instance.NextId(), Score =39, Name = "Tom7"},
};
List<School> Schools = new List<School>()
{
    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<School>().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}"); }