SQL查询在SSMS/VS工具正常，C#程序运行无数据问题解决

我编写的SQL查询关联tbl_vehiculos、tbl_facturas、tbl_bitacora三张表，使用DECLARE定义参数，在SSMS和Visual Studio的SQL工具中均可正常运行并返回预期的车辆信息，但将查询集成到C#程序中运行时，DataGridView仅显示列头，无数据返回。尝试转换日期参数类型等操作均未解决问题，最终通过将查询中的NOT IN替换为NOT EXISTS，并将WHERE子句中车辆燃料类型的判断条件从AND改为OR，成功解决问题。

初始SQL查询 #

DECLARE @fecha DATE;
DECLARE @tipocombustible INT;
DECLARE @tipocombustible2 INT;

SET @fecha = '2021-01-26';
SET @tipocombustible = 15101514;
SET @tipocombustible2 = 15101515;

SELECT DISTINCT 
    tbl_vehiculos.idtbl_vehiculos AS ID,
    TRIM (' ' FROM tbl_vehiculos.marca) + ' ' + 
        TRIM(' ' FROM tbl_vehiculos.tipo) + ' ' + 
        TRIM(' ' FROM tbl_vehiculos.modelo) AS 'Vehiculo',
    tbl_vehiculos.placas AS 'Placa',
    tbl_vehiculos.tanque AS 'Capacidad',
    tbl_vehiculos.rendimiento AS 'Kms/Ltr'
FROM
    tbl_vehiculos 
INNER JOIN 
    tbl_facturas ON bl_facturas.idtbl_tipocombust = tbl_vehiculos.idtbl_tipocombust 
LEFT JOIN 
    tbl_bitacora ON tbl_bitacora.fecha_carga = tbl_facturas.fecha 
                 AND tbl_bitacora.idtbl_vehiculos = tbl_vehiculos.idtbl_vehiculos
WHERE 
    tbl_vehiculos.idtbl_tipocombust = @tipocombustible 
    AND tbl_vehiculos.idtbl_tipocombust2 = @tipocombustible2 
    AND tbl_bitacora.fecha_carga NOT IN (SELECT tbl_bitacora.fecha_carga 
                                         FROM tbl_bitacora 
                                         WHERE tbl_bitacora.fecha_carga = @fecha 
                                           AND tbl_bitacora.idtbl_vehiculos = tbl_vehiculos.idtbl_vehiculos);

初始C#代码 #

private void facturasGrid_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
    if (facturasGrid.SelectedRows.Count != 0)
    {
        DataGridViewRow row = this.facturasGrid.SelectedRows[0];

        int combustible1 = Convert.ToInt32(row.Cells["Clave"].Value);
        int combustible2 = Convert.ToInt32(row.Cells["Clave"].Value);
        var fecha = Convert.ToDateTime(row.Cells["Fecha"].Value).ToString("yyyy-MM-dd");
        textBox7.Text = fecha.ToString();
        actualizar_vehiculos(combustible1, combustible2, fecha);
        dateTimePicker1.Value= Convert.ToDateTime(fecha);
    }
}

