#6 Creating the RDS MySQL Database Connection

#6 Creating the RDS MySQL Database Connection

Before you get to excited about my code and how it’s written, please remember I’m self taught and my have picked up some bad habit’s along the way, but I’m open to corrections and helpful suggestions.

In this blog we will look at the four classes that will allow us to create a connection, convert data table rows to classes, managing Sql Commands to stop Sql Injection and Sql Repository’s.

DataToClass

The problem that we have, is that the data that is returned from our Sql query’s will be data tables, that’s where this class comes in useful. It uses generics to convert the data table into something useful like a specific class object or an enumerable of class objects. We are going to use this specifically with our Get method.

using Serilog;

using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;

namespace MyFirstLambdaProject.Database
{
    public class DatatoClass
    {
        private ILogger _log = null;

        public DatatoClass(ILogger ilog)
        {
            _log = ilog;
            _log.Information("Creating New Data to Class.");
        }

        public List<T> ConvertDataTable<T>(DataTable dt) where T : new()
        {
            List<T> ReturnType = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                T item = GetItem<T>(row);
                ReturnType.Add(item);
            }
            return ReturnType;
        }

        private T GetItem<T>(DataRow dr) where T : new()
        {
            Type Type = typeof(T);
            T ReturnType = new T();
            foreach (DataColumn column in dr.Table.Columns)
            {
                foreach (PropertyInfo propertyInfo in Type.GetProperties())
                {
                    var ColumnNameAttribute = propertyInfo.GetCustomAttribute<ColumnNameAttribute>();
                    string Name;
                    if (ColumnNameAttribute != null)
                    {
                        Name = ColumnNameAttribute.ColumnName;
                    }
                    else
                    {
                        Name = propertyInfo.Name;
                    }

                    object propertyVal = dr[column.ColumnName];
                    Type propertyType = propertyInfo.PropertyType;

                    if (Name.Equals(column.ColumnName, StringComparison.InvariantCultureIgnoreCase))
                    {
                        object Writeable = null;
                        if (Convert.IsDBNull(propertyVal) || propertyVal.ToString() == string.Empty)
                        {
                            Writeable = CreateEmptyBaseType(propertyType);
                        }
                        else
                        {
                            Writeable = propertyVal;
                        }
                        if (propertyInfo.CanWrite == true)
                        {
                            propertyInfo.SetValue(ReturnType, Convert.ChangeType(Writeable, propertyType));
                        }
                        break;
                    }
                }
            }
            return ReturnType;
        }

        private object CreateEmptyBaseType(Type iPropertyType)
        {
            if (iPropertyType.IsValueType)
            {
                return Activator.CreateInstance(iPropertyType);
            }
            else
            {
                return null;
            }
        }
    }

    [System.AttributeUsage(System.AttributeTargets.Property)]
    public class ColumnNameAttribute : System.Attribute
    {
        public string ColumnName;
        public ColumnNameAttribute(string iColumnName)
        {
            ColumnName = iColumnName;
        }
    }
}

DataCommandHandler

The Command Handler is a simple class that allows us to build a dictionary of parameters and then replace strings prefixed by a symbol with the parameter values, and return the final query to be executed.

using Serilog;

using System;
using System.Collections.Generic;

namespace MyFirstLambdaProject.Database
{
    public class DataCommandHandler
    {
        private ILogger _log = null;
        private Dictionary<string, string> _ParamsList = new Dictionary<string, string>();

        public DataCommandHandler(ILogger ilog)
        {
            _log = ilog;
            _log.Information("Creating New Data Command Handler.");
        }

        public void Add(String iParamName, Object iParamValue)
        {
            this._ParamsList.Add(iParamName, iParamValue.ToString());
        }

        public string GetSqlCommand(string iCommandString)
        {
            string ReturnString = string.Empty;
            foreach (string Key in this._ParamsList.Keys)
            {
                if (Key.Contains(":"))
                {
                    iCommandString = iCommandString.Replace(Key, "'" + this._ParamsList[Key] + "'");
                }
                else if (Key.Contains(";"))
                {
                    if (this._ParamsList[Key] != string.Empty)
                    {
                        iCommandString = iCommandString.Replace(Key, "'%" + this._ParamsList[Key] + "%'");
                    }
                    else
                    {
                        iCommandString = iCommandString.Replace(Key, "'%'");
                    }
                }
                else
                {
                    iCommandString = iCommandString.Replace(Key, this._ParamsList[Key]);
                }
            }
            ReturnString = iCommandString;
            this._ParamsList.Clear();

            return ReturnString;
        }
    }
}

