Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

passing data to SQL table



Hi all,

i am extremely new to programming and have ben asked to build a screenscraper for some web data and then pass it to a SQL database from a progfram in C# - so far i have managed to get the sceen to scrape using some varying parameters but i am wondering how you pass strings across to a SQL 2k backend - i have created the connection to the database and have attached the code below to show what is working so far. Any help is much appreciated. This data also updates evry half hour so it needs to be added into the table and then rerun again without duplicating the data.


using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Net;
using System.Data.SqlClient;



namespace ScreenScrape
{
      /// <summary>
      /// Summary description for Form1.
      /// </summary>
      public class Form1 : System.Windows.Forms.Form
      {
            private System.Windows.Forms.Button btnGo;
            private System.Windows.Forms.Button btnStop;
            private System.Windows.Forms.TextBox textBox1;
            private System.Windows.Forms.Timer timer2;
            private System.ComponentModel.IContainer components;

            private System.Windows.Forms.Label label1;
            private System.Windows.Forms.Label label2;
            private System.Windows.Forms.Label label3;
            private System.Windows.Forms.Label label4;
            private System.Windows.Forms.Label label5;
            private System.Windows.Forms.TextBox txtSession;
            private System.Windows.Forms.TextBox txtFpnStart;
            private System.Windows.Forms.TextBox txtFpnFinish;
            private System.Windows.Forms.Label label6;
            private System.Windows.Forms.Label label8;
            private System.Windows.Forms.Label label9;
            private System.Windows.Forms.Label label7;
            private System.Windows.Forms.TextBox txtMelStart;
            private System.Windows.Forms.TextBox txtMelFinish;
            private System.Windows.Forms.TextBox txtTimeA;
            private System.Windows.Forms.TextBox txtTimeB;
            private System.Windows.Forms.Label label10;
            private System.Windows.Forms.TextBox txtDate1;
            private System.Windows.Forms.TextBox txtDate2;
            private System.Windows.Forms.Button button1;

            int myCounter = 0;


            public Form1()
            {
                  //
                  // Required for Windows Form Designer support
                  //
                  InitializeComponent();

                  //
                  // TODO: Add any constructor code after InitializeComponent call
                  //
                  

            }
            

            /// <summary>
            /// Clean up any resources being used.
            /// </summary>
            protected override void Dispose( bool disposing )
            {
                  if( disposing )
                  {
                        if (components != null)
                        {
                              components.Dispose();
                        }
                  }
                  base.Dispose( disposing );
            }

