DatabaseObjectMapper 2.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;
    }

Static Connection String Property

To avoid the requirement of passing the connection string with every request, you can set the connection string using the ConnectionStringManager. This will be set globally as a static property; however, this uses AsyncLocal which should be thread safe. Every request will have a separate connection string, but you must set the connection string at the beginning of each request.

    In newer versions of web based .NET applications, created a Middleware for setting the connection string.
    public class ConnectionStringMiddleware
    {
        private readonly RequestDelegate _next;

        public ConnectionStringMiddleware(RequestDelegate next)
        {
            _next = next;
        }

        public async Task InvokeAsync(HttpContext context)
        {
            // Set the connection string for the current request
            ConnectionStringManager.ConnectionString = SetConnectionString(context);

            try
            {
                await _next(context); // Proceed with the request
            }
            finally
            {
                // Clean up if needed (not mandatory for AsyncLocal)
                ConnectionStringManager.ConnectionString = null;
            }
        }

        private string SetConnectionString(HttpContext context)
        {
            // Logic to determine the connection string, e.g., based on headers, subdomain, etc.
            // This should be loaded from elsewhere, but this is a hardcoded example for simplicity.
            return "Server=myServer;Database=myDatabase;User Id=myUser;Password=myPassword;";
        }
    }

After creating this Middleware, register it in Program.cs.

    app.UseMiddleWare<ConnectionStringMiddleware>();

After this, you can make requests to DatabaseRequestWithDefaults without specifying the database connection string, but if necessary, you can still pass the connection string without using the globally configuration by using DatabaseRequest instead.

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", null);

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.

Passing Parameterized Query Variables to Database Requests

There are four ways to send your parameters.

Class Property Names and Values

MockModel model = new() { Id = 123 };
model.DefaultTable = "mocks";
model.Select($"SELECT TOP 1 * FROM {model.DefaultTable} WHERE mock_id = @Id");

In this example, we set the Id from our MockModel object to 123. We can now put this directly into our query, and the parameter will be translated and safely parameterized.

Inline comma separated optional parameters.

int pk = 123;
string name = 'Testing';
List<MockLinkModel> links = DatabaseRequest.Select<MockLinkModel>(GlobalConnectionString, "SELECT TOP 10 * FROM mock_links WHERE mock_id = @pk OR name = @name", false, "@pk", pk, "@name", name);

In this example, we supply the parameters in the style @pk and then supply objects with values. In this example, we are searching for where the mock_id equals 123 or the name equals "Testing".

Using SqlParameterConfiguration

SqlParameterConfiguration configuration = new SqlParameterConfiguration();
configuration.AddSqlParameter("@name", testName);
MockModel mock = DatabaseRequest.SelectFirstWithParameters<MockModel>(connectionString, "SELECT TOP 10 * FROM mocks WHERE name = @name", configuration);

You can pass the SqlParameterConfiguration if you make a call to a method that has WithParameters at the end. You can set various configurations in SqlParameterConfiguration, and those are then sent to the function to get results.

You can optionally set the SqlCommandType to CommandType.Text or CommandType.StoredProcedure. This defaults to CommandType.Text if not supplied.

Using SqlClient Sql Parameters

string name = "NonQuery Testing Update";
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@name", name));
parameters.Add(new SqlParameter("@id", id));
DatabaseRequest.NonQuery(connectionString, "UPDATE mocks SET name = @name WHERE mock_id = @id", CommandType.Text, parameters);

In this example, we are using a NonQuery request (which does not return a response) to update the mocks table. We must specific the SqlClient CommandType, and then we are passing the List<SqlParameter> to the method.

This is considered deprecated, and you should use the SqlParameterConfiguration syntax instead.

When using Static ConnectionString property, this syntax is not available. If you would like to list out the parameters, use the SqlParameterConfiguration syntax with the connection string specified instead.

Automatic Table Migrations

You can use the new experimental feature PerformAutomaticMigrations which will attempt to automatically create tables if they are missing. This only works when calling from an object whose class inherits DatabaseObject.

This does not work with columns because columns are always considered optional and are only loaded if found in the database. If not found, missing columns are skipped. However, if the table does not exist, it will attempt to load all instances of columns where the [Column] has been set.

You are expected to create all tables and columns prior to usage of this ORM because calls to PerformAutomaticMigrations would slow down database calls. However, the migration can help during the initial release of database structures.

Version 2.1.1 Release Notes

1. Added PerformAutomaticMigrations which will attempt to create tables with their columns if they are missing from the database. If the table already exists, though, columns will not be created when calling this method. The Column attribute must be applied in order for the columns to be created when the table does not exist.
2. Performance improvements in areas that use reflection. However, this is offset by the automapping.
3. If AreColumnsAutoMapped is set to true, then properties that do not have the Column attribute will still be mapped if the lowercase database column matches the lowercase property name.
4. The preferred method for making requests is to create a SqlParameterConfiguration and then pass that to the function to get results.
5. Methods that use the static ConnectionString have been standardized to end with Query if the command type is Text and end with StoredProcedure is the command type is a Stored Procedure. Please consider this if using the ConnectionStringManager to set the connection string.

Version 2.1.1 Breaking Changes

Version 2.1.1 should be backwards compatible with version 2.0.0 and later; however, the SqlClient Sql Parameters are considered deprecated. Use SqlParameterConfiguration with methods that have WithParameters in their name instead if you want to use this style of supplying parameters.

If using the new DatabaseRequestWithDefaults, the method name may be different so it is not as simple as simply setting the ConnectionStringManager.ConnectionString to make it work. With the new DatabaserequestWithDefaults, the opportunity was taken to rename methods.

Removed access to the DatabaseRequest.RunSQLCommand function. This is only intended to be used internally as a private function.