Using Two DataSets on One Windows Form RRS feed

  • Question

  • SQL 2005 Server, Visual Studio 2010

    I have a company financial Database named ESS developed by Microsoft and a reporting Database named LIMS developed by me. Both are in Microsoft SQL databases in the same instance.

    On my windows form, I want to access the LIMS data we generate internally along with the customer records held in the ESS data.

    With the New DataSources wizard, I can create connection strings with Initial Catalog = ESS as ESSDataSet. Likewise, I can create connection strings with Initial Catalog = LIMS as LIMSDataSet. I have created many web pages and forms with each of the datasets independently, but never combined.

    I can use SELECT * FROM ESS.dbo.MyFinancialTables and SELECT * FROM LIMS.dbo.MyNewTables with no problems since they are in the same instance and I have the privileges; however, I would like to tie them to a DataSet.

    In dBase, I combined both databases into a DataModule. Is there a way to combine in SQL?

    Thursday, July 22, 2010 6:25 PM

All replies

  • A single DataSet can contain (almost) any number of DataTables. These DataTables can be sourced from anywhere you choose. This means using one connection you could populate one DataTable and with another connection populate the other. Another approach is to "link" the two database servers (if they are on different servers) or simply JOIN the two database rowsets together using SQL that fully references the database along with the Table.

    William Vaughn
    Mentor, Consultant, Trainer, MVP

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, July 22, 2010 6:47 PM
  • William, what you are saying is I can use two Connections in the same form with each Connection linked to one of the DataSets, correct?  I can write the SELECT statement to JOIN them with no problems; I just want to make sure I have a Connection(s) that will allow me use both Databases in the same form.  The new DataSource wizard to create the DataSet does not allow more than one database in a DataSet.

    FWIW, both Databases are on the same server in the same instance.

    To kind of summarize what I am doing:

    The form opens, I input the project number, it goes to the ESS database to get the client information which populates the client textboxes.  For the remainder of the form, it accesses the LIMS database for INSERT, UPDATE, and DELETE commands to populate the rest of the form textboxes.

    Thank you for your help!


    Thursday, July 22, 2010 7:28 PM