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; }