Transacciones, try catch, esquemas y tipo de dato tabla en Sql Server 2008 , Ejemplo de Uso desarrollando un aplicativo 4 capas con Visual Studio 2010

Bueno. en esta oportunidad desarrollare un aplicativo de ejemplo en la cual aprenderemos de una manera dinámica lo siguiente :

1. Creación de la base de datos Ventas.
2. Creación de los Esquemas(Facturación, Globales, Inventario) en la cual clasificaremos nuestras tablas.
3. Creación de las tablas Vendedores(Globales), Clientes(Globales), Boleta(Facturacion), DetalleBoleta(Facturacion), Productos(Inventario).
4. Creación del tipo de dato tabla (Nuevo en SQL SERVER 2008) que nos ayudara en la inserción de la cabecera y detalle de la boleta. El uso lo veremos en el punto 6.
5. Creación de los procedimientos almacenados de inserción(ins), actualización(upd), eliminación (del).
6. Creación del procedimiento almacenado que contendrá la transacción (Begin Tran) la cual  insertara la cabecera y el detalle de la boleta usando el try catch (Nuevo en SQL SERVER 2008) como manejo de excepciones con la finalidad de detectar el error y revertir la operación.
7. Creación de la aplicación Con vb.net en 4 capas, ejemplo con el  Mantenimiento de Vendedor, Boletas.
enviaremos el detalle de la boleta en un tipo de dato tabla desde .Net. con la finalidad de que la transacción este del lado del servidor Sql Server y no del lado del aplicativo.

Desarrollo

1) Creación de la base de datos Ventas.

create database Ventas

• Esto es una manera simple de crear una base de datos, debemos tener en-cuenta que hemos obviado planear el crecimiento auto matico (autogrowth), la intercalación (Collation), etc. esto con la finalidad de mejorar la performance.

2) Creación de los Esquemas(Facturación, Globales, Inventario) en la cual clasificaremos nuestras tablas.

use Ventas
go
create schema Facturacion
go

create schema Globales

go

create schema Inventario

• La creación de estos esquemas nos permitirá clasificar nuestras tablas, pero también permite clasificar, procedimientos almacenados, vistas, etc. 
• Otro de los beneficios que nos brinda los esquemas es que hace flexible la seguridad (Permisos).

3) Creación de las tablas Vendedores(Globales), Clientes(Globales), Boleta(Facturacion), DetalleBoleta(Facturacion), Productos(Inventario).

• Si creamos una tabla Vendedores con la siguiente sentencia CREATE TABLE Vendedores ......., SQL SERVER lo incluirá automáticamente en la esquema por defecto que es el dbo. Entonces la sentencia seria similar a esto CREATE TABLE dbo.Vendedores....
• Pero si a la sentencia  CREATE TABLE dbo.Vendedores ...  remplazamos solo dbo por alguna esquema que ya hemos creado como Globales esto quedaría así: CREATE TABLE Globales.Vendedores ....

use Ventas

-- Vendedor


CREATE TABLE Globales.Vendedores(
IdVendedor int IDENTITY(1,1) NOT NULL,
NombreVendedor varchar(100) ,
Documento varchar(50) ,
UsuarioCrea varchar(250) ,
FechaCrea datetime,
UsuarioModif varchar(250) ,
FechaModif datetime ,
Estado bit ,
 CONSTRAINT PK_Vendedores PRIMARY KEY CLUSTERED
(
IdVendedor ASC
)
)

GO


ALTER TABLE Globales.Vendedores ADD  CONSTRAINT DF_Vendedores_FechaCrea  DEFAULT (getdate()) FOR FechaCrea
GO

ALTER TABLE Globales.Vendedores ADD  CONSTRAINT DF_Vendedores_Estado  DEFAULT ((1)) FOR Estado
GO

-- Cliente

