Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL
Hello, I'm trying to fill a DataSet with values of several tables in SQL Database.
 
The number of tables is dynamic (all this tables has common columns and has the same ID always), so I have other tables with the name of the tables to insert into DataSet...
 
I used this code for insert the rows, but doesn't fill anything:
 
 
public DataView DVForm (string Param)
{
    List<string> TableList = new List<string> //Get All tables
    string cmdGetTableListstr = string.Format("SELECT DBName FROM TableList WHERE Param='{0}'", Param);
    SqlCommand cmdGetTableList = new SqlCommand (cmdGetTableListstr, Connection);
    SqlDataReader drGetTableList;
    Connection.Open();
    drGetTableList=cmdGetTableList.ExecuteReader();
    drGetTableList.Read();
    while (drGetTableList.Read())
    {
        TableList.Add(drGetTableList[0].ToString()); //Full the TableList (List) with the list in the TableList (Table)
    }
    Connection.Close();
    
    List<string> cmdColumns = new List<string>();
    DataSet dsreturn = new DataSet();
    dsreturn.Tables.Add("Table1");
    
    foreach (string tablename in TableList)
    {
        cmdColumns.Add(string.Format("SELECT ID,Col1 FROM {0} ORDER BY ASC", tablename ); //Full the SQL Commands with respective table name
        dsreturn.Table[0].Columns.Add(tablename); //Add columns
    }
    
    for (int i=0; i<cmdColumns.Count;i++)
    {
        SqlCommand cmdFull = new SqlCommand(cmdColumns[i],Connection);
        SqlDataReader drFull;
        dsreturn.Tables[0].NewRow();
        Connection.Open();
        drFull=cmdFull.ExecuteReader();
        drFull.Read();
        while (drFull.Read())
        {
            for (int x = 0; x<tablename.Count;x++)
            {
                dsreturn.Tables[0].Rows[x][i]=drFull[0].ToString();
                dsreturn.Tables[0].Rows[x][i+1]=drFull[1].ToString();
            }
        }
    }
    return dsreturn.Tables[0].DefaultView;
}
Posted 4-Oct-12 16:34pm
TANicox1.2K

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

There are a few bad things in your code:
You make two consecutive calls to drFull.Read() by doing such you ignore the 1st record.
You create a new row in your dataset but never add it to the table afterwards : that's why your dataset is empty in the end.
You don't close your datareader as well as the connection.
 
Now from a functional point of view:
It looks like you are trying to pivot data coming from multiple tables to build a dataset.
Each table has the same structure : Id, Col1
You want to have a dataset with following structure :
Id, ValFromTable1Col1, ValFromTable2Col1, ValFromTable2Col1, ValFromTable3Col1, etc...
 

If I got things right then the following code should bring you closer to what you expect.
 

 
 
List<string> GetListOfTables(string Param)
        {
            List<string> TableList = new List<string>(); //Get All tables

            string cmdGetTableListstr = string.Format("SELECT DBName FROM TableList WHERE Param='{0}'", Param);
            SqlCommand cmdGetTableList = new SqlCommand(cmdGetTableListstr, Connection);
            SqlDataReader drGetTableList;
 
            Connection.Open();
            drGetTableList = cmdGetTableList.ExecuteReader();
            drGetTableList.Read();
            while (drGetTableList.Read())
            {
                TableList.Add(drGetTableList[0].ToString()); //Full the TableList (List) with the list in the TableList (Table)
            }
            Connection.Close();
 
            return TableList;
        }
 
        DataSet BuildDataSetForTableList(List<string> TableList)
        {
            DataSet dsreturn = new DataSet();
            dsreturn.Tables.Add("Table1");
 
            //if ID is your common Discriminator accross all tables you probably want to add it here...
            dsreturn.Tables[0].Columns.Add("ID");
 
            foreach (string tablename in TableList) dsreturn.Tables[0].Columns.Add(tablename); //Add one column per table
            return dsreturn;
        }
 

        public DataView DVForm(string Param)
        {
            List<string> TableList = GetListOfTables(Param); 
 
            DataSet dsreturn = BuildDataSetForTableList(TableList);
 
            // Fill the dataset
            foreach (string tablename in TableList)
            {
                var commandText = string.Format("SELECT ID, Col1 FROM {0}", tablename); //Full the SQL Commands with respective table name

                var newRow = dsreturn.Tables[0].NewRow();
                SqlCommand cmd = new SqlCommand(commandText, Connection);
                SqlDataReader dr;
                Connection.Open();
                dr = cmd.ExecuteReader();
 
                // dr.Read(); // If you read twice then you lose first record so just read when you are in the loop

                while (dr.Read())
                {
                    newRow["ID"] = dr[0].ToString();
                    newRow[tablename] = dr[1].ToString();
 
                    //for (int x = 0; x < TableList.Count; x++)
                    //{
                    //    dsreturn.Tables[0].Rows[x][i] = dr[0].ToString();
                    //    dsreturn.Tables[0].Rows[x][i + 1] = dr[1].ToString();
                    //}

                }
 
                dsreturn.Tables[0].Rows.Add(newRow); // You must add your new row to the dataset

                dr.Close(); // Don't forget to close your datareader
                Connection.Close(); // Don't forget to close your connection
            }
 
            return dsreturn.Tables[0].DefaultView;
        }
</string></string></string></string></string>
  Permalink  
Comments
TANicox at 5-Oct-12 21:06pm
   
Thanks for your answer, now I'm gonna try this!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Dnyaneshwar@Pune 604
1 thatraja 370
2 OriginalGriff 319
3 Sergey Alexandrovich Kryukov 241
4 Gihan Liyanage 194
0 OriginalGriff 737
1 Dnyaneshwar@Pune 604
2 Kornfeld Eliyahu Peter 505
3 Sergey Alexandrovich Kryukov 372
4 thatraja 370


Advertise | Privacy | Mobile
Web03 | 2.8.140902.1 | Last Updated 4 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100