使用没有主键的SQLAdapter和SQLCommandBuilder

Question

我正在探索如何使用SqlCommandBuilder和Adapter.Update()同步一个DataGridView和一个SQL表。

我想使用SqlCommandBuilder.GetUpdateCommand()自动生成SQL语句，但是它在"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information“中失败了。这是有意义的，因为我的表没有主键。

我不能在源表上设置主键，但是我有一个标识列。我想向命令生成器指定要使用哪个列作为主键。DataTable类上有这样一个特性，但它似乎对SqlCommandBuilder没有任何影响。

我尝试了以下几点：

// Add Primary Key to help command builder identify unique rows
Table.PrimaryKey = new DataColumn[] { Table.Columns["ComponentID"] };

但是似乎这些信息并没有传播到SqlDataAdapter和SqlCommandBuilder，因为我仍然得到了错误。

这是我试过的命令：

// get data
Adapter.Fill(Table);

// specify primary key column
Table.PrimaryKey = new DataColumn[] { Table.Columns["ComponentID"] };

cmdBuilder = new SqlCommandBuilder(Adapter);

cmdBuilder.GetUpdateCommand() // <-- Error here

这里是否有任何解决方案，还是必须指定更新和插入语句？

Answer 1

因此，正如@PanagiotisKanavos所指出的，SqlCommandBuilder不支持没有主键的表，即使您在DataTable对象中设置了它。

因此，我别无选择，只能编写自己的命令生成器。

要使用它，您需要提供：

connection)

• The
• SqlConnection to
• 数据库(如果没有在

SQLAdapter中提供，则选择命令已经设置(有构造函数))

你如何使用它：

string selectQuery = "SELECT * FROM [dbCache].[dbo].[Component] ORDER BY [ComponentType] DESC";

// Initialize the SqlDataAdapter object by specifying a Select command 
// that retrieves data from the table.
Adapter = new SqlDataAdapter(selectQuery, Connection)
{
    FillLoadOption = LoadOption.PreserveChanges,
    MissingSchemaAction = MissingSchemaAction.AddWithKey
};

// build all sql commands
Adapter = SQLCommandBuilder.BuildAll(Adapter, Connection);

接下来，完整的类代码：

public static class SQLCommandBuilder
{
    public enum CommandType
    {
        Update = 0,
        Insert = 1,
        Delete = 2
    }

    /// <summary>
    /// Build and add the insert, update and delete commands to the given SqlAdapter
    /// </summary>
    /// <param name="adapter"></param>
    /// <param name="connection"></param>
    /// <param name="database"></param>
    /// <param name="idColumns"></param>
    /// <returns>the modified adapter</returns>
    public static SqlDataAdapter BuildAll(
        SqlDataAdapter adapter, SqlConnection connection, string database = null, string[] idColumns = null
        )
    {
        DataTable data = new DataTable();
        // fill datatable with select data
        adapter.Fill(data);

        if (database == null)
        {
            if (string.IsNullOrEmpty(connection.Database))
            {
                throw new ArgumentException(
                    "Could not determine database from connection object. Please specify it manually"
                    );
            }
            // get database from connection
            database = connection.Database;
        }
        // get table name
        string table = data.TableName;
        // get all column names
        string[] allColumns = data.Columns.Cast<DataColumn>()
            .Select(col => col.ColumnName).ToArray();
        
        // only get id columns if the user has not manually specified them
        if (idColumns == null)
        {
            // get id columns from the table. This includes any unique or auto-incrementing column
            idColumns = data.Columns.Cast<DataColumn>()
                .Where(col => col.AutoIncrement || col.Unique)
                .Select(col => col.ColumnName)
                .ToArray();

            // if no id columns found
            if (idColumns.Length == 0)
            {
                // throw an error
                throw new Exception("No ID columns found in the table!");
            }
        }
        else
        {
            // if the specfified columns don't exist
            if (idColumns.All(id => allColumns.Contains(id, StringComparer.CurrentCultureIgnoreCase)))
            {
                // throw an error
                throw new ArgumentException("Provided ID columns do not exist in the table!");
            }
        }
        

        // generate all commands
        adapter.InsertCommand =
            BuildCommand(CommandType.Insert, connection, database, table, allColumns, idColumns);
        adapter.UpdateCommand =
            BuildCommand(CommandType.Update, connection, database, table, allColumns, idColumns);
        adapter.DeleteCommand =
            BuildCommand(CommandType.Delete, connection, database, table, allColumns, idColumns);

        // return the modified adapter
        return adapter;
    }

