[Ado.Net] Clase de conexion generica para cualquier motor de base datos, usando .Net Providers

Problema: En internet hay mucha información sobre como trabajar con Ado.Net y SQL, pero cuando tenemos que usar un nuevo proveedor  de base de datos desde .Net, comenzamos a buscar ejemplos específicos sin darnos cuenta, que también podemos usar los ejemplos de Ado.Net y SQL (usar la estructura).

Solución: Exponer una estructura básica de Ado.NET para las diversas operaciones que podemos hacer una fuente de datos, consultar, insertar, actualizar, y eliminar, y que esta estructura puede ser usada con cualquier proveedor disponible en .Net.

En el artículo usaremos las clases: XyzConnection, XyzCommand, XyzDataReader, y un XyzParameter, para presentar las clases base que después pueden ser reemplazadas (en la mayoría de casos) por las siguientes clases de acuerdo a los proveedores:

  1. SQL Server: SqlConnection, SqlCommand, SqlDataReader, y SqlParameter.
  2. SQL Server CE: SqlCeConnection, SqlCeCommand, SqlCeDataReader, y SqlCeParameter.
  3. Oracle (ODP.Net): OracleConnection, OracleCommand, OracleDataReader, y OracleParameter. Hay un proveedor de MS: Microsoft’s .NET for Oracle Provider, pero el Oracle Data Provider for .NET, es el oficial de la gentita de Oracle.
  4. DB2: DB2Connection, DB2Command, DB2DataReader, y DB2Parameter.
  5. MySQL: MySqlConnection, MySqlCommand, MySqlDataReader, y MySqlParameter. Ejemplos: Connector/NET Examples and Usage Guide.
  6. PostgreSQL: NpgsqlConnection, NpgsqlCommand, NpgsqlDataReader, y NpgsqlParameter. Ejemplos: Npgsql 2.0 User Manual.
  7. VistaDB: VistaDBConnection, VistaDBCommand, VistaDBDataReader, y VistaDBParameter.
  8. OleDb: OleDbConnection, OleDbCommand, OleDbDataReader, y OleDbParameter.
  9. Odbc: OdbcConnection, OdbcCommand, OdbcDataReader, y OdbcParameter.
  10. Y así…., se entiende la idea?. Ya no pongo más proveedores, por que no acabo la entrada. No se pierda la saga: OleDb vs Odbc.

En el caso que este disponible un proveedor puntual para una fuente de datos, podemos usar OleDb, y si no esta disponible en está, podemos usar Odbc:

  1. Access. Ejemplo de conexión con OleDb. Ejemplo en código: Using ADO .NET – Access and OleDB Part 2. Más ejemplos.
  2. Excel: Ejemplo de conexión con OleDb. Ejemplos en código: Reading Excel (.xls) Files with ADO.NET, y Reading and Writing Excel Spreadsheets Using ADO.NET C# DbProviderFactory. Más ejemplos.
  3. Text Files (CSV, tab, custom): Ejemplo de conexión con OleDb. Ejemplo en código: Using OleDb to Import Text Files (tab, CSV, custom). Más ejemplos.
  4. Se entiende la idea?

 

Código Ejemplo

Y después de tantos links, vayamos con el código propuesto para hacer operaciones (CRUD) contra una fuente de datos.

En nuestro modelo, para enviar o recuperar información no vamos a usar DataSet ni DataTables, vamos a usar objetos y listas (generics) de objetos:

  1. Las listas de objetos son tipadas. Con dataset: Convert.ToInt32(ds.Tables[0].Rows[0][“SupplierID”]), con un List<Supplier>: lstSuppliers[0].SupplierID, se nota la diferencia no?. El dataset también se puede tipar, pero no viene por defecto.
  2. El rendimiento que se puede lograr con una lista puede ser mejor que con DataSets. Revisar: Anti Prácticas .NET: Lectura de Datos con ADO.NET II.
  3. Si están trabajando con ASP.Net, es soportado por el control ObjetDataSource.
  4. Le tengo cariño a los List<Object>.

