/// <summary>
/// Este método realiza una consulta en la B.D.
/// </summary>
/// <param name="tabla">La tabla en la que se realiza la consulta.</param>
/// <param name="campos">Nombre de los campos que se quieren consultar.</param>
/// <param name="condicion">Indica que registros de la tabla se quieren consultar.</param>
/// <param name="orderCampo">Indica el campo por el que se ordena la consulta.</param>
/// <returns>Devuelve una Lista de Array de Objects. En cada fila de la lista, hay un array
/// de Objects, cada Object es un campo del registro, y cada fila de la lista es un registro de
/// la tabla.</returns>
public FilasDB Select(string tabla, string[] campos, string orderCampo = "", params CadenaParametro[] condicion)
{
FilasDB filas = new FilasDB();
string cadenaCondicion = string.Empty;
foreach (CadenaParametro stringParametro in condicion)
{//Se crea la condición concatenando todos los objetos 'CadenaParametro' por si la condición incluye una comparación
//con más de 1 campo.
cadenaCondicion += stringParametro.ToString() + " ";
}
string sentencia = "Select " + string.Join(", ", campos);
sentencia += " from " + tabla;
sentencia += cadenaCondicion != string.Empty ? " where " + cadenaCondicion : cadenaCondicion;
sentencia += orderCampo != string.Empty ? " order by " + orderCampo : orderCampo;
using (SqlCommand cmd = new SqlCommand(sentencia))
{
//Se añaden los Parámetros SQL de la condicion a la consulta:
foreach (CadenaParametro parametro in condicion)
{
SqlParameter paramSql = new SqlParameter();
paramSql.ParameterName = parametro.NombreParametro;
paramSql.SqlDbType = TipoNetToSql(parametro.ValorParametro);
paramSql.Value = parametro.ValorParametro;
cmd.Parameters.Add(paramSql);
}
//Al hacer la consulta Ejecuta() devuelve una lista con todas las filas seleccionadas.
//Cada fila es una Array de Objects donde cada Object es un campo del registro/fila.
filas = Ejecuta(cmd);
//Para los campos que son Nullables en la B.D. le asigno el valor null en su respectiva referencia a objeto.
foreach (Object[] registro in filas)
{
for (int ind = 0; ind < registro.Length; ind++)
{
if (registro[ind] is DBNull)
{
registro[ind] = null;
}
}
}
}
return(filas);
}
/// <summary>
/// Este método ejecuta una sentencia sql en la B.D.
/// </summary>
/// <param name="sentencia">Cadena de caracteres con la sentencia sql (insertar, consultar, borrar...)</param>
/// <returns>Devuelve el resultado de cada sentencia en una Lista de Array de Objects.</returns>
public FilasDB Ejecuta(string sentencia)
{
FilasDB filas = new FilasDB();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conex;
cmd.CommandText = sentencia;
if (transaction != null)
{//Si se creó una transacción para la conexión.
cmd.Transaction = transaction;
}
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Object[] campos = new object[reader.FieldCount];
reader.GetValues(campos);
filas.Add(campos);
}
//reader.Close(); No se pone porque al acabar el using, se llama a reader.Dispose()
//que a su vez llama a reader.Close();
}
}
catch (Exception ex)
{
//Título en el archivo de Logs para separar por párrafos todos los mensajes
//que pertenecen a la misma operación.
GestionExcepcionWeb.WriteLog("ERROR SQL SERVER:");
GestionExcepcionWeb ge = new GestionExcepcionWeb(ex);
ge.Log();
if (transaction != null)
{//Si se están ejecutando los comandos SQL dentro de una transacción, si se produce alguna Excepción en algún
//comando, hay que lanzar una excepción para recogerla desde el código que utiliza el objeto 'AccesoSql' y capturarla
//para ejecutar el método 'RollBack()' de este objeto.
GestionExcepcionWeb.WriteLog("FALLO EN TRANSACCIÓN SQL: La siguiente operación no se pudo completar con éxito: \n" + cmd.CommandText);
throw new TransactionSqlException("No se pudo completar la Transacción Sql con éxito.");
}
}
return(filas);
}
}
/// <summary>
/// Este método devuelve el nº total de filas/registros de una tabla en la B.D.
/// </summary>
/// <param name="tabla">El nombre de la tabla en la B.D.</param>
/// <param name="condicion">Condición para determinar que filas se deben contar o no.</param>
/// <returns>Nº de filas o registros de la tabla</returns>
public long TotalFilas(string tabla, string condicion = "")
{
long nfilas = 0;
condicion = condicion != string.Empty?"where " + condicion:string.Empty;
string sentencia = "SELECT COUNT(*) FROM " + tabla + " " + condicion;
FilasDB filas = Ejecuta(sentencia);
if (filas.Count > 0)
{
nfilas = Convert.ToInt64(filas[0][0]);
}
return(nfilas);
}
/// <summary>
/// Este método elimina de la base de datos uno o más registros.
/// </summary>
/// <param name="tabla">La tabla de la que se quieren borrar los registros.</param>
/// <param name="condicion">Indica que registro/s se quieren borrar.</param>
/// <returns>Devuelve el nº de registros eliminados de la tabla en la B.D.</returns>
public int Delete(string tabla, params CadenaParametro[] condicion)
{
string cadenaCondicion = string.Empty;
foreach (CadenaParametro stringParametro in condicion)
{//Se crea la condición concatenando todos los objetos 'CadenaParametro' por si la condición incluye una comparación
//con más de 1 campo.
cadenaCondicion += stringParametro.ToString() + " ";
}
if (cadenaCondicion == "")
{//Si no hay condición, se borran todos los registros/filas de la B.D. y no se puede dejar que eso pase por error.
return(0);
}
string sentencia = "Delete from " + tabla + " ";
sentencia += "where " + cadenaCondicion;
sentencia += "select @@rowcount";
//Al usar el "select @@rowcount", Ejecuta() devuelve el nº de registros afectados.
using (SqlCommand cmd = new SqlCommand(sentencia))
{
//Se añaden los Parámetros SQL de la condicion a la consulta:
foreach (CadenaParametro parametro in condicion)
{
SqlParameter paramSql = new SqlParameter();
paramSql.ParameterName = parametro.NombreParametro;
paramSql.SqlDbType = TipoNetToSql(parametro.ValorParametro);
paramSql.Value = parametro.ValorParametro;
cmd.Parameters.Add(paramSql);
}
FilasDB filas = Ejecuta(cmd);
if (filas.Count > 0)
{
return(Convert.ToInt32(filas[0][0]));
}
else
{
return(0);
}
}
}
//Insert con parámetros Sql.
public int Insert(string tabla, string[] campos, Object[] valores)
{//Los parámetros evitan la Inyección Sql porque tratan su contenido como un literal, y las
//instrucciones inyectadas Sql no se ejecutan en el servidor.
string sentencia = "Insert into " + tabla;
sentencia += string.Format(" ({0}) ", string.Join(",", campos));
sentencia += "values (";
for (int ind = 0; ind < campos.Length; ind++)
{//El nombre del parámetro es igual al de su campo correspondiente con la @ al principio.
sentencia += "@" + campos[ind];
sentencia += (ind == campos.Length - 1) ? ") " : ", ";
}
sentencia += "select @@Identity";
using (SqlCommand cmd = new SqlCommand(sentencia))
{
//Parámetros Sql:
for (int ind = 0; ind < valores.Length; ind++)
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + campos[ind];
param.SqlDbType = TipoNetToSql(valores[ind]);
param.Value = valores[ind];
cmd.Parameters.Add(param);
}
//Al usar el "select @@identity", Ejecuta() devuelve el "id" del nuevo registro.
FilasDB filas = Ejecuta(cmd);
if (filas.Count > 0)
{//Si no se pudo insertar el registro, filas no tienen ninguna fila.
return(Convert.ToInt32(filas[0][0]));
}
else
{//Si no se pudo insertar el registro en la tabla, se devuelve id -1.
return(-1);
}
}
}
/// <summary>
/// Este método realiza una consulta en la B.D. en la tabla indicada com parámetro pero selecciona solo
/// aquellos registros indicados por su posición en dicha tabla. Permitiendo así poder seleccionar
/// registros por medio de paginación.
/// </summary>
/// <param name="posIni">Nº de fila o posición del primer registro que encabeza la lista a devolver.</param>
/// <param name="posFin">Nº de fila o posición del último registro de la lista a devolver.</param>
/// <param name="tabla">Nombre de la tabla en la B.D.</param>
/// <param name="condicion">Indica la condición que deben cumplir aquellos campos que se van a enumerar.</param>
/// <param name="campos">Array de strings con los nombres de los campos en la B.D.</param>
/// <param name="orderAsc">Indica como se deben ordenar los campos: true:Menor a Mayor. false:Mayor a Menor.</param>
/// <param name="orderCampo">Indica el campo de la tabla por el que se deben ordenar los registros/filas.</param>
/// <returns>Lista de Array de 'Object' con los valores de los campos de los registros/filas
/// cuya posición en la tabla de la B.D. va desde la fila 'posIni' a 'posFin'.</returns>
public FilasDB SelectPage(string tabla, string[] campos, int posIni, int posFin, string condicion = "", string orderCampo = "", bool orderAsc = true)
{ /*El Row_Number() crea un campo con el nº de fila que ocupa el registro en la consulta
* para poder usar dicha consulta como una subQuery y de ella escoger ciertos registros
* haciendo Paginación.*/
if (orderCampo == "")
{ //No se ordena por ningún campo en particular para calcular el ROW_NUMBER.
orderCampo = "(SELECT 1)";
}
string nombreCampos = string.Join(", ", campos);
string dirOrdenacion = (orderAsc == true)?"asc":"desc";
condicion = (condicion != "") ? "where " + condicion : string.Empty;
string sentencia = "SELECT " + nombreCampos + " from " +
"( SELECT ROW_NUMBER() OVER(order by " + orderCampo + " " + dirOrdenacion + ") AS [Posicion], " + nombreCampos +
" FROM " + tabla + " AS [tabla] " + condicion + " )" +
"AS SubQuery1 " +
"where (Posicion>=" + posIni + " AND Posicion<=" + posFin + ")";
//Al hacer la consulta Ejecuta() devuelve una lista con todas las filas seleccionadas.
//Cada fila es una Array de Objects donde cada Object es un campo del registro/fila.
FilasDB filas = Ejecuta(sentencia);
//Para los campos que son Nullables en la B.D. le asigno el valor null en su respectiva referencia a objeto.
foreach (Object[] registro in filas)
{
for (int ind = 0; ind < registro.Length; ind++)
{
if (registro[ind] is DBNull)
{
registro[ind] = null;
}
}
}
return(filas);
}
//Update con parámetros sql.
public int Update(string tabla, string[] campos, Object[] valores, params CadenaParametro[] condicion)
{//Los parámetros evitan la Inyección Sql porque tratan su contenido como un literal, y las
//instrucciones inyectadas Sql no se ejecutan en el servidor.
string cadenaCondicion = string.Empty;
foreach (CadenaParametro stringParametro in condicion)
{//Se crea la condición concatenando todos los objetos 'CadenaParametro' por si la condición incluye una comparación
//con más de 1 campo.
cadenaCondicion += stringParametro.ToString() + " ";
}
if (cadenaCondicion == string.Empty)
{//Si no hay una condición en la instrucción de modificación, se modifican todos los registros/filas para la tabla,
//y hay que evitar que eso se produzca por error.
return(0);
}
string sentencia = "Update " + tabla + " set ";
for (int ind = 0; ind < campos.Length; ind++)
{//Se usa el mismo nombre del campo para su parámetro correspondiente pero con la @ delante.
sentencia += campos[ind] + "=@" + campos[ind];
sentencia += (ind == (campos.Length - 1)) ? " " : ", ";
}
sentencia += "where " + cadenaCondicion + " ";
sentencia += "select @@rowcount";
//Al usar el "select @@rowcount", Ejecuta() devuelve el nº de registros afectados.
using (SqlCommand cmd = new SqlCommand(sentencia))
{
//Parámetros Sql para los campos de la tabla:
for (int ind = 0; ind < valores.Length; ind++)
{
SqlParameter paramCampo = new SqlParameter();
paramCampo.ParameterName = "@" + campos[ind];
paramCampo.SqlDbType = TipoNetToSql(valores[ind]);
paramCampo.Value = valores[ind];
cmd.Parameters.Add(paramCampo);
}
//Parámetros Sql para la Condición:
foreach (CadenaParametro parametro in condicion)
{
SqlParameter paramCondicion = new SqlParameter();
paramCondicion.ParameterName = parametro.NombreParametro;
paramCondicion.SqlDbType = TipoNetToSql(parametro.ValorParametro);
paramCondicion.Value = parametro.ValorParametro;
cmd.Parameters.Add(paramCondicion);
}
FilasDB filas = Ejecuta(cmd);
if (filas.Count > 0)
{
return(Convert.ToInt32(filas[0][0]));
}
else
{
return(0);
}
}
}
