Null in SQL query

Question

Null in SQL query

Navigation

#1 by (2 votes)

2

I'm doing an SQL query but I can not generate it correctly, these are the parameters that filter

Area = cbbxArea.Text.Equals("Todas") ? System.Data.SqlTypes.SqlString.Null : cbbxArea.Text;
Linea = cbbxLinea.Text.Equals("Todas") ? System.Data.SqlTypes.SqlString.Null : cbbxLinea.Text;
Estacion = cbbxEstacion.Text.Equals("Todas") ? System.Data.SqlTypes.SqlString.Null : cbbxEstacion.Text;
Maquina = cbbxMaquina.Text.Equals("Todas") ? System.Data.SqlTypes.SqlString.Null : cbbxMaquina.Text;

This is the SQL query

"SELECT PC.Lolo, PC.Lo, PC.OpL,  pc.Oph, pc.Hi, PC.Hihi, R.registro, R.fecha,CASE WHEN R.registro >= PC.opL AND R.registro <= PC.opH THEN 'Verde' WHEN ( R.registro < PC.opL OR R.registro > PC.opH) AND ( R.registro >= PC.Lo AND R.registro <= PC.Hi)  THEN 'Amarillo'  ELSE 'Rojo' END AS Registro FROM PuntosControl AS PC JOIN Registro AS R on R.maquina = PC.idMaquina AND R.revision = PC.idPuntoControl AND R.afectacion = PC.idAfectacion WHERE (Null IS Null OR  r.area = Null) AND ('Null' IS Null OR r.linea = 'Null') AND ('Null' IS Null OR r.estacion = 'Null') AND ('Null' IS Null OR r.maquina = 'Null') AND ('Null' IS Null OR r.afectacion = 'Null') AND ('Null' IS Null OR r.revision= 'Null')"

which works with single quotes more, when adding doubles the null parameters receive them as 'null' and do not take them into account

Object Area, Linea, Estacion, Maquina, Revision, Afectacion;
// ?: conditional operator.  
Area = cbbxArea.Text.Equals("Todas") ? System.Data.SqlTypes.SqlString.Null : cbbxArea.Text;
Linea = cbbxLinea.Text.Equals("Todas") ? System.Data.SqlTypes.SqlString.Null : cbbxLinea.Text;
Estacion = cbbxEstacion.Text.Equals("Todas") ? System.Data.SqlTypes.SqlString.Null : cbbxEstacion.Text;
Maquina = cbbxMaquina.Text.Equals("Todas") ? System.Data.SqlTypes.SqlString.Null : cbbxMaquina.Text;
Revision = cbbxRevision.Text.Equals("Todas") ? System.Data.SqlTypes.SqlString.Null : cbbxRevision.Text;
Afectacion = cbbxAfectacion.Text.Equals("Todas") ? System.Data.SqlTypes.SqlString.Null : cbbxAfectacion.Text;
MessageBox.Show(Area + " " + Linea + " " + Estacion + " " + Maquina + " " + Revision + " " + Afectacion);
int v = 0, a = 0, r = 0;
int dv = 0, da = 0, dr = 0;
con.Open();
DataTable dt = new DataTable();
adapt = new SqlDataAdapter("SELECT PC.Lolo, PC.Lo, PC.OpL,  pc.Oph, pc.Hi, PC.Hihi, R.registro, R.fecha,CASE WHEN R.registro >= PC.opL AND R.registro <= PC.opH THEN 'Verde' WHEN ( R.registro < PC.opL OR R.registro > PC.opH) AND ( R.registro >= PC.Lo AND R.registro <= PC.Hi)  THEN 'Amarillo'  ELSE 'Rojo' END AS Registro FROM PuntosControl AS PC JOIN Registro AS R on R.maquina = PC.idMaquina AND R.revision = PC.idPuntoControl AND R.afectacion = PC.idAfectacion WHERE (" + Area + " IS Null OR  r.area = '" + Area + "') AND ("+Linea+" IS Null OR r.linea = '"+Linea+"') AND ("+Estacion+" IS Null OR r.estacion = '"+Estacion+"') AND ("+Maquina+" IS Null OR r.maquina = '"+Maquina+"') AND ("+Revision+" IS Null OR r.afectacion = '"+Revision+"') AND ("+Afectacion+" IS Null OR r.revision= '"+Afectacion+"')", con);
adapt.Fill(dt);
dataGridView1.DataSource = dt;
con.Close();
dataGridView1.AutoResizeColumns();

This is the execution of the query and the filling of the dataGrid

and finally the SQL statement where it takes the parameters ('"+ Null +"' does not accept it and is displayed as 'Null' or '+ Null +' accepts them but when they are not null marks error)

Thanks for your time

sql c# sql-server

asked by Xavier 04.01.2018 в 01:00

source

1 answer

The property 'json' does not exist in the type 'Object' Create branch from another using commands (git)

score 2 · Accepted Answer

The problem is clearly because you are trying to concatenate the values directly to your SQL string. The most popular reason why you should avoid doing this is to protect yourself from SQL injection. But another compelling reason is to avoid making a mess with the quotes as in your case.

The solution is to parameterize the query correctly. I leave you a simplified model that you can adapt to your query:

using(var adapt = new SqlDataAdapter())
{
    var cmd = new SqlCommand("select * from tabla where (@param1 is null or col1 = @param1) and (@param2 is null or col2 = @param2)", con);

    cmd.Parameters.AddWithValue("@param1", cbbxParam1.Text == "Todas" ? (object)DBNull.Value : cbbxParam1.Text);
    cmd.Parameters.AddWithValue("@param2", cbbxParam2.Text == "Todas" ? (object)DBNull.Value : cbbxParam2.Text);

    adapt.SelectCommand = cmd;
    adapt.Fill(dt);
    // ...
}

Apart from parameterizing the query, which you should always do, personally I would make another improvement. Instead of always making all these conditions in your SQL, always verifying if the value is null or not, I would assemble the query dynamically to minimize the amount of necessary conditions and completely eliminate the conditions that verify the NULL.

It requires a bit more code, but the generated SQL is more compact and efficient.

Example:

var sql = new StringBuilder("select * from tabla where 1=1");
var parametros = new List<SqlParameter>();

if (cbbxParam1.Text != "Todas")
{
    sql.Append(" and col1 = @param1");
    parametros.Add(new SqlParameter("@param1", cbbxParam1.Text));
}

if (cbbxParam2.Text != "Todas")
{
    sql.Append(" and col2 = @param2");
    parametros.Add(new SqlParameter("@param2", cbbxParam2.Text));
}

using(var adapt = new SqlDataAdapter())
{
    var cmd = new SqlCommand(sql.ToString(), con);
    cmd.Parameters.AddRange(parametros.ToArray());
    adapt.SelectCommand = cmd;
    adapt.Fill(dt);
    // ...
}