public IPeople GetByLoginDetails(string emailId, string password) { IPeople user = new MyDiary.Domain.Domains.People(); user.UserRoles = new List <IRole>() { new Role() }; user.UserImages = new List <IImage>() { new Image() }; SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.People.USERLOGIN_SELECT); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.EmailId, emailId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.Password, password); DataTable dtUser = SQLDbConnection.FillDataSetFromDataAdapter(SQLDbConnection.GetNewSqlDataAdapterObject(cmd)); if (dtUser != null && dtUser.Rows.Count > 0) { user = Map_PeopleDataRow_To_PeopleDomain(dtUser.Rows[0]); } return(user); }
public int AddUser(IPeople user) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.People.USER_ADD); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.FirstName, user.FirstName); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.MiddleName, user.MiddleName == null ? string.Empty : user.MiddleName); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.LastName, user.LastName); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.EmailId, user.EmailId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.SiteUserId, user.SiteUserId); //optional cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.SiteId, user.SiteId); //optional var userRole = user.UserRoles.FirstOrDefault(); if (userRole != null) { cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.RoleId, userRole.RoleId); var userImage = user.UserImages.FirstOrDefault(); if (userImage != null) { cmd.Parameters.AddWithValue(Constants.StoredProcedures.Image.Parameters.ImageId, userImage.ImageId); } } object result = cmd.ExecuteScalar(); //if image exists 3 tables has to be affected otherwise two return(int.Parse(result.ToString())); }
public IPeople GetByOpenSiteDetails(int siteId, string siteUserId) { IPeople user = new MyDiary.Domain.Domains.People(); user.UserRoles = new List <IRole>() { new Role() }; user.UserImages = new List <IImage>() { new Image() }; try { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.People.DRY_USER_GET_BYOPENSITEDETAILS); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.SiteId, siteId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.SiteUserId, siteUserId); DataTable dtUser = SQLDbConnection.FillDataSetFromDataAdapter(SQLDbConnection.GetNewSqlDataAdapterObject(cmd)); if (dtUser != null && dtUser.Rows.Count > 0) { user = Map_PeopleDataRow_To_PeopleDomain(dtUser.Rows[0]); } return(user); } catch (Exception) { return(user); } }
private DataTable GetAllIncomeTypesAsDataTable(int userId) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.IncomeTypes.INCOMETYPES_SELECTBYUSERID); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.UserId, userId); SqlDataAdapter sDataAdapter = SQLDbConnection.GetNewSqlDataAdapterObject(cmd); conn.Close(); return(SQLDbConnection.FillDataSetFromDataAdapter(sDataAdapter)); }
public void Delete(UserDO user) { using (var connection = SQLDbConnection.GetConnection()) { var command = connection.CreateCommand(); command.CommandText = $"DELETE FROM {DbSchema.Users.Table} WHERE {DbSchema.Users.Id} = @id"; command.Parameters.Add(new SqlParameter("@id", user.Id)); command.ExecuteNonQuery(); connection.Close(); } }
private DataTable GetFilteredIncomesDatatTable(int userId) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Incomes.DRY_GETFILTEREDINCOMES); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.UserId, userId); SqlDataAdapter sDataAdapter = SQLDbConnection.GetNewSqlDataAdapterObject(cmd); conn.Close(); return(SQLDbConnection.FillDataSetFromDataAdapter(sDataAdapter)); }
private DataTable GetAllIncomesDatatTable(int userId) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Incomes.Incomes_SelectByUserId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.USERId, userId); SqlDataAdapter sDataAdapter = SQLDbConnection.GetNewSqlDataAdapterObject(cmd); conn.Close(); return(SQLDbConnection.FillDataSetFromDataAdapter(sDataAdapter)); }
public List <UserDO> GetAll() { using (var connection = SQLDbConnection.GetConnection()) { var command = connection.CreateCommand(); command.CommandText = $"SELECT * FROM {DbSchema.Users.Table}"; var result = ExecuteRead(command); connection.Close(); return(result); } }
public List <UserDO> GetById(long id) { using (var connection = SQLDbConnection.GetConnection()) { var command = connection.CreateCommand(); command.CommandText = $"SELECT * FROM {DbSchema.Users.Table} WHERE {DbSchema.Users.Id} = @id"; command.Parameters.Add(new SqlParameter("@id", id)); var result = ExecuteRead(command); connection.Close(); return(result); } }
public void Delete(int expenseId) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Expenses.Expense_Delete); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseId, expenseId); cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); }
public void AddUserLoginInformation(IUserLogin userLogin) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.People.USERLOGIN_ADD); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.UserId, userLogin.UserId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.EmailId, userLogin.EmailId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.Password, userLogin.Password); cmd.ExecuteNonQuery(); //if image exists 3 tables has to be affected otherwise two }
public List <UserDO> GetByUsername(string usrname) { using (var connection = SQLDbConnection.GetConnection()) { var command = connection.CreateCommand(); command.CommandText = $"SELECT * FROM {DbSchema.Users.Table} WHERE {DbSchema.Users.Alias} = @usrname"; command.Parameters.Add(new SqlParameter("@usrname", usrname)); var result = ExecuteRead(command); connection.Close(); return(result); } }
private DataTable GetFilteredExpensesDataTable(string date, string type, int userId) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.ExpenseTypes.DRY_GETALLEXPENSES_BYDATEANDTYPE); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseDate, date); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseType, type); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.UserId, userId); SqlDataAdapter sDataAdapter = SQLDbConnection.GetNewSqlDataAdapterObject(cmd); DataTable dtExpenses = SQLDbConnection.FillDataSetFromDataAdapter(sDataAdapter); conn.Close(); return(dtExpenses); }
public bool AddExpenseType(string expenseType, int userId) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.ExpenseTypes.EXPENSETYPE_ADD); cmd.Parameters.AddWithValue(Constants.StoredProcedures.ExpenseTypes.Parameters.ExpenseType, expenseType); cmd.Parameters.AddWithValue(Constants.StoredProcedures.ExpenseTypes.Parameters.UserId, userId); bool result = cmd.ExecuteNonQuery() == 1 ? true : false; conn.Close(); return(bool.Parse(result.ToString())); }
public int Add(IIncomeType incomeType) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.IncomeTypes.INCOMETYPE_ADD); cmd.Parameters.AddWithValue(Constants.StoredProcedures.IncomeTypes.Parameters.INCOMETYPE, incomeType.Type); cmd.Parameters.AddWithValue(Constants.StoredProcedures.IncomeTypes.Parameters.USERID, incomeType.UserId); int incomeTypeId = int.Parse(cmd.ExecuteScalar().ToString()); conn.Close(); return(incomeTypeId); }
public List <IPeople> GetAll() { List <IPeople> users = new List <IPeople>(); SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.People.DRY_GETALLUSERS); DataTable dtUsers = SQLDbConnection.FillDataSetFromDataAdapter(SQLDbConnection.GetNewSqlDataAdapterObject(cmd)); if (dtUsers != null && dtUsers.Rows.Count > 0) { users.AddRange(this.Map_PeopleDataTable_To_PeopleList(dtUsers)); } return(users); }
public IImage GetUploadImageById(int imageId) { IImage uploadImage = new MyDiary.Domain.Domains.Image(null); SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Image.Image_SelectById); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Image.Parameters.ImageId, imageId); DataTable dtUploadImage = SQLDbConnection.FillDataSetFromDataAdapter(SQLDbConnection.GetNewSqlDataAdapterObject(cmd)); if (dtUploadImage != null && dtUploadImage.Rows.Count > 0) { uploadImage = GetImageFromDataTable(dtUploadImage); } return(uploadImage); }
public int AddIncome(IIncome income) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Incomes.INCOME_ADD); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.USERId, income.UserId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.INCOMETYPEID, income.IncomeType.TypeId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.AMOUNT, income.Amount); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.INCOMEDATE, income.IncomeDate); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.DESCRIPTION, income.Description); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.COMMENTS, income.Comments); int incomeTypeId = int.Parse(cmd.ExecuteScalar().ToString()); conn.Close(); return(incomeTypeId); }
public int Add(IExpense expenseDomain) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Expenses.Expense_Add); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseTypeId, expenseDomain.ExpenseType.TypeId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseDate, expenseDomain.ExpenseDate); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Description, expenseDomain.Description); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Comments, expenseDomain.Comments); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Amount, expenseDomain.Amount); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.UserId, expenseDomain.CurrentUser.UserId); object result = cmd.ExecuteScalar(); conn.Close(); return(int.Parse(result.ToString())); }
public void Insert(UserDO user) { string hashedPassword = EncriptadorIrreversible.EncriptarIrreversible(user.Password); using (var connection = SQLDbConnection.GetConnection()) { var command = connection.CreateCommand(); command.Parameters.Add(new SqlParameter("@username", user.Alias)); command.Parameters.Add(new SqlParameter("@password", hashedPassword)); command.Parameters.Add(new SqlParameter("@name", user.Nombre)); command.Parameters.Add(new SqlParameter("@lastname", user.Apellido)); command.Parameters.Add(new SqlParameter("@email", user.Email)); command.CommandText = $"INSERT INTO {DbSchema.Users.Table} ({DbSchema.Users.ListColumns()}) " + $"VALUES(@username, @password, @name, @lastname, @email)"; command.ExecuteNonQuery(); connection.Close(); } }
public void Update(IIncome incomeDomain) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Incomes.Income_Update); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.INCOMEID, incomeDomain.IncomeId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.USERId, incomeDomain.UserId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.INCOMETYPEID, incomeDomain.IncomeType.TypeId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.INCOMEDATE, incomeDomain.IncomeDate); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.DESCRIPTION, incomeDomain.Description); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.COMMENTS, incomeDomain.Comments); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.AMOUNT, incomeDomain.Amount); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.MODIFIEDBY, incomeDomain.UserId); cmd.ExecuteNonQuery(); cmd.Dispose(); //ToDo ==> If this is success ,implement this in all methods... conn.Close(); }
public void Update(IExpense expenseDomain) { SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Expenses.Expense_Update); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseId, expenseDomain.Id); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.UserId, expenseDomain.CurrentUser.UserId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseTypeId, expenseDomain.ExpenseType.TypeId); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseDate, expenseDomain.ExpenseDate); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Description, expenseDomain.Description); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Comments, expenseDomain.Comments); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Amount, expenseDomain.Amount); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.MODIFIEDBY, expenseDomain.CurrentUser.UserId); cmd.ExecuteNonQuery(); cmd.Dispose(); //ToDo ==> If this is success ,implement this in all methods... conn.Close(); }
public void Update(UserDO user) { string hashedPassword = EncriptadorIrreversible.EncriptarIrreversible(user.Password); using (var connection = SQLDbConnection.GetConnection()) { var command = connection.CreateCommand(); command.Parameters.Add(new SqlParameter("@id", user.Id)); command.Parameters.Add(new SqlParameter("username", user.Alias)); command.Parameters.Add(new SqlParameter("@password", hashedPassword)); command.Parameters.Add(new SqlParameter("@name", user.Nombre)); command.Parameters.Add(new SqlParameter("@lastname", user.Apellido)); command.Parameters.Add(new SqlParameter("@email", user.Email)); command.CommandText = $"UPDATE {DbSchema.Users.Table} " + $"SET {DbSchema.Users.Nombre} = @username, {DbSchema.Users.Password} = @password, " + $"{DbSchema.Users.Nombre} = @name, {DbSchema.Users.Apellido} = @lastname, " + $"{DbSchema.Users.Email} = @email " + $"WHERE {DbSchema.Users.Id} = @id"; command.ExecuteNonQuery(); connection.Close(); } }
public int UploadImage(IImage image) { int uploadedImageId = -1; if (image == null) { return(uploadedImageId); } SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject(); conn.Open(); SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Image.Image_Add); cmd.Parameters.AddWithValue(Constants.StoredProcedures.Image.Parameters.IMAGE, image.UserImage); object result = cmd.ExecuteScalar(); if (result != null) { uploadedImageId = int.Parse(result.ToString()); } ; return(uploadedImageId); }