Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

ADO.Net Breakdown

Im working on a project that uses vb.net with an access database.

I'm quite new to this and am just looking for a clarifying overview of how things work as my view is slightly cloudy.

My understanding is that the following are required in this order:-

DataAdapter  - to connect to source i.e database
DataSet - to act as a cache.
Datarow - to manipulate the data in the data set whether that be add, amend or delete.

If I want to take details from one table within a database and work with that data, will the above suffice or do i need to create a datatable?

any1 clear this up 4 me?
  • 2
3 Solutions
cmd.CommandText = "select * from table"
        da.SelectCommand = cmd
        da.Fill(dataset1, "Table1")

        Dim dt As DataTable = dataset1.Tables("table1")
        Dim dr As DataRow
        For Each dr In dt.Rows
            textbox1.text += "" & dr.Item(0) 'field 1
            textbox2.Text  += "" & dr.Item(1) 'field2

also put in the connection part

this is just 1 example that I used before, this is just a little code that loops  through and adds data to the textboxes....
One thing u have to remember there are a million different ways to do it, and u will hear all kinds of opinions. U just have to decide what is best for u....

My 2 cents
Hi scm0sml

VB.NET is typical Microsoft, there's always more than one way to get to things.
You are right in the things that are required to do what you want but there are othre options as well. We tend to choose them based on what's best suited for the scenario.
I'd just take a scenario and see if I can help you out.

Our case is we need to read a single table from the database, do some manipulations on it (AddNew/Edit/Delete) and then save Back.

First we need a DataAdapter, DataAdpater is a Link between your front End Cache and the actual back end storage. So we require it twice, once when we are filling our cache second when we are saving back our cache to the database.

Second DataSet, surprisingly Not required here. The basic unit for our Cache is DataTable and a DataSet is a collection of DataTables. Now in our case we are dealing with one table (at a time) only so we do not require DataSet just the dataTable.

Third DataRow, this is optional, we can directly Access the Rows of the dataTable alternatively for our ease we can fetch a particular Row from the datatable in a separate Datrow.

Now a small eg.

'Declare this at Form Level
Dim DT as New DataTable

'On Form Load Fill this Cache i.e. the DataTable
Dim DA as New OleDBDataAdapter("Select * From SomeTable",YourConnectionString)
DA.Fill(DT) 'Fill the Cache

'Now we can use this DT as we want
'To Edit a Record
DT.Rows(5).Item("SomeColumn")="A New value"

'Now Alternatively we can use a DataRow for this
Dim DR as DataRow=DT.Rows(5)
Dr("SomeColumn")="A New Value"
'You might not appreciate this new way here but it saves a lot of typing in case you have 10 columns

'To Delete a Row

'To Add a New Row
Dim DR as dataRow=DT.NewRow
Dr("SomeColumn")="Some Value"

'Now in Order to save back these changes we again require a dataAdapter
Dim DA as New OleDBDataAdapter("Select * From SomeTable",YourConnectionString)
Dim CB as New CommandBuilder(DA)

'The CommandBuilder generates the Insert/Update/Delete command automatically for the DataAdapter to Update the changes to the BackEnd.

So this is how it is in a simple scenario, when things get complex (like Multiple tables, Joins, Linkings etc.) the coding gets a bit complex...

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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