SqlSugar学习笔记 一——简单入门

一、准备

• 项目中通过Nuget 引入程序集--SQlSugarCore

二、创建以及初始化数据库链接对象

• 支持多种数据库类型，根据应用的数据库类型，可进行修改

 ConnectionConfig connectionConfig = new ConnectionConfig()
            {
                DbType = DbType.SqlServer,//数据库类型
                ConnectionString = "Data Source=DESKTOP-T2D6ILD;Initial Catalog=SqlSugarCustomerDB;User ID=sa;Password=sa123",
                //InitKeyType = InitKeyType.Attribute
                IsAutoCloseConnection = true
            };

三、DbFrist操作

• 整个操作过程中都是通过SqlSugarClient对象完成各种操作

• 基于数据库生成实体对象

  注意：在生成实体类的过程中，表总为主键的字段，不会自动进行特性标识，生成成功后，需要手动进行添加,特性标识如下：

  [SugarColumn(IsIdentity = true, IsPrimaryKey = true)]

  public int Id { get; set; }

 using (SqlSugarClient sqlSugarClient = new SqlSugarClient(connectionConfig))
 {
     //方式一
     {
                    //把数据库中的表全部生成实体类
                    //@"D:\SqlSugarDemot\SqlSugarDemo.Models"  生成实体对象的文件夹
                    sqlSugarClient.DbFirst.CreateClassFile(@"D:\SqlSugarDemo\SqlSugarDemo.Models");
     }
 	//方式二
     {
                    //根据条件筛选--生成实体类
                    sqlSugarClient.DbFirst.Where(c => c.StartsWith("Stu")).CreateClassFile(@"D:\SqlSugarDemot\SqlSugarDemo.Models");
 
     }

 }

• 完成对数据库的CRUD

  Commodity实体类

   [SugarTable("Commodity")]//指定对应的数据库中表名称
   public partial class Commodity
    {
        public Commodity()
        {


        }
        /// <summary>
        /// Desc:主键ID
        /// Default:
        /// Nullable:False
        /// </summary>   
        [SugarColumn(IsIdentity = true, IsPrimaryKey = true)]
        public int Id { get; set; }

        /// <summary>
        /// Desc:
        /// Default:
        /// Nullable:True
        /// </summary>           
        public long? ProductId { get; set; }

        /// <summary>
        /// Desc:
        /// Default:
        /// Nullable:True
        /// </summary>           
        public int? CategoryId { get; set; }

        /// <summary>
        /// Desc:Tilet信息
        /// Default:
        /// Nullable:True
        /// </summary>           
        public string Title { get; set; }

        /// <summary>
        /// Desc:
        /// Default:
        /// Nullable:True
        /// </summary>           
        public decimal? Price { get; set; }

        /// <summary>
        /// Desc:
        /// Default:
        /// Nullable:True
        /// </summary>           
        public string Url { get; set; }

        /// <summary>
        /// Desc:
        /// Default:
        /// Nullable:True
        /// </summary>           
        public string ImageUrl { get; set; }

    }

​ CRUD操作

 using (SqlSugarClient sqlSugarClient = new SqlSugarClient(connectionConfig))
 {
     //监控Sql语句
     sqlSugarClient.Aop.OnLogExecuting = (sql, par) =>
     {
         Console.WriteLine($"sql语句：{sql}");
     };
     //新增
     var addmodel = new Commodity()
     {
         CategoryId = 123,
         ImageUrl = "ImageUrl",
         Price = 34567,
         ProductId = 2345,
         Title = "测试数据",
         Url = "Url"
     };
     sqlSugarClient.Insertable<Commodity>(addmodel).ExecuteCommand();
     //查询
     Commodity commodity = sqlSugarClient.Queryable<Commodity>().OrderBy(c => c.Id, OrderByType.Desc).First(); ;
     //修改
     commodity.ImageUrl += DateTime.Now.ToShortDateString();
     sqlSugarClient.Updateable<Commodity>(commodity).ExecuteCommand();
     //删除
     sqlSugarClient.Deleteable<Commodity>(commodity).ExecuteCommand();
 }

四、CodeFrist操作

代码先行，不用关心数据库的，只需要写后台代码，需要什么对象就定义什么对象，然后一次给生成到数据库中去；

• 通过反射指定哪些类生成到数据库

 using (SqlSugarClient sqlSugarClient = new SqlSugarClient(connectionConfig))
 {
     //通过反射，指定哪些类来生成数据库  
     Assembly assembly = Assembly.LoadFrom("SqlSugarDemo.Models.dll");
     IEnumerable<Type> typelist = assembly.GetTypes().Where(c => c.Namespace == "SqlSugarDemo.Models");
     //是否备份数据库
     bool Backup = false;  
     //根据上面的数据库链接创建一个数据库出来
     sqlSugarClient.DbMaintenance.CreateDatabase();

     if (Backup)
     {
         sqlSugarClient.CodeFirst.BackupTable().InitTables(typelist.ToArray());
     }
     else
     {
         sqlSugarClient.CodeFirst.InitTables(typelist.ToArray());
     }
 }

• 对指定的某一个实体对应的表完成CRUD

 using (SqlSugarClient sqlSugarClient = new SqlSugarClient(connectionConfig))
 {
     //监控Sql语句
     sqlSugarClient.Aop.OnLogExecuting = (sql, par) =>
     {
         Console.WriteLine($"sql语句：{sql}");
     };

     //创建一个实体对象
     Commodity addmodel = new Commodity()
     {
         CategoryId = 123,
         ImageUrl = "ImageUrl",
         Price = 34567,
         ProductId = 2345,
         Title = "测试数据",
         Url = "Url"
     };

     //增加
     sqlSugarClient.Insertable<Commodity>(addmodel).ExecuteCommand();
     //查询
     Commodity commodity = sqlSugarClient.Queryable<Commodity>().OrderBy(c => c.Id, OrderByType.Desc).First(); ;
     //修改
     commodity.ImageUrl += DateTime.Now.ToShortDateString();
     sqlSugarClient.Updateable<Commodity>(commodity).ExecuteCommand();
     //删除 
     sqlSugarClient.Deleteable<Commodity>(commodity).ExecuteCommand();
 }