Utilizando secuencias (sequences) en PostgreSQL con C# .

Con frecuencia en un buen diseño de bases de datos es necesario que las tablas tengan al menos un identificador único para poderlas relacionar con otras, siguiendo las recomendaciones de las conocidas reglas de Codd para el modelo relacional de bases de datos.Para cumplir con este requerimiento podemos fijarnos en la entidad que representa cada tabla y seguir la especificación del identificador único que le correspondería según nos dicten las reglas del negocio o el tipo de sistema que vamos a construir.

Hay casos en donde la tabla no tiene una entidad definida sino sirve únicamente como tabla de soporte para una relación muchos a muchos, como un catálogo o un listado de parámetros para el sistema, en estos casos donde no se tiene una especificación definida para asignar un identificador único lo más recomendado es utilizar una serie o un contador como valor para ese identificador.

PostgreSQL proporciona unos objetos llamados secuencias (sequences) que sirven para crear contadores o series, las secuencias son objetos de bases de datos al mismo nivel que las tablas, vistas, triggers o funciones.

Aunque pueden crearse contadores y series de forma manual esto no será tan eficiente como los objetos sequence que nos proporciona PostgreSQL ya que mejoran el desempeño de la base de datos sobretodo en sistemas multiusuario. La forma automática de crear una secuencia es utilizar el tipo de dato serial en una columna, como se muestra a continuación con el siguiente script para una tabla llamada Publishers.

Al ejecutar este script se crean dos objetos: la secuencia (sequence) y la tabla (en ese orden), como lo muestra la pestaña messages de pgadmin al finalizar la ejecución del script.

Ahora creamos una función plpgsql con la que agregaremos los registros a la tabla, en esta función establecemos los valores mediante parámetros para cada una de las columnas, excepto claro el identificador (columna publisherid), ya que de ese valor se encargará la secuencia.

Ahora con el siguiente programa en C# probaremos la secuencia agregando algunos registros e imprimiendo sus identificadores en la consola.

Compilamos el programa con el siguiente comando.

$ gmcs -r:/home/martin/lib/Npgsql2.0.11.94/Mono2.0/bin/Npgsql.dll,System.Data Main.cs

Al ejecutar el programa, se mostrarán los registros agregados y los valores que les asigno la secuencia como identificador.

También podemos crear una secuencia (sequence) de forma manual siguiendo la sintaxis:

create sequence [name] start with [number] increment by [number]

De manera predeterminada las secuencias comienzan en el número 1 al menos no se indique con el comando start with.
Por ejemplo creamos una secuencia que se incremente de 6 en 6

Test=# CREATE SEQUENCE my_first_sequence_seq INCREMENT BY 6;

Para acceder a los valores de la secuencia lo hacemos con un SELECT y la función nextval() como se muestra a continuación:

Observamos que la secuencia comienza en 1 e incrementa de 6 en 6.
Para acceder al valor actual de la secuencia lo hacemos con la función currval() como se muestra a continuación:

Para establecer un nuevo valor en la secuencia utilizamos la función setval()

Por último podemos eliminar la secuencia creada con el comando:

DROP SEQUENCE [name]

Como se muestra a continuación, en la siguiente imagen:


Descargar el código fuente del ejemplo (Download the source code)

Utilizando parameterized commands (comandos con parámetros) de ADO.NET en PostgreSQL con GTK#

Para complementar el tema del post anterior, como un segundo ejemplo mostramos un formulario GTK# que utiliza una función PL/SQL, en donde además de los parámetros de entrada se establecen dos parámetros de salida, los cuales una vez de ejecutada la función devuelve los valores número de referencia y la fecha actual, que el formulario GTK# muestra en un mensaje.
Para preparar el ejemplo, creamos una base de datos llamada Test con una tabla llamada workitems en donde la función creará un nuevo registro.
Aquí el código de la tabla:


A continuación el código de la función.


Esta función utiliza la función createrefnum(id) utilizada en la primera parte de este tutorial.
En esta función los parámetros de salida se especifican con la palabra reservada OUT junto a los parámetros de entrada en los argumentos, inmediatamente después del la declaración del nombre, como se muestra el siguiente fragmento del código.


create or replace function usp_createworkitem(out nref varchar,varchar,varchar,varchar,
varchar,out creationDate timestamp)

