SQL CONSOLE应用程序，优化 [英] SQL CONSOLE APPLICATION, OPTIMIZATION
本文介绍了SQL CONSOLE应用程序，优化的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
嘿所有，
所以我有一个用C＃编写的控制台应用程序，我的印象是我会有多个文件，可以一次插入所有文件。众所周知，事物和规格会发生变化。我有1个文件，包含大约300个记录，需要3个插入来制作一个强大的对象，而不是大约1000个文件，并能够产生新的线程来下载和处理。这显然需要很长时间。我不能将文件保存在文件中并生成一个新线程，因为它们必须按顺序插入。任何想法或帮助将不胜感激
代码：
Hey All,
so I have a console application written in C# and I was under the impression that I would have multiple files and could insert them all at once. As we all know, things and specs change. Instead of having about 1000 files and being able to spawn a new thread to download and process, I have 1 file that contains around 300 records that require 3 inserts to make one potent object. This obviously is taking a very long time. I cant lump records in the file and spawn a new thread as they have to be inserted in order. Any ideas or help would be appreciated
code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Net;
using System.Threading;
using System.Data.SqlClient;
using System.Data;
using System.ComponentModel;
namespace AWG_Special_Insert
{
class Program
{
public class DownloadThreadStartParam
{
public string FileName { get; set; }
public ManualResetEvent ResetEvent { get; set; }
}
static void Main(string[] args)
{
WaitForThreads(DownloadFiles());
}
public static void WaitForThreads(List<downloadthreadstartparam> threads)
{
//make sure the application doesnt close until all the threads are completed
while (threads.Any())
{
threads[0].ResetEvent.WaitOne();
threads.RemoveAt(0);
}
}
public static List<downloadthreadstartparam> DownloadFiles()
{
var threadStartParams = new List<downloadthreadstartparam>();
var Files = GetBatchFiles();
foreach (object newFile in Files)
{
//find if its acatually data or the archive folder
string file = newFile.ToString();
if (file.StartsWith("LBL"))
{
//download the file and process for thread asynchronous insert
DownloadThreadStartParam param = new DownloadThreadStartParam();
param.FileName = newFile.ToString();
param.ResetEvent = new ManualResetEvent(false);
threadStartParams.Add(param); //add to the list of threadstart params -- so we know how to track
Thread downloadThread = new Thread(DownloadFile);
downloadThread.Start(param);
}
}
return threadStartParams;
}
private static string[] GetBatchFiles()
{
string ReturnStr = "";
FtpWebRequest request = (FtpWebRequest)WebRequest.Create("Filler");
request.Method = WebRequestMethods.Ftp.ListDirectory;
StringWriter sw = new StringWriter();
//Get a response
FtpWebResponse response = (FtpWebResponse)request.GetResponse();
Stream responseStream = response.GetResponseStream();
//Convert the response to a string
int ch;
while ((ch = responseStream.ReadByte()) != -1)
ReturnStr = ReturnStr + Convert.ToChar(ch);
//clean up
responseStream.Close();
response.Close();
//split the string by new line
string[] sep = { "\r\n" };
string[] Files = ReturnStr.Split(sep, StringSplitOptions.RemoveEmptyEntries);
return Files;
}
private static void DeleteBatchFiles(string fileName)
{
FtpWebRequest request = (FtpWebRequest)WebRequest.Create("Filler" + fileName);
request.Method = WebRequestMethods.Ftp.DeleteFile;
//Get a response
FtpWebResponse response = (FtpWebResponse)request.GetResponse();
Stream responseStream = response.GetResponseStream();
}
private static void DownloadFile(object param)
{
var threadParam = param as DownloadThreadStartParam;
//Connect to the FTP
string fileName = threadParam.FileName;
//make sure that we are getting the data files not the archive folder
FtpWebRequest request = (FtpWebRequest)WebRequest.Create(FTP Filler);
//Specify the download location
request.Method = WebRequestMethods.Ftp.DownloadFile;
string replaceFile = null;
if (fileName.Contains(".DTA"))
{
replaceFile = fileName.Replace(".DTA", ".txt");
}
else if (fileName.Contains(".DAT"))
{
replaceFile = fileName.Replace(".DAT", ".txt");
}
else
{
replaceFile = fileName;
}
//Initialize the FileStream and specify the locale path
FileStream localFileStream = new FileStream(@"E:\replaceFile", FileMode.Create, FileAccess.Write);
//get a response
WebResponse response = request.GetResponse();
Stream responseStream = response.GetResponseStream();
//create the file
byte[] buffer = new byte[1024];
int bytesRead = responseStream.Read(buffer, 0, 1012);
while (bytesRead != 0)
{
localFileStream.Write(buffer, 0, bytesRead);
bytesRead = responseStream.Read(buffer, 0, 1024);
}
//delete the file after its been downloaded
//request.Method = WebRequestMethods.Ftp.DeleteFile;
//FtpWebResponse responseFileDelete = (FtpWebResponse)request.GetResponse();
//clean up
localFileStream.Close();
response.Close();
responseStream.Close();
//Now we actually want to process the file
InsertIntoTables(replaceFile);
DeleteBatchFiles(fileName);
threadParam.ResetEvent.Set();
}
public static void InsertIntoTables(string FileName)
{
string line = null;
DataParse parse = null;
Level newLevel = null;
int tagTypeId = 0;
int stampID = 0;
string strConnection = @"Filler";
using (StreamReader sr = File.OpenText(FileName))
{
using (SqlConnection conn = new SqlConnection(strConnection))
{
List<sign> signs = new List<sign>();
while ((line = sr.ReadLine()) != null)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
Sign sign = new Sign();
//need to find level
newLevel = SignManager.GetLevelByLevelName(line.Substring(3, 4));
if (newLevel != null)
{
parse = new DataParse();
tagTypeId = Convert.ToInt32(line.Substring(0, 2));
if (tagTypeId == 01)
stampID = parse.StockCode01StampId;
else if (tagTypeId == 02)
stampID = parse.StockCode02StampId;
else if (tagTypeId == 03)
stampID = parse.StockCode03StampId;
else if (tagTypeId == 04)
stampID = parse.StockCode04StampId;
else if (tagTypeId == 05)
stampID = parse.StockCode05StampId;
else if (tagTypeId == 06)
stampID = parse.StockCode06StampId;
else if (tagTypeId == 07)
stampID = parse.StockCode07StampId;
else if (tagTypeId == 08)
stampID = parse.StockCode08StampId;
else if (tagTypeId == 10)
stampID = parse.StockCode10StampId;
else if (tagTypeId == 12)
stampID = parse.StockCode12StampId;
else if (tagTypeId == 15)
stampID = parse.StockCode15StampId;
else if (tagTypeId == 18)
stampID = parse.StockCode18StampId;
else if (tagTypeId == 19)
stampID = parse.StockCode19StampId;
else if (tagTypeId == 20)
stampID = parse.StockCode20StampId;
else if (tagTypeId == 21)
stampID = parse.StockCode21StampId;
else if (tagTypeId == 22)
stampID = parse.StockCode22StampId;
else if (tagTypeId == 23)
stampID = parse.StockCode23StampId;
else if (tagTypeId == 24)
stampID = parse.StockCode24StampId;
else if (tagTypeId == 25)
stampID = parse.StockCode25StampId;
else if (tagTypeId == 33)
stampID = parse.StockCode33StampId;
else if (tagTypeId == 39)
stampID = parse.StockCode39StampId;
else if (tagTypeId == 40)
stampID = parse.StockCode40StampId;
else if (tagTypeId == 44)
stampID = parse.StockCode44StampId;
else if (tagTypeId == 45)
stampID = parse.StockCode45StampId;
else if (tagTypeId == 53)
stampID = parse.StockCode53StampId;
else if (tagTypeId == 61)
stampID = parse.StockCode61StampId;
else if (tagTypeId == 62)
stampID = parse.StockCode62StampId;
else if (tagTypeId == 63)
stampID = parse.StockCode63StampId;
else if (tagTypeId == 66)
stampID = parse.StockCode66StampId;
else if (tagTypeId == 73)
stampID = parse.StockCode73StampId;
else if (tagTypeId == 74)
stampID = parse.StockCode74StampId;
else if (tagTypeId == 77)
stampID = parse.StockCode77StampId;
else if (tagTypeId == 78)
stampID = parse.StockCode78StampId;
else if (tagTypeId == 79)
stampID = parse.StockCode79StampId;
else if (tagTypeId == 80)
stampID = parse.StockCode80StampId;
else if (tagTypeId == 83)
stampID = parse.StockCode83StampId;
else if (tagTypeId == 86)
stampID = parse.StockCode86StampId;
else if (tagTypeId == 87)
stampID = parse.StockCode87StampId;
else if (tagTypeId == 88)
stampID = parse.StockCode88StampId;
else if (tagTypeId == 89)
stampID = parse.StockCode89StampId;
else if (tagTypeId == 92)
stampID = parse.StockCode92StampId;
else if (tagTypeId == 93)
stampID = parse.StockCode93StampId;
else if (tagTypeId == 96)
stampID = parse.StockCode96StampId;
else if (tagTypeId == 98)
stampID = parse.StockCode98StampId;
else if (tagTypeId == 99)
stampID = parse.StockCode99StampId;
parse.ChangeTypeFieldValue = line.Substring(10, 2);
parse.OBIFieldValue = line.Substring(14, 5); //make 5 digits on the upload per MH 10/4
parse.MajorCategoryValue = line.Substring(19, 5);
parse.MinorCategoryValue = line.Substring(24, 5);
parse.ItemCodeValue = line.Substring(41, 6);//per LS and MH 1022 need to make it not strip leading zeros, but only 6 digits, start at 42
parse.ItemDescriptionValue = line.Substring(47, 30);
parse.PackValue = line.Substring(77, 5);
parse.SizeValue = line.Substring(82, 9);
parse.VendorValue = line.Substring(91, 7);
parse.EffectiveMSU = line.Substring(98, 3);
parse.EffectiveRetailValue = line.Substring(101, 6);
parse.SecondEffectiveMSU = line.Substring(107, 3);
parse.SecondRetailValue = line.Substring(110, 6);
parse.UsaveValue = line.Substring(116, 6);
parse.PricePerUnitValue = line.Substring(122, 6);
parse.SaleStartDateValue = line.Substring(128, 6);
parse.SaleEndDateValue = line.Substring(134, 6);
parse.PalletValue = line.Substring(140, 4);
parse.MasterPackValue = line.Substring(144, 4);
parse.TodaysDateValue = line.Substring(150, 6);
parse.AWGSizeIDValue = line.Substring(156, 2);
parse.UPC13Value = line.Substring(158, 13);
parse.LOQValue = line.Substring(171, 3);
DateTime startDate = new DateTime();
DateTime endDate = new DateTime();
string stringStartDate = line.Substring(128, 6);
if (stringStartDate == null)
{
startDate = Convert.ToDateTime(stringStartDate);
}
else
{
startDate = DateTime.Now;
}
string stringEndDate = line.Substring(128, 6);
if (stringEndDate == null)
{
endDate = Convert.ToDateTime(stringStartDate);
}
else
{
endDate = DateTime.Now.AddDays(6);
}
int Quantity = 1;
sign.LevelID = newLevel.LevelID;
sign.SignIsValid = true;
sign.ArbitraryDate = startDate.AddDays(6);
sign.SoftPrint = true;
sign.DepartmentID = 1;
sign.SignLastUpdated = DateTime.Now;
sign.StampID = stampID;
sign.SignTypeID = 8;
sign.SignQuantity = Quantity;
DateTime today = DateTime.Now;
if (newLevel.LevelUser.LevelUserInfoId == 11)
{
if (today.DayOfWeek.Equals(DayOfWeek.Friday))
{
sign.BatchTypeId = 1;
}
//else if (today.DayOfWeek.Equals(DayOfWeek.Saturday))
//{
// sign.BatchTypeId = 1;
//}
//else if (today.DayOfWeek.Equals(DayOfWeek.Sunday))
//{
// sign.BatchTypeId = 1;
//}
//else if (today.DayOfWeek.Equals(DayOfWeek.Monday))
//{
// sign.BatchTypeId = 1;
//}
else if (today.DayOfWeek.Equals(DayOfWeek.Tuesday))
{
sign.BatchTypeId = 1;
}
else if (today.DayOfWeek.Equals(DayOfWeek.Wednesday))
{
sign.BatchTypeId = 2;
}
else if (today.DayOfWeek.Equals(DayOfWeek.Thursday))
{
sign.BatchTypeId = 2;
}
}
else if (newLevel.LevelUser.LevelUserInfoId == 12)
{
if (today.DayOfWeek.Equals(DayOfWeek.Friday))
{
sign.BatchTypeId = 5;
}
//else if (today.DayOfWeek.Equals(DayOfWeek.Saturday))
//{
// sign.BatchTypeId = 5;
//}
//else if (today.DayOfWeek.Equals(DayOfWeek.Sunday))
//{
// sign.BatchTypeId = 5;
//}
//else if (today.DayOfWeek.Equals(DayOfWeek.Monday))
//{
// sign.BatchTypeId = 5;
//}
else if (today.DayOfWeek.Equals(DayOfWeek.Tuesday))
{
sign.BatchTypeId = 5;
}
else if (today.DayOfWeek.Equals(DayOfWeek.Wednesday))
{
sign.BatchTypeId = 6;
}
else if (today.DayOfWeek.Equals(DayOfWeek.Thursday))
{
sign.BatchTypeId = 6;
}
}
else if (newLevel.LevelUser.LevelUserInfoId == 13)
{
if (today.DayOfWeek.Equals(DayOfWeek.Friday))
{
sign.BatchTypeId = 3;
}
//else if (today.DayOfWeek.Equals(DayOfWeek.Saturday))
//{
// sign.BatchTypeId = 5;
//}
//else if (today.DayOfWeek.Equals(DayOfWeek.Sunday))
//{
// sign.BatchTypeId = 5;
//}
//else if (today.DayOfWeek.Equals(DayOfWeek.Monday))
//{
// sign.BatchTypeId = 5;
//}
else if (today.DayOfWeek.Equals(DayOfWeek.Tuesday))
{
sign.BatchTypeId = 3;
}
else if (today.DayOfWeek.Equals(DayOfWeek.Wednesday))
{
sign.BatchTypeId = 4;
}
else if (today.DayOfWeek.Equals(DayOfWeek.Thursday))
{
sign.BatchTypeId = 4;
}
}
//Fill up all the signfields first
if (!String.IsNullOrWhiteSpace(parse.ChangeTypeFieldValue))
{
SignField field1 = new SignField();
field1.FieldID = parse.ChangeTypeFieldID;
field1.SignFieldValue = parse.ChangeTypeFieldValue.TrimStart('' '');
field1.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field1);
}
if (!String.IsNullOrWhiteSpace(parse.EffectiveRetailValue))
{
//LS and MH - need to handle 2nd MSU if its there and make the 2/4 example
SignField field2 = new SignField();
field2.FieldID = parse.EffectiveRetailID;
string effectiveRetail = null;
string before = null;
string after = null;
if (parse.EffectiveRetailValue.Length == 6)
{
before = parse.EffectiveRetailValue.Substring(0, 4);
after = parse.EffectiveRetailValue.Substring(4, 2);
effectiveRetail = before + "." + after;
}
else if (parse.EffectiveRetailValue.Length == 5)
{
before = parse.EffectiveRetailValue.Substring(0, 3);
after = parse.EffectiveRetailValue.Substring(3, 2);
effectiveRetail = before + "." + after;
}
else if (parse.EffectiveRetailValue.Length == 4)
{
before = parse.EffectiveRetailValue.Substring(0, 2);
after = parse.EffectiveRetailValue.Substring(2, 2);
effectiveRetail = before + "." + after;
}
else if (parse.EffectiveRetailValue.Length == 3)
{
before = parse.EffectiveRetailValue.Substring(0, 1);
after = parse.EffectiveRetailValue.Substring(1, 2);
effectiveRetail = before + "." + after;
}
else if (parse.EffectiveRetailValue.Length == 2)
{
after = parse.EffectiveRetailValue.Substring(0, 2);
effectiveRetail = "." + after;
}
string EffectiveTrimmed = effectiveRetail.TrimStart('' '');
if (String.IsNullOrWhiteSpace(parse.EffectiveMSU))
{
field2.SignFieldValue = EffectiveTrimmed;
}
else
{
if (Convert.ToInt32(parse.EffectiveMSU) > 1)
{
field2.SignFieldValue = parse.EffectiveMSU.TrimStart('' '') + "/" + EffectiveTrimmed;
}
else
{
field2.SignFieldValue = EffectiveTrimmed;
}
}
if (EffectiveTrimmed == ".00")
{
//do nothing
}
else if (EffectiveTrimmed == ".0")
{
}
else if (String.IsNullOrEmpty(EffectiveTrimmed))
{
//do nothing
}
else
{
field2.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field2);
}
}
if (!String.IsNullOrWhiteSpace(parse.SecondRetailValue))
{
SignField field3 = new SignField();
field3.FieldID = parse.SecondRetailID;
string SecondEffectiveRetail = null;
string before = null;
string after = null;
string value = parse.SecondRetailValue.TrimStart('' '');
if (parse.SecondRetailValue.Length == 6)
{
before = parse.SecondRetailValue.Substring(0, 4);
after = parse.SecondRetailValue.Substring(4, 2);
SecondEffectiveRetail = before + "." + after;
}
else if (parse.SecondRetailValue.Length == 5)
{
before = parse.SecondRetailValue.Substring(0, 3);
after = parse.SecondRetailValue.Substring(3, 2);
SecondEffectiveRetail = before + "." + after;
}
else if (parse.SecondRetailValue.Length == 4)
{
before = parse.SecondRetailValue.Substring(0, 2);
after = parse.SecondRetailValue.Substring(2, 2);
SecondEffectiveRetail = before + "." + after;
}
else if (parse.SecondRetailValue.Length == 3)
{
before = parse.SecondRetailValue.Substring(0, 1);
after = parse.SecondRetailValue.Substring(1, 2);
SecondEffectiveRetail = before + "." + after;
}
else if (parse.SecondRetailValue.Length == 2)
{
after = parse.SecondRetailValue.Substring(0, 2);
SecondEffectiveRetail = "." + after;
}
string secondEffectiveTrimmed = SecondEffectiveRetail.TrimStart('' '');
if (String.IsNullOrWhiteSpace(parse.SecondEffectiveMSU))
{
field3.SignFieldValue = secondEffectiveTrimmed;
}
else
{
if (Convert.ToInt32(parse.SecondEffectiveMSU) > 1)
{
field3.SignFieldValue = parse.SecondEffectiveMSU.TrimStart('' '') + "/" + secondEffectiveTrimmed;
}
else
{
field3.SignFieldValue = secondEffectiveTrimmed;
}
}
if (secondEffectiveTrimmed == ".00")
{
//do nothing
}
else if (secondEffectiveTrimmed == ".0")
{
}
else if (String.IsNullOrEmpty(secondEffectiveTrimmed))
{
//do nothing
}
else
{
field3.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field3);
}
}
if (!String.IsNullOrWhiteSpace(parse.SizeValue))
{
SignField field4 = new SignField();
field4.FieldID = parse.SizeID;
//need to strip down the lb and others
string FieldValue = null;
if (parse.SizeValue.Contains("LB"))
FieldValue = parse.SizeValue.Replace("LB", "");
else if (parse.SizeValue.Contains("CT"))
FieldValue = parse.SizeValue.Replace("CT", "");
else if (parse.SizeValue.Contains("OZ"))
FieldValue = parse.SizeValue.Replace("OZ", " ");
else if (parse.SizeValue.Contains("Z"))
FieldValue = parse.SizeValue.Replace("Z", "");
else if (parse.SizeValue.Contains("USE"))
FieldValue = parse.SizeValue.Replace("USE", "");
else if (parse.SizeValue.Contains("CARTON"))
FieldValue = parse.SizeValue.Replace("CARTON", "");
else if (parse.SizeValue.Contains("INCH"))
FieldValue = parse.SizeValue.Replace("INCH", "");
else if (parse.SizeValue.Contains("FT"))
FieldValue = parse.SizeValue.Replace("FT", "");
else if (parse.SizeValue.Contains("LT"))
FieldValue = parse.SizeValue.Replace("LT", "");
else if (parse.SizeValue.Contains("BULK"))
FieldValue = parse.SizeValue.Replace("BULK", "");
else if (parse.SizeValue.Contains("PK"))
FieldValue = parse.SizeValue.Replace("PK", "");
else if (parse.SizeValue.Contains("COUNT"))
FieldValue = parse.SizeValue.Replace("COUNT", "");
else if (parse.SizeValue.Contains("-"))
FieldValue = parse.SizeValue.Replace("-", "");
else if (parse.SizeValue.Contains("GR"))
FieldValue = parse.SizeValue.Replace("GR", "");
else if (parse.SizeValue.Contains("YD"))
FieldValue = parse.SizeValue.Replace("YD", "");
else if (parse.SizeValue.Contains("PAIR"))
FieldValue = parse.SizeValue.Replace("PAIR", "");
else if (parse.SizeValue.Contains("+"))
FieldValue = parse.SizeValue.Replace("+", "");
else if (parse.SizeValue.Contains("BH"))
FieldValue = parse.SizeValue.Replace("BH", "");
else if (parse.SizeValue.Contains("BR"))
FieldValue = parse.SizeValue.Replace("BR", "");
else if (parse.SizeValue.Contains("BU"))
FieldValue = parse.SizeValue.Replace("BU", "");
else if (parse.SizeValue.Contains("BX"))
FieldValue = parse.SizeValue.Replace("BX", "");
else if (parse.SizeValue.Contains("CF"))
FieldValue = parse.SizeValue.Replace("CF", "");
else if (parse.SizeValue.Contains("CM"))
FieldValue = parse.SizeValue.Replace("CM", "");
else if (parse.SizeValue.Contains("CN"))
FieldValue = parse.SizeValue.Replace("CN", "");
else if (parse.SizeValue.Contains("CR"))
FieldValue = parse.SizeValue.Replace("CR", "");
else if (parse.SizeValue.Contains("CS"))
FieldValue = parse.SizeValue.Replace("CS", "");
else if (parse.SizeValue.Contains("DZ"))
FieldValue = parse.SizeValue.Replace("DZ", "");
else if (parse.SizeValue.Contains("ER"))
FieldValue = parse.SizeValue.Replace("ER", "");
else if (parse.SizeValue.Contains("PAIR"))
FieldValue = parse.SizeValue.Replace("PAIR", "");
int value = 1;
if (String.IsNullOrWhiteSpace(FieldValue))
FieldValue = value.ToString();
field4.SignFieldValue = FieldValue.TrimStart('' '');
field4.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field4);
}
if (!String.IsNullOrWhiteSpace(parse.PackValue))
{
SignField field5 = new SignField();
field5.FieldID = parse.PackID;
field5.SignFieldValue = parse.PackValue.TrimStart('' '');
field5.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field5);
}
if (!String.IsNullOrWhiteSpace(parse.ItemDescriptionValue))
{
SignField field6 = new SignField();
field6.FieldID = parse.ItemDescriptionID;
field6.SignFieldValue = parse.ItemDescriptionValue.TrimStart('' '');
field6.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field6);
}
if (!String.IsNullOrWhiteSpace(parse.ItemCodeValue))
{
SignField field7 = new SignField();
field7.FieldID = parse.ItemCodeID;
field7.SignFieldValue = parse.ItemCodeValue.TrimStart('' ''); //make it 6 digits always as opposed to strip leading zeros as LS instructed 10122012, now 10/19/2012
field7.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field7);
}
if (!String.IsNullOrWhiteSpace(parse.MinorCategoryValue))
{
//need to strip leading zeros and blank spaces
SignField field8 = new SignField();
field8.FieldID = parse.MinorCategoryID;
field8.SignFieldValue = parse.MinorCategoryValue.TrimStart('' '', ''0''); //strip leading zeros per MH 9122012
field8.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field8);
}
if (!String.IsNullOrWhiteSpace(parse.OBIFieldValue))
{
SignField field9 = new SignField();
field9.FieldID = parse.OBIId;
field9.SignFieldValue = parse.OBIFieldValue.TrimStart('' '');
field9.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field9);
}
if (!String.IsNullOrWhiteSpace(parse.MajorCategoryValue))
{
SignField field10 = new SignField();
field10.FieldID = parse.MajorCategoryID;
field10.SignFieldValue = parse.MajorCategoryValue.TrimStart('' '', ''0''); //strip leading zeros per MH 9122012
field10.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field10);
}
if (!String.IsNullOrWhiteSpace(parse.VendorValue))
{
SignField field11 = new SignField();
field11.FieldID = parse.VendorID;
field11.SignFieldValue = parse.VendorValue.TrimStart('' '', ''0''); //strip leading zeros per LS 10082012
field11.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field11);
}
if (!String.IsNullOrWhiteSpace(parse.UsaveValue))
{
SignField field12 = new SignField();
field12.FieldID = parse.UsaveID;
field12.SignFieldValue = parse.UsaveValue.TrimStart('' '');
field12.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field12);
}
if (!String.IsNullOrWhiteSpace(parse.PricePerUnitValue))
{
SignField field13 = new SignField();
field13.FieldID = parse.PricePerUnitID;
field13.SignFieldValue = parse.PricePerUnitValue.TrimStart('' '');
field13.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field13);
}
if (!String.IsNullOrWhiteSpace(parse.PalletValue))
{
SignField field14 = new SignField();
field14.FieldID = parse.PalletID;
field14.SignFieldValue = parse.PalletValue.TrimStart('' '');
field14.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field14);
}
if (!String.IsNullOrWhiteSpace(parse.MasterPackValue))
{
SignField field15 = new SignField();
field15.FieldID = parse.MasterPackId;
field15.SignFieldValue = parse.MasterPackValue.TrimStart('' '');
field15.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field15);
}
if (!String.IsNullOrWhiteSpace(parse.TodaysDateValue))
{
string month = parse.TodaysDateValue.Substring(0, 2);
string day = parse.TodaysDateValue.Substring(2, 2);
string year = parse.TodaysDateValue.Substring(4, 2);
SignField field16 = new SignField();
field16.FieldID = parse.TodaysDateID;
DateTime date = Convert.ToDateTime(month + "/" + day + "/20" + year);
field16.SignFieldValue = month + "/" + day + "/20" + year;
field16.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field16);
sign.SignDateFrom = date;
sign.SignDateTo = date.AddDays(6);
}
if (!String.IsNullOrWhiteSpace(parse.AWGSizeIDValue))
{
SignField field17 = new SignField();
field17.FieldID = parse.AWGSizeID;
if (parse.AWGSizeIDValue.TrimStart('' '') == "CF")
{
field17.SignFieldValue = "5";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "CM")
{
field17.SignFieldValue = "6";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "CN")
{
field17.SignFieldValue = "7";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "CR")
{
field17.SignFieldValue = "8";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "CS")
{
field17.SignFieldValue = "9";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "CT")
{
field17.SignFieldValue = "10";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "DZ")
{
field17.SignFieldValue = "11";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "BX")
{
field17.SignFieldValue = "4";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "BU")
{
field17.SignFieldValue = "3";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "BR")
{
field17.SignFieldValue = "2";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "BH")
{
field17.SignFieldValue = "1";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "EA")
{
field17.SignFieldValue = "12";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "ER")
{
field17.SignFieldValue = "13";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "FT")
{
field17.SignFieldValue = "14";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "GL")
{
field17.SignFieldValue = "15";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "GR")
{
field17.SignFieldValue = "16";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "HD")
{
field17.SignFieldValue = "17";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "IN")
{
field17.SignFieldValue = "18";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "KG")
{
field17.SignFieldValue = "19";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "LB")
{
field17.SignFieldValue = "20";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "LG")
{
field17.SignFieldValue = "21";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "LT")
{
field17.SignFieldValue = "22";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "ME")
{
field17.SignFieldValue = "23";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "MG")
{
field17.SignFieldValue = "24";
}
else if (parse.AWGSizeIDValue.Trim('' '') == "ML")
{
field17.SignFieldValue = "25";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "MM")
{
field17.SignFieldValue = "26";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "MT")
{
field17.SignFieldValue = "27";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "NA")
{
field17.SignFieldValue = "28";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "OZ")
{
field17.SignFieldValue = "29";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "PC")
{
field17.SignFieldValue = "30";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "PK")
{
field17.SignFieldValue = "31";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "PR")
{
field17.SignFieldValue = "32";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "PT")
{
field17.SignFieldValue = "33";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "QT")
{
field17.SignFieldValue = "34";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "RL")
{
field17.SignFieldValue = "35";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "RW")
{
field17.SignFieldValue = "36";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "SH")
{
field17.SignFieldValue = "37";
}
else if (parse.AWGSizeIDValue.Trim('' '') == "SK")
{
field17.SignFieldValue = "38";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "SM")
{
field17.SignFieldValue = "39";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "ST")
{
field17.SignFieldValue = "40";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "SZ")
{
field17.SignFieldValue = "41";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "US")
{
field17.SignFieldValue = "42";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "YD")
{
field17.SignFieldValue = "44";
}
else if (parse.AWGSizeIDValue.TrimStart('' '') == "WT")
{
field17.SignFieldValue = "43";
}
field17.SignFieldLastUpdate = DateTime.Now;
if (field17.SignFieldValue != null)
{
sign.SignFields.Add(field17);
}
}
if (!String.IsNullOrWhiteSpace(parse.UPC13Value))
{
SignField field18 = new SignField();
field18.FieldID = parse.UPC13ID;
field18.SignFieldValue = parse.UPC13Value.TrimStart('' '');
field18.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field18);
}
if (!String.IsNullOrWhiteSpace(parse.LOQValue))
{
SignField field19 = new SignField();
field19.FieldID = parse.LOQID;
field19.SignFieldValue = parse.LOQValue.TrimStart('' '');
field19.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field19);
}
if (!String.IsNullOrWhiteSpace(parse.SaleStartDateValue))
{
string month = parse.SaleStartDateValue.Substring(0, 2);
string day = parse.SaleStartDateValue.Substring(2, 2);
string year = parse.SaleStartDateValue.Substring(4, 2);
SignField field20 = new SignField();
field20.FieldID = parse.SaleStartDateID;
field20.SignFieldValue = month + "/" + day + "/20" + year;
field20.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field20);
}
if (!String.IsNullOrWhiteSpace(parse.SaleEndDateValue))
{
string month = parse.SaleEndDateValue.Substring(0, 2);
string day = parse.SaleEndDateValue.Substring(2, 2);
string year = parse.SaleEndDateValue.Substring(4, 2);
SignField field21 = new SignField();
field21.FieldID = parse.SaleEndDateID;
field21.SignFieldValue = month + "/" + day + "/20" + year;
field21.SignFieldLastUpdate = DateTime.Now;
sign.SignFields.Add(field21);
}
LevelSign levelSign = new LevelSign();
levelSign.LevelID = newLevel.LevelID;
levelSign.LevelSignLastUpdated = DateTime.Now;
levelSign.LevelSignQuantity = Quantity;
levelSign.LevelSignSoftDelete = false;
levelSign.StampID = stampID;
sign.LevelSigns.Add(levelSign);
sign.SignID = -1;
if (sign.SignDateFrom == null)
sign.SignDateFrom = DateTime.Now;
if (sign.SignDateTo == null)
sign.SignDateTo = DateTime.Now.AddDays(6);
SignManager.SaveSign(sign, true, true);
}
else
{
//do nothing
}
}
}
}
}
}
}
Anything would help, ideas on better processing, ive tried bulk insert but without a lot of luck.
thanks!
Anything would help, ideas on better processing, ive tried bulk insert but without a lot of luck.
thanks!
推荐答案
Hi,
I think I like where this is going. Your speed will be down to the inserts into SQL as we all probably know. One way to really improve performance is bulk inserts but you mention that you have tried this before but no luck.
You have not mentioned what .Net framework version you are using. I would assume (hope) you are using .net 4 +
Have you considered the following scenario?
1) Using some of your method in reading the files to be inserted maybe you can break this down into insert chunks. i.e. you mentioned that you could have 3 inserts per potent object. If so create a ConcurrentBag (see ConcurrentBag[^], basically a thread safe list) of these objects to be inserted in one go. Once you have this bag / collection item (and if order is needed then use a ConcurrentStack) you can then add these to a BlockingCollection.
2) Using a BlockingCollection (BlockingCollection[^]) you can then enumerate over each object in parallel as this blocking collection is threadsafe. You can do something like:
Hi,
I think I like where this is going. Your speed will be down to the inserts into SQL as we all probably know. One way to really improve performance is bulk inserts but you mention that you have tried this before but no luck.
You have not mentioned what .Net framework version you are using. I would assume (hope) you are using .net 4 +
Have you considered the following scenario?
1) Using some of your method in reading the files to be inserted maybe you can break this down into insert chunks. i.e. you mentioned that you could have 3 inserts per potent object. If so create a ConcurrentBag (see ConcurrentBag[^], basically a thread safe list) of these objects to be inserted in one go. Once you have this bag / collection item (and if order is needed then use a ConcurrentStack) you can then add these to a BlockingCollection.
2) Using a BlockingCollection (BlockingCollection[^]) you can then enumerate over each object in parallel as this blocking collection is threadsafe. You can do something like:
Parallel.ForEach(_bc.GetConsumingEnumerable(), item =>
{
… // process item here. i.e. insert into database
});
By doing this you can operate on your data chunks in parallel which would increase performance.
BIG NOTE!
By doing the above on its own will cause some problems with concurrency which is not great. However, if you use the Parallel extensions from Microsoft this problem is less painful.
Check out : http://blogs.msdn.com/b/pfxteam/archive/2010/04/06/9990420.aspx[^]
Once you have the GetConsumingPartitioner in place you can really add performance gains.
Secondly, I personally love TPL. Get into that instead of spawning new threads. Sasha Barber did a great article on TPL Task Parallel Library: 1 of n[^]. By doing your chunk collection generation in parallel or in task collections you can process things very quickly.
Lastly, there will be problems and strange exceptions that are thrown when issues are encountered and managing exceptions can be tricky however with a bit of patients, love and care, you should be able to generate something quite fantastic.
Let me know how you get on!
By doing this you can operate on your data chunks in parallel which would increase performance.
BIG NOTE!
By doing the above on its own will cause some problems with concurrency which is not great. However, if you use the Parallel extensions from Microsoft this problem is less painful.
Check out : http://blogs.msdn.com/b/pfxteam/archive/2010/04/06/9990420.aspx[^]
Once you have the GetConsumingPartitioner in place you can really add performance gains.
Secondly, I personally love TPL. Get into that instead of spawning new threads. Sasha Barber did a great article on TPL Task Parallel Library: 1 of n[^]. By doing your chunk collection generation in parallel or in task collections you can process things very quickly.
Lastly, there will be problems and strange exceptions that are thrown when issues are encountered and managing exceptions can be tricky however with a bit of patients, love and care, you should be able to generate something quite fantastic.
Let me know how you get on!
Hi me again.
I am interested to find out what went wrong with bulk inserts.
Have you also looked at:
1) Generate your chunk files as explained in solution 1. Instead of inserting into a database directly consider writing the file(s) to a folder/directory.
2) Using SSIS to read the file(s) use the very powerful processing power of SSIS to bulk insert the files by looping over them and processing them asynchronously.
thanks
Hi me again.
I am interested to find out what went wrong with bulk inserts.
Have you also looked at:
1) Generate your chunk files as explained in solution 1. Instead of inserting into a database directly consider writing the file(s) to a folder/directory.
2) Using SSIS to read the file(s) use the very powerful processing power of SSIS to bulk insert the files by looping over them and processing them asynchronously.
thanks
这篇关于SQL CONSOLE应用程序，优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
