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?
Advertisement
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!