Solved

passing data to SQL table

Posted on 2004-09-23
9
268 Views
Last Modified: 2011-09-20


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
Comment
Question by:redmayne
  • 5
  • 3
9 Comments
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12131361
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
 
LVL 6

Expert Comment

by:Chester_M_Ragel
ID: 12131422
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
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12131477
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
 

Author Comment

by:redmayne
ID: 12131481
so from your answer would tthe following be a valid piece of code ?

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

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:redmayne
ID: 12131508
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
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12131600
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
 

Author Comment

by:redmayne
ID: 12131618
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
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12131628
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
 
LVL 3

Accepted Solution

by:
mAjKoL earned 500 total points
ID: 12131660
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now