Building Data Layers Quickly with OpenAccess

Building Data Layers Quickly with OpenAccess

Posted on July 06, 2011 0 Comments

The content you're reading is getting on in years
This post is on the older side and its content may be out of date.
Be sure to visit our blogs homepage for our latest news, updates and information.

In last week’s webinar on building Sitefinity Intra-Site Modules, we used the Entity Framework to build a simple data layer, as we have had many requests for a module example that does not use OpenAccess.

However, I wanted to take this opportunity to compare this approach to OpenAccess by using it to recreate the data layer for the example. This process allows you to further simplify and package your modules so that they are fully self-contained and reusable.

Code-Only Data Layer

One of the greatest strengths of OpenAccess is that it allows you to build “Code-Only” data layers. There are no wizards, no designers, and especially no messy XML files to handle mapping your classes.

Instead, OpenAccess offers a powerful Fluent Mapping Interface that handles wiring everything up in code. Not only does this remove the need for external tools and mapping files, but it also means that you can fully encapsulate ALL the code necessary for installing your data layer.

Enhance the Project for OpenAccess

Before you begin using the Fluent Mapping API, your project must be enhanced for OpenAccess. This is simply a matter of unloading your project, adding a short snippet of code to the .cjsproj file, then reloading the project back into your solution.

A complete walkthrough this process is outlined in the OpenAccess documentation: Integration with OpenAccess Enhancer.

Three Classes to a Data Layer

To recreate the Testimonials Module Data Layer with OpenAccess, we only need to implement three classes.

Testimonials Model

This is the data model for the class that defines all the properties the module needs to persist. It’s a straightforward, POCO model, which is what you want in a simple Data Layer.

/// <summary>
/// Class used to represent a Testimonial
/// </summary>
public class Testimonial
{
    /// <summary>
    /// Gets or sets the testimonial ID.
    /// </summary>
    /// <value>
    /// The testimonial ID.
    /// </value>
    public int Id { get; set; }

    /// <summary>
    /// Gets or sets the name of the user who submitted the testimonial.
    /// </summary>
    /// <value>
    /// The name of the testimonial author.
    /// </value>
    public string Name { get; set; }

    /// <summary>
    /// Gets or sets a one-line summary for the testimonials List View.
    /// </summary>
    /// <value>
    /// The testimonial summary.
    /// </value>
    public string Summary { get; set; }

    /// <summary>
    /// Gets or sets the full text of the testimonial.
    /// </summary>
    /// <value>
    /// The testimonial text.
    /// </value>
    public string Text { get; set; }

    /// <summary>
    /// Gets or sets the date the testimonial was created.
    /// </summary>
    /// <value>
    /// The date the testimonial was created.
    /// </value>
    public DateTime DatePosted { get; set; }

    /// <summary>
    /// Gets or sets the star rating.
    /// </summary>
    /// <value>
    /// The star rating.
    /// </value>
    public decimal Rating { get; set; }

    /// <summary>
    /// Gets or sets a value indicating whether this <see cref="Testimonial"/> is published.
    /// </summary>
    /// <value>
    ///   <c>true</c> if published; otherwise, <c>false</c>.
    /// </value>
    public bool Published { get; set; }

    /// <summary>
    /// Initializes a new instance of the <see cref="Testimonial"/> class.
    /// </summary>
    public Testimonial()
    {
        DatePosted = DateTime.Now;
    }

}

FluentMetaDataSource

This is the class that handles mapping the properties of the Testimonials object to the database. The Fluent Mapping API allows you to perform a wide range of mappings, from a simple default mapping to mapping associations between types and even type inheritance.

For our example, we only have the one type, so mapping is pretty straightforward.

public class TestimonialsFluentMetaDataSource : FluentMetadataSource
{
    /// <summary>
    /// Called when this context instance is initializing and a model needs to be obtained.
    /// </summary>
    /// <returns></returns>
    protected override IList<MappingConfiguration> PrepareMapping()
    {
        // initialize mappings
        var mappings = new List<MappingConfiguration>();

        // map to table
        var tableMapping = new MappingConfiguration<Testimonial>();
        tableMapping.MapType().ToTable("sf_testimonials");

        // map properties
        tableMapping.HasProperty(t => t.Id).IsIdentity(Telerik.OpenAccess.Metadata.KeyGenerator.Autoinc);
        tableMapping.HasProperty(t => t.Name).HasLength(255).IsNotNullable();
        tableMapping.HasProperty(t => t.Summary).HasLength(255).IsNotNullable();
        tableMapping.HasProperty(t => t.Text).HasColumnType("varchar(max)");
        tableMapping.HasProperty(t => t.Rating).IsNotNullable();
        tableMapping.HasProperty(t => t.DatePosted).IsNotNullable();
        tableMapping.HasProperty(t => t.Published).IsNotNullable();

        // save mapping
        mappings.Add(tableMapping);
        return mappings;
    }
}

FluentContext

This is the class responsible for actually interacting with the data, as it is a context that is specific to your custom data type. This simply needs to be initialized with a connection string, then modified with a custom IQueryable method to retrieve items of the specified type.

public class TestimonialsContext : OpenAccessContext
{
    /// <summary>
    /// Gets the connection string from Sitefinity configuration.
    /// </summary>
    static string ConnectionString
    {
        get
        {
            var config = Config.Get<DataConfig>();
            return config.ConnectionStrings["Sitefinity"].ConnectionString;
        }
    }

