Inserting a nullable Int64 value from C# into a nullable column in SQL server, when user leaves the TextBox empty

Question

I have a Table as below: (Time-saving tip: Just focus on the "Price" column)

public class Expense
{
    public Expense()
    {
        Price = null; //Setting default value for Price
    }
    public string Type { get; set; }
    public Int64? Price { get; set; }
    public string Date { get; set; }
    public string Comment { get; set; }
    public string UserName { get; set; }
    public Int16 ChangeCount { get; set; }
    public string ErrorMessage { get; set; }
}

I want to INSERT INTO this table as below:

public Boolean Insert(Expense exp)
    {
        String query = "INSERT INTO Expense VALUES(N'{0}', {1}, '{2}', N'{3}', '{4}', {5})";
        query = String.Format(query,
               exp.Type,
               exp.Price == null ? (Int64?)null : exp.Price, //This line works perfectly
               exp.Date,
               exp.Comment,
               exp.UserName,
               exp.ChangeCount);
    }

After Executing, Everything works great, JUST one thing is not right; the resulting INSERT Command is:

INSERT INTO Expense VALUES(N'anyTitle', , '1393/09/07 22:37', N'anthing', 'hb', 0)

I expect to see null instead of blank in the Price column! Error is: Incorrect syntax near ','

Thank you in advance.

Answer 1

I think what you're looking for is this:

public Boolean Insert(Expense exp)
{
    String query = "INSERT INTO Expense VALUES(N'{0}', {1}, '{2}', N'{3}', '{4}', {5})";
    query = String.Format(query,
           exp.Type,
           exp.Price == null ? "NULL" : exp.Price.Value.ToString(),
           exp.Date,
           exp.Comment,
           exp.UserName,
           exp.ChangeCount);
}

However, as marc_s absolutely rightly points out, you should be doing this with parameters - there's a danger of a SQL injection attack otherwise.

Answer 2

The answer from @Nugsson solved my problem.

However I've decided to use parametrized queries to overcome this problem and threats like SQL injection. Thanks to all. @Patrick Hofman @marc_s @Nugsson