c# - visual - Búsqueda de una columna int sobre la base de un valor de cadena




proyecto asp net con sql server (6)

Tengo una vista View_Booking en el servidor sql 2014:

bkID    bkSlot    bkStatus
----    ------    --------
2       Lunch     1
4       Lunch     1
6       Dinner    0
7       Lunch     1

Mientras que en c # he usado una vista de cuadrícula y bkStatus convertido bkStatus en una cadena como:

<asp:Label ID="lblStatus" Text='<%# (Eval("bkStatus")+"" == "1") ? "Booked" : "Pending" %>'
    ... ></asp:Label>

bkID    bkSlot    bkStatus
----    ------    --------
2       Lunch     Booked
4       Lunch     Booked
6       Dinner    Pending
7       Lunch     Booked

Ahora estoy buscando en Vista usando esta consulta:

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE '%" + keyword + "%' 
OR bkSlot LIKE '%"+keyword+"%' 
OR bkStatus LIKE << ? >>

¿Pero no sabe cómo buscar bkStatus que se pasa como cadena desde c # mientras es un int en sql?


Algunas recomendaciones

La consulta que ha proporcionado debe optimizarse:

  1. Primero, el uso de CAST(bkID AS NVARCHAR(MAX)) afectará el rendimiento de la consulta, ya que no utilizará ningún índice, y la conversión a NVARCHAR(MAX) disminuirá el rendimiento.

  2. bkStatus es una columna numérica, por lo que debe usar el operador = y comparar con los valores numéricos (0 or 1 or ...) , también los valores de texto proporcionados se definen en la etiqueta asp no se encuentra en la base de datos, por lo que se utilizan en la aplicación nivel no el nivel de datos.

  3. Si está utilizando CAST(bkID AS NVARCHAR(MAX)) para buscar la columna bkid que contiene un dígito específico (por ejemplo: buscar 1 -> resultado 1 , 10 , 11 , ...) , intente realizar la conversión a un determinado tamaño (por ejemplo, CAST(bkID as NVARCHAR(10) )

  4. Se recomienda utilizar consultas parametrizadas para un mejor rendimiento y para evitar ataques de inyección de SQL . mira @ respuesta desafortunada

  5. Puede usar un objeto de diccionario para almacenar los valores de ID relacionados con las palabras clave

Ejemplo

Nota: el uso de CAST y Like no utilizará ningún índice, este ejemplo se basa en sus requisitos (traté de combinar las recomendaciones que proporcioné con otras recomendaciones)

var dicStatus = new Dictionary<int, string> { 
    { 0, "Pending" }, 
    { 1, "Booked"  },
    { 2, "Cancelled" }
    // ...
};

string querySql = " SELECT * FROM View_Booking" +
                  " WHERE CAST(bkID AS NVARCHAR(10)) LIKE @bkID" + 
                  " OR bkSlot LIKE @bkSlot" +
                  " OR bkStatus = @status";
using (SqlConnection dbConn = new SqlConnection(connectionString))
{
    dbConn.Open();
    using (SqlCommand sqlCommand = new SqlCommand(querySql, dbConn))
    {
        sqlCommand.Parameters.Add("@bkID", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@bkSlot", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@status", SqlDbType.Int).value = dicStatus.FirstOrDefault(x => x.Value == keyword).Key;
        sqlCommand.ExecuteNonQuery();
     }
}

Además, si BkID es una columna entera, es mejor usar

sqlCommand.Parameters.Add("@bkID", SqlDbType.Int).value = (Int)keyword ;

Referencias y enlaces útiles


Cree una enum para BookingStatus y haga una función que acepte la cadena y devuelva el valor enum. Vea el siguiente código.

public enum BookingStatus {
    [Description("Pending")]
    Pending = 0,
    [Description("Booked")]
    Booked = 1
}

Ahora la función es la siguiente,

    public static T GetValueFromDescription<T>(string p_description)
    {
        var type = typeof(T);
        if (!type.IsEnum) throw new InvalidOperationException();
        foreach (var field in type.GetFields())
        {
            var attribute = Attribute.GetCustomAttribute(field,
                typeof(DescriptionAttribute)) as DescriptionAttribute;
            if (attribute != null)
            {
                if (attribute.Description == p_description)
                    return (T)field.GetValue(null);
            }
            else
            {
                if (field.Name == p_description)
                    return (T)field.GetValue(null);
            }
        }
        throw new ArgumentException("Not found.", "description");
        // or return default(T);
    }

Ahora en el parámetro en la consulta de SQL, llame a esta función con el parámetro como "Booked" o "Pending" y devolverá enumeración BookingStatus.Booked . Puedes extraer fácilmente el valor int de eso.

(int)BookingStatus.Booked // will give 1

Parece como si estuvieras intentando buscar libremente entre varias columnas. Este es un problema bastante común, y la solución real se puede encontrar en www.Sommarskog.se en condiciones de búsqueda dinámica.

Su solución parece como si fuera vulnerable a la inyección de SQL. ¿Puedo sugerirle que implemente algo similar al procedimiento almacenado search_orders_3?


Por lo tanto, necesita un cuadro de búsqueda en el que el usuario pueda buscar utilizando bkID , bkSlot o bkStatus . Si el texto de búsqueda está Booked o Pending debemos agregar el filtro para bkStatus que será un campo entero en la base de datos. ¿Correcto? Un poco más de lo que debo mencionar aquí es el uso del using , así como la parametrización de las consultas para una forma de ejecución más inteligente y segura . Así que me gustaría sugerir construir y ejecutar la consulta como la siguiente:

int statusCode = -1;
if(keyword.ToLower() == "booked")
   statusCode = 1;
else if(keyword.ToLower() == "pending")
   statusCode = 0;
string querySql = " SELECT * FROM View_Booking" +
                  " WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE @bkID" + 
                  " OR bkSlot LIKE @bkSlot" +
                  " OR bkStatus = @status";
using (SqlConnection dbConn = new SqlConnection("connectionString here"))
{
    dbConn.Open();
    using (SqlCommand sqlCommand = new SqlCommand(querySql, dbConn))
    {
        sqlCommand.Parameters.Add("@bkID", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@bkSlot", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@status", SqlDbType.int).value = statusCode;
        sqlCommand.ExecuteNonQuery();
     }
}

Tenga en cuenta lo siguiente:

  • Si desea incluir el filtro bkStatus para book , Pend , etc., entonces tiene que cambiar la condición en consecuencia usando .Contains() o .StartsWith() lugar de .ToLower()
  • statusCode se inicializa con -1 para evitar el filtro basado en bkStatus para todos los demás valores

Si la keyword es el nombre del estado y no la identificación del estado, crearía la tabla BookingStatus , tendría las columnas bkStatus y bkStatusTitle allí y la uniría a View_Booking . Usted podría fácilmente hacer LIKE en bkStatusTitle entonces.

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(16)) LIKE '%' + @keyword + '%' 
OR bkSlot LIKE '%' + @keyword + '%' 
OR bkStatusTitle LIKE '%' + @keyword + '%'