En este ejemplo debemos de tener una solución en MonoDevelop con dos tipos de proyectos: uno de tipo library en donde se pondrán las siguientes clases:
WorkItem.cs : representa la clase de transporte de datos o POCO.
MessageDAC.cs: que representa la clase de acceso a PostgreSQL.

El otro es un proyecto tipo GTK# que contiene las clases Main.cs y MainWindow.cs correspondientes a la GUI del formulario y al manejo de eventos.
La solución deberá de verse como en la siguiente imagen:

El diseño del formulario queda como en la siguiente imagen:

A continuación el código de las clases de la solución.
Aquí el código de las clase Workitem.cs


Aquí el código de la clase MessageDAC.cs


Aquí el código de la clase MainWindow.cs


En la clase MessageDAC se encuentra el código en donde ejecutamos la función, para que se ejecute correctamente debemos establecer la propiedad CommandType de la clase NpgsqlCommand con el valor de la enumeración CommandType.StoredProcedure.

cmd.CommandType = CommandType.StoredProcedure;

Entonces creamos los parámetros de entrada y de salida, para los de salida se crea un NpgsqlParameter por cada uno y se establece su propiedad Direction con la enumeración ParameterDirection.Output, es importante no olvidar agregarlos a la enumeración Parameters del NpgsqlCommand.

NpgsqlParameter nref = new NpgsqlParameter("nref",NpgsqlDbType.Varchar);
nref.Direction = ParameterDirection.Output;
cmd.Parameters.Add(nref);
cmd.Parameters.Add("Title",NpgsqlDbType.Varchar).Value = wk.Title;
cmd.Parameters.Add("AssignedTo",NpgsqlDbType.Varchar).Value = wk.AssignedTo;
cmd.Parameters.Add("Area",NpgsqlDbType.Varchar).Value = wk.Area;
cmd.Parameters.Add("Reason",NpgsqlDbType.Varchar).Value = wk.Reason;
NpgsqlParameter created = new NpgsqlParameter("creationDate",NpgsqlDbType.Timestamp);
created.Direction = ParameterDirection.Output;
cmd.Parameters.Add(created);

Después de ejecutar el comando para llamar la función obtenemos el valor de regreso, haciendo referencia por su nombre de la enumeración Parameters.

wk.Numref = cmd.Parameters["nref"].Value.ToString();
wk.Created = Convert.ToDateTime(cmd.Parameters["creationDate"].Value);

Al compilar y ejecutar el formulario se verá como en la siguiente imagen:

Al introducir los valores y presionar el botón OK se ejecutará el llamado a la función agregando el registro y devolviendo los valores de salida, que se mostrarán con un mensaje como se ve en la siguiente imagen:

Si todo se ejecuto correctamente, podemos consultar el registro ejecutando el siguiente comando desde una terminal:


$ psql Test –c “Select * from workitems” - A

Descarga el código fuente en un proyecto para MonoDevelop o Visual Studio

Utilizando parameterized commands (comandos con parámetros) de ADO.NET en PostgreSQL con C#.

Aunque este tutorial se centra en PostgreSQL, utilizo la clase genérica
DbCommand, ya que estos conceptos son aplicables a otros manejadores de bases de datos como Oracle o SQL Server, únicamente reemplazando las clases DbCommand y DbParameter por las clases específicas de ADO .NET para estas bases de datos.

La clase DbCommand de ADO .NET nos proporciona la capacidad de ejecutar comandos parametrizados, lo que nos permite pasar información en tiempo de ejecución a los store procedures o comandos SQL que nuestra aplicación .NET envié hacia la base de datos.
Estos parámetros se clasifican por su valor dentro de la enumeración ParameterDirection:

  • Input: Son el tipo predeterminado, envía los valores hacia la base de datos, es posible tener múltiples parámetros de entrada.
  • Output: Similar a los parámetros Input, solo que regresan los valores de retorno una vez que el comando es ejecutado, es posible tener múltiples parámetros de salida.
  • InputOutput: El parámetro es capaz de enviar y recibir un valor después de que el comando es ejecutado.
  • ReturnValue: El parámetro representa el valor de retorno de la función.

