Creación de Capa de Acceso a Datos

Introducción

Se creará una arquitectura de software compuesta de una DAL (Capa de Acceso a Datos) usando DataSets y una capa de presentación compuesta por páginas ASP.NET que compartan un diseño similar.

Para ésto usaremos la base de datos Norhwind que se almacenará en el directorio App_Data, que además de poder almacenar archivos de bases de datos, también puede contener scripts de SQL para crear la base de datos.

1: Creación de un proyecto Web y conexión a la Base de Datos Creamos un Nuevo sitio web ASP.NET, seleccionando la ubicación para guardarlo y escogiendo como lenguaje C#

Figura 1:Creación de nuevo Sitio Web

El siguiente paso es crear una referencia a la base de datos en el Server Explorer de Visual Studio. Con esto podremos agregar tablas, procedimientos almacenados, vistas, etc. además de poder analizar datos de las tablas o crear consultas personalizadas directamente o mediante el Generador de Consultas (Query Builder). Además, cuando construimos Data

Uso de la Base de Datos en la carpeta App_Data

A database placed in the App_Data folder is automatically added to the Server Explorer. Assuming you have SQL Server 2005 Express Edition installed on your machine you should see a node named NORTHWND.MDF in the Server Explorer, which you can expand and explore its tables, views, stored procedure, and so on (see Figure 2).

The App_Data folder can also hold Microsoft Access .mdb files, which, like their SQL Server counterparts, are automatically added to the Server Explorer. If you don't want to use any of the SQL Server options, you can always download a Microsoft Access version of the Northwind database file and drop into the App_Data directory. Keep in mind, however, that Access databases aren't as feature-rich as SQL Server, and aren't designed to be used in web site scenarios. Furthermore, a couple of the 35+ tutorials will utilize certain database-level features that aren't supported by Access.

Connecting to the Database in a Microsoft SQL Server 2000 or 2005 Database Server Una vez instalada la Base de Datos, dando click derecho en el Server Explorer ( si no está visible, ir a Ver/Server Explorer o presionar Ctrl+Alt+S ), se selecciona Agregar Conexión. Se especifica el servidor a conectar, y nombre de la base de datos. Una vez configurado esto, se agrega la base de datos como un nodo debajo de Conecciones de Datos. Se puede expandir el nodo para revisar la tablas, vistas, etc

Figura 2: Conexión de Northwind Agregada

2: Creación de la Capa de Acceso a Datos Al trabajar con datos, una opción es incrustar la lógica específica de datos directamente dentro de la capa de presentación. Sin embargo, lo recomendable es separar la lógica de acceso a datos de la capa de presentación.

Esta capa separada se conoce como DAL, que se implementa típicamente como un proyecto de Biblioteca de Clases. En principio, esta arquitectura de capas nos permite manejar de forma aislada los elementos relacionados con una u otra sin afectar ambas

Todo el código específico al origen de datos, relacionado con los comandos SELECT, INSERT, UPDATE, y DELETE debe ubicarse en la DAL. La capa de presentación sólo debe hacer llamadas a la DAL para cualquier petición de datos.

La BD Nortwind, por ejemplo, contiene las tablas Products y Categories que guardan los productos en venta y sus categorías relacionadas. En la DAL tendremos métodos como:

• GetCategories(), que regresará información de todas las GetProducts(), which will return information about all of the products

• GetProductsByCategoryID(categoryID), que regresará todos los productos pertenecientes a una categoría en específico

• GetProductByProductID(productID), que regresará información de un product en particular

Estos métodos pueden regresar simplemente un DataSet o DataReader conteniendo los datos generados por la consulta, pero idealmente deben contener objetos fuertemente tipados (strongly-typed objects). Éstos son aquellos cuyo esquema está definido desde el tiempo de compilación.

Por ejemplo, el DataReader y el DataSet no son por default objetos de este tipo ya que su esquema se define por las columnas que regresa la consulta que envía los datos

La figrua 3 ilustra el flujo de trabajo entre las diferentes capas de una aplicación con Data Set

Figura 3: Todo el código de Acceso a Datos se relega a la

Creación del DataSet y el Table Adapter Primero agregamos un DataSet al proyecto, para esto, dar click derecho en en nodo del proyecto y seleccionar Agregar Nuevo Elemento. Seleccionar la opción DataSet de la lista y nombrarlo como Northwind.xsd.

Figura 4: Agregar Nuevo DataSet

Aceptar para agregar la carpeta App_Code

La clase TableAdapter funcionará como la Capa de Acceso a Datos. Para este caso, contendrá los métodos GetProducts(), GetProductByCategoryID(categoryID), y demás que se invocarán desde la capa de presentación.El asistente pide seleccionar la BD a trabajar.

