In this post we describe how you can create a generic database provider for MS SQL in ASP.NET Core. Our database provider will include all the methods needed to communicate with a MS SQL database and it can handle all of our different models.
A generic class means less code to write and code that is easy to maintain. Instead of having the same code in many classes in your project, you can have this code in one class and only have to make changes in one place. A generic class includes generic methods that can handle almost any data type. A client who uses a generic method determines the data type used in parameters and the data type of the returned type.
Interface
We have created a generic interface for a database provider, this interface will be a dependency in other classes in our project. This interface should be designed in a way that it can be implemented by a MS SQL provider, a MySql provider or another database provider. This interface is intended to be used with synchronous methods, an out parameter can not be used in asynchronous methods and asynchronous methods must return a Task.
public interface IDatabaseRepository
{
void Insert<T>(string sql, IDictionary<string, object> parameters, out T value);
void Insert(string sql, IDictionary<string, object> parameters);
void Update(string sql, IDictionary<string, object> parameters);
T GetCount<T>(string sql, IDictionary<string, object> parameters);
T GetValue<T>(string sql, IDictionary<string, object> parameters);
T GetModel<T>(string sql, IDictionary<string, object> parameters);
IList<T> GetModelList<T>(string sql, IDictionary<string, object> parameters, Int32 listSize);
IDictionary<TKey, TValue> GetKeyValueDictionary<TKey, TValue>(string sql, IDictionary<string, object> parameters, Int32 listSize);
Int32 Delete(string sql, IDictionary<string, object> parameters);
} // End of the interfaceMS SQL database provider
We have created a Microsoft SQL database provider that implements our IDatabaseRepository interface. This provider includes synchronous methods. If we want to make methods in this class asynchronous we need to return Tasks and can not use out parameters. If we need to return multiple values in an asynchronous method, we have to return a wrapper model around the generic datatype or return tuples (C# 7).
public class MsSqlRepository : IDatabaseRepository
{
#region Variables
private readonly DatabaseOptions options;
private readonly Random rnd;
#endregion
#region Constructors
/// <summary>
/// Create a new sql repository
/// </summary>
public MsSqlRepository(IOptions<DatabaseOptions> options)
{
// Set values for instance variables
this.options = options.Value;
this.rnd = new Random();
} // End of the constructor
#endregion
#region Insert methods
/// <summary>
/// Insert a post to the database
/// </summary>
public void Insert<T>(string sql, IDictionary<string, object> parameters, out T value)
{
// Create the variable to return
value = default(T);
// Make retries
for (int r = 0; r < this.options.sql_retry_count; r++)
{
// The using block is used to call dispose automatically, even if there is a exception
using (SqlConnection cn = new SqlConnection(this.options.connection_string))
{
// The using block is used to call dispose automatically, even if there is a exception
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
// Add parameters
foreach (KeyValuePair<string, object> entry in parameters)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
// The Try/Catch/Finally statement is used to handle unusual exceptions in the code to
// avoid having our application crash in such cases
try
{
// Open the connection
cn.Open();
// Execute the insert
value = (T)cmd.ExecuteScalar();
}
catch (SqlException sqlEx)
{
// Deadlock or timeout, 1205 = Deadlock, -2 = TimeOut
if (sqlEx.Number == 1205 || sqlEx.Number == -2)
{
Thread.Sleep(this.rnd.Next(5000, 10000));
continue;
}
}
catch (Exception e)
{
throw e;
}
}
}
// No exceptions (break out from the loop)
break;
} // End of the for (int r = 0; r < sqlRetryCount; r++)
} // End of the Insert method
/// <summary>
/// Insert a post to the database
/// </summary>
public void Insert(string sql, IDictionary<string, object> parameters)
{
// Make retries
for (int r = 0; r < this.options.sql_retry_count; r++)
{
// The using block is used to call dispose automatically even if there is a exception.
using (SqlConnection cn = new SqlConnection(this.options.connection_string))
{
// The Using block is used to call dispose automatically even if there is a exception.
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
// Add parameters
foreach (KeyValuePair<string, object> entry in parameters)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
// The Try/Catch/Finally statement is used to handle unusual exceptions in the code to
// avoid having our application crash in such cases
try
{
// Open the connection
cn.Open();
// Execute the insert
cmd.ExecuteNonQuery();
}
catch (SqlException sqlEx)
{
// Deadlock or timeout, 1205 = Deadlock, -2 = TimeOut
if (sqlEx.Number == 1205 || sqlEx.Number == -2)
{
Thread.Sleep(this.rnd.Next(5000, 10000));
continue;
}
}
catch (Exception e)
{
throw e;
}
}
}
// No exceptions (break out from the loop)
break;
} // End of the for (int r = 0; r < sqlRetryCount; r++)
} // End of the Insert method
#endregion
#region Update methods
/// <summary>
/// Update a post in the database
/// </summary>
public void Update(string sql, IDictionary<string, object> parameters)
{
// Make retries
for (int r = 0; r < this.options.sql_retry_count; r++)
{
// The using block is used to call dispose automatically even if there is a exception.
using (SqlConnection cn = new SqlConnection(this.options.connection_string))
{
// The Using block is used to call dispose automatically even if there is a exception.
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
// Add parameters
foreach (KeyValuePair<string, object> entry in parameters)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
// The Try/Catch/Finally statement is used to handle unusual exceptions in the code to
// avoid having our application crash in such cases
try
{
// Open the connection
cn.Open();
// Execute the insert
cmd.ExecuteNonQuery();
}
catch (SqlException sqlEx)
{
// Deadlock or timeout, 1205 = Deadlock, -2 = TimeOut
if (sqlEx.Number == 1205 || sqlEx.Number == -2)
{
Thread.Sleep(this.rnd.Next(5000, 10000));
continue;
}
}
catch (Exception e)
{
throw e;
}
}
}
// No exceptions (break out from the loop)
break;
} // End of the for (int r = 0; r < sqlRetryCount; r++)
} // End of the Update method
#endregion
#region Count methods
/// <summary>
/// Count the number of posts
/// </summary>
public T GetCount<T>(string sql, IDictionary<string, object> parameters)
{
// Create the variable to return
T count = default(T);
// Make retries
for (int r = 0; r < this.options.sql_retry_count; r++)
{
// The using block is used to call dispose automatically even if there are an exception.
using (SqlConnection cn = new SqlConnection(this.options.connection_string))
{
// The using block is used to call dispose automatically even if there are an exception.
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
// Add parameters
foreach (KeyValuePair<string, object> entry in parameters)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
// The Try/Catch/Finally statement is used to handle unusual exceptions in the code to
// avoid having our application crash in such cases.
try
{
// Open the connection
cn.Open();
// Execute the select statment
count = (T)cmd.ExecuteScalar();
}
catch (SqlException sqlEx)
{
// Deadlock or timeout, 1205 = Deadlock, -2 = TimeOut
if (sqlEx.Number == 1205 || sqlEx.Number == -2)
{
Thread.Sleep(this.rnd.Next(5000, 10000));
continue;
}
}
catch (Exception e)
{
throw e;
}
}
}
// No exceptions (break out from the loop)
break;
} // End of the for (int r = 0; r < sqlRetryCount; r++)
// Return the count
return count;
} // End of the GetCount method
#endregion
#region Get methods
/// <summary>
/// Get a value as the type specified
/// </summary>
public T GetValue<T>(string sql, IDictionary<string, object> parameters)
{
// Create the value to return
T value = default(T);
// Make retries
for (int r = 0; r < this.options.sql_retry_count; r++)
{
// The using block is used to call dispose automatically even if there is a exception.
using (SqlConnection cn = new SqlConnection(this.options.connection_string))
{
// The using block is used to call dispose automatically even if there is a exception.
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
// Add parameters
foreach (KeyValuePair<string, object> entry in parameters)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
// The Try/Catch/Finally statement is used to handle unusual exceptions in the code to
// avoid having our application crash in such cases.
try
{
// Open the connection.
cn.Open();
// Get the value
value = (T)cmd.ExecuteScalar();
}
catch (SqlException sqlEx)
{
// Deadlock or timeout, 1205 = Deadlock, -2 = TimeOut
if (sqlEx.Number == 1205 || sqlEx.Number == -2)
{
Thread.Sleep(this.rnd.Next(5000, 10000));
continue;
}
}
catch (Exception e)
{
throw e;
}
}
}
// No exceptions (break out from the loop)
break;
} // End of the for (int r = 0; r < sqlRetryCount; r++)
// Return the value
return value;
} // End of the GetValue method
/// <summary>
/// Get a model as the type specfied
/// </summary>
public T GetModel<T>(string sql, IDictionary<string, object> parameters)
{
// Create the post to return
T post = default(T);
// Make retries
for (int r = 0; r < this.options.sql_retry_count; r++)
{
// The using block is used to call dispose automatically even if there are an exception.
using (SqlConnection cn = new SqlConnection(this.options.connection_string))
{
// The using block is used to call dispose automatically even if there are an exception.
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
// Add parameters
foreach (KeyValuePair<string, object> entry in parameters)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
// Create a SqlDataReader
SqlDataReader reader = null;
// The Try/Catch/Finally statement is used to handle unusual exceptions in the code to
// avoid having our application crash in such cases.
try
{
// Open the connection.
cn.Open();
// Fill the reader with one row of data.
reader = cmd.ExecuteReader();
// Loop through the reader as long as there is something to read and add values
while (reader.Read())
{
post = (T)Activator.CreateInstance(typeof(T), reader);
}
}
catch (SqlException sqlEx)
{
// Deadlock or timeout, 1205 = Deadlock, -2 = TimeOut
if (sqlEx.Number == 1205 || sqlEx.Number == -2)
{
Thread.Sleep(this.rnd.Next(5000, 10000));
continue;
}
}
catch (Exception e)
{
throw e;
}
finally
{
// Call dispose when done reading to avoid memory leakage
if (reader != null)
reader.Dispose();
}
}
}
// No exceptions (break out from the loop)
break;
} // End of the for (int r = 0; r < sqlRetryCount; r++)
// Return the post
return post;
} // End of the GetModel method
/// <summary>
/// Get a list with models
/// </summary>
public IList<T> GetModelList<T>(string sql, IDictionary<string, object> parameters, Int32 listSize)
{
// Create the list to return
IList<T> posts = new List<T>(listSize);
// Make retries
for (int r = 0; r < this.options.sql_retry_count; r++)
{
// The using block is used to call dispose automatically even if there are an exception.
using (SqlConnection cn = new SqlConnection(this.options.connection_string))
{
// The using block is used to call dispose automatically even if there are an exception.
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
// Add parameters
foreach (KeyValuePair<string, object> entry in parameters)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
// Create a reader
SqlDataReader reader = null;
// The Try/Catch/Finally statement is used to handle unusual exceptions in the code to
// avoid having our application crash in such cases.
try
{
// Open the connection.
cn.Open();
// Fill the reader with data from the select command.
reader = cmd.ExecuteReader();
// Loop through the reader as long as there is something to read.
while (reader.Read())
{
posts.Add((T)Activator.CreateInstance(typeof(T), reader));
}
}
catch (SqlException sqlEx)
{
// Deadlock or timeout, 1205 = Deadlock, -2 = TimeOut
if (sqlEx.Number == 1205 || sqlEx.Number == -2)
{
Thread.Sleep(this.rnd.Next(5000, 10000));
continue;
}
}
catch (Exception e)
{
throw e;
}
finally
{
// Call dispose when done reading to avoid memory leakage
if (reader != null)
reader.Dispose();
}
}
}
// No exceptions (break out from the loop)
break;
} // End of the for (int r = 0; r < numberOfRetries; r++)
// Return the list of posts
return posts;
} // End of the GetModelList method
/// <summary>
/// Get a dictionary
/// </summary>
public IDictionary<TKey, TValue> GetKeyValueDictionary<TKey, TValue>(string sql, IDictionary<string, object> parameters, Int32 listSize)
{
// Create the dictionary to return
IDictionary<TKey, TValue> posts = new Dictionary<TKey, TValue>(listSize);
// Make retries
for (int r = 0; r < this.options.sql_retry_count; r++)
{
// The using block is used to call dispose automatically even if there are an exception
using (SqlConnection cn = new SqlConnection(this.options.connection_string))
{
// The using block is used to call dispose automatically even if there is a exception
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
// Add parameters
foreach (KeyValuePair<string, object> entry in parameters)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
// Create a reader
SqlDataReader reader = null;
// The Try/Catch/Finally statement is used to handle unusual exceptions in the code to
// avoid having our application crash in such cases
try
{
// Open the connection.
cn.Open();
// Fill the reader with data from the select command
reader = cmd.ExecuteReader();
// Loop through the reader as long as there is something to read
while (reader.Read())
{
posts.Add((TKey)reader[0], (TValue)reader[1]);
}
}
catch (SqlException sqlEx)
{
// Deadlock or timeout, 1205 = Deadlock, -2 = TimeOut
if (sqlEx.Number == 1205 || sqlEx.Number == -2)
{
Thread.Sleep(this.rnd.Next(5000, 10000));
continue;
}
}
catch (Exception e)
{
throw e;
}
finally
{
// Dispose when done reading to avoid memory leakage
if (reader != null)
reader.Dispose();
}
}
}
// No exceptions (break out from the loop)
break;
} // End of the for (int r = 0; r < numberOfRetries; r++)
// Return the dictionary
return posts;
} // End of the GetKeyValueDictionary method
#endregion
#region Delete methods
/// <summary>
/// Delete posts
/// </summary>
public Int32 Delete(string sql, IDictionary<string, object> parameters)
{
// Create the integer to return
Int32 errorNumber = 0;
// Make retries
for (int r = 0; r < this.options.sql_retry_count; r++)
{
// The using block is used to call dispose automatically even if there is a exception.
using (SqlConnection cn = new SqlConnection(this.options.connection_string))
{
// The using block is used to call dispose automatically even if there is a exception.
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
// Add parameters
foreach (KeyValuePair<string, object> entry in parameters)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
// The Try/Catch/Finally statement is used to handle unusual exceptions in the code to
// avoid having our application crash in such cases.
try
{
// Open the connection.
cn.Open();
// Execute the delete
cmd.ExecuteNonQuery();
}
catch (SqlException sqlEx)
{
// Deadlock or timeout, 1205 = Deadlock, -2 = TimeOut
if (sqlEx.Number == 1205 || sqlEx.Number == -2)
{
Thread.Sleep(this.rnd.Next(5000, 10000));
continue;
}
else
{
errorNumber = sqlEx.Number;
}
}
catch (Exception e)
{
throw e;
}
}
}
// No exceptions (break out from the loop)
break;
} // End of the for (int r = 0; r < sqlRetryCount; r++)
// Return the error number
return errorNumber;
} // End of the Delete method
#endregion
} // End of the classServices
Our database provider depends on IOptions<DatabaseOptions>, database options is a simple model with only two properties. We add services for database options and our MS SQL database provider in the ConfigureServices method in the StartUp class.
public void ConfigureServices(IServiceCollection services)
{
// Add the mvc framework
services.AddRazorPages();
// Create database options
services.Configure<DatabaseOptions>(options =>
{
options.connection_string = configuration.GetSection("AppSettings")["ConnectionString"];
options.sql_retry_count = 1;
});
// Add repositories
services.AddSingleton<IDatabaseRepository, MsSqlRepository>();
services.AddSingleton<IStaticPageRepository, StaticPageRepository>();
} // End of the ConfigureServices methodUse the database provider
We have several classes that uses our database provider, our StaticPageRepository class is responsible of static pages on our website and it uses our IDatabaseRepository interface. The class below displays some examples on how our database provider is used.
public class StaticPageRepository : IStaticPageRepository
{
#region Variables
private readonly IDatabaseRepository database_repository;
private readonly IHttpClientFactory client_factory;
#endregion
#region Constructors
public StaticPageRepository(IDatabaseRepository database_repository, IHttpClientFactory client_factory)
{
// Set values for instance variables
this.database_repository = database_repository;
this.client_factory = client_factory;
} // End of the constructor
#endregion
#region Insert methods
public Int32 Add(StaticPage post)
{
// Create the int to return
Int32 idOfInsert = 0;
// Create the sql statement
string sql = "INSERT INTO dbo.static_pages (connected_to_page, link_name, title, main_content, meta_description, meta_keywords, "
+ "meta_robots, page_name, inactive, news_search_string, sort_value) "
+ "VALUES (@connected_to_page, @link_name, @title, @main_content, @meta_description, @meta_keywords, @meta_robots, "
+ "@page_name, @inactive, @news_search_string, @sort_value);SELECT CAST(SCOPE_IDENTITY() AS INT);";
// Create parameters
IDictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@connected_to_page", post.connected_to_page);
parameters.Add("@link_name", post.link_name);
parameters.Add("@title", post.title);
parameters.Add("@main_content", post.main_content);
parameters.Add("@meta_description", post.meta_description);
parameters.Add("@meta_keywords", post.meta_keywords);
parameters.Add("@meta_robots", post.meta_robots);
parameters.Add("@page_name", post.page_name);
parameters.Add("@inactive", post.inactive);
parameters.Add("@news_search_string", post.news_search_string);
parameters.Add("@sort_value", post.sort_value);
// Insert the post
this.database_repository.Insert<Int32>(sql, parameters, out idOfInsert);
// Return the id of the inserted item
return idOfInsert;
} // End of the Add method
#endregion
#region Update methods
public void Update(StaticPage post)
{
// Create the sql statement
string sql = "UPDATE dbo.static_pages SET connected_to_page = @connected_to_page, link_name = @link_name, title = @title, main_content = @main_content, "
+ "meta_description = @meta_description, meta_keywords = @meta_keywords, meta_robots = @meta_robots, page_name = @page_name, "
+ "inactive = @inactive, news_search_string = @news_search_string, sort_value = @sort_value WHERE id = @id;";
// Create parameters
IDictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@id", post.id);
parameters.Add("@connected_to_page", post.connected_to_page);
parameters.Add("@link_name", post.link_name);
parameters.Add("@title", post.title);
parameters.Add("@main_content", post.main_content);
parameters.Add("@meta_description", post.meta_description);
parameters.Add("@meta_keywords", post.meta_keywords);
parameters.Add("@meta_robots", post.meta_robots);
parameters.Add("@page_name", post.page_name);
parameters.Add("@inactive", post.inactive);
parameters.Add("@news_search_string", post.news_search_string);
parameters.Add("@sort_value", post.sort_value);
// Update the post
this.database_repository.Update(sql, parameters);
} // End of the Update method
#endregion
#region Count methods
public Int32 GetCountBySearch(string[] keywords)
{
// Create the sql statement
string sql = "SELECT COUNT(id) AS count FROM dbo.static_pages WHERE 1 = 1";
for (int i = 0; i < keywords.Length; i++)
{
sql += " AND (title LIKE @keyword_" + i.ToString() + " OR meta_description LIKE @keyword_" + i.ToString() + ")";
}
sql += ";";
// Create parameters
IDictionary<string, object> parameters = new Dictionary<string, object>();
for (int i = 0; i < keywords.Length; i++)
{
parameters.Add("@keyword_" + i.ToString(), "%" + keywords[i].ToString() + "%");
}
// Get the count
Int32 count = this.database_repository.GetCount<Int32>(sql, parameters);
// Return the count
return count;
} // End of the GetCountBySearch method
#endregion
#region Get methods
public StaticPage GetOneById(Int32 id)
{
// Create the sql statement
string sql = "SELECT * FROM dbo.static_pages WHERE id = @id;";
// Create parameters
IDictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@id", id);
// Get the post
StaticPage post = this.database_repository.GetModel<StaticPage>(sql, parameters);
// Return the post
return post;
} // End of the GetOneById method
public StaticPage GetOneByPageName(string pageName)
{
// Create the sql statement
string sql = "SELECT * FROM dbo.static_pages WHERE page_name = @page_name AND connected_to_page = @connected_to_page;";
// Create parameters
IDictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@page_name", pageName);
parameters.Add("@connected_to_page", 0);
// Get the post
StaticPage post = this.database_repository.GetModel<StaticPage>(sql, parameters);
// Return the post
return post;
} // End of the GetOneByPageName method
public IList<StaticPage> GetBySearch(string[] keywords, Int32 pageSize, Int32 pageNumber, string sortField, string sortOrder)
{
// Make sure that sort variables are valid
sortField = GetValidSortField(sortField);
sortOrder = GetValidSortOrder(sortOrder);
// Create the sql statement
string sql = "SELECT * FROM dbo.static_pages WHERE 1 = 1";
for (int i = 0; i < keywords.Length; i++)
{
sql += " AND (title LIKE @keyword_" + i.ToString() + " OR meta_description LIKE @keyword_" + i.ToString() + ")";
}
sql += " ORDER BY " + sortField + " " + sortOrder + " OFFSET @pageNumber ROWS FETCH NEXT @pageSize ROWS ONLY;";
// Create parameters
IDictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@pageNumber", (pageNumber - 1) * pageSize);
parameters.Add("@pageSize", pageSize);
for (int i = 0; i < keywords.Length; i++)
{
parameters.Add("@keyword_" + i.ToString(), "%" + keywords[i].ToString() + "%");
}
// Get the list
IList<StaticPage> posts = this.database_repository.GetModelList<StaticPage>(sql, parameters, pageSize);
// Return the list of posts
return posts;
} // End of the GetBySearch method
#endregion
#region Delete methods
public Int32 DeleteOnId(Int32 id)
{
// Create the sql statement
string sql = "DELETE FROM dbo.static_pages WHERE id = @id;";
// Create parameters
IDictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@id", id);
// Delete the post
Int32 errorNumber = this.database_repository.Delete(sql, parameters);
// Return error number
return errorNumber;
} // End of the DeleteOnId method
#endregion
} // End of the class