MiniPortal - UDCsabia.tic.udc.es/.../transparencias/04-MiniPortal.pdf · 1 MiniPortal Integración de Sistemas Diseño e implementación con .NET MiniPortal Implementación en .NET

1

MiniPortal

Integración de SistemasDiseño e implementación con .NET

MiniPortal

Implementación en .NET de la aplicación con el mismo nombre, vista en la primera parte de la asignatura

Casos de usoRegistrar usuario

Autenticarse

Recuperar información de registro

Actualizar información de registro

Cambiar contraseña

2

Estructura de paquetes del modeloEs.UDC.DotNet

Util

UserProfile

UserFacade

Exceptions

VO

Delegate

Model

MiniPortal

VO

DAO

Es.UDC.DotNet.MiniPortal.Model.UserProfile.VO

+UserProfileDetailsVO()+ToString() : String

+FirstName : String+Surname : String+Email : String+Language : String+Country : String

UserProfileDetailsVO

+UserProfileVO()+ToString()

+LoginName : String+EncryptedPassword : String+UserProfileDetailsVO : UserProfileDetailsVO

UserProfileVO

1

Ambas clases parametrizadas con el atributo [Serializable()]

3

Patrón Value Object

Representa estado/valor

El estado se almacena en PropiedadesIncluyen métodos get y set implícitos

Sobrescribiremos (override) el método ToString()para imprimir el estado

VOs deben ser objetos serializablesSe indica con el atributo [Serializable()] precediendo a la declaración de la clase

Usaremos la notación xxxVO

Es.UDC.DotNet.MiniPortal.Model.UserProfile.DAO

4

Patrón Data Access Object

Oculta cómo se hace el acceso a la BD

Sus métodos necesitan recibirLa conexión (DbConnection)

Para crear los comandos a partir de ella

command = connection.CreateCommand()

La transacción (DbTransaction)Si hay una transacción iniciada, los comandos deben añadirse a ella (obligatoriamente)

/* If transaction exists, command will be added */if (transaction != null) {

command.Transaction = transaction;}


IUserProfileDAO (Adapter)Define una interfaz para insertar, borrar, actualizar y encontrar UserProfileVOs

Permite implementar adaptadores para distintas BD (dialectos de SQL)

UserProfileDAOFactory (Factory)Permite obtener una instancia de un adaptador apropiado para la aplicación

5


IUserProfileDAO + UserProfileDAOFactoryEs posible hacer plug-n-play (sin recompilar) de adaptadores cuando se cambia de BD

Facilita la instalación/configuración de la aplicación

Usaremos la notación xxxDAOEl prefijo SQL en el entorno .NET se usa para hacer referencia aSQL Server, no para referirse a SQL estándarPara evitar confusiones, no utilizaremos el prefijo SQL (siempretrabajaremos con SQL estándar) y utilizaremos SQLServer para referirnos a la BD de Microsoft

Carga Dinámica de Clases

Implementación de Factorías

Clases que se desean instanciar puedenPertenecer al assembly activoEstar situadas en un assembly independiente (fichero .dll)

NamespacesSystem.Reflection

System.Runtime.Remoting

6

Carga Dinámica de ClaseClase pertenece al assembly activo

// Nombre completo de la clase que se desea instanciarString daoClassName =

"Es.UDC.DotNet.MiniPortal.Model.UserProfile.DAO.SQLServerUserProfileDAO";

// Recuperamos el assembly actualmente en ejecucionAssembly assembly = Assembly.GetExecutingAssembly();

// Creamos instancia del tipo especificado// Parametros: // * nombre del assembly en el que se define el tipo// * tipo que deseamos instanciarObject theObject = AppDomain.CurrentDomain.

CreateInstanceAndUnwrap(assembly.FullName, daoClassName);

return (IUserProfileDAO)theObject;

Carga Dinámica de ClaseClase pertenece al assembly independiente

// Nombre completo de la clase que se desea instanciarString className = "MySql.Data.MySqlClient.MySqlConnection";

// Nombre de la libreria en la que esta definida la claseString driverFile =

"c:\\MySQL Connector Net 1.0.9\\Binaries\\.NET 1.1\\MySql.Data.dll";

// Cargamos el assembly a partir de su nombreAssembly assembly = Assembly.LoadFrom(driverFile);

// Creamos instancia tipo especificado// Parametros: // * nombre del assembly en el que se define el tipo// * tipo que deseamos instanciarObject obj = AppDomain.CurrentDomain.

CreateInstanceAndUnwrap(assembly.FullName, className);

return (DbConnection) obj;

7

Es.UDC.DotNet.MiniPortal.Model.UserProfile.DAO.UserProfileDAOFactory

public sealed class UserProfileDAOFactory {

/* NOTE: constants are implicitly static */private const String DAO_CLASS_NAME_PARAMETER =

"UserProfileDAOFactory/daoClassName";

/* NOTE: Class constructor must be private, so nobody will* be allowed to instantiate it */private UserProfileDAOFactory() { }


/// <exception cref="ConfigurationParameterException"/> /// <exception cref="InternalErrorException"/> public static IUserProfileDAO GetDAO() {

try {String daoClassName = ConfigurationManager.

AppSettings[DAO_CLASS_NAME_PARAMETER];

Assembly assembly = Assembly.GetExecutingAssembly();

Object theObject = AppDomain.CurrentDomain.CreateInstanceAndUnwrap(assembly.FullName, daoClassName);

return (IUserProfileDAO)theObject;

} catch (Exception e) {throw new InternalErrorException(e);

}}

}

8


Ejemplo de uso

DbProviderFactory dbFactory = DbProviderFactories.GetFactory(providerInvariantName);

/* Create and open the connection */connection = dbFactory.CreateConnection();connection.ConnectionString = connectionString;connection.Open();

/* Get DAO */IUserProfileDAO dao = UserProfileDAOFactory.GetDAO();

/* Test DAO.Exists */Boolean userExist = dao.Exists(connection, transaction,

userProfileVO.LoginName);

Es.UDC.DotNet.Util

Es.UDC.DotNet

Exceptions

Log

Crypto

MiniPortal

Util

9

Es.UDC.DotNet.Util.Exceptions


InternalErrorException

Error grave durante la ejecución de un métodoCaída de la BD, carga errónea de clases, etc.

Oculta el API empleada en la implementación

Encapsula la excepción real que se ha producidoÚtil para depuración

ModelException

Errores Lógicos en el modelo

10

Es.UDC.DotNet.Util.Exceptionsusing System;

namespace Es.UDC.DotNet.Util.Exceptions {

public class InternalErrorException : Exception {

private Exception encapsulatedException = null;

public InternalErrorException(Exception exception) {encapsulatedException = exception;

}

public InternalErrorException(String msg) : base(msg) { }


#region Properties Region

public override String Message{get { if (encapsulatedException == null)

return this.Message; else

encapsulatedException.Message;}

}

public Exception EncapsulatedException {get { return encapsulatedException; }

}

#endregion}

}

11

Es.UDC.DotNet.Util.Exceptionsusing System;

namespace Es.UDC.DotNet.Util.Exceptions {

public class InstanceException : ModelException {

private Object key;private String className;

protected InstanceException(String specificMessage, Object key, String className): base(specificMessage + " (key = '" + key +

"' - className = '" + className + "')") {

this.key = key;this.className = className;

}


#region Properties

public Object Key {get { return key; }

}

public String ClassName {get { return className; }

}

#endregion

}

}

12

Es.UDC.DotNet.Util.LogProporciona log de mensajes a pantalla y/o fichero

Contenido:LogManager.cs

App.config: parámetros de configuraciónLogDestination: destino de los mensajes (SCREEN / FILE / ALL)SeverityLevel: tipo de mensajes de los que se hará log (INFO / WARNING / ERROR)FileLocation: ruta al fichero de logFilePartialName y FileExtension: nombre del fichero de log con la sintaxis:

{FilePartialName}_DATE.{FileExtension}

Ejemplo de uso:

LogManager.RecordMessage("Example Message", LogManager.MessageType.INFO, this);

Resultado:

[13/11/2006 17:26:56] Es.UDC.DotNet.MiniPortal.Model.UserProfile.VO: INFO: Example Message

Sustituto de Console.WriteLine("…")

Es.UDC.DotNet.MiniPortal.Model.UserProfile.DAO.AbstractUserProfileDAO

13


AbstractUserProfileDAO

Implementación de IUserProfileDAO independiente del Data Provider

Deja sin implementar los métodos: String GetParameterSyntax(String parameterName)

String GetParameterName(String parameterName)

SQLServerUserProfileDAO, OracleUserProfileDAO, MySQLUserProfileDAO, etc.

Implementaciones para un Data Provider específico


public abstract class AbstractUserProfileDAO : IUserProfileDAO {

#region IUserProfileDAO Members

/// <summary>/// Create a new <code>UserProfile</code>/// </summary>/// <exception cref="DuplicateInstanceException"/> /// <exception cref="InternalErrorException"/> public void Create(DbConnection connection, DbTransaction transaction,

UserProfileVO userProfileVO) {

/* Check if the user already exists. */if (Exists(connection, transaction, userProfileVO.LoginName)) {

throw new DuplicateInstanceException(userProfileVO.LoginName, typeof(UserProfileVO).FullName);

}

14


try {/* Create the command. */DbCommand command = connection.CreateCommand();

/* If transaction exists, command will be added */if (transaction != null) {

command.Transaction = transaction;}

command.CommandText ="INSERT INTO UserProfile (loginName, enPassword, " +

"firstName, surname, email, language, country) " +"VALUES (" + GetParameterSyntax("loginName") +

", " + GetParameterSyntax("enPassword") +", " + GetParameterSyntax("firstname") +", " + GetParameterSyntax("surname") +", " + GetParameterSyntax("email") +", " + GetParameterSyntax("language") +", " + GetParameterSyntax("country") + ")";


DbParameter loginNameParam = command.CreateParameter();loginNameParam.ParameterName = GetParameterName("loginName");loginNameParam.DbType = DbType.String;loginNameParam.Size = 30;loginNameParam.Value = userProfileVO.LoginName;command.Parameters.Add(loginNameParam);

DbParameter encryptedPasswordParam = command.CreateParameter();<< ... >>

DbParameter firstNameParam = command.CreateParameter();<< ... >>

DbParameter surnameParam = command.CreateParameter();<< ... >>

DbParameter emailParam = command.CreateParameter();<< ... >>

DbParameter languageParam = command.CreateParameter();<< ... >>

15


DbParameter countryParam = command.CreateParameter();<< ... >>

command.Prepare();

int insertedRows = command.ExecuteNonQuery();

if (insertedRows == 0) {throw new SQLException("Can not add row to table" +

" 'UserProfile'");}

if (insertedRows > 1) {throw new SQLException("Duplicate row for login name = '" +

userProfileVO.LoginName + "' in table 'UserProfile'");}

} catch (SQLException e) { throw new InternalErrorException(e);

}} // Create


/// <summary>/// Looks if an <code>UserProfile</code> with/// this <paramref name="loginName"/> exists/// </summary>/// <returns>True if the <code>UserProfile</code> exists</returns>/// <exception cref="InternalErrorException"/> public Boolean Exists(DbConnection connection,

DbTransaction transaction, String loginName) {

DbDataReader dataReader = null;

try {/* Create the command. */

/* If transaction exists, command will be added */

command.CommandText ="SELECT loginName FROM UserProfile " +"WHERE loginName = " + GetParameterSyntax("loginName");

16


DbParameter loginNameParam = command.CreateParameter();loginNameParam.ParameterName = GetParameterName("loginName");loginNameParam.DbType = DbType.String;loginNameParam.Value = loginName;loginNameParam.Size = 30;command.Parameters.Add(loginNameParam);

command.Prepare();

dataReader = command.ExecuteReader();

return (dataReader.Read());

} catch (SQLException e) { throw new InternalErrorException(e);

} finally {dataReader.Close();

}} // Exists


/// <exception cref="InstanceNotFoundException"/> /// <exception cref="InternalErrorException"/> public UserProfileVO Find(DbConnection connection,


DbDataReader dataReader = null;

try {

/* Create the command. */


command.CommandText ="SELECT enPassword, firstName, surname, email, " +

"language, country " +"FROM UserProfile " +"WHERE loginName = " + GetParameterSyntax("loginName");

17


DbParameter loginNameParam = command.CreateParameter();loginNameParam.ParameterName = GetParameterName("loginName");loginNameParam.DbType = DbType.String;loginNameParam.Size = 30;loginNameParam.Value = loginName;command.Parameters.Add(loginNameParam);

command.Prepare();

dataReader = command.ExecuteReader();

if (!dataReader.Read()) {throw new InstanceNotFoundException(loginName,

typeof(UserProfileVO).FullName);}


String encryptedPassword = dataReader.GetString(0);String firstname = dataReader.GetString(1);String surname = dataReader.GetString(2);String email = dataReader.GetString(3);String language = dataReader.GetString(4);String country = dataReader.GetString(5);

UserProfileDetailsVO userProfileDetailsVO =new UserProfileDetailsVO(firstname,

surname, email, country, language);

/* Return the value object. */return new UserProfileVO(loginName, encryptedPassword,

userProfileDetailsVO);

} catch (DbException e) { throw new InternalErrorException(e);

} finally {dataReader.Close();

}} // Find

18


/// <exception cref="InstanceNotFoundException"/> /// <exception cref="InternalErrorException"/> public void Update(DbConnection connection, DbTransaction transaction,

UserProfileVO userProfileVO) {

try {



command.CommandText ="UPDATE UserProfile " +"SET enPassword = " + GetParameterSyntax("enPassword") +

", firstName = " + GetParameterSyntax("firstname") +", surname = " + GetParameterSyntax("surname") +", email = " + GetParameterSyntax("email") +", language = " + GetParameterSyntax("language") +", country = " + GetParameterSyntax("country") +

" WHERE loginName = " + GetParameterSyntax("loginName");


DbParameter loginNameParam = command.CreateParameter();loginNameParam.ParameterName = GetParameterName("loginName");loginNameParam.DbType = DbType.String;loginNameParam.Size = 30;loginNameParam.Value = userProfileVO.LoginName;command.Parameters.Add(loginNameParam);

/* Crear el resto de parametros:* encryptedPasswordParam, firstNameParam, surnameParam,* emailParam, languageParam y countryParam*/

command.Prepare();

19


int updatedRows = command.ExecuteNonQuery();

if (updatedRows == 0) {throw new InstanceNotFoundException(

userProfileVO.LoginName, typeof(UserProfileVO).FullName);

}

if (updatedRows > 1) {throw new SQLException("Duplicate row for login name = '" +

userProfileVO.LoginName + "' in table 'UserProfile'");}


}} // Update


/// <exception cref="InstanceNotFoundException"/> /// <exception cref="InternalErrorException"/> public void Remove(DbConnection connection,


try {


/* If transaction exists, command will be added. */

command.CommandText ="DELETE FROM UserProfile " +"WHERE loginName = " + GetParameterSyntax("loginName");

DbParameter loginNameParam = command.CreateParameter();loginNameParam.ParameterName = GetParameterName("loginName");loginNameParam.DbType = DbType.String;loginNameParam.Size = 30;loginNameParam.Value = loginName;command.Parameters.Add(loginNameParam);

20


command.Prepare();

int removedRows = command.ExecuteNonQuery();

if (removedRows == 0) {throw new InstanceNotFoundException(loginName,

typeof(UserProfileVO).FullName);}


}} // Remove

#endregion

Es.UDC.DotNet.MiniPortal.Model.UserProfile.DAO.SQLServerUserProfileDAO

21

Es.UDC.DotNet.MiniPortal.Model.UserProfile.DAO.SQLServerUserProfileDAO

public class SQLServerUserProfileDAO : AbstractUserProfileDAO {

private const String SQL_SERVER_PARAM_PREFIX_PARAMETER ="UserProfileDAOFactory/parameterPrefix";

private static String PARAMETER_PREFIX =ConfigurationManager.AppSettings[SQL_SERVER_PARAM_PREFIX_PARAMETER];

protected override String GetParameterSyntax(string parameterName) {

return PARAMETER_PREFIX + parameterName;

}

protected override String GetParameterName(string parameterName) {

return PARAMETER_PREFIX + parameterName;}

}

Es.UDC.DotNet.MiniPortal.Model.UserFacade.Delegate

22

Session Facade y Business Delegate

Patrón Session FacadeNotación XXXFacade

Patrón Business DelegateNotación XXXDelegate

En este caso, el objeto Business Delegate y el Session Facade serán el mismo

No necesitamos hacer plug-n-play del modeloEl Delegate será una clase concreta

Session Facade y Business Delegate

Para facilitar la comprensión del código, las operaciones del Session Facade se han implementado directamente

En una aplicación real, las operaciones del Session Facade deberían implementarse en términos de acciones (implementar cada operación en una clase)

23

Es.UDC.DotNet.MiniPortal.Model.UserFacade.Delegate.UserFacadeDelegate

[Serializable()]public class UserFacadeDelegate {

private String loginName;

private const String PROVIDER_INVARIANT_NAME_PARAMETER ="UserProfileDAOFactory/providerInvariantName";

private const String CONNECTION_STRING_PARAMETER ="UserProfileDAOFactory/connectionString";

private static String providerInvariantName =ConfigurationManager.AppSettings[PROVIDER_INVARIANT_NAME_PARAMETER];

private static String connectionString =ConfigurationManager.AppSettings[CONNECTION_STRING_PARAMETER];


private static DbProviderFactory dbProviderFactory =DbProviderFactories.GetFactory(providerInvariantName);

/* State Facade */public UserFacadeDelegate() {

loginName = null;}

24


/// <exception cref="InstanceNotFoundException">/// This error occurs when this <paramref name="loginName"/> does/// not exist./// </exception>/// <exception cref="IncorrectPasswordException">/// This error occurs when the <paramref name="password"/> does not/// match with the user password stored./// </exception>/// <exception cref="InternalErrorException"/> public LoginResultVO Login(String loginName, String password,

Boolean passwordIsEncrypted) {

DbConnection connection = null;String encryptedPassword = null;

try {

/* Create the connection. */connection = dbProviderFactory.CreateConnection();connection.ConnectionString = connectionString;connection.Open();


IUserProfileDAO dao = UserProfileDAOFactory.GetDAO();

UserProfileVO userProfileVO =dao.Find(connection, null, loginName);

if (passwordIsEncrypted) {encryptedPassword = password;

} else {encryptedPassword = Crypto.crypt(password);

}

if (!userProfileVO.EncryptedPassword.Equals(encryptedPassword)) {throw new IncorrectPasswordException(loginName);

}

this.loginName = loginName;return new LoginResultVO(userProfileVO.LoginName,

userProfileVO.EncryptedPassword,userProfileVO.UserProfileDetailsVO.Language,userProfileVO.UserProfileDetailsVO.Country);

25


} catch (InstanceNotFoundException e) {throw e;

} catch (InternalErrorException e) {throw e;


} finally {try {

if (connection != null) {connection.Close();

}} catch (Exception e) {

throw new InternalErrorException(e);}

}} // Login


/// <exception cref="InternalErrorException"/> public UserProfileVO FindUserProfile() {

DbConnection connection = null;

