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); } }