El uso de comandos parametrizados nos ofrece los siguientes beneficios:

  • Nos permite definir el tipo de dato del parámetro.
  • Evita la concatenación de sentencias SQL en el código, con lo que disminuye el riesgo de un ataque SQL Injection.
  • Obtenemos un mejor rendimiento, las consultas pre-compiladas tienen un mejor desempeño al ejecutarse ya que el plan de ejecución es reutilizado para la misma consulta en vez construirlo repetidamente en cada ejecución como ocurre con las consultas SQL literales.
  • Los parámetros son revisados y validados para comprobar que no exista código malicioso, una de esas validaciones es la longitud, por ejemplo si el parámetro especifica una longitud de 50 caracteres, entonces solo 50 caracteres serán aceptados.

DbCommand tiene la propiedad Parameters que representa la colección de parámetros que están asociados al comando SQL. Estos parámetros son representados por el objeto DbParameter el cual tiene las siguientes propiedades:

    DbType: Representa el tipo de dato de la fuente de datos como un tipo CLR.
    Direction: Indica si el parámetro es de entrada, de salida o bidireccional.
    IsNullable: Indica si el parámetro acepta valores nulos.
    ParameterName: Representa el nombre del parámetro.
    Size: Representa la longitud del parámetro.
    Value: Obtiene u establece el valor del parámetro.

Como ejemplo un programa C# que muestra el uso de los comandos parametrizados llamando a una función de PL/pgSQL en PostgreSQL que realiza una concatenación de un número aleatorio, el año actual y el parámetro de entrada para crear un número distinto cada vez que se ejecute.
El código PL/pgsql de la función es el siguiente:


El resultado de la ejecución de la función es:

A continuación un ejemplo en C# en donde se muestra el uso de los parámetros ejecutando la función anterior con su parámetro de entrada (input) y recibiendo el valor de retorno.


Este ejemplo en MonoDevelop puede construirse como una aplicación de consola, agregando las refrencias al ensamblado Npgsql, como se muestra en la siguiente imagen:

La salida de la ejecucción de este programa es:

Si se ejecuta desde MonoDevelop la salida es:


Descargar el código fuente del ejemplo (Download the source code)

Trabajando con Store Procedures PL/pgSQL en PostgreSQL

Los lenguajes de procedimientos (procedural languages) han extendido la funcionalidad de las bases de datos proporcionando al lenguaje SQL cierto flujo de control similar al de un lenguaje de programación.

Cada fabricante tiene su propia implementación de un lenguaje de procedimiento basado en SQL, así Microsoft SQL Server tiene Transact-SQL, Oracle tiene PL/SQL y así sucesivamente una lista de bases de datos con su correspondiente lenguaje de procedimiento.
En el caso de PostgreSQL se tienen varios lenguajes que pueden usarse como lenguajes de procedimiento entre ellos tenemos a PL/Tcl, PL/Perl, PL/Python,C y como opción predeterminada PL/pgSQL.

Las ventajas de ejecutar funciones del lado del servidor o Store Procedures con PL/pgSQL son las siguientes:

  1. Extensibilidad: PL/pgsql es como un lenguaje de programación incluye la asignación y evaluación de variables y la posibilidad de hacer iteraciones y cálculos más complejos que con SQL.
  2. Seguridad: Ayuda a evitar ciertos ataques con SQL Injection ya que al utilizar parámetros evita la construcción de comandos SQL utilizando la concatenación de cadenas.
  3. Rapidez: Son ejecutados más rápidamente que las consultas SQL individuales ya que después de la primera ejecución el plan de ejecución se mantiene en memoria y el código no tiene que ser analizado ni optimizado nuevamente.
  4. Programación modular: similar a los procedimientos y funciones en los lenguajes de programación, lo que evita que se tengan planas de sentencias SQL.
  5. Reutilización: de código una función puede ejecutarse dentro de distintos Store Procedures.
  6. Rendimiento: un Store Procedure puede contener decenas de sentencias SQL que son ejecutadas como una sola unidad de golpe, a diferencia de las sentencias SQL individuales donde hay que esperar a que cada una sea procesada.

Mediante los siguientes ejemplos mostraremos el uso de Store Procedures utilizando PL/pgSQL.

Creamos una base de datos de ejemplo llamada Catalogs con el siguiente comando desde el Shell.

    $ createdb Catalogs
