Link to home
Start Free TrialLog in
Avatar of redmayne
redmayne

asked on

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;
Avatar of mAjKoL
mAjKoL
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
Avatar of Chester_M_Ragel
Chester_M_Ragel
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.
Avatar of mAjKoL
mAjKoL
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.
Avatar of redmayne
redmayne

ASKER

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

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

Avatar of redmayne
redmayne

ASKER

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
Avatar of mAjKoL
mAjKoL
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
Avatar of redmayne
redmayne

ASKER

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
Avatar of mAjKoL
mAjKoL
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
ASKER CERTIFIED SOLUTION
Avatar of mAjKoL
mAjKoL
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial