Multiple datasets - am I doing this right?

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")
LVL 8
JRockFLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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

try
   danetpay.fill(ds)   'creates "NetPay" datatable in ds
   daexpenses.fill(ds)   'creates "Expenses" datatable in ds
catch ex as exception
   messagebox.show("Error Loading Data: " + ex.message)
end try
amyhxuCommented:
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.
   
JRockFLAuthor Commented:
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?
amyhxuCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.