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

目录

一、安装

二、使用(Mysql)

1、获取连接字符串

2、获取一个SqlSugarClient实例

3、构建一个表模型(名字不同,可通过SugarTable特性指定表)

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

二、使用(sqlite)

1、配置

2、创建表

3、增删改查

4、以导航方式增加 

5、以导航方式更新

6、以导航方式查询


一、安装

        通过NuGet包管理器搜索SqlSugarMySql还要安装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}"); }

  • 3
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值