Long time to insert into SQL Server 2008 R2

See more:

C#5.0

SQL-Server-2008R2

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.

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.

Real Scenario:
Promotion will be created for some items between specific date range let say between 26-june-2015 to 30-june-2015 so once again same item shouldn't fall for new promotion so i am giving Db hit each and every time to check desired item has already been on promotion if not then i am inserting else those items will be skipped ...like wise totally i have 200,000 records to check
How can i optimize the below code or new any new way to accomplish the task

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
                    }
                }
            }
        }
    }
}