C# DBNull.Value Inserting 0

Question

I am trying to insert data into a SQL table. The data types I am having issues with are nullable floats. When the NULL values are inserted they change to 0. How can I keep them NULL.

private void InsertStatisticsData(DataTable dt)
{
    //check isin periodicity and As of Date
    foreach(DataRow row in dt.Rows)
    {
        DataTable queryResultTable = SQL.Query($@"SELECT * FROM Statistics
                                                WHERE [CodeID] = '{row["CodeID"]}' 
                                                AND [Periodicity] = '{row["Periodicity"]}'
                                                AND [As of Date] = '{row["As of Date"]}'");

        if(queryResultTable.Rows.Count == 0)
        {
            //Check for Null Values 
            for(int i = 0; i < row.ItemArray.Count(); i++)
            {
                if (Convert.ToString(row[i]) == "")                        
                    row[i] = (object)DBNull.Value;                        
            }
            //Insert Data Into DataBase
            SQL.NonQuery($@"INSERT INTO Statistics
                        VALUES ('{row["CodeID"]}' ,
                                '{row["Volatility"]}',
                                '{row["Beta"]}',
                                '{row["Info Ratio"]}',
                                '{row["Tracking"]}',
                                '{row["Drawdown"]}',
                                '{row["Periodicity"]}',
                                '{row["As of Date"]}')");
        }
    }
}

Nonquery Function:

public static void NonQuery(string query, string databaseName = "Database", string serverAddress = "server-name", int commandTimeout = 30)
{
    string connString = $"Server = {serverAddress}; Database = {databaseName}; Trusted_Connection = True";
    using (SqlConnection sqlConn = new SqlConnection(connString))
    using (SqlCommand cmd = new SqlCommand(query, sqlConn))
    {
        sqlConn.Open();
        cmd.CommandTimeout = commandTimeout;
        cmd.ExecuteNonQuery();
    }
}

Try using parameters. You are currently constructing the query as a string and probably end up with some bullshit value in the string. — Stilgar, Dec 12 '18 at 16:18
Also, please make sure that affected columns haven't any default value associated. — LittleSweetSeas, Dec 12 '18 at 16:20
If you're like the idea of datatables, you should use strongly typed ones, then you wouldn't have any of this low grade data access code (it's really crappy; the first time someone succeeds in getting an apostrophe into one of your datatable cells/database rows, this code will fall apart) and this trivial problem wouldn't be biting you. start with https://learn.microsoft.com/en-us/visualstudio/data-tools/create-and-configure-datasets-in-visual-studio?view=vs-2017 — Caius Jard, Dec 12 '18 at 16:27
2nd the idea to convert this to parameterized inputs. There's really not that much extra effort and it will save you many headaches (including this one). If you are still having the issue, please update the post with the CREATE scripts for the SQL table. — Jacob H, Dec 12 '18 at 16:33
@Stilgar, duh thank you, sometimes I just need a friendly reminder for these types of things. — Mwspencer, Dec 12 '18 at 16:48
@CaiusJard, thank you this is interesting and I will look into it. I — Mwspencer, Dec 12 '18 at 16:49

score 1 · Answer 1 · answered Dec 12 '18 at 16:21

1

You need to make sure your database column structure contains NULL types where you actually need them. Also make sure you don't have any default constraints set, which automatically values the columns to 0 when null is assigned.

answered Dec 12 '18 at 16:21

garry man

445
4
14

If I insert manually using sql and add null values they insert fine. My data types are (float, null). It's my C# that I am realizing is creating a messy insert statement string. – Mwspencer Dec 12 '18 at 16:38

score 1 · Answer 2 · answered Dec 12 '18 at 16:22

1

if(Convert.ToString(null) == "")

will be evaluated as false.

so below code won't get executed

row[i] = (object)DBNull.Value;

on a side note, you should use SqlParameters instead of appending values in a string.

answered Dec 12 '18 at 16:22

Mihir Dave

3,954
1
12
28

I think parameters are the right choice in the this instance. Thanks. – Mwspencer Dec 12 '18 at 16:37

score 1 · Answer 3 · answered Dec 13 '18 at 01:00

This may seem a little heavy handed and bloaty, but if you use parameters (and you really, truly should), I have an extention method I use in my project to take any command object and loop through the parameters to turn a .NET null into a DbNull:

private static void ProcessNullParameters(this DbCommand command)
{
    foreach (DbParameter param in command.Parameters)
    {
        if (param.Value == null)
            param.Value = DBNull.Value;
    }
}

This way, if your native object returns a null value, you can call the extention method against the command object. I couldn't tell what your SQL object was in your example (a framework of some type?), but presumably, somewhere behind the scenes something like this would be going on:

SqlCommand cmd = new SqlCommand("insert into Statistics values (@Code, @Volatility)", conn);
cmd.Parameters.Add("@Code", SqlDbType.VarChar);
cmd.Parameters.Add("@Volatility", SqlDbType.Decimal);

foreach (DataRow dr in dt.Rows)
{
    cmd.Parameters[0].Value = dr["Code"];
    cmd.Parameters[1].Value = dr["Volatility"];

    // And here you convert your nulls to DbNull
    cmd.ProcessNullParameters();

    cmd.ExecuteNonQuery();
}

The alternative would be to do this on every value declaration that is nullable.

cmd.Parameters[0].Value = dr["Code"] ?? DbNull.Value
cmd.Parameters[1].Value = dr["Volatility"] ?? DbNull.Value;

C# DBNull.Value Inserting 0

3 Answers3