c# - 在 DataRow 中设置特定列

Question

我正在尝试执行“批量”将一组规则插入到规则表中。该表具有以下结构:

RuleId int,
Rule nvarchar(256),
Domain nvarchar(256),
RuleTypeId int,
ExpirationDate DateTime

在我的插入函数中，我尝试在获得表的架构后设置一行的列，但该行似乎不包含指定的列(请注意，我没有指定RuleId，因为它是自动生成的):

private void InsertRulesXml(List<Rule> rules)
{
    using (DataSet ds = new DataSet())
    {
        using (DataTable rulesTable = GetSchemeForTable("RulesTable"))
        {
            // Add the rules to the table
            foreach (Rule rule in rules)
            {
                DataRow row = rulesTable.NewRow();

                // When I try to set the specific column it tells me that the column doesn't exist
                row["Rule"] = rule.Rule;
                row["Domain"] = rule.Domain;
                row["RuleTypeId"] = rule.RuleTypeId;
                row["ExpirationDate"] = rule.Expiration;

                rulesTable.Rows.Add(row);
            }
            ds.Tables.Add(rulesTable);

            // Convert the data to XML
            StringBuilder sb = new StringBuilder();
            using (StringWriter sw = new StringWriter(sb))
            {
                rulesTable.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema);
            }

            // Insert the XML rules
            InsertUpdateRulesXml(sb.ToString());
        }
    }
}

这是获取指定表的架构的函数:

private DataTable GetSchemeForTable(string tableName)
{
    DataTable ret = null;

    using (SqlConnection connection = GetContentScraperSqlConnection())
    {
        try
        {
            connection.Open();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = string.Format("SELECT TOP 0 [{0}].* FROM [{0}] WHERE 1 = 2;", tableName);
            using (IDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
            {
                ret = reader.GetSchemaTable();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception in GetSchemeForTable: " + ex.ToString());
        }
        finally
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
            connection.Dispose();
        }
    }

    return ret;
}

问题:当我尝试设置特定列时，它告诉我该列不存在。
异常信息:

An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll

Additional information: Column 'Rule' does not belong to table SchemaTable.

我怀疑获取表模式实际上并没有给我 Rule 表，而是 SchemaTable。我真正想要得到的是对应于 Rule 表的 DataTable(即它具有相同的模式)。

问题:在给定记录列表的行中设置列值的正确方法是什么？

Answer 1

GetSchemaTable 并不像您期望的那样执行。它返回一个 DataTable，其中行是源表的列，列是关于这些列的元数据。

如果你想得到一个“空的”DataTable 和给定表的模式，将你的函数更改为:

private DataTable GetSchemeForTable(string tableName)
{
    DataTable ret = new DataTable();

    try
    {
        connection.Open();
        SqlCommand command = connection.CreateCommand();
        command.CommandText = string.Format("SELECT * FROM [{0}] WHERE 1 = 2;", tableName);
        using (IDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
        {
            ret.Load(reader);
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine("Exception in GetSchemeForTable: " + ex.ToString());
    }
    finally
    {
        if (connection.State == ConnectionState.Open)
        {
            connection.Close();
        }
        connection.Dispose();
    }

    return ret;
}

请注意，我取出了 using block ，因为无论如何您都要在 finally block 中处理连接(这实际上是 using 所做的)。