Inserting NULL to SQL DB from C# DbCommand

c# sql dbcommand
SkonJeet picture SkonJeet · Mar 21, 2012 · Viewed 39.1k times · Source 
        DbParameter param = comm.CreateParameter();
        param = comm.CreateParameter();
        param.ParameterName = "@StaffId";
        if (!string.IsNullOrEmpty(activity.StaffId))
            param.Value = activity.StaffId;
        param.DbType = DbType.String;
        comm.Parameters.Add(param);

The above does not work (obviously), object not instantiated. I am attempting to insert a NULL into the database when StaffId is NOT populated. How can I achieve this?

Answer

Andrey Gurinov picture Andrey Gurinov · Mar 21, 2012

You can use DBNull.Value when you need to pass NULL as a parameter to the stored procedure.

param.Value = DBNull.Value;

Or you can use that instead of your if operator:

param.Value = !string.IsNullOrEmpty(activity.StaffId) ? activity.StaffId : (object)DBNull.Value;

Related questions

How to generate List<String> from SQL query?

If I have a DbCommand defined to execute something like: SELECT Column1 FROM Table1 What is the best way to generate a List<String> of the returned records? No Linq etc. as I am using VS2005.

Read More
What is the syntax for an inner join in LINQ to SQL?

I'm writing a LINQ to SQL statement, and I'm after the standard syntax for a normal inner join with an ON clause in C#. How do you represent the following in LINQ to SQL: select DealerContact.* from Dealer inner join …

Read More
Fastest Way of Inserting in Entity Framework

I'm looking for the fastest way of inserting into Entity Framework. I'm asking this because of the scenario where you have an active TransactionScope and the insertion is huge (4000+). It can potentially last more than 10 minutes (default timeout of transactions), …

Read More