>>>Try reading again.
>>>"To generate INSERT, UPDATE, or DELETE statements, the SqlCommandBuilder uses the SelectCommand property to retrieve a required set of metadata automatically. If you change the SelectCommand after the metadata has been retrieved, such as after the first update, you should call the RefreshSchema method to update the metadata."
>>
>>I did read this line. However, it doesn't explain why will it use all columns to generate WHERE clause. What exactly does required metadata mean? Why it can not see that PK column I retrieve is a PK column?
>
>Look at the ConflictOptions property (http://msdn.microsoft.com/en-us/library/system.data.common.dbcommandbuilder.conflictoption%28v=vs.110%29.aspx) and the options that that enumeration has (http://msdn.microsoft.com/en-us/library/system.data.conflictoption%28v=vs.110%29.aspx). You might also want to look at the SetAllValues property (http://msdn.microsoft.com/en-us/library/system.data.common.dbcommandbuilder.setallvalues%28v=vs.110%29.aspx).

Hi Rob,

This all looks quite helpful.

My question is how can I apply it in the generic case?

Based on what I see it looks like I may want to set the update command right before performing the actual update, so it will get only the changed values.

However, I am not exactly sure what should I do for the PrimaryKey property which has to be set and be array of the columns. How can I instruct this property to be generated automatically?

This is my current class:

 protected Boolean ProcessDataTable(SqlCommand toSqlCommand, Boolean generateCommands = false)
        {
            Boolean results = false;

            try
            {
                this.table = new DataTable();
                this.adapter = new SqlDataAdapter(toSqlCommand);
                this.adapter.SelectCommand = toSqlCommand;
                this.adapter.Fill(this.table);
                if (generateCommands)
                {
                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                    adapter.UpdateCommand = builder.GetUpdateCommand(true);
                }
                results = true;
            }
            catch (Exception ex)
            {
                results = false;
                Logging.LogFormat(2, ex.Message);  // at least log the problem
            }

            return results;
        }

and its update method:

 /// <summary>
        /// Update information
        /// </summary>
        /// <returns></returns>
        public Boolean UpdateRows()
        {
            Boolean results = false;
            try
            {
                adapter.Update(this.table);
                results = true;
            }

            catch (Exception ex)
            {
                results = false;
                Logging.LogFormat(2, ex.Message);  // at least log the problem
            }

            return results;
        }

So, I understand it will be a good idea to move the generate update command into the UpdateRows method instead. How would it figure out the PrimaryKey?

This is how I am calling this class right now:

  String cParameter = ("SWIPE_NO" == this.cWhichKey) ? this.cCardNo : this.nPassNo.ToString();
                String cSql = string.Format("SELECT * FROM dbo.{0} WHERE {1} = @param", this.passTable.ToString().ToLower(), this.cWhichKey.ToLower());
                using (SqlCommand sqlCommand = new SqlCommand(cSql, database.sqlConnection))
                {
                    if (this.bIsCard || 0 != this.nPassNo)
                    {
                        String cMessage = "";
                        if (database.GetColumnType(this.passTable.ToString(), this.cWhichKey, ref cMessage).IsTypeNumericOrBit())
                            sqlCommand.Parameters.Add("@param", SqlDbType.BigInt).Value = this.nPassNo;
                        else
                            sqlCommand.Parameters.Add("@param", SqlDbType.VarChar, 22).Value = cParameter;
                    }
                    this.dthPass.Populate(sqlCommand);

So, the call doesn't really know which table I am going to update.

Thanks again.