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(); } } }
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; } CustomerTableAdapter TACustomer = new CustomerTableAdapter(); ImportLogTableAdapter TAImportLog = new ImportLogTableAdapter(); ImportLogDetailTableAdapter TAImportLogDetail = new ImportLogDetailTableAdapter(); MasterDataBLL mdBLL = new MasterDataBLL(); transaction = TableAdapterHelper.BeginTransaction(TACustomer); 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 = 3; logRow.TotalCount = dt.Rows.Count - 1; logRow.SuccessCount = dt.Rows.Count - 1; logRow.FailCount = 0; logTable.AddImportLogRow(logRow); TAImportLog.Update(logTable); //����ÿ����ϸ ERS.CustomerDataTable tbCustomer = new ERS.CustomerDataTable(); 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 CustomerNo = row[0].ToString().Trim(); string CustomerName = row[1].ToString().Trim(); string ProvinceName = row[2].ToString().Trim(); string CityName = row[3].ToString().Trim(); string CustomerTypeName = row[4].ToString().Trim(); string ChannelTypeName = row[5].ToString().Trim(); string OUName = row[6].ToString().Trim(); string BudgetOUName = row[7].ToString().Trim(); ERS.CustomerDataTable tbCustomerTemp = mdBLL.GetCustomerByCustomerName(CustomerName); if (tbCustomerTemp != null && tbCustomerTemp.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.ProvinceDataTable tbProvince = mdBLL.GetProvinceByProvinceName(ProvinceName); //if (tbProvince == null || tbProvince.Count == 0) { // errorInfor = "��" + (i + 1) + "���д���Ҳ���ʡ�ݡ�" + ProvinceName + "��"; // 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.CityDataTable tbCity = mdBLL.GetCityByCityName(CityName); if (tbCity == null || tbCity.Count == 0) { errorInfor = "��" + (i + 1) + "���д���Ҳ������С�" + CityName + "��"; 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.CustomerTypeDataTable tbCustomerType = mdBLL.GetCustomerTypeByCustomerTypeName(CustomerTypeName); if (tbCustomerType == null || tbCustomerType.Count == 0) { errorInfor = "��" + (i + 1) + "���д���Ҳ����ͻ����͡�" + CustomerTypeName + "��"; 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.ChannelTypeDataTable tbChannelType = mdBLL.GetChannelTypeByChannelTypeName(ChannelTypeName); if (tbChannelType == null || tbChannelType.Count == 0) { errorInfor = "��" + (i + 1) + "���д���Ҳ����ͻ�������" + ChannelTypeName + "��"; 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; } AuthorizationDS.OrganizationUnitDataTable ouTable = new OUTreeBLL().GetDataByOrganizationUnitName(OUName); if (ouTable == null || ouTable.Count == 0) { errorInfor = "��" + (i + 1) + "���д��ϵͳ���Ҳ����˲��š�" + OUName + "��"; 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; } if (ouTable.Count > 1) { errorInfor = "��" + (i + 1) + "���д��ϵͳ���ҵ���������ƵIJ��š�" + OUName + "��"; 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; } AuthorizationDS.OrganizationUnitDataTable BudgetOUTable = new OUTreeBLL().GetDataByOrganizationUnitName(BudgetOUName); if (BudgetOUTable == null || BudgetOUTable.Count == 0) { errorInfor = "��" + (i + 1) + "���д��ϵͳ���Ҳ����˲��š�" + BudgetOUName + "��"; 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; } if (BudgetOUTable.Count > 1) { errorInfor = "��" + (i + 1) + "���д��ϵͳ���ҵ���������ƵIJ��š�" + BudgetOUName + "��"; 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.CustomerRow rowCustomer = tbCustomer.NewCustomerRow(); rowCustomer.CustomerNo = CustomerNo; rowCustomer.CustomerName = CustomerName; rowCustomer.CityID = tbCity[0].CityID; rowCustomer.CustomerTypeID = tbCustomerType[0].CustomerTypeID; rowCustomer.ChannelTypeID = tbChannelType[0].ChannelTypeID; rowCustomer.OrganizationUnitID = ouTable[0].OrganizationUnitId; rowCustomer.ApplyOrganizationUnitID = BudgetOUTable[0].OrganizationUnitId; rowCustomer.IsActive = true; tbCustomer.AddCustomerRow(rowCustomer); TACustomer.Update(rowCustomer); } } 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(); } } }