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.

Grate

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 ) – Dafodil

Answer 1

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();
    }
}

Levon

Cast<DataColumn> doesn't exist. Is the type DataTable, and what .NET libary are you using? (found answer myself: "using System.Linq;" – Awestricken

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. – Moriarty

Will the parameters {0} and {1} in the string.Format of the variable sqlCommandInsert be vulnerable to SQL injection ? – Melisamelisande

@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. – Levon

So is this a safe way to build a dynamic set of columns for update / insert queries ? – Melisamelisande

@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. – Levon

This is regarding the HTTP PATCH 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 – Melisamelisande

@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 – Levon

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. :) – Trouble

Answer 2

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;
}   

Strongwilled

Hello and welcome to Stack Overflow. Please explain how your answer improves on the other one given as it's not immediately obvious. – Ns

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. – Strongwilled