try {

/* Create the connection. */<< ... >>

/* Get a DAO */<< ... >>

/* State Facade. loginName is stored. */return dao.Find(connection, null, this.loginName);

} catch (InstanceNotFoundException e) {throw new InternalErrorException(e);

} catch (InternalErrorException e) {throw e;

26



} finally {try {

if (connection != null) {connection.Close();



}} // FindUserProfile


/// <exception cref="DuplicateInstanceException">/// This error occurs when exists a previous instance with the same key./// </exception>/// <exception cref="InternalErrorException"/>public void RegisterUser(String loginName, String clearPassword,

UserProfileDetailsVO userProfileDetailsVO) {

DbConnection connection = null;DbTransaction transaction = null;Boolean rollback = false;

try {


/* Starts new transaction. */transaction =

connection.BeginTransaction(IsolationLevel.Serializable);

27


IUserProfileDAO dao = UserProfileDAOFactory.GetDAO();

String encryptedPassword = Crypto.crypt(clearPassword);

UserProfileVO userProfileVO = new UserProfileVO(loginName,encryptedPassword, userProfileDetailsVO);

dao.Create(connection, transaction, userProfileVO);

this.loginName = loginName;

} catch (DuplicateInstanceException e) {rollback = true;throw e;

} catch (InternalErrorException e) {rollback = true;throw e;


} catch (Exception e) {rollback = true;throw new InternalErrorException(e);

} finally {try {

/* Commit or rollback, and finally, close connection. */if (connection != null) {

if (rollback) {transaction.Rollback();

} else {transaction.Commit();

}connection.Close();



}} // RegisterUser

28


/// <exception cref="InternalErrorException"/> public void UpdateUserProfileDetails(UserProfileDetailsVO

userProfileDetailsVO) {

<< ... >>

try {


/* Starts new transaction. */<< ... >>

/* Get a DAO */<< ... >>

UserProfileVO userProfileVO = dao.Find(connection,transaction, this.loginName);

userProfileVO.UserProfileDetailsVO = userProfileDetailsVO;

dao.Update(connection, transaction, userProfileVO);


} catch (InstanceNotFoundException e) {rollback = true;throw new InternalErrorException(e);



} finally {try {

/* Commit or rollback, and finally, close connection. */<< ... >>


}}

} // UpdateUserProfileDetails

29


/// <exception cref="IncorrectPasswordException">/// This error occurs when the <paramref name="oldClearPassword"/> does/// not match with the user's password stored./// </exception>/// <exception cref="InternalErrorException"/> public void ChangePassword(String oldClearPassword,

String newClearPassword) {

<< ... >>

try {/* Create the connection. */

<< ... >>/* Starts new transaction. */

<< ... >>/* Get a DAO */

<< ... >>


UserProfileVO userProfileVO = dao.Find(connection,transaction, this.loginName);

/* Recovery current password. */String storedPassword = userProfileVO.EncryptedPassword;

/* Compare current with oldClearPassword. */if (!storedPassword.Equals(Crypto.crypt(oldClearPassword))) {

throw new IncorrectPasswordException(this.loginName);}

/* Update current password. */storedPassword = Crypto.crypt(newClearPassword);

userProfileVO.EncryptedPassword = storedPassword;

dao.Update(connection, transaction, userProfileVO);

30


} catch (InstanceNotFoundException e) {rollback = true;throw new InternalErrorException(e);



} finally {try {

/* Commit or rollback, and finally, close connection. */<< ... >>


}}

} // ChangePassword

Pruebas de unidad

Se han incluido pruebas de unidad para validar clases individuales

