We help IT Professionals succeed at work.

Multiple datasets - am I doing this right?

JRockFL
JRockFL asked
on
Medium Priority
266 Views
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")
Comment
Watch Question

Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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.
   

Author

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?
Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.