DatabaseObjectMapper 1.1.1

#About DatabaseObjectMapper

The DatabaseObjectMapper allows you to quickly map classes to Sql Server database tables and columns.

At this time, this is inteded for use with Microsoft Sql Server.

#Using DatabaseObjectMapper

To begin using the DatabaseObjectMapper as an ORM after the NuGet Packages are installed, you must include the appropriate using statements.

using DatabaseObjectMapper;
using DatabaseObjectMapper.SqlServer;

#Creating New DatabaseObject Classes

public class MockModel : DatabaseObject
{
    [PrimaryKey]
    [Column("mock_id")]
    public int Id { get; set; }

    [Column("name")]
    public string Name { get; set; } = string.Empty;

    [Column("date_added")]
    public DateTime DateAdded { get; set; } = DateTime.Now;

    [Column("date_updated")]
    public DateTime? DateUpdated { get; set; }
...
}

In this example, MockModel inherits from DatabaseObject which gives access to the [Column], and [PrimaryKey] attributes. Also, certain additional properties of DefaultTable and ConnectionString are made available. None of these attributes or properties are required to be defined in the class unless you want those particular features.

#Default Values While you can specify the table when making calls to pull data for the DatabaseObject, it is recommended that you supply default values for the DefaultTable. This is the table in the database that will be used if no table or stored procedure is specified. If you are using properties such as "DateAdded" or "DateModified", you are recommended to go ahead and set DateTime.Now as the default values for those properties when instantiating the class so you should put preferred default values in your constructor. You can also specify the default ConnectionString value in the class if working with enumerables extension methods or can pass this along to the requests to load data into the class.

    public MockModel() {
	    DefaultTable = "mocks";
	    ConnectionString = DatabaseObjectTests.GlobalConnectionString;
    }

#Using Your DatabaseObject Derived Class

MockModel model = new MockModel();
model.LoadByPK(1);
if (model.Name == "First Mock")
{
	model.Name = "Replace Name";
	model.Save();
}

In this example, we create a new model, and we load mock data ID 1 by making a call to the LoadByPK method which loads the primary key value (in this case the mock_id column from the mocks default table) into the class. Now, model.MockID will contain 1, model.Name will contain the mock name, etc.

Next, in this example, we compare to see if the name of the first mock model is "First Mock". If so, we change the name to "Replace Name" and then call the .Save() method. This will perform an update statement where mock_id = 1 to set the name and save the changes to the database.

#Updating Database Records

You can update a record by loading it, changing values, then calling the Save() method as shown in the previous example. If the class you are using is an interface, you can define the type when calling the .Save() method. In the following, we tell the Save() method that we are using the MockModel class so should use the DefaultTable contained in this class.

mockModel.Save<MockModel>();

#Creating New Database Records

If the primary key is 0, a new insert statement will be supplied to the database instead of updating. If the primary key value is greater than 0, the save method will update the preexisting record.

The primary key is intended to be an integer.

public bool Example()
{
    Vendor vendor = new Vendor();
    vendor.Name = "Bob";
    vendor.Phone = "212-555-1212";
    if (vendor.Save<Vendor>() > 0)
        return true;
    return false;
}

When the Save method is called, the primary key of the new record will be returned. If this is 0, then the insert or update failed. If the primary key integer was 0, the primary key will automatically be updated during an insert.

#Using Lists

Multiple records can be returned so we can use List objects to store these. To load data into a List, use methods within DatabaseRequest.

List<MockModel> models = DatabaseRequest.LoadAll<MockModel>(GlobalConnectionString, "mocks");

#Passing Parameters to the Select(...) Method

List<MockLinkModel> links = DatabaseRequest.Select<MockLinkModel>(GlobalConnectionString, "SELECT * FROM mock_links WHERE mock_id = @mock_id", false, "@mock_id", pk);

In this example, we create new parameters while loading the links. The parameterized query uses @mock_id and then replaces that with the integer in the variable named pk. After reaching the parameter list, an unlimited number of parameters can be passed into the function.

For some functions and methods, instead of listing each of the parameters in this manner, you can also use an overload to pass a List<SqlParameter> object to keep your code more readable.

#Saving Multiple Records

Use the .SaveAll() method to save a list.

You can have multiple primary keys, and if so, each of those values must be exact for the save to happen. At least one primary key attribute must be supplied for SaveAll() to work.

#More Information About Save and SaveAll

If the primary key is 0, Save and SaveAll will create new records using INSERT statements to the table. If the primary key is greater than 0, Save and SaveAll will update the records that match the primary key using UPDATE statements to the table. You can pass the table name to these methods if you want to override the default table, e.g. Save("new_table_name"). You can also make calls directly to Insert or Update methods if you do not want the Save method to figure out whether the records already exist. These methods only work correctly if there is a primary key value set for the table and in the class because it uses these values to determine whether to perform an insert or update.

Note: If you specify a value for the primary key(s) but there is no record that matches the primary key in the WHERE clause of the query, then no update or insert will happen. To maintain speed, the system does not validate whether an entry exists before performing the insert or updates so the insert will only happen if the primary key value is set to 0. The primary key is always expected to be an auto-increment value. If the value is not 0, the record in the table must already exist for the update to happen correctly.

#Deleting Multiple Records

The List<T>.DeleteAll() method can be used to delete multiple records quickly. This will only delete records if a property has been set with the PrimaryKey and for records where this value matches. If there is no primary key attribute defined then no deletes will happen using this technique because the WHERE clause would be empty. This would be dangerous.

#Loading Rows Into Dynamic Objects using DatabaseRequest.SelectDynamic()

It is possible to load SQL queries into dynamic objects which can be referenced in real time. This means that if you updated a procedure to add a new column, it will load the parameters in real time without needing to recompile.

string names = string.Empty;
dynamic results = DatabaseRequest.SelectDynamic(connectionString, "SELECT example_id, example_name FROM example", false, null);
if (results != null)
{
    foreach (dynamic d in results)
    {
        names += $"{d.example_name} - {d.example_id}\r\n";
    }
}

In this example, the results dynamic object would contain anything that the example table contains. The class is dynamic so the properties of the class such as example_id, example_name, etc. are not specified anywhere in the source code until their attempted usage. The SelectDynamic function always returns an enumerable ExpandoObject. First check to make sure it is not null. Next, you can iterate over the results.

#Class Relationships

Automatic loading of relationships can be done using the Relationship attribute.

    public class MockModel : DatabaseObject
    {
        [PrimaryKey]
        [Column("mock_id")]
        public int Id { get; set; }

        [Column("name")]
        public string Name { get; set; } = string.Empty;

        [Relationship("mock_links")]
        public List<MockLinkModel> Links { get; set; } = new List<MockLinkModel>();

        public MockModel() { }

        public MockModel(string connectionString) {
            DefaultTable = "mocks";
            ConnectionString = connectionString;
        }
    }
...
        public void LoadRelationshipsExample(string connectionString)
        {
            MockModel model = new MockModel(connectionString);
            model.LoadByPK(999); // Load primary key 999.
            model.LoadRelationships();
        }
  

In this example, we load relationships for the table by calling the LoadRelationships() method. We simply need to specify the class type for the child relationship because we have defined the relationship in our parent class.