            #region Windows Form Designer generated code
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {
                  this.components = new System.ComponentModel.Container();
                  this.btnGo = new System.Windows.Forms.Button();
                  this.btnStop = new System.Windows.Forms.Button();
                  this.textBox1 = new System.Windows.Forms.TextBox();
                  this.timer2 = new System.Windows.Forms.Timer(this.components);
                  this.label1 = new System.Windows.Forms.Label();
                  this.label2 = new System.Windows.Forms.Label();
                  this.label3 = new System.Windows.Forms.Label();
                  this.label4 = new System.Windows.Forms.Label();
                  this.label5 = new System.Windows.Forms.Label();
                  this.txtDate1 = new System.Windows.Forms.TextBox();
                  this.txtSession = new System.Windows.Forms.TextBox();
                  this.txtFpnStart = new System.Windows.Forms.TextBox();
                  this.txtFpnFinish = new System.Windows.Forms.TextBox();
                  this.txtMelStart = new System.Windows.Forms.TextBox();
                  this.txtMelFinish = new System.Windows.Forms.TextBox();
                  this.label8 = new System.Windows.Forms.Label();
                  this.label9 = new System.Windows.Forms.Label();
                  this.txtTimeA = new System.Windows.Forms.TextBox();
                  this.txtTimeB = new System.Windows.Forms.TextBox();
                  this.label6 = new System.Windows.Forms.Label();
                  this.label7 = new System.Windows.Forms.Label();
                  this.txtDate2 = new System.Windows.Forms.TextBox();
                  this.label10 = new System.Windows.Forms.Label();
                  this.button1 = new System.Windows.Forms.Button();
                  this.SuspendLayout();
                  //
                  // btnGo
                  //
                  this.btnGo.Location = new System.Drawing.Point(16, 56);
                  this.btnGo.Name = "btnGo";
                  this.btnGo.TabIndex = 0;
                  this.btnGo.Text = "Go";
                  this.btnGo.Click += new System.EventHandler(this.clickGo);
                  //
                  // btnStop
                  //
                  this.btnStop.Location = new System.Drawing.Point(16, 88);
                  this.btnStop.Name = "btnStop";
                  this.btnStop.TabIndex = 1;
                  this.btnStop.Text = "Stop";
                  this.btnStop.Click += new System.EventHandler(this.clickStop);
                  //
                  // textBox1
                  //
                  this.textBox1.Location = new System.Drawing.Point(32, 336);
                  this.textBox1.Multiline = true;
                  this.textBox1.Name = "textBox1";
                  this.textBox1.ReadOnly = true;
                  this.textBox1.Size = new System.Drawing.Size(368, 40);
                  this.textBox1.TabIndex = 2;
                  this.textBox1.Text = "";
                  //
                  // timer2
                  //
                  this.timer2.Interval = 5000;
                  this.timer2.Tick += new System.EventHandler(this.timerTick);
                  //
                  // label1
                  //
                  this.label1.Location = new System.Drawing.Point(8, 16);
                  this.label1.Name = "label1";
                  this.label1.Size = new System.Drawing.Size(552, 23);
                  this.label1.TabIndex = 3;
                  this.label1.Text = "BM_reports_scraper";
                  //
                  // label2
                  //
                  this.label2.Location = new System.Drawing.Point(112, 64);
                  this.label2.Name = "label2";
                  this.label2.TabIndex = 4;
                  this.label2.Text = "Date-start";
                  //
                  // label3
                  //
                  this.label3.CausesValidation = false;
                  this.label3.Location = new System.Drawing.Point(152, 97);
                  this.label3.Name = "label3";
                  this.label3.TabIndex = 5;
                  this.label3.Text = "half hour";
                  //
                  // label4
                  //
                  this.label4.Location = new System.Drawing.Point(152, 130);
                  this.label4.Name = "label4";
                  this.label4.TabIndex = 6;
                  this.label4.Text = "fpn-start";
                  //
                  // label5
                  //
                  this.label5.Location = new System.Drawing.Point(152, 163);
                  this.label5.Name = "label5";
                  this.label5.TabIndex = 7;
                  this.label5.Text = "fpn-finish";
                  //
                  // txtDate1
                  //
                  this.txtDate1.Location = new System.Drawing.Point(240, 64);
                  this.txtDate1.Name = "txtDate1";
                  this.txtDate1.Size = new System.Drawing.Size(88, 20);
                  this.txtDate1.TabIndex = 8;
                  this.txtDate1.Text = "";
                  this.txtDate1.TextChanged += new System.EventHandler(this.txtDate1_TextChanged);
                  //
                  // txtSession
                  //
                  this.txtSession.Location = new System.Drawing.Point(312, 96);
                  this.txtSession.Name = "txtSession";
                  this.txtSession.Size = new System.Drawing.Size(144, 20);
                  this.txtSession.TabIndex = 9;
                  this.txtSession.Text = "";
                  this.txtSession.TextChanged += new System.EventHandler(this.txtSession_TextChanged);
                  //
                  // txtFpnStart
                  //
                  this.txtFpnStart.Location = new System.Drawing.Point(312, 128);
                  this.txtFpnStart.Name = "txtFpnStart";
                  this.txtFpnStart.Size = new System.Drawing.Size(144, 20);
                  this.txtFpnStart.TabIndex = 10;
                  this.txtFpnStart.Text = "";
                  this.txtFpnStart.TextChanged += new System.EventHandler(this.txtFpnStart_TextChanged);
                  //
                  // txtFpnFinish
                  //
                  this.txtFpnFinish.Location = new System.Drawing.Point(312, 160);
                  this.txtFpnFinish.Name = "txtFpnFinish";
                  this.txtFpnFinish.Size = new System.Drawing.Size(144, 20);
                  this.txtFpnFinish.TabIndex = 11;
                  this.txtFpnFinish.Text = "";
                  this.txtFpnFinish.TextChanged += new System.EventHandler(this.txtFpnFinish_TextChanged);
                  //
                  // txtMelStart
                  //
                  this.txtMelStart.Location = new System.Drawing.Point(312, 200);
                  this.txtMelStart.Name = "txtMelStart";
                  this.txtMelStart.Size = new System.Drawing.Size(144, 20);
                  this.txtMelStart.TabIndex = 12;
                  this.txtMelStart.Text = "";
                  //
                  // txtMelFinish
                  //
                  this.txtMelFinish.Location = new System.Drawing.Point(312, 232);
                  this.txtMelFinish.Name = "txtMelFinish";
                  this.txtMelFinish.Size = new System.Drawing.Size(144, 20);
                  this.txtMelFinish.TabIndex = 13;
                  this.txtMelFinish.Text = "";
                  //
                  // label8
                  //
                  this.label8.Location = new System.Drawing.Point(152, 196);
                  this.label8.Name = "label8";
                  this.label8.TabIndex = 14;
                  this.label8.Text = "mel-start";
                  //
                  // label9
                  //
                  this.label9.Location = new System.Drawing.Point(152, 229);
                  this.label9.Name = "label9";
                  this.label9.TabIndex = 15;
                  this.label9.Text = "mel-finish";
                  this.label9.Click += new System.EventHandler(this.label9_Click);
                  //
                  // txtTimeA
                  //
                  this.txtTimeA.Location = new System.Drawing.Point(136, 288);
                  this.txtTimeA.Name = "txtTimeA";
                  this.txtTimeA.TabIndex = 16;
                  this.txtTimeA.Text = "";
                  this.txtTimeA.TextChanged += new System.EventHandler(this.TimeA_TextChanged);
                  //
                  // txtTimeB
                  //
                  this.txtTimeB.Location = new System.Drawing.Point(392, 288);
                  this.txtTimeB.Name = "txtTimeB";
                  this.txtTimeB.TabIndex = 17;
                  this.txtTimeB.Text = "";
                  //
                  // label6
                  //
                  this.label6.Location = new System.Drawing.Point(16, 288);
                  this.label6.Name = "label6";
                  this.label6.TabIndex = 18;
                  this.label6.Text = "Start time";
                  //
                  // label7
                  //
                  this.label7.Location = new System.Drawing.Point(264, 288);
                  this.label7.Name = "label7";
                  this.label7.TabIndex = 19;
                  this.label7.Text = "end time";
                  //
                  // txtDate2
                  //
                  this.txtDate2.Location = new System.Drawing.Point(472, 64);
                  this.txtDate2.Name = "txtDate2";
                  this.txtDate2.Size = new System.Drawing.Size(88, 20);
                  this.txtDate2.TabIndex = 20;
                  this.txtDate2.Text = "";
                  //
                  // label10
                  //
                  this.label10.Location = new System.Drawing.Point(352, 64);
                  this.label10.Name = "label10";
                  this.label10.TabIndex = 21;
                  this.label10.Text = "Date-end";
                  this.label10.Click += new System.EventHandler(this.label10_Click);
                  //
                  // button1
                  //
                  this.button1.Location = new System.Drawing.Point(16, 128);
                  this.button1.Name = "button1";
                  this.button1.TabIndex = 22;
                  this.button1.Text = "DB Form";
                  this.button1.Click += new System.EventHandler(this.button1_Click);
                  //
                  // Form1
                  //
                  this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
                  this.ClientSize = new System.Drawing.Size(576, 398);
                  this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                                                              this.button1,
                                                                                                              this.label10,
                                                                                                              this.txtDate2,
                                                                                                              this.label7,
                                                                                                              this.label6,
                                                                                                              this.txtTimeB,
                                                                                                              this.txtTimeA,
                                                                                                              this.label9,
                                                                                                              this.label8,
                                                                                                              this.txtMelFinish,
                                                                                                              this.txtMelStart,
                                                                                                              this.txtFpnFinish,
                                                                                                              this.txtFpnStart,
                                                                                                              this.txtSession,
                                                                                                              this.txtDate1,
                                                                                                              this.label5,
                                                                                                              this.label4,
                                                                                                              this.label3,
                                                                                                              this.label2,
                                                                                                              this.label1,
                                                                                                              this.textBox1,
                                                                                                              this.btnStop,
                                                                                                              this.btnGo});
                  this.Name = "Form1";
                  this.Text = "Form1";
                  this.Load += new System.EventHandler(this.Form1_Load);
                  this.ResumeLayout(false);

            }
            #endregion

            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main()
            {
                  Application.Run(new Form1());
                  
            }

            private string GetHtmlPage(string strURL)
            {
                  // the html retrieved from the page
                  string strResult;
                  
                  WebResponse objResponse;
                  WebRequest objRequest = System.Net.HttpWebRequest.Create(strURL);
                  objResponse = objRequest.GetResponse();
                  // the using keyword will automatically dispose the object
                  // once complete
                  using (StreamReader sr =
                                 new StreamReader(objResponse.GetResponseStream()))
                  {
                        strResult = sr.ReadToEnd();
                        // Close and clean up the StreamReader
                        sr.Close();
                  }
                  return strResult;
            }

            private void clickGo(object sender, System.EventArgs e)
            {
                  textBox1.Text = "starting...please wait...";
                  myCounter = 1;
                  timer2.Start();
            }

            private void clickStop(object sender, System.EventArgs e)
            {
                  textBox1.Text = "Stopped";
                  timer2.Stop();
            }

            private void timerTick(object sender, System.EventArgs e)
            {
                  string theUrl="http://www.bmreports.com/servlet/com.logica.neta.bwp_LatestBmDataServlet?param1=";

                  if(myCounter == 1) theUrl += "T_FERR-2";
                  if(myCounter == 2) theUrl += "T_FERR-3";
                  if(myCounter == 3) theUrl += "T_FERR-4";

                  theUrl += "&param2=&param3=";
                  
                  label1.Text = theUrl;

                  string htmlSrc = GetHtmlPage(theUrl);

                  textBox1.Text = htmlSrc;

                  if(htmlSrc.IndexOf("axis start sett date") > 0)
                  {
                        string MyDateA = htmlSrc.Substring((htmlSrc.IndexOf("series time 1") + 22), 10);
                        txtDate1.Text = MyDateA;
                  }
                  if(htmlSrc.IndexOf("axis start sett date") > 0)
                  {
                        string MyDateB = htmlSrc.Substring((htmlSrc.IndexOf("series time 1") + 39), 10);
                        txtDate2.Text = MyDateB;
                  }

                  if(htmlSrc.IndexOf("axis min period") > 0)
                  {
                        string Session = htmlSrc.Substring((htmlSrc.IndexOf("axis min period") + 24), 2);
                        txtSession.Text = Session;
                  }

                  if(htmlSrc.IndexOf("series level 1") > 0)
                  {
                        string FpnStart = htmlSrc.Substring((htmlSrc.IndexOf("series level 1") + 23), 7);
                        txtFpnStart.Text = FpnStart;
                  
                        if (htmlSrc.IndexOf("series level 1") > 0)
                        {
                              string FpnFinish = htmlSrc.Substring((htmlSrc.IndexOf("series level 1") + 31), 7);
                              txtFpnFinish.Text = FpnFinish;
                        }
                        if (htmlSrc.IndexOf("series level 3") > 0)
                        {
                              string MelStart = htmlSrc.Substring((htmlSrc.IndexOf("series level 3") + 23), 7);
                              txtMelStart.Text = MelStart;
                        }
                        if (htmlSrc.IndexOf("series level 3") > 0)
                        {
                              string MelFinish = htmlSrc.Substring((htmlSrc.IndexOf("series level 3") + 31), 7);
                              txtMelFinish.Text = MelFinish;
                        }
                        if (htmlSrc.IndexOf("series time 1") > 0)
                        {
                              string TimeA = htmlSrc.Substring((htmlSrc.IndexOf("series time 1") + 33), 5);
                              txtTimeA.Text = TimeA;
                        }
                        if (htmlSrc.IndexOf("series time 1") > 0)
                        {
                              string TimeB= htmlSrc.Substring((htmlSrc.IndexOf("series time 1") + 50), 5);
                              txtTimeB.Text = TimeB;
                        }
                        htmlSrc = "";
                        myCounter++;
                        if(myCounter > 3) myCounter = 1;
0
redmayne
Asked:
redmayne
  • 5
  • 3
1 Solution
 
mAjKoLCommented:
Make a Stored Procedure in the SQL Server to insert data you need.
Than make a typed DataSet in VS. ( Create empty DataSet, Drag your stroed proc from Server Explorer and drop it on the Design window of your new DataSet)
Make a DataAdapter with your SP set to UPDATE.
than invoke DataAdapter's Update method passind the DataSet.

HTH
0
 
Chester_M_RagelCommented:
This is not a good programming practice. Anyway, I'll follow you. If you have database, datatable with field to have data, you can just do something like this to add data.

SqlCommand command = new SqlCommand(("Insert into [tablename] [(columnname)] values('"+yourString+"')"),connection);

Similarly you can update using the correct syntax.
0
 
mAjKoLCommented:
And you think that inline queries and string concatanation are good ideas ?!?
Yes - you can skip the part with the DataSet, and you can invoke your SP from SQLCommand, but you have to use SQLParameter.
And this is the first time I hear that making SP-s isn't a good programming practise.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
redmayneAuthor Commented:
so from your answer would tthe following be a valid piece of code ?

SqlCommand command = new SqlCommand(("Insert [BMreports_data04][DateStart](MyDateA)"));
                        

0
 
redmayneAuthor Commented:
from the code i have above the strings i used are all there could someone give me an example of how to do this - like i said i am a compelte novice to programming - this is my first ever program!!!!!!!

as for SQL ibarely managed to create the table so any simplistic steps aer very welcome
0
 
mAjKoLCommented:
If you cant't create a Stored Proc you can do sth like that:

SqlConnection conn = new SqlConnection("<your connection string>");
SqlCommand command = new SqlCommand("INSERT INTO [BMreports_data04](DateStart) VALUES (@myDateA)");
SqlParameter param = new SqlParameter("@myDateA", System.Data.SqlDbType.DateTime); //the second should be the type of the column in DB
param.Value = MyDateA;
command.Parameters.Add(param);
command.Connection = conn;
conn.Open();
command.ExecuteNonQuery();
command.Connection.Close();

You can add as many parameters as you need.
HTH
0
 
redmayneAuthor Commented:
i think that helps a lot
so by creating say 8 of these replacing MyDateA for B etc... then it should run and everytime it receives a paramater value for say Mydate A,B then it will place it in the correct column right?

if so thats spot on...
Andy
0
 
mAjKoLCommented:
SqlCommand command = new SqlCommand("INSERT INTO [BMreports_data04](DateStart, OtherColumn, SomeOtherColumn) VALUES (@myDateA, @otherParam, @someOtherParam)");
if you want to insert more values to the same row, and than define 3 params, assign them a Value, an add them to the command
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now