Figura 5 Seleccionar la Base de Datos Northwind

Figura 6: Guardar la Cadena de Conexión en el Web.Config

Figura 7: Seleccionar consulta SQL

Creamos una consulta que seleccione todos los registros y campos de la tabla Products:

Figura 8: Consulta SQL

De igual modo, se puede seleccionar el Generador de Consultas (Query Buider) y construir gráficamente la consulta:

Figura 9: Uso del Editor de Consultas

Seleccionar el botón de Opcioones Avanzadas y comprobar que esté seleccionada la opción de “Generate Insert, Update, and Delete statements" .

Figura 10: Seleccionar Generate Insert, Update, y Delete statements

Dar siguiente para ir a la pantalla final. Aquí se pide seleccionar los métodos para el TableAdapter

Figura 11: Cambio del nombre del método

Figura 12: El DataTable Products y ProductsTableAdapter

Los objetos agregados nos sirven para tener acceso a los datos con código tal como:

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new NorthwindTableAdapters.ProductsTableAdapter(); Northwind.ProductsDataTable products;

products = productsAdapter.GetProducts(); foreach (Northwind.ProductsRow productRow in products) Response.Write("Product: " + productRow.ProductName + "<br />");

Construir la siguiente página web que enlaza el DataTable generado por el método GetProducts() a un GridView dentro del evento Page_Load

AllProducts.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AllProducts.aspx.cs" Inherits="AllProducts" %> <!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>View All Products in a GridView</title> </head> <body> <form id="form1" runat="server"> <div> <h2> All Products</h2> <p> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> </p> </div> </form> </body> </html>

AllProducts.aspx.cs

using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using NorthwindTableAdapters; public partial class AllProducts : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { ProductsTableAdapter productsAdapter = new ProductsTableAdapter(); GridView1.DataSource = productsAdapter.GetProducts(); GridView1.DataBind(); }

}

Figura 13: Lista de Productos

3: Añadir Métodos PArametrizados a la DAL Crearemos el método GetProductsByCategoryID(categoryID). Para ésto, ir al diseñador de DataSet Designer, click derecho en la sección ProductsTableAdapter y seleccionar Add Query.

Figura 14: Click derecho en el TableAdapter y seleccionar AddQuery

Figura 15: Escoger crear una sentencia SELECT que Regrese Filas

Figura 16: Agregar la línea de consulta parametrizada

In the final step we can choose which data access patterns to use, as well as customize the names of the methods generated. For the Fill pattern, let's change the name to

Figura 17: Seleccionar los Nombres de loa métodos

Figure 18: Productos Consultados por Categoría

PRÁCTICA:Crear y Agregar un método GetProductByProductID(productID) usando la misma técnica.

Click derecho en el método dentro del TableAdapter y seleccionar Vista Previa

Figure 19: Vista Previa de Datos

Ahora crearemos una página que muestre sólo los productos de una categoría específica.

Beverages.asp

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Beverages.aspx.cs" Inherits="Beverages" %> <!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>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <h2>Beverages</h2> <p> <asp:GridView ID="GridView1" runat="server" /> </asp:GridView>

</p> </div> </form> </body> </html>

Beverages.aspx.cs

using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using NorthwindTableAdapters; public partial class Beverages : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { ProductsTableAdapter productsAdapter = new ProductsTableAdapter(); GridView1.DataSource = productsAdapter.GetProductsByCategoryID(1); GridView1.DataBind(); } }

Figura 20: Productos de la Categoría de Bebidas

4: Manejo de Datos

Figure 21: Cada consulta de Insert, Update, y Delete se envían de forma inmediata a la BD en el enfoque de consulta individual

Figura 22: Todos los cambios se sincronizan con la BD cuando se usa el enfoque Update Method

Figura 23: Verificar que el TableAdapter contiene elementos en las propiedades InsertCommand, UpdateCommand, and DeleteCommand

.

Figura 24: Configurar las sentencias INSERT, UPDATE, y DELETE en el Generador de Consultas

Probar el siguiente código de ejemplo con el enfoque de lotes para duplicar el precio de los productos no descontinuados con 25 unidades en bodega o menos:

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new NorthwindTableAdapters.ProductsTableAdapter(); Northwind.ProductsDataTable products = productsAdapter.GetProducts(); foreach (Northwind.ProductsRow product in products) if (!product.Discontinued && product.UnitsInStock <= 25) product.UnitPrice *= 2; productsAdapter.Update(products);

