public static void SaveBaby(string babyName, long position, bool gender, int year, long rank)
{
Database objDB = new SqlDatabase(ConfigurationManager.ConnectionStrings["DBaseConnectionString"].ConnectionString);
using (DbCommand objCMD = objDB.GetStoredProcCommand("PSP_Babies_Save"))
{
objDB.AddInParameter(objCMD, "@Name",
DbType.String, babyName);
objDB.AddInParameter(objCMD, "@Gender",
DbType.String, gender.ToIndicator());
objDB.AddInParameter(objCMD, "@Position",
DbType.Int64, position);
objDB.AddInParameter(objCMD, "@Rank",
DbType.Int64, rank);
objDB.AddInParameter(objCMD, "@Year",
DbType.Int32, year);
//objDB.AddOutParameter(objCMD, "@strMessage", DbType.String, 255);
try
{
objDB.ExecuteNonQuery(objCMD);
}
catch (Exception ex)
{
throw ex;
}
}
}
//fetch record
public static DataTable GetImportedRecords(int? top = 1000, bool direction = true, string name = "",
bool? gender = null, int? year = null, long? rank = null)
{
Database objDB = new SqlDatabase(ConfigurationManager.ConnectionStrings["DBaseConnectionString"].ConnectionString);
DataSet _ds = new DataSet();
using (DbCommand objCMD = objDB.GetStoredProcCommand("PSP_Babies_Get"))
{
objDB.AddInParameter(objCMD, "@Top",
DbType.Int32, top??1000000);
objDB.AddInParameter(objCMD, "@SortingDirection",
DbType.String, direction.ToIndicator());
objDB.AddInParameter(objCMD, "@Name",
DbType.String, name);
objDB.AddInParameter(objCMD, "@Gender",
DbType.String, gender.ToIndicator());
objDB.AddInParameter(objCMD, "@Year",
DbType.Int32, year);
objDB.AddInParameter(objCMD, "@Rank",
DbType.Int64, rank);
try
{
_ds = objDB.ExecuteDataSet(objCMD);
return _ds != null ? _ds.Tables[0] : new DataTable();
}
catch (Exception ex)
{
throw ex;
}
}
}
public void PublicarMensajeSql(string aplicacion, string error, Exception excepcion)
{
try
{
SqlDatabase baseDedatos = new SqlDatabase(ConfigurationManager.ConnectionStrings["AccesoDual"].ConnectionString);
DbCommand comando = baseDedatos.GetStoredProcCommand("adm.NlayerSP_RegistrarErrorAplicativo");
comando.CommandType = CommandType.StoredProcedure;
string interna = null;
if (excepcion.InnerException != null)
{
interna = excepcion.InnerException.Message;
}
baseDedatos.AddInParameter(comando, "Aplicacion", SqlDbType.NVarChar, aplicacion);
baseDedatos.AddInParameter(comando, "Error", SqlDbType.NVarChar, error);
baseDedatos.AddInParameter(comando, "Excepcion", SqlDbType.NText, excepcion.Message);
baseDedatos.AddInParameter(comando, "Interna", SqlDbType.NText, interna);
baseDedatos.ExecuteNonQuery(comando);
}
catch {}
}
public override CustomerList GetCustomer(string vipCode)
{
SqlDatabase database = new SqlDatabase(ConnectionString);
DbCommand command = database.GetStoredProcCommand("rmSP_WSPOS_GetCustomer");
command.CommandTimeout = 300;
database.AddInParameter(command, "@VipCode", DbType.String, vipCode);
List<Customer> customers = new List<Customer>();
using (IDataReader reader = database.ExecuteReader(command))
{
while (reader.Read())
{
Customer customer = new Customer();
customer.CustomerId = Convert.ToInt32(reader["VIPCode_id"]);
customer.CustomerCode = reader["VipCode"] as string;
customer.FirstName = reader["VIPGName"] as string;
customer.LastName = reader["VIPName"] as string;
customer.Telephone = reader["VIPTel"] as string;
if (reader["VIPBDay"] != DBNull.Value)
customer.BirthDate = Convert.ToDateTime(reader["VIPBDay"]);
customers.Add(customer);
}
}
CustomerList customerList = new CustomerList();
customerList.Customers = customers;
customerList.TotalCount = customers.Count;
return customerList;
}
public static int CurrectWeek()
{
SqlDatabase db = new SqlDatabase( connString );
DbCommand command = db.GetStoredProcCommand( "getWeekNr" );
command.CommandType = CommandType.StoredProcedure;
//the +1 below is to correct for an apparent off-by-one error in the stored procedure
return Convert.ToInt32( db.ExecuteScalar( command ) ) + 1;
}
public static DateTime CurrectWeekStartDate( int currentWeek )
{
SqlDatabase db = new SqlDatabase( connString );
DbCommand command = db.GetStoredProcCommand( "smWkNmStr" );
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter( command, "@week_no", DbType.Int32, currentWeek );
return Convert.ToDateTime( db.ExecuteScalar( command ) );
}
public static IDataReader JobsAssignedTo(int clientId, int userId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_JobsAssignedTo");
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter(command, "@ClientId", DbType.Int32, clientId);
db.AddInParameter(command, "@UserId", DbType.Int32, userId);
return db.ExecuteReader(command);
}
public static IDataReader Deparments(int clientId)
{
SqlDatabase db = new SqlDatabase( connString );
DbCommand command = db.GetStoredProcCommand( "ALOC__DepartmentSelectList" );
db.AddInParameter( command, "@ClientId", DbType.Int32, clientId );
command.CommandType = CommandType.StoredProcedure;
return db.ExecuteReader( command );
}
public static void Assign(int jobId, int userId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand cmd = db.GetStoredProcCommand("ALOC_Assign");
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
db.ExecuteNonQuery(cmd);
cmd.Dispose();
}
public static IDataReader Clients( string type, int userId )
{
SqlDatabase db = new SqlDatabase( connString );
DbCommand command = db.GetStoredProcCommand( "ALOC__ClientSelectList" );
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter( command, "@Type", DbType.String, type );
db.AddInParameter( command, "@UserId", DbType.Int32, userId );
return db.ExecuteReader( command );
}
private static bool SelectUser(ref User user)
{
try
{
SqlDatabase db = new SqlDatabase(Properties.Settings.Default.connString);
using (DbCommand cmd = db.GetStoredProcCommand("SelectUser"))
{
db.AddInParameter(cmd, "authcode", DbType.String, user.AuthCode);
using (IDataReader dr = db.ExecuteReader(cmd))
{
bool first = true;
while (dr.Read())
{
if (first)
{
user.Age = dr["age"] as string;
user.AuthCode = dr["authcode"] as string;
user.Gender = dr["gender"] as string;
first = false;
}
int? tid = dr["testid"] as int?;
if (tid != null)
{
Test t = null;
if (user.Tests.ContainsKey(tid.Value))
t = user.Tests[tid.Value];
else
{
t = new Test()
{
ID = tid.Value,
TimeEst = (int)dr["timeest"],
MaxArraySize = (int)dr["maxarraysize"],
DelayPeriod = (int)dr["delayperiod"]
};
user.Tests.Add(tid.Value, t);
}
t.ImageArrays.Add(new ImageArray()
{
Index = (int)dr["index"],
ImagesDisplayed = (int)dr["imagesdisplayed"],
UserInput = (int)dr["userinput"],
ImageFile = (string)dr["imagefile"]
});
}
}
}
return true;
}
}
catch
{
return false;
}
}
public override bool IsUserInRole(string username, string roleName)
{
SqlDatabase sqlDatabase = new SqlDatabase(_connectionString);
DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.SCISP_EstaElUsuarioEnElRol");
sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName);
sqlDatabase.AddInParameter(dbCommand, "Login", DbType.String, username);
sqlDatabase.AddInParameter(dbCommand, "Rol", DbType.String, roleName);
return (bool) sqlDatabase.ExecuteScalar(dbCommand);
}
public override void CreateRole(string roleName)
{
SqlDatabase sqlDatabase = new SqlDatabase(_connectionString);
DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.NlayerSP_RegistrarRol");
sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName);
sqlDatabase.AddInParameter(dbCommand, "Nombre", DbType.String, roleName);
sqlDatabase.AddInParameter(dbCommand, "Activo", DbType.Boolean, true);
sqlDatabase.ExecuteNonQuery(dbCommand);
}
public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate)
{
SqlDatabase sqlDatabase = new SqlDatabase(_connectionString);
DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.NlayerSP_EliminarPerfilesInactivos");
sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName);
sqlDatabase.AddInParameter(dbCommand, "UltimaActividad", DbType.DateTime, userInactiveSinceDate);
int deleteCount = (int) sqlDatabase.ExecuteScalar(dbCommand);
return deleteCount;
}
private static DataTable AllocatedJobs(int region, int startWeek)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_RegionGridJobListSelect");
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter(command, "@RegionId", DbType.Int32, region);
db.AddInParameter(command, "@StartWeek", DbType.Int32, startWeek);
DataTable dt = null;
dt = db.ExecuteDataSet(command).Tables[0].Copy();
dt.TableName = "Jobs";
command.Dispose();
return dt;
}
public IDataReader ObtenerDataReader(string conexion, string procedimiento, List<DbParameter> parametros)
{
var bd = new SqlDatabase(conexion);
DbCommand cmd = bd.GetStoredProcCommand(procedimiento);
EstablecerParametros(parametros, bd, cmd);
IDataReader resultado = bd.ExecuteReader(cmd);
ObtenerParametrosOut(parametros, cmd);
return resultado;
}
public int ObtenerRowCount(string conexion, string procedimiento, List<DbParameter> parametros)
{
var bd = new SqlDatabase(conexion);
DbCommand cmd = bd.GetStoredProcCommand(procedimiento);
EstablecerParametros(parametros, bd, cmd);
int filasAfectadas = bd.ExecuteNonQuery(cmd);
ObtenerParametrosOut(parametros, cmd);
return filasAfectadas;
}
public DataSet ObtenerDataSet(string conexion, string procedimiento, List<DbParameter> parametros)
{
var bd = new SqlDatabase(conexion);
DbCommand cmd = bd.GetStoredProcCommand(procedimiento);
EstablecerParametros(parametros, bd, cmd);
DataSet ds = bd.ExecuteDataSet(cmd);
ObtenerParametrosOut(parametros, cmd);
return ds;
}
private static DataTable Availability(int empId, int weekNumber)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_EmpGridAvailability");
db.AddInParameter(command, "@userId", DbType.Int32, empId);
db.AddInParameter(command, "@weekNumber", DbType.Int32, weekNumber);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Availability";
command.Dispose();
return t;
}
private static DataTable AllocatedUsers(int region, int startWeek, int deptId, string resType)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_RegionGridUserListSelect");
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter(command, "@RegionId", DbType.Int32, region);
db.AddInParameter(command, "@StartWeek", DbType.Int32, startWeek);
db.AddInParameter(command, "@DepartmentId", DbType.Int32, deptId);
db.AddInParameter(command, "@ResourceType", DbType.String, resType);
DataTable dt = null;
dt = db.ExecuteDataSet(command).Tables[0].Copy();
dt.TableName = "User";
command.Dispose();
return dt;
}
private static DataTable AllocatedDepts(int clientId, int startWeek, int jobId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_ProjectGridDeptSelect");
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter(command, "@client_id", DbType.Int32, clientId);
db.AddInParameter(command, "@start_week", DbType.Int32, startWeek);
db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Depts";
command.Dispose();
return t;
}
/// <summary>
/// Retrieves a rule from the database
/// </summary>
/// <param name="Name">The name of the rule</param>
/// <returns>An AuthorizationRuleData object</returns>
public AuthorizationRuleData GetRule(string name)
{
AuthorizationRuleData rule = null;
DbCommand cmd = dbRules.GetStoredProcCommand("dbo.GetRuleByName");
dbRules.AddInParameter(cmd, "Name", DbType.String, name);
using (IDataReader reader = dbRules.ExecuteReader(cmd))
{
if (reader.Read())
{
rule = GetRuleFromReader(reader);
}
}
return(rule);
}
public override int DeleteProfiles(ProfileInfoCollection profiles)
{
XElement perfilesXml = new XElement("Perfiles");
foreach (ProfileInfo profileInfo in profiles)
{
perfilesXml.Add(new XElement("Perfil", new XAttribute("Login", profileInfo.UserName)));
}
SqlDatabase sqlDatabase = new SqlDatabase(_connectionString);
DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.NlayerSP_EliminarPerfil");
sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName);
sqlDatabase.AddInParameter(dbCommand, "Perfiles", DbType.Xml, perfilesXml.ToString());
sqlDatabase.ExecuteNonQuery(dbCommand);
return profiles.Count;
}
private static DataTable AllocatedJobs( int clientId, int startWeek, string resourceType )
{
SqlDatabase db = new SqlDatabase( connString );
DbCommand command = db.GetStoredProcCommand( "ALOC__TeamGridJobListSelect" );
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter( command, "@ClientId", DbType.Int32, clientId );
db.AddInParameter( command, "@StartWeek", DbType.Int32, startWeek );
db.AddInParameter( command, "@Type", DbType.String, resourceType );
DataTable t = new DataTable();
t = db.ExecuteDataSet( command ).Tables[0].Copy();
t.TableName = "Job";
command.Dispose();
return t;
}
private static DataTable AllocatedEmps(int clientId, int startWeek, int jobId, string resType, int region)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_ProjectGridEmpsSelect");
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter(command, "@client_id", DbType.Int32, clientId);
db.AddInParameter(command, "@start_week", DbType.Int32, startWeek);
//TODO: why is this setting @dept_id to Zero?
db.AddInParameter(command, "@dept_id", DbType.Int32, 0);
db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
db.AddInParameter(command, "@resource_type", DbType.String, resType);
db.AddInParameter(command, "@region", DbType.Int32, region);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Emps";
command.Dispose();
return t;
}
public DataTable ObtenerDataTable(string conexion, string procedimiento, List<DbParameter> parametros)
{
DataTable dt = null;
var bd = new SqlDatabase(conexion);
DbCommand cmd = bd.GetStoredProcCommand(procedimiento);
EstablecerParametros(parametros, bd, cmd);
DataSet ds = bd.ExecuteDataSet(cmd);
ObtenerParametrosOut(parametros, cmd);
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
return dt;
}
private static bool IsUser(string authcode, ref bool result)
{
try
{
SqlDatabase db = new SqlDatabase(Properties.Settings.Default.connString);
using (DbCommand cmd = db.GetStoredProcCommand("IsUser"))
{
db.AddInParameter(cmd, "authcode", DbType.String, authcode);
int? o = db.ExecuteScalar(cmd) as int?;
if (o == null)
result = false;
else
result = (o.Value == 1);
return true;
}
}
catch
{
return false;
}
}
public static int GetMaxTestID()
{
try
{
int result = 0;
SqlDatabase db = new SqlDatabase(Properties.Settings.Default.connString);
using (DbCommand cmd = db.GetStoredProcCommand("SelectMaxTest"))
{
int? o = db.ExecuteScalar(cmd) as int?;
if (o == null)
result = 0;
else
result = o.Value;
return result;
}
}
catch
{
return 0;
}
}
/// <summary>
/// Método que Ejecuta un Procedimiento Almacenado de Inserción, Eliminación o Modificación
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="LstEntidad">Entidad de Tipo Lista que se interpretará como parametro de Entrada del SP</param>
/// <param name="NombreTabla">Nombre de la Tabla a Insertar</param>
/// <param name="Procedimiento">Nombre del Procedimiento</param>
/// <returns></returns>
public static Resultado <T> EjecutarProcedimientoOperacional <T>(List <T> LstEntidad, string NombreTabla, string Procedimiento)
{
var ObjResultado = new Resultado <T>();
try
{
SqlDatabase db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(ConfigBase.ConexionSQL);
DbCommand dbCommand = db.GetStoredProcCommand(Procedimiento);
db.AddInParameter(dbCommand, "@" + NombreTabla, SqlDbType.Structured, ListToDataTable <T>(LstEntidad));
//db.SetParametros(dbCommand, Entidad);
db.ExecuteNonQuery(dbCommand);
return(ObjResultado);
}
catch (Exception Ex)
{
DacLog.Registrar(Ex, Procedimiento);
ObjResultado.ResultadoGeneral = false;
ObjResultado.Mensaje = Ex.Message;
return(ObjResultado);
}
}
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
XElement usuariosXml = new XElement("Usuarios");
foreach (string username in usernames)
{
usuariosXml.Add(new XElement("Usuario", new XAttribute("Login", username)));
}
XElement rolesXml = new XElement("Roles");
foreach (string role in roleNames)
{
rolesXml.Add(new XElement("Rol", new XAttribute("Nombre", role)));
}
SqlDatabase sqlDatabase = new SqlDatabase(_connectionString);
DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.NlayerSP_RegistrarUsuariosEnRoles");
sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName);
sqlDatabase.AddInParameter(dbCommand, "Usuarios", DbType.Xml, usuariosXml.ToString());
sqlDatabase.AddInParameter(dbCommand, "Roles", DbType.Xml, rolesXml.ToString());
sqlDatabase.ExecuteNonQuery(dbCommand);
}
public static int[] Update( int employeeId, int jobId, int weekNum, int allocationId, int mins, int doNotAssign)
{
int[] retval = new int[] { -1, -1 };
SqlDatabase db = new SqlDatabase( connString );
DbCommand command = db.GetStoredProcCommand( "ALOC__AllocationUpdate" );
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter( command, "@EmployeeId", DbType.Int32, employeeId );
db.AddInParameter( command, "@JobId", DbType.Int32, jobId );
db.AddInParameter( command, "@WeekNum", DbType.Int32, weekNum );
db.AddInParameter(command, "@DoNotAssign", DbType.Int32, doNotAssign);
//db.AddInParameter( command, "@AllocationId", DbType.Int32, allocationId );
db.AddInParameter( command, "@Mins", DbType.Int32, mins );
//object val = db.ExecuteScalar(command);
IDataReader rr = db.ExecuteReader(command);
//if (rr.NextResult())
if (rr.Read())
{
retval[0] = Convert.ToInt32(rr["AllocId"].ToString());
retval[1] = Convert.ToInt32(rr["NoteLength"].ToString());
}
command.Dispose();
return retval;
}
public override SiteMapNode BuildSiteMap()
{
lock (this)
{
if (!IsInitialized)
{
throw new Exception("El proveedor de SiteMap no ha sido inicializado.");
}
// TODO: _nodoRaiz == null
if (true)
{
Clear();
SqlDatabase sqlDatabase = new SqlDatabase(_connectionString);
DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.SCISP_ObtenerSiteMap");
sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName);
using (IDataReader dataReader = sqlDatabase.ExecuteReader(dbCommand))
{
dataReader.Read();
int siteMapId = dataReader.IsDBNull(0) ? 0 : dataReader.GetInt32(0);
string titulo = dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1);
string descripcion = dataReader.IsDBNull(2) ? string.Empty : dataReader.GetString(2);
string roles = dataReader.IsDBNull(4) ? "NULL" : dataReader.GetString(4);
string url = dataReader.IsDBNull(3) ? null : dataReader.GetString(3);
string[] rolList = roles.Split(',');
_nodoRaiz = new SiteMapNode(this, siteMapId.ToString(CultureInfo.InvariantCulture), url, titulo,
descripcion, rolList, null, null, null);
_nodos.Add(siteMapId, _nodoRaiz);
AddNode(_nodoRaiz);
while (dataReader.Read())
{
siteMapId = dataReader.GetInt32(0);
titulo = dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1);
descripcion = dataReader.IsDBNull(2) ? string.Empty : dataReader.GetString(2);
url = dataReader.IsDBNull(3) ? null : dataReader.GetString(3);
roles = dataReader.IsDBNull(4) ? "NULL" : dataReader.GetString(4);
int padreId = dataReader.IsDBNull(5) ? 0 : dataReader.GetInt32(5);
rolList = roles.Split(',');
SiteMapNode nodoHijo = new SiteMapNode(this,
siteMapId.ToString(CultureInfo.InvariantCulture), url,
titulo, descripcion, rolList, null, null, null);
_nodos.Add(siteMapId, nodoHijo);
if (_nodos.ContainsKey(padreId))
{
AddNode(nodoHijo, _nodos[padreId]);
}
else
{
AddNode(nodoHijo);
}
}
}
}
return _nodoRaiz;
}
}
