.net数据库操作框架SqlSugar的简单入门

1
2
3
4
5
6
public class DataDictionary
{
    public string Code { get; set; }
    public string Name { get; set; }
    public string Type { get; set; }
}

1
2
3
4
5
6
7
8
9
10
11
var db = GetInstance();
            List<DataDictionary> datas = new List<DataDictionary>();
            datas.Add(new DataDictionary() { Code="1", Name="男",Type="sex" });
            datas.Add(new DataDictionary() { Code = "2", Name = "女", Type = "sex" });
            datas.Add(new DataDictionary() { Code = "1", Name = "南通市", Type = "city" });
            datas.Add(new DataDictionary() { Code = "2", Name = "苏州市", Type = "city" });
            datas.Add(new DataDictionary() { Code = "1", Name = "江苏省", Type = "province" });
            datas.Add(new DataDictionary() { Code = "2", Name = "湖南省", Type = "province" });
 
            db.CodeFirst.InitTables<DataDictionary>();//这样就能根据实体建表了
            db.Insertable(datas).ExecuteCommand();//这样就能把数据插进数据库了<br>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public class Person
{
    //数据库字段
    [SqlSugar.SugarColumn(IsPrimaryKey =true,IsIdentity =true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public int SexId { get; set; }
    public int CityId { get; set; }
    public int ProvinceId { get; set; }
 
    //非数据库字段
    [SqlSugar.SugarColumn(IsIgnore =true)]
    public string SexName { get; set; }
    [SqlSugar.SugarColumn(IsIgnore = true)]
    public string CityName { get; set; }
    [SqlSugar.SugarColumn(IsIgnore = true)]
    public string ProviceName { get; set; }
}

1
2
3
4
5
6
7
8
9
if (!db.ConfigQuery.Any())
   {
                var types= db.Queryable<DataDictionary>().Select(it => it.Type).Distinct().ToList();
                foreach (var type in types)
                {
                    db.ConfigQuery.SetTable<DataDictionary>(it => it.Code, it => it.Name, type, it => it.Type == type);
                }
                //如果其中Code都是唯一值可以按 1.4中的用法使用循环都不要
}

1
2
3
4
5
6
7
8
9
   var res=db.Queryable<Person>().Select(it => new Person()
   {
                 Id=it.Id.SelectAll(),
                 SexName=it.SexId.GetConfigValue<DataDictionary>("sex"),
                 ProvinceName = it.ProvinceId.GetConfigValue<DataDictionary>("province"),
                 CityName = it.CityId.GetConfigValue<DataDictionary>("city"),
   }).ToList();
 
//也支持支持写在Where或者Orderby

1
2
3
4
5
6
db.ConfigQuery.SetTable<Order>(it => it.Id, it => it.Name);//配置Order<br>
var list3 = db.Queryable<OrderItem>().Select(it => new OrderItem
{
        ItemId = it.ItemId.SelectAll(),
        OrderName = it.OrderId.GetConfigValue<Order>() //查询的时候直接用
}).ToList();

1
2
3
4
5
6
7
8
9
10
11
[TenantAttribute("1")]
  public class C1Table
  {
     public string Id { get; set; }
  }
 
  [TenantAttribute("2")]
  public class C2Table
  {
      public string Id { get; set; }
  }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
public class Repository<T> : SimpleClient<T> where T : class, new()
    {
        public Repository(ISqlSugarClient context = null) : base(context)//注意这里要有默认值等于null
        {
            if (context == null)
            {
                var db = new SqlSugarClient(new List<ConnectionConfig> {
                                                    new ConnectionConfig()
                                                {
                                                    ConfigId="1",
                                                    DbType = SqlSugar.DbType.SqlServer,
                                                    IsAutoCloseConnection = true,
                                                    ConnectionString = Config.ConnectionString
                                                },
                                                    new ConnectionConfig()
                                                {
                                                    ConfigId="2",
                                                    DbType = SqlSugar.DbType.SqlServer,
                                                    IsAutoCloseConnection = true,
                                                    ConnectionString = Config.ConnectionString2
                                                }
                });
                base.Context = db;
                var configId = typeof(T).GetCustomAttribute<TenantAttribute>().configId;
                db.ChangeDatabase(configId);
            }
        }
 
        /// <summary>
        /// 扩展方法，自带方法不能满足的时候可以添加新方法
        /// </summary>
        /// <returns></returns>
        public List<T> CommQuery(string sql)
        {
            //base.Context.Queryable<T>().ToList();可以拿到SqlSugarClient 做复杂操作
            return base.Context.Queryable<T>().Where(sql).ToList();
        }
 
    }

1
2
3
4
5
6
7
8
9
10
11
12
public class C1Service : Repository<C1Table>
{
    public void Test()
    {
        base.AsTenant().BeginTran();
 
        base.GetList(); //调用内部仓储方法
        base.ChangeRepository<Repository<C2Table>>().GetList();//调用外部仓储
 
        base.AsTenant().CommitTran();
    }
}

1
2
3
4
5
6
7
var test06 = db.Queryable<Order>()
                   .ToPivotTable(it => it.Id, it => it.Name, it => it.Sum(x => x.Price));//返回Datatable
 
 var test07 = db.Queryable<Order>()
            .ToPivotList(it => it.Id, it => it.Name, it => it.Sum(x => x.Price));//返回List<dynamic><br>
var test08 = db.Queryable<Order>()
            .ToPivotJson(it => it.Id, it => it.Name, it => it.Sum(x => x.Price));//返回Json