P.D.: Ojo con esto no quiero desechar a los Dataset y Datatable, quizás para Winforms se le encuentre más beneficios (traer toda la data y filtrar data en memoria), pero en entornos Web la palabra rendimiento es más necesitada, por eso los filtros deben venir desde la base de datos y no en memoria.

Como vamos a usar Listas (generics) de objetos para pasar entre nuestros métodos/capas, vamos a tener que definir nuestras entidades (capa de Entidades). La definición de nuestro entidad Category (Category.cs):

   1: using System;
   2:  
   3: namespace Northwind.BO
   4: {
   5:  public  class Cateogory
   6:   {
   7:  
   8:    private Int32 categoryIDField;   
   9:    public Int32 CategoryID
  10:    {
  11:      get { return categoryIDField; }
  12:      set { categoryIDField = value; }
  13:    }
  14:  
  15:    private String categoryNameField;
  16:    public String CategoryName
  17:    {
  18:      get { return categoryNameField; }
  19:      set { categoryNameField = value; }
  20:    }
  21:  
  22:    private String descriptionField;
  23:    public String Description
  24:    {
  25:      get { return descriptionField; }
  26:      set { descriptionField = value; }
  27:    }
  28:  
  29:   }
  30: }

Esta clase (entidad) normalmente tiene un atributo por cada columna de la tabla a mapear. En este caso hemos mapeado la tabla Category, de la famosísima Northwind. Nota: Si es una aplicación Web ASP.NET, y va usar el control ObjectDataSource, no se olvide de usar el atributo: DataObjectField. Leer más.

Ahora vamos a implementar la clase de acceso a datos (capa de acceso a datos) XyzCategory. Esta sólo es una clase base, y podemos hacer el uso de patrones, interfaces, o custom, para hacer más genérica esta estructura, y que sea el contrato para cualquier operación con la base de datos. Por hacer más simples los métodos, quedo pendiente el manejo de excepciones, pero cuando intente borrar un registro con dependencias notará su necesidade. No se olvide de revisar el uso de Using (con lección de Unai incluida).

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Configuration;
   4: using System.Data.XyzClient;
   5: using Northwind.BO;
   6:  
   7: namespace Northwind.DA
   8: {
   9:   public class XyzCategory
  10:   {
  11:     private String connectionString;
  12:  
  13:     public XyzCategory(String connectionName)
  14:     {
  15:       connectionString =
  16:         ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
  17:     }
  18:  
  19:     #region getAll , getOne
  20:  
  21:     public List<Cateogory> getAll()
  22:     {   
  23:     }
  24:  
  25:     public Cateogory getOneByID(Int32 catID)
  26:     {      
  27:     }
  28:  
  29:     #endregion
  30:  
  31:     #region Insert, Update, Delete
  32:  
  33:     public Int32 Insert(Cateogory objCat)
  34:     {    
  35:     }
  36:  
  37:     public Boolean Update(Cateogory objCat)
  38:     {    
  39:     }
  40:  
  41:     public Boolean Delete(Int32 categoryID)
  42:     {    
  43:     }
  44:  
  45:     #endregion
  46:   }
  47: }

