I det här inlägget beskriver vi hur du kan skapa en generisk databasklass för MS SQL i ASP.NET Core. Vår databasklass kommer att inkludera alla metoder som behövs för att kommunicera med en MS SQL-databas och den kan hantera alla våra olika modeller.
En generisk klass innebär mindre kod att skriva och kod som är lätt att underhålla. Istället för att ha samma kod i många klasser i ditt projekt kan ha den här koden i en klass och behöver då bara göra ändringar på ett ställe. En generisk klass innehåller generiska metoder som kan hantera nästan vilken datatyp som helst. En klient som använder en generisk metod bestämmer datatypen som används i parametrar och datatypen som returneras av metoden.
Gränssnitt
Vi har skapat ett generiskt gränssnitt för en databasklass, detta gränssnitt kommer att vara ett beroende i andra klasser i vårt projekt. Detta gränssnitt ska utformas så att det kan implementeras av en MS SQL-klass, en MySql-klass eller en annan databasklass. Detta gränssnitt är avsett att användas med synkrona metoder, en out parametern kan inte användas i asynkrona metoder och asynkrona metoder måste returnera en 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 interfaceDatabasklass för MS SQL
Vi har skapat en Microsoft SQL-databasklass som implementerar vårt IDatabaseRepository-gränssnitt. Denna klass innehåller synkrona metoder. Om vi vill göra metoderna i denna klass asynkrona måste vi returnera Task och kan inte använda out parametrar. Om vi måste returnera flera värden i en asynkron metod måste vi returnera en omslagsmodell kring den generiska datatypen eller returnera 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 classTjänster
Vår databasklass beror på IOptions<DatabaseOptions>, DatabaseOptions är en enkel modell med endast två egenskaper. Vi lägger till tjänster för databasinställningar och vår MS SQL-databasklass i metoden ConfigureServices i klassen StartUp.
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 methodHur använda databasklassen?
Vi har flera klasser som använder vår databasklass, vår StaticPageRepository-klass ansvarar för statiska sidor på vår hemsida och den använder vårt IDatabaseRepository-gränssnitt. I klassen nedan visas några exempel på hur vår databasklass används.
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