How to build a dynamic Command and Parameter method to insert a record into SQL

Question

I need to insert as DATA TABLE in to SQL (all new records) my problem is that the Data table could have many different columns from 2 - 15. I have been able to build the commandtext (insert into xxx(col1, col2) values(@col1,@col2)" dynamically no problem. I have also been able to create a string of myparamstring = command.paramerter["@col1"].value = "val1"; command.paramerter["@col2"].value = "val2";

but just dawned on me hou would i actualy execute the command.executeNonQuery())

Have you tried using "command.executeNonQuery()"??? This question is lacking all the important pieces of a good question....primarily the details needed for others to have a chance at helping. — Sean Lange

Mad Myche Mad Myche · Accepted Answer · 2017-05-03T18:48:07

There are a few problems in the your presentation of what you have done. Parameters are not string strings, they would be SqlParamaters and can be defined distinctly SqlParameter p1 = new SqlParameter("@col1", "val1"); or added directly to the command object cmd.Parameters.AddWithValue("@col1", "val1"); The actual execution of the query is actually as you have it. One common thing about the command.executeNonQuery() method is that it returns an integer reflecting the amount of rows affected. For an insert it should be 1, but if you are deleting multiple records it would be higher or limiting (via WHERE) it could be 0. I assign negative numbers within catch statements for easy problem recogition.

Anyways, this is a rough layout of how I would write the statement you want to run on a SQL Sever via ADO:

int RowsAffected;
using (SqlConnection conn = new SqlConnection(strConn)) {

    string cmdText = "insert into xxx(col1, col2) values(@col1,@col2)";

    using (SqlCommand cmd = new SqlCommand(cmdText, conn)) {

        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@col1", "val1");
        cmd.Parameters.AddWithValue("@col2", "val2");

        try {
            conn.Open();
            RowsAffected = cmd.ExecuteNonQuery();
        }
        catch (SqlException sx) {
            RowsAffected = -1;
            Console.Write(sx); // your SQL error handling
        }
        catch (Exception ex) {
            RowsAffected = -2;
            Console.Write(ex); // other exception handling
        }
        finally {
                // your cleanup routines
                conn.Close();
                Console.Write("Rows Added = " + RowsAffected);
            }
        }
    }

How to build a dynamic Command and Parameter method to insert a record into SQL

1 Answers