views:

1339

answers:

3

Hello everyone,

I am using C# + .Net 3.5 + VSTS 2008 + ADO.Net + SQL Server 2008. I have two related tables in SQL Server (foreign key relationship). And I want to load the two tables as two datatables in a dataset. Any reference code to do this?

thanks in advance, George

+2  A: 

Try this

Dim myAdapter as SqlDataAdapter = new SqlDataAdapter(
      “SELECT * FROM Customers; SELECT * FROM Orders“, connection)

myAdapter.Fill(dsTables)
dsTables.Tables(0).TableName = “Customers“)
dsTables.Tables(1).TableName = “Orders“)

http://pietschsoft.com/post/2004/08/22/Fill-DataSet-with-multiple-Tables-and-update-them-with-DataAdapter.aspx

Anuraj
If Customers table and Orders table has foreign key relationships, will the relationships be kept in dsTables.Tables(0) and dsTables.Tables(2)?
George2
BTW: I have read your sample, and the dsTables is a type of what? And how could I assign dsTables.Tables property to dsTables?
George2
dsTables is Dataset, sorry for the confusion :(
Anuraj
From MSDNWhen handling batch SQL statements that return multiple results, the implementation of FillSchema for the .NET Framework Data Provider for OLE DB retrieves schema information for only the first result. To retrieve schema information for multiple results, use Fill with the MissingSchemaAction set to AddWithKey.http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.fill(VS.71).aspx
Anuraj
+1  A: 

George,

Can you please clarify your question - is it about filling mulitple tables in dataset or specifically realted tables only.

As far loading multiple tables is concerned you can refer to following code (this code is avialable at MSDN http://msdn.microsoft.com/en-us/library/5fd1ahe2.aspx):

static void Main()
 {
   DataSet dataSet = new DataSet();

    DataTable customerTable = new DataTable();
    DataTable productTable = new DataTable();

    // This information is cosmetic, only.
    customerTable.TableName = "Customers";
    productTable.TableName = "Products";

    // Add the tables to the DataSet:
    dataSet.Tables.Add(customerTable);
    dataSet.Tables.Add(productTable);

    // Load the data into the existing DataSet. 
    DataTableReader reader = GetReader();
    dataSet.Load(reader, LoadOption.OverwriteChanges,
        customerTable, productTable);

    // Print out the contents of each table:
    foreach (DataTable table in dataSet.Tables)
       {
        PrintColumns(table);
        }

    Console.WriteLine("Press any key to continue.");
    Console.ReadKey();
}

private static DataTable GetCustomers()
{
    // Create sample Customers table.
    DataTable table = new DataTable();
    table.TableName = "Customers";

    // Create two columns, ID and Name.
    DataColumn idColumn = table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Name", typeof(string));

    // Set the ID column as the primary key column.
    table.PrimaryKey = new DataColumn[] { idColumn };

    table.Rows.Add(new object[] { 0, "Mary" });
    table.Rows.Add(new object[] { 1, "Andy" });
    table.Rows.Add(new object[] { 2, "Peter" });
    table.AcceptChanges();
    return table;
}

private static DataTable GetProducts()
{
    // Create sample Products table.
    DataTable table = new DataTable();
    table.TableName = "Products";

    // Create two columns, ID and Name.
    DataColumn idColumn = table.Columns.Add("ID",
        typeof(int));
    table.Columns.Add("Name", typeof(string));

    // Set the ID column as the primary key column.
        table.PrimaryKey = new DataColumn[] { idColumn };

    table.Rows.Add(new object[] { 0, "Wireless Network Card" });
    table.Rows.Add(new object[] { 1, "Hard Drive" });
    table.Rows.Add(new object[] { 2, "Monitor" });
    table.Rows.Add(new object[] { 3, "CPU" });
    table.AcceptChanges();
    return table;
}

private static void PrintColumns(DataTable table)
{
    Console.WriteLine();
    Console.WriteLine(table.TableName);
    Console.WriteLine("=========================");
    // Loop through all the rows in the table:
    foreach (DataRow row in table.Rows)
    {
        for (int i = 0; i < table.Columns.Count; i++)
        {
            Console.Write(row[i] + " ");
        }
        Console.WriteLine();
    }
}

private static DataTableReader GetReader()
{
    // Return a DataTableReader containing multiple
    // result sets, just for the sake of this demo.
    DataSet dataSet = new DataSet();
    dataSet.Tables.Add(GetCustomers());
    dataSet.Tables.Add(GetProducts());
    return dataSet.CreateDataReader();
}

Here is the code for managing parent child relation in data sets

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DataRelation
  Inherits System.Web.UI.Page

  Protected lblDisplay As System.Web.UI.WebControls.Label

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim objConn As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet
    Dim dtrParent As DataRow
    Dim dtrChild As DataRow

    objConn = New SqlConnection(ConfigurationSettings.Appsettings("NorthwindConnection"))
    da = New SqlDataAdapter("SELECT * FROM Categories", objConn)
    ds = New DataSet()
    Try
      objConn.Open()
      da.Fill( ds,"Categories")
      da.SelectCommand = New SqlCommand("SELECT * FROM Products", objConn)
      da.Fill(ds, "Products")
    Catch exc As SqlException
      Response.Write(exc.ToString())
    Finally
      objConn.Dispose()
    End Try 
    'Create the Data Relationship
    ds.Relations.Add("Cat_Prod",ds.Tables("Categories").Columns("CategoryID"), _
                            ds.Tables("Products").Columns("CategoryID"))

    'Display the Category and Child Products Within
    For each dtrParent in ds.Tables("Categories").Rows
      lblDisplay.Text &= "<h3>" & dtrParent("CategoryName") & "</h3><ul>"
      For each dtrChild in dtrParent.GetChildRows("Cat_Prod")
        lblDisplay.Text &= "<li>" & dtrChild("ProductName") & "</li>"
      Next
      lblDisplay.Text &= "</ul>"
    Next
  End Sub

End Class

You can find further explanation over here

Rutesh Makhijani
Thanks Rutesh, sorry for my confusing question. I want to retrieve all content of two tables from database to two DataTables into a DataSet, and two tables are of foreign key relationship in database and I want to keep the relationship in the two retrieved DataTable. Does your solution work for me?
George2
My confusion of your code sample is, I want to know in my scenario (load all table content), whether I need to "Set the ID column as the primary key column" as method GetProducts and GetCustomers showed above? What is the function of so-called "Set the ID column as the primary key column"?
George2
George, Previous sample is only for adding multiple tables, if you want the refential integrity (foreing key relations) to be maintained then you need to use DataRelationsI am posting the sample in next reply
Rutesh Makhijani
A: 

Here is a sample code on how to load a DataSet using LINQ queries Here 2 tables have got a relationship. "dc" is the data context.

            var query = dc.GetTable<Media>().Where(s => s.MediaID == new Guid("A72AA79A-6C40-4D6B-A826-241553FECDFE"));
            var query1 = dc.GetTable<MediaVersion>().Where(s => s.MediaID == new Guid("A72AA79A-6C40-4D6B-A826-241553FECDFE"));
            var query2 = dc.GetTable<RootPath>().Where(s => s.RootPathID == new Guid("62145B2C-BA36-4313-8CA2-0F224F8FE7E8"));


            SqlCommand cmd = dc.GetCommand(query) as SqlCommand;
            //Load first
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ada.Fill(ds, "Media");
            //Load second
            cmd = dc.GetCommand(query1) as SqlCommand;
            ada.SelectCommand = cmd;
            ada.Fill(ds, "MediaVersion");
            ds.Relations.Add("Med_MedVer", ds.Tables["Media"].Columns["MediaID"],
                       ds.Tables["MediaVersion"].Columns["MediaID"]);

            //Load third independent table
            cmd = dc.GetCommand(query2) as SqlCommand;
            ada.SelectCommand = cmd;
            ada.Fill(ds, "RootPath");