SqlSugar ORM 定义索引等高级功能

2024 年 4 月
一	二	三	四	五	六	日
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

1、创建数据

//如果不存在创建数据库
db.DbMaintenance.CreateDatabase();

1 2	//如果不存在创建数据库 db.DbMaintenance.CreateDatabase();

2、创建表

public class CodeFirstTable1
{
[SugarColumn(IsIdentity = true, IsPrimaryKey = true)]
public int Id { get; set; }
public string Name { get; set; }
[SugarColumn(ColumnDataType = “Nvarchar(255)”)]//自定格式的情况 length不要设置
public string Text { get; set; }
[SugarColumn(IsNullable = true)]
public DateTime CreateTime { get; set; }
}
db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(CodeFirstTable1));//这样一个表就能成功创建了

public class CodeFirstTable1

{

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

public int Id { get; set; }

public string Name { get; set; }

[SugarColumn(ColumnDataType = “Nvarchar(255)”)]//自定格式的情况 length不要设置

public string Text { get; set; }

[SugarColumn(IsNullable = true)]

public DateTime CreateTime { get; set; }

}

db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(CodeFirstTable1));//这样一个表就能成功创建了

3、描述

名称描述
IsIdentity 是否创建自增标识
IsPrimaryKey 是否创建主键标识
ColumnName 创建数据库字段的名称（默认取实体类属性名称）
ColumnDataType
创建数据库字段的类型

用法1： “varchar(20)” 不需要设置长度

用法2: 不设置该参数系统会根据C#类型自动生成相应的数据库类型

用法3: “nvarchar(20),text” 多个以逗号隔开例如 B数据库不支持nvarchar那么会选择text

IsIgnore ORM不处理该列
IsOnlyIgnoreInsert 插入操作时不处理该列
ColumnDescription 备注
Length 长度
IsNullable 是否可以为null默为false
DecimalDigits 精度如 decimal(18,2) length=18,DecimalDigits=2
OracleSequenceName 设置Oracle序列，设置后该列等同于自增列
OldColumnName 修改列名用，这样不会新增或者删除列
IndexGroupNameList 创建索引用
UniqueGroupNameList 创建唯一索引

4、更多用例

//修改列

[SugarColumn( OldColumnName =“Name”)]
public string NewName { get; set; }

//创建索引

[SqlSugar.SugarColumn(IndexGroupNameList = new string[] { “index1” })]
public int V1 { get; set; }
[SqlSugar.SugarColumn(IndexGroupNameList =new string[] { “index1” } )]
public DateTime? V2 { get; set; }
//等同create index index1_v1_v2 ON table_name (v1,v2)

//唯一索引用法类似

//添加表描述

db.CodeFirst.InitTables(typeof(CodeFirstTable));
[SugarTable(“CodeFirstTable2”,TableDescription = “TableDescription”)]
public class CodeFirstTable
{
[SugarColumn(IsPrimaryKey = true)]
public Guid Id { get; set; }
}

//修改列

[SugarColumn( OldColumnName =“Name”)]

public string NewName { get; set; }

//创建索引

[SqlSugar.SugarColumn(IndexGroupNameList = new string[] { “index1” })]

public int V1 { get; set; }

[SqlSugar.SugarColumn(IndexGroupNameList =new string[] { “index1” } )]

public DateTime? V2 { get; set; }

//等同create index index1_v1_v2 ON table_name (v1,v2)

//唯一索引用法类似

//添加表描述

db.CodeFirst.InitTables(typeof(CodeFirstTable));

[SugarTable(“CodeFirstTable2”,TableDescription = “TableDescription”)]

public class CodeFirstTable

{

[SugarColumn(IsPrimaryKey = true)]

public Guid Id { get; set; }

}

生成实体
1.生成实体到指定目录

db.DbFirst.CreateClassFile(“c:\Demo\1”, “Models”);

2.生成实体并且带有筛选

db.DbFirst.Where(“Student”).CreateClassFile(“c:\Demo\2”, “Models”);
db.DbFirst.Where(it => it.ToLower().StartsWith(“view”)).CreateClassFile(“c:\Demo\3”, “Models”);
db.DbFirst.Where(it => it.ToLower().StartsWith(“view”)).CreateClassFile(“c:\Demo\4”, “Models”);

db.DbFirst.Where(“Student”).CreateClassFile(“c:\Demo\2”, “Models”);

db.DbFirst.Where(it => it.ToLower().StartsWith(“view”)).CreateClassFile(“c:\Demo\3”, “Models”);

db.DbFirst.Where(it => it.ToLower().StartsWith(“view”)).CreateClassFile(“c:\Demo\4”, “Models”);

3.生成带有SqlSugar特性的实体

