public Case GetCustomersCase(int id, string mail) { try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@id", id, DbType.Int32); dal.AddParameter("@mail", mail, DbType.String); DataTable dt = dal.ExecuteDataTable("SELECT * FROM [case] WHERE [id] = @id AND [customer_id] = (SELECT [id] FROM [vwCustomer] WHERE [mail] = @mail)"); dal.ClearParameters(); if (dt.Rows.Count != 1) { throw new DoesNotExistException(typeof(Case)); } else { return(MapCase(dt.Rows[0])); } } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public IEnumerable <Category> GetCategories(bool fullAssociation = false) { try { DataTable dt = new DataAccessLayer().ExecuteDataTable("SELECT * FROM category"); return(dt.AsEnumerable().Select(r => MapCategory(r, fullAssociation))); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public IEnumerable <Case> GetCases() { try { DataAccessLayer dal = new DataAccessLayer(); DataTable dt = dal.ExecuteDataTable("SELECT * FROM [case]"); return(dt.AsEnumerable().Select(r => MapCase(r))); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public IEnumerable <Supporter> GetSupporters() { try { DataAccessLayer dal = new DataAccessLayer(); DataTable dt = dal.ExecuteDataTable("SELECT * FROM [vwSupporter]"); return(dt.AsEnumerable().Select(r => MapSupporter(r))); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public void CaseChangeStatus(int caseId, int statusId, int supporterId) { try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@caseId", caseId, DbType.Int32); dal.AddParameter("@statusId", statusId, DbType.Int32); dal.AddParameter("@editingSupporterId", supporterId, DbType.Int32); dal.ExecuteStoredProcedure("CaseChangeStatus"); dal.ClearParameters(); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public void CreateComment(int caseId, int personId, string text) { try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@caseId", caseId, DbType.Int32); dal.AddParameter("@personId", personId, DbType.Int32); dal.AddParameter("@text", text, DbType.String); dal.ExecuteStoredProcedure("CommentCreate"); dal.ClearParameters(); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public IEnumerable <Comment> GetComments(int caseId) { try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@caseId", caseId, DbType.Int32); DataTable dt = dal.ExecuteDataTable("SELECT * FROM [comment] WHERE [case_id] = @caseId"); dal.ClearParameters(); return(dt.AsEnumerable().Select(r => MapComment(r))); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public IEnumerable <Subcategory> GetSubcategories(int categoryId) { try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@category_id", categoryId, DbType.Int32); DataTable dt = dal.ExecuteDataTable("SELECT * FROM subcategory WHERE [category_id] = @category_id"); dal.ClearParameters(); return(dt.AsEnumerable().Select(r => MapSubcategory(r))); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public Category GetCategoryForSubcategory(int subcategoryId, bool fullAssociation = false) { try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@subcategoryId", subcategoryId, DbType.Int32); DataTable dt = dal.ExecuteDataTable("SELECT * FROM [category] WHERE [id] = (SELECT [category_id] FROM [subcategory] WHERE [subcategory].id = @subcategoryId)"); dal.ClearParameters(); return(dt.Rows.Count == 1 ? MapCategory(dt.Rows[0], fullAssociation) : null); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public void CaseEdit(Case c, int editingSupporterId) { try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@caseId", c.Id, DbType.Int32); dal.AddParameter("@description", c.Description, DbType.String); dal.AddParameter("@priority", c.Priority, DbType.Int32); dal.AddParameter("@subcategoryId", c.Subcategory.Id, DbType.Int32); dal.AddParameter("@operatingSystem", c.OperatingSystem, DbType.String); //The editing supporter. Not the supporter assigned to the case. dal.AddParameter("@editingSupporterId", editingSupporterId, DbType.Int32); dal.ExecuteStoredProcedure("CaseEdit"); dal.ClearParameters(); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public IEnumerable <Case> GetCasesForSupporter(int supporterId) { //No reason to query DB if we know that no records exist if (supporterId <= 0) { return(new Case[0]); } try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@supporterId", supporterId, DbType.Int32); DataTable dt = dal.ExecuteDataTable("SELECT * FROM [case] WHERE [supporter_id] = @supporterId"); dal.ClearParameters(); return(dt.AsEnumerable().Select(r => MapCase(r))); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public IEnumerable <Case> GetCasesForCustomer(string mail) { //No reason to query DB if we know that no records exist if (String.IsNullOrEmpty(mail)) { return(new Case[0]); } try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@mail", mail, DbType.String); DataTable dt = dal.ExecuteDataTable("SELECT * FROM [case] WHERE [customer_id] = (SELECT [Id] FROM [vwCustomer] WHERE [mail] = @mail)"); dal.ClearParameters(); return(dt.AsEnumerable().Select(r => MapCase(r))); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public int CaseCreate(Case c) { try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@description", c.Description, DbType.String); dal.AddParameter("@priority", c.Priority, DbType.String); dal.AddParameter("@subcategoryId", c.Subcategory.Id, DbType.Int32); dal.AddParameter("@operatingSystem", c.OperatingSystem, DbType.String); dal.AddParameter("@customerId", c.Customer.Id, DbType.Int32); dal.AddParameter("@newId", null, DbType.Int32, ParameterDirection.Output); dal.ExecuteStoredProcedure("CaseCreate"); int newId = Int32.Parse(dal.GetParameter("@newId").Value.ToString()); dal.ClearParameters(); return(newId); } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public Person GetPerson(int id) { try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@id", id, DbType.Int32); DataTable dt = dal.ExecuteDataTable("SELECT * FROM [vwPerson] WHERE [id] = @id"); dal.ClearParameters(); if (dt.Rows.Count == 1) { return(MapPerson(dt.Rows[0])); } else { throw new DoesNotExistException(typeof(Person)); } } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }
public Customer GetCustomer(string mail) { try { DataAccessLayer dal = new DataAccessLayer(); dal.AddParameter("@mail", mail, DbType.String); DataTable dt = dal.ExecuteDataTable("SELECT * FROM [vwCustomer] WHERE [mail] = @mail"); dal.ClearParameters(); if (dt.Rows.Count == 1) { return(MapCustomer(dt.Rows[0])); } else { throw new DoesNotExistException(typeof(Customer)); } } catch (Exception ex) { throw DataExceptionCheck.CheckException(ex); } }