：使用多个DataRow

Question

如果我只有一个tableRow，那么我的代码就能工作，如下所示：

 DataRow tableRow = table.NewRow();
 tableRow["DisplayName"] = sqlReader["DisplayName"].ToString();
 table.Rows.Add(tableRow);

如果我添加了第二个，我的Repeater就不会显示任何内容，并且无法工作：

 DataRow tableRow = table.NewRow();
 tableRow["DisplayName"] = sqlReader["DisplayName"].ToString();
 tableRow["ColumnName"] = sqlReader["ColumnName"].ToString();
 table.Rows.Add(tableRow);

添加其中多个的正确代码是什么？

编辑：

我也尝试过，但没有成功：

 DataRow tableRow = table.NewRow();
 tableRow["DisplayName"] = sqlReader["DisplayName"].ToString();
 table.Rows.Add(tableRow);
 DataRow tableRow2 = table.NewRow();
 tableRow2["ColumnName"] = sqlReader["ColumnName"].ToString();
 table.Rows.Add(tableRow2);

这是我的存储过程代码：

SELECT DisplayName AS DisplayName
    FROM FormField
    WHERE EventId = @EventId AND 
    FormId = @FormId AND 
    Visible = 1
    UNION
    SELECT ColumnName AS ColumnName
    FROM FormField
    WHERE EventId = @EventId AND 
    FormId = @FormId AND 
    Visible = 1

编辑: C#

  DataTable table = new DataTable();
            table.Columns.Add("DisplayName", typeof(string));
            table.Columns.Add("ColumnName", typeof(string));

using (SqlConnection sqlConn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Events2"].ConnectionString))
                {
                    sqlConn2.Open();

                    using (SqlCommand sqlCmd2 = new SqlCommand())
                    {
                        sqlCmd2.Connection = sqlConn2;
                        sqlCmd2.CommandType = CommandType.StoredProcedure;
                        sqlCmd2.CommandText = "spGetFormFields";
                        sqlCmd2.Parameters.Add("@EventId", SqlDbType.NVarChar).Value = eventId;
                        sqlCmd2.Parameters.Add("@FormId", SqlDbType.NVarChar).Value = formId;
                        sqlCmd2.ExecuteNonQuery();
                        using (SqlDataReader sqlReader = sqlCmd2.ExecuteReader())
                        {
                            while (sqlReader.Read())
                            {
                                DataRow tableRow = table.NewRow();
                                tableRow["DisplayName"] = sqlReader["DisplayName"].ToString();
                                table.Rows.Add(tableRow);
                                DataRow tableRow2 = table.NewRow();
                                tableRow2["ColumnName"] = sqlReader["ColumnName"].ToString();
                                table.Rows.Add(tableRow2);
                            }
                            RepeaterForm.DataSource = table;
                            RepeaterForm.DataBind();
                        }

                        sqlConn2.Close();
                    }
                }

Answer 1

就像这样。SqlDataAdapter可以自动填充数据表。如果需要，还可以进行插入、更新和删除，但不能如图所示：

using (SqlConnection sqlConn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Events2"].ConnectionString)) 
using (SqlCommand sqlCmd2 = new SqlCommand())
{
    sqlConn2.Open();
    sqlCmd2.Connection = sqlConn2;
    sqlCmd2.CommandType = CommandType.StoredProcedure;
    sqlCmd2.CommandText = "spGetFormFields";
    sqlCmd2.Parameters.Add("@EventId", SqlDbType.NVarChar).Value = eventId;
    sqlCmd2.Parameters.Add("@FormId", SqlDbType.NVarChar).Value = formId;

    using (SqlDataAdapter da = new SqlDataAdapter(sqlCmd2))
    {
        da.Fill(Table);
        RepeaterForm.DataSource = table;
        RepeaterForm.DataBind();
    }

    sqlConn2.Close();
}

Answer 2

+1 to Crowcoders上面关于列名被搞砸的评论。

如果您确实想这样做，我建议您可以尝试将查询更改为

SELECT DisplayName AS DisplayName, ColumnName AS ColumnName FROM FormField WHERE EventId = @EventId AND FormId = @FormId AND Visible = 1

看看这是否给了你想要的结果。不过，我还是会按照@Crowcoder建议的方式去做。