使用BulkInserts批量写数据到临时表原创
85人赞赏了该文章
902次浏览
编辑于2023年03月31日 23:47:48
有时需要从数据库 按多个字段 组合查询大量数据,我知道的更好的办法是 写组合条件数据到临时表, 再关联查询
创建临时表:示例为 按物料+批号查数据
private void CreateMaterialLotTable(string tableName) { StringBuilder builder = new StringBuilder(); builder.AppendLine(string.Format("/*dialect*/ CREATE TABLE {0} ( ", tableName)); builder.AppendLine("FMaterialID bigint null,"); //物料ID builder.AppendLine("FLOT bigint null"); //批号 builder.AppendLine(" ) "); //创建索引 builder.AppendLine($@"CREATE UNIQUE CLUSTERED INDEX [IDX_MaterialLot] ON [{tableName}]( [FMaterialID] ASC, [FLOT] ASC)"); DBUtils.Execute(this.Context, builder.ToString()); builder.Clear(); }
使用DataTable批量写入数据
private void InitialMaterialLotTable(string tmpMaterialLot, DynamicObjectCollection docInStockAdjustBill) { CreateMaterialLotTable(tmpMaterialLot); //构建临时表DATaTable DataTable data = new DataTable(); data.TableName = tmpMaterialLot; data.Columns.Add("FMaterialID", typeof(long)); //物料ID long data.Columns.Add("FLOT", typeof(long)); //批号ID long //按物料ID、批号ID分组 var varMaterialLots = docInStockAdjustBill.GroupBy(o => new { FMATERIALID = Convert.ToInt64(o["FMATERIALID"]), FLOT = Convert.ToInt64(o["FLOT"]) }) .Select(g => new { g.Key.FMATERIALID, g.Key.FLOT }); DataRow newRow; foreach (var varMaterialLot in varMaterialLots) { newRow = data.NewRow(); newRow["FMaterialID"] = varMaterialLot.FMATERIALID == 0 ? 0 : varMaterialLot.FMATERIALID; newRow["FLOT"] = varMaterialLot.FLOT == 0 ? 0 : varMaterialLot.FLOT; data.Rows.Add(newRow); } DBServiceHelper.BulkInserts(this.Context, string.Empty, string.Empty, data); }
有一个待验证的性能项:是创建表的时候就创建索引,还是写入完数据再创建索引 , 或者是写入数据前禁用索引,写完后再禁用用索引,待找大数据测试验证。