Usando el control RadComboBox de Telerik con PostgreSQL

Aunque ASP.NET ofrece una extensa colección de Web Controls que proporcionan al desarrollador una programación fácil, estandarizada y orientada a objetos, también existe como parte del ecosistema de ASP.NET la suite de controles Telerik que complementan y ofrecen más funcionalidad que los Web Controls predeterminados incluidos en el conjunto estándar de ASP.NET.

Del conjunto de controles de la suite Telerik, mostraré brevemente el uso del control RadComboBox de Telerik, el cual extiende la funcionalidad proporcionada por el control DropDownList de ASP.NET al tener no solamente las características de este control sino capacidades adicionales como:la carga de datos de diferentes colecciones de datos, API con funciones de servidor y de cliente, el uso de templates para mejorar la aparencia visual y la propiedad de cargar items bajo demanda para mejorar el rendimiento.

A continuación un sencillo ejemplo para mostrar dos características básicas de este control:

• La carga de items bajo demanda (Load on Demand)
• El uso de templates para la aparencia visual de los items.

El ejemplo consiste de una página ASP.NET que consulta una tabla de products que contiene una lista de productos clínicos de una base de datos myinvoices, la tabla tiene el siguiente esquema:

Consultamos la lista de productos

El ejemplo esta compuesto por 6 clases: DataManager, Logger, PostgreSQLCommand,PostgreSQLDataBase, Product, ProductFilters y dos páginas ASP.NET: Default.aspx y Default2.aspx.

El código de la clase DataManager

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Npgsql;
using System.Data;
using System.Text;

namespace TestRadComboBox
{
public class DataManager
{
public List GetProducts(ProductFilters filters) {
    StringBuilder commandText = new StringBuilder("SELECT product_id, ")
    .Append("product_code, ")
    .Append("product_name ")
    .Append("FROM Products ")
    .AppendFormat("WHERE product_name Like '%{0}%'",filters.ProductName.ToUpper());
    List productList = null;
    Product p = null;
    using(NpgsqlDataReader reader = PostgreSQLCommand.GetReader(commandText.ToString(),
        null,
        CommandType.Text)){
        productList = new List();
        while (reader.Read()) {
            p = new Product();
            p.Product_ID = Convert.ToInt32(reader["product_id"]);
            p.ProductCode = reader["product_code"].ToString();
            p.ProductName = reader["product_name"].ToString();
            productList.Add(p);
        } 
    }
    return productList;
}
}
}

