Description
This is a simple "demo" app designed to help gain familiarity with interacting with an Sqlite database from C#, particularly storing images as blobs. The database has a single table, Contacts, represented by the following table creation SQL:
CREATE TABLE "Contacts" (
"id" INTEGER NOT NULL,
"name" TEXT NOT NULL,
"email" TEXT NOT NULL,
"address" TEXT NOT NULL,
"phoneNumber" INTEGER NOT NULL,
"image" BLOB
PRIMARY KEY("id")
For context, a screenshot of this application is shown below. There may be some UX issues around this design, but that is not so important here as it is only a "prototype" or "demo" app, and the idea is to implement the database interaction code.
Code
The code uses the repository and data access object patterns.
Contact.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Drawing;
namespace SqliteImageTest
{
public class Contact
{
public int id { get; set; }
public string name { get; set; }
public string email { get; set; }
public string address { get; set; }
public long phoneNumber { get; set; }
public Image photo { get; set; }
}
}
DataAccessObject.cs
Implements CRUD operations.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Sql;
using System.Drawing;
using System.IO;
using Microsoft.Data.Sqlite;
namespace SqliteImageTest
{
public interface IDataAccessObject
{
void create(Contact newContact);
IEnumerable<Contact> read();
void update(Contact contact);
void delete(int id);
}
public class DataAccessObject : IDataAccessObject
{
private readonly string _connString;
/// <summary>
/// Constructor with connection string.
/// </summary>
/// <param name="connectionString"></param>
public DataAccessObject(string connectionString)
{
this._connString = connectionString;
}
/// <summary>
/// Create a new contact record in the database.
/// </summary>
/// <param name="newContact">Contact object.</param>
public void create(Contact newContact)
{
// build the query
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.AppendLine("INSERT INTO Contacts (name, email, address, phoneNumber, image)");
queryBuilder.AppendLine("VALUES ($name, $email, $address, $phoneNumber, $image)");
string query = queryBuilder.ToString();
// execute it
using (var conn = new SqliteConnection(this._connString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("$name", newContact.name);
cmd.Parameters.AddWithValue("$address", newContact.address);
cmd.Parameters.AddWithValue("$email", newContact.email);
cmd.Parameters.AddWithValue("$phoneNumber", newContact.phoneNumber);
if (newContact.photo == null)
{
cmd.Parameters.AddWithValue("$image", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("$image", writeImage(newContact.photo, System.Drawing.Imaging.ImageFormat.Jpeg));
}
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Delete a contact record by its unique ID.
/// </summary>
/// <param name="id"></param>
public void delete(int id)
{
// build the query
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.AppendLine("DELETE FROM Contacts");
queryBuilder.AppendLine("WHERE Contacts.id = $id;");
string query = queryBuilder.ToString();
// execute it
using (var conn = new SqliteConnection(this._connString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("$id", id);
cmd.ExecuteNonQuery();
}
}
private Image readImage(byte[] imageBytes)
{
MemoryStream stream = new MemoryStream(imageBytes, 0, imageBytes.Length);
stream.Write(imageBytes, 0, imageBytes.Length);
Image image = new Bitmap(stream);
return image;
}
private byte[] writeImage(Image image, System.Drawing.Imaging.ImageFormat format)
{
using (MemoryStream stream = new MemoryStream())
{
image.Save(stream, format);
byte[] imageBytes = stream.ToArray();
return imageBytes;
}
}
/// <summary>
/// Retrieve all contact records from the database.
/// </summary>
/// <returns>IEnumerable of Contact objects.</returns>
public IEnumerable<Contact> read()
{
// build the query
string query = "SELECT * FROM Contacts";
// execute it
using (var conn = new SqliteConnection(this._connString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = query;
SqliteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
yield return new Contact
{
id = reader.GetInt32(0),
name = reader.GetString(1),
email = reader.GetString(2),
address = reader.GetString(3),
phoneNumber = reader.GetInt64(4),
photo = reader.IsDBNull(5) ? null : readImage((byte[])reader[5]) // check if there is an image with this record first
};
}
}
}
/// <summary>
/// Update a contact record, by its id.
/// </summary>
/// <param name="contact">Contact object.</param>
public void update(Contact contact)
{
// build the query
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.AppendLine("UDPATE Contacts");
queryBuilder.AppendLine("SET name = $name,");
queryBuilder.AppendLine("email = $email,");
queryBuilder.AppendLine("address = $address,");
queryBuilder.AppendLine("phoneNumber = $phoneNumber,");
queryBuilder.AppendLine("image = $image");
queryBuilder.AppendLine("WHERE id = $id");
string query = queryBuilder.ToString();
// execute it
using (var conn = new SqliteConnection(this._connString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.Parameters.AddWithValue("$id", contact.id);
cmd.Parameters.AddWithValue("$name", contact.name);
cmd.Parameters.AddWithValue("$email", contact.email);
cmd.Parameters.AddWithValue("$address", contact.address);
cmd.Parameters.AddWithValue("$phoneNumber", contact.phoneNumber);
if (contact.photo == null)
{
cmd.Parameters.AddWithValue("$image", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("$image", writeImage(contact.photo, System.Drawing.Imaging.ImageFormat.Jpeg));
}
cmd.ExecuteNonQuery();
}
}
}
}
Repository.cs
This class might be unit-tested. It has two constructors, one used to instantiate it in the "production" code, and the other for unit-testing which will get a mock or fake of the data access object. However, no unit tests were written yet, as they would be rather trivial for its current set of methods. In the future, methods with more complex logic interacting with the data access layer might be written.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SqliteImageTest
{
public interface IRepository
{
IEnumerable<Contact> getAll();
Contact getById(int id);
void insert(Contact newContact);
void update(Contact contact);
void deleteById(int id);
}
public class Repository : IRepository
{
private readonly IDataAccessObject _dataAccessObject;
/// <summary>
/// Production constructor.
/// </summary>
/// <param name="connectionString"></param>
public Repository(string connectionString)
{
this._dataAccessObject = new DataAccessObject(connectionString);
}
/// <summary>
/// Test constructor.
/// </summary>
/// <param name="dataAccessObject">Intended to be a mock or fake.</param>
public Repository(IDataAccessObject dataAccessObject)
{
this._dataAccessObject = dataAccessObject;
}
/// <summary>
/// Ask the data access object to delete the record with this id.
/// </summary>
/// <param name="id"></param>
public void deleteById(int id)
{
this._dataAccessObject.delete(id);
}
/// <summary>
/// Ask the data access object for all records.
/// </summary>
/// <returns>IEnumerable of Contact objects.</returns>
public IEnumerable<Contact> getAll()
{
return this._dataAccessObject.read();
}
/// <summary>
/// Retrieve one contact record, by its unique id.
/// </summary>
/// <param name="id">Unique id as stored in the database.</param>
/// <returns>Contact object.</returns>
public Contact getById(int id)
{
return (this._dataAccessObject.read()).Where(c => c.id == id).FirstOrDefault(); // note there should only be one record with a particular ID
}
/// <summary>
/// Create a new contact record.
/// </summary>
/// <param name="newContact">Contact object.</param>
public void insert(Contact newContact)
{
this._dataAccessObject.create(newContact);
}
/// <summary>
/// Update a contact record, by its id.
/// </summary>
/// <param name="contact">Contact object.</param>
public void update(Contact contact)
{
this._dataAccessObject.update(contact);
}
}
}
```
string query = queryBuilder.ToString();? Methods should be PascalCased, ditto public properties. \$\endgroup\$