并行处理并得到两个错误:已经有一个打开的数据读取器与此连接相关联，必须首先关闭&
本文关键字：连接 关联 读取 数据 两个 错误 有一个 并行处理 | 更新日期: 2023-09-27 18:17:37
我有这样的代码:
string mainUrlMarkafoni = "https://www.markafoni.com";
List<string> menuLinksMarkafoni = new List<string>();
DataTable butiksTableMarkafoni = new DataTable();
DataTable productsTableMarkafoni = new DataTable();
DataTable categoryTableMarkafoni = new DataTable();
private int menuCounterMarkafoni = 0;
int butikCounterMarkafoni = 0;
bool isLockedMarkafoniButik = false;
int categoryCounterMarkafoni = 0;
bool isLockedMarkafonicategory = true;
private void getHtmlMarkafoni()
{
{
con2 = new MySqlConnection(connetionString);
var doc = webGet.Load(mainUrlMarkafoni);
//select first tag a of top menu
foreach (var a in doc.DocumentNode.SelectNodes("//*[contains(@class,'first_line')]"))
{
var href = a.Attributes["href"].Value;
if (href != "/" && href != "/okul/")
menuLinksMarkafoni.Add(a.Attributes["href"].Value);
}
foreach (var menuURL in menuLinksMarkafoni)
{
string URL = mainUrlMarkafoni + menuURL;
Task.Factory.StartNew(() =>
{
doc = webGet.Load(URL);
//***********Start Get campaign_big div of campaigns***********//
HtmlNodeCollection campaignBig = doc.DocumentNode.SelectNodes("//div[contains(@class,'campaign_big')]");
if (campaignBig != null)
{
foreach (HtmlNode element in campaignBig)
{
//Get butiks Attribute values
var butikUrl = element.SelectSingleNode(".//a[@class='campaign-link-big']").GetAttributeValue("href", null);
var butikTitle = element.SelectSingleNode(".//div[contains(@class,'campaign_name_big')]").InnerText;
var butikImg = element.SelectSingleNode(".//div/a/img").GetAttributeValue("src", null);
var butikEndTime = element.SelectSingleNode(".//div[contains(@class,'campaign_overlay')]").GetAttributeValue("data-seconds", null);
var butikCode = getButikCode(butikUrl);
var butikCategory = menuURL.Replace("/", string.Empty);
//Change butikEndTime to DateTime
TimeSpan time = TimeSpan.FromSeconds(double.Parse(butikEndTime));
DateTime butikDatetime = DateTime.Today.Add(time);
//Add values to dataGrid
this.UIThread(() => dataGridView1.Rows.Add("", "Markafoni", butikCode, butikCategory, butikTitle, butikUrl, butikImg, butikDatetime, "pending"));
//Add values to butiksTable DataTable for next level (Insert products)
butiksTableMarkafoni.Rows.Add(butikCode, butikCategory, butikTitle, butikUrl, butikImg, butikDatetime);
}
}
else
{
MessageBox.Show("Null Obeject...! Not Found Larg Butiks");
}
//***********End Get campaign_big div of campaigns***********//
//***********Start Get campaign_medium div of campaigns***********//
HtmlNodeCollection campaignMedium = doc.DocumentNode.SelectNodes("//div[contains(@class,'campaign_medium')]");
if (campaignMedium != null)
{
foreach (HtmlNode element in campaignMedium)
{
//Get butiks Attribute values
var butikUrl = element.SelectSingleNode(".//a[@class='campaign-link-medium']").GetAttributeValue("href", null);
var butikTitle = element.SelectSingleNode(".//div[contains(@class,'campaign_name')]").InnerText;
var butikImg = element.SelectSingleNode(".//img").GetAttributeValue("src", null);
var butikEndTime = element.SelectSingleNode(".//div[contains(@class,'campaign_overlay_medium')]").GetAttributeValue("data-seconds", null);
var butikCode = getButikCode(butikUrl);
var butikCategory = menuURL.Replace("/", string.Empty);
//Change butikEndTime to DateTime
TimeSpan time = TimeSpan.FromSeconds(double.Parse(butikEndTime));
DateTime butikDatetime = DateTime.Today.Add(time);
//Add values to dataGrid
this.UIThread(() => dataGridView1.Rows.Add("", "Markafoni", butikCode, butikCategory, butikTitle, butikUrl, butikImg, butikDatetime, "pending"));
//Add values to butiksTable DataTable for next level (Insert products)
butiksTableMarkafoni.Rows.Add(butikCode, butikCategory, butikTitle, butikUrl, butikImg, butikDatetime);
}
}
else
{
//MessageBox.Show("Null Obeject...! Not Found Small Butiks");
}
//***********End Get campaign_medium div of campaigns***********//
//Increment counter after finish each thread
Interlocked.Increment(ref menuCounterMarkafoni);
//Insert dataTable to xs_butiks if all thread done...!
if (menuLinksMarkafoni.Count() == menuCounterMarkafoni)
{
MessageBox.Show(butiksTableMarkafoni.Rows.Count.ToString());
string sqlQuery;
sqlQuery = "INSERT INTO xs_butiks (id,siteName,butikCode,butikCategory,butikTitle,butikLink,butikImgLink,butikEndTime,butikStatus) VALUES (@ID,@SITE,@CODE,@CATEGORY,@TITLE,@LINK,@IMGLINK,@ENDTIME,@STATUS)";
//open connection
con2.Open();
foreach (DataRow row in butiksTableMarkafoni.Rows)
{
MySqlCommand cmd3 = new MySqlCommand(sqlQuery, con2);
cmd3.CommandText = sqlQuery;
cmd3.Parameters.AddWithValue("@ID", "");
cmd3.Parameters.AddWithValue("@SITE", "Markafoni");
cmd3.Parameters.AddWithValue("@CODE", row["butikCode"]);
cmd3.Parameters.AddWithValue("@CATEGORY", row["butikCategory"]);
cmd3.Parameters.AddWithValue("@TITLE", row["butikTitle"]);
cmd3.Parameters.AddWithValue("@LINK", row["butikLink"]);
cmd3.Parameters.AddWithValue("@IMGLINK", row["butikImgLink"]);
DateTime date = DateTime.Parse(row["butikEndTime"].ToString());
cmd3.Parameters.AddWithValue("@ENDTIME", date);
cmd3.Parameters.AddWithValue("@STATUS", "pending");
cmd3.ExecuteNonQuery();
/********************************************************************/
while (isLockedMarkafoniButik) ;
Task.Factory.StartNew(() =>
{
GetMarkafoniProducts(row, doc);
}
);
Interlocked.Increment(ref butikCounterMarkafoni);
if (butikCounterMarkafoni == 10) isLockedMarkafoniButik = true;
}
//close connection
con2.Close();
}
});
}
//*********************end foreach MenuLink*******************//
}
}
//**************************End Level 1****************************//
//
//**************************Start Level 2****************************//
private void GetMarkafoniProducts(DataRow row, HtmlAgilityPack.HtmlDocument doc)
{
string butikUrl = mainUrlMarkafoni + row["butikLink"];
this.UIThread(() => label1.Text = butikUrl);
try
{
doc = webGet.Load(butikUrl);
//Get Category links from ul_category
HtmlNodeCollection categoryLinks = doc.DocumentNode.SelectNodes("//ul[contains(@class,'ul_category')]/li/a");
if (categoryLinks != null)
{
foreach (HtmlNode link in categoryLinks)
{
var categoryHref = link.GetAttributeValue("href",null);
var categoryTitle = link.InnerText;
if (!categoryHref.Contains("/all/"))
{
string categoryLink = mainUrlMarkafoni + categoryHref;
doc = webGet.Load(categoryLink);
HtmlNodeCollection butikProducts = doc.DocumentNode.SelectNodes("//ul[@id='product_list']/li");
if (butikProducts != null)
{
foreach (HtmlNode product in butikProducts)
{
var productTitle = product.SelectSingleNode(".//p[@class='bold product_name']/a").GetAttributeValue("title", null);
var productSubTitle = string.Empty; //Don't exist in Markafoni
var productLink = product.SelectSingleNode(".//p[@class='bold product_name']/a").GetAttributeValue("href", null);
var productImg = product.SelectSingleNode(".//a/img").GetAttributeValue("src", null);
var productCategory = categoryTitle;
var productSize = string.Empty;
var productBrand = string.Empty;
var productStatus = product.GetAttributeValue("data-sold", null);
var productAvailability = string.Empty;
var productPrice = product.GetAttributeValue("data-price", null);
//Add values to dataGrid
this.UIThread(() => dataGridView2.Rows.Add("", "Markafoni", row["butikCode"], row["butikLink"], row["butikCategory"], row["butikEndTime"], productTitle, productSubTitle, productLink, productImg, productCategory, productSize, productBrand, productStatus, productAvailability, productPrice, "pending"));
string sqlQuery2;
sqlQuery2 = "INSERT INTO xs_butiks_products (product_id,siteName,butikCode,butikLink,butikCategory,butikEndTime,productTitle,productSubTitle,productLink,productImgLink,productCategory,productSize,productBrand,productStatus,productAvailability,productPrice,status) VALUES (@ID,@SITE,@BCODE,@BLINK,@BCATEGORY,@BENDTIME,@TITLE,@SUBTITLE,@PLINK,@PIMGLINK,@PCATEGORY,@SIZE,@BRAND,@PSTATUS,@AVAILABILITY,@PRICE,@STATUS)";
MySqlCommand cmd2 = new MySqlCommand(sqlQuery2, con2);
cmd2.CommandText = sqlQuery2;
cmd2.Parameters.AddWithValue("@ID", "");
cmd2.Parameters.AddWithValue("@SITE", "Markafoni");
cmd2.Parameters.AddWithValue("@BCODE", row["butikCode"]);
cmd2.Parameters.AddWithValue("@BLINK", row["butikLink"]);
cmd2.Parameters.AddWithValue("@BCATEGORY", row["butikCategory"]);
DateTime date2 = DateTime.Parse(row["butikEndTime"].ToString());
cmd2.Parameters.AddWithValue("@BENDTIME", date2);
cmd2.Parameters.AddWithValue("@TITLE", productTitle);
cmd2.Parameters.AddWithValue("@SUBTITLE", productSubTitle);
cmd2.Parameters.AddWithValue("@PLINK", productLink);
cmd2.Parameters.AddWithValue("@PIMGLINK", productImg);
cmd2.Parameters.AddWithValue("@PCATEGORY", productCategory);
cmd2.Parameters.AddWithValue("@SIZE", productSize);
cmd2.Parameters.AddWithValue("@BRAND", productBrand);
cmd2.Parameters.AddWithValue("@PSTATUS", productStatus);
cmd2.Parameters.AddWithValue("@AVAILABILITY", productAvailability);
cmd2.Parameters.AddWithValue("@PRICE", productPrice);
cmd2.Parameters.AddWithValue("@STATUS", "pending");
cmd2.ExecuteNonQuery();
/**********/
//Interlocked.Increment(ref categoryCounterMarkafoni);
}
}
}
}
}
}
catch (Exception ex)
{
Interlocked.Decrement(ref butikCounterMarkafoni);
if (butikCounterMarkafoni == 0) isLockedMarkafoniButik = false;
MessageBox.Show(ex.Message);
}
Interlocked.Decrement(ref butikCounterMarkafoni);
if (butikCounterMarkafoni == 0) isLockedMarkafoniButik = false;
}
private string getButikCategory(string butikUrl)
{
string[] parts = butikUrl.Split('/');
var item = parts.Last();
return item;
}
private string getButikCode(string butikUrl)
{
string[] parts = butikUrl.Split('/');
foreach (var item in parts)
{
if (IsNumeric(item))
return item;
}
return null;
}
public static bool IsNumeric(object Expression)
{
double retNum;
bool isNum = Double.TryParse(Convert.ToString(Expression), System.Globalization.NumberStyles.Any, System.Globalization.NumberFormatInfo.InvariantInfo, out retNum);
return isNum;
}
将显示代码的执行并重复以下错误。几分钟后，可以看到以下错误代码。但是它并没有停止代码的执行。我检查了几次代码，但没有能够解决这个问题。谁能提供一点时间来解决这个问题?
已经有一个打开的数据读取器与此连接相关联，必须先关闭。
和
The request was aborted: The request was cancelled .
您在多个线程中使用相同的连接，我怀疑这就是造成问题的原因，因为MySQLConnection可能不是线程安全的。
每次创建和使用MySQLConnection或MySQLCommand对象时，将它们包装在using块中，像这样，以确保它们在完成后立即成为Disposed。
using (var con = new MySqlConnection(connectionString))
using (var cmd = new MySqlCommand(sqlQuery, con))
{
cmd.CommandText = sqlQuery;
cmd.Parameters.AddWithValue("@ID", "");
//...
cmd.ExecuteNonQuery();
}
这实际上并不是每次都创建一个新的连接，而是从池中获取一个连接，并在using块的末尾返回它，因此开销很小。
类似于对另一个问题的回答