El código de la clase Logger

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace TestRadComboBox
{
public class Logger
{
public static void LogWriteError(string s)
{
using (FileStream stream = new FileStream("log.txt", 
    FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
    StreamWriter sw = new StreamWriter(stream);
    sw.BaseStream.Seek(0, SeekOrigin.End);
    sw.Write(s);
    sw.Flush();
    sw.Close();
}

}
}
}

El código de la clase PostgreSQLCommand

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Npgsql;

namespace TestRadComboBox
{
internal sealed class PostgreSQLCommand
{

internal static NpgsqlDataReader GetReader(string commandText,
NpgsqlParameter[] parameters,System.Data.CommandType cmdtype){
NpgsqlDataReader resp = null;
try{
 NpgsqlConnection conn = PostgreSQLDataBase.GetInstance().GetConnection();
 using (NpgsqlCommand cmd = new NpgsqlCommand(commandText, conn))
 {
            if (parameters != null)
                cmd.Parameters.AddRange(parameters);
  resp = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 }
 return resp;
}catch(Exception ex){
 Logger.LogWriteError(ex.Message);
 throw ex;
}
}
}
}

El código de la clase PostgreSQLDataBase

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using Npgsql;
using NpgsqlTypes;

namespace TestRadComboBox
{
internal sealed class PostgreSQLDataBase
{
static NpgsqlConnection _conn = null;
static PostgreSQLDataBase Instance = null;
string ConnectionString = null;
private PostgreSQLDataBase()
{
    try
    {
        ConnectionString = ConfigurationManager.
            ConnectionStrings["myinvoices"].ConnectionString;
    }
    catch (Exception ex)
    {
        Logger.LogWriteError(ex.Message);
        throw ex;
    }
}
private static void CreateInstance()
{
    if (Instance == null)
    { Instance = new PostgreSQLDataBase(); }
}
public static PostgreSQLDataBase GetInstance()
{
    if (Instance == null)
        CreateInstance();
    return Instance;
}
public NpgsqlConnection GetConnection()
{
    try
    {
        _conn = new NpgsqlConnection(ConnectionString);
        _conn.Open();
        return _conn;
    }
    catch (Exception ex)
    {
        Logger.LogWriteError(ex.Message);
        throw ex;
    }
}
}
}

El código de la clase Product

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace TestRadComboBox
{
public class Product
{
    public int Product_ID { set; get; }
    public string ProductCode { set; get; }
    public string ProductName { set; get; }
}
}

El código de la clase ProductFilters

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace TestRadComboBox
{
public struct ProductFilters
{
    public string ProductName { set; get; }

}
}

El código de la página ASP.NET Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ Import Namespace="TestRadComboBox" %>
<%@ Register assembly="Telerik.Web.UI" 
namespace="Telerik.Web.UI" 
tagprefix="telerik" %>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<script runat="server">

    protected void cmbProducts_OnItemsRequested(object sender,
        RadComboBoxItemsRequestedEventArgs args) {
        DataManager dm = new DataManager();
        ProductFilters filter = new ProductFilters { 
            ProductName = args.Text
        };

        if (cmbProducts.DataSource == null)
        {
            cmbProducts.DataSource = dm.GetProducts(filter);
            cmbProducts.DataValueField = "Product_ID";
            cmbProducts.DataTextField = "ProductName";
            cmbProducts.DataBind();
        }
    }
</script>
<body>
<form id="form1" runat="server">
<telerik:RadScriptManager ID="RadScriptManager1" runat="server">
    <Scripts>
<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.Core.js">
</asp:ScriptReference>
<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQuery.js">
</asp:ScriptReference>
<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQueryInclude.js">
</asp:ScriptReference>
</Scripts>
</telerik:RadScriptManager>
<div>
Please select a product:
    <telerik:RadComboBox ID="cmbProducts" 
    DropDownWidth="333px"
    OnItemsRequested="cmbProducts_OnItemsRequested"
    EnableLoadOnDemand="true"
    Runat="server">
    </telerik:RadComboBox>
</div>
</form>
</body>
</html>

La Carga de items bajo demanda
Una de las capacidades más utilizadas del control RadComboBox es la capacidad de cargar las opciones o items bajo demanda es decir construir la consulta en base a las coincidencias de los items y lo teclado por el usuario, esta capacidad se programa con el metódo cmbProducts_OnItemsRequested

protected void cmbProducts_OnItemsRequested(object sender,
        RadComboBoxItemsRequestedEventArgs args) {
        DataManager dm = new DataManager();
        ProductFilters filter = new ProductFilters { 
            ProductName = args.Text
        };

        if (cmbProducts.DataSource == null)
        {
            cmbProducts.DataSource = dm.GetProducts(filter);
            cmbProducts.DataValueField = "Product_ID";
            cmbProducts.DataTextField = "ProductName";
            cmbProducts.DataBind();
        }
    }

En este metódo se contruye una clase ProductFilter que contine el texto tecleado por el usuario en el RadComboBox, una vez creada esta clase se pasa como argumento al metódo GetProducts de la clase DataManager dentro de este metódo se construye la consulta SQL que filtra mediante un comando LIKE la consulta de la tabla Products hacia la lista (List) que sirve de DataSource para el RadComboBox.

//Here we past the filter in order to build the SQL Query
public List GetProducts(ProductFilters filters) {
    StringBuilder commandText = new StringBuilder("SELECT product_id, ")
    .Append("product_code, ")
    .Append("product_name ")
    .Append("FROM Products ")
    .AppendFormat("WHERE product_name Like '%{0}%'",filters.ProductName.ToUpper());
    List productList = null;
    Product p = null;
    using(NpgsqlDataReader reader = PostgreSQLCommand.GetReader(commandText.ToString(),
        null,
        CommandType.Text)){
        productList = new List();
        while (reader.Read()) {
            p = new Product();
            p.Product_ID = Convert.ToInt32(reader["product_id"]);
            p.ProductCode = reader["product_code"].ToString();
            p.ProductName = reader["product_name"].ToString();
            productList.Add(p);
        } 
    }
    return productList;
}

Al ejecutar la página vemos el resultado como en las siguientes imágenes:

Se realiza la búsqueda de las coincidencias entre lo tecleado por el usuario y el listado de productos

Es característica mejora el rendimiento de los formularios.

Uso de un template para los items.

Uno de los aspectos visuales más llamativos de este control Telerik es el utilizar un template para personalizar la aparencia de la lista de elementos seleccionables, como ejemplo en la siguiente página Default2.aspx utilizamos la misma funcionalidad de la página Default.aspx con la diferencia de utilizar una tabla como template para los items.

El código de la página ASP.NET Default2.aspx que muestra el uso de un template para los items.

<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ Import Namespace="TestRadComboBox" %>
<%@ Register assembly="Telerik.Web.UI" namespace="Telerik.Web.UI" 
tagprefix="telerik" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<script runat="server">


protected void cmbProducts_OnItemsRequested(object sender,
    RadComboBoxItemsRequestedEventArgs args) {
    DataManager dm = new DataManager();
    ProductFilters filter = new ProductFilters { 
        ProductName = args.Text
    };

    if (cmbProducts.DataSource == null)
    {
        cmbProducts.DataSource = dm.GetProducts(filter);
        cmbProducts.DataValueField = "Product_ID";
        cmbProducts.DataTextField = "ProductName";
        cmbProducts.DataBind();
    }
}
</script>
<body>
<form id="form1" runat="server">
<telerik:RadScriptManager ID="RadScriptManager1" runat="server">
<Scripts>
<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.Core.js">
</asp:ScriptReference>
<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQuery.js">
</asp:ScriptReference>
<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQueryInclude.js">
</asp:ScriptReference>
</Scripts>
</telerik:RadScriptManager>
<div>
Please select a product:
<telerik:RadComboBox ID="cmbProducts" 
DropDownWidth="333px"
OnItemsRequested="cmbProducts_OnItemsRequested"
EnableLoadOnDemand="true"
HighlightTemplatedItems="true"
Runat="server">
<HeaderTemplate>
<table cellspacing="3" width="99%">
<tr>
<td width="44%">Code</td>
<td>Name</td>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table cellspacing="3" width="99%">
<tr>
<td width="44%"><%#DataBinder.Eval(Container.DataItem, "ProductCode")%></td>
<td><%#DataBinder.Eval(Container.DataItem, "ProductName")%></td>
</tr>
</table>
</ItemTemplate>
</telerik:RadComboBox>
</div>
</form>
</body>
</html>

Al ejecutar la página Default2.aspx, veremos el resultado como en la siguiente imágen:

Por último el fragmento de código del archivo Web.conf del ejemplo, en donde se pone la cadena de conexión.

  <connectionStrings>
    <add connectionString="Server=127.0.0.1;Port=5432;Database=myinvoices;User ID=postgres;Password=Pa$$W0rd" name="myinvoices" />
  </connectionStrings>

Utilizando refcursors (cursores de referencia) en PostgreSQL

Esta una entrada complementaria a un post anterior acerca del uso de cursores en PostgreSQL.

Después de que se han obtenido los registros de un cursor abierto con el comando FETCH entonces ya podemos asignar los valores de esos registro a ciertos tipos de variables, los tipos de variable que se utilizan para el trabajo con cursores son:

1. Mismo tipo de datos que cada una de las columnas obtenidas por el cursor.
2. TYPE
3. RECORD
4. ROWTYPE

Como ejemplo para el primer tipo de variable programaremos una función PL/SQL con refcursors,en PostgreSQL un refcursor (cursor de referencia) es un cursor que se construye en runtime(tiempo de ejecucción), como su nombre indica es la referencia a un cursor.
Usaremos el siguiente schema como parte del ejemplo:

Este schema representa una estructura básica para la contabilidad de pagos y documentos, en donde cada pago recibido debe ser aplicado a un documento y reducir su monto.

Consultamos la tabla invoices

Consultamos los pagos

La aplicación de un pago hacia un documento debe generar un registro en la tabla payments_docs así que el próposito del código PL/SQL como ejemplo es asociar un pago aplicado a una factura y actualizar su monto. Esta funcionalidad la creamos con el siguiente código PL/SQL, que consiste de dos funciones de apoyo getinvoice(), getpayment() y una función principal setpaymentdoc(p_inv_id numeric,p_pay_id numeric). Con esta función regresamos un cursor de referencia con los datos de una factura de la tabla invoices.

Similar a la función anterior pero ahora regresamos un pago de la tabla payments.

Ahora el código PL/SQL de la función principal la cuál establece la relación entre el pago y la factura y actualiza su balance.

Al ejecutar las funciones getinvoice() y getpayment(), ambas regresan un cursor de referencia.

Antes de ejecutar el procedimiento consultamos el pago y la factura de las cuales se obtendrán los datos para crear el registro en la tabla de relación payments_docs, como resultado de la ejecucción del procedimiento.

En la primera función getinvoice() observamos como el tipo de valor devuelto es un cursor de referencia, esto mediante la sentencia RETURNS refcursor AS

CREATE FUNCTION getinvoice(p_inv_id numeric) RETURNS refcursor AS 

Declaramos la variable con el tipo refcursor(cursor de referencia) para contener el cursor devuelto por la función

 v_cursor_inv refcursor;

El cursor devuelto lleva asociado las columnas de la consulta a la tabla invoices, la cuál se encuentra asociada al cursor.

OPEN v_cursor_inv FOR SELECT invoice_id,invoice_total FROM invoices 
 WHERE invoice_id = p_inv_id;

finalmente devolvemos la variable a la cual le asignamos el cursor.

return v_cursor_inv;

La función getpayment() es similar a la función getinvoice() lo que cambia es la consulta asociada a su cursor de referencia. En la función principal setpaymentdoc(p_inv_id numeric,p_pay_id numeric) declaramos las variables que almacenarán los cursores de referencia y las variables que almacenarán a las columnas que cada cursor tenga asociadas como parte de su consulta SQL.

DECLARE

resp int;
 v_payment refcursor;
 v_invoice refcursor;
 v_invoice_id int;
 v_invoice_total numeric(9,2);
 v_pay_id int;
 v_pay_amount numeric(9,2);
 v_balance numeric(11,2);

Ahora almacenamos los cursores devueltos por las funciones getinvoice() y getpayment() en las variables v_invoice y v_payment respectivamente.

v_invoice := getinvoice(p_inv_id);
v_payment := getpayment(p_pay_id);

Una vez guardado el cursor en la variable, podemos extraer los valores de las columnas y almacenarlos en su variable correspondiente, esto lo hacemos con el comando FETCH INTO.

FETCH [cursor name] INTO [variable1, variable2, ….n].

FETCH v_invoice INTO v_invoice_id,v_invoice_total;
 CLOSE v_invoice;

Es obligatorio que la variable que guarda el valor de la columna sea del mismo tipo de dato que la columna extraída del cursor. Por último insertamos la relación en la tabla utilizando los valores guardados en las variables, y regresando el número de secuencia.

v_balance := v_invoice_total - v_pay_amount;
 raise NOTICE 'balance : %',v_balance;
 INSERT INTO payments_docs(pay_id,inv_id,balance,date_created)
 VALUES(v_pay_id,v_invoice_id,v_balance,now());
 resp := currval('payments_docs_payments_docs_id_seq');

Al ejecutar el procedimiento con todos sus argumentos se mostrará el resultado como se ve en la siguiente imagen:

Ahora consultamos la tabla de relación, para ver el registro creado, resultado de la ejecución del procedimiento.

Cursores (Cursors) y Funciones (Functions) PLpg/SQL en PostgreSQL

Un cursor es el nombre que recibe un apuntador (pointer) de solo lectura hacia un conjunto de datos ( resultset) que se obtiene de una consulta SQL asociada, para los cursores pensemos en términos de arreglos similar a los arreglos de un lenguaje de programación, los cursores nos sirven para procesar una a una las filas que componen un conjunto de resultados en vez de trabajar con todos los registros como en una consulta tradicional de SQL.

Los cursores pueden declararse con una consulta SQL sin parámetros o con parámetros, en donde el tamaño del conjunto de datos depende del valor de los parámetros de la consulta. Así por ejemplo declaramos:

Los cursores pueden emplearse dentro de funciones PL/SQL para que las aplicaciones que accedan a PostgreSQL puedan utilizarlos más de una vez.

Como ejemplo tenemos una base de datos llamada myinvoices, que contiene las siguientes tablas:

invoices

invoicedetails

Ahora como ejemplo de un cursor utilizado dentro de una función, declaramos una función utilizando un cursor con parámetros para devolver el total de facturas emitidas dentro de un rango de fechas.

Existen dos errores comunes cuando trabajamos con cursores:

1. Si tratas de abrir un cursor que ya se encuentra abierto PostgreSQL enviará un mensaje de “cursor [name] already in use”
2. Si tratas de ejecutar FETCH en un cursor que no ha sido abierto, PostgreSQL enviará un mensaje de “cursor [name] is invalid”

Cuando utilizamos el comando FETCH obtenemos una por una las filas del conjunto de resultados después de cada fila procesada el cursor avanza a la siguiente fila y la fila procesada puede ser entonces utilizada dentro de una variable.

Consultamos los datos de la tabla invoices

Ejecutamos la función, usando los siguientes argumentos:

Obtenemos los siguientes resultados:

Entendiendo Transacciones (Transactions) con ADO .NET y PostgreSQL

En .NET las transacciones son representadas por la clase Transaction que implementa la interfaz IDbTransaction definida dentro del ensamblado System.Data esta interfaz proporciona los métodos:

Commit: Confirma la transacción y persiste los datos.

Rollback: Regresa los datos a un estado anterior a la transacción.

Esta interfaz se utiliza para crear clases Transaction asociadas con un proveedor especifico, así para SQL Server tenemos SqlTransaction, para Oracle OracleTransaction y para PostgreSQL NpgsqlTransaction.

La ventaja de crear transacciones en .NET y no en la bases de datos es proporcionar a la aplicaciones la capacidad de las transacciones en caso de utilizar una base de datos que no proporcione o soporte esa característica.
Como ejemplo escribimos un programa en MonoDevelop que utiliza las tablas: Invoices e InvoiceDetails que se utilizaron en esta entrada este programa utiliza una transaction y 4 comandos SQL con los que guarda una factura (invoice) con dos detalles (invoice details), actualizando el total de la factura conforme a la cantidad de productos y su precio.

using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using NpgsqlTypes;
using System.Data;
using System.Globalization;


namespace MonoTransExamples
 {
  class Program
  {
  static string connString = "Server=127.0.0.1;Port=5432;Database=myinvoices;
User ID=postgres;Password=Pa$$W0rd";

  static string commandText1 = "INSERT INTO invoices(invoice_number,invoice_date,
invoice_total)" + "VALUES(:number,:date,:total)";

  static string commandText2 = "SELECT MAX(invoice_id) FROM invoices";
  static string commandText3 = "INSERT INTO invoicedetails
(invoice_id,invoiced_description,invoiced_quantity,invoiced_amount)" +
"VALUES(:id,:description,:quantity,:amount)";

static string commandText4 = "UPDATE invoices SET invoice_total =
 :total WHERE invoice_id = :id";
static void Main(string[] args)
{
    bool success = false;
    int recordsAffected = 0;
    Invoice invoice = new Invoice { 
        Invoice_number = 2099,
        Invoice_date = new DateTime(2013,01,29,6,6,6,100,Calendar.CurrentEra)
    };
    Invoicedetails[] details = { 
    new Invoicedetails{
        Invoice = invoice,
        Invoiced_description = "walkie-talkie 22-Channel",
        Invoiced_quantity = 3,
        Invoiced_amount = 19.99M
    },
    new Invoicedetails{
        Invoice = invoice,
        Invoiced_description = "2 GB SD Memory Card",
        Invoiced_quantity = 4,
        Invoiced_amount = 6.99M
    }
    };
    NpgsqlTransaction transaction = null;
    NpgsqlConnection conn = null;
    try
    {
    conn = new NpgsqlConnection(connString);
    conn.Open();
    transaction = conn.BeginTransaction();
using (NpgsqlCommand cmd1 = new NpgsqlCommand(commandText1, conn, transaction))
{
cmd1.CommandType = CommandType.Text;
cmd1.Parameters.Add("number", NpgsqlDbType.Integer, 4).Value = invoice.Invoice_number;
cmd1.Parameters.Add("date", NpgsqlDbType.Timestamp).Value = invoice.Invoice_date;
cmd1.Parameters.Add("total", NpgsqlDbType.Money).Value = invoice.Invoice_total;
      recordsAffected = cmd1.ExecuteNonQuery();
        }
        Console.WriteLine("{0} invoiced inserted",recordsAffected);
        if (recordsAffected > 0)
        {
 using (NpgsqlCommand cmd2 = new NpgsqlCommand(commandText2, conn, transaction))
            {
       cmd2.CommandType = CommandType.Text;
       invoice.Invoice_id = Convert.ToInt32(cmd2.ExecuteScalar());
            }
        Console.WriteLine("Invoice Id {0} ",invoice.Invoice_id);
        }
        if (invoice.Invoice_id > 0)
        {
        recordsAffected = 0;
        foreach (Invoicedetails invd in details)
        {
        invd.Invoice.Invoice_id = invoice.Invoice_id;
         using (NpgsqlCommand cmd3 = new NpgsqlCommand(commandText3, conn, transaction))
         {
 cmd3.CommandType = CommandType.Text;
 cmd3.Parameters.Add("id", NpgsqlDbType.Integer, 4).Value = invd.Invoice.Invoice_id;
 cmd3.Parameters.Add("description", NpgsqlDbType.Varchar, 512).Value = invd.Invoiced_description;
 cmd3.Parameters.Add("quantity", NpgsqlDbType.Smallint).Value = invd.Invoiced_quantity;
 cmd3.Parameters.Add("amount", NpgsqlDbType.Money).Value = invd.Invoiced_amount;
        recordsAffected += cmd3.ExecuteNonQuery();
                }
        invoice.Invoice_total += invd.Invoiced_amount * invd.Invoiced_quantity;
            }
Console.WriteLine("Total: {0} ,{1} records affected ",invoice.Invoice_total,recordsAffected);
        }
        if (recordsAffected == details.Length)
        {
      using (NpgsqlCommand cmd4 = new NpgsqlCommand(commandText4, conn, transaction)) {
 cmd4.CommandType = CommandType.Text;
 cmd4.Parameters.Add("total",NpgsqlDbType.Money).Value = invoice.Invoice_total;
 cmd4.Parameters.Add("id",NpgsqlDbType.Integer).Value = invoice.Invoice_id;
      recordsAffected = cmd4.ExecuteNonQuery();
            }
Console.WriteLine("Updated invoice {0} with total {1} ",
invoice.Invoice_id,invoice.Invoice_total);
        }
        if(recordsAffected > 0)
                success = true;
    }catch(NpgsqlException ex){
        Console.WriteLine("Error {0} ", ex.Message);
    }finally{
        if (success)
            transaction.Commit();
        else
            transaction.Rollback();
        if (conn != null)
            if (conn.State == ConnectionState.Open)
                conn.Close();
    }
    Console.WriteLine("Done");
    Console.ReadLine();
}
}

    class Invoice {
    public int Invoice_id { set; get; }
    public int Invoice_number { set; get; }
    public DateTime Invoice_date { set; get; }
    public Decimal Invoice_total { set; get; }

    }

    class Invoicedetails {
    public int Invoiced_id { set; get; }
    public Invoice Invoice { set; get; }
    public string Invoiced_description { set; get; }
    public int Invoiced_quantity { set; get; }
    public Decimal Invoiced_amount { set; get; }
    }
}