    /// <summary>
    /// Build a command of the given type using the provided parameters
    /// </summary>
    /// <param name="cmdtype"></param>
    /// <param name="connection"></param>
    /// <param name="database"></param>
    /// <param name="table"></param>
    /// <param name="allColumns"></param>
    /// <param name="idColumns"></param>
    /// <returns></returns>
    public static SqlCommand BuildCommand(
        CommandType cmdtype, SqlConnection connection, string database, string table, 
        string[] allColumns, string[] idColumns
        )
    {
        if (allColumns == null || allColumns.Length == 0)
        {
            throw new ArgumentNullException("allColumns", "allColumns cannot be null or empty!");
        }
        if (idColumns == null || idColumns.Length == 0)
        {
            throw new ArgumentNullException("idColumns", "idColumns cannot be null or empty!");
        }

        string strCommand = null;

        switch (cmdtype)
        {
            case CommandType.Insert:

                // get columns to set values for. Id columns not included because they should
                // be set by the table
                string[] insertCols = allColumns.Except(idColumns).ToArray();

                strCommand =
                    "INSERT INTO [" + database + "].[dbo].[" + table + "]\n" +
                    "([" + string.Join("], [", insertCols) + "])\n" +
                    "VALUES (@" + string.Join(", @", insertCols.Select(s => s.Replace(" ", ""))) + ")";
                break;
            case CommandType.Update:
                // compare each id column to a paremeterized variable of the same name prefixed with "old"
                string[] idCompsOld = idColumns
                    .Select(col => "[" + col + "] = @old" + col.Replace(" ", ""))
                    .ToArray();

                // create a setting statement. Don't set id columns, as they should never be modifiable
                string[] setStatement = allColumns.Except(idColumns)
                    .Select(col => "[" + col + "] = @" + col.Replace(" ", ""))
                    .ToArray();

                strCommand =
                    "UPDATE [" + database + "].[dbo].[" + table + "]\n" +
                    "SET " + string.Join(", ", setStatement) + "\n" +
                    "WHERE " + string.Join(" AND ", idCompsOld);
                break;
            case CommandType.Delete:
                // compare each id column to a paremeterized variable of the same name
                string[] idComps = idColumns
                    .Select(col => "[" + col + "] = @" + col.Replace(" ", ""))
                    .ToArray();
                strCommand =
                    "DELETE FROM [" + database + "].[dbo].[" + table + "]\n" +
                    "WHERE " + string.Join(" AND ", idComps);
                break;
        }

        SqlCommand command = new SqlCommand(strCommand, connection);

        // cycle through all columns
        for( int i = 0; i < allColumns.Length; i++)
        {
            string col = allColumns[i];

            // create a parameter for that column
            SqlParameter para = new SqlParameter()
            {
                ParameterName = "@" + col.Replace(" ", ""),
                SourceColumn = col
            };
            // add the paramter to the command
            command.Parameters.Add(para);

            // in the special case of the update statement, extra parameters are needed for the
            // old values
            if (cmdtype == CommandType.Update)
            {
                // create a parameter for that column
                para = new SqlParameter()
                {
                    ParameterName = "@old" + col.Replace(" ", ""),
                    SourceColumn = col,
                    SourceVersion = DataRowVersion.Original
                };
                // add the paramter to the command
                command.Parameters.Add(para);
            }
        }

        return command;
    }
}

使用以下代码打印命令：

// Display the Update, Insert, and Delete commands that were automatically generated
// by the SQLCommandBuilder.
Console.WriteLine("Update command : ");
Console.WriteLine(Adapter.UpdateCommand.CommandText);
Console.WriteLine();

Console.WriteLine("Insert command : ");
Console.WriteLine(Adapter.InsertCommand.CommandText);
Console.WriteLine();

Console.WriteLine("Delete command : ");
Console.WriteLine(Adapter.DeleteCommand.CommandText);
Console.WriteLine();

我得到：

Update command : 
UPDATE [dbCache].[dbo].[Component]
SET [ComponentType] = @ComponentType, [Drawings] = @Drawings, [StatusNo] = @StatusNo
WHERE [ComponentlD] = @oldComponentlD 

Insert command :
INSERT INTO [dbCache].[dbo].[Component]
([ComponentType], [Drawings], [StatusNo])
VALUES (@ComponentType, @Drawings, @StatusNo) 

Delete command :
DELETE FROM [dbCache].[dbo].[Component]
WHERE [ComponentlD] = @ComponentlD 

我已经测试了插入，更新和删除语句，他们似乎所有的工作！