[.NET] ¿Cómo usar Enterprise Library 6 Data Access Application Block usando solo código?

En una publicación pasada mostré como usar EntLibDAAB configurándola usando el .config. En esta ocasión enseñaré como usar este componente usando solo C#.

Para esta demostración usé cualquier procedimiento almacenado que devuelva datos (en este caso dbo.uspGetEmployeeManagers) de una base de datos muy conocida llamada Adventure Works y un proyecto WPF con la siguiente class y DataGrid para mostrar los datos.

	public class EmployeeManager
	{
	public string FirstName { get; set; }
	public string LastName { get; set; }
	public string OrganizationNode { get; set; }
	public string ManagerFirstName { get; set; }
	public string ManagerLastName { get; set; }
	}

view raw EmployeeManager.cs hosted with ❤ by GitHub

	<Window x:Class="EntLib6DAABInCode.MainWindow"
	xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
	xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
	Title="[.NET] EntLib6DAABInCode . Epicalsoft — Superheroic Software Development Blog" Height="270" Width="900">
	<Grid>
	<DataGrid x:Name="ManagersDataGrid" IsReadOnly="True">
	<DataGrid.Columns>
	<DataGridTextColumn Header="First Name" Width="*" Binding="{Binding FirstName}"/>
	<DataGridTextColumn Header="Last Name" Width="*" Binding="{Binding LastName}"/>
	<DataGridTextColumn Header="Organization Node" Width="*" Binding="{Binding OrganizationNode}"/>
	<DataGridTextColumn Header="Manager First Name" Width="*" Binding="{Binding ManagerFirstName}"/>
	<DataGridTextColumn Header="Manager Last Name" Width="*" Binding="{Binding ManagerLastName}"/>
	</DataGrid.Columns>
	</DataGrid>
	</Grid>
	</Window>

view raw MainPage.xaml hosted with ❤ by GitHub

SqlDatabase

La primera forma de trabajar con EntLib6DAAB y SQL Server es usando directamente Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase y pasándole como argumento la cadena de conexión.

	var employeeManagers = new List<EmployeeManager>();
	var database = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase("Data source=.;User ID=sa; Password=sql; Initial Catalog=AdventureWorks2012");
	using (var dataReader = database.ExecuteReader("dbo.uspGetEmployeeManagers", 81))
	{
	while (dataReader.Read())
	employeeManagers.Add(new EmployeeManager
	{
	FirstName = dataReader.GetString(2),
	LastName = dataReader.GetString(3),
	OrganizationNode = dataReader.GetString(4),
	ManagerFirstName = dataReader.GetString(5),
	ManagerLastName = dataReader.GetString(6)
	});
	}
	this.ManagersDataGrid.ItemsSource = employeeManagers;

view raw Snippet-1.cs hosted with ❤ by GitHub

DatabaseSettings

Si queremos tener un mayor control sobre la configuración podemos usar Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings

	var databaseSettings = new DatabaseSettings();
	databaseSettings.ProviderMappings.Add(new DbProviderMapping(DbProviderMapping.DefaultSqlProviderName, typeof(Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase)));
	var connectionStringsSection = new ConnectionStringsSection();
	connectionStringsSection.ConnectionStrings.Add(new ConnectionStringSettings("AdventureWorks", "Data Source=.;User ID=sa; Password=sql; Initial Catalog=AdventureWorks2012", DbProviderMapping.DefaultSqlProviderName));
	var dictionaryConfigurationSource = new DictionaryConfigurationSource();
	dictionaryConfigurationSource.Add(DatabaseSettings.SectionName, databaseSettings);
	dictionaryConfigurationSource.Add("connectionStrings", connectionStringsSection);
	DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory(dictionaryConfigurationSource), false);
	var employeeManagers = new List<EmployeeManager>();
	var database = DatabaseFactory.CreateDatabase("AdventureWorks");
	using (var dataReader = database.ExecuteReader("dbo.uspGetEmployeeManagers", 81))
	{
	while (dataReader.Read())
	employeeManagers.Add(new EmployeeManager
	{
	FirstName = dataReader.GetString(2),
	LastName = dataReader.GetString(3),
	OrganizationNode = dataReader.GetString(4),
	ManagerFirstName = dataReader.GetString(5),
	ManagerLastName = dataReader.GetString(6)
	});
	}
	this.ManagersDataGrid.ItemsSource = employeeManagers;

view raw Snippet-2.cs hosted with ❤ by GitHub

Si queremos usar alguna otra base de datos con EntLib como Oracle o MySQL tenemos que agregar nuevas entradas a ProviderMappings o si queremos establecer alguna base de datos por defecto no olvidemos hacer lo siguiente:

	databaseSettings.ProviderMappings.Add(new DbProviderMapping(DbProviderMapping.DefaultSqlProviderName, typeof(Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)));
	databaseSettings.DefaultDatabase = "AdventureWorks";

view raw Snippet-3.cs hosted with ❤ by GitHub

CodePlex

Código fuente disponible en: CSharp -> EntLib6DAABInCode