Fig 1. Creando la base de datos de ejemplo

Revisamos los lenguajes de procedimiento instalados en la base de datos, revisamos que PL/pgSQL se encuentre instalado con el siguiente comando.

    $ createlang -l Catalogs
Fig 2. Revisando la instalación del lenguaje PLSQL

Si no se encuentra, entonces ejecutamos el siguiente comando para instalarlo, esto siempre como administrador del servidor:

    $ createlang –U postgres plpgsql Catalogs

Si está instalado entonces abrimos un editor de texto y creamos un archivo llamado catalogs.sql donde escribiremos los comandos para crear las tablas de ejemplo y los Store Procedures para administrar los registros de cada una de las tablas.

Para la creación de las tablas escribimos lo siguiente:

En acuerdo con la llave foránea definida en cada tabla, debe existir un país para poder crear un estado, así mismo debe de existir un estado para poder crear una ciudad, entonces creamos unos registros en la tabla países

Fig 3. Insertando los registros en la tabla

Supongamos que estas tablas van a utilizarse en un sistema donde sea obligatorio que los nombres de país, estado y ciudad, se almacenen teniendo la primera letra mayúscula o en notación Camel Case,(en este caso los países quedaron guardados con letras minúsculas de manera intencional, con el fin de mostrar un Store Procedure).

Creamos entonces una función para aplicar esta regla a los datos de la tabla countries.

La función se define con el siguiente código:

Lo aplicamos de la siguiente manera para actualizar los registros.

Fig 4. Actualizando los registros utilizando la función UpperCamelCase

Una vez creada en el servidor se encuentra disponible para cualquier transformación que queramos aplicar sobre cualquier cadena.

Fig 5. Ejecutando la función UpperCamelCase

Ahora escribiremos las funciones para insertar registros en cada una de las tablas.


Básicamente la estructura de un Store Procedure es la siguiente:

CREATE FUNCTION [nombre de la función] ([parámetros separados por comas]) RETURNS [el tipo de dato que regresa] AS
'DECLARE aquí se definen las variables que se usarán.
BEGIN indica el inicio de la función.
SELECT INTO este comando permite que los resultados de las consultas sean asignados a variables. 
(no debe de confundirse con SELECT [columnas] INTO)
RETURN Sale de la función y regresa el tipo de dato que se declaro después de la palabra RETURNS del CREATE FUNCTION
END indica el fin de la función
' LANGUAGE indica con que lenguaje esta escrita la función, puede ser un lenguaje de procedimiento 
(plpgsql) o de consulta (SQL).

Vemos que en cada Store Procedure, reutiliza la función UpperCamelCase(varchar) que habíamos previamente creado.

Esto porque un Store Procedure puede llamar a otro Store Procedure que se encuentre disponible.

En las siguientes imágenes los resultados de la ejecución de cada Store Procedure.

Fig 6. Ejecucción del procedimiento InsertState

Fig 7. Ejecucción del procedimiento InsertCity

Fig 8. Comprobando la aplicación de los procedimientos en los datos

  Descarga el código fuente para PostgreSQL

Utilizando los objetos NpgsqlConnection y NpgConnectionStringBuilder para PostgreSQL con MonoDevelop

Hoy día es difícil imaginarse un sistema informático que no haga uso de fuente de datos para persistir información.
Entre estas fuentes de datos se encuentran:

  • Sin estructura: Archivos que no tienen un orden lógico. (Cartas, memos)
  • Estructurado, sin jerarquía: Contienen datos agrupados por separadores o indicadores de inicio y fin (archivos de acceso secuencial, archivos separados por tabuladores ó comas)
  • Jerárquica: tienen una estructura de nodos anidados. (XML)
  • Bases de datos relacionales:
  • Objetos: Los datos están organizados como objetos.

Debido a esta variedad, las clases de ADO para acceder y extraer cuentan con un diseño independiente y portable de la implementación de la fuente de datos que se utilice.
Desde luego, primeramente debemos tener acceso a la fuente de datos antes de ejecutar operaciones de consulta o modificación.
Para lograr este propósito usamos un objeto proveedor de conexión especifico según la fuente de datos a utilizar, cada uno de estos objetos son equivalentes en funcionalidades generales por derivar de la clase DBConnection.
Aunque estos objetos son equivalentes en la funcionalidad general, cada uno tiene código específico para su fuente de datos, como se muestra en la tabla siguiente.


