Skip to content

Generic database provider for MS SQL in ASP.NET Core

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 interface

MS 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 class

Services

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 method

Use 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

Leave a Reply

Your email address will not be published. Required fields are marked *