+1-888-365-2779
Try Now
More in this section
Categories
Bloggers
Blogs RSS feed

Building Data Layers Quickly with OpenAccess

by Josh Morales

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!

8 comments

Leave a comment
  1. Jim Jul 08, 2011
    Josh,

    First of all, I want to thank you for the number of technical subjects that you blog about.  They are always relevant to real-world scenarios and provide plenty of detail.

    Secondly, I want to ask the you excuse my ignorance.  I have never dealt with data layers and frankly am not sure what they even are or are used for.  Until this point, any data that I use on my website is retrieved using a SQL stored procedure call from a class that I built.  Can you explain to the ignorant (me) what a data layer is, what its practical applications are, and what the advantages/disadvantages are of using data layers over some other method?

    Thank you.
  2. Josh Jul 08, 2011
    Jim, I'm glad that you are finding these posts helpful, and appreciate your feedback.

    With regard sto data layers, there are many reasons to use them, and the benefits of separating your data from the application almost always far outweigh any time spent integrating with one. Here is a brief article I found that talks a bit about using a data layer.

    They are helpful in making it easier for your project to interact with data. OpenAccess is a great way to get started, as noted above, there's not a whole lot you need to setup to get started. take a look at the videos linked in More Information for a very helpful walkthrough!  
  3. Dan Jul 22, 2011
    Josh please provide a zip with this ... it seems when you do these blog posts you always show only part of the solution. What about the views?
  4. Josh Jul 25, 2011
    @Peter the completed project will be available in the Q2 SDK release, however I see your point, I didn't talk about using OpenAccess in the user controls.

    I will update the post this week with some sample code, thanks for your feedback!
  5. Jonathan Apr 17, 2012
    Hello Josh, I am just now finding this post as I am a newer user of Sitefinity. I have actually been trying to find examples/tutorials related to accessing an external Sql Database using the Sitefinity provider model. The data is not media or files, but rather just numbers and strings that I would like to surface in custom widgets in my site. In your comments here, you say you have examples in the SDK, but there are no examples in the SDK that you can download today. Everything is reference to online documentation, and actually the developer documentation doesn't even have a link in the SDK browser, which is a bit frustrating. Can you tell me where your examples live now? If not, can you point me to a other posts that cover my scenario? (if the answer is spread across several articles that need to be stitched together, that is fine too).
  6. Jonathan Apr 17, 2012
    Hello Josh, I am just now finding this post as I am a newer user of Sitefinity. I have actually been trying to find examples/tutorials related to accessing an external Sql Database using the Sitefinity provider model. The data is not media or files, but rather just numbers and strings that I would like to surface in custom widgets in my site. In your comments here, you say you have examples in the SDK, but there are no examples in the SDK that you can download today. Everything is reference to online documentation, and actually the developer documentation doesn't even have a link in the SDK browser, which is a bit frustrating. Can you tell me where your examples live now? If not, can you point me to a other posts that cover my scenario? (if the answer is spread across several articles that need to be stitched together, that is fine too).
  7. Jonathan Apr 18, 2012
    Update: The SDK does have the code, but the reason why I didn't see it is because there's a problem with the SDK installing to a directory other than the default. It copies some of the files to the custom directory, but the rest to the C:\... default directory. I already submitted a PITS for this.
  8. Josh Apr 18, 2012
    Jonathan, thanks for the update and report about the issues with the SDK, I'm glad you were able to find the examples. If you have any additional questions about the SDK, stop by the Sitefinity SDK forum and open a thread. I watch the forums and participate regularly.

    Leave a comment