Tuesday, April 24, 2018

Using Dapper with Asp.net Core website on Mac

In this blog post we will create an Asp.Net Core 2.0 website with Visual Studio for the Mac and use Dapper to access a SQLite database.  Dapper is micro ORM written by the StackOverflow team.  It is meant to send the data from database to C# class.

To start create a new Asp.Net Core MVC app.



Now we need to add 2 NuGet packages.  Microsoft.Data.SQLite and Dapper




Next we are going to create an Interface for our DataAccess class.  I called it IDataAccess

    public interface IDataAccess
    {
        void InsertName(string Name);

        Name GetName(int id);

        void DeleteName(int Id);

        void UpdateName(int Id, string Name);
        
        List<Name> GetNames();
    }



Here is the class Name

    public class Name
    {
        public int Id { get; set; }

        public string name { get; set; }
    }



Now we are going to create an Class that Implements the IDataAccess interface

    public class SQLIteDataAccess : IDataAccess
    {
        private const string fileName = "MyDatabase.db";

        
private IDbConnection connection = null;

        public SQLIteDataAccess()
        {
            string connectionString = $"FileName={fileName}";
            connection = new SqliteConnection(connectionString);
            if(!File.Exists(fileName))
            {
                FileStream fileStream = File.Create(fileName);
                fileStream.Close();

                try
                {
                    connection.Open();
                    connection.Execute("Create Table Names(Id int Primary Key AUTOINCREMENT, Name Text) ");
                }
                catch (Exception ex)
                {
                    Trace.WriteLine(ex.Message);
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        public void DeleteName(int Id)
        {
            try
            {
                connection.Open();
                connection.Execute("Delete from Names Where Id = @Id", new {Id = Id});
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public System.Collections.Generic.List<Name> GetNames()
        {
            List<Name> results = null;
            try
            {
                connection.Open();
                results = connection.Query<Name>("Select * from Names").ToList();
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }
            return results;
        }

        public void InsertName(string Name)
        {
            try
            {
                connection.Open();
                connection.Execute("Insert into Names (Name) value (@Name)", new { Name = Name });
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void UpdateName(int Id, string Name)
        {
            try
            {
                connection.Open();
                connection.Execute("Update Names set Name = @Name Where Id = @Id", 
                                   new { Name = Name, Id = Id });
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }



Ok lets talk about the Constructor before we move on to the Dapper code.

       public SQLIteDataAccess()
        {
            string connectionString = $"FileName={fileName}";
            connection = new SqliteConnection(connectionString);
            if(!File.Exists(fileName))
            {
                FileStream fileStream = File.Create(fileName);
                fileStream.Close();

                try
                {
                    connection.Open();
                    connection.Execute("Create Table Names(Id INTEGER Primary Key AUTOINCREMENT, Name Text) ");                }
                catch (Exception ex)
                {
                    Trace.WriteLine(ex.Message);
                }
                finally
                {
                    connection.Close();
                }

            }
        }

First thing I do is look and see if the database was created.  If it was not I am creating creating a new file with the database name I am looking for.  I store the file stream in a variable so I can close it.  If you don't do this the file will be locked and you won't be able to execute scripts on the database.

Next I create a connection to the database and run a create table script.  

connection.Execute("Create Table Names(Id INTEGER Primary Key AUTOINCREMENT, Name Text) ");


Now lets look at the dapper code.  It adds some methods to the connection that allows us to Execute a script or Query the database.

For example this command Queries the Database for all the records in the names table

results = connection.Query<Name>("Select * from Names").ToList();

To execute a command on the database with some parameters you do it like this

   connection.Execute("Insert into Names (Name) value (@Name)", new { Name = Name });

Please do not pass user input directly in to a database query I would use a parameter to reduce the chance of SQL injection



Finally we are going to add a controller to expose these methods via the web api

    [Route("api/[controller]")]
    public class SQLiteController : Controller
    {
        IDataAccess dataAccess;

        public SQLiteController(IDataAccess data)
        {
            dataAccess = data;
        }
        

        // GET: api/values
        [HttpGet]
        public List<Name> Get()
        {
            return dataAccess.GetNames();
        }

        // GET api/values/5
        [HttpGet("{id}")]
        public Name Get(int id)
        {
            return dataAccess.GetName(id);
        }

        // POST api/values
        [HttpPost]
        public void Post([FromBody]string value)
        {
            dataAccess.InsertName(value);
        }

        // PUT api/values/5
        [HttpPut("{id}")]
        public void Put(int id, [FromBody]string value)
        {
            dataAccess.UpdateName(id, value);
        }

        // DELETE api/values/5
        [HttpDelete("{id}")]
        public void Delete(int id)
        {
            dataAccess.DeleteName(id);
        }
    }


In the constructor I am injecting an Instance of the IDataAccess so I can use it in the controller.

       IDataAccess dataAccess;

        public SQLiteController(IDataAccess data)
        {
            dataAccess = data;
        }

To register the IDataAccess interface for dependency injection we do it in the Startup class

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            services.AddTransient<IDataAccess, SQLIteDataAccess>();
        }



Since I am doing this on the Mac I am going to use curl in the terminal window to test the service

Lets insert a name in the database

kentucker$ curl -H "Content-Type: application/json" -X POST -d '"Bill Gates"' http://localhost:19266/api/SQLite

List the Names


curl http://localhost:19266/api/SQLite






In this post I showed how to use Dapper with Asp.Net core. When I develop software that interacts with a database I like to develop using a local database.  Since SQL Server my server data base of choice is not available for the Mac. I decided to use SQLite for local development.  It is possible to use host SQL Server on a Mac in docker container but since it uses a lot of resources I figured SQLite would work.    In my next post I will show how to refactor the code to allow the class to allow the code the connect to either a SQL Server of SQLite database.


The source code is available on GITHUB


https://github.com/vb2ae/AspNetCoreSQLite

No comments:

Post a Comment