Using SQLAdapter and SQLCommandBuilder without a Primary Key

I’m exploring the use of SqlCommandBuilder alongside Adapter.Update() to synchronize a DataGridView with an SQL Database table.

I want to auto-generate SQL Update statements using SqlCommandBuilder.GetUpdateCommand(), however, it fails with “Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information“. This makes sense, because my table doesn’t have a primary key.

I cannot set the primary key on the source table, but I do have an identity column. I’d like to specify to the command builder which column to use as the primary key. There is such a feature on the DataTable class, but it seems to have no effect on the SqlCommandBuilder.

I tried the following:

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

But it seems that this information does not propagate to the SqlDataAdapter and SqlCommandBuilder because I still get the error.

Here’s the order I’ve tried:

// get data
Adapter.Fill(Table);

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

cmdBuilder = new SqlCommandBuilder(Adapter);

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

Are there any solutions here at all, or do I have to specify the update and insert statements?

Answer

So, as pointed out by @PanagiotisKanavos, the SqlCommandBuilder does not support tables without primary keys, even if you set it in the DataTable object.

Therefore, I had no choice but write my own Command Builder.

To use it, you need to provide:

• The SqlConnection to use
• The Database (if not provided in the connection)
• The SQLAdapter, with the Select Command already set (there’s a constructor for that)

How you use it:

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

Next, the full class code:

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

Using the following code to print the commands:

// 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();

I get:

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 

I have tested the insert, update and delete statements and they seem to all work!