Generisk databasklass för MS SQL i ASP.NET Core

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 interface

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

Tjä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.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);

    // 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

Hur 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

Lämna ett svar

E-postadressen publiceras inte. Obligatoriska fält är märkta *