Vayamos con los métodos específicos, el método getAll vamos a traer la lista de registros. Si están en una aplicación Web la recomendación es traer la información paginada para no traer todos los resultados, y si estamos usando un ObjectDataSource tendríamos que agregar un método Count (será tema de otro post). Nota: para los string que contienen los queries vamos a usar: cadenas verbatim.

   1: public List<Cateogory> getAll()
   2: {
   3:   List<Cateogory> lstSup = new List<Cateogory>();    
   4:  
   5:   //crear conexion
   6:   using (XyzConnection xxxCn = new XyzConnection())
   7:   {
   8:  
   9:     String cmdText = @"SELECT categoryID, categoryName, Description  
  10:                        FROM Categories";
  11:     //crear comando
  12:     using (XyzCommand cmd = new XyzCommand(cmdText, xxxCn))
  13:     {
  14:       //abrir conexion
  15:       cmd.Connection.Open();
  16:  
  17:       //recorrer resultados
  18:       using (XyzDataReader rd = cmd.ExecuteReader())
  19:       {
  20:  
  21:         Cateogory objCat = null;
  22:  
  23:         //recuperar el orden de columnas
  24:         Int32 categoryIDIndex = rd.GetOrdinal("categoryID");
  25:         Int32 categoryNameIndex = rd.GetOrdinal("categoryName");
  26:         Int32 descriptionIndex = rd.GetOrdinal("Description");
  27:  
  28:         Int32 colCount = rd.FieldCount;
  29:         Object[] values = new Object[colCount];
  30:  
  31:         while (rd.Read())
  32:         {
  33:           objCat = new Cateogory();
  34:  
  35:           //obtener todos los valores
  36:           rd.GetValues(values);
  37:  
  38:           objCat.CategoryID = Convert.ToInt32(values[categoryIDIndex]);
  39:           objCat.CategoryName = values[categoryNameIndex].ToString();
  40:           //en el caso de que la columna sea null
  41:           //Null to Int = (exception)
  42:           if ( !(values[descriptionIndex] is DBNull))
  43:           {
  44:             objCat.Description = values[descriptionIndex].ToString();
  45:           }
  46:          
  47:           //agregar objecto a la lista
  48:           lstSup.Add(objCat);
  49:         }
  50:       }
  51:     }
  52:   }
  53:  
  54:   return lstSup;
  55: }

Ahora el método para devolver un registro, si va usar SQL dinámico, es recomendable usar consultas parametrizadas para enviar los ataques SQL-Injection:

   1: public Cateogory getOneByID(Int32 catID)
   2: {
   3:   Cateogory objCat = null;
   4:  
   5:   if (catID > 0)
   6:   {
   7:  
   8:     //connection
   9:     using (XyzConnection xxxCn = new XyzConnection())
  10:     {
  11:  
  12:       String cmdText = @"SELECT categoryID, categoryName, Description
  13:                          FROM Categories 
  14:                          WHERE CategoryID=@categoryID";
  15:       //comando
  16:       using (XyzCommand cmd = new XyzCommand(cmdText, xxxCn))
  17:       {           
  18:         cmd.Connection.Open();
  19:  
  20:         //consulta parametrizada 
  21:         //evita ataques SQL-Injection
  22:         XyzParameter param = new XyzParameter("categoryID", catID);
  23:         cmd.Parameters.Add(param);
  24:  
  25:         using (XyzDataReader rd = cmd.ExecuteReader())
  26:         {
  27:  
  28:           Int32 categoryIDIndex = rd.GetOrdinal("categoryID");
  29:           Int32 categoryNameIndex = rd.GetOrdinal("categoryName");
  30:           Int32 descriptionIndex = rd.GetOrdinal("Description");
  31:  
  32:           Int32 colCount = rd.FieldCount;
  33:           Object[] values = new Object[colCount];
  34:  
  35:           while (rd.Read())
  36:           {
  37:             objCat = new Cateogory();
  38:  
  39:             rd.GetValues(values);
  40:             objCat.CategoryID = Convert.ToInt32(values[categoryIDIndex]);
  41:             objCat.CategoryName = values[categoryNameIndex].ToString();
  42:             if (!(values[descriptionIndex] is DBNull))
  43:             {
  44:               objCat.Description = values[descriptionIndex].ToString();
  45:             }
  46:           }
  47:         }
  48:       }          
  49:     }                
  50:   }
  51:  
  52:   return objCat;
  53: }

