Retrieving and Saving data using Massive.DynamicRepository

###Links

• Declaration
• Enable Logging
• Inserting a Record
• Updating a Record
• Saving a Record
• Delete
• Intercepting Insert, Update, Save
• Get a Record by Id
• Inspecting Returned Objects
• Get a Single Record by Where Clause
• Retrieving Multiple Records by Where Clause
• Pagination
• Ad Hoc Queries
• Stored Procedures
• Intercepting Retrieval

###Declaration You can create an instance of a DynamicRepository like so:

public class Blogs : DynamicRepository { }

this will give you access to CRUD operations associated with a Blogs table.

There are overrides if you need to support unconventional schema:

public class Blogs : DynamicRepository
{
  public Blogs() : base(tableName: "Blog", primaryKeyField: "BlogId");
}

It's recommended that you stay with conventions (you'll have a much better development experience, and less code).

###Enable Logging Logging can be enabled globally. DynamicRepository has built in logging to the console by setting a static field and setting the logging delegate.

DynamicRepository.WriteDevLog = true;
DynamicRepository.LogSqlCommand = DynamicRepository.LogSqlDelegate

If you've bootstrapped a solution with Oak, logging will automatically be enabled for debug builds running under IISExpress.

You can define the logging delegate to be your custom implementation if you need to log elsewhere:

DynamicRepository.WriteDevLog = true;
DynamicRepository.LogSqlCommand = (sender, query, parameters) =>
{
}

###Inserting a Record Inserting a record can be done by passing an object with public properties to the repository.

var blogId = blogs.Insert(new { Title = "A blog post" });

###Updating a Record Updating a record can be done by passing an object with a key to the repository.

blogs.Update(new { Title = "An update" }, blogId);

###Saving a Record Saving a record will look at the object and determine if an insert or update needs to be performed.

//performs insert
blogs.Save(new { Title = "A blog post" });

//performs update
blogs.Save(new { Id = blogId, Title = "A blog post" });

###Delete Deleting a record is peformed by passing an Id to delete by.

blogs.Delete(blogId);

or a where clause

blogs.Delete(where: "IsPublished = @0", args: new object[] { false });

###Intercepting Insert, Update, Save You can explicitly set what properties are saved by overriding public virtual IDictionary<string, object> GetAttributesToSave(object o) in your derivation of DynamicRepository. Here is an example of how to override this method:

    public override IDictionary<string, object> GetAttributesToSave(object o)
    {
      var attributes = base.GetAttributesToSave(o);
      attributes.Remove("SomeProperty);
      attributes.Add("LastSaved", DateTime.Now);
      return attributes;
    }

###Get a Record by Id How to retrieve a single record by Id. Null will be returned if no record is found.

dynamic blog = blogs.Single(blogId):

###Inspecting Returned Objects Any object inheriting from Gemini will return a nicely formatted object hierarchy.

To Display Object in the IISExpress Console

Console.Out.WriteLine(blog)

To Inspect Object in the Immediate Window

blog.ToString(),nq

To Inspect Object in a Razor Template

<pre>@blog</pre>

###Get a Single Record by Where Clause How to retrieve a single record with a where clause. Null will be returned if no record is found.

dynamic blog = blogs.SingleWhere("Title = @0", "Some Title");

If you find that you're using the same query over and over again, you may want to add a method to your DynamicRepository that encapsulates the query:

class Blogs : DynamicRepository
{
  public dynamic WithTitle(string title)
  {
    return SingleWhere("Title = @0", title);
  }
}

dynamic blog = blogs.WithTitle("Some Title");

###Retrieving Multiple Records by Where Clause How to retrieving multiple records with a where clause.

//retrieve all records
IEnumerable<dynamic> result = blogs.All();

//retrieve records with a where clause
IEnumerable<dynamic> result = blogs.All(where: "PostedDate > @0", args: new object[] { DateTime.Today });

//where clause over loads
IEnumerable<dynamic> result = blogs.All(
  where: "PostedDate > @0",
  orderBy: "PostedDate desc",
  columns: "Id, Title",
  limit: 20,
  args: new object[] { DateTime.Today });

###Pagination Paged results can also be returned.

dynamic result = blogs.Paged(
  where: "PostedDate > @0",
  orderBy: "PostedDate desc",
  columns: "Id, Title",
  pageSize: 20,
  currentPage: 1,
  args: new object[] { DateTime.Today });

int totalRecords = result.TotalRecords;
int totalPages = result.TotalPages;
IEnumerable<dynamic> items = result.Items;

###Ad Hoc Queries If you need to go right against the metal, and deviate from the table convention, you can do so (with great power comes great responsibility):

IEnumerable<dynamic> result = blogs.Query(
  "select Id, Text from Comments where BlogId = @0 order by BlogId",
  new object[] { blogId }
)";

consider using Dynamic Model Assocations

###Stored Procedures You can also call stored procedures using the Query method:

IEnumerable<dynamic> result = blogs.Query("GetRecords @0", "Name");

###Intercepting Retrieval

In DynamicRepository, a property: public Func<dynamic, dynamic> Projection { get; set; } has been added. Setting this property will tell DynamicRepository to execute the projection for any query that is executed.

Here is an example of a projection being defined for a DynamicRepository:

public class Blogs : DynamicRepository
{
  public Blogs()
  {
    //for any query given, a Blog will be returned as opposed to a Gemini
    Projection = d => new Blog(d);
  }
}

You can do more interesting thing than just return a new type. Because the value returned is dynamic, you can return different types based on the data returned:

public class Animals : DynamicRepository
{
  public Animals()
  {
    //these objects returned from this method do not have to 
    //derive from the same type (it's dynamic)
    Projection = d =>
    {
      if(d.SomeColumnInRow == "Mammal") return new Mammal(d);

      else if(d.SomeColumnInRow == "Reptile") return new Reptile(d);

      else return d;
    }
  }
}

Here is another example where aliasing is performed on the columns that are returned to be more friendly to a developer:

public class People : DynamicRepository
{
  public People()
  {
    //you can perform any aliasing at this point and 
    //change values to your liking
    Projection = d =>
    {
      //the column in the database is called First_Name
      //we can add properties on the fly like so
      d.FirstName = d.First_Name;

      //the class type for d is Gemini
      //Gemini has methods that allow you to delete properties
      //all together
      d.DeleteMember("First_Name");

      return d;
    }
  }
}