How to dynamically build an insert command from Datatable in c#

Question

I am facing some problem with making a SQL insert statement dynamically from a dataTable object in c#. I want to know the best practices to make it.Here is my code snippet , I have tried so far.

 String sqlCommandInsert = "INSERT INTO dbo.RAW_DATA(";
 String sqlCommandValue = "";
 foreach (DataColumn dataColumn in dataTable.Columns)
 {
     sqlCommandInsert += dataColumn + ",";
 }
 sqlCommandInsert += sqlCommandInsert.TrimEnd(',');

 sqlCommandInsert += ") VALUE(";

 for (int i = 0; i < dataTable.Rows.Count; i++)
 {
     sqlCommandValue += "'" + dataTable.Rows[i].ItemArray[i] + "',";
 }

 var insertCommand = sqlCommandInsert;
 sqlCommandValue = sqlCommandValue.TrimEnd(',');

 var command = insertCommand + sqlCommandValue + ")";
 dataContext.Database.ExecuteSqlCommand(command);

Any suggestion would be appreciated :) Regards.

The best practice is to not do that at all, but build a correct precise command with the correct set of parameters. (By the way, it is VALUES ) — Steve, Feb 18 '14 at 14:57

score 24 · Accepted Answer · answered Feb 18 '14 at 15:23

24

Use VALUES instead of VALUE. Apart from that you should always use sql-parameters:

string columns = string.Join("," 
    , dataTable.Columns.Cast<DataColumn>().Select(c => c.ColumnName));
string values = string.Join("," 
    , dataTable.Columns.Cast<DataColumn>().Select(c => string.Format("@{0}", c.ColumnName)));
String sqlCommandInsert = string.Format("INSERT INTO dbo.RAW_DATA({0}) VALUES ({1})" , columns, values);

using(var con = new SqlConnection("ConnectionString"))
using (var cmd = new SqlCommand(sqlCommandInsert, con))
{
    con.Open();
    foreach (DataRow row in dataTable.Rows)
    {
        cmd.Parameters.Clear();
        foreach (DataColumn col in dataTable.Columns)
            cmd.Parameters.AddWithValue("@" + col.ColumnName, row[col]);
        int inserted = cmd.ExecuteNonQuery();
    }
}

answered Feb 18 '14 at 15:23

Tim Schmelter

450,073
74
686
939

Cast doesn't exist. Is the type DataTable, and what .NET libary are you using? (found answer myself: "using System.Linq;" – Alan Baljeu Nov 21 '18 at 21:33
Careful for people copying this code to automatically generate an insert statement based on a DataTable: "RAW_DATA" seems to be the table name from OP. So you can't just copy and paste this code. So should be replaced with `dataTable.TableName`. – Wolfsblvt Jan 22 '19 at 08:15
Will the parameters `{0} and {1}` in the `string.Format` of the variable `sqlCommandInsert` be vulnerable to SQL injection ? – Kunal Mukherjee May 11 '21 at 14:52
1

@KunalMukherjee: No, the value is not used directly in the SQL, it will be passed to the database as sql-parameter. The sql is just `INSERT INTO Table(col1,col2) VALUES(@col1,@col2)`. The column-names `col1`, `col2` come from the `DataTable`-Columns. So you should be on the safe side if it's created by your code. – Tim Schmelter May 11 '21 at 15:00
So is this a safe way to build a dynamic set of columns for `update / insert` queries ? – Kunal Mukherjee May 11 '21 at 15:08
1

@KunalMukherjee: depends on your definition of _dynamic_. If the user can provide the column names and you don't validate them against existing columns, it's not safe. If you build the `DataTable` yourself it's safe. – Tim Schmelter May 11 '21 at 15:11
This is regarding the [`HTTP PATCH`](https://learn.microsoft.com/en-us/aspnet/core/web-api/jsonpatch?view=aspnetcore-5.0#the-replace-operation) replace operation, so the column names comes from the `path` key, so the client can pass anything over the network, so my question is how to build dynamic set clause for the update statement in that case without being vulnerable to SQL injection – Kunal Mukherjee May 11 '21 at 15:15
1

@KunalMukherjee: the client passes the table, the columns and the values? Then there is only one way to be safe, validate every table and column against the database before you execute the inserts/updates. You can create a question on Stackoverflow – Tim Schmelter May 11 '21 at 15:19
Spent hours to insert C# entity data in MySQL datatable where there is no primary key or identity column because all dapper extensions also rejecting to insert in such case and as there are so many columns with space as well (with Column Attribute) I couldn't think of a better way to insert data in a table having no primary key and all columns are string! Created my own code using your answer of preparing insert statement and added reflection in my case as it was MySQL columns. Thanks for a better workaround to generate own insert query. :) – sanpat Feb 01 '22 at 22:45

score 4 · Answer 2 · answered Sep 08 '15 at 06:50

   ## Dynamic Update Query from Datatable with Npgsql##
  public string UpdateExecute(DataTable dataTable, string TableName)
{

    NpgsqlCommand cmd = null;
    string Result = String.Empty;

    try
    {            

        if (dataTable.Columns.Contains("skinData")) dataTable.Columns.Remove("skinData");
        string columns = string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(c => c.ColumnName));

        string values = string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(c => string.Format("@{0}", c.ColumnName)));

        StringBuilder sqlCommandInsert = new StringBuilder();
        sqlCommandInsert.Append("Update " + TableName + " Set ");

        string[] TabCol = columns.Split(',');
        string[] TabVal = values.Split(',');

        for (int i = 0; i < TabCol.Length; i++)
        {
            for (int j = 0; j < TabVal.Length; j++)
            {
                sqlCommandInsert.Append(TabCol[i] +" = "+ TabVal[i] + ",");
                break;
            }
        }
      string  NpgsqlCommandUpdate= sqlCommandInsert.ToString().TrimEnd(',');
      NpgsqlCommandUpdate += (" where " + TabCol[0] + "=" + TabVal[0]);


        using (var con = new NpgsqlConnection("Server=localhost;Port=5432;uid=uapp;pwd=Password;database=Test;"))
        {
            con.Open();
            foreach (DataRow row in dataTable.Rows)
            {
                cmd = new NpgsqlCommand(NpgsqlCommandUpdate.ToString(), con);
                cmd.Parameters.Clear();
                foreach (DataColumn col in dataTable.Columns)
                    cmd.Parameters.AddWithValue("@" + col.ColumnName, row[col]);

                Result = cmd.ExecuteNonQuery().ToString();
            }
        }
    }
    catch (Exception)
    {
        Result = "-1";
    }
    return Result;
}

Hello and welcome to Stack Overflow. Please explain how your answer improves on the other one given as it's not immediately obvious. — bernie, Sep 19 '15 at 03:14
My answer is not immediate because its a Update Statement Dynamically, The answer previous are Insert Statement, so i thought if any one came here searching Insert statement dynamically then he/she might need update statement dynamically also. — vsharma10286, Sep 23 '15 at 13:02

How to dynamically build an insert command from Datatable in c#

2 Answers2

Linked