public int addNewUser(string firstname, string lastname, string companyname, string email, string password, string countryid, string stateid, string mobile, int type, string verify)
{
int status = 0;
try
{
Guid guid = Guid.NewGuid();
Database objDB = new SqlDatabase(connectionStr);
DbCommand objAdd = new SqlCommand();
objAdd.CommandType = CommandType.StoredProcedure;
objAdd.CommandText = "InsertUser";
objDB.AddInParameter(objAdd, "@FName", DbType.String, firstname);
objDB.AddInParameter(objAdd, "@LName", DbType.String, lastname);
objDB.AddInParameter(objAdd, "@Companyname", DbType.String, companyname);
objDB.AddInParameter(objAdd, "@Email", DbType.String, email);
objDB.AddInParameter(objAdd, "@Password", DbType.String, password);
objDB.AddInParameter(objAdd, "@Countryid", DbType.Int32, countryid);
objDB.AddInParameter(objAdd, "@Stateid", DbType.Int32, stateid);
objDB.AddInParameter(objAdd, "@Mobile", DbType.String, mobile);
objDB.AddInParameter(objAdd, "@Type", DbType.Int32, type);
objDB.AddInParameter(objAdd, "@Verify", DbType.String, verify);
objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
objDB.ExecuteNonQuery(objAdd);
status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));
return status;
}
catch (Exception ex)
{
objErr.GeneralExceptionHandling(ex, "Website - User Registration", "addNewUser", "GENERAL EXCEPTION");
return status;
}
}
//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 static DataTable GetResources(int jobId, int deptId)
{
SqlDatabase db = new SqlDatabase(connString);
string sql = @" SELECT U.UserId, U.FirstName + ' ' + U.LastName AS FullName,
t.TitleName as Title
FROM AllocableUsers U LEFT JOIN JobTitles AS t ON U.currentTitleID=t.TitleID
WHERE U.Active=1 AND U.UserId NOT IN (
SELECT UserId FROM Assignments WHERE JobId=@job_id
AND (EndDate IS NULL OR EndDate>DATEADD(s, 1, CURRENT_TIMESTAMP)))
AND U.DeptId=@dept_id AND realPerson='Y'
AND UserId NOT IN (
SELECT UserId
FROM timeEntry
WHERE JobId=@job_id AND UserId=U.UserId AND (TimeSpan IS NULL OR TimeSpan > 0)
)
ORDER BY FullName";
DbCommand command = db.GetSqlStringCommand(sql);
db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
db.AddInParameter(command, "@dept_id", DbType.Int32, deptId);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Resources";
command.Dispose();
return t;
}
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;
}
}
}
public int addInquiry(string name, string email, string subject, string message)
{
int status = 0;
try
{
Database objDB = new SqlDatabase(connectionStr);
DbCommand objAdd = new SqlCommand();
objAdd.CommandType = CommandType.StoredProcedure;
objAdd.CommandText = "InsertInquiry";
objDB.AddInParameter(objAdd, "@Name", DbType.String, name);
objDB.AddInParameter(objAdd, "@Email", DbType.String, email);
objDB.AddInParameter(objAdd, "@Subject", DbType.String, subject);
objDB.AddInParameter(objAdd, "@Message", DbType.String, message);
objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
objDB.ExecuteNonQuery(objAdd);
status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));
return status;
}
catch (Exception ex)
{
objCommom.LogFile("Contact.aspx", "addInquiry", ex);
return status;
}
}
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 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 void AddEmployeeToRole(int employeeID, int roleID)
{
string sqlQuery = "INSERT INTO EMPLOYEEROLES(EmployeeID, RoleID) Values (@EmployeeID, @RoleID)";
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "EmployeeID", DbType.Int32, employeeID);
db.AddInParameter(dbCommand, "RoleID", DbType.Int32, roleID);
db.ExecuteNonQuery(dbCommand);
}
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 );
}
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 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 static void UpdateTerritory(Territory territory)
{
string sqlQuery = "UPDATE Territory SET ParentTerritoryID=@ParentTerritoryID, FullDescription=@FullDescription, Name=@Name WHERE TerritoryID=" + territory.TerritoryID;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "ParentTerritoryID", DbType.Int32, territory.ParentTerritoryID);
db.AddInParameter(dbCommand, "FullDescription", DbType.String, territory.FullDescription);
db.AddInParameter(dbCommand, "Name", DbType.String, territory.Name);
db.ExecuteNonQuery(dbCommand);
}
public static void UpdateContact(Contact contact)
{
string sqlQuery = "UPDATE Contact SET FirstName=@FirstName,LastName=@LastName,Email=@Email,Phone=@Phone WHERE ContactID=" + contact.ContactID;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "FirstName", DbType.String, contact.FirstName);
db.AddInParameter(dbCommand, "LastName", DbType.String, contact.LastName);
db.AddInParameter(dbCommand, "Email", DbType.String, contact.Email);
db.AddInParameter(dbCommand, "Phone", DbType.String, contact.Phone);
db.ExecuteNonQuery(dbCommand);
}
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 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 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;
}
public static Territory InsertTerritory(Territory territory)
{
string sqlQuery = "INSERT INTO Territory(ParentTerritoryID,FullDescription,Name) " +
" VALUES(@ParentTerritoryID,@FullDescription,@Name);SELECT @@Identity";
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "ParentTerritoryID", DbType.Int32, territory.ParentTerritoryID);
db.AddInParameter(dbCommand, "FullDescription", DbType.String, territory.FullDescription);
db.AddInParameter(dbCommand, "Name", DbType.String, territory.Name);
territory.TerritoryID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
return territory;
}
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;
}
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 Contact InsertContact(Contact contact)
{
string sqlQuery = "INSERT INTO Contact(FirstName,LastName,Email,Phone) " +
" VALUES(@FirstName,@LastName,@Email,@Phone);SELECT @@Identity";
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "FirstName", DbType.String, contact.FirstName);
db.AddInParameter(dbCommand, "LastName", DbType.String, contact.LastName);
db.AddInParameter(dbCommand, "Email", DbType.String, contact.Email);
db.AddInParameter(dbCommand, "Phone", DbType.String, contact.Phone);
contact.ContactID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
return contact;
}
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;
}
public static void UpdateRole(Role role)
{
string sqlQuery = "UPDATE ROLE SET Name=@Name WHERE RoleID=" + role.RoleID;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "Name", DbType.String, role.Name);
db.ExecuteNonQuery(dbCommand);
}
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;
}
/// <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 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 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 UpdateCity(City city)
{
string sqlQuery = "UPDATE City SET Name=@Name WHERE CityID=" + city.CityID;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "Name", DbType.String, city);
db.ExecuteNonQuery(dbCommand);
}
public static IDataReader AllJobs(int clientId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetSqlStringCommand(@"
SELECT JobId, Name FROM AllOpenJobs J
WHERE Active=1 AND ClientId=@clientId
ORDER BY Name");
db.AddInParameter(command, "@clientId", DbType.Int32, clientId);
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;
}
}
/// <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);
}
}
