Passing NULL values in a query string

Question

I have a function called "InsertEmpolyee" that receives parameters to fill a query string. The thing is, I want to make some of these variables optional, in other words, I would like to be able to call the function without passing values to those parameters and still make the correct query string by inserting NULL into the database.

This is the function InsertEmployee

public int InsertEmployee(string FirstName, char Minit, string LastName, int SSN
 , int? Salary)
{
    string query = "INSERT INTO Employee (Fname, Minit, Lname, SSN, Salary) " +    "Values ('" + FirstName + "','" + Minit + "','" + LastName + "'," + Salary + ");";
    return model.ExecuteNonQuery(query);
}

And here is how I call it.

int res = Controlobj.InsertEmployee(txtbox_FirstName.Text, txtbox_Minit.Text[0],
                   txtbox_LastName.Text, Int32.Parse(txtbox_SSN.Text), null);

I have tried to do the following

if (!Salary.HasValue)
            Salary = DBNull.Value;

But it gives me the following error "Can't implicitly convert system.DBNull to int?"

How can I fix this? And is there is a better way to do this?

Answer 1

Your code doesn't only fail on null, but on strings containing apostrophes. There could be other pitfalls as well. That's why we use parameters.

public int InsertEmployee(string Fname, char Minit, string Lname, int SSN, int? Salary)
{
    return model.ExecuteNonQuery(
        @"
            INSERT INTO Employee (
                       Fname, Minit, Lname, SSN, Salary
                   ) VALUES (
                       @Fname, @Minit, @Lname, @SSN, @Salary
                   )
        ",
        new SqlParameter("@Fname",  SqlDbType.VarChar) { Value = (object)Fname  ?? System.DBNull.Value },
        new SqlParameter("@Minit",  SqlDbType.VarChar) { Value =         Minit                         },
        new SqlParameter("@Lname",  SqlDbType.VarChar) { Value = (object)Lname  ?? System.DBNull.Value },
        new SqlParameter("@SSN",    SqlDbType.Int    ) { Value =         SSN                           },
        new SqlParameter("@Salary", SqlDbType.Int    ) { Value = (object)Salary ?? System.DBNull.Value });
}