目录
开头配置代码
ConnectionConfig connectionConfig = new ConnectionConfig()
{
ConnectionString = SqlSugarConnectionString.GetConnectionString1(),
IsAutoCloseConnection = true,
DbType = DbType.SqlServer
};
Student student = new Student()
{
Name = "返回自增列",
SchoolId = 1,
CreateTime = DateTime.Now
};
Snowflake snowflakeModel = new Snowflake()
{
Name = "返回雪花ID",
SchoolId = 1
};单条插入
if (db.DbMaintenance.IsAnyTable("Student"))
{
db.DbMaintenance.DropTable("Student");
}
if (db.DbMaintenance.IsAnyTable("Snowflake"))
{
db.DbMaintenance.DropTable("Snowflake");
}
///初始化表
db.CodeFirst.InitTables(typeof(Snowflake), typeof(Student));
//删除学生，插入学生，返回自增ID
db.Deleteable<Student>().ExecuteCommand();
var sid = db.Insertable<Student>(student).ExecuteReturnIdentity();
//删除雪花，插入雪花，返回雪花ID
db.Deleteable<Snowflake>().ExecuteCommand();
var nid = db.Insertable<Snowflake>(snowflakeModel).ExecuteReturnSnowflakeId();
db.Deleteable<Student>().ExecuteCommand();
//实用技巧2： 强制设置表名（默认表名来自实体）
db.Insertable(student).AS("Student").ExecuteCommand();
db.Deleteable<Student>().ExecuteCommand();
//字典插入 //不能写数据库中的字段名，必须和实体属性保持一致
Dictionary<string, object> dc = new Dictionary<string, object>();
dc.Add("SchoolId", 3);
dc.Add("Name", "字典插入");
dc.Add("CreateTime", "2023/1/24");
db.Insertable<Student>(dc).ExecuteCommand();
db.Deleteable<Student>().ExecuteCommand();
//匿名对象 插入
var obj = new
{
StudentName = "匿名对象 插入",
CreateTime = DateTime.Now,
SchoolId = "456"
};
db.Insertable<Dictionary<string, object>>(obj).AS("Student").ExecuteCommand();
db.Deleteable<Student>().ExecuteCommand();忽略或者指定插入
忽略插入某列
db.Deleteable<Student>().ExecuteCommand();
db.Insertable(student).IgnoreColumns(s => s.Name).ExecuteCommand();直插入某列的数据
db.Deleteable<Student>().ExecuteCommand();
db.Insertable(student).InsertColumns(s => s.Name).ExecuteCommand();批量插入
db.Deleteable<Student>().ExecuteCommand();
List<Student> students = new List<Student>();
for (int i = 0; i < 100; i++)
{
students.Add(new Student()
{
Name = $"StudentName_{i}",
SchoolId = i,
CreateTime = DateTime.Now
});
}
//(1)、非参数化插入（防注入）
//优点：综合性能比较平均，列少1万条也不慢，属于万写法,不加事务情况下部分库有失败回滚机质
//缺点：数据量超过5万以上占用内存会比较大些，内存小可以用下面2种方式处理
db.Insertable(students).ExecuteCommand();
//(2)、使用参数化内部分页插入
//优点：500条以下速度最快，兼容所有类型和emoji，10万以上的大数据也能跑，就是慢些，内部分批量处理过了。
//缺点：500以上就开始慢了，要加事务才能回滚
db.Insertable(students).UseParameter().ExecuteCommand();
//(3)、大数据写入（特色功能：大数据处理上比所有框架都要快30%）
//优点：1000条以上性能无敌手
//缺点：不支持数据库默认值， API功能简单， 小数据量并发执行不如普通插入，插入数据越大越适合用这个
//新功能 5.0.44
db.Fastest<Student>().PageSize(100000).BulkCopy(students);大数据插入演示
List<Student> students = new List<Student>();
for (int i = 0; i < 10000; i++)
{
students.Add(new Student()
{
Name = $"Name_{i}",
SchoolId = i,
CreateTime = DateTime.Now
});
;
}
db.Deleteable<Student>().ExecuteCommand();
Stopwatch stopwatch = Stopwatch.StartNew();
stopwatch.Start();
db.Insertable<Student>(students).ExecuteCommand();
stopwatch.Stop();
Console.WriteLine($"普通方式：1000000条数据大概用时：{stopwatch.ElapsedMilliseconds} 毫秒");
db.Deleteable<Student>().ExecuteCommand();
stopwatch.Reset();
stopwatch.Start();
db.Fastest<Student>().BulkCopy(students);
stopwatch.Stop();
Console.WriteLine($"BulkCopy大数据操作：1000000条数据大概用时：{stopwatch.ElapsedMilliseconds} 毫秒");分页插入
if (db.DbMaintenance.IsAnyTable("Student"))
{
db.DbMaintenance.DropTable("Student");
}
db.CodeFirst.InitTables(typeof(Student));
List<Student> students = new List<Student>();
for (int i = 0; i < 100; i++)
{
students.Add(new Student()
{
Name = $"StudentName_{i}",
SchoolId = i,
CreateTime = DateTime.Now
});
}
db.Utilities.PageEach(students, 10, pageList =>
{
db.Insertable(pageList).ExecuteCommand();
});Guid主键自动赋值
含有主键为Guid的插入，会进行自动对Guid赋值
if (db.DbMaintenance.IsAnyTable("UserInfo"))
{
db.DbMaintenance.DropTable("UserInfo");
}
db.CodeFirst.InitTables(typeof(UserInfo));
UserInfo userInfo = new UserInfo()
{
Name = "Zhang",
CreateTime = DateTime.Now
};
db.Insertable(userInfo).ExecuteCommand();使用实体的时候可以调用它里面的方法
db.CodeFirst.InitTables(typeof(UnitInsertMethod));
db.Deleteable<UnitInsertMethod>().ExecuteCommand();
db.Insertable(new UnitInsertMethod() { Name = "1" })
.CallEntityMethod(it => it.Create())
.ExecuteCommand();
db.Insertable(new UnitInsertMethod() { Name = "1" })
.CallEntityMethod(it => it.Modify("Test"))
.ExecuteCommand();插入临时表
db.Deleteable<UserInfo>().ExecuteCommand();
UserInfo userInfo = new UserInfo()
{
Name = "Zhang",
CreateTime = DateTime.Now
};
db.Insertable(userInfo).ExecuteCommand();
db.Ado.OpenAlways();
db.Queryable<UserInfo>().Select(" * into #temp").ToList();
var tempList = db.Queryable<dynamic>().AS("#temp").ToList();