DataConnection

Next we have our Data Connection, which requires the Secrets Dictionary to construct the connection string. It also contains methods for Opening and Closing the database connection.

Note we added an additional NuGet package MySql.Data.

using MyFirstLambdaProject.Models;
using MySql.Data.MySqlClient;

using Serilog;

using System;
using System.Collections.Generic;
using System.Data;

namespace MyFirstLambdaProject.Database
{
    public class DataConnection
    {
        private ILogger _log = null;
        private MySqlConnection _mariaConnection = null;
        private string _ConnectionString = string.Empty;

        public bool IsConnectionOpen => this._mariaConnection.State == ConnectionState.Open;
        public MySqlCommand MySqlCommand => this._mariaConnection.CreateCommand();

        public DataConnection(Dictionary<string, string> iConnectionStrings, ILogger ilog)
        {
            _log = ilog;
            _log.Information("Creating New Data Connection.");

            _ConnectionString = String.Format("server={0};user={1};password={2}",
            iConnectionStrings[SecretsConstantModel.Server],
            iConnectionStrings[SecretsConstantModel.User],
            iConnectionStrings[SecretsConstantModel.Password]);

            if (iConnectionStrings != null)
            {
                this._mariaConnection = new MySqlConnection(this._ConnectionString);
            }
        }

        public bool OpenConnection()
        {
            _log.Information("Opening Data Connection.");
            try
            {
                if (this.IsConnectionOpen == false)
                {
                    this.ManageConnection(ConnectionState.Open);
                    _log.Information("Connection Opened.");
                }
                return true;
            }
            catch(Exception ex)
            {
                _log.Error(ex.Message);
                return false;
            }
        }
      
        public bool CloseConnection()
        {
            _log.Information("Closing Data Connection.");
            try
            {
                if (this.IsConnectionOpen == true)
                {
                    this.ManageConnection(ConnectionState.Closed);
                    _log.Information("Connection Closed.");
                }
                return true;
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return false;
            }
        }

        private void ManageConnection(ConnectionState iConnectionState)
        {
            if (_mariaConnection != null)
            {
                if (iConnectionState == ConnectionState.Open)
                {
                    if (this.IsConnectionOpen == false) { _mariaConnection.Open(); };
                }
                else
                {
                    if (this.IsConnectionOpen == true) { _mariaConnection.Close(); };
                }
            }
        }
    }
}

DataRepo

Finally the Data Repo class which uses the previous three classes to execute the MySQL query’s. This class contains the four queries for the four Rest API end points, Get, Post, Patch, and Delete. Notice within the constructor we make the call to the Secrets Manager, to get the database connection information from AWS. In a later post we will look to see how we can refactor the code and create a GitHub package dedicated to database connections and data management.

using MyFirstLambdaProject.Models;
using MyFirstLambdaProject.Secrets;

using MySql.Data.MySqlClient;

using Serilog;

using System;
using System.Collections.Generic;
using System.Data;

namespace MyFirstLambdaProject.Database
{
    public class DataRepo
    {
        private ILogger _log = null;

        private readonly DatatoClass _DatatoClass = null;
        private readonly DataCommandHandler _DataCommandHandler = null;
        private readonly DataConnection _DataConnection = null;

        public DataRepo(ILogger ilog)
        {
            _log = ilog;
            _log.Information("Creating New Data Repository.");

            _DatatoClass = new DatatoClass(_log);
            _DataCommandHandler = new DataCommandHandler(_log);

            Dictionary<string, string> ConnectionDictionary = SecretsManager.GetSecretFromSecretManager("DataModel", _log);
            _DataConnection = new DataConnection(ConnectionDictionary, _log);
        }

        #region "Database Methods"

        public IEnumerable<DataModel> GetDataModels()
        {
            _log.Information("Getting Data Models.");
            IEnumerable<DataModel> ReturnIEnumerable = null;
            MySqlCommand DBCommand = null;
            MySqlDataReader DBDataReader = null;
            try
            {
                if (_DataConnection != null)
                {
                    _DataConnection.OpenConnection();
                    DBCommand = _DataConnection.MySqlCommand;
                    String Query = _DataCommandHandler.GetSqlCommand(GetAllDataModelsSQL());
                    _log.Information(Query);

                    DBCommand.CommandText = Query;
                    DBDataReader = DBCommand.ExecuteReader();
                    DataTable DataTable = new DataTable();
                    if (DBDataReader.HasRows)
                    {
                        DataTable.Load(DBDataReader);
                        ReturnIEnumerable = this._DatatoClass.ConvertDataTable<DataModel>(DataTable);
                    }
                }
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                ReturnIEnumerable = null;
                throw new Exception("Class :DataRepo - Method :GetDataModels - Message: Failed To Get DataModels Data.", ex);
            }
            finally
            {
                DBDataReader.Close();
                DBCommand.Dispose();
                _DataConnection.CloseConnection();
            }
            return ReturnIEnumerable;
        }
      
