Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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")
0
JRockFL
Asked:
JRockFL
  • 2
2 Solutions
 
Brian CroweCommented:
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
0
 
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.
   
0
 
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?
0
 
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.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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