Modifying any SQL command string to remove a specific column prior to an insert or update to database

Question

I'm trying to remove columns, which exist in ALL of our tables, before adding or updating a record to the table. This is necessary due to some features of Azure, and synchronizing tables on mobile devices. In the example below I am removing named "Col4".

Below is my best approach, so far. It does a simple string replace after the SQL command is cleaned up with the SqlCommandBuilder (see an example of this cleaned up SQL below the code). After the column is removed with a string replace, then I replace the command text in the SqlCommandBuilder. I was hoping to use the string builder as a way to remove the column, as the format should be consistent, as compared to the commandText parameter, which can vary quite a bit. But I fear that this approach is fraught with issues, such as I would then probably need to also modify the VALUES portion in the updated SQL command as well, but this might be difficult as I'm dealing with different tables with differing numbers of columns. Anyhow, as you can see, I get an error where indicated in the code.

Is there a simpler way to remove columns before the da.InsertCommand = \command without specified values

public bool UpdateTest(DataTable dt, string commandText)
{
    bool success = false;
    SqlDataAdapter da = null;
    SqlCommand command = null;
    SqlCommandBuilder cb = null;

    try
    {
        lock ((_Lock))
        {
            using (SqlConnection connX = new SqlConnection(_ConnectionString))
            {
                connX.Open();

                command = new SqlCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;
                command.Connection = connX;

                da = new SqlDataAdapter();
                da.SelectCommand = command;

                // This section is where I try to remove the column
                SqlCommandBuilder testcb = new SqlCommandBuilder(da);
                string testSQL = testcb.GetInsertCommand.CommandText;
                testSQL = testSQL.Replace(", [Col4]", string.Empty);
                da.SelectCommand.CommandText = testSQL;

                cb = new SqlCommandBuilder(da);

                da.InsertCommand = cb.GetInsertCommand; //Code fails here--not surprising
                da.DeleteCommand = cb.GetDeleteCommand;
                da.UpdateCommand = cb.GetUpdateCommand;

                da.Update(dt.GetChanges);

                success = true;
            }
        }
    }
    catch (Exception ex)
    {
    }
    finally
    {
        dt = null;

        if (!command == null)
        {
            command.Dispose();
            command = null;
        }

        if (!(da == null))
        {
            da.Dispose();
            da = null;
        }
    }

    return success;
}

Original da.SelectCommand.CommandText:

"INSERT INTO [Table] ([Col1], [Col2], [Col3], [Col4]) VALUES (@p1, @p2, @p3, @p4)"

Updated da.SelectCommand.CommandText:

"INSERT INTO [Table] ([Col1], [Col2], [Col3]) VALUES (@p1, @p2, @p3, @p4)"

Probably because your edited column-name list has 3 columns, but your VALUES clause still has 4? — jlew, Oct 24 '18 at 17:53
Create the command with parameters. Do not add the parameter if you don't need it. Then assign the command you created to the `da` commands — CodingYoshi, Oct 24 '18 at 17:55
@CodingYoshi -- I'm not sure I understand. Do you have an example? — E. A. Bagby, Oct 24 '18 at 18:07
Create a `SqlCommand` with parameters as shown [here](https://csharp-station.com/Tutorial/AdoDotNet/Lesson06). Then assign it to your `da`. You need to create one for each of your `da` commands. — CodingYoshi, Oct 24 '18 at 18:12
Unrelated tips: use a `using` block for the SqlCommand and SqlDataAdapter rather than having to explicitly Dispose them. Setting the `dt` parameter to null before returning has no effect. So the try, catch and finally can all be removed. — Richardissimo, Oct 24 '18 at 20:50

Modifying any SQL command string to remove a specific column prior to an insert or update to database

0 Answers0