Método insertar, notar como pasamos todos los parámetros del registro a través de la entidad. No se pierda la saga @@Identity vs SCOPE_IDENTITY (Only SQL).

   1: public Int32 Insert(Cateogory objCat)
   2: {
   3:   Int32 catID = 0;
   4:  
   5:   using (XyzConnection xxxCn = new XyzConnection())
   6:   {
   7:  
   8:     String cmdText = @"INSERT INTO Categories (categoryName, Description)
   9:                        VALUES (@categoryName, @description) 
  10:                        SELECT SCOPE_IDENTITY() ";
  11:     //comando
  12:     using (XyzCommand cmd = new XyzCommand(cmdText, xxxCn))
  13:     {
  14:       cmd.Connection.Open();
  15:  
  16:       //parametros
  17:       XyzParameter param = 
  18:           new XyzParameter( "categoryID", objCat.CategoryName);
  19:       cmd.Parameters.Add(param);
  20:       param = new XyzParameter("Description", objCat.Description);
  21:       cmd.Parameters.Add(param);
  22:  
  23:       catID = Convert.ToInt32(cmd.ExecuteScalar());
  24:       
  25:     }       
  26:   }     
  27:   return catID;
  28: }

Método Actualizar, si van tener varios tipos de actualización como actualizar registro y actualizar estado, podría crear dos métodos Update con distinto nombre, y cada uno con el query específico y usando sólo las propiedades que necesite:

   1: public Boolean Update(Cateogory objCat)
   2: {
   3:   Boolean exito = false;
   4:  
   5:   using (XyzConnection xxxCn = new XyzConnection())
   6:   {
   7:  
   8:     String cmdText = @"UPDATE  Categories 
   9:                        SET categoryName = @categoryName, 
  10:                            Description =  @description
  11:                        WHERE categoryID = @categoryID ";       
  12:     using (XyzCommand cmd = new XyzCommand(cmdText, xxxCn))
  13:     {
  14:       cmd.Connection.Open();
  15:       
  16:       XyzParameter param =
  17:           new XyzParameter("Description", objCat.Description);
  18:       cmd.Parameters.Add(param);
  19:       param = new XyzParameter("categoryName", objCat.CategoryName);
  20:       cmd.Parameters.Add(param);
  21:       param = new XyzParameter("categoryID", objCat.CategoryID);
  22:       cmd.Parameters.Add(param);
  23:  
  24:       exito = Convert.ToBoolean(cmd.ExecuteNonQuery());
  25:  
  26:     }      
  27:   }     
  28:  
  29:   return exito;
  30: }

Y con el último método, eliminar, para cerrar la entrada. Nota: no se olvide de revisar el tema de manejo de excepciones.

   1: public Boolean Delete(Int32 categoryID)
   2: {
   3:   Boolean exito = false;
   4:  
   5:   using (XyzConnection xxxCn = new XyzConnection())
   6:   {
   7:  
   8:     String cmdText = @"DELETE  Categories                            
   9:                        WHERE categoryID = @categoryID ";      
  10:     using (XyzCommand cmd = new XyzCommand(cmdText, xxxCn))
  11:     {
  12:       cmd.Connection.Open();
  13:      
  14:       XyzParameter param =
  15:           new XyzParameter("categoryID", categoryID);
  16:       cmd.Parameters.Add(param);    
  17:  
  18:       exito = Convert.ToBoolean(cmd.ExecuteNonQuery());
  19:  
  20:     }        
  21:   }    
  22:  
  23:   return exito;
  24: }

P.D.: Preguntas (con respecto a la estructura), sugerencias, bugs, en los comentarios por favor :D. A medida del tiempo, y bajo demanda trataré de enviar ejemplos específicos para un determinado proveedor .Net.

P.D.2.: Si tiene preguntas técnicas con respecto algún código en alguno de estos providers, les recomendaría visitar algunos de los foros de estos productos (.Net Providers), quizás en algún hilo del foro ya se encuentre la solución.

Saludos,

