private int InsertProductIntoDB(Product aProduct)
{
//connect to SQL
SqlConnection myConnection = new SqlConnection();
//get connection string from app.config file
myConnection.ConnectionString = ConfigurationManager.AppSettings["Target"].ToString();
//"Data Source=(localdb)\\Projects;Initial Catalog=LincatLocalDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False";
//ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
try{
myConnection.Open();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
return 1;
}
//setting up each of the parameters from the Product that was submitted
SqlCommand myCommand = new SqlCommand("INSERT INTO LinCatProductsTable (Model, Range, Description, Height, Width, Depth, Power, Fuel, notes) " +
"VALUES (@ModelParam, @RangeParam, @DescriptionParam, @HeightParam, @WidthParam, @DepthParam, @PowerParam, @FuelParam, @notesParam)", myConnection);
//"VALUES (@ModelParam, 'test', 'test', '1', '2', '3', 'test' , 'test')", myConnection);
myCommand.Parameters.Add("@ModelParam", SqlDbType.NVarChar, 100);
myCommand.Parameters["@ModelParam"].Value = aProduct.Model;
myCommand.Parameters.Add("@RangeParam", SqlDbType.NVarChar, 100);
myCommand.Parameters["@RangeParam"].Value = aProduct.Range;
myCommand.Parameters.Add("@DescriptionParam", SqlDbType.NVarChar);
myCommand.Parameters["@DescriptionParam"].Value = aProduct.Description;
myCommand.Parameters.Add("@HeightParam", SqlDbType.Int);
myCommand.Parameters["@HeightParam"].Value = aProduct.height;
myCommand.Parameters.Add("@WidthParam", SqlDbType.Int);
myCommand.Parameters["@WidthParam"].Value = aProduct.width;
myCommand.Parameters.Add("@DepthParam", SqlDbType.Int);
myCommand.Parameters["@DepthParam"].Value = aProduct.depth;
myCommand.Parameters.Add("@PowerParam", SqlDbType.NVarChar, 50);
myCommand.Parameters["@PowerParam"].Value = aProduct.power;
myCommand.Parameters.Add("@FuelParam", SqlDbType.NVarChar, 50);
myCommand.Parameters["@FuelParam"].Value = aProduct.powerType;
myCommand.Parameters.Add("@notesParam", SqlDbType.NVarChar);
myCommand.Parameters["@notesParam"].Value = aProduct.otherNotes;
if (myCommand.ExecuteNonQuery().Equals(1))
{
textBox1.AppendText("\r\nInserted into DB Product: " + aProduct.Model);
}
else
{
textBox1.AppendText("\r\nFailed Insert DB Product: " + aProduct.Model);
}
//close up the connection
try
{
myConnection.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
return 1;
}
return 0;
}
private Product GetProduct(string URL)
{
//First get a page from the lincat website....
HtmlWeb HW = new HtmlWeb();
Product SingleProduct = new Product();
var document = HW.Load(URL);
//17.08 - var ProdTags = document.DocumentNode.SelectNodes("//table[@id='product_table']//td//@href");
var ProdTags = document.DocumentNode.SelectNodes("//section[@id='content']//table//tbody//td");
var SpanTags = document.DocumentNode.SelectNodes("//section[@id='content']//p//span");
var ImgTags = document.DocumentNode.SelectNodes("//div[@id='product_image']//img");
var pdfTags = document.DocumentNode.SelectNodes("//li[@class='pdf']//a");
if (ProdTags != null)
{
StringBuilder sb = new StringBuilder();
StringBuilder SpanSb = new StringBuilder();
int i = 0;
if (SpanTags != null)
{
foreach (var tag in SpanTags)
{
//gets the bullet notes for the product
SpanSb.Append("\r\n - " + tag.InnerText);
}
}
//enumerates the table, and fills up aProduct object.
foreach (var tag in ProdTags)
{
i++;
switch (i)
{
case 1:
SingleProduct.Model = tag.InnerText;
break;
case 2:
SingleProduct.Range = tag.InnerText;
break;
case 3:
SingleProduct.Description = tag.InnerText;
break;
case 4:
try{
SingleProduct.height = Convert.ToInt32(tag.InnerText);
}
catch{
SingleProduct.height = 0;
}
break;
case 5:
try
{
SingleProduct.width = Convert.ToInt32(tag.InnerText);
}
catch
{
SingleProduct.width = 0;
}
break;
case 6:
try
{
SingleProduct.depth = Convert.ToInt32(tag.InnerText);
}
catch{
SingleProduct.depth = 0;
}break;
case 7:
SingleProduct.power = tag.InnerText;
break;
case 8:
SingleProduct.powerType = tag.InnerText;
break;
default:
break;
}
//sb.Append("\r\n (" + i + ")" + tag.InnerText);
}
if (ImgTags != null)
{
foreach (var tag in ImgTags)
{
string link = tag.Attributes["SRC"].Value.ToString();
//gets the url for the image on the page
SpanSb.Append("\r\n + " + link);
if (link.ToLower().Contains("jpg"))
{
GetProductContent(link, SingleProduct.Model);
}
}
}
if (pdfTags != null)
{
foreach (var tag in pdfTags)
{
string link = tag.Attributes["HREF"].Value.ToString();
//gets each of the pdfs for the product
SpanSb.Append("\r\n + " + link);
//only get content if it's a pdf
if (link.ToLower().Contains("pdf"))
{
GetProductContent(link, SingleProduct.Model);
}
}
}
SingleProduct.otherNotes = SpanSb.ToString();
//textBox1.AppendText(SpanSb.ToString());
//textBox1.AppendText(sb.ToString());
}
return SingleProduct;
}
