How to load views from database in MVC

We may need to store views in database tables and tell MVC engine to look for views from database instead on file system. This way, we can also define the hierarchy of pages. I'm going to use my previous implementation of loading views from assembly and with a few modifications, we will make that work for our database. First off, we will create a table named Page with the following schema.

Schema

We will now modify methods IsExistByVirtualPath and GetByVirtualPath which will look for views in table and return it's content.

public static bool IsExistByVirtualPath(string virtualPath)
{
    string connectionString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
    bool flag = false;

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = connection.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = @"IF EXISTS (SELECT [Page].[Id] FROM [Page] WHERE [Page].[VirtualPath] = @virtualPath)
                    BEGIN
                        SELECT 1
                    END
                    ELSE
                    BEGIN
                        SELECT 0
                    END";
        cmd.Parameters.AddWithValue("@virtualPath", virtualPath);
        connection.Open();

        flag = Convert.ToBoolean(cmd.ExecuteScalar());
    }

    return flag;
}

public static string GetByVirtualPath(string virtualPath)
{
    string connectionString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = connection.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT [Page].[Body] FROM [Page] WHERE [Page].[VirtualPath] = @virtualPath";
        cmd.Parameters.AddWithValue("@virtualPath", virtualPath);
        connection.Open();

        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                byte[] b = null;
                if (!dr.IsDBNull(dr.GetOrdinal("Body")))
                {
                    b = (byte[])dr.GetValue(dr.GetOrdinal("Body"));
                }

                if (b == null)
                    return string.Empty;

                return System.Text.ASCIIEncoding.ASCII.GetString(b);
            }
        }
    }

    return string.Empty;
}

Views will now load from the database. Download the sample project from here.

Happy Coding!

And we know you want to learn more! So, register now for FalafelCon 2014 and perfect your skills, expand your horizons, and get inspired.

comments powered by Disqus

Get weekly updates in your inbox!