Multiple datasets - am I doing this right?

Posted on 2006-03-30
Last Modified: 2010-04-23
I need to execute two stored procedures and create two datasets.
This is what I have done so far, I'm not getting any errors, but wanted to now if I am doing this "right".
           connection = New SqlConnection(ConfigurationManager.ConnectionStrings("WachoviaConnectionString1").ConnectionString)
            'Initialize the command
            netPayCommand = New SqlCommand("NetPay", connection)
            'Mark the command as stored procedure
            netPayCommand.CommandType = CommandType.StoredProcedure
            da = New SqlDataAdapter(netPayCommand)
            ds = New DataSet
            'Fill data dataset
            da.Fill(ds, "dtNetPay")

            expensesCommand = New SqlCommand("MonthlyExpenses", connection)
            expensesCommand.CommandType = CommandType.StoredProcedure
            da = New SqlDataAdapter(expensesCommand)
            da.Fill(ds, "dtExpenses")
Question by:JRockFL
    LVL 34

    Assisted Solution

    by:Brian Crowe
    create separate dataadapters but use the same dataset.  You could recycle the dataadapter if you change the .selectcommand.commandtext and the .tablemappings but it's better to have separate adapters in case you decide you want to write back to your database.

    with daNetPay
       .selectcommand = new sqlcommand
       with .selectcommand
          .connection = New SqlConnection(ConfigurationManager.ConnectionStrings("WachoviaConnectionString1").ConnectionString)
          .commandtext = "SELECT cola, colb, colc FROM NetPay"
       end with

       .tablemappings.add("Table", "NetPay")
       .acceptchangesduring fill = true
    end with

    with daExpenses
       .selectcommand = new sqlcommand
       with .selectcommand
          .connection = New SqlConnection(ConfigurationManager.ConnectionStrings("WachoviaConnectionString1").ConnectionString)
          .commandtext = "SELECT cola, colb, colc FROM Expenses"
       end with

       .tablemapping.add("Table", "Expenses")
       .acceptchangesduring fill = true
    end with

       danetpay.fill(ds)   'creates "NetPay" datatable in ds
       daexpenses.fill(ds)   'creates "Expenses" datatable in ds
    catch ex as exception"Error Loading Data: " + ex.message)
    end try
    LVL 14

    Expert Comment

    From what I've seen in your code, you are using two different dataadapter instances (same variable) to fill two datatables in the same dataset. If you don't need to update the database thereafter, this is fine. If you want to use dataadapters to update database, then you probably want to use two class level variables for the two dataadapters, and one class level variable for the dataset. The two dataadapters are responsible for taking care of the two datatables respectively.
    e.g. When filling the dataset, you can use:
        daNetPay.Fill(ds, "dtNetPay")
        daExpenses.Fill(ds, "dtExpenses")

    When updating the dataset, you can use:
        daNetPay.Update(ds, "dtNetPay")  
        daExpenses.Update(ds, "dtExpenses")

    I would usually drag two dataadapters to the designer and configure them, and use the code above when needed.
    LVL 8

    Author Comment

    Hello Thanks for the replies. I guess using the command object or data adapter object confuses me a little.

      netPayCommand = New SqlCommand("NetPay", connection)
     da = New SqlDataAdapter(netPayCommand)

    Do I have to used a command and a dataadapter?
    LVL 14

    Accepted Solution

    Two command variables and one dataadapter variable is fine, since you created two instances of the dataadapter. One command variable and one dataadapter variable is also fine, as long as you create two instances of commands and dataadapters. But I would use two command variables and two dataadapter variables just to reduce the confusion. And like I said, dragging dataadapters to the designer and configure them would be much easier than writing all the codes yourself.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, 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…

    758 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

    8 Experts available now in Live!

    Get 1:1 Help Now