internal static void Execute(string query, T t) { using (SqlConnection dbConnection = ConnectionDb.OpenConnection()) { dbConnection.Execute(query, t); } }
internal static List <T> Execute(string query) { using (SqlConnection connection = ConnectionDb.OpenConnection()) { return(connection.Query <T>(query).ToList()); } }
/// <summary> /// Get User List with short info /// </summary> /// <returns></returns> public async Task <List <UsersListViewModel> > GetUserListAsync() { return(await Task.Run(async() => { List <UsersListViewModel> result = new List <UsersListViewModel>(); ConnectionDb conn = new ConnectionDb(_config); using (IDbConnection sqlCon = conn.GetConnection) { string query = @" select u.id as UserId, u.UserName, u.Email as UserMail, r.name as UserRole from User u join UserRole ur on u.id = ur.UserId join Role r on r.id = ur.RoleId "; ConnectionDb.OpenConnect(sqlCon); var resultQuery = await sqlCon.QueryAsync <UsersListViewModel>(query); result = resultQuery.ToList(); } return result; })); }
/// <summary> /// Get all reserved events on week by the date /// </summary> /// <param name="orgId"></param> /// <param name="dateCal"></param> /// <param name="currUser"></param> /// <returns></returns> public async Task <List <ReservItemsViewModel> > GetOrgReservByWeek(int orgId, DateTime dateCal, string currUser) { List <ReservItemsViewModel> result = new List <ReservItemsViewModel>(); if (orgId == 0 || dateCal == DateTime.MinValue) { return(result); } DateTime dateFrom = new DateTime(dateCal.Year, dateCal.Month, 1); DateTime dateTo = dateFrom.AddMonths(1).AddDays(-1); await Task.Run(async() => { ConnectionDb conn = new ConnectionDb(_config); using (IDbConnection sqlCon = conn.GetConnection) { string query = @" select cl.orgId, ifnull(org.orgName,'') as orgName, ifnull(fn.userName,ow.userName) as staffName, case when cl.userId = @currUser then 1 else 0 end as isMy, cl.dateFrom, cl.dateTo, cl.isConfirm, cl.comment from orgReserve cl left join org on org.orgId = cl.orgId left join orgStaff st on st.orgStaffId = cl.orgStaffId left join user fn on fn.id = st.staffId left join user ow on ow.id = org.owner where cl.orgId = @orgId and dateFrom between @dateFrom and @dateTo "; // Ensure that the connection state is Open ConnectionDb.OpenConnect(sqlCon); var resultQuery = await sqlCon.QueryAsync <ReservItemsViewModel>( query, new { orgId = orgId, dateFrom = dateFrom, dateTo = dateTo, currUser = currUser }); result = resultQuery.ToList(); } }); return(result); }
public static ConnectionDb getInstance() { if (instance == null) { instance = new ConnectionDb(); } return(instance); }
public void Release() { if ((TransactionDb != null) || !IsOpened) { return; } ConnectionDb.Close(); IsOpened = false; }
public void Open() { if (IsOpened) { return; } ConnectionDb.Open(); IsOpened = true; }
public PostgreTransaction BeginTransaction() { Open(); TransactionDb = new PostgreTransaction(ConnectionDb.BeginTransaction()); TransactionDb.Disposed += TransactionDb_Disposed; TransactionDb.Commited += TransactionDb_Commited; TransactionDb.Rollbacked += TransactionDb_Rollbacked; return(TransactionDb); }
/// <summary> /// Get User Info with Owner Role /// User data, Organization data, Staff in organization data /// </summary> /// <param name="user"></param> /// <returns></returns> public async Task <EditOwnerViewModel> GetOwnerInfoAsync(User user) { if (user == null) { return(null); } return(await Task.Run(async() => { EditOwnerViewModel result = new EditOwnerViewModel() { Id = user.Id, Name = user.UserName, Email = user.Email, NewPassword = string.Empty, OldPassword = string.Empty, ConfirmNewPassword = string.Empty }; result.Orgs = _dbOrg.GetOrgsByOwner(user.Id); ConnectionDb conn = new ConnectionDb(_config); using (IDbConnection sqlCon = conn.GetConnection) { string query = @" select u.UserName, u.Email as emailUser, s.orgStaffId, s.isConfirm, s.orgId, s.staffId from org o join orgStaff s on s.orgId = o.orgId join user u on u.id = s.staffId where o.owner = @userId "; // Ensure that the connection state is Open ConnectionDb.OpenConnect(sqlCon); var resultQuery = await sqlCon.QueryAsync <OwnerOrgStaffViewModel>(query, new { userId = user.Id }); result.Staff = resultQuery.ToList(); } return result; })); }
protected ConnectionDb GetConnection() { if (_connDb == null) { lock (_lock) { if (_connDb == null) { _connDb = new ConnectionDb(); } } } return(_connDb); }
private ConnectionDb GetConnectionDb() { if (_connDb == null) { lock (_lock) { if (_connDb == null) { _connDb = new ConnectionDb(); } } } return(_connDb); }
/// <summary> /// Confirm Reserved Event /// </summary> /// <param name="userId"></param> /// <param name="resId"></param> /// <param name="isAdmin"></param> /// <returns></returns> public async Task ConfirmResEvent(string userId, int resId, bool isAdmin) { if (string.IsNullOrEmpty(userId) || resId == 0) { return; } int resultQuery = 0; await Task.Run(async() => { ConnectionDb conn = new ConnectionDb(_config); using (IDbConnection sqlCon = conn.GetConnection) { string query = @" select count(*) as kol from orgreserve r left join org on org.orgid = r.orgid left join orgstaff s on s.orgid = r.orgid and s.orgstaffid = r.orgstaffid where resid = 5 and (ifnull(org.owner,'') = @userId or ifnull(s.staffid,'') = @userId) "; // Ensure that the connection state is Open ConnectionDb.OpenConnect(sqlCon); resultQuery = await sqlCon.ExecuteScalarAsync <int>( query, new { resid = resId, userId = userId } ); } }); if (resultQuery != 0 || isAdmin) { var reserveItem = await _dbContext.OrgReserve.Where(w => w.resId == resId).FirstOrDefaultAsync(); if (reserveItem != null) { reserveItem.isConfirm = true; _dbContext.OrgReserve.Update(reserveItem); await _dbContext.SaveChangesAsync(); } } }
public void nonQuery(string Password, string Nonquery) { try { if (Password.Equals("123")) { ConnectionDb.ExecuteNonQuery(Nonquery); MessageBox.Show("La Accion se realizo correctamente!"); } else { throw new AccessException("La contraseña que ingreso no es correcta!"); } } catch (AccessException ex) { MessageBox.Show(ex.Message); } }
public JsonResult LoadBook() { try { using (ConnectionDb cd = new ConnectionDb()) { var draw = Request.Form.GetValues("draw").FirstOrDefault(); var start = Request.Form.GetValues("start").FirstOrDefault(); var length = Request.Form.GetValues("length").FirstOrDefault(); var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault(); var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault(); var searchValue = Request.Form.GetValues("search[value]").FirstOrDefault(); int pageSize = length != null?Convert.ToInt32(length) : 0; int skip = start != null?Convert.ToInt32(start) : 0; int recordTotal = 0; var bookData = (from b in db.Book select b); if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir))) { bookData = bookData.OrderBy(sortColumn + " " + sortColumnDir); } if (!string.IsNullOrEmpty(searchValue)) { bookData = bookData.Where(m => m.BookName == searchValue); } recordTotal = bookData.Count(); var data = bookData.Skip(skip).Take(pageSize).ToList(); return(Json(new { draw = draw, recordsFiltered = recordTotal, recordsTotal = recordTotal, data = data })); } } catch (Exception) { throw; } }
public async Task <List <AmortizationScheduleEntiti> > GetAllAmortizationScheduleAsync() { var response = await ConnectionDb.QueryAsync <AmortizationScheduleEntiti>(sql : QueriesResources.GetAmortizationSchedule, commandType : CommandType.Text); return(response.ToList()); }
public async Task <AmortizationScheduleEntiti> GetAmortizationScheduleAsync(uint amortizationScheduleId) { return(await ConnectionDb.QueryFirstOrDefaultAsync <AmortizationScheduleEntiti>(QueriesResources.GetAmortizationScheduleById, new { AmortizationScheduleId = (int)amortizationScheduleId }, commandType : CommandType.Text)); }
public void Dispose() { ConnectionDb?.Dispose(); TransactionDb?.Dispose(); }
public DataTable query(string query) { return(ConnectionDb.ExecuteQuery(query)); }
/// <summary> /// Get User calendar reserved items /// </summary> /// <param name="userId">user id</param> /// <param name="dateCal">calendar date</param> /// <returns></returns> /// <exception cref="NotImplementedException"></exception> public async Task <List <UserCalendarViewModel> > GetUserCalendar(string userId, DateTime dateCal) { List <UserCalendarViewModel> result = new List <UserCalendarViewModel>(); if (string.IsNullOrEmpty(userId) || dateCal == DateTime.MinValue) { return(result); } DateTime dateFrom = new DateTime(dateCal.Year, dateCal.Month, 1); DateTime dateTo = dateFrom.AddMonths(1).AddDays(-1); await Task.Run(async() => { ConnectionDb conn = new ConnectionDb(_config); using (IDbConnection sqlCon = conn.GetConnection) { string query = @" select cl.resId, case when ifnull(ow.id,'') = @userId then 1 else 0 end as isOwner, cl.orgId, ifnull(org.orgName,'') as orgName, ifnull(fn.userName,ow.userName) as staffName, cl.dateFrom, cl.dateTo, cl.isConfirm, cl.comment from orgReserve cl left join org on org.orgId = cl.orgId left join orgStaff st on st.orgStaffId = cl.orgStaffId left join user fn on fn.id = st.staffId left join user ow on ow.id = org.owner where dateFrom between @dateFrom and @dateTo and ( ifnull(cl.userId,'') = @userId or ifnull(fn.id,'') = @userId or ifnull(ow.id,'') = @userId ) "; // Ensure that the connection state is Open ConnectionDb.OpenConnect(sqlCon); var resultQuery = await sqlCon.QueryAsync <UserCalendarViewModel>( query, new { dateFrom = dateFrom, dateTo = dateTo, userId = userId }); result = resultQuery.ToList(); } }); return(result); }