11 comentarios en “[Ado.Net] Clase de conexion generica para cualquier motor de base datos, usando .Net Providers”

  1. “…Ojo con esto no quiero desechar a los Dataset y Datatable, quizás para Winforms se le encuentre más beneficios (traer toda la data y filtrar data en memoria)…”

    Programo en winforms casi exclusivamente, y siempre he usado listas genéricas para devolver los datos.
    Decís que puede ser más beneficioso programar con dataset y datatable en forms: ¿podrías explicar un poco mejor ésta situación?

  2. Hola Pablo,

    En verdad lo beneficio depende del modelo de programación, tu podrías tener un modelo de programación que sea beneficioso con los List, como también un modelo beneficioso con los DataSet y DataTable.

    Yo también siempre prefiero usar listas, pero tuve que terminar una aplicación Winforms que hacía uso de DataSet y DataTables, y funciona… aunque siempre que puedo cambio pequeñas partes a objetos… debe haber escenarios donde encajan perfectamente un DataSet o DataTable, y creo que deben predominar en las aplicaciones Winforms.

    La ventaja de un DataSet (basado en la aplicación WinForms que tuve que terminar):
    1. Fácil filtrado en memoria (también se puede hacer en Listcon un foreach o dos, o mejor si estas usando .Net 3.5, puedes usar Linq to Objects)
    2. Llenado rápido: adapter.Fill(ds).

    Y en WinForms se puede sacrificar un poco más (a comparación de la Web) de rendimiento por simplicidad.

    Saludos,

  3. Uno de los problemas que tienes cuando trabajas cpm las listas genericas es que no carecen de cierta información que puedes necesitar por ejemplo, la longitud del campo, los campos claves de la entidad, etc. Información muy utilizada para realizar validaciones, busquedas en los controles. ¿ Como resuelves esto ?

  4. Pablo, ahí tienes otra ventaja de los DataSets…

    Y bueno en listas podrías usar atributos en las propiedades de las entidades para solucionar este requerimiento, pero no viene por defecto, hay que hacer codificación adicional.

    P.D.: Para Servicios Web, y que tipo de información devolver, leer los comentarios finales de Rodrigo en esta entrada: http://geeks.ms/blogs/rcorral/archive/2007/02/03/c-mo-llamar-a-servicios-web-desde-c-c.aspx.

    Saludos,

  5. pero no solo eso, tambien hay que tener consideraciones sobre la memoria, ya que si la pc es de poca capacidad, esto afectaria….
    y lo mas importante la cantidad de registros que vayas a usar , ya que si tienes menos de 2000 registros usando …( en memoria..o en tus consultas) el rendimiento no baja mucho, pero si hablamos de unos 20 000 datos ( o mas )… el rendimiento ya bajara notablemente…

    puedes usar muchas soluciones, todo depende de la evaluacion costo /beneficio que hagas.

    salu2

    Ddaz

  6. Sergio, creo que una gran ventaja de los DataSets es la “magia” de cambiar un Select y tener un nuevo DataColumn, sin tener que tocar código.

    Eso, más un buen bindeo automático contra los controles de la interfaz, es una gran herramienta en proyectos con requerimientos cambiantes, pocos desarrolladores o tamaño pequeño-mediano.

    En grandes equipos de trabajo, con proyectos de envergadura y/o con un análisis funcional más detallado (¡y orientado a objetos!), es muy útil trabajar con objetos, pero la ventaja creo pasa por la aplicación de metodologías, modelo de dominio, testing, etc.

    Igualmente ambos enfoques son válidos en la mayoría de los escenarios.

    A lo que nunca le he encontrado ventajas es a los DataSets tipados, mas que el intelisense al momento de escribir código, después de eso tienen como lastre lo peor de los dos mundos.

    Igualmente, en mi experiencia, una gran parte de los desarrolladores se “casan” con lo que más les gusta o conocieron primero y desprecian el otro enfoque, aduciendo generamente problemas de metodología o rendimiento que pasan siempre por el equipo de desarrollo más que por utilizar DataSets u objetos. Saludos

  7. Ale, gracias por los comentarios, y el término se “casan” es muy cierto también.

    Esa magia del dataSet es una ventaja siempre y cuando sea para binding directo al control, pero si vas a trabajar esa nueva columna en una proceso, si tienes que llamar al datarow(“nuevacolumna”)… Y al final todo depende del escenario como dices :D….

    Saludos,

  8. Gente, Gracias por las respuestas.
    La verdad que se me había pasado éste post.

    Cómo todo, supongo que qué usar depende de cada caso.

    Saludos,

  9. para rizar el rizo… como sería un ejemplo de esta clase consumida en un form?

    se pueden ejecutar consultas linq al getall por ejemplo?

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *