/// <summary> /// Updates an Offence record inside the Event-Guardian database, using the ID and values provided. /// </summary> /// <param name="id">ID indicating the Offence record to be updated</param> /// <param name="isDeleted">New value for the IsDeleted column</param> /// <param name="isVisible">New value for the IsVisible column</param> /// <param name="name">New value for the Name column</param> /// <param name="offenceCategoryID">The ID of the corresponding offence category.</param> public void UpdateOffence(int id, bool isDeleted, bool isVisible, string name, int?offenceCategoryID) { using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { SqlMapper.Execute(sqlConnection, CrimeSpUpdateOffence, new { ID = id, IsDeleted = isDeleted, IsVisible = isVisible, Name = name, OffenceCategoryID = offenceCategoryID }, commandType: CommandType.StoredProcedure); } }
/// <summary> /// Gets all crime information from the database. /// </summary> /// <returns>Returns a list of Crime objects representing the result of the operation.</returns> public List <CrimeOffenceLocalGovernmentAreaState> GetCrimeOffenceLocalGovernmentAreaStates(int skip, int take, string orderBy, string sortDirection) { List <CrimeOffenceLocalGovernmentAreaState> crimeOffenceLocalGovernmentAreaStates = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { crimeOffenceLocalGovernmentAreaStates = SqlMapper .Query(sqlConnection, CrimeSpGetCrimeOffenceLocalGovernmentAreaState, new { Skip = skip, Take = take, OrderBy = orderBy, SortDirection = sortDirection }, commandType: CommandType.StoredProcedure) .Select(m => new CrimeOffenceLocalGovernmentAreaState ( m.Count, m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.LocalGovernmentAreaID, m.LocalGovernmentAreaName, m.Month, m.OffenceID, m.OffenceName, m.StateID, m.StateName, m.Year )) .ToList(); } return(crimeOffenceLocalGovernmentAreaStates); }
/// <summary> /// Selects crime information from the underlying data source. /// </summary> /// <param name="id">The ID of the associated crime information.</param> /// <returns>Returns a Crime object representing the result of the operation.</returns> public CrimeOffenceLocalGovernmentAreaState GetCrimeOffenceLocalGovernmentAreaStateByID(int id) { CrimeOffenceLocalGovernmentAreaState crimeOffenceLocalGovernmentAreaState = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { crimeOffenceLocalGovernmentAreaState = SqlMapper .Query(sqlConnection, CrimeSpGetCrimeOffenceLocalGovernmentAreaStateByID, new { ID = id }, commandType: CommandType.StoredProcedure) .Select(m => new CrimeOffenceLocalGovernmentAreaState ( m.Count, m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.LocalGovernmentAreaID, m.LocalGovernmentAreaName, m.Month, m.OffenceID, m.OffenceName, m.StateID, m.StateName, m.Year )) .FirstOrDefault(); } return(crimeOffenceLocalGovernmentAreaState); }
/// <summary> /// Updates local government area information in the underlying data source. /// </summary> /// <param name="id">The ID of the associated local government area.</param> /// <param name="isDeleted">Specifies whether the local government area is flagged as deleted.</param> /// <param name="isVisible">Specifies whether the local government area is flagged as visible.</param> /// <param name="name">Specifies the name of the local government area.</param> /// <param name="stateID">Specifies the ID of the associated state.</param> public void UpdateLocalGovernmentArea(int id, bool isDeleted, bool isVisible, string name, int stateID) { using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { SqlMapper.Execute(sqlConnection, LocationSpUpdateLocalGovernmentArea, new { ID = id, IsDeleted = isDeleted, IsVisible = isVisible, Name = name, StateID = stateID }, commandType: CommandType.StoredProcedure); } }
/// <summary> /// Updates state or territory information in the underlying data source. /// </summary> /// <param name="id">The ID of the associated state or territory.</param> /// <param name="abbreviatedName">The state or territory name in abbreviated form.</param> /// <param name="isDeleted">Specifies whether the offence category is flagged as deleted.</param> /// <param name="isVisible">Specifies whether the offence category is flagged as visible.</param> /// <param name="name">The name of the state or territory.</param> public void UpdateState(int id, string abbreviatedName, bool isDeleted, bool isVisible, string name) { using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { SqlMapper.Execute(sqlConnection, "Location.spUpdateState", new { ID = id, AbbreviatedName = abbreviatedName, IsDeleted = isDeleted, IsVisible = isVisible, Name = name }, commandType: CommandType.StoredProcedure); } }
/// <summary> /// Executes SQL against the underlying data source. /// </summary> /// <param name="sql">The SQL to execute.</param> /// <param name="commandTimeout">Specifies the amount of time in seconds the command is permitted to wait before throwing an exception.</param> /// <returns>Returns the number of rows affected</returns> public int ExecuteScript(string sql, int commandTimeout) { int executeScript = 0; if (String.IsNullOrEmpty(sql) == false) { try { using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { SqlMapper.Execute(sqlConnection, sql, commandType: CommandType.Text, commandTimeout: commandTimeout); } } catch (Exception exception) { OutputStreams.WriteLine(); OutputStreams.WriteLine(exception.Message); OutputStreams.WriteLine(); OutputStreams.WriteLine(sql); OutputStreams.WriteLine(); } } return(executeScript); }
/// <summary> /// Selects crime information from the underlying data source. /// </summary> /// <param name="latitude">Specifies the latitude used to look up the corresponding local government area.</param> /// <param name="longitude">Specifies the longitude used to look up the corresponding local government area.</param> /// <returns>Returns a list of Crime objects representing the result of the operation.</returns> public List <CrimeByCoordinate> GetCrimesByCoordinate(double latitude, double longitude) { List <CrimeByCoordinate> crimeByCoordinates = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { crimeByCoordinates = SqlMapper .Query(sqlConnection, CrimeSpGetCrimesByCoordinate, commandType: CommandType.StoredProcedure, param: new { Latitude = latitude, Longitude = longitude }) .Select(m => new CrimeByCoordinate(m.BeginYear, m.EndYear, m.Name, m.OffenceCount, m.OffenceID, m.Offence)) .ToList(); } return(crimeByCoordinates); }
/// <summary> /// Selects all users from the underlying data source. /// </summary> /// <returns>Returns a list of User objects representing the result of the operation.</returns> public List <User> GetUsers() { List <User> users = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { users = SqlMapper .Query(sqlConnection, RegistrationSpGetUser, commandType: CommandType.StoredProcedure) .Select(m => new User(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsActive, m.IsDeleted, m.Password, m.Username)) .ToList(); } return(users); }
/// <summary> /// Selects a user from the underlying data souce. /// </summary> /// <param name="username">The username of theassociated user.</param> /// <returns>Returns a User object representing the result of the operation.</returns> public User GetUserByUsername(string username) { User user = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { user = SqlMapper .Query(sqlConnection, RegistrationSpGetUserByUsername, new { Username = username }, commandType: CommandType.StoredProcedure) .Select(m => new User(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsActive, m.IsDeleted, m.Password, m.Username)) .FirstOrDefault(); } return(user); }
/// <summary> /// Retreives a full list of Offenses inside the Event-Guardian database. /// </summary> /// <returns>A List of Offence records</returns> public List <Offence> GetOffences() { List <Offence> offences = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { offences = SqlMapper .Query(sqlConnection, CrimeSpGetOffence, commandType: CommandType.StoredProcedure) .Select(m => new Offence(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name, m.OffenceCategoryID)) .ToList(); } return(offences); }
/// <summary> /// Searches the Event-Guardian database for an Offence record with a specified Offence ID. /// </summary> /// <param name="id">Identification number for the Offence record</param> /// <returns>An Offence record with the specified ID, or null if no matches were found</returns> public Offence GetOffenceByID(int id) { Offence offence = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { offence = SqlMapper .Query(sqlConnection, "Crime.spGetOffenceByID", new { ID = id }, commandType: CommandType.StoredProcedure) .Select(m => new Offence(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name, m.OffenceCategoryID)) .FirstOrDefault(); } return(offence); }
/// <summary> /// Selects state or territory information from the underlying data source. /// </summary> /// <param name="id">The ID of the corresponding state or territory information.</param> /// <returns>Returns a State object representing the result of the operation.</returns> public State GetStateByID(int id) { State state = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { state = SqlMapper .Query(sqlConnection, "Location.spGetStateByID", new { ID = id }, commandType: CommandType.StoredProcedure) .Select(m => new State(m.AbbreviatedName, m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name)) .FirstOrDefault(); } return(state); }
/// <summary> /// Selects all local government area and state or territory information from the underlying data source. /// </summary> /// <returns>Returns a list of LocalGovernmentAreaState objects representing the result of the operation.</returns> public List <LocalGovernmentAreaState> GetLocalGovernmentAreaStates() { List <LocalGovernmentAreaState> localGovernmentAreaStates = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { localGovernmentAreaStates = SqlMapper .Query(sqlConnection, LocationSpGetLocalGovernmentAreaState, commandType: CommandType.StoredProcedure) .Select(m => new LocalGovernmentAreaState(m.AbbreviatedName, m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name, m.StateID, m.StateName)) .ToList(); } return(localGovernmentAreaStates); }
/// <summary> /// Selects local government area information from the underlying data source. /// </summary> /// <param name="stateID">The ID of the associated state that the local government area resides in.</param> /// <returns>Returns a list of LocalGovernmentArea objects representing the result of the operation.</returns> public List <LocalGovernmentArea> GetLocalGovernmentAreasByStateID(int stateID) { List <LocalGovernmentArea> localGovernmentAreas = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { localGovernmentAreas = SqlMapper .Query(sqlConnection, LocationSpGetLocalGovernmentAreasByStateID, new { StateID = stateID }, commandType: CommandType.StoredProcedure) .Select(m => new LocalGovernmentArea(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name, m.StateID)) .ToList(); } return(localGovernmentAreas); }
/// <summary> /// Selects local government area information from the underlying data source. /// </summary> /// <param name="id">The ID of the associated local government area.</param> /// <returns>Returns a LocalGovernmentArea object representing the result of the operation.</returns> public LocalGovernmentArea GetLocalGovernmentAreaByID(int id) { LocalGovernmentArea localGovernmentArea = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { localGovernmentArea = SqlMapper .Query(sqlConnection, LocationSpGetLocalGovernmentAreaByID, new { ID = id }, commandType: CommandType.StoredProcedure) .Select(m => new LocalGovernmentArea(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name, m.StateID)) .FirstOrDefault(); } return(localGovernmentArea); }
/// <summary> /// Inserts local government area information into the underlying data source. /// </summary> /// <param name="isDeleted">Specifies whether the local government area is flagged as deleted.</param> /// <param name="isVisible">Specifies whether the local government area is flagged as visible.</param> /// <param name="name">Specifies the name of the local government area.</param> /// <param name="stateID">Specifies the ID of the associated state.</param> /// <returns>Returns the newly created ID for a successful operation, otherwise returns 0.</returns> public int AddLocalGovernmentArea(bool isDeleted, bool isVisible, string name, int stateID) { int id = 0; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { id = (int)SqlMapper .Query(sqlConnection, LocationSpAddLocalGovernmentArea, new { IsDeleted = isDeleted, IsVisible = isVisible, Name = name, StateID = stateID }, commandType: CommandType.StoredProcedure) .Select(m => m.NewID) .Single(); } return(id); }
/// <summary> /// Inserts crime information into the underlying data source. /// </summary> /// <param name="count">Specifies the count of the offences.</param> /// <param name="localGovernmentAreaID">Specified the ID of the associated local government area.</param> /// <param name="month">Specifies the month of the crime.</param> /// <param name="offenceID">Specifies the ID of the associated offence.</param> /// <param name="year">Specifies the year of the crime.</param> /// <returns></returns> public int AddCrime(int count, int localGovernmentAreaID, int month, int offenceID, int year) { int id = 0; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { id = (int)SqlMapper .Query(sqlConnection, CrimeSpAddCrime, new { Count = count, LocalGovernmentAreaID = localGovernmentAreaID, Month = month, OffenceID = offenceID, Year = year }, commandType: CommandType.StoredProcedure) .Select(m => m.NewID) .Single(); } return(id); }
/// <summary> /// Inserts a new record inside the Offence table of the Event-Guardian database. /// </summary> /// <param name="isDeleted">Indicates whether or not the record is deleted</param> /// <param name="isVisible">Indicates whether or not the record should be shown in the user interfaces</param> /// <param name="name">The name of the offense</param> /// <returns>Offence ID number</returns> public int AddOffence(bool isDeleted, bool isVisible, string name) { int id = 0; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { id = (int)SqlMapper .Query(sqlConnection, CrimeSpAddOffence, new { IsDeleted = isDeleted, IsVisible = isVisible, Name = name }, commandType: CommandType.StoredProcedure) .Select(m => m.NewID) .Single(); } return(id); }
/// <summary> /// Selects all state or territory information from the underlying data source. /// </summary> /// <returns>Returns a list of State objects representing the result of the operation.</returns> public List <State> GetStates() { List <State> states = null; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { states = SqlMapper .Query(sqlConnection, "Location.spGetState", commandType: CommandType.StoredProcedure) .Select(m => new State(m.AbbreviatedName, m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name)) .ToList(); } return(states); }
/// <summary> /// Inserts state or territory information into the underlying data source. /// </summary> /// <param name="abbreviatedName">The state or territory name in abbreviated form.</param> /// <param name="isDeleted">Specifies whether the offence category is flagged as deleted.</param> /// <param name="isVisible">Specifies whether the offence category is flagged as visible.</param> /// <param name="name">The name of the state or territory.</param> /// <returns></returns> public int AddState(string abbreviatedName, bool isDeleted, bool isVisible, string name) { int id = 0; using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { id = (int)SqlMapper .Query(sqlConnection, LocationSpAddState, new { AbbreviatedName = abbreviatedName, IsDeleted = isDeleted, IsVisible = isVisible, Name = name }, commandType: CommandType.StoredProcedure) .Select(m => m.NewID) .Single(); } return(id); }
/// <summary> /// Updates crime information in the underlying data source. /// </summary> /// <param name="id">The ID of the associated crime.</param> /// <param name="localGovernmentAreaID">Specified the ID of the associated local government area.</param> /// <param name="offenceID">Specifies the ID of the associated offence.</param> /// <param name="count">Specifies the count of the offences.</param> /// <param name="month">Specifies the month of the crime.</param> /// <param name="year">Specifies the year of the crime.</param> /// <param name="isDeleted">Specifies whether the crime information is flagged as deleted.</param> /// <param name="isVisible">Specifies whether the crime information is flagged as visible.</param> public void UpdateCrime(int id, int localGovernmentAreaID, int offenceID, int count, int month, int year, bool isDeleted, bool isVisible) { using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { SqlMapper.Execute(sqlConnection, CrimeSpUpdateCrime, new { ID = id, LocalGovernmentAreaID = localGovernmentAreaID, OffenceID = offenceID, Count = count, Month = month, Year = year, IsDeleted = isDeleted, IsVisible = isVisible, }, commandType: CommandType.StoredProcedure); } }