问题填充数据集 [英] Problem Filling DataSet

问题描述

您好，我试图用SQL数据库中几个表的值填充数据集.

表的数量是动态的(所有这些表都有公共列，并且始终具有相同的ID)，因此我还有其他一些表，这些表的名称要插入到DataSet中...

我使用此代码插入行，但未填充任何内容:

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;
}

推荐答案

您的代码中有一些不好的地方:
这样，您将连续两次调用drFull.Read()，而忽略了第一个记录.
您在数据集中创建了一个新行，但之后再也没有将其添加到表中:这就是为什么数据集最后为空的原因. 您不会关闭数据读取器以及连接.

现在从功能的角度来看:
看来您正在尝试透视来自多个表的数据以构建数据集.
每个表具有相同的结构:Id，Col1
您想要一个具有以下结构的数据集:
Id，ValFromTable1Col1，ValFromTable2Col1，ValFromTable2Col1，ValFromTable3Col1等...


如果我做对了，那么以下代码应使您更接近您的期望.

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>

这篇关于问题填充数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！