Data accessing

Data accessing

• Elie Ata

  brokenrulz wrote:

  http://www.fpoint.com/support/whitep/ADO/ADOwp.pdf

  (I have no idea why this person posted this link. Spam )

  carty,

  ADO.NET uses a "disconnected" approach. The Idea being you get your data from your database and load it into a dataset. You then manipulate the data in the dataset and then reconnect and post your data back. A Dataset is, for all intents and purposes, an in memory database in that it is a collection of datatables/dataviews. A DataAdapter is the mediator that acts as the traffic cop between your connection and the DataSet.

  The motivation for this approach is due to the nature of distributed applications, i.e. large scale LAN based apps or the internet, where constant connections are "expensive" (the former) or impossible (the latter.)

  here is where I would start:

  http://msdn.microsoft.com/library/default.asp url=/library/en-us/cpguide/html/cpconoverviewofadonet.asp

  good luck!

  
  
  

• sfibich

  Try this link

  http://www.fpoint.com/support/whitep/ADO/ADOwp.pdf

  
  

• Antoine Sirianni

  Dats fine..

   

  MyDataTable["myCol"][ n ]

  so by this command if i cld navigate thru a dataset..

  In my form i shld suposedly create a loop structure to check if the groupbox has been filled with ten labels and if not i shld add a new label and move to the next record by

  MyDataTable["myCol"][ n +1]

  Ive stopped my project for a while as my dad scoldin me to study my medicine subjects.. as tis quite big project im gonna do it after some months.. meanwhile im jus doin some programs for my pocket pc... u also target on pocket pc devices

  Im in india and VS 2005 is a bit expensive for a doctor and i recently only bought VS 2003 :)..

  smart device programmability is also not ther in VB 2005 express :(

  
  
  

• Internationalpaddy

  Ok I am going to take you down a different road.

  create an access database called scription.mdb

  In this database create a table called "Label" with the following structure:

  LabelText text(50) PrimaryKey
  Description text(50)

  Insert some LabelText items into the table

  Create a new VB windows project called Scription. Copy scription.mdb to the solution .\bin folder.

  In the Solution explorer, click show all files and right click scription.mdb and add it to the project.

  On the Tool box, click the data tab and drag an OleDbDataAdapter to your form. This will start the Data Adapter Configuration Wizard.

  Click new connection. In the Data Link Properties Windowselect the Provider tab. Doublc Click Microsoft jet 4.0 Ole DbProvider. On the Connection Tab, browse to the scriptions.mdb file. Click Test Connection and if it works, click Ok.

  Back in the Data Adapter Wizard, click Next, select us sql statements. Click next.

  Paste this in the "What Data Adapter should the Data Adapter load into the data set "

  SELECT Description, LabelText FROM label

  Click next, click finish, click "Dont include Password"

  Click the OleDbDataAdapter1 component in the component tray and in the properites window Change its name to labelAdapter, Click the OleDbConnection component and change to scriptionConnection

  While scriptionConnction is selected, in the properties window, expand (Dynamic Properties) and click the ellipsis [...] on "Conneciton String", check the Map Connection string to Configuration File and click ok.

  in solution explorer, double click app.config and change the text to this:

  < xml version="1.0" encoding="utf-8" >
  <configuration>
      <appSettings>
          <!-- User application and configured property settings go here.-->
          <!-- Example: <add key="settingName" value="settingValue"/> -->
          <add key="scriptionConnection.ConnectionString" value="Jet OLEDB:Global PartialeBulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=&quot;.\scription.mdb&quot;;Jet OLEDB:Engine Type=5;Provider=&quot;Microsoft.Jet.OLEDB.4.0&quot;;Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;JeteOLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1" />
      </appSettings>
  </configuration>

  IMPORTANT make sure this:

          <add key="scriptionConnection.ConnectionString" value="Jet OLEDB:Global PartialeBulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=&quot;.\scription.mdb&quot;;Jet OLEDB:Engine Type=5;Provider=&quot;Microsoft.Jet.OLEDB.4.0&quot;;Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;JeteOLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1" />

  is all on one line.

  Close app.config.

  right click labelAdapter and select "Generate DataSet", Select "New:" and give the name scriptionDataSet, check "Add this dataset to designer" and click ok.

  drop a List box, a group box and a button on your form

  select the listbox and in properties set the following

  Datasource: ScriptionDataSet1.label
  DisplayMember: Description
  ValueMember: LabelText 

  your desktop should look like this (click to enlarge)

  

  right click the form and select view code.

  At the top of the code editor select Listbox1 from the top left drop down and select the DoubleClick event in the right drop down and add this code:

    Private Sub ListBox1_DoubleClick(ByVal sender As Object, _ 
                                       ByVal e As System.EventArgs) _
                                                           Handles ListBox1.DoubleClick
      Dim cm As CurrencyManager = ListBox1.BindingContext(ListBox1.DataSource)
      Dim drv As DataRowView = CType(cm.Current, DataRowView)
      Dim row As scriptionDataSet.labelRow = CType(drv.Row, scriptionDataSet.labelRow)
      MessageBox.Show(row.Description, row.LabelText)
    End Sub

   and add an override to the form load:

    Protected Overrides Sub OnLoad(ByVal e As System.EventArgs)
      scriptionConnection.Open()
      labelAdapter.Fill(ScriptionDataSet1.label)
      scriptionConnection.Close()
    End Sub

  go back to the form designer and double click the button and add this code:

    Private Sub Button1_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) _
                                          Handles Button1.Click
      For Each row As scriptionDataSet.labelRow In _
                                    ScriptionDataSet1.label
        Dim lbl As New Label
        lbl.Text = row.LabelText
        If GroupBox1.Controls.Count = 0 Then
          lbl.Top = 15
        Else
          lbl.Top = GroupBox1.Controls(GroupBox1.Controls.Count - 1).Top + _
                     GroupBox1.Controls(GroupBox1.Controls.Count - 1).Height
        End If
        lbl.Left = 10
        GroupBox1.Controls.Add(lbl)
      Next
    End Sub

  run the code. . . double click the list box. . .  click the button

  complete code to be found here: http://www.obj-tec.com/msdnforums/carty/scription.zip

  
  
  