Este programa asocia una transacción con una conexión abierta.

conn = new NpgsqlConnection(connString);
conn.Open();
transaction = conn.BeginTransaction();

Se crea cada uno de los comandos SQL dentro del alcance de la transacción

using (NpgsqlCommand cmd1 = new NpgsqlCommand(commandText1, conn, transaction))
using (NpgsqlCommand cmd2 = new NpgsqlCommand(commandText2, conn, transaction))
using (NpgsqlCommand cmd3 = new NpgsqlCommand(commandText3, conn, transaction))
using (NpgsqlCommand cmd4 = new NpgsqlCommand(commandText4, conn, transaction)) 

Si todos los comandos se ejecutan correctamente se llama al método Commit() de lo contrario se llama al método Rollback y se cierra la conexión.

  if (success)
            transaction.Commit();
        else
            transaction.Rollback();
        if (conn != null)
            if (conn.State == ConnectionState.Open)
                conn.Close();

Es importante recordar que la transacción queda pendiente hasta que no se confirme (commit) o se cancele (rollback), si se cierra la conexión mediante el método Close se ejecuta un rollback en todas las transacciones pendientes.

Entendiendo Transacciones con PostgreSQL.

Hay operaciones en los sistemas de bases de datos (DBMS) que no pueden expresarse como una única operación SQL sino como el resultado de un conjunto de dos o más operaciones SQL, cuyo éxito depende de que cada una de esas operaciones se ejecute correctamente ya que si una de ellas falla se considera que toda la operación fallo.
El control de transacciones es una característica fundamental de cualquier DBMS (como PostgreSQL,MS SQL Server ú Oracle) esto permite agrupar un conjunto de operaciones o enunciados SQL en una misma unidad de trabajo discreta , cuyo resultado no puede ser divisible ya que solo se considera el total de operaciones completadas, si hay una ejecución parcial el DBMS se encarga de revertir esos cambios para dejar la información consistente.

