none
Display dataset data in GridView from multiple data tables RRS feed

  • Question

  • Hi this is driving me nuts. I can't get dataset tables to display in GridView from methods that I write in a separate class. Even simple methods.

    I ultimately want to display dataset data from two tables similar to SQL INNER JOIN. I do have a TableAdapter set up with an INNER JOIN

    but I guess this doesn't matter with a dataset. Please help.

     public DataTable SomeTable()
        {
            DataSet ds = new DataSet();
            DataSetTableAdapters.SometableTableAdapter sta =
                new DataSetTableAdapters.SometableTableAdapter();
            sta.Fill(ds.Sometable);
            return ds.SomeOtherTable;

         }

    Monday, October 10, 2011 2:19 AM

Answers

  • And I should amend my previous reply to say that if the GridView *does* support multiple DataTables (much like the WinForm older control, the DataGrid), then all you need to do is set up a relationship between 2 DataTables in your DataSet and they will both be displayed. Do you need help with how to set up relationships?

    // SomeTable is the parent, OtherTable is the child
    DataRelation dr = new DataRelation("MyRelation", ds.SomeTable.Columns["code"], ds.OtherTable.Columns["othercode"]);
    
    // you can set the DataSource of the grid with either of the below syntaxes:
    
    oGrid.DataSource = ds.SomeTable;
    
    // -or-
    
    oGrid.DataSource = ds.Relations["MyRelation"].ParentTable;
    

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, October 12, 2011 3:22 PM

All replies

  • Can you please explain this in detail..?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, October 10, 2011 6:08 AM
  • I recommend forgetting about the TableAdapters and do it the old-fashioned way with DataAdapters. However, we need some more information on exactly how you want to approach this.

    You mentioned GridView, so I'm assuming this is an ASP.NET grid? I'm not sure if it works similarly to a WinForm DataGridView, but I *do* know that the DataGridView can only be bound to one DataTable. So, yeah, if the GridView works the same way, then you'll have to use a join to put the two database tables into one DataTable. The code you posted above doesn't make much sense, though. Sorry.

    // method to fill the dataset
    public void FillMyDataSet(MyDataSet ds)
    {
        string sql = "SELECT a.*, b.* FROM MyTable a JOIN MyOtherTable b ON a.key = b.key";
        using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
        {
            da.Fill(ds, "MyTable");
        }
    }
    
    
    // call it like this
    MyDataSet dsMine = new MyDataSet();
    this.FillMyDataSet(dsMine);
    MyGridView.DataSource = dsMine;
    

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, October 12, 2011 4:59 AM
  • Check

    http://msdn.microsoft.com/en-us/library/aa984294%28v=vs.71%29.aspx

    try to got with the following section, looks like providing the exact sol you are looking for


    Mark Answered, if it solves your question
    Rohit Arora
    Wednesday, October 12, 2011 10:11 AM
  • Hi,

     

    You can convert DataSet to DataTable as follows

    DataTable dt=ds.SomeOtherTable[0];

     

    Copy and Paste the below code:

     public DataTable SomeTable()
        {

           DataTable dt=new DataTable();
            DataSet ds = new DataSet();
            DataSetTableAdapters.SometableTableAdapter sta =
                new DataSetTableAdapters.SometableTableAdapter();
            sta.Fill(ds.Sometable);

           dt=ds.SomeOtherTable[0];
            return dt;

         }

     

    Hope it helps you


    PS.Shakeer Hussain
    Wednesday, October 12, 2011 11:27 AM
  • And I should amend my previous reply to say that if the GridView *does* support multiple DataTables (much like the WinForm older control, the DataGrid), then all you need to do is set up a relationship between 2 DataTables in your DataSet and they will both be displayed. Do you need help with how to set up relationships?

    // SomeTable is the parent, OtherTable is the child
    DataRelation dr = new DataRelation("MyRelation", ds.SomeTable.Columns["code"], ds.OtherTable.Columns["othercode"]);
    
    // you can set the DataSource of the grid with either of the below syntaxes:
    
    oGrid.DataSource = ds.SomeTable;
    
    // -or-
    
    oGrid.DataSource = ds.Relations["MyRelation"].ParentTable;
    

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, October 12, 2011 3:22 PM
  • I have a somewhat similar problem, and wonder if any of you can help me. The dataset returned from a stored procedure has 3 tables as follows: Tables[0] list of columns to be displayed, in horizontal order Tables[1] row IDs of data returned, sorted according to criteria passed in Tables[3] value of each data row / column For example, if there are 3 columns and 5 records, the first table will have 3 records, the second will have 5 (row IDs 1 through 5), and the third will have 15 records. The web page for this app displays the data using complicated, nested repeater controls with java script, but I want a single DataTable that can be exported to Excel, etc. There must be a way to relate these tables or query them to produce the results I want, but I'm not sure how to do it. Suggestions? THANKS!!
    Wednesday, June 13, 2012 3:32 PM
  • Phil --- exactly what *are* the results you want? I'll assume that you want one DataTable, containing 3 columns, but I can't figure out (based on your description) whether it will have 5 rows or 15. Could you explain that part a bit more? Here are some code snippets off the top of my head that might help get you started:

    // Assuming that dsOrig DataSet contains your 3 tables
    
    // First, let's create the new DataTable and add the columns
    DataTable MyTable = new DataTable("MyTable");
    foreach (DataRow row in dsOrig.Tables[0])
    {
        // I'm assuming the first column contains the column name
        // I'm also assuming they are typeof(string), which is the default
        MyTable.Columns.Add(row[0]); 
    }
    
    // Now, let's add in the rows containing the ID
    // Hopefully, you know the column name, or perhaps it's always the first column
    foreach (DataRow row in dsOrig.Tables[1])
    {
        DataRow NewRow = MyTable.NewRow();
        NewRow["ID"] = row["ID"];
        MyTable.Rows.Add(NewRow);
    }

    And I don't know what to do with the third table.

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Thursday, June 14, 2012 3:37 PM
  • Bonnie, you gave me all the information I need to figure out a solution - THANKS!!   I had actually found a different work-around - modifying the stored proc so that, depending on a flag parameter, it would return either the multi-table dataset with one pageful of data, or all records as a single table. But when a user's preference is for the information to be returned in French (with many column headings that include single quotes), there were other problems.  I fixed that as well.

    However, since there are other web pages in this app that must be exportable, and the single-table solution cannot suffice, I will use your methodology.

    Just FYI: the second table has just row numbers (row_id), and the third has one record per row_id / column. For example:

    ds.Tables[1]

    1

    2

    3

    ds.Tables[2]

    <value for row_id 1, column A>

    <value for row_id 1, column B>

    <etc. for however many columns there are> Then:

    <value for row_id 2, column A>


    Therefore, your second loop would have another loop nested inside it, something like:

    foreach (DataRow row in dsOrig.Tables[1]) { DataRow NewRow = MyTable.NewRow(); NewRow["ID"] = row["ID"];

    int colNum = 0;

    foreach (DataRow colRow in dsOrig.Tables[2][row["ID"])

       {
    

    NewRow[colNum++] = colRow["ColValue"];

       }

    MyTable.Rows.Add(NewRow);

    }

    At least, I think that's how it would go. Does it make sense to you?

    Thanks again!!


    Monday, June 25, 2012 5:29 PM
  • Yeah, that sounds like it should work ok ... I assume you've already tried it and it does! ;0)

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, June 26, 2012 4:56 AM