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
