1、创建数据
|
1 2 |
//如果不存在创建数据库 db.DbMaintenance.CreateDatabase(); |
2、创建表
|
1 2 3 4 5 6 7 8 9 10 11 |
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、更多用例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
//修改列 [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.生成实体并且带有筛选
|
1 2 3 |
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新的格式
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.格式化类名和属性名
|
1 2 3 4 5 6 7 8 9 10 |
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”); |
生成的实体如下
|
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 40 41 42 43 44 |
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模版
这种方式什么样的格式都可以满足你，你自已写解析格式
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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
|
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 |
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或者存储过程一起打包可能会不支持
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
//将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>(); |
分库分表
注意：数据库连接账号要高级账号
1、动态创建数据库
下面代码将会创建db1和db2数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 两张表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
//注意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、实体增、删、查、改
|
1 2 |
var list= db.Queryable().AS(“Order1”).ToList();//查询Order1的表 //增 删 改用法和查询一样 Inasertable().AS Deleteable().AS Updateable().As |
4、跨库联表查询
|
1 2 3 4 5 6 |
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、多库切换
|
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 |
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(); } |
