studio - login con base de datos asp net c#

Búsqueda de una columna int sobre la base de un valor de cadena

Question

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?

Answer 1

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

• Como operador para entero
• Rendimiento exitoso usando CAST en T-SQL
• ¿Cuánto afectan las declaraciones CAST al rendimiento?
• SQL Server: columnas de índice utilizadas en like?
• Diccionario C # obtener artículo por índice
• Obteniendo consulta para trabajar con parámetro y "me gusta"

Answer 2

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

Answer 3

Me centraré en esta parte de su pregunta (¿es la pregunta en sí?):

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

Una forma de lidiar con eso en SQL es con la ayuda de la cláusula CASE . En su caso específico podría (no significa que debería) hacer algo como:

SELECT * FROM View_Booking
WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE ''%" + keyword + "%''
OR bkSlot LIKE ''%"+keyword+"%''
OR bkStatus = CASE ''%"+keyword+"%'' WHEN ''Booked'' THEN CAST(1 AS INT) WHEN ''Pending'' THEN CAST(0 AS INT) WHEN ... THEN ... ELSE ... END'' 

Pero sugiero el uso de parámetros como se indica en la respuesta de @ un-lucky . Hay mucho más que podríamos discutir en términos de mejores prácticas aquí, por lo que le sugiero que eche un vistazo a los siguientes artículos:

1. Tablas de búsqueda: Usted indicó que bkStatus es de tipo INT soy Supongo que podría tener más opciones que Reservado o Pendiente , por ejemplo: Reservado o Cancelado . En ese caso, su código real puede volverse cada vez más desordenado con cada opción que agregue.
2. Prácticas recomendadas para el uso de ADO.NET: No especificó cómo acceder a la base de datos desde su interfaz. A pesar de que este artículo ha existido durante años, la mayoría de su contenido es actual. Supongo que esto puede ser útil.
3. Creación de un Entity Framework mejor: en caso de que esté utilizando Entity Framework para acceder a su base de datos.

Espero eso ayude.

Answer 4

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?

Answer 5

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

Answer 6

Puede usar la función declare para crear una tabla temporal que tenga una lista de bkStatus .

Será más fácil para usted crear una consulta utilizando bkstatus como clave externa. Después de eso, ya no tienes que usar la función de conversión o like . Será un poco ineficiente.

Puedes probar este código a continuación:

declare @bkstatus table (number int primary key , bkstatus varchar(10) )
insert into @bkstatus (number , bkstatus)
values ( 0 , ''Pending''), (1 , ''Booked'')

y luego usando esta consulta:

SELECT * FROM View_Booking v
INNER JOIN @bkstatus b on v.bkstatus = b.number
WHERE b.bkstatus = @keyword 

Answer 7

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.

Answer 8

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

Answer 9

Su bkStatus es entero. En la vista, usted traduce el valor entero en una cadena significativa para el usuario. Hasta ese punto, todo está bien. Ahora, para que el usuario busque todo lo que necesita hacer es revertir sus traducciones de cadenas a números enteros y buscar números enteros.

Mantener las cosas simples

searchStatusKey = yourVariableHodingTheString == "Booked" ? 1 : 0;

Sin embargo, para evitar errores tediosos y actualizaciones indoloras de código en varios lugares (por ejemplo, porque decidieron agregar otro estado allí), recomendaría una tabla de traducción aquí. Algo así como HashMap (matriz asociativa).

var statusMap = new Dictionary<int, string> {
{ 0, "Pending" },
{ 1, "Booked" },
/* can always add more pairs in the future as needed */
};

Ahora, asumiendo que su parámetro está en una variable llamada searchStatus

searchStatusKey = statusMap.FirstOrDefault(x => x.Value == searchStatus).Key;

Ahora proporcione como parámetro bkStatus en la parte donde está el valor searchStatusKey y listo.

select * from View_Booking where bkStatus = << ? >>