db.DbFirst.IsCreateAttribute().CreateClassFile(“c:\Demo\5”, “Models”);

4.生成实体带有默认值

db.DbFirst.IsCreateDefaultValue().CreateClassFile(“c:\Demo\6”, “Demo.Models”);

5.重写生成格式(如果还满足不了需求可以使用最下面的自定义Razor模版)

打断点查看old的格式修改后return新的格式

db.DbFirst. SettingClassTemplate(old => { return old;})
.SettingNamespaceTemplate(old =>{ return old;})//命名空间
.SettingPropertyDescriptionTemplate(old => //备注
{
return @" ///
/// Desc_New:{PropertyDescription}
/// Default_New:{DefaultValue}
/// Nullable_New:{IsNullable}
/// ";
})
.SettingPropertyTemplate(old =>{return old;})//属性
.SettingConstructorTemplate(old =>{return old; })//构造函数
.CreateClassFile(“c:\Demo\7”);

db.DbFirst. SettingClassTemplate(old => { return old;})

.SettingNamespaceTemplate(old =>{ return old;})//命名空间

.SettingPropertyDescriptionTemplate(old => //备注

{

return @" ///

/// Desc_New:{PropertyDescription}

/// Default_New:{DefaultValue}

/// Nullable_New:{IsNullable}

/// ";

})

.SettingPropertyTemplate(old =>{return old;})//属性

.SettingConstructorTemplate(old =>{return old; })//构造函数

.CreateClassFile(“c:\Demo\7”);

6.格式化类名和属性名

foreach (var item in db.DbMaintenance.GetTableInfoList())
{
string entityName = item.Name.ToUpper();/实体名大写/
db.MappingTables.Add(entityName , item.Name);
foreach (var col in db.DbMaintenance.GetColumnInfosByTableName(item.Name))
{
db.MappingColumns.Add(col.DbColumnName.ToUpper() /类的属性大写/, col.DbColumnName, entityName);
}
}
db.DbFirst.IsCreateAttribute().CreateClassFile(“c:\Demo\8”, “Models”);

foreach (var item in db.DbMaintenance.GetTableInfoList())

