protected bool Update <T>(T obj) where T : class { using (var context = PODFactory.GetContext(true)) { return(context.Database.Connection.Update <T>(obj)); } }
public void Edit(Organization customer) { string adminUserName = GetByID <User>(customer.AdminUser.ID).UserName; //admmin hasnt been changed if (String.Compare(customer.AdminUser.UserName, adminUserName, true) == 0) { string sql = @"UPDATE ""Organization"" SET OrganizationName = @OrganizationName WHERE Id = @Id"; base.Execute(sql, new { OrganizationName = customer.OrganizationName, ID = customer.ID }); } //admin user has been changed else { try { DynamicParameters param = new DynamicParameters(); param.Add("@CustomerID", customer.ID, DbType.Guid, direction: ParameterDirection.Input); param.Add("@CustomerName", customer.OrganizationName, DbType.String, direction: ParameterDirection.Input); param.Add("@AdminID", customer.AdminUser.ID, DbType.Guid, direction: ParameterDirection.Input); param.Add("@RoleID", customer.AdminUser.Role.ID, DbType.Guid, direction: ParameterDirection.Input); using (var context = PODFactory.GetContext(true)) { var multi = context.Database.Connection.QueryMultiple("[EditOrganization]", param, commandType: CommandType.StoredProcedure); } } catch (Exception e) { throw; } } }
protected int Execute <T>(string sql, T obj) where T : class { using (var context = PODFactory.GetContext(true)) { return(context.Database.Connection.Execute(sql, obj)); } }
protected IEnumerable <T> GetList <T>(object predicate) where T : class { using (var context = PODFactory.GetContext(true)) { return(context.Database.Connection.GetList <T>(predicate)); } }
public Organization GetOrganizationWithAdminUser(Guid ID) { try { var param = new DynamicParameters(); param.Add("@OrganizationID", ID, dbType: DbType.Guid, direction: ParameterDirection.Input); using (var context = PODFactory.GetContext(true)) { var multi = context.Database.Connection.QueryMultiple("[GetOrganizationByID]", param, commandType: CommandType.StoredProcedure); Organization org = multi.Read <Organization>().FirstOrDefault(); if (org != null) { User user = multi.Read <User>().FirstOrDefault(); org.AdminUser = user; } return(org); } } catch (Exception ex) { _loggingManager.Log.Error(ex, ex.Source); throw; } }
protected Guid Insert <T>(T obj) where T : class { using (var context = PODFactory.GetContext(true)) { return(context.Database.Connection.Insert <T>(obj)); } }
public List <Organization> GetCustomers(Guid currentOrganizationID, string accountNumber, string accountName, string adminUser, string userName, string statusName, DateTime?lastLoginDate, int currentPageNumber, int pageSize, string sortExpression, string sortDirection, out int totalRows) { totalRows = 0; try { List <Organization> customers = new List <Organization>(); var offset = (currentPageNumber - 1) * pageSize; var param = new DynamicParameters(); param.Add("@ParentOrganizationID", currentOrganizationID, dbType: DbType.Guid, direction: ParameterDirection.Input); param.Add("@AccountNumber", accountNumber, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@AccountName", accountName, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@AdminUser", adminUser, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@UserName", userName, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@StatusName", statusName, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@LastLoginDate", lastLoginDate, dbType: DbType.DateTime, direction: ParameterDirection.Input); param.Add("@Offset", offset, dbType: DbType.Int32, direction: ParameterDirection.Input); param.Add("@PageSize", pageSize, dbType: DbType.Int32, direction: ParameterDirection.Input); param.Add("@SortExpression", sortExpression, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@SortDirection", sortDirection, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.Output); using (var context = PODFactory.GetContext(true)) { var multi = context.Database.Connection.QueryMultiple("[GetOrganizations]", param, commandType: CommandType.StoredProcedure); customers = multi.Read <Organization, User, Organization>((a, g) => { a.AdminUser = g; return(a); }, splitOn: "UserName").ToList(); totalRows = param.Get <int>("RowCount"); } if (customers == null || customers.Count == 0) { return(customers); } //foreach (Organization organization in customers) //{ // var customerRole = customerRoles.FirstOrDefault(cr => cr.OrganizationID == organization.ID); // if (customerRole != null) // { // organization.AdminUser = users.FirstOrDefault(u => u.ID == customerRole.UserID); // }; //} return(customers); } catch (Exception e) { throw; } }
//public Organization GetCustomerByID(Guid id) //{ // Organization customer = null; // try // { // // Basic DapperExtensions read // // customer = get.<Organization>(new { ID = id }); // //if (res.Count() == 0) // //{ // // // Not found - return null // // return null; // //} // //else if (res.Count() > 1) // //{ // // throw new InvalidOperationException("Get by username returned more than 1 user for username: "******"SELECT * from [Organization] Where AccountNumber='" + accountNumber + "'"; using (var context = PODFactory.GetContext(true)) { var result = context.Database.Connection.Query <Organization>(sql).FirstOrDefault(); if (result != null) { return(result); } } return(null); }
protected T GetByID <T>(Guid id) where T : class { try { using (var context = PODFactory.GetContext(true)) { return(context.Database.Connection.Get <T>(id)); } } catch (Exception ex) { return(null); } }
public Ticket GetTicket(int ticketNumber) { string sql = "SELECT * from [Ticket] Where TicketNumber='" + ticketNumber + "'"; List <Ticket> tickets = new List <Ticket>(); using (var context = PODFactory.GetContext(true)) { var result = context.Database.Connection.Query <Ticket>(sql).FirstOrDefault(); if (result != null) { return(result); } return(null); } }
public IEnumerable <User> GetUsers(Guid organizationID, string organizationName, string fullName, string userName, string roleName, string statusName, DateTime?lastLoginDate, int currentPageNumber, int pageSize, string sortExpression, string sortDirection, out int totalRows) { try { List <User> users = new List <User>(); var offset = (currentPageNumber - 1) * pageSize; var param = new DynamicParameters(); param.Add("@OrganizationID", organizationID, dbType: DbType.Guid, direction: ParameterDirection.Input); param.Add("@OrganizationName", organizationName, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@FullName", fullName, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@UserName", userName, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@RoleName", roleName, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@StatusName", statusName, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@LastLoginDate", lastLoginDate, dbType: DbType.DateTime, direction: ParameterDirection.Input); param.Add("@Offset", offset, dbType: DbType.Int32, direction: ParameterDirection.Input); param.Add("@PageSize", pageSize, dbType: DbType.Int32, direction: ParameterDirection.Input); param.Add("@SortExpression", sortExpression, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@SortDirection", sortDirection, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.Output); using (var context = PODFactory.GetContext(true)) { var multi = context.Database.Connection.QueryMultiple("[GetUsers]", param, commandType: CommandType.StoredProcedure); users = multi.Read <User, UserRole, UserOrganization, User>((u, r, o) => { u.Role = r; u.Organization = o; return(u); }, splitOn: "IsPrimary, OrganizationName").ToList(); totalRows = param.Get <int>("RowCount"); } if (users == null || users.Count == 0) { // Not found return(users); } return(users); } catch (Exception ex) { _loggingManager.Log.Error(ex, ex.Source); throw; } }
public int Delete(Guid userId) // { try { var param = new DynamicParameters(); param.Add("@UserID", userId, dbType: DbType.Guid, direction: ParameterDirection.Input); using (var context = PODFactory.GetContext(true)) { int result = context.Database.Connection.Execute("[DeleteUser]", param, commandType: CommandType.StoredProcedure); return(result); } } catch (Exception ex) { _loggingManager.Log.Error(ex, ex.Source); throw; } }
public Organization GetCustomerByName(string name) { try { var param = new DynamicParameters(); param.Add("@OrganizationName", name, DbType.String, direction: ParameterDirection.Input); using (var context = PODFactory.GetContext(true)) { var multi = context.Database.Connection.QueryMultiple("[GetOrganizationByName]", param, commandType: CommandType.StoredProcedure); Organization org = multi.Read <Organization, User, Organization>((o, u) => { o.AdminUser = u; return(o); }, splitOn: "FullName").FirstOrDefault(); return(org); } } catch (Exception e) { throw; } }
public IEnumerable <Ticket> GetTickets(Guid organizationID, string accontNumber, string accountName, int?ticketNumber, DateTime?deliveryDate, string invoiceNumber, int currentPageNumber, int pageSize, string sortExpression, string sortDirection, out int totalRows) { try { //test pageSize = 15; currentPageNumber = 1; List <Ticket> tickets = new List <Ticket>(); var offset = (currentPageNumber - 1) * pageSize; var param = new DynamicParameters(); param.Add("@OrganizationID", organizationID, dbType: DbType.Guid, direction: ParameterDirection.Input); param.Add("@AccontNumber", accontNumber, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@AccountName", accountName, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@TicketNumber", ticketNumber, dbType: DbType.Int32, direction: ParameterDirection.Input); param.Add("@DeliveryDate", deliveryDate, dbType: DbType.DateTime, direction: ParameterDirection.Input); param.Add("@InvoiceNumber", invoiceNumber, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@Offset", offset, dbType: DbType.Int32, direction: ParameterDirection.Input); param.Add("@PageSize", pageSize, dbType: DbType.Int32, direction: ParameterDirection.Input); param.Add("@SortExpression", sortExpression, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@SortDirection", sortDirection, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.Output); using (var context = PODFactory.GetContext(true)) { var query = context.Database.Connection.QueryMultiple("[GetTickets]", param, commandType: CommandType.StoredProcedure); tickets = query.Read <Ticket>().ToList(); totalRows = param.Get <int>("RowCount"); } return(tickets); } catch (Exception ex) { _loggingManager.Log.Error(ex, ex.Source); throw; } }
public List <Organization> GetOrganizationsByParentID(Guid parentOrganization) { try { var param = new DynamicParameters(); param.Add("@ParentOrganization", parentOrganization, DbType.Guid, direction: ParameterDirection.Input); using (var context = PODFactory.GetContext(true)) { var multi = context.Database.Connection.QueryMultiple("[GetOrganizationsByParentID]", param, commandType: CommandType.StoredProcedure); List <Organization> orgs = multi.Read <Organization>().ToList(); //if (orgs != null) //{ //} return(orgs); } } catch (Exception e) { throw; } }
public void Update(User user) // void { try { using (var context = PODFactory.GetContext(true)) { var param = new DynamicParameters(); param.Add("@UserID", user.ID, dbType: DbType.Guid, direction: ParameterDirection.Input); param.Add("@FullName", user.FullName, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@RoleID", user.Role.RoleID, dbType: DbType.Guid, direction: ParameterDirection.Input); param.Add("@IsPrimary", user.Role.IsPrimary, dbType: DbType.Boolean, direction: ParameterDirection.Input); param.Add("@StatusID", user.StatusID, dbType: DbType.Int32, direction: ParameterDirection.Input); context.Database.Connection.Execute("[dbo].[UpdateUser]", param, commandType: CommandType.StoredProcedure); } } catch (Exception ex) { _loggingManager.Log.Error(ex, ex.Source); throw; } }
public User GetUserByUsername(string username) { try { var param = new DynamicParameters(); param.Add("@Username", username, dbType: DbType.String, direction: ParameterDirection.Input); using (var context = PODFactory.GetContext(true)) { var multi = context.Database.Connection.QueryMultiple("[GetUserByUsername]", param, commandType: CommandType.StoredProcedure); User user = multi.Read <User, UserRole, UserOrganization, User>((u, r, o) => { u.Role = r; u.Organization = o; return(u); }, splitOn: "RoleID, OrganizationID").FirstOrDefault(); return(user); } } catch (Exception ex) { _loggingManager.Log.Error(ex, ex.Source); throw; } }
public IEnumerable <Report> GetReports(Guid userID, int?reportTypeID, string details, int currentPageNumber, int pageSize, string sortExpression, string sortDirection, out int totalRows) { try { //test pageSize = 15; currentPageNumber = 1; List <Report> reports = new List <Report>(); var offset = (currentPageNumber - 1) * pageSize; var param = new DynamicParameters(); param.Add("@UserID", userID, dbType: DbType.Guid, direction: ParameterDirection.Input); param.Add("@ReportTypeID", reportTypeID, dbType: DbType.Int32, direction: ParameterDirection.Input); param.Add("@Details", details, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@Offset", offset, dbType: DbType.Int32, direction: ParameterDirection.Input); param.Add("@PageSize", pageSize, dbType: DbType.Int32, direction: ParameterDirection.Input); param.Add("@SortExpression", sortExpression, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@SortDirection", sortDirection, dbType: DbType.String, direction: ParameterDirection.Input); param.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.Output); using (var context = PODFactory.GetContext(true)) { var query = context.Database.Connection.QueryMultiple("[GetReports]", param, commandType: CommandType.StoredProcedure); reports = query.Read <Report>().ToList(); totalRows = param.Get <int>("RowCount"); } return(reports); } catch (Exception ex) { _loggingManager.Log.Error(ex, ex.Source); throw; } }
/// <summary> /// Load your modules or register your services here! /// </summary> /// <param name="kernel">The kernel.</param> private static void RegisterServices(IKernel kernel) { //kernel.Bind<POD.Interfaces.ICustomerDataService>().To<POD.Data.CustomerDataService>(); kernel.Bind <POD.Interfaces.ICustomerDataService>().To <POD.Data.Services.CustomerDataService>() .WithConstructorArgument("context", PODFactory.GetContext(true)); }