+-----------------+-------------------+
| Fuente de datos |Objeto de conexión |
+-----------------+-------------------+
| SQL Server | SqlConnection |
+-----------------+-------------------+
| Suportan OLE DB | OleDbConnection |
+-----------------+-------------------+
| Oracle | Oracleconnection* |
+-----------------+-------------------+
| PostgreSQL | NpgsqlConnection |
+-----------------+-------------------+
*Depende de que el cliente de Oracle se encuentre instalado.

El objeto NpgsqlConnection

Un objeto conexión representa una conexión física a una fuente de datos, por lo que una de las mejores recomendaciones es conectarse y cerrar las conexiones abiertas en cuanto dejen de utilizarse.
ConnectionStrings (Cadenas de conexión)
Antes de trabajar con cualquiera de los objetos de conexión es indispensable proporcionarle la Connection String (cadena de conexión) , esta Connection String es propia de cada objeto Connection y cada objeto Connection es propio de cada fuente de datos con la que trabajemos.
La cadena de conexión es una serie de parámetros y valores separados por (;) y aunque cada Connection String cambie según el proveedor de Base de datos, hay ciertos parámetros que son siempre requeridos, como:


+---------------------------+-------------------------------------------------+
|Data Source o Server | El lugar donde se encuentra la fuente de datos: |
| servidor, máquina, lugar de red, IP, directorio,|
| archivo. |
+---------------------------+-------------------------------------------------+
DataBase o Initial Catalog | El nombre de la base de datos a usar. |
+---------------------------+-------------------------------------------------+
|User ID | El usuario para acceder. |
+---------------------------+-------------------------------------------------+
Password | El password del usuario para aceder |
+---------------------------+-------------------------------------------------+
Integrated Security | Si es true se utiliza la seguridad de Windows
| para acceder, si es false se debe de proporcionar
| el user y password para acceder.
+---------------------------+-------------------------------------------------+
Timeout o Connection Timeout| El tiempo que debe esperar la aplicación para |
| que el servidor le asigne una conexión. |
| El valor predeterminado es de 15 segundos |
+---------------------------+-------------------------------------------------+
Pooling | Crea un pool de conexiones para la cadena de |
| conexión si no existe, caso contrario |
| asigna una conexión de un pool existente. |
+---------------------------+-------------------------------------------------+
MinPoolSize | Número mínimo de conexiones por pool, valor |
| predeterminado 1 |
+---------------------------+-------------------------------------------------+
MaxPoolSize | Número máximo de conexiones por pool, valor |
| máximo permitido 100 |
+---------------------------+-------------------------------------------------+

El sitio Connection Strings.com contiene ejemplos y una amplia referencia a cada uno de los parámetros y valores según la fuente de datos.

El objeto NpgsqlConnectionStringBuilder

Hay una clase que nos ayuda a evitar errores de sintaxis al momento de construir cadenas de conexión, esto para no tener recordar cada una de las opciones en el caso de trabajar con diferentes bases de datos. Esta clase es similar a las clases Connection, hay una clase DbConnectionStringBuilder de donde surgen las objetos ConnectionStringBuilder específicos para cada cadena de conexión de cada objeto Connection.
Derivados de la clase DbConnectionStringBuilder, existe la clases NpgsqlConnectionStringBuilder la cual encapsula una ConnectionString (cadena de conexión) para PostgreSQL, la cual demostraremos en el siguiente programa junto con el uso de la clase NpgsqlConnection respectivamente.

Abrimos MonoDevelop, creamos una nueva solución GTK# y agregamos unos controles al formulario para que su aspecto luzca como en la siguiente imagen.

Como utilizaremos el proveedor de datos de PostgreSQL para .NET debemos agregarlo a la solución, hacemos click derecho en la solución(recomiendo la versión 2.x).

Aparecerá la ventana para agregar/quitar las referencias a los ensamblados instalados en el GAC o para buscarlos en el sistema de archivos.

Una vez agregado correctamente, se mostrará el ensamblado en la solución.

Dentro del método para dar funcionalidad al botón de aceptar, mostramos la utilización de la clase NpgsqlConnectionStringBuilder como ayuda para crear la cadena de conexión.