private void actualizar_vehiculos (int c1,int c2, string fecha)
{
    try
    {
        string conceptos = "SELECT DISTINCT tbl_vehiculos.idtbl_vehiculos AS ID,\r\nTRIM (' ' FROM tbl_vehiculos.marca)+' '+TRIM(' ' FROM tbl_vehiculos.tipo)+' '+TRIM(' ' FROM tbl_vehiculos.modelo) AS 'Vehiculo', "+
                    "\r\ntbl_vehiculos.placas AS 'Placa', " +
                    "\r\ntbl_vehiculos.tanque AS 'Capacidad'," +
                    "\r\ntbl_vehiculos.rendimiento AS 'Kms/Ltr' " +
                    "\r\nFROM tbl_vehiculos " +
                    "\r\nINNER JOIN tbl_facturas ON tbl_facturas.idtbl_tipocombust=tbl_vehiculos.idtbl_tipocombust " +
                    "\r\nLEFT JOIN tbl_bitacora ON tbl_bitacora.fecha_carga=tbl_facturas.fecha AND tbl_bitacora.idtbl_vehiculos=tbl_vehiculos.idtbl_vehiculos " +
                    "\r\nWHERE tbl_vehiculos.idtbl_tipocombust=@tipocombustible and tbl_vehiculos.idtbl_tipocombust2=@tipocombustible2 AND tbl_bitacora.fecha_carga NOT IN (SELECT tbl_bitacora.fecha_carga FROM tbl_bitacora WHERE tbl_bitacora.fecha_carga=@fecha AND tbl_bitacora.idtbl_vehiculos=tbl_vehiculos.idtbl_vehiculos)";

        using (connection = new SqlConnection(connectionString))
        {
            connection.Open();

            SqlCommand comando = new SqlCommand(conceptos, connection);
            comando.Parameters.AddWithValue("@tipocombustible", c1);
            comando.Parameters.AddWithValue("@tipocombustible2", c2);
            comando.Parameters.AddWithValue("@fecha", Convert.ToDateTime(fecha));

            SqlDataAdapter adapter = new SqlDataAdapter(comando);
            comando.ExecuteScalar();

            DataSet datos = new DataSet();
            adapter.Fill(datos);

            vehiculosGrid.ReadOnly = true;
            vehiculosGrid.DataSource = datos.Tables[0];
            vehiculosGrid.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            vehiculosGrid.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            vehiculosGrid.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            vehiculosGrid.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            vehiculosGrid.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            textBox7.Text = vehiculosGrid.RowCount.ToString();

            // Now that DataGridView has calculated it's Widths; we can now store each column Width values.
            for (int i = 0; i <= vehiculosGrid.Columns.Count - 1; i++)
            {
                // Store Auto Sized Widths:
                int colw = vehiculosGrid.Columns[i].Width;

                // Remove AutoSizing:
                vehiculosGrid.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;

                // Set Width to calculated AutoSize value:
                vehiculosGrid.Columns[i].Width = colw;
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

修改后的C#代码与查询 #

private DataSet actualizar_vehiculos(int c1, int c2, DateTime fecha)
{
    string conceptos = @"
SELECT DISTINCT v.idtbl_vehiculos AS ID,
    TRIM (' ' FROM v.marca)+' '+TRIM(' ' FROM v.tipo)+' '+TRIM(' ' FROM v.modelo) AS 'Vehiculo',
    v.placas AS 'Placa', 
    v.tanque AS 'Capacidad',
    v.rendimiento AS 'Kms/Ltr' 
FROM tbl_vehiculos v
INNER JOIN tbl_facturas f ON f.idtbl_tipocombust=v.idtbl_tipocombust 
LEFT JOIN tbl_bitacora b ON b.fecha_carga=f.fecha 
     AND b.idtbl_vehiculos=v.idtbl_vehiculos 
WHERE NOT EXISTS (
           SELECT fecha_carga 
           FROM tbl_bitacora 
           WHERE fecha_carga=@fecha 
               AND idtbl_vehiculos=v.idtbl_vehiculos
    ) AND v.idtbl_tipocombust=@tipocombustible 
    OR v.idtbl_tipocombust2=@tipocombustible2";

    var datos = new DataSet();
    using (connection = new SqlConnection(connectionString))
    using (var comando = new SqlCommand(conceptos, connection))
    using (var adapter = new SqlDataAdapter(comando))
    {
        comando.Parameters.Add("@tipocombustible", SqlDbType.Int).Value = c1;
        comando.Parameters.Add("@tipocombustible2", SqlDbType.Int).Value = c2;
        comando.Parameters.Add("@fecha", SqlDbType.Date).Value = fecha;

        adapter.Fill(datos);
    }
    return datos;
}

内容的提问来源于stack exchange，提问作者Squalfenix