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>();
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());
}
Connection.Close();
return TableList;
}
DataSet BuildDataSetForTableList(List<string> TableList)
{
DataSet dsreturn = new DataSet();
dsreturn.Tables.Add("Table1");
dsreturn.Tables[0].Columns.Add("ID");
foreach (string tablename in TableList) dsreturn.Tables[0].Columns.Add(tablename);
return dsreturn;
}
public DataView DVForm(string Param)
{
List<string> TableList = GetListOfTables(Param);
DataSet dsreturn = BuildDataSetForTableList(TableList);
foreach (string tablename in TableList)
{
var commandText = string.Format("SELECT ID, Col1 FROM {0}", tablename);
var newRow = dsreturn.Tables[0].NewRow();
SqlCommand cmd = new SqlCommand(commandText, Connection);
SqlDataReader dr;
Connection.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
newRow["ID"] = dr[0].ToString();
newRow[tablename] = dr[1].ToString();
}
dsreturn.Tables[0].Rows.Add(newRow);
dr.Close();
Connection.Close();
}
return dsreturn.Tables[0].DefaultView;
}
</string></string></string></string></string>