• fums64

  Blair,

          Tat was amazzzing job done by u.. It was wot exactly i was searching for long.. I really don know how to thank you for spending yr precious time to explain in a clean way wit an example project.. Tanx a lotttt..

          By the way, this method is called as OLEDB cz the method i was using was DAO.. I learnt it long back wen i was in scool, now my career has turned towards medicine and so din concentrate much on VB.. Now ive got the intrest again.. To develop projects for my personal use, to make my life easier!! Its Fun too..

         Wer to learn more abt the method uve used for me Cz i need to know more for my project.. In yr method u add the data to the groupbox by a FOR loop.. It loops again n again for every record i gez im not rong.. But the problem comes here..wen there are more records than the space in the group box So v can Use IF loop so that checking every ten records at a time and pressing the next button wld take to the next ten records.. so i need to know hoe to navigate thru the dataset..

  in DAO i use the code rs.movenext,.moveprevious,first,last for navigation.. In this method

  and for getting the value of a coloumn in DAO its rs.fields(1).value & in this method

  Pls help me..... Tanx again in advance.. Yr amazing..

   

  I was trying to reply frm yesterday evening but the reply message windowwasnt opening.. Can u gimme yr email id pls.. I don mind if u cld help me here in the forum also but email wld be easier i tht.. My mail id is some1sxi@hotmail.com.. Tanx.. Take care..

  
  
  

• 4pbears

  Tanx for both replies..

  Brokenrulz link also gave the pdf document.. i din check it yet.. Now transfering it to my Poket PC to read it..

  Blair' msdn link also gives a clear idea of ADO usage..

  I know a bit abt ADO but my question was, will data accessing be faster than DAO or which will be faster.. cz the labels were updating slowly one by one.. took 3-4 seconds for the ten labels in my form to get updated..Is tat due to the use of ADO or can it b made faster using DAO or SQL

  Im using VB express..

  Waiting for yr replies.... Thank you..

  
  
  

• Ric Fisher

  lets see your code for loading the labels.

  The Ado.Net might be a little slower than DAO, but not dramatically.

  
  
  

• blumash

  Carty wrote:

  Pls help me..... Tanx again in advance.. Yr amazing..

  I keep telling that to my girlfriend but she still doesnt believe me. . . I swear she wanted Visual Studio for christmas. . .

  at any rate. .Net uses a disconnected Data Access model. the Dataset is for all intents and purposes a Client Side Database. It has no knowledge of the actual Database that the data came from. In most cases this is a good thing. in a few isolated cases the old DAO/ADO/OleDB connected model may be better. The propose of the data adapter is to map your Server database to your client Dataset.

  A Dataset is a collection of Datatables (among other things)

  A DataTable is, among other things, a collection of DataColumns and a Collection of DataRows. the DataColumns collection is indexed via column ordinal and column name. The DataRows collection is indexed via an integer index. A value from a DataRow in the Datarows collection can be retireved by column name or column ordinal. A DataTable can be thought of as a twodimensional array (but caution, it really isnt) and you can get the "myCol' value out of the nth row via

  MyDataTable["myCol"][ n ]

  I suggest you work through the walkthroughs in the help file.

  If you can, get VS 2005 as the data access is significantly improved.

  
  
  

• swit

  Tats a gud idea.. Awrite here goes my codin which is rather the simplest way i followed..

  'to load the database and populate the labels

  db = DAODBEngine_definst.OpenDatabase(VB6.GetPath & "\scription.mdb")

  rs = db.OpenRecordset("System")

  rs.MoveLast()

  Dim a As String

  a = rs.Fields(1).Value

  ' since the record count command din work, i used the field named index in database

  'so rs.fields(1).value is the recordcount value, which is assigned to the variable a

  rs.MoveFirst()

  If a >= 1 Then

  Lablsys1.Text = rs.Fields(0).Value

  Lablsys1.Visible = True

  End If

  If a >= 2 Then

  rs.MoveNext()

  lablsys2.Text = rs.Fields(0).Value

  lablsys2.Visible = True

  End If

  If a >= 3 Then

  rs.MoveNext()

  lablsys3.Text = rs.Fields(0).Value

  lablsys3.Visible = True

  End If

  and so on......till all the ten labels are populated.. This is how the ten labels are populated.. Does this way slow the data loading

  One of my fren told me to use a label array method.. by which a new label wld b created, but since i know the total labels(10 labels) i used this method of adding labels and setiing them visiblity=false..

  cz if new labels r created i assumed, it will take much more time..

   

  Now pls help me.. Tanx in advance..

  
  
  
• Data accessing