How to easily null certains parts of a SqlCommand?

Question

I am writing a kind of democrazy Programm for my collegues with a SQL Server (Express).

Thusfar i got along with it pretty well, but I fail for about an hour not, to create a simple way of using a SqlCommand with parameters, that can be null, but don't always are.

My current "solution" looks like this:

public bool CreateSubTopic(string subtopic, DateTime enddate, int maxParts, int BitKeepEntries)
{
    Connected = true;
    var myDict = GetSubtopics(HelpClass.TopicId);
    if (myDict.ContainsKey(subtopic))
    {
        return false;
    }

    myCommand = null;
    myCommand = new SqlCommand("insert into SubtopicsParameters(FK_Topic, Subtopic, StartDate, Enddate, MaxParticipants, KeepsEntries) values(" +
                                HelpClass.TopicId + "," +
                                subtopic + "," +
                                DateTime.Today + "," +
                                enddate + "," +
                                maxParts + "," +
                                BitKeepEntries + ")");
    myCommand.ExecuteNonQuery();
    Connected = false;
    return true;
}

The Table i write in has the additional primary Key PK_ID. The columns FK_Topic, Enddate and MaxParticipants can be null, based on checkboxes on a form.

The Problem is, I could just write 7 different methodes, but it would be dirty.

Do you have a solution ?

€: The Solution I use now looks like this:

public bool CreateSubTopic(string subtopic, DateTime enddate, int maxParts, bool KeepEntries)
{
    var myDict = GetSubtopics(HelpClass.TopicId);
    if (myDict.ContainsKey(subtopic))
    {
        return false;
    }
    Connected = true;
    myCommand = null;
    myCommand = new SqlCommand("insert into SubtopicsParameters(FK_Topic, Subtopic, StartDate, Enddate, MaxParticipants, KeepsEntries) values(" +
        "@FKParam," + " @SubtopicParam," + "@Startdateparam," + "@Enddateparam," + "@MaxPartsParam," + "@KeepEntriesParam)", myConnection);


    myCommand.Parameters.AddWithValue("FkParam", HelpClass.TopicId);
    myCommand.Parameters.AddWithValue("SubtopicParam", subtopic);
    myCommand.Parameters.AddWithValue("@Startdateparam", DateTime.Today);
    if (enddate != DateTime.Today)
    {
        myCommand.Parameters.AddWithValue("@Enddateparam", enddate);
    }
    else
    {
        myCommand.Parameters.AddWithValue("@Enddateparam", DBNull.Value);
    }
    if (maxParts == 0)
    {
        myCommand.Parameters.AddWithValue("@MaxPartsParam", DBNull.Value);
    }
    else
    {
        myCommand.Parameters.AddWithValue("@MaxPartsParam", maxParts);
    }
    if(KeepEntries == true)
    {  
        myCommand.Parameters.AddWithValue("@KeepEntriesParam",  1);
    }
    else
    {
        myCommand.Parameters.AddWithValue("@KeepEntriesParam", 0);
    }
    myCommand.ExecuteNonQuery();
    Connected = false;
    return true;
}

This works, is safe and based on your comments and answers :) Thank you.

Never create queries using string concatenation. Use named parameters (See "SQL injection") — Fedor Hajdu, May 22 '14 at 12:20
Actually using [Parameters](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx) would be a good start. — Damien_The_Unbeliever, May 22 '14 at 12:21
Hi, try to rewrite this with the SqlParameter. see http://stackoverflow.com/questions/8148815/c-sharp-sqlparameters-short-hand for an example — Stefan, May 22 '14 at 12:21

score 1 · Answer 1 · edited May 22 '14 at 13:06

1

Dynamically add parameters to SQLCommand.Parameters collection.

Here is an example of SqlCommand.Parameters

edited May 22 '14 at 13:06

huMpty duMpty

14,346
14
60
99

answered May 22 '14 at 12:24

st4hoo

2,196
17
25

score 0 · Accepted Answer · answered May 22 '14 at 13:02

Try this

public bool CreateSubTopic(string subtopic, DateTime enddate, int maxParts, int BitKeepEntries)
{
   Connected = true;
   var myDict = GetSubtopics(HelpClass.TopicId);
   if (myDict.ContainsKey(subtopic))
   {
    return false;
   }

    myCommand = null;
    myCommand = new SqlCommand("insert into SubtopicsParameters(FK_Topic, Subtopic, StartDate, Enddate, MaxParticipants, KeepsEntries) values(@topic,@stopic,@sd,@ed,@partici,@KEntries);
    if(HelpClass.TopicId=="")
        myCommand.Parameters.AddWithValue("@topic",System.DBNull.Value);
    else 
        myCommand.Parameters.AddWithValue("@topic",HelpClass.TopicId);

    if(subtopic=="")
        myCommand.Parameters.AddWithValue("@stopic",System.DBNull.Value);
    else 
        myCommand.Parameters.AddWithValue("@stopic",subtopic);

    myCommand.Parameters.AddWithValue("@sd",DateTime.Today);

    if(enddate=="")
        myCommand.Parameters.AddWithValue("@ed",System.DBNull.Value);
    else 
        myCommand.Parameters.AddWithValue("@ed",enddate);

    if(maxParts=="")
        myCommand.Parameters.AddWithValue("@partici",System.DBNull.Value);
    else 
        myCommand.Parameters.AddWithValue("@partici",maxParts); 

    if(BitKeepEntries=="")
        myCommand.Parameters.AddWithValue("@KEntries",System.DBNull.Value);
    else 
        myCommand.Parameters.AddWithValue("@KEntries",BitKeepEntries);      

    myCommand.ExecuteNonQuery();
    Connected = false;
    return true;
}

Thank you , the code looks good, i was just reading into parameters :) — ExNought, May 22 '14 at 13:20

How to easily null certains parts of a SqlCommand?

2 Answers2