        public DataModel PostDataModel(DataModel iDataModel)
        {
            _log.Information("Deleting Data Model.");
            DataModel ReturnDataModel = null;
            MySqlCommand DBCommand = null;

            try
            {
                if (_DataConnection != null)
                {
                    _DataConnection.OpenConnection();
                    DBCommand = _DataConnection.MySqlCommand;
                    _DataCommandHandler.Add(":FirstName", iDataModel.FirstName);
                    _DataCommandHandler.Add(":LastName", iDataModel.LastName);
                    _DataCommandHandler.Add(":Age", iDataModel.Age);

                    String Query = _DataCommandHandler.GetSqlCommand(PostDataModelSQL());
                    _log.Information(Query);

                    DBCommand.CommandText = Query;
                    int NumberOfRows = DBCommand.ExecuteNonQuery();
                    if (NumberOfRows > 0)
                    {
                        ReturnDataModel = iDataModel;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                ReturnDataModel = null;
                throw new Exception("Class :DataRepo - Method :PostDataModels - Message: Failed To Post DataModel Data.", ex);
            }
            finally
            {
                DBCommand.Dispose();
                _DataConnection.CloseConnection();
            }
            return ReturnDataModel;
        }
      
        public DataModel PatchDataModel(ChangeDataModelRequest iChangeDataModelRequest)
        {
            _log.Information("Patch Data Model.");
            DataModel ReturnDataModel = null;
            MySqlCommand DBCommand = null;

            try
            {
                if (_DataConnection != null)
                {
                    _DataConnection.OpenConnection();
                    DBCommand = _DataConnection.MySqlCommand;
                    _DataCommandHandler.Add(":FirstName", iChangeDataModelRequest.Original.FirstName);
                    _DataCommandHandler.Add(":LastName", iChangeDataModelRequest.Original.LastName);
                    _DataCommandHandler.Add(":Age", iChangeDataModelRequest.Original.Age);
                    _DataCommandHandler.Add(":NewFirstName", iChangeDataModelRequest.ToBe.FirstName);
                    _DataCommandHandler.Add(":NewLastName", iChangeDataModelRequest.ToBe.LastName);
                    _DataCommandHandler.Add(":NewAge", iChangeDataModelRequest.ToBe.Age);

                    String Query = _DataCommandHandler.GetSqlCommand(PatchDataModelSQL());
                    _log.Information(Query);

                    DBCommand.CommandText = Query;
                    int NumberOfRows = DBCommand.ExecuteNonQuery();
                    if (NumberOfRows > 0)
                    {
                        ReturnDataModel = iChangeDataModelRequest.ToBe;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                ReturnDataModel = null;
                throw new Exception("Class :DataRepo - Method :PatchDataModels - Message: Failed To Patch DataModel Data.", ex);
            }
            finally
            {
                DBCommand.Dispose();
                _DataConnection.CloseConnection();
            }
            return ReturnDataModel;
        }
      
        public bool DeleteDataModel(DataModel iDataModel)
        {
            _log.Information("Deleting Data Model.");
            bool ReturnBoolean = false;
            MySqlCommand DBCommand = null;

            try
            {
                if (_DataConnection != null)
                {
                    _DataConnection.OpenConnection();
                    DBCommand = _DataConnection.MySqlCommand;
                    _DataCommandHandler.Add(":FirstName", iDataModel.FirstName);
                    _DataCommandHandler.Add(":LastName", iDataModel.LastName);
                    _DataCommandHandler.Add(":Age", iDataModel.Age);

                    String Query = _DataCommandHandler.GetSqlCommand(DeleteDataModelSQL());
                    _log.Information(Query);

                    DBCommand.CommandText = Query;
                    int NumberOfRows = DBCommand.ExecuteNonQuery();
                    if(NumberOfRows > 0)
                    {
                        ReturnBoolean = true;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                ReturnBoolean = false;
                throw new Exception("Class :DataRepo - Method :DeleteDataModels - Message: Failed To Delete DataModel Data.", ex);
            }
            finally
            {
                DBCommand.Dispose();
                _DataConnection.CloseConnection();
            }
            return ReturnBoolean;
        }
        #endregion

        #region "SQL Statements"
        private string GetAllDataModelsSQL()
        { 
            string ReturnString  = @"
                SELECT * 
                FROM CatiaWidgetsDB.DataModel_Table DMT";
            return ReturnString;
        }
        private string PostDataModelSQL()
        {
            string ReturnString = @"
                INSERT INTO CatiaWidgetsDB.DataModel_Table
                (FirstName,LastName,Age)
                VALUES
                (:FirstName,:LastName,:Age)";             
            return ReturnString;
        }
        private string PatchDataModelSQL()
        {
            string ReturnString = @"
                UPDATE CatiaWidgetsDB.DataModel_Table DMT
                SET DMT.FirstName = :NewFirstName, DMT.LastName = :NewLastName, DMT.Age = :NewAge
                WHERE DMT.FirstName = :FirstName
                AND DMT.LastName = :LastName
                AND DMT.Age = :Age";
            return ReturnString;
        }
        private string DeleteDataModelSQL()
        {
            string ReturnString = @"
                DELETE 
                FROM CatiaWidgetsDB.DataModel_Table DMT 
                WHERE DMT.FirstName = :FirstName
                AND DMT.LastName = :LastName
                AND DMT.Age = :Age";
            return ReturnString;
        }
        #endregion
    }
}

Processor

Finally, we will put all this code to use within the Processor, by making the relevant calls to the ‘DataRepo’.

using MyFirstLambdaProject.Database;
using MyFirstLambdaProject.Models;

using Serilog;

using System;
using System.Collections.Generic;

namespace MyFirstLambdaProject.Processors
{
    public class DataModelProcessor : IDataModelProcessor
    {
        private readonly DataRepo _DataRepo = null;
        private ILogger _log = null;

        public DataModelProcessor()
        {
            _log = Log.Logger;
            _log.Information("Creating New Data Model Processor.");
            _DataRepo = new DataRepo(_log);
        }        
        
        public IEnumerable<DataModel> GetDataModel()
        {
            try
            {
                return this._DataRepo.GetDataModels();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message,ex);
            }
        }
        
        public DataModel PostDataModel(DataModel iDataModel)
        {
            DataModel ReturnDataModel = null;
            if (iDataModel!=null)
            {
                ReturnDataModel = this._DataRepo.PostDataModel(iDataModel);           
            }
            return ReturnDataModel;
        }
        
        public DataModel PatchDataModel(ChangeDataModelRequest iChangeDataModelRequest)
        {
            DataModel ReturnDataModel = null;
            if (iChangeDataModelRequest !=null)
            {
                if (iChangeDataModelRequest.Original != null && iChangeDataModelRequest.ToBe != null)
                {
                    ReturnDataModel = this._DataRepo.PatchDataModel(iChangeDataModelRequest);
                }
            }
            return ReturnDataModel;
        }
        
        public bool DeleteDataModel(DataModel iDataModel)
        {
            bool ReturnBoolean = false;
            if (iDataModel !=null)
            {             
                ReturnBoolean = this._DataRepo.DeleteDataModel(iDataModel);
            }
            return ReturnBoolean;
        }
    }
}

Testing With Postman

In the previous post we used post man to test our endpoints were going to do the same again. Remember we must provide an API key as an authentication token, because we added API keys in the API Gateway to secure our end points.

Post

In the Raw->Json Body we will define a new DataModel as shown below, then we will send the Post Request to our end point.

Postman – Post

If everything is correct we will get a response back, we can see her that we got the DataModel back in Json format, as well as a 200 OK.

Post Response 200 OK

We can also see in the database we have our first data row.

Post – Database Data

Get

Now that we have Posted some data, we can now use a Get Method to retrieve the current data in the database. When the responce comes back we can see the previously Posted data.

Get Response 200 OK

I went ahead and posted some additional data tot he database and re-run the Get method here is the respoonce i got back.

Multiple Data Models

Patch

Next we want to edit some of the existing data, using a Patch method. In this case the Raw->Json Body needs to have both the Original and the ToBe DataModels. Again once the data is sent we get the 200 Ok response and the updated DataModel.

Patch Response 200 OK

We can also see in the Database the updated table row.

Patch – Database Data

Delete

Finally, let’s delete a row using the Delete method. In this case the Raw->Json Body needs to have the DataModel that needs to be deleted. Again once the data is sent we get the 200 Ok response and a True Boolean response indicating that the data has been deleted.

Delete Response 200 OK

In a future Blog, we will create a simple M-V-VM WPF app that uses our rest API end points to create an nice front end app.