En mi caso, hice una publica clase llamada MSSQL, que esta compuesta por 2 funciones:
1.- script(sentencia sql) -> Retorna un String con el resultado de la operacion
2.- query(sentencia sql) -> Retorna un DataTable con el resultado de la operacion
En el Archivo web.config esta definida la cadena de conexion a la Base de Datos, dentro de las eqtiquetas "<connectionStrings>"
<connectionStrings>
<add name="DB" connectionString="Server=IP_SERVIDOR,1433;Initial Catalog=NOMBRE_DB;Persist Security Info=False;User ID=NOMBRE_USUARIO;Password=CONTRASEÑA;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" />
</connectionStrings>
Usamos 2 librerias de referencia:
- using System.Data;
- using System.Data.SqlClient;
En la construccion de la clase MSSQL declare la conexion fuera de las funciones, para que todas pudieran tener acceso a esta...
La variable CONEXION esta declarada como privada, ya que necesito que sea visible solo por las funciones que utilizaran la conexion, al inicializar el objeto le indique que lea la cadena de conexion almacenada en el archivo web.config
private SqlConnection CONEXION = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);
La funcion QUERY(sentencia SQL) recibe como parametro un string con la sentencia sql que se desea ejecutar, y devuelve un DataTable.
///<summary>Devuelve un DataTable con el resultado de la consulta. </summary>
///<param name="QUERY">String de consulta SQL</param>
public DataTable QUERY(string QUERY)
{
DataTable tabla = new DataTable(); //declara un DataTable nuevo que recibira el resultado de la Query
try
{
//Verifica si la conexion no esta abierta, si el estado es distinto a open, la abre.
if (CONEXION.State != ConnectionState.Open)
CONEXION.Open();
//Se declara e inicializa el objeto SqlCommand que contiene la QUERY (que se paso por parametro a la funcion) y la CONEXION.
SqlCommand SENTENCIA = new SqlCommand(QUERY,CONEXION);
//Se define que la propiedad Timeout de la sentencia seran 180 segundos, esta propiedad define cuantotiempo esperar antes de arrojar error.
SENTENCIA.CommandTimeout = 180;
//se crea e inicializa un SqlDataAdapter y se le entrega como parametro el objeto SENTENCIA que contiene la sentencia sql.
SqlDataAdapter ADAPTADOR = new SqlDataAdapter(SENTENCIA);
//Utilizo la funcion Fill(DataTable) del objeto ADAPTADOR, entregandole como parametro la tabla vacia que declaramos al principio, esta funcion se encarga de llenar la tabla con el resultado de la consulta sql.
ADAPTADOR.Fill(tabla);
//realizamos una validacion verificando que vienen resultados en la consulta.
if (tabla.Rows.Count == 0)
{
//si no vienen resultados creamos una tabla y la inicializamos
DataTable tmptbl = new DataTable();
//le agregamos una columna llamada "Resultados"
tmptbl.Columns.Add("Resultados");
//creamos una nueva fila con la estructura de la tabla
DataRow tmp = tmptbl.NewRow();
//le asignamos el valor a la fila nueva en la columna 0 con un texto que indica que no hay resultados
tmp[0] = "No se encontraron resultados.";
//agregamos la fila a la table
tmptbl.Rows.Add(tmp);
//cerramos la conexion
CONEXION.Close();
//retornamos la tabla con el mensaje como resultado a la funcion
return tmptbl;
}
//si por el contrario, SI hay resultados...
else
{
//cerramos la conexion
CONEXION.Close();
//devolvemos como resultado de la funcion la tabla con los resultados.
return tabla;
}
}
//en caso de error lo manejamos de la siguiente forma
catch (Exception ERR)
{
//creamos una tabla y la inicializamos
DataTable tmptbl = new DataTable();
//se agrega una columna con el nombre ERROR
tmptbl.Columns.Add("ERROR");
//agregamos una fila con la estrucutra de la tabla
DataRow tmp = tmptbl.NewRow();
//agregamos un mensaje con el detalle del error
tmp[0] = "Mensaje: " + ERR.Message;
//agregamos la fila a la tabla
tmptbl.Rows.Add(tmp);
//cerramos la conexion al servidor
CONEXION.Close();
//retornamos como resultado al servidor la tabla con el error que se produjo
return tmptbl;
}
}
La funcion SCRIPT(sentencia SQL), recibe como parametro un string con la sentencia sql que se desea ejecutar, y devuelve un string que indica el resultado de la ejecucion de la sentencia SQL indicada.
La explicacion de las partes de esta funcion son casi las mismas de la anterior, excepto por unos pequeños detalles....
public string SCRIPT(string T_SQL)
{
try
{
if (CONEXION.State != ConnectionState.Open)
CONEXION.Open();
SqlCommand SENTENCIA = new SqlCommand(T_SQL, CONEXION);
//la funcion ExecuteNonQuery() devuelve un numero entero que indica el numero de filas afectadas ó -1 si es que es otro tipo de sentencia como un create table por ejemplo...
int AFECTADAS = SENTENCIA.ExecuteNonQuery();
CONEXION.Close();
//si el resultado es -1 se ejecuto una sentencia, y devolvemos un mensaje indicando esto.
if (AFECTADAS == -1)
return "Sentencia Ejecutada.";
else
//si el resultado era >= 0 devolvemos un mensaje indicando cuantos registros fueron afectados
return AFECTADAS.ToString() + " Registros afectados.";
}
En caso de error devolvemos string con el mensaje de error.
catch (Exception ERR)
{
CONEXION.Close();
return ERR.Message;
}
}
Finalmente aca abajo, les dejo el codigo completo de la clase.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
///<summary>Clase para manejo de consultas Microsoft Sql Server</summary>
public class MSSQL
{
private SqlConnection CONEXION = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);
///<summary>Devuelve un texto con el resultado de la operacion. </summary>
///<param name="T_SQL">String de consulta SQL</param>
public string SCRIPT(string T_SQL)
{
try
{
if (CONEXION.State != ConnectionState.Open)
CONEXION.Open();
SqlCommand SENTENCIA = new SqlCommand(T_SQL, CONEXION);
int AFECTADAS = SENTENCIA.ExecuteNonQuery();
CONEXION.Close();
if (AFECTADAS == -1)
return "Sentencia Ejecutada.";
else
return AFECTADAS.ToString() + " Registros afectados.";
}
catch (Exception ERR)
{
CONEXION.Close();
return ERR.Message;
}
}
///<summary>Devuelve un DataTable con el resultado de la consulta. </summary>
///<param name="QUERY">String de consulta SQL</param>
public DataTable QUERY(string QUERY)
{
DataTable tabla = new DataTable();
try
{
if (CONEXION.State != ConnectionState.Open)
CONEXION.Open();
SqlCommand SENTENCIA = new SqlCommand(QUERY,CONEXION);
SENTENCIA.CommandTimeout = 180;
SqlDataAdapter ADAPTADOR = new SqlDataAdapter(SENTENCIA);
ADAPTADOR.Fill(tabla);
if (tabla.Rows.Count == 0)
{
DataTable tmptbl = new DataTable();
tmptbl.Columns.Add("Resultados");
DataRow tmp = tmptbl.NewRow();
tmp[0] = "No se encontraron resultados.";
tmptbl.Rows.Add(tmp);
CONEXION.Close();
return tmptbl;
}
else
{
CONEXION.Close();
return tabla;
}
}
catch (Exception ERR)
{
DataTable tmptbl = new DataTable();
tmptbl.Columns.Add("ERROR");
DataRow tmp = tmptbl.NewRow();
tmp[0] = "Mensaje: " + ERR.Message;
tmptbl.Rows.Add(tmp);
CONEXION.Close();
return tmptbl;
}
}
}
En Mas adelante veremos como pasar un DataTable a HTML o a Excel (.xls), espero les sea util, comentarios de como mejorar esto son bienvenidos...
