Pass Null Values In A SQL Command (For Two Columns) - Adding In Foreach Loop

Question

I want to select rows from my SQL Server table (it has 3 columns) and to display them into my Datagridview. My problem about null values. How can I pass null values? (Sometimes the first column and the second column are empty. ).

Below is my code:

    string cm = "SELECT column1, column2, column3 FROM mytablename WHERE column1='" 
   + sIS[mSI] + "' AND column2='" + sR[mSSI] + "' OR column1='" + sR[mSSI] 
   + "' AND column2='" + sIS[mSI] + "'";

Note: I edited my code.

Answer 1

You should be using parameters to pass values into a SQL statement or else you will be vulnerable to SQL injection attacks. You can create a SqlCommand object, create SqlParameter objects, then set values on them. If any of your values are null, then you can pass DBNull.Value as the parameter value.

SqlCommand cmd = con.CreateCommand();
string cm = "SELECT column1, column2, column3 "
cm += "FROM mytablename "
cm += "WHERE column1=@mSI "
cm += "AND column2=@mSSI OR column1=@mYSI AND column2=@mSI";
cmd.CommandText = cm;

for(int mSSI=0; mSSI<sR.Count(); mSSI++)
{
   cmd.Parameters.AddWithValue("@mSI ", sR[mSI]);

   // check for a null value
   if (sr[mSSI] == null)
   {
     cmd.Parameters.AddWithValue("@mSSI", DBNull.Value);
   }
   else
   { 
     cmd.Parameters.AddWithValue("@mSSI", sR[mSSI]);
   }

   cmd.Parameters.AddWithValue("@mYSI", sR[mYSI]);

   SqlDataAdapter sd = new SqlDataAdapter(cmd);
   DataTable dat = new DataTable();
   sd.Fill(dat);
   // clear parameters after each iteration
   cmd.Parameters.Clear()
}

Answer 2

I think the answer to your question is using "DBNull.Value" as mentioned above, something like this;

            string cm = "SELECT column1, column2, column3 FROM mytablename WHERE column1='"
           + DBNull.Value + "' AND column2='" + DBNull.Value + "' OR column1='" + sR[mSSI]
           + "' AND column2='" + DBNull.Value + "'";