    /// <summary>
    /// Testimonials Fluent Metadata Source instance
    /// </summary>
    static TestimonialsFluentMetaDataSource metadata = new TestimonialsFluentMetaDataSource();


    /// <summary>
    /// Initializes a new instance of the <see cref="TestimonialsContext"/> class.
    /// </summary>
    public TestimonialsContext() : base(ConnectionString, new BackendConfiguration() { Backend = "mssql" }, metadata) { }

    /// <summary>
    /// Gets an IQueryable result of all testimonials.
    /// </summary>
    public IQueryable<Testimonial> Testimonials
    {
        get { return GetAll<Testimonial>(); }
    }
}

Installation

This is where we can truly take advantage of the power and simplicity of OpenAccess. Previously, we build the Data Layer by manually creating the data tables and using that to build the Entity Model. This means that anyone who wishes to reuse this module must also make the same manual modifications to the database.

OpenAccess handles all of this for you, not only creating the table, but also mapping any relationships between entities.

Finally, since our module has an Install method that executes before anything else, we can simply put the OpenAccess initialization before anything else, so that all of this is handled automatically.

/// <summary>
/// Installs this module in Sitefinity system for the first time.
/// </summary>
/// <param name="initializer">The Site Initializer. A helper class for installing Sitefinity modules.</param>
public override void Install(SiteInitializer initializer)
{
    #region Create Data Tables

    // check context for db
    using (var context = new TestimonialsContext())
    {
        var schemaHandler = context.GetSchemaHandler();
        string script = null;

        // check if db needs creating or updating
        if (schemaHandler.DatabaseExists())
            script = schemaHandler.CreateUpdateDDLScript(null);
        else
        {
            schemaHandler.CreateDatabase();
            script = schemaHandler.CreateDDLScript();
        }

        // execute script to create or update database
        if (!string.IsNullOrEmpty(script))
            schemaHandler.ExecuteDDLScript(script);
    }

    #endregion

    #region Install Pages 
    
    // ...

    #endregion

}

Now when the module is first installed, OpenAccess will take care of creating all the necessary tables and relationships. You could use also use the custom context to easily create sample items to demonstrate the module. In fact, this is exactly the process used by our Sitefinity SDK examples!

Using the Data Layer

A comment from this post reminded me that I didn’t talk about using this OpenAccess Data Layer in the User Controls for the module. Fortunately, it is very easy to replace the Entity Framework module with OpenAccess.

As an example, I’ll modify the public TestimonialsView control to bind with OpenAccess instead of using the EntityDataSource. A complete working example of this module using OpenAccess will be available in the next SDK release (Q2).

Remove the TestimonialsSource control, and also remove the DataSourceID property from the TestimonialsRepeater on the control. Now we can bind the repeater manually in the code-behind.

First add an instance of the FluentContext class we created above to the control.

private TestimonialsContext context = new TestimonialsContext();

Now simply retrieve the items through that context, using the IQueryable property we defined, and bind the result to the Repeater. Here I’ve wrapped the code in a new method, which I call in Page_Load depending on the mode of the control.

protected void Page_Load(object sender, EventArgs e)
{
    if (IsPostBack) return;

    switch (Mode)
    {
            // List View
        case ControlMode.List:
            ShowList();
            break;

            // Details View
        case ControlMode.Details:
            ShowDetails();
            break;
    }
}
  
private void ShowList()
{
    // retrieve testimonials and bind
    var testimonials = context.Testimonials.Where(t=> t.Published).Take(Count);
    TestimonialsRepeater.DataSource = testimonials;
    TestimonialsRepeater.DataBind();
    TestimonialsMultiView.SetActiveView(ListView);
}

As you can see, the Testimonials as an extension method Where which allows you to retrieve based on specific criteria.

Adding or editing items is handled similarly, with few changes needed from our original code. Here is the btnSave_Click method from the AddEditView.ascx.cs file. Notice that the only thing that really changed was the name of the Add method.

protected void btnSave_Click(object sender, EventArgs e)
{
    switch (Mode)
    {
        case AdminControlMode.Edit:

            // update existing testimonial
            var testimonial = context.Testimonials.Where(t => t.Id == TestimonialID).FirstOrDefault();
            if (testimonial == null) return;
            testimonial.Name = Name.Text;
            testimonial.Summary = Summary.Text;
            testimonial.Text = Text.Value.ToString();
            testimonial.Rating = Rating.Value;
            testimonial.Published = Published.Checked;
            break;

        case AdminControlMode.Create:
            // create and save new testimonial
            var newTestimonial = new Testimonial();
            newTestimonial.Name = Name.Text;
            newTestimonial.Summary = Summary.Text;
            newTestimonial.Text = Text.Value.ToString();
            newTestimonial.Rating = Rating.Value;
            newTestimonial.Published = Published.Checked;
            context.Add(newTestimonial);
            break;
    }

Once again make sure to include an instance of the FluentContext for the Testimonials class.

TestimonialsContext context = new TestimonialsContext();

With OpenAccess, it’s really that simple! For more information on working with data using the FluentContext, see this documentation: Working With Data.

More Information

This post only scratches the surface on how OpenAccess makes developing your Data Layer easier. For more, I encourage you to take a look at this series of videos on using the Fluent Mapping API, as well as the OpenAcccess Code-Only Documentation.

Finally, be sure to download the free trial of OpenAccess as its usefulness is certainly not limited to Sitefinity!

progress-logo

The Progress Team

View all posts from The Progress Team on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.

Comments

Comments are disabled in preview mode.
Topics

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

Loading animation