The database object mapper is an ORM for quickly mapping classes to database structures in SQL Server.
$ dotnet add package DatabaseObjectMapperThe DatabaseObjectMapper allows you to quickly map classes to database tables and columns.
At this time, this is intended only for use with Microsoft Sql Server and MySQL.
To begin using the DatabaseObjectMapper as an ORM after the NuGet Packages are installed, you must include the appropriate using statements.
using DatabaseObjectMapper;
// You can change this to a different database server by changing the using statement.
using DatabaseObjectMapper.SqlServer;
At this time, only integer values are permitted for the primary keys. This means you cannot use BIGINT data types for the primary key. If you do so, then the primary key in the C# code will be truncated to an int, and this can result in data loss. This limits the primary key using the DatabaseObjectMapper to plus or minus ~2.1 billion database rows.
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.
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;
}
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;Encrypt=True;";
}
}
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.
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.
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>();
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.
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);
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 object to keep your code more readable.
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.
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.
The List.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.
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.
There are four ways to send your parameters.
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.
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".
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.
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 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.
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.
This is only available with Sql Server and not MySQL at this time.
This should normally be set to false and exists primarily for backwards compatibility with older DatabaseObjectMapper versions.
If the name of the property in the class matches the column name and this is set to true, then the system will try to automatically map those properties to the database values without using the Column attribute. The names and data types must match exactly.
This feature is only supported for Sql Server at this time.
DBOList<Vendor> vendors = new DBOList<Vendor>();
vendors.ConnectionString = GlobalConnectionString;
vendors.Select("SELECT * FROM vendors WHERE name = @name", false, "@name", "Bob");
foreach (Vendor v in vendors)
{
// Do something with the vendors that are found that match the name specified.
}
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.