{

string entityName = item.Name.ToUpper();/实体名大写/

db.MappingTables.Add(entityName , item.Name);

foreach (var col in db.DbMaintenance.GetColumnInfosByTableName(item.Name))

{

db.MappingColumns.Add(col.DbColumnName.ToUpper() /类的属性大写/, col.DbColumnName, entityName);

}

db.DbFirst.IsCreateAttribute().CreateClassFile(“c:\Demo\8”, “Models”);

生成的实体如下

using System;
using System.Linq;
using System.Text;
using SqlSugar;
namespace Models
{
///
///
///
[SugarTable(“Order”)]
public partial class ORDER
{
public ORDER(){
}
///
/// Desc:
/// Default:
/// Nullable:False
///
[SugarColumn(IsPrimaryKey=true,IsIdentity=true,ColumnName=“Id”)]
public int ID {get;set;}
///
/// Desc:
/// Default:
/// Nullable:False
///
[SugarColumn(ColumnName=“Name”)]
public string NAME {get;set;}
///
/// Desc:
/// Default:
/// Nullable:False
///
[SugarColumn(ColumnName=“Price”)]
public decimal PRICE {get;set;}
///
/// Desc:
/// Default:
/// Nullable:True
///
[SugarColumn(ColumnName=“CreateTime”)]
public DateTime? CREATETIME {get;set;}
}
}

using System;

using System.Linq;

using System.Text;

using SqlSugar;

namespace Models

{

///

[SugarTable(“Order”)]

public partial class ORDER

{

public ORDER(){

}

///

/// Desc:

/// Default:

/// Nullable:False

///

[SugarColumn(IsPrimaryKey=true,IsIdentity=true,ColumnName=“Id”)]

public int ID {get;set;}

///

/// Desc:

/// Default:

/// Nullable:False

///

[SugarColumn(ColumnName=“Name”)]

public string NAME {get;set;}

///

/// Desc:

/// Default:

/// Nullable:False

///

[SugarColumn(ColumnName=“Price”)]

public decimal PRICE {get;set;}

///

/// Desc:

/// Default:

/// Nullable:True

///

[SugarColumn(ColumnName=“CreateTime”)]

public DateTime? CREATETIME {get;set;}

}

8.使用Razor模版

这种方式什么样的格式都可以满足你，你自已写解析格式

public static void Init()
{
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = Config.ConnectionString,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true,
ConfigureExternalServices = new ConfigureExternalServices()
{//创建一个ConfigureExternalServices.cs
RazorService = new RazorService()
}
});
var defaultTemplateString=RazorFirst.DefaultRazorClassTemplate;//复制默认的Razor模版进行修改
db.DbFirst.UseRazorAnalysis(defaultTemplateString).CreateClassFile(“c:\Demo\Razor\”);

public static void Init()

{

SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()

{

ConnectionString = Config.ConnectionString,

DbType = DbType.SqlServer,

IsAutoCloseConnection = true,

ConfigureExternalServices = new ConfigureExternalServices()

{//创建一个ConfigureExternalServices.cs

RazorService = new RazorService()

}

});

var defaultTemplateString=RazorFirst.DefaultRazorClassTemplate;//复制默认的Razor模版进行修改

db.DbFirst.UseRazorAnalysis(defaultTemplateString).CreateClassFile(“c:\Demo\Razor\”);

ConfigureExternalServices 代码如下：

Nuget需要引用RazorEngine

using RazorEngine;
using RazorEngine.Templating;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SqlSugar.DbFirstExtensions
{
public class RazorService : IRazorService
{
public List<KeyValuePair<string,string>> GetClassStringList(string razorTemplate,Listmodel)
{
if (model != null && model.Any())
{
var result = new List<KeyValuePair<string, string>>();
foreach (var item in model)
{
try
{
item.ClassName = item.DbTableName;//这边可以格式化类名
string key = “RazorService.GetClassStringList”+ razorTemplate.Length;
var classString = Engine.Razor.RunCompile(razorTemplate, key, item.GetType(), item);
result.Add(new KeyValuePair<string,string>(item.ClassName,classString));
}
catch (Exception ex)
{
new Exception(item.DbTableName + " error ." + ex.Message);
}
}
return result;
}
else
{
return new List<KeyValuePair<string, string>> ();
}
}
}
}

using RazorEngine;

using RazorEngine.Templating;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace SqlSugar.DbFirstExtensions

{

public class RazorService : IRazorService

{

public List<KeyValuePair<string,string>> GetClassStringList(string razorTemplate,Listmodel)

{

if (model != null && model.Any())

{

var result = new List<KeyValuePair<string, string>>();

foreach (var item in model)

{

try

{

item.ClassName = item.DbTableName;//这边可以格式化类名

string key = “RazorService.GetClassStringList”+ razorTemplate.Length;

var classString = Engine.Razor.RunCompile(razorTemplate, key, item.GetType(), item);

result.Add(new KeyValuePair<string,string>(item.ClassName,classString));

}

catch (Exception ex)

{

new Exception(item.DbTableName + " error ." + ex.Message);

}

return result;

}

else

{

return new List<KeyValuePair<string, string>> ();

}

打包提交
打包提交默认带有事务

注意：一些自定义的sql或者存储过程一起打包可能会不支持

//将2条插入一起执行
var db = GetInstance();
db.Insertable(new Student() { Name = “a” }).AddQueue();
db.Insertable(new Student() { Name = “b” }).AddQueue();
db.SaveQueues();

//异步提交
db.Insertable(new Student() { Name = “a” }).AddQueue();
db.Insertable(new Student() { Name = “b” }).AddQueue();
db.Insertable(new Student() { Name = “c” }).AddQueue();
db.Insertable(new Student() { Name = “d” }).AddQueue();
var ar = db.SaveQueuesAsync();

//多个操作一起提交
db.Queryable().AddQueue();
db.Queryable().AddQueue();
db.AddQueue(“select * from student where id=@id”, new { id = 1 });
var result2 = db.SaveQueues<Student, School, Student>();

//将2条插入一起执行

var db = GetInstance();

db.Insertable(new Student() { Name = “a” }).AddQueue();

db.Insertable(new Student() { Name = “b” }).AddQueue();

db.SaveQueues();

//异步提交

db.Insertable(new Student() { Name = “a” }).AddQueue();

db.Insertable(new Student() { Name = “b” }).AddQueue();

db.Insertable(new Student() { Name = “c” }).AddQueue();

db.Insertable(new Student() { Name = “d” }).AddQueue();

var ar = db.SaveQueuesAsync();

//多个操作一起提交

db.Queryable().AddQueue();

db.AddQueue(“select * from student where id=@id”, new { id = 1 });

var result2 = db.SaveQueues<Student, School, Student>();

分库分表
注意：数据库连接账号要高级账号

1、动态创建数据库

下面代码将会创建db1和db2数据库

string conn = “server=.;uid=sa;pwd=haosql;database={0}”;
var db = new SqlSugarClient(new ConnectionConfig()
{
DbType = SqlSugar.DbType.SqlServer,
ConnectionString = string.Format(conn, “db1”)，
InitKeyType=InitKeyType.Attribute
});
db.DbMaintenance.CreateDatabase();
var db2 = new SqlSugarClient(new ConnectionConfig()
{
DbType = SqlSugar.DbType.SqlServer,
ConnectionString = string.Format(conn, “db2”)
});
db2.DbMaintenance.CreateDatabase();

string conn = “server=.;uid=sa;pwd=haosql;database={0}”;

var db = new SqlSugarClient(new ConnectionConfig()

{

DbType = SqlSugar.DbType.SqlServer,

ConnectionString = string.Format(conn, “db1”)，

InitKeyType=InitKeyType.Attribute

});

db.DbMaintenance.CreateDatabase();

var db2 = new SqlSugarClient(new ConnectionConfig()

{

DbType = SqlSugar.DbType.SqlServer,

ConnectionString = string.Format(conn, “db2”)

});

db2.DbMaintenance.CreateDatabase();

2、动态建表

下面代码将生成生Order1和Order2 两张表

//注意db必须是同一个上下文
var db = new SqlSugarClient(new ConnectionConfig()
{
DbType = SqlSugar.DbType.SqlServer,
ConnectionString = “.;xxx”,
InitKeyType=InitKeyType.Attribute //这个属性必须这么设置

});

db.MappingTables.Add(typeof(Order).Name, typeof(Order).Name + 1);
db.CodeFirst.InitTables(typeof(Order));
db.MappingTables.Add(typeof(Order).Name, typeof(Order).Name + 2);
db.CodeFirst.InitTables(typeof(Order));

//注意db必须是同一个上下文

var db = new SqlSugarClient(new ConnectionConfig()

{

DbType = SqlSugar.DbType.SqlServer,

ConnectionString = “.;xxx”,

InitKeyType=InitKeyType.Attribute //这个属性必须这么设置

});

db.MappingTables.Add(typeof(Order).Name, typeof(Order).Name + 1);

db.CodeFirst.InitTables(typeof(Order));

db.MappingTables.Add(typeof(Order).Name, typeof(Order).Name + 2);

db.CodeFirst.InitTables(typeof(Order));

3、实体增、删、查、改

var list= db.Queryable().AS(“Order1”).ToList();//查询Order1的表
//增 删 改用法和查询一样 Inasertable().AS Deleteable().AS Updateable().As

1 2	var list= db.Queryable().AS(“Order1”).ToList();//查询Order1的表 //增删改用法和查询一样 Inasertable().AS Deleteable().AS Updateable().As

4、跨库联表查询

var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId)
.AS(“xx.dbo.order”)
.AS(“yy.dbo.OrderItem”)
.AS(“zz.dbo.Custom”)
.Select()
.ToList();

var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId)

.AS(“xx.dbo.order”)

.AS(“yy.dbo.OrderItem”)

.AS(“zz.dbo.Custom”)

.Select()

.ToList();

5、多库切换

SqlSugarClient db = new SqlSugarClient(new List()
{
new ConnectionConfig(){ ConfigId=“1”, DbType=DbType.SqlServer,
ConnectionString=Config.ConnectionString,InitKeyType=InitKeyType.Attribute,IsAutoCloseConnection=true },
new ConnectionConfig(){ ConfigId=“2”, DbType=DbType.MySql,
ConnectionString=Config.ConnectionString4 ,InitKeyType=InitKeyType.Attribute ,IsAutoCloseConnection=true}
});

//库1
try
{
db.BeginTran();
db.Deleteable().ExecuteCommand();

db.ChangeDatabase("2");//使用库2
db.Deleteable<Order>().ExecuteCommand();


db.CommitTran();
1
2
3
4
5
}
catch
{
db.RollbackTran();
}

SqlSugarClient db = new SqlSugarClient(new List()

{

new ConnectionConfig(){ ConfigId=“1”, DbType=DbType.SqlServer,

ConnectionString=Config.ConnectionString,InitKeyType=InitKeyType.Attribute,IsAutoCloseConnection=true },

new ConnectionConfig(){ ConfigId=“2”, DbType=DbType.MySql,

ConnectionString=Config.ConnectionString4 ,InitKeyType=InitKeyType.Attribute ,IsAutoCloseConnection=true}

});

//库1

try

{

db.BeginTran();

db.Deleteable().ExecuteCommand();

db.ChangeDatabase("2");//使用库2

db.Deleteable<Order>().ExecuteCommand();

db.CommitTran();

}

catch

{

db.RollbackTran();

}

分类

近期文章

归档

站点日历

其他操作

应用笔记 · 2023年3月7日

SqlSugar ORM 定义索引等高级功能

您可能还喜欢...

应用笔记 · 2023年3月7日

您可能还喜欢...

Basics of XAML in Easy Samples for Multiplatform Avalonia .NET Framework

DD-WRT AT Asus RT-N16

Java实现多种标准的CRC-32算法