Probar el código siguiente de enfoque directo para actualizar y agregar un registro:

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new NorthwindTableAdapters.ProductsTableAdapter(); productsAdapter.Update("Chai", 1, 1, "10 boxes x 20 bags", 18.0m, 39, 15, 10, false, 1); productsAdapter.Insert("New Product", 1, 1, "12 tins per carton", 14.95m, 15, 0, 10, false);

Métodos Personalizados Click derecho en el Diseñador del DataSet. Click en Agregar Consulta. En la segunda pantalla del asistente:

Figura 25: Creación de un método para agregar una nueva fila a la tabla Products.

En la siguiente pantalla aumentar la consulta agregando:

Figura 26: Agregar SCOPE_IDENTITY()

Finalmente nombrar al método como InsertProduct.

Figura 27: Nuevo nombre del método

Revisar que al regresar al diseñador del DataSet el ProductsTableAdapter contenga el método InsertProduct. Si no contiene parámetros para cada columna en la tabla Products es porque no se agregó punto y coma al final de la sentencia INSERT.

Los métodos insert por default regresan el número de filas afectadas. Queremos que el método InsertProduct regrese el valor de la consulta, no el número de filas. Para esto, seleccionar el valor Scalar:

Figure 29: Cambio de ExecuteMode a Scalar

Probar el código siguiente para usar el método agregado:

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new NorthwindTableAdapters.ProductsTableAdapter(); int new_productID = Convert.ToInt32(productsAdapter.InsertProduct ("New Product", 1, 1, "12 tins per carton", 14.95m, 10, 0, 10, false));

5: Completar la DAL Podemos aumentar el método inicial de GetProducts(), para incluir los valores de las columnas CategoryName y CompanyName, que actualizarán el DataTable.

Agregando consultas a Categories y Suppliers como subconsultas en lugar de usar JOINs, evitamos el retrabajo sobre esos métodos para modificar datos.

Dar click derecho en el método GetProducts() dentro del ProductsTableAdapter y seleccionar Configure. Ajustar la cláusula SELECT a:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName, (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName FROM Products

Figura 29: Actualizar la sentencia SELECT para el método GetProducts()

Figura 30: La DataTable Products contiene dos Columnas Nuevas

Práctica: Actualizr la consulta SELECT en el método GetProductsByCategoryID(categoryID).

Usando JOIN el Diseñador del DataSet no podría autogenerar los métodos para trabajr con los datos usando el enfoque directo.

PARA ENTREGAR

TABLE ADAPTERS 1.- Crear los siguientes TableAdapters y métodos usando las consultas siguientes

Notar que las consultas en el ProductsTableAdapter incluyen las subconsultas para tomar los nombres de cada categoría y proveedor

ProductsTableAdapter

o GetProducts: SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued , (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName, (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName FROM Products

o GetProductsByCategoryID: SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued , (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName, (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName FROM Products WHERE CategoryID = @CategoryID

o GetProductsBySupplierID: SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued , (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName,

(SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName FROM Products WHERE SupplierID = @SupplierID

o GetProductByProductID: SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued , (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName, (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName FROM Products WHERE ProductID = @ProductID

• CategoriesTableAdapter o GetCategories:

SELECT CategoryID, CategoryName, Description FROM Categories

o GetCategoryByCategoryID: SELECT CategoryID, CategoryName, Description FROM Categories WHERE CategoryID = @CategoryID

• SuppliersTableAdapter o GetSuppliers:

SELECT SupplierID, CompanyName, Address, City, Country, Phone FROM Suppliers

o GetSuppliersByCountry: SELECT SupplierID, CompanyName, Address, City, Country, Phone FROM Suppliers WHERE Country = @Country

o GetSupplierBySupplierID: SELECT SupplierID, CompanyName, Address, City, Country, Phone FROM Suppliers WHERE SupplierID = @SupplierID

• EmployeesTableAdapter o GetEmployees:

SELECT EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, Country FROM Employees

o GetEmployeesByManager: SELECT EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, Country

FROM Employees WHERE ReportsTo = @ManagerID

o GetEmployeeByEmployeeID: SELECT EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, Country FROM Employees WHERE EmployeeID = @EmployeeID

Figura 31: Diseñador de DataSet despúes de agregar 4 TableAdapters

2.- Actualizar la capa de presentación para que contenga:

1) Dos Controles ComboBox: uno para seleccionar el CategoryID y otro la Cantidad en Bodega y con base en ésta selección, mostrar los productos coincidentes.

2) Un ListBox que contenga los nombres de 5 productos, que al ser seleccionados nos los muestre con el resto de los campos correspondeintes