Ejemplo n.º 1
0
    public void SaveDataToDB(string FullPath, string FileName, string excelFileExtension)
    {
        SqlTransaction transaction = null;

        try {
            DataTable dt = null;
            dt = this.GetDataSet(FullPath, excelFileExtension).Tables[0];
            if (dt.Rows.Count <= 1)
            {
                PageUtility.ShowModelDlg(this.Page, "文件中没有任何记录,请重新选择");
                return;
            }
            ShopTableAdapter            TAShop            = new ShopTableAdapter();
            ImportLogTableAdapter       TAImportLog       = new ImportLogTableAdapter();
            ImportLogDetailTableAdapter TAImportLogDetail = new ImportLogDetailTableAdapter();
            MasterDataBLL mdBLL = new MasterDataBLL();

            transaction = TableAdapterHelper.BeginTransaction(TAShop);
            TableAdapterHelper.SetTransaction(TAImportLog, transaction);
            TableAdapterHelper.SetTransaction(TAImportLogDetail, transaction);
            //存储log信息
            ImportDS.ImportLogDataTable logTable = new ImportDS.ImportLogDataTable();
            ImportDS.ImportLogRow       logRow   = logTable.NewImportLogRow();

            int    stuffUserID = ((AuthorizationDS.StuffUserRow)Session["StuffUser"]).StuffUserId;
            string fullname    = this.fileUpLoad.PostedFile.FileName.ToString();
            string tmpFile     = fullname.Remove(0, fullname.LastIndexOf("\\") + 1);
            logRow.FileName     = tmpFile;
            logRow.ImportDate   = DateTime.Now;
            logRow.ImportUserID = stuffUserID;
            logRow.ImportType   = 4;
            logRow.TotalCount   = dt.Rows.Count - 1;
            logRow.SuccessCount = dt.Rows.Count - 1;
            logRow.FailCount    = 0;
            logTable.AddImportLogRow(logRow);
            TAImportLog.Update(logTable);

            //处理每条明细
            ERS.ShopDataTable tbShop = new ERS.ShopDataTable();
            ImportDS.ImportLogDetailDataTable ImportLogDetailTable = new ImportDS.ImportLogDetailDataTable();
            int    row_count  = dt.Rows.Count;
            string errorInfor = string.Empty;
            //  int expenseTypeID = int.Parse(ExpenseTypeDDL.SelectedValue);
            //开始处理每条明细
            for (int i = 1; i <= row_count - 1; i++)
            {
                if (CheckData(dt.Rows[i]) != null)
                {
                    errorInfor = "第" + (i + 1) + "行有错:" + CheckData(dt.Rows[i]);
                    ImportDS.ImportLogDetailRow ImportDetailRow = ImportLogDetailTable.NewImportLogDetailRow();
                    ImportDetailRow.LogID = logRow.LogID;
                    ImportDetailRow.Line  = i + 1;
                    ImportDetailRow.Error = errorInfor;
                    ImportLogDetailTable.AddImportLogDetailRow(ImportDetailRow);
                    logRow.FailCount    = logRow.FailCount + 1;
                    logRow.SuccessCount = logRow.SuccessCount - 1;
                    TAImportLog.Update(logRow);
                    continue;
                }
                else
                {
                    DataRow row           = dt.Rows[i];
                    string  ShopName      = row[0].ToString().Trim();
                    string  ShopNo        = row[1].ToString().Trim();
                    string  CustomerName  = row[2].ToString().Trim();
                    string  ShopLevelName = row[3].ToString().Trim();
                    string  Address       = row[4].ToString().Trim();
                    string  Contacter     = row[5].ToString().Trim();
                    string  Telephone     = row[6].ToString().Trim();
                    string  Email         = row[7].ToString().Trim();

                    ERS.CustomerDataTable tbCustomer = mdBLL.GetCustomerByCustomerName(CustomerName);

                    if (tbCustomer == null || tbCustomer.Count == 0)
                    {
                        errorInfor = "第" + (i + 1) + "行有错:找不到客户《" + CustomerName + "》";
                        ImportDS.ImportLogDetailRow ImportDetailRow = ImportLogDetailTable.NewImportLogDetailRow();
                        ImportDetailRow.LogID = logRow.LogID;
                        ImportDetailRow.Line  = i + 1;
                        ImportDetailRow.Error = errorInfor;
                        ImportLogDetailTable.AddImportLogDetailRow(ImportDetailRow);
                        logRow.FailCount    = logRow.FailCount + 1;
                        logRow.SuccessCount = logRow.SuccessCount - 1;
                        TAImportLog.Update(logRow);
                        continue;
                    }

                    ERS.ShopLevelDataTable tbShopLevel = mdBLL.GetShopLevelByShopLevelName(ShopLevelName);

                    if (tbShopLevel == null || tbShopLevel.Count == 0)
                    {
                        errorInfor = "第" + (i + 1) + "行有错:找不到门店等级《" + ShopLevelName + "》";
                        ImportDS.ImportLogDetailRow ImportDetailRow = ImportLogDetailTable.NewImportLogDetailRow();
                        ImportDetailRow.LogID = logRow.LogID;
                        ImportDetailRow.Line  = i + 1;
                        ImportDetailRow.Error = errorInfor;
                        ImportLogDetailTable.AddImportLogDetailRow(ImportDetailRow);
                        logRow.FailCount    = logRow.FailCount + 1;
                        logRow.SuccessCount = logRow.SuccessCount - 1;
                        TAImportLog.Update(logRow);
                        continue;
                    }
                    int iCount = (int)new ShopTableAdapter().SearchNameByIns(ShopName, tbCustomer[0].CustomerID, tbShopLevel[0].ShopLevelID);
                    if (iCount > 0)
                    {
                        errorInfor = string.Format("第" + (i + 1) + "行有错:同一客户,同一门店等级({0})下门店名称({1})不能重复!", tbShopLevel[0].ShopLevelName, ShopName);
                        ImportDS.ImportLogDetailRow ImportDetailRow = ImportLogDetailTable.NewImportLogDetailRow();
                        ImportDetailRow.LogID = logRow.LogID;
                        ImportDetailRow.Line  = i + 1;
                        ImportDetailRow.Error = errorInfor;
                        ImportLogDetailTable.AddImportLogDetailRow(ImportDetailRow);
                        logRow.FailCount    = logRow.FailCount + 1;
                        logRow.SuccessCount = logRow.SuccessCount - 1;
                        TAImportLog.Update(logRow);
                        continue;
                    }

                    ERS.ShopRow rowShop = tbShop.NewShopRow();

                    //string ShopName = row[0].ToString().Trim();
                    //string ShopNo = row[1].ToString().Trim();
                    //string CustomerNo = row[2].ToString().Trim();
                    //string CustomerName = row[3].ToString().Trim();
                    //string ShopLevelName = row[4].ToString().Trim();
                    //string Address = row[5].ToString().Trim();
                    //string Contacter = row[6].ToString().Trim();
                    //string Telephone = row[7].ToString().Trim();
                    //string Email = row[8].ToString().Trim();

                    rowShop.ShopName    = ShopName;
                    rowShop.ShopNo      = ShopNo;
                    rowShop.CustomerID  = tbCustomer[0].CustomerID;
                    rowShop.ShopLevelID = tbShopLevel[0].ShopLevelID;
                    rowShop.Address     = Address;
                    rowShop.Contacter   = Contacter;
                    rowShop.Tel         = Telephone;
                    rowShop.Email       = Email;
                    rowShop.IsActive    = true;

                    tbShop.AddShopRow(rowShop);
                    TAShop.Update(rowShop);
                }
            }
            TAImportLog.Update(logRow);
            TAImportLogDetail.Update(ImportLogDetailTable);

            transaction.Commit();
            string returnString = "成功导入" + logRow.SuccessCount.ToString() + "条信息";
            PageUtility.ShowModelDlg(this.Page, returnString);
        } catch (Exception ex) {
            if (transaction != null)
            {
                transaction.Rollback();
            }
            PageUtility.ShowModelDlg(this.Page, "Save Fail!" + ex.ToString());
        } finally {
            if (transaction != null)
            {
                transaction.Dispose();
            }
        }
    }