Incluyen un método Main (comentado con //) con código de prueba dentro de

#region Test Code Region. Uncomment for testing.

#endregion

Las pruebas de unidad de los DAOs se han ubicado en la factoría de DAOs

El Session Facade contiene código de prueba para cada caso de uso

31

Acceso a Parámetros Configurables

App.configFichero que permite almacenar propiedades configurables

Almacena pares (variable/valor)

Formato XML

Accesible en tiempo de ejecución

Acceso a parámetros no definidos implica que se lance ConfigurationErrorException

Aplicaciones Web ASP.NETLectura de propiedades desde un fichero Web.config

Acceso a Parámetros ConfigurablesEjemplo de fichero App.config

<?xml version="1.0" encoding="utf-8" ?><configuration>

<appSettings>

<add key="UserProfileDAOFactory/providerInvariantName"

value="System.Data.SqlClient"/>

<add key="UserProfileDAOFactory/connectionString"

value="Data Source=localhost\SQLExpress; Initial Catalog=miniportal; User ID=user; Password=password"/>

</appSettings>

</configuration>

32

Acceso a Parámetros ConfigurablesEjemplo de acceso

using System.Configuration;

try {

// Gets the AppSettings section for the current applicattion’s// default configuration and access to the parameter by keyString connectionString =

ConfigurationSettings.AppSettings[“UserProfileDAOFactory/connectionString”];

<< ... >>

} catch(ConfigurationErrorException e) {

Console.WriteLine(“Parameter not found”);}

Es.UDC.DotNet.PageByPageIterator

Patrón Page-by-Page IteratorSolicitar objetos por bloques

En JavaResultSet.TYPE_SCROLL_INSENSITIVE

Permite mover el cursor hacia adelante, atrás o saltar a una posición absoluta o relativaNo muestra los cambios que se estén haciendo a la BD mientras está abierto

En .NETNo se dispone de un equivalente al ResultSet.TYPE_SCROLL_INSENSITIVE

33

Es.UDC.DotNet.PageByPageIteratorpublic List<UserProfileVO> Find(DbConnection connection,

DbTransaction transaction, String condition, String orderField, int startIndex, int count) {

List<UserProfileVO> info = new List<UserProfileVO>();

try { String commandText =

" Select top " + count + " * from UserProfile " +" where " + condition + " and " + orderField + " not in " + " (select top " + startIndex + " " + orderField +" from UserProfile where " + condition +" order by " + orderField + ") " +" order by " + orderField + ";";

DbCommand command = connection.CreateCommand();command.Transaction = transaction;command.CommandText = commandText;command.Prepare();DbDataReader dr = command.ExecuteReader();

Es.UDC.DotNet.PageByPageIteratorwhile (dr.Read()) {

String loginName = dr.GetString(0);String encryptedPassword = dr.GetString(1);String firstName = dr.GetString(2);String surname = dr.GetString(3);String email = dr.GetString(4);String language = dr.GetString(5);String country = dr.GetString(6);

UserProfileDetailsVO userProfileDetailsVO =new UserProfileDetailsVO(firstName, surname, email, language, country);

UserProfileVO userProfileVO =new UserProfileVO(loginName, encryptedPassword, userProfileDetailsVO);

info.Add(userProfileVO);

dr.Close();}

} catch(DbException e) { <<...>>> }

return info;}

34

Es.UDC.DotNet.PageByPageIterator#region Test Code Region. Uncomment for testing.

public static void Main(String[] args) {

int startIndex = 0;int count = 25;String orderField = "loginName";String condition = "language='ES'";

Boolean end = false;

// Get Connection<<...>>

while (!end) {

List<UserProfileVO> info = Find(connection, transaction, condition, orderField,

startIndex, count);

foreach (UserProfileVO userProfile in info) {Console.WriteLine(userProfile);

}


Console.WriteLine("--------------------------");

if (info.Count < count)end = true;

elsestartIndex = startIndex + count;

info.Close();

}

Console.ReadLine();

} // Main

#endregion

35


Otras soluciones:Usando DataSet

DbDataAdapter – Método Fill(DataSet, Int32, Int32, String)adapter.Fill(dataSet, startIndex, count, "Users");

InconvenienteEficiencia reducida

Usando DataReaderDr.Read()

Solución más eficiente que usando un DataSetInconveniente

Necesario recorrer todas las filas previas a startIndex

Generación de IdentificadoresBasado en Columnas Contador

Inserción de cada fila implica la creación automática de un valor numérico empleado como identificador

Una vez insertada la fila, puede consultarse el último identificador creado mediante una query

Query dependiente del SGBD

MS SQL Serverselect @@identity

MySQLselect LAST_INSERT_ID()

36

Generación Identificadores

<<implements>>

Generación IdentificadoresIEntityIdentifierRetrieverusing System;using System.Data.Common;

namespace IdentifierGenerator {

interface IEntityIdentifierRetriever {

/// <summary>/// Returns the last Entity Identifier generated/// </summary>Int64 GetGeneratedIdentifier(DbConnection connection);

}}

37

Generación IdentificadoresGenericEntityIdentifierRetriever (I)using System;using System.Data;using System.Data.Common;using System.Configuration;

using Es.UDC.DotNet.Util.Exceptions;

namespace IdentifierGenerator {

public class GenericEntityIdentifierRetriever : IEntityIdentifierRetriever {

private const String QUERY_PARAMETER ="GenericEntityIdentifierRetriever/query";

private static String queryString =ConfigurationManager.AppSettings[QUERY_PARAMETER];

Generación IdentificadoresGenericEntityIdentifierRetriever (II)

/// <summary>/// Returns the last Entity Identifier generated/// </summary>/// <param name="connection">DataBase Connection</param>/// <exception cref="InternalErrorException"/> public Int64 GetGeneratedIdentifier(DbConnection connection) {

DbCommand command = null;

try {command = connection.CreateCommand();command.CommandText = queryString;

Int64 identity = Convert.ToInt64(command.ExecuteScalar());return identity;


}}

}}

