Click here to Skip to main content
15,896,912 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

C#
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

1 solution

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.



C#
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>
 
Share this answer
 
Comments
TANicox 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900