Si la keyword es una representación de cadena de bkStatus , solo vería si los valores son iguales.

Como nota al margen, es una mala idea construir sus consultas SQL concatenando las aportaciones del usuario como '%' + keyword + '%' . Esto está abierto a ataques de inyección SQL. Es mejor usar los parámetros de SQL para pasar la entrada del usuario a las consultas de SQL. El uso de '%' + @keyword + '%' en el bit SQL y en C # algo como el ejemplo a continuación sería mucho más seguro.

sqlCommand.Parameters.Add("@keyword", SqlDbType.VarChar, 1000);
sqlCommand.Parameters["@keyword"].Value = searchText;

Las consultas parametrizadas también le ofrecen un beneficio del mismo texto de consulta para múltiples solicitudes, lo que a su vez permite que SQL Server almacene en caché los planes de ejecución de SQL y los reutilice, lo que brinda un rendimiento ligeramente mejor.


Solo otra opción usando CHOOSE () para decodificar bkStatus y TRY_CONVERT () para probar bkID.

Ejemplo

Declare @KeyWord varchar(50) = 'Pending';

Select * 
 From  View_Booking 
 Where bkID = try_convert(int,@KeyWord)
    or bkSlot like '%'+@KeyWord+'%'
    or choose(bkStatus+1,'Pending','Booked')=@KeyWord

Devoluciones

bkID    bkSlot  bkStatus
6       Dinner  0




sql-server-2014