public static bool Autenticar(string userName, string password)
{
bool returnValue=false;
SHA1 encryptador = SHA1.Create();
byte[] pass = Encoding.Unicode.GetBytes(password);
byte[] passByte = encryptador.ComputeHash(pass,0,pass.Length);
//string passEncriptado = System.Text.Encoding.Unicode.GetString(passByte);
string passString = BitConverter.ToString(passByte).Replace("-", String.Empty);
string queryFinal = String.Format(QueryLogin, userName, passString);
DataAccessCommand command = new DataAccessCommand(queryFinal, System.Data.CommandType.Text);
DataAccessDAO dao = DataAccessDAO.GetInstance();
//int result = dao.ExecuteAction(command);
object result = dao.GetFirstResult(command);
if (result != null)
returnValue = result.ToString().Equals(userName);
return returnValue;
}
public void TestExecuteCommand()
{
//arrange
bool expect = true;
bool actual = false;
IDatabaseAccess target = new OracleClientDataAccess(this._ConnectionString);
DataAccessCommand command = new DataAccessCommand();
int dataExecutedCount = 0;
//act
command.SqlCommand =
@"UPDATE TABLE
SET TIME=SYSDATE
WHERE ID BETWEEN :BEGIN_ID AND :END_ID";
command.AddParameter("BEGIN_ID", 1668);
command.AddParameter("END_ID", 1670);
try
{
dataExecutedCount = target.ExecuteCommand(command);
actual = (dataExecutedCount > 0);
}
catch (Exception)
{
throw;
}
//assert
Assert.AreEqual(expect, actual);
}
public int ExecuteCommand(DataAccessCommand command)
{
int excutedCount;
try
{
OpenConnection();
this._Command.Connection = this._Connection;
this.AddParameters(command.ParameterCollection);
this._Command.CommandText = command.SqlCommand;
excutedCount = this._Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new CommandExecutionExeception(ex.Message, command);
}
finally
{
this._Connection.Close();
}
return excutedCount;
}
public DataTable QueryWithDataTable(DataAccessCommand command)
{
DataSet queryResult = new DataSet();
this.AddParameters(command.ParameterCollection);
this._Command.CommandText = command.SqlCommand;
this._Command.Connection = this._Connection;
this._DataAdapter.SelectCommand = this._Command;
try
{
this._DataAdapter.Fill(queryResult);
return queryResult.Tables[0];
}
catch (Exception ex)
{
throw new CommandExecutionExeception(ex.Message, command);
}
}
public IDataReader QueryWithDataReader(DataAccessCommand command)
{
this.AddParameters(command.ParameterCollection);
this._Command.CommandText = command.SqlCommand;
this._Command.Connection = this._Connection;
try
{
OpenConnection();
this._DataReader = this._Command.ExecuteReader();
return this._DataReader;
}
catch (Exception ex)
{
throw new CommandExecutionExeception(ex.Message, command);
}
}
/// <summary>
/// Metodo para Obtener Casas para la pagina de busqueda, aplica filtros de tipo de inmueble o colonia
/// </summary>
/// <param name="filterBusqueda"></param>
/// <param name="inicio"></param>
/// <param name="final"></param>
/// <returns></returns>
public static List<CasasResult> ObtenerCasasResultado(CasasFilterBusqueda filterBusqueda)
{
DataAccessDAO dao = DataAccessDAO.GetInstance();
DataAccessCommand command = new DataAccessCommand(CasasConstantStoreProcedure.OBTENERCASASRESULTADO, filterBusqueda);
List<CasasResult> casas = dao.GetList<CasasResult>(command);
CasaHelper.FormatProperty<CasasResult>(casas);
return casas;
}
public static List<CasasResultPanel> ObtenerCasasPanel(CasasPanelFilter filterBusqueda)
{
DataAccessDAO dao = DataAccessDAO.GetInstance();
DataAccessCommand command = new DataAccessCommand("BusquedaCasasPanel", filterBusqueda);
List<CasasResultPanel> casas = dao.GetList<CasasResultPanel>(command);
string editHRef=ConfigurationManager.AppSettings["editHRef"];
string deleteHRef=ConfigurationManager.AppSettings["deleteHRef"];
casas.ForEach(casa =>
{
casa.EditHRef = String.Format(editHRef, casa.ID);
//casa.DeleteHRef = String.Format(deleteHRef, casa.ID);
casa.DeleteHRef = "javascript:eliminarCasa(" + casa.ID + ","+ casa.EnVenta.ToString().ToLower() +")";
casa.Estado = (casa.EnVenta) ? "Deshabilitar" : "Habilitar";
}
);
return casas;
}
/// <summary>
/// Consulta que devuelve un result ubicado por ID, se espera que sea solo un registro
/// Se usa en la pagina de detalles de casa
/// </summary>
/// <param name="id">ID de la casa en la Base de Datos</param>
/// <returns>Result de la Casa</returns>
public static CasasResultById ObtenerCasasById(int id)
{
DataAccessDAO dao = DataAccessDAO.GetInstance();
DataAccessCommand command = new DataAccessCommand(CasasConstantStoreProcedure.OBTENERCASABYID);
command.SetParamater("ID", id.GetType(), id);
CasasResultById casa = dao.GetOneRow<CasasResultById>(command);
CasaHelper.FormatProperty<CasasResultById>(casa);
return casa;
}
public static CasaCatalogoResult ObtenerCasa(int id)
{
DataAccessCommand command = new DataAccessCommand("ObtenerCasa");
command.SetParamater("ID", typeof(int), id);
return DataAccessDAO.GetInstance().GetOneRow<CasaCatalogoResult>(command);
}
public static int CambiarEstadoEnVenta(int id, bool enVenta)
{
byte enVentaByte = Convert.ToByte(enVenta);
DataAccessCommand command = new DataAccessCommand("UPDATE casa SET enVentaCasa=" + enVentaByte + " WHERE idCasa=" + id, CommandType.Text);
DataAccessDAO dao = DataAccessDAO.GetInstance();
int resultado = dao.ExecuteAction(command);
return resultado;
}
public void TestQueryDataTableWithParameter()
{
//arrange
bool expect = true;
bool actual = true;
IDatabaseAccess target = new OracleClientDataAccess(this._ConnectionString);
DataTable result;
DataAccessCommand command = new DataAccessCommand();
//act
command.SqlCommand = @"
SELECT *
FROM TABLE
WHERE ID=:ROOM_ID";
command.AddParameter("ROOM_ID", 150376);
result = target.QueryWithDataTable(command);
actual = (result != null);
//assert
Assert.AreEqual(expect, actual);
}
public void TestQueryDataTableException()
{
//arrange
bool expect = true;
bool actual = true;
IDatabaseAccess target = new OracleClientDataAccess(this._ConnectionString);
DataTable result;
DataAccessCommand command = new DataAccessCommand();
string errMessage = "";
//act
command.SqlCommand =
@"SELECT *
FROM TABLE
WHERE COLUMNNAME=:ROOM_ID";
command.AddParameter("ggdg", "fdsfsd");
command.AddParameter("efe", "434");
command.AddParameter("fe23", "fdr3sfsd");
try
{
result = target.QueryWithDataTable(command);
}
catch (CommandExecutionExeception ex)
{
errMessage = ex.DetailErrorMessage;
actual = (errMessage != "");
}
//assert
Assert.AreEqual(expect, actual);
}
public void TestQueryDataTable()
{
//arrange
bool expect = true;
bool actual = true;
IDatabaseAccess target = new OracleClientDataAccess(this._ConnectionString);
DataTable result;
DataAccessCommand command = new DataAccessCommand();
//act
command.SqlCommand = "SELECT SYSDATE FROM DUAL";
result = target.QueryWithDataTable(command);
actual = (result != null);
//assert
Assert.AreEqual(expect, actual);
}
public void TestQueryDataReaderWithParameter()
{
//arrange
bool expect = true;
bool actual = true;
IDatabaseAccess target = new OracleClientDataAccess(this._ConnectionString);
IDataReader result;
DataAccessCommand command = new DataAccessCommand();
List<Chatroom> roomCollection;
//act
command.SqlCommand = @"
SELECT *
FROM TABLE
WHERE COLUMNNAME=:ID";
command.AddParameter("ID", 150376);
try
{
result = target.QueryWithDataReader(command);
roomCollection = new List<Chatroom>();
while (result.Read())
{
roomCollection.Add(
new Chatroom(
result.GetInt32(result.GetOrdinal("ID")),
result.GetDateTime(result.GetOrdinal("ACTDATE")),
result.GetString(result.GetOrdinal("FLICODE")),
result.GetString(result.GetOrdinal("DPORT")))
);
}
actual = (roomCollection.Count > 0);
}
catch (Exception)
{
throw;
}
finally
{
target.CloseConnection();
}
//assert
Assert.AreEqual(expect, actual);
}
public void TestQueryDatareaderException()
{
bool expect = true;
bool actual = true;
IDatabaseAccess target = new OracleClientDataAccess(this._ConnectionString);
IDataReader result;
DataAccessCommand command = new DataAccessCommand();
List<Chatroom> roomCollection;
string errMessage = "";
//act
command.SqlCommand = @"
SELECT *
FROM TABLE
WHERE ID=:ROOM_ID";
command.AddParameter("ROOM_ID", 150376);
try
{
result = target.QueryWithDataReader(command);
roomCollection = new List<Chatroom>();
while (result.Read())
{
roomCollection.Add(
new Chatroom(
result.GetInt32(result.GetOrdinal("ROOM_ID")),
result.GetDateTime(result.GetOrdinal("ACTDATE")),
result.GetString(result.GetOrdinal("FLICODE")),
result.GetString(result.GetOrdinal("DPORT")))
);
}
}
catch (CommandExecutionExeception ex)
{
errMessage = ex.DetailErrorMessage;
actual = (errMessage != "");
}
finally
{
target.CloseConnection();
}
//assert
Assert.AreEqual(expect, actual);
}
public void TestExecuteTransactionException()
{
//arrange
bool expect = true;
bool actual = false;
IDatabaseAccess target = new OracleClientDataAccess(this._ConnectionString);
List<DataAccessCommand> commandCollection = new List<DataAccessCommand>();
DataAccessCommand command;
int dataExecutedCount = 0;
string errMessage = "";
//act
command = new DataAccessCommand();
command.SqlCommand =
@"UPDATE TABLENAME
SET TIME=SYSDATE
WHERE ID TWEEN :BEGIN_ID AND :END_ID";
command.AddParameter("BEGIN_ID", 1668);
command.AddParameter("END_ID", 1670);
commandCollection.Add(command);
command = new DataAccessCommand();
command.SqlCommand =
@"UPDATE TABLENAME
SET TIME=SYSDATE
WHERE ID TWEEN :BEGIN_ID AND :END_ID";
command.AddParameter("BEGIN_ID", 1660);
command.AddParameter("END_ID", 1665);
commandCollection.Add(command);
try
{
dataExecutedCount = target.ExecuteTransaction(commandCollection);
}
catch (CommandExecutionExeception ex)
{
errMessage = ex.DetailErrorMessage;
actual = (errMessage != "");
}
//assert
Assert.AreEqual(expect, actual);
}