CREATE TABLE Globales.Clientes(
IdCliente int IDENTITY(1,1) NOT NULL,
IdVendedor int NOT NULL,
NombreCliente varchar(100) ,
Documento varchar(50) ,
UsuarioCrea varchar(250) ,
FechaCrea datetime ,
UsuarioModif varchar(250) ,
FechaModif datetime ,
Estado bit ,
 CONSTRAINT PK_Clientes PRIMARY KEY CLUSTERED
(
IdCliente ASC
)
)

GO

ALTER TABLE Globales.Clientes  WITH CHECK ADD  CONSTRAINT FK_Clientes_Vendedores FOREIGN KEY(IdVendedor)
REFERENCES Globales.Vendedores (IdVendedor)
GO

ALTER TABLE Globales.Clientes CHECK CONSTRAINT FK_Clientes_Vendedores
GO

ALTER TABLE Globales.Clientes ADD  CONSTRAINT DF_Clientes_Estado  DEFAULT ((1)) FOR Estado
GO

-- Productos


CREATE TABLE Inventario.Productos(
IdProducto int IDENTITY(1,1) NOT NULL,
NombreProducto varchar(100) ,
UnidadMedida varchar(100) ,
Precio decimal(18, 2) ,
UsuarioCrea varchar(250) ,
FechaCrea datetime ,
UsuarioModif varchar(250) ,
FechaModif datetime ,
Estado bit ,
 CONSTRAINT PK_Productos PRIMARY KEY CLUSTERED
(
IdProducto ASC
)
)

GO

ALTER TABLE Inventario.Productos ADD  CONSTRAINT DF_Productos_FechaCrea  DEFAULT (getdate()) FOR FechaCrea
GO

ALTER TABLE Inventario.Productos ADD  CONSTRAINT DF_Productos_Estado  DEFAULT (1) FOR Estado
GO

-- Boleta

CREATE TABLE Facturacion.Boletas(
IdBoleta int IDENTITY(1,1) NOT NULL,
Serie int ,
Numero int ,
IdCliente int ,
NombreCliente varchar(50) ,
IdVendedor int ,
NombreVendedor varchar(50) ,
SubTotal decimal(18, 2) ,
Igv decimal(18, 2) ,
Total decimal(18, 2) ,
UsuarioCrea varchar(250) ,
FechaCrea datetime ,
UsuarioModif varchar(250) ,
FechaModif datetime ,
Estado bit ,
 CONSTRAINT PK__Boletas PRIMARY KEY CLUSTERED
(
IdBoleta ASC
)
)

GO

ALTER TABLE Facturacion.Boletas  WITH CHECK ADD  CONSTRAINT FK_Boletas_Clientes FOREIGN KEY(IdCliente)
REFERENCES Globales.Clientes (IdCliente)
GO

ALTER TABLE Facturacion.Boletas CHECK CONSTRAINT FK_Boletas_Clientes
GO

ALTER TABLE Facturacion.Boletas  WITH CHECK ADD  CONSTRAINT FK_Boletas_Vendedores FOREIGN KEY(IdVendedor)
REFERENCES Globales.Vendedores (IdVendedor)
GO

ALTER TABLE Facturacion.Boletas CHECK CONSTRAINT FK_Boletas_Vendedores
GO

ALTER TABLE Facturacion.Boletas ADD  CONSTRAINT DF_Boletas_Estado  DEFAULT (1) FOR Estado
GO

-- Dellate de la boleta

CREATE TABLE Facturacion.DetalleBoleta(
IdDetalleBoleta int IDENTITY(1,1) NOT NULL,
IdBoleta int ,
IdProducto int ,
NombreProducto varchar(50) ,
Cantidad decimal(18, 2) ,
PrecioUnitario decimal(18, 2) ,
SubTotal decimal(18, 2) ,
igv decimal(18, 2) ,
Total decimal(18, 2) ,
 CONSTRAINT PK__DetalleBoleta PRIMARY KEY CLUSTERED
(
IdDetalleBoleta ASC
)
)

GO

ALTER TABLE Facturacion.DetalleBoleta  WITH CHECK ADD  CONSTRAINT FK_DetalleBoleta_Boletas FOREIGN KEY(IdBoleta)
REFERENCES Facturacion.Boletas (IdBoleta)
GO

