ADO.Net Breakdown

Posted on 2005-04-07
Last Modified: 2010-04-23
Im working on a project that uses 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?
Question by:scm0sml
    LVL 8

    Assisted Solution

    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
    LVL 8

    Assisted Solution

    LVL 19

    Accepted Solution

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    755 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

    24 Experts available now in Live!

    Get 1:1 Help Now