How do I add a new table to a Telerik Open Access MVC project?

Question

I've inherited a MVC project that seems to use Telerik Open Access to handle data instead of using something I'm more familiar with like entity framework. I'm trying to understand the whole concept of how to work with this data method, but right now I'm just needing to find out how to add a table. I've limited my code examples to one table, but in reality there are dozens of them.

So I see that the class OpenAccessContext.cs has a database connection string, but it also has a IQueryable item made up of the class tblMaterial. The tblMaterial class is defined in tblMaterial.cs. I don't understand how this class is connected to the SQL database version of tblMaterial (so feel free to educate me on that).

I have a table called tblContacts in the SQL database. What do I need to do to connect it to my project? There's no "update from database" option when I right click any object in the solution (because they're all just classes). Will I need to create a new class manually called tblContacts.cs? If so, how do I connect it to the database version of tblContacts? Am I going to need to manually change multiple classes to add the table (OpenAccessContext, MetadataSources, Repository, etc.)?

I tried to keep this as one simple question (how do I add a table) so I don't get dinged, but any light you can shine on the Telerik Open Access would be helpful. (Please don't ding me for asking that!) I checked out the Telerik documentation here: http://docs.telerik.com/data-access/developers-guide/code-only-mapping/getting-started/fluent-mapping-getting-started-fluent-mapping-api , but it's related to setting up a new open access solution. I need to know how to modify one (without ruining the already working code). Thank you in advance for your help!

Here's the solution as seen in Visual Studio:

• Open Access
  • Properties
  • References
  • OpenAccessContext.cs
  • OpenAccessMetadataSources.cs
  • Repository.cs
  • tblMaterial.cs

Here's the code:

OpenAccessContext.cs

namespace OpenAccess
{
public partial class OpenAccessContext : OpenAccessContext
{
    static MetadataContainer metadataContainer = new OpenAccessMetadataSource().GetModel();

    static BackendConfiguration backendConfiguration = new BackendConfiguration()
    {
        Backend = "mssql"
    };

    private static string DbConnection = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;

    private static int entity = ConfigurationManager.AppSettings["Entity"] == "" ? 0 : int.Parse(ConfigurationManager.AppSettings["Entity"]);

    public OpenAccessContext() : base(DbConnection, backendConfiguration, metadataContainer)
    {

    }

    public IQueryable<tblMaterial> tblMaterials
    {
        get 
        { 
            return this.GetAll<tblMaterial>(); //.Where(a => a.EntityId == entity);
        }
    }
 }
}

OpenAccessMetadataSources.cs

namespace OpenAccess
{
public class OpenAccessMetadataSource : FluentMetadataSource
{
    protected override IList<MappingConfiguration> PrepareMapping()
    {
        var configurations = new List<MappingConfiguration>();
        // tblMaterial
        var materialConfiguration = new MappingConfiguration<tblMaterial>();
        materialConfiguration.MapType(x => new
        {
            MaterialId = x.MaterialId,
            MaterialName = x.MaterialName,
            MaterialDescription = x.MaterialDescription,
            MaterialActive = x.MaterialActive,
            MaterialUsageType = x.MaterialUsageType,
            AddDate = x.AddDate,
            AddBy = x.AddBy,
            ModDate = x.ModDate,
            ModBy = x.ModBy
        }).ToTable("tblMaterial");
        materialConfiguration.HasProperty(x => x.MaterialId).IsIdentity(KeyGenerator.Autoinc);
     }
  }
}

Repository.cs

namespace OpenAccess
{
public class Repository : IRepository
{

    #region private variables

    private static OpenAccessContext dat = null;

    #endregion private varibles

    #region public constructor

    /// <summary>
    /// Constructor
    /// </summary>
    public Repository()
    {
        if (dat == null)
        {
            dat = new OpenAccessContext();                
        }
    }

    #endregion public constructor

    #region Material (tblMaterials)
    public int CreateMaterial(tblMaterial itm)
    {
        try
        {
            dat.Add(itm);
            dat.SaveChanges();
            return itm.MaterialId;
        }
        catch (Exception)
        {
            return 0;
        }
    }
 }
}

tblMaterial.cs

namespace OpenAccess
{
public class tblMaterial
{

    public int MaterialId { get; set; }
    public string MaterialName { get; set; }
    public string MaterialDescription { get; set; }
    public bool MaterialActive { get; set; }
    public int MaterialUsageType { get; set; }
    public DateTime? AddDate { get; set; }
    public string AddBy { get; set; }
    public DateTime? ModDate { get; set; }
    public string ModBy { get; set; }

}
}

Answer 1

In the case of tblContacts, I would suggest to you the following workflow for extending the model:

1. Add a new class file that will hold the definition of the tblContact POCO class. In this class add properties that will correspond to the columns of the table. The types of the properties should logically match the datatypes of the table columns.
2. In the OpenAccessMetadataSource class, add a new MappingConfiguration<tblContact> for the tblContact class and using explicit mapping provide the mapping details that logically connect the tblContact class with the tblContacts table. Make sure to add both the existing and the new mapping configurations to the configurations list.
3. Expose the newly added class through an IQueryable<tblContact> property in the context. This property will allow you to compose LINQ queries against the tblContacts table.

Regarding the Repository class, it seems like it is related to the custom logic of the application. It surely is not a file generated by Data Access. Therefore, you need to discuss it in your team.

I also strongly advise you against using OpenAccess in the namespaces of your application. This is known to interfere with the Data Access' namespaces during build time and at some point it causes runtime errors.

I hope this helps.