Action Accessor

ActionAccessor.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Accessors.Helpers;
using Core.Interfaces.Accessors;
using Core.Interfaces.Helpers;
using Action = Models.Action;

namespace Accessors.Accessors
{
    /// <summary>
    /// Accesses action data
    /// </summary>
    public class ActionAccessor : IActionAccessor
    {
        private IDataTransformationHelper dataTransformer; // Data tranformation object that will be used for building concrete objects out of sql reader entries
        private readonly string connectionString; // Connection string to datababse set from the Config file

        public ActionAccessor(string connectionString)
        {
            // Set up private resources
            this.dataTransformer = new DataTransformationHelper();
            this.connectionString = connectionString;
        }

        public Action GetById(int id)
        {
            // Check for invalid ID
            if(id<1)
            {
                return default(Action);
            }
            // Set up SQL command string
            string sql = "SELECT * FROM [dbo].[Action] WHERE [dbo].[Action].[id]=@id;";

            // Set up sql connection and command
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // Add values for the paramaters in the SQL command String
                    command.Parameters.AddWithValue("@id", id);

                    // Run the command
                    connection.Open();
                    SqlDataReader dataReader = command.ExecuteReader();

                    // Build responses from data reader and return actions
                    Action action = dataTransformer.BuildListOfActions(dataReader)[0];
                    return action;
                }
            }
        }
        
        public IList<Action> GetAllActions()
        {
            // Set up SQL command string
            string sql = "SELECT * FROM [dbo].[Action]";

            // Set up sql connection and command
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // Run the command
                    connection.Open();
                    SqlDataReader dataReader = command.ExecuteReader();

                    // Build actions from data reader and return actions
                    IList<Action> actions = dataTransformer.BuildListOfActions(dataReader);
                    return actions;
                }
            }
        }
        
        public Action PutAction(Action action)
        {
            // Set up SQL command string
            string sql = "INSERT INTO [dbo].[Action] VALUES (@actionType, @userId, @timeStamp, @oldId, @newId);" +
                "SELECT SCOPE_IDENTITY();";

            // Set up sql connection and command
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // Add values for the parameters in the SQL command String
                    command.Parameters.AddWithValue("@actionType", (int) action.actionType);
                    command.Parameters.AddWithValue("@userId", action.UserId);
                    command.Parameters.AddWithValue("@timeStamp", action.Timestamp);
                    command.Parameters.AddWithValue("@oldId", Int32.Parse(action.OldData));
                    command.Parameters.AddWithValue("@newId", Int32.Parse(action.NewData));

                    // Run the command
                    connection.Open();
                    var id = Convert.ToInt32(command.ExecuteScalar());

                    // Add the ID to the Action
                    action.Id = id;

                    return action;
                }
            }
        }

        public bool DeleteById(int id)
        {
            // Check for invalid ID
            if(id<1)
            {
                return false;
            }

            // Set up SQL command string
            string sql = "DELETE FROM [dbo].[Action] WHERE [dbo].[Action].[id]=@id;";

            // Set up sql connection and command
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // Add values for the paramaters in the SQL command String
                    command.Parameters.AddWithValue("@id", id);

                    // Run the command
                    connection.Open();
                    int numberOfRowsAffected = command.ExecuteNonQuery();

                    // Check if the returned number of rows effected, if 0 the command failed 
                    return numberOfRowsAffected != 0;
                }
            }
        }
    }
}

IActionAccessor.cs

using Models;
using System.Collections.Generic;

namespace Core.Interfaces.Accessors
{
    /// <summary>
    /// Accesses action data
    /// </summary>
    public interface IActionAccessor
    {
        /// <summary>
        /// Gets an action by its ID
        /// </summary>
        /// <param name="id">The ID of the Action to be retrieved</param>
        /// <returns>Returns the action at the specified ID</returns>
        Action GetById(int id);

        /// <summary>
        /// Gets a list of all Actions
        /// </summary>
        /// <returns>Returns a list of all Actions</returns>
        IList<Action> GetAllActions();

        /// <summary>
        /// Puts an Action
        /// </summary>
        /// <param name="action">The Action to be inserted into the database</param>
        /// <returns>Returns the action placed into the database with its ID</returns>
        Action PutAction(Action action);

        /// <summary>
        /// Deletes an Action by ID
        /// </summary>
        /// <param name="id">The ID of the action to be deleted</param>
        /// <returns>Returns true if the action is successfully deleted and false otherwise</returns>
        bool DeleteById(int id);
    }
}