38

Generación IdentificadoresEntityIdentifierRetrieverFactoryclass EntityIdentifierRetrieverFactory {

private const String RETRIEVER_CLASS_NAME_PARAMETER ="EntityIdentifierRetrieverFactory/retrieverClassName";

private EntityIdentifierRetrieverFactory() { }

public static IEntityIdentifierRetriever GetRetriever() {

Object theObject = null;

try {

String retrieverClassName =ConfigurationManager.AppSettings[RETRIEVER_CLASS_NAME_PARAMETER];

Assembly assembly = Assembly.GetExecutingAssembly();

theObject = AppDomain.CurrentDomain.CreateInstanceAndUnwrap(assembly.FullName, retrieverClassName);


}

return (IEntityIdentifierRetriever)theObject;}

}

Generación IdentificadoresParámetros de configuración

Query para la obtención del último identificador insertado

<add key="GenericEntityIdentifierRetriever/query"

value="SELECT @@IDENTITY"/>

Clase instanciada por la factoría EntityIdentifierRetrieverFactory para la recuperación de identificadores

<add key="EntityIdentifierRetrieverFactory/retrieverClassName“

value="IdentifierGenerator.GenericEntityIdentifierRetriever"/>

39

Generación IdentificadoresEjemplo de uso: MiniBank (I)public class CCAccountDAO : AbstractAccountDAO {

/// <summary>/// Implements the Create operation for AccountDAO using a Counter Columns approach/// </summary>/// <exception cref="InternalErrorException"/> public AccountVO Create(DbConnection connection, Transaction transaction, AccountVO accountVO) {

DbCommand command = null;

try {command = connection.CreateCommand();command.CommandText = "INSERT INTO Account (userID, balance) " +

" values (" + GetParameterSyntax(userID) + ", " + GetParameterSyntax(balance) + ")";

// paramaters configuration <<…>>

command.Prepare();

/* Execute query. */int insertedRows = command.ExecuteNonQuery();

if (insertedRows == 0) throw new SQLException("Can not add row to table 'Account'");

if (insertedRows > 1)throw new SQLException("Duplicate row in table 'Account'");

Generación IdentificadoresEjemplo de uso: MiniBank (II)

/* Get account identifier. */IEntityIdentifierRetriever entityIdentifierRetriever =

EntityIdentifierRetrieverFactory.GetRetriever();

Int64 accountIdentifier = entityIdentifierRetriever.GetGeneratedIdentifier(connection);

/* Return the value object AccountVO(id, userID, balance) */return new AccountVO(accountIdentifier, accountVO.UserIdentifier,

accountVO.Balance);

} catch(Exception e) {throw new InternalErrorException(e);

}}

}

Documents

MiniPortal - UDCsabia.tic.udc.es/.../transparencias/04-MiniPortal.pdf · 1 MiniPortal Integración de Sistemas Diseño e implementación con .NET MiniPortal Implementación en .NET