ALTER TABLE Facturacion.DetalleBoleta CHECK CONSTRAINT FK_DetalleBoleta_Boletas
GO

ALTER TABLE Facturacion.DetalleBoleta  WITH CHECK ADD  CONSTRAINT FK_DetalleBoleta_Productos FOREIGN KEY(IdProducto)
REFERENCES Inventario.Productos (IdProducto)
GO

ALTER TABLE Facturacion.DetalleBoleta CHECK CONSTRAINT FK_DetalleBoleta_Productos
GO


4. Creación del tipo de dato tabla para la insercion de la cabecera y detalle de la boleta.

• Una novedad que trajo el SQL SERVER 2008 es el tipo de dato tabla, la cual nos permitirá manejar múltiples registros dentro de una sola variable. como por ejemplo el detalle de una boleta.

use Ventas

GO


CREATE TYPE dbo.DetalleBoletaType AS TABLE(
IdProducto int ,
NombreProducto varchar(50) ,
Cantidad decimal(18, 2) ,
PrecioUnitario decimal(18, 2) ,
SubTotal decimal(18, 2) ,
igv decimal(18, 2) ,
Total decimal(18, 2)
)

5. Creación de los procedimientos almacenados de inserción(ins), actualización(upd), eliminación (del).

• Para identificar la acción de algún procedimiento almacenado, usamos prefijos las cuales serán ins para insertar, upd actualizar, del Eliminar (Esta eliminación es lógica por cuestiones de auditoria) y sel para las consultas.

create proc insVendedor

@NombreVendedor varchar(100), @Documento varchar(50)

as

insert into Globales.Vendedores

(NombreVendedor, Documento, UsuarioCrea, FechaCrea)

values(@NombreVendedor,@Documento,HOST_NAME(),GETDATE())

go

create proc updVendedor

@IdVendedor int, @NombreVendedor varchar(100), @Documento varchar(50)

as

update Globales.Vendedores

set NombreVendedor=@NombreVendedor,

Documento=@Documento,UsuarioModif=HOST_NAME(), FechaModif=getdate()

where IdVendedor=@IdVendedor

go

create proc delVendedor

@IdVendedor int

as

update Globales.Vendedores

set estado=0

where IdVendedor=@IdVendedor

go

create proc selVendedor

as

select IdVendedor,NombreVendedor,Documento,Estado from 

Globales.Vendedores

where Estado=1 

go

6. Creación del procedimiento almacenado que contendrá la transacción la cual  insertara la cabecera y el detalle de la boleta usando el try catch.

• Una de las novedades que trajo SQL SERVER 2008 es el manejo de errores mediante el Try catch. Esto nos permitirá mejorar el flujo de nuestra transacción.
• Aquí observaremos el uso del tipo de dato tabla en nuestra transacción.

CREATE PROCEDURE dbo.insBoleta

   @IdCliente int, @NombreCliente varchar(50), 

   @IdVendedor int, @NombreVendedor varchar(50),    

   @SubTotal decimal(18,2), @Igv decimal(18,2), @Total decimal(18,2),

   @DetalleBoleta dbo.DetalleBoletaType READONLY

as

BEGIN TRY

begin tran

insert into Facturacion.Boletas

(IdCliente, NombreCliente, IdVendedor, NombreVendedor, 

SubTotal, Igv, Total,UsuarioCrea,FechaCrea)

values

(@IdCliente, @NombreCliente, @IdVendedor, @NombreVendedor, 

@SubTotal, @Igv, @Total,HOST_NAME(),GETDATE())

insert into Facturacion.DetalleBoleta

(IdBoleta, IdProducto, NombreProducto, Cantidad, PrecioUnitario, SubTotal, igv, Total)

select @@IDENTITY, db.IdProducto, 

db.NombreProducto, db.Cantidad, db.PrecioUnitario, db.SubTotal, db.igv, db.Total from @DetalleBoleta db

commit tran

END TRY

BEGIN CATCH