También dentro de este método tenemos el código para la creación de la conexión al servidor.


using(NpgsqlConnection conn = new NpgsqlConnection(connString.ToString())){
conn.Open();
if(conn.State == System.Data.ConnectionState.Open)
MessageBox("Conexión exitosa",MessageType.Info);
}

Si descargamos el código completo del proyecto, al compilarlo correctamente y ejecutarlo,nos pedirá teclear los parámetros para una conexión hacia un servidor PostgreSQL.

Si los parámetros son correctos y el servidor se encuentra disponible entonces la aplicación mostrará el mensaje siguiente:

En caso de un parámetro incorrecto nos mostrará los mensajes de la excepción recibida.

  Descarga el código fuente del proyecto para MonoDevelop

El patrón singleton con Monodevelop y PostgreSQL

En el post anterior mostré un ejemplo utilizando el patrón Singleton con conexiones a bases de datos SQL Server ejecutándonse bajo Microsoft Windows, ahora mostraré las modificaciones que tuve que realizar a ese ejemplo para que funcione con
PostgreSQL como base de datos y Linux como sistema operativo.
Primeramente abrí el proyecto en Monodevelop, después descargue la última versión de la biblioteca Npgsql para tener actualizado los últimos cambios de esta biblioteca, lo cuál es recomendable ya que además de arreglar los errores el equipo responsable agrega nuevas funcionalidades, una vez descargada la biblioteca comence con las siguientes modificaciones:

1-.Quitar la referencia al ensamblado de Sql Server (System.Data.SqlClient) y reemplazarlo por el de PostgreSQL (Npgsql), esto se hace dentro del explorador de la solución, haciendo click derecho sobre las referencias como se muestra en la imagen.

Nos aparecerá la ventana “edit references”, ahí escogemos la pestaña que dice “Ensamblado .Net” para buscar en el sistema de archivos en la ruta donde descargamos y descomprimimos la biblioteca Npgsql,esto para seleccionar el ensamblado Npgsql.dll como se muestra en la siguiente imagen.

2-. Reemplazar en el código de las clases DataBase y DataBase2 respectivamente el namespace System.Data.SqlClient por Npgsql
using System.Data.SqlClient;
por
using Npgsql;

3-. También en el código de esas clases reemplazar las líneas donde se hace la declaración del objeto que representa la conexión y donde se realiza la creación de dicho objeto, reemplazar las siguientes líneas:

SqlConnection _conn = null;
_conn = new SqlConnection();

por

NpgsqlConnection _conn = null;
_conn = new NpgsqlConnection();

4-. En estas mismas clases cambiar el tipo de excepción en el catch del try del metódo GetConnection().
catch(SqlException x)
por
catch(NpgsqlException x)

5-. Reemplazar el evento StateChange del objeto NpgsqlConnection y el delegado asociado al evento, por una propiedad de tipo string
que infome de las características de la conexión, esto debido a que aún el proveedor de datos de postgreSQL no cuenta con esta característica.

StateChange += delegate(object o,StateChangeEventArgs args){
Info = String.Format(" {1}| {2}| {3}|",args.CurrentState.ToString(),
System.DateTime.Now.ToLocalTime(), _conn.DataSource,_conn.Database);

por

Info = String.Format(" {0}|{1}| {2}| {3}|",GetCurrentState,System.DateTime.Now.ToLocalTime(),
_conn.DataSource ,_conn.Database);

Agregando la siguiente propiedad

public string GetCurrentState{ get {return (IsOpen == true ? "Open" : "Closed");}}

Terminando los cambios debemos de configurar MonoDevelop para que al ejecutar la aplicación el programa se ejecute en una terminal propia y no en la ventana de salida (output) de MonoDevelop, esto se hace en el menú principal en la opción “Proyecto” eligiendo el submenú “Opciones”, nos mostrará una pantalla como la siguiente imagen.


Después de estos cambios podemos observar el resultado, de forma idéntica al resultado logrado en la plataforma Microsoft.
Ingreso de los parámetros de conexión


Sin el uso del patrón Singleton, cambian los tiempos en la conexión


Usando el patrón Singleton, el tiempo es único.

 Descarga el código fuente