/// <summary> /// Создаёт от открывает новое соединение с БД /// </summary> /// <returns></returns> public static SqlConnection GetDatabaseConnection() { SqlConnection cnt = null; try { dsParserTableAdapters.tInventItemsTableAdapter ta = new dsParserTableAdapters.tInventItemsTableAdapter(); string connectionString = ta.Connection.ConnectionString; cnt = new SqlConnection(connectionString); cnt.Open(); } catch { cnt = null; } return(cnt); }
/// <summary> /// Выполнение процедуры экспорта данных /// </summary> private void StartExport() { SqlConnection cnt = Site.GetDatabaseConnection(); if (cnt != null && cnt.State == System.Data.ConnectionState.Open) { dsParserTableAdapters.tInventItemsTableAdapter taItems = new dsParserTableAdapters.tInventItemsTableAdapter(); int itemsCount; try { itemsCount = (int)taItems.ItemsCount(); } catch { itemsCount = 0; } // Работаем с товарами через SqlDataReader, поскольку в выборке очень много записей string query = @"SELECT * FROM [dbo].[tInventItems]"; SqlCommand cndInvent = new SqlCommand(query, cnt); SqlDataReader rdrInvent = cndInvent.ExecuteReader(); if (rdrInvent.HasRows) { FileRow exportRow; int exportedItems = 0; string logMessage = @"{0} {1} из {2}"; string logCaption; while (rdrInvent.Read() && IsActive && (!ExportFragments || exportedItems < PreviousFragment + FragmentSize)) { List <ImageResource> imgList = GetImagesList((int)rdrInvent["Id"]); exportRow = new FileRow(); exportRow.XmlId = (int)rdrInvent["Id"]; exportRow.Name = rdrInvent["Name"].ToString().Replace(";", " ").Replace("\n", " ").Replace("\r", " ").Replace(@"""", " "); exportRow.PreviewText = rdrInvent["ShortDescr"].ToString().Replace(";", " ").Replace("\n", " ").Replace("\r", " ").Replace(@"""", " "); exportRow.DetailText = rdrInvent["Descr"].ToString().Replace(";", " ").Replace("\n", " ").Replace("\r", " ").Replace(@"""", " "); exportRow.ArtNumber = @"SMR-" + rdrInvent["Id"].ToString().Trim(); exportRow.Price = (decimal)rdrInvent["Price"]; FillOptions(ref exportRow); FillGroups(ref exportRow, (int)rdrInvent["ParentId"]); if (!ExportFragments || exportedItems >= PreviousFragment) { // Выгрузка с учётом изображений if (imgList.Count > 0) { foreach (ImageResource img in imgList) { if (img.LocalImageFile != null && img.LocalImageFile.Exists) { //string remoteFileName = @"invent_export/" + img.LocalImageFile.Name; string remoteFileName = img.LocalImageFile.Name; exportRow.MorePhoto = remoteFileName; ExportFile.FileWriter.WriteLine(exportRow.ToString()); } } } else { ExportFile.FileWriter.WriteLine(exportRow.ToString()); } logCaption = @"Экспортировано"; } else { logCaption = @"Пропущено"; } // Формирование сообщения пользователю exportedItems++; if (exportedItems % 100 == 0) { Log = string.Format(logMessage, logCaption, exportedItems, itemsCount); } } // Фиксируем значение границы выгрузки PreviousFragment = exportedItems >= itemsCount ? 0 : exportedItems; } rdrInvent.Close(); cnt.Close(); } }
/// <summary> /// Записывает данные товара в БД /// </summary> public void RegisterInventItem() { if (Url.Length > 0 && Parent != null) { dsParserTableAdapters.tInventItemsTableAdapter taInventItems = new dsParserTableAdapters.tInventItemsTableAdapter(); dsParser.tInventItemsDataTable tblInventItems = taInventItems.GetDataByUrl(Url); dsParser.tInventItemsRow rowInventItems; if (tblInventItems.Rows.Count > 0) { rowInventItems = (dsParser.tInventItemsRow)tblInventItems.Rows[0]; } else { rowInventItems = tblInventItems.NewtInventItemsRow(); rowInventItems.Url = Url; } // Обновление данных товара rowInventItems.ParentId = Parent.Id; rowInventItems.Name = Name; rowInventItems.ShortDescr = ShortDescr; rowInventItems.Descr = Descr; rowInventItems.OldPrice = (decimal)OldPrice; rowInventItems.Price = (decimal)Price; if (rowInventItems.RowState == System.Data.DataRowState.Detached) { tblInventItems.Rows.Add(rowInventItems); } taInventItems.Update(tblInventItems); // Сохранение данных о картинках dsParserTableAdapters.tInventImagesTableAdapter taImages = new dsParserTableAdapters.tInventImagesTableAdapter(); dsParser.tInventImagesDataTable tblImages; dsParser.tInventImagesRow rowImages; foreach (string imageUrl in Images) { tblImages = taImages.GetDataByUrl(rowInventItems.Id, imageUrl); if (tblImages.Rows.Count == 0) { rowImages = tblImages.NewtInventImagesRow(); rowImages.InventItemId = rowInventItems.Id; rowImages.Url = imageUrl; tblImages.Rows.Add(rowImages); taImages.Update(tblImages); } } // Сохранение данных о характеристиках dsParserTableAdapters.tInventOptionsTableAdapter taOptions = new dsParserTableAdapters.tInventOptionsTableAdapter(); dsParser.tInventOptionsDataTable tblOptions; dsParser.tInventOptionsRow rowOptions; foreach (InventItemOption optionItem in Options) { tblOptions = taOptions.GetDataByName(rowInventItems.Id, optionItem.Name); if (tblOptions.Rows.Count > 0) { rowOptions = (dsParser.tInventOptionsRow)tblOptions.Rows[0]; } else { rowOptions = tblOptions.NewtInventOptionsRow(); rowOptions.InventItemId = rowInventItems.Id; rowOptions.Name = optionItem.Name; } rowOptions.Value = optionItem.Value; if (rowOptions.RowState == System.Data.DataRowState.Detached) { tblOptions.Rows.Add(rowOptions); } taOptions.Update(tblOptions); } } }