I have over 200,000 records in c# Winforms gridview, it takes around an hour to get inserted into my database. I'm trying to improve the performance of this insert. I'm looking to insert all of the records within 5 to 10 minutes.
我在c#Winforms gridview中有超过200,000条记录,大约需要一个小时才能插入到我的数据库中。我正在努力提高这种插入的性能。我想在5到10分钟内插入所有记录。
I am using For loop to populate each and every row to get insert into DB with a SQL transactions and I don't think that SqlBulkCopy will work out because all 200,000 records needs to be validated with the DB before insertion into DB.
我正在使用For循环来填充每一行以使用SQL事务插入到DB中,并且我认为SqlBulkCopy不会运行,因为在插入DB之前需要使用DB验证所有200,000条记录。
Save-Function:
if (chkretailprice.Checked)
{
DataTable dt_grid = (DataTable)gcPromotion.DataSource;
dt_grid.AcceptChanges();
for (int tt = 0; tt < gvPromotion.RowCount; tt++)
{
gvPromotion.FocusedRowHandle = tt;
double dRGridMinus = Convert.ToDouble(gvPromotion.GetRowCellValue(tt, gvPromotion.Columns["PromotionalRetailPrice"]));
string sItem = Convert.ToString(gvPromotion.GetRowCellValue(tt, gvPromotion.Columns["ItemName"]).ToString());
string sPack = Convert.ToString(gvPromotion.GetRowCellValue(tt, gvPromotion.Columns["Package"]).ToString());
if (dRGridMinus < 0)
{
gvPromotion.FocusedRowHandle = tt;
MessageBoxInfo("Promotional RetailPrice contains Negative Values for this ItemName-'" + sItem + "' & Package-'" + sPack + "'");
gvPromotion.Focus();
return;
}
}
int iReCount = dt_grid.Select("PromotionalRetailPrice='0.00'").Length;
if (iReCount != 0)
{
MessageBoxInfo("Promotional RetailPrice Must not be 0");
gvPromotion.Focus();
return;
}
}
if (rgPromotion.Checked)
{
for (int p = 0; p < gvPromotion.RowCount; p++)
{
string[] sbranchArr = sBranchIDs.Split(',');
for (int pp = 0; pp < sbranchArr.Length; pp++)
{
objProEntity.PromotionMasterId = objProEntity.PromotionMasterId;
objProEntity.BranchId = Convert.ToInt32(sbranchArr[pp]);//gvPromotion.GetRowCellValue(p, gvPromotion.Columns["BranchID"]));
objProEntity.ItemId = Convert.ToInt64(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ItemID"]));
objProEntity.PackId = Convert.ToInt32(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PackTypeID"]));
objProEntity.PromotionValueType = Convert.ToString(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionValueType"]));
objProEntity.PromotionValue = Convert.ToString(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionValue"]));
if (chkretailprice.Checked && chkwholesaleprice.Checked)// when both retailprice & wholesaleprice checkbox is checked
{
objProEntity.ActualRetailPrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualRetailPrice"]));
objProEntity.PromoRetailPrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionalRetailPrice"]));
objProEntity.ActualWholeSalePrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualWholeSalePrice"]));
objProEntity.PromoWholesalePrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionalWholeSalePrice"]));
}
else if (chkretailprice.Checked)// when retailprice checkbox is checked
{
objProEntity.ActualRetailPrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualRetailPrice"]));
objProEntity.PromoRetailPrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionalRetailPrice"]));
objProEntity.ActualWholeSalePrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualWholeSalePrice"]));
objProEntity.PromoWholesalePrice = Convert.ToDecimal(0);
}
else if (chkwholesaleprice.Checked)// when wholesaleprice checkbox is checked
{
objProEntity.ActualRetailPrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualRetailPrice"]));
objProEntity.PromoRetailPrice = Convert.ToDecimal(0);
objProEntity.ActualWholeSalePrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualWholeSalePrice"]));
objProEntity.PromoWholesalePrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionalWholeSalePrice"]));
}
objProEntity.DiscountAllowed = Convert.ToBoolean(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["DiscountAllowed"]));
DataTable dt_Check = new DataTable();
dt_Check = SalesPromotionData.IsCheckItemExists(objProEntity, SQLTrans);
if (dt_Check.Rows.Count == 0)
{
if (!IsEdit)
{
DataTable dt_child = SalesPromotionData.InsertChildData(objProEntity, SQLTrans); // Insert Child Details when isEdit=false
}
else
{
if (gvPromotion.Columns.Contains(gvPromotion.Columns["PromotionChildId"]))
if ((DBNull.Value.Equals(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionChildId"]))) || (gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionChildId"]) == "") || Convert.ToString(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionChildId"]).ToString()) == "0")
{
objProEntity.PromotionMasterId = masterid;
SalesPromotionData.InsertChildData(objProEntity, SQLTrans);// insert child details when isEdit=true
}
else
{
objProEntity.PromotionChildId = Convert.ToInt64(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionChildId"]).ToString());
SalesPromotionData.UpdateChildDetails(objProEntity, SQLTrans); // update child details when isEdit=true
}
else
{
objProEntity.PromotionMasterId = masterid;
SalesPromotionData.InsertChildData(objProEntity, SQLTrans);// insert child details when isEdit=true
}
}
}
}
}
}
1 个解决方案
#1
1
Normally, you'd stage your data into the database by bulk inserting it into [a] work table(s), with no referential integrity or anything -- just the raw data plus any [non-unique] indices you might need. Once you've got it staged, you can then:
通常,您可以通过将数据大量插入到[a]工作表中来将数据存储到数据库中,而无需引用完整性或任何内容 - 只需原始数据加上您可能需要的任何[非唯一]索引。一旦你上演了它,你就可以:
- Validate the data in the work table(s) against your database and
- apply it to the "real" tables in question.
根据您的数据库验证工作表中的数据
将其应用于相关的“真实”表格。
#1
1
Normally, you'd stage your data into the database by bulk inserting it into [a] work table(s), with no referential integrity or anything -- just the raw data plus any [non-unique] indices you might need. Once you've got it staged, you can then:
通常,您可以通过将数据大量插入到[a]工作表中来将数据存储到数据库中,而无需引用完整性或任何内容 - 只需原始数据加上您可能需要的任何[非唯一]索引。一旦你上演了它,你就可以:
- Validate the data in the work table(s) against your database and
- apply it to the "real" tables in question.
根据您的数据库验证工作表中的数据
将其应用于相关的“真实”表格。