Una transacción tiene cuatro características esenciales conocidas como el acrónimo ACID:

• Atomicity(Atomicidad): Una transacción es una unidad atómica o se ejecutan las operaciones múltiples por completo o no se ejecuta absolutamente nada, cualquier cambio parcial es revertido para asegurar la consistencia en la base de datos.
• Consistency (Consistencia): Cuando finaliza una transacción debe dejar todos los datos sin ningún tipo de inconsistencia, por lo que todas las reglas de integridad deben ser aplicadas a todos los cambios realizados por la transacción, o sea todas las estructuras de datos internas deben de estar en un estado consistente.
• Isolation (Aislamiento o independencia): Esto significa que los cambios de cada transacción son independientes de los cambios de otras transacciones que se ejecuten en ese instante, o sea que los datos afectados de una transacción no están disponibles para otras transacciones sino hasta que la transacción que los ocupa finalice por completo.
• Durability (Permanencia): Después de que las transacciones hayan terminado, todos los cambios realizados son permanentes en la base de datos incluso si después hay una caída del DBMS.

Las transacciones en PostgreSQL utilizan las siguientes palabras reservadas:

BEGIN: Empieza la transacción

SAVEPOINT [name]: Le dice al DBMS la localización de un punto de retorno en la transacción si una parte de la transacción es cancelada. El DBMS guarda el estado de la transacción hasta este punto.

COMMIT: Todos los cambios realizados por las transacciones deben ser permanentes y accesibles a las demás operaciones del DBMS.

ROLLBACK [savepoint]: Aborta la actual transacción todos los cambios realizados deben ser revertidos.

Para poner en práctica estos comandos utilizaremos dos tablas relacionadas Invoices y InvoiceDetails.

Agregamos un par de registros, cada uno dentro de una transacción en el primer registro el commit (confirmación) se realiza de forma automática al terminar la transacción con el comando END.

En el segundo registro utilizamos el comando COMMIT de forma explicita para hacer los cambios permanentes.

Ahora insertamos un nuevo registro y eliminamos un par pero en vez de confirmar la transacción con COMMIT deshacemos los cambios y regresamos los registros a su estado original, utilizando ROLLBACK.

Aquí otro ejemplo del uso de ROLLBACK.

En el siguiente bloque de PL/SQL anónimo vamos a utilizar los comandos anteriores además del comando SAVEPOINT el cuál permite deshacer parcialmente los cambios hechos dentro de una transacción y no toda la transacción por completo.
Persistimos entonces solo los cambios antes del SAVEPOINT, los cambios realizados después serán revertidos por el comando ROLLBACK.

Nuevo número de la revista ATIX de divulgación del software libre (número 21)

Ya se encuentra en línea, el nuevo número de la revista Atix de divulgación del software libre.

Los mirrors para la descarga gratuita se encuentran en este enlace.

El código fuente del tutorial de la revista se publico en la revista se encuentra en este post.

Trabajando con Binary Large Object (BLOB) en PostgreSQL con GTK# y MonoDevelop II

En el número 20 de la revista Atix se publicó acerca de como leer y escribir datos binarios en PostgreSQL con C#, ahora en este documento complementamos la información anterior mostrando un programa en GTK# que hace uso de los mismos conceptos salvo que ahora los integra con las capacidades ofrecidas por Monodevelop para la creación de formularios GTK#.

Fig. 1 Monodevelop proyecto GTK#

Fig. 2 Diseño del formulario

Fig. 3 Muestra de la solución

Fig. 4 La aplicación ejecutándose

Fig. 5 Consulta de un registro

Fig. 6 Alta de un registro