Creating INSERT Query dynamically without empty values

Question

First, I read this: How to dynamically build an insert command from Datatable in c#

But this doesn't helped me because i have multiple DataTypes (for example int) which can't be NULL.

I want that if a value is empty, that this would not be added to the Insert string.

Now: INSERT INTO table (col1, col2, col3, col4) VALUES ('val1', '', '', 'val4');

Goal: INSERT INTO table (col1, col4) VALUES ('val1','val4');

Alternative goal: INSERT INTO (col1, col2, col3, col4) VALUES ('val1', 'NULL', '0', 'val4'); (If integer then replace NULL with 0)

First I tried to edit the parameter value so if Length <= 0 then set the value to NULL. But this won't work for an INTEGER.

cmd.CommandText = sql;

foreach (DataRow row in _dataTable.Rows)
{
    cmd.Parameters.Clear();
    foreach (DataColumn col in _dataTable.Columns)
    {
        var rowitem = row[col].ToString();
        if (rowitem.Length <= 0)
        {
            cmd.Parameters.AddWithValue($"@{col.ColumnName}", "NULL");
        }
        else
        {
            cmd.Parameters.AddWithValue($"@{col.ColumnName}", row[col]);
        }
    }
}
cmd.ExecuteNonQuery();

In my DataBase all tables allows NULL so that shouldn't be the problem. It's just for the Integer.

Then I thought of getting the DataType of row[col] so if the datatype is Int32 then reset "NULL" with "0".

But if I want to check the DataType:

Debug.WriteLine(row[col].GetType());

Im only getting AllowDBNull. So thats not the real DataType.

Oh ignore me, I am dumb. I didnt see the `@`, you are absolutely right. For some reason my morning brain thought the `@` character was part of the string interpolation syntax — maccettura, Jun 26 '18 at 14:31
Do you mean "**all colums** of all tables allow `NULL`"? So why do you want to fill `int` fields with `0` and `string` fields with (the string) `"NULL"` instead of just filling everything with `DBNull.Value` if needed? — Corak, Jun 26 '18 at 14:38
try this `cmd.Parameters.Add($"@{col.ColumnName}", col.DataType).Value = row[col];` — Hardik, Jun 26 '18 at 14:39
@Pielroja - a field in a table filled with the `string` `"NULL"` is something completely different than it not being filled at all (`NULL` or in this case `DBNull.Value`). And the same goes for a field in a table filled with the valid integer value `0` and not being filled at all. -- also, if this is your actual code, you're only ever inserting the last row of the `_dataTable`. — Corak, Jun 26 '18 at 14:48
@Corak Yea i noticed that i only have the same row. Thing is, it worked before until the error comes ("int cant be 'NULL'") now everything is fine, but its the same row every time. — Pielroja, Jun 26 '18 at 15:13

score 2 · Accepted Answer · answered Jun 26 '18 at 14:44

If the value is null (DBNull.Value) then do not add parameter.

cmd.CommandText = sql;

foreach (DataRow row in _dataTable.Rows)
{
    cmd.Parameters.Clear();
    foreach (DataColumn col in _dataTable.Columns)
    {                        
        if(row[col] != DBNull.Value)
        {
            cmd.Parameters.AddWithValue($"@{col.ColumnName}", row[col]);
        }
    }
}
cmd.ExecuteNonQuery();

In case if you require to know the data type of the column use DataColumn.DataType. For more https://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype(v=vs.110).aspx

If using C#6 or above

cmd.CommandText = sql;

foreach (DataRow row in _dataTable.Rows)
{
    cmd.Parameters.Clear();
    foreach (DataColumn col in _dataTable.Columns)
    {                        
        cmd.Parameters.AddWithValue($"@{col.ColumnName}", row[col] ?? DBNull.Value);
    }
}
cmd.ExecuteNonQuery();

Creating INSERT Query dynamically without empty values

1 Answers1