ROLLBACK TRAN

END CATCH

7. Creación de la aplicación Con vb.net en 4 capas, ejemplo con el  Mantenimiento de Vendedor, Boletas. Enviaremos el detalle de la boleta en un tipo de dato tabla desde .Net. con la finalidad de que la transacción ya no estará en la aplicación sino en el servidor Sql Server.

• Creación de la solución que contendrá los cuatro proyectos (cuatro capas), La capa de Entidad, La Capa Datos, La Capa de Negocios, La Capa Cliente.

• Agregamos los proyectos a la solución, dando click derecho a la solución, agregar --> nuevo proyecto. Capa entidad.

• Re-nombramos la clase Class1 por vendedor, y luego ingresamos los siguientes atributos de la entidad vendedor.

• Ahora creamos la capa de datos la cual va hacer referencia a la capa entidad. La capa de datos, no es mas ni menos que un proyectos en la solución la cual contendrá clases que permitirá la conexión a la base de datos.

• Hacemos referencia la capa de entidad

• Re-nombramos la clase Class1 por vendedor en la capa de datos, y luego ingresamos los métodos, las cuales son las funciones y procedimientos.
• Función Insertar Vendedor

• Función Actualizar Vendedor

• Función Eliminar Vendedor

• Función Mostrar Vendedor (Aquí mostramos un ejemplo de polimorfismo)

• Procederemos con la creación de la capa de negocio y la implementación de la clase vendedor.

• Ahora crearemos la interface de cliente en la cual mostraremos el mantenimiento del vendedor

•  en este ejemplo mostraremos el uso de la inserción y mostrar el vendedor.

• Ahora mostraremos el uso de la transacción del lado del servidor, usando el tipo de dato tabla.
• capa cliente

• capa entidad (observamos del tipo de dato tabla al final del código fuente)

Public Class Boleta

    Private m_Serie As String

    Public Property Serie() As String

        Get

            Return m_Serie

        End Get

        Set(ByVal value As String)

            m_Serie = value

        End Set

    End Property

    Private m_Numero As String

    Public Property Numero() As String

        Get

            Return m_Numero

        End Get

        Set(ByVal value As String)

            m_Numero = value

        End Set

    End Property

    Private m_IdCliente As Integer

    Public Property IdCliente() As Integer

        Get

            Return m_IdCliente

        End Get

        Set(ByVal value As Integer)

            m_IdCliente = value

        End Set

    End Property

    Private m_NombreCliente As String

    Public Property NombreCliente() As String

        Get

            Return m_NombreCliente

        End Get

        Set(ByVal value As String)

            m_NombreCliente = value

        End Set

    End Property

    Private m_IdVendedor As Integer

    Public Property IdVendedor() As Integer

        Get

            Return m_IdVendedor

        End Get

        Set(ByVal value As Integer)

            m_IdVendedor = value

        End Set

    End Property

    Private m_NombreVendedor As String

    Public Property NombreVendedor() As String

        Get

            Return m_NombreVendedor

        End Get

        Set(ByVal value As String)

            m_NombreVendedor = value

        End Set

    End Property

    Private m_SubTotal As Decimal

    Public Property SubTotal() As Decimal

        Get

            Return m_SubTotal

        End Get

        Set(ByVal value As Decimal)

            m_SubTotal = value

        End Set

    End Property

    Private m_Igv As Decimal

    Public Property Igv() As Decimal

        Get

            Return m_Igv

        End Get

        Set(ByVal value As Decimal)

            m_Igv = value

        End Set

    End Property

    Private m_Total As Decimal

    Public Property Total() As Decimal

        Get

            Return m_Total

        End Get

        Set(ByVal value As Decimal)

            m_Total = value

        End Set

    End Property

    Private m_DetalleBoleta As DataTable

    Public Property DetalleBoleta() As DataTable

        Get

            Return m_DetalleBoleta

        End Get

        Set(ByVal value As DataTable)

            m_DetalleBoleta = value

        End Set

    End Property

End Class

• Capa de datos