The DLP Connectors is intended to be a container for several communication services like databases, cache services, sockets and more. At the moment only the DatabaseConnector is available for use, specifically with MSSQL databases.
The main funcionality of the DatabaseConnector is the ability to auto map database tables and columns to your entity objects, without any additional instructions or configurations.
If you are looking for the project page on GitHub, please navigate to Dlp.Connectors Project page.
The official DLP Connectors Package can be installed in your projects just like any other NuGet package.
PM> Install-Package Dlp.Connectors.dll
To have the Dlp.Connectors features available, don't forget to include the Dlp.Connectors reference to any file you want to use them.
using Dlp.Connectors;
In the next sessions are presented the Dlp.Connectors methods and some example of usage.
The DatabaseConnector class provides all the necessary methods and utilities for access MSSQL databases with ease and simplicity.
This method executes the specified query in a database and returns the number of affected rows.
Method signature and overloads:public int ExecuteNonQuery(string query, dynamic parameters = null)
Parameters
new {Param1 = value1, Param2 = value2, ...}
.Returns an integer with the number of rows affected by your query.
ExamplesThe following example shows how to use the insert statement to create a new row with the ExecuteNonQuery method.
// Query to be used to insert the new row.
string query = "INSERT INTO Address (Street, Number) VALUES ('Main Street', '119b')";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteNonQuery to insert the new row.
return databaseConnector.ExecuteNonQuery(query);
}
Although the previous example is not a good practice (by having the parameters hard coded), the next example shows how to use SqlParameters to execute the same task of inserting a row using the ExecuteNonQuery method.
internal int SaveAddress(string street, string number) {
// Query to be used to insert the new row.
string query = "INSERT INTO Address (Street, Number) VALUES (@Street, @Number);";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteNonQuery to insert the new row, creating a new dynamic object to be used as the parameter.
return databaseConnector.ExecuteNonQuery(query, new { Street = street, Number = number });
}
}
Another option is to use your own class or entity as the SqlParameters for any query, as shown in the next example.
public sealed class AddressEntity {
public string Street { get; set; }
public string Number { get; set; }
}
...
internal int SaveAddress(AddressEntity addressEntity) {
// Query to be used to insert the new row.
string query = "INSERT INTO Address (Street, Number) VALUES (@Street, @Number);";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteNonQuery to insert the new row, using our entity as the parameter.
return databaseConnector.ExecuteNonQuery(query, addressEntity);
}
}
You should keep in mind that the entity's properties must match the SqlParameters names, but not necessarily the name of the columns in the database. The following example shows the previous example with different property names in entity.
public sealed class AddressEntity {
public string StreetName { get; set; }
public string StreetNumber { get; set; }
}
...
internal int SaveAddress(AddressEntity addressEntity) {
// Query to be used to insert the new row.
// Pay attention to the @StreetName and @StreetNumber that must match the name of the properties of your entity.
string query = "INSERT INTO Address (Street, Number) VALUES (@StreetName, @StreetNumber);";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteNonQuery to insert the new row, using our entity as the parameter.
return databaseConnector.ExecuteNonQuery(query, addressEntity);
}
}
This method executes the specified query and returns the first column of the first row returned by the database server.
Method signature and overloads:public T ExecuteScalar<T>(string query, dynamic parameters = null)
Parameters
new {Param1 = value1, Param2 = value2, ...}
.Returns a new instance of type T with the mapped result returned by the database, or default(T)
, if the the query execution returns no result.
The following example shows how to retrieve the number of rows from a table.
// Query to be executed.
string query = "SELECT COUNT(1) FROM Address;";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteScalar to count the number of rows as an integer.
int rowCount = databaseConnector.ExecuteScalar<int>(query);
}
The next example shows how to get the database server current date and time.
// Query to be executed.
string query = "SELECT GETDATE();";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteScalar to get the database server date and time.
DateTime serverDateTime = databaseConnector.ExecuteScalar<DateTime>(query);
}
This method executes the specified query and returns a list containing the returned rows already mapped to the specified type. Optionally the result can be paginated for better performance.
Method signature and overloads:public IEnumerable<T> ExecuteReader<T>(string query, dynamic parameters = null)
public KeyValuePair<int, IEnumerable<T>> ExecuteReader<T>(string query, int pageNumber, int pageSize, string orderByColumnName, SortDirection sortDirection, dynamic parameters = null)
Parameters
ORDER BY
clause.new {Param1 = value1, Param2 = value2, ...}
Using the non paginated method, the return value is a collection of type T. If no results are found, an empty collection is returned.
When the paginated overload is used the method returns a KeyValuePair
where the key
is the number of available pages and the value
is the list of rows of the requested page.
The following example shows how to load a collection of addresses using the ExecuteReader method.
public sealed class AddressEntity {
public string Name { get; set; }
public string Number { get; set; }
}
...
internal IEnumerable<AddressEntity> LoadAddresses() {
// Query to be used to load the addresses.
string query = "SELECT Name, Number FROM Address;";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteReader to load all the addresses from the database.
return databaseConnector.ExecuteReader<AddressEntity>(query);
}
}
The next example shows the same query from the previous example, but returning a paginated result.
public sealed class AddressEntity {
public string Name { get; set; }
public string Number { get; set; }
}
...
internal KeyValuePair<int, IEnumerable<AddressEntity>> LoadAddresses() {
// Query to be used to load the addresses.
string query = "SELECT Name, Number FROM Address;";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteReader to load the first page of addresses containing 50 rows per page, alphabetically ordered by the "Name" column.
return databaseConnector.ExecuteReader<AddressEntity>(query, 1, 50, "Name", SortDirection.ASC);
}
}
Using parameters with the ExecuteReader method is the same as with ExecuteScalar and ExecuteNonQuery.
public sealed class AddressEntity {
public long AddressId { get; set; }
public string Name { get; set; }
public string Number { get; set; }
}
...
internal AddressEntity LoadAddressById(long addressId) {
// Query to be used to load the address.
string query = "SELECT AddressId, Name, Number FROM Address WHERE AddressId = @AddressId;";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteReader to load the specified address. If nothing is found the FirstOrDefault extension method returns null.
return databaseConnector.ExecuteReader<AddressEntity>(query, new { AddressId = addressId }).FirstOrDefault();
}
}
Important: Keep in mind that the ExecuteReader mapper works based on names by convention. The mapper search primarily a property that matches the name of the column from a database. If a property with the same name of a column name is found the value is mapped to that property. When using complex objects, the mapper searches for any property that matches the name of the table. Having the property found, the mapper starts the search for the property containing the name of the column, to store the returned value.
In the Advanced samples section we provide some examples, including options to map data automatically when your properties does not match the name of columns or tables from database.
This method allows the insertion of multiple rows using batch operations. You simply informs a collection and the name of the table where the data must be inserted, without the need of any query.
Method signature and overloads:public void BulkInsert(string tableName, IEnumerable collection, SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.Default)
Parameters
The following example demonstrates how to insert multiple items in a table using the BulkInsert method. The properties from the class ShoppingCartItem will be mapped to the columns with the same name from the ShoppingCartItems table.
public sealed class ShoppingCartItem {
public string ItemName { get; set; }
public int Quantity { get; set; }
public int AmountInCents { get; set; }
public string ItemDescription { get; set; }
public long ShoppingCartId { get; set; }
}
...
internal void SaveShoppingCartItems(IEnumerable<ShoppingCartItem> shoppingCartItems) {
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the BulkInsert to insert all the shoppingCartItems.
databaseConnector.BulkInsert("ShoppingCartItems", shoppingCartItems);
}
}
In this section we demonstrate some common use scenarios using simple and complex object structures.
The DatabaseConnector can handle joins like any other query statement. And it is pretty simple to have a query result from several tables with joins to be automatically mapped to your business entity or database entity.
Join to populate a business entityBusiness entities are classes focused in your business logic, therefore containing only the specific data required to execute a specific role or purpose. For example, consider the following business entity:
public sealed class Customer {
public string Name { get; set; }
public int Age { get; set; }
public bool IsEnabled { get; set; }
public string SecurityQuestion { get; set; }
}
For our example, the customer information are stored in two different tables. The Name and Age values are stored in Customer table and IsEnabled and SecurityQuestion are persisted in CustomerConfiguration table.
The following query can retrieve the data from both tables and the result is mapped to our business entity.
internal Customer GetCustomerById(long customerId) {
// Query to retrieve the customer information to our business entity.
string query = @"SELECT Customer.Name, Customer.Age, CustomerConfiguration.IsEnabled, CustomerConfiguration.SecurityQuestion
FROM Customer
INNER JOIN CustomerConfiguration ON CustomerConfiguration.CustomerId = Customer.CustomerId
WHERE Customer.CustomerId = @CustomerId;";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteReader to load the specified customer. If nothing is found the FirstOrDefault extension method returns null.
return databaseConnector.ExecuteReader<Customer>(query, new { CustomerId = customerId }).FirstOrDefault();
}
}
Join to populate a database entity
The bellow code represents a customer database entity. Database entities are intended to represent the database structure inside your code. Although it is not recomended for critical operations that requires performance, a good number of developers are used to work with database entities.
public sealed class CustomerConfigurationEntity {
public bool IsEnabled { get; set; }
public string SecurityQuestion { get; set; }
}
public sealed class CustomerEntity {
public string Name { get; set; }
public int Age { get; set; }
public CostumerConfigurationEntity CustomerConfiguration { get; set; }
}
The same piece of code from the previous example also works to map the result to the database entity
internal Customer GetCustomerById(long customerId) {
// Query to retrieve the customer information to our database entity.
string query = @"SELECT Customer.Name, Customer.Age, CustomerConfiguration.IsEnabled, CustomerConfiguration.SecurityQuestion
FROM Customer
INNER JOIN CustomerConfiguration ON CustomerConfiguration.CustomerId = Customer.CustomerId
WHERE Customer.CustomerId = @CustomerId;";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteReader to load the specified customer. If nothing is found the FirstOrDefault extension method returns null.
return databaseConnector.ExecuteReader<Customer>(query, new { CustomerId = customerId }).FirstOrDefault();
}
}
The DatabaseConnector supports auto mapping of properties from a base class in the same way as self properties. Consider the class structure bellow, where the VipCustomer class inherits from Customer class:
public class Customer {
public string Name { get; set; }
public int Age { get; set; }
}
public sealed class VipCustomer : Customer {
public string PersonalManagerName { get; set; }
public int CurrentPlanPoints { get; set; }
}
Classes that have inheritance are automatically mapped by the DatabaseConnector without any additional configuration. The following example demonstrates how to select informations from two separate tables to populate the VipCostumer class.
internal VipCustomer GetVipCustomerById(long vipCustomerId) {
// Query to retrieve the customer information.
string query = @"SELECT Customer.Name, Customer.Age, VipCustomer.PersonalManagerName, VipCustomer.CurrentPlanPoints
FROM VipCustomer
INNER JOIN Customer ON Customer.CustomerId = VipCustomer.CustomerId
WHERE VipCustomer.VipCustomerId = @VipCustomerId;";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteReader to load the specified customer. If nothing is found the FirstOrDefault extension method returns null.
return databaseConnector.ExecuteReader<VipCustomer>(query, new { VipCustomerId = vipCustomerId }).FirstOrDefault();
}
}
When you have more than one user defined type in an entity, may occur that those types can contain properties with the same name. In these situations is recomended that you include the name of the table for each column in your query to explicitily map each column to the correct property.
Consider the following Account class that contains the User and Service properties and both of them have a Name property including its own Name property.
public sealed class UserEntity {
public string Name { get; set; }
public string Email { get; set; }
}
public sealed class ServiceEntity {
public string Name { get; set; }
public string Url { get; set; }
}
public sealed class AccountEntity {
public string Name { get; set; }
public ServiceEntity Service { get; set; }
public UserEntity User { get; set; }
}
The following example demonstrates a query containing the name of the table owner of each column to explicity map each Name property for the AccountEntity class.
internal AccountEntity GetAccountEntityByUserEmail(string email) {
// Query to retrieve the account information.
string query = @"SELECT Account.Name, Service.Name, Service.Url, User.Name, User.Email
FROM User
INNER JOIN Account ON Account.UserId = User.UserId
INNER JOIN Service ON Service.AccountId = Account.ServiceId
WHERE User.Email = @Email;";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteReader to load the specified AccountEntity. If nothing is found the FirstOrDefault extension method returns null.
return databaseConnector.ExecuteReader<AccountEntity>(query, new { Email = email }).FirstOrDefault();
}
}
In the previous section we used a well defined class structure where every property name matched the desired data from the database. However that is not always the case. Sometimes your classes will not have every property exactly the same as the column or table name.
For example, consider the following class structure:
public sealed class UserEntity {
public string UserName { get; set; }
public string UserEmail { get; set; }
}
public sealed class ServiceEntity {
public string ServiceName { get; set; }
public string ServiceUrl { get; set; }
}
public sealed class AccountEntity {
public string AccountName { get; set; }
public ServiceEntity AccountService { get; set; }
public UserEntity AccountOwner { get; set; }
}
The following example demonstrates how to explicity map each returned value from the database to the correct property using aliases.
internal AccountEntity GetAccountEntityByUserEmail(string email) {
// Query to retrieve the account information.
string query = @"SELECT Account.Name AS 'AccountName', Service.Name AS 'AccountService.ServiceName',
Service.Url AS 'AccountService.ServiceUrl', User.Name AS 'AccountOwner.UserName', User.Email AS 'AccountOwner.UserEmail'
FROM User
INNER JOIN Account ON Account.UserId = User.UserId
INNER JOIN Service ON Service.AccountId = Account.ServiceId
WHERE User.Email = @Email;";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteReader to load the specified AccountEntity. If nothing is found the FirstOrDefault extension method returns null.
return databaseConnector.ExecuteReader<AccountEntity>(query, new { Email = email }).FirstOrDefault();
}
}
Pay attention to the alias format that differs for the root object and its sub-properties. The query must include an alias with "SubPropertyName.TargetProperty" format for every sub-property like the AccountService and AccountOwner. Take this piece from the query: Service.Name AS 'AccountService.ServiceName'
. This instruction says to the mapper to take the value of the Name column from the Service table and store in the ServiceName property of the AccountService instance.
Attention to this piece of code: Account.Name AS 'AccountName'
. The AccountName property belongs to the root object, so you must specify only the name of the target property.
The DatabaseConnector supports SQL transactions in two ways: global transactions and instance transactions. The following sections describes each one.
The global transactions are managed internally by the DatabaseConnector and are the most common and simple way to work with SQL transactions. They take advantage of the following static methods:
When global transactions are used, you do not need to modify any existing query or connection that already exists in your code.
Examples
Consider the following situation where we need to register a user and its profile. We do so saving the data to two different tables: User and Profile, as shown bellow.
// Creates a new user entry and returns the id generated by the database.
public long CreateNewUser(string name, string email) {
// Query to insert a new user to database.
string query = @"INSERT INTO Users (Name, Email) OUTPUT INSERTED.UserId VALUES (@Name, @Email);";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteReader to save the new entry and return the generated Id, specified by the OUTPUT INSERTED.UserId clause.
return databaseConnector.ExecuteReader<long>(query, new { Name = name, Email = email });
}
}
// Creates a new user profile entry entry.
public bool CreateNewUserProfile(long userId, string nickname, string avatarUrl, int age) {
// Query to insert a new user profile to database.
string query = @"INSERT INTO UserProfiles (UserId, Nickname, AvatarUrl, Age) VALUES (@UserId, @Nickname, @AvatarUrl, @Age);";
// Creates a new instance of the DatabaseConnector class, passing the database connection string.
using(DatabaseConnector databaseConnector = new DatabaseConnector(connectionString)) {
// Calls the ExecuteNonQuery to save the new entry and returns true if there is at least one affected row.
return databaseConnector.ExecuteNonQuery(query, new { UserId = userId, Nickname = nickname, AvatarUrl = avatarUrl, Age = age }) > 0;
}
}
Actually the previous code could be used by the following method, as follows:
// Creates a new user.
public bool CreateUser(string name, string email, string nickname, string avatarUrl, int age) {
// Create a new user and store the generated id.
long userId = CreateNewUser(name, email);
// Save the profile for the new user.
CreateNewUserProfile(userId, nickname, avatarUrl, age);
return true;
}
Although this code works, it does have some problems. For example, consider the following situations:
To avoid situations like that, your queries and database access should always focus on data integrity. And the DatabaseConnector can handle this kind of situation using global transaction as demonstrated next:
// Creates a new user.
public bool CreateUser(string name, string email, string nickname, string avatarUrl, int age) {
try {
// Creates a new database transaction.
DatabaseConnector.BeginGlobalTransaction(connectionString);
// Create a new user and store the generated id.
long userId = CreateNewUser(name, email);
// Save the profile for the new user.
CreateNewUserProfile(userId, nickname, avatarUrl, age);
// If everything worked as expected, than we can persist the data.
DatabaseConnector.CommitGlobalTransacton();
return true;
} catch(Exception ex) {
// Rollback the database transaction if anything goes wrong.
DatabaseConnector.RollbackGlobalTransaction();
// TODO: Create some log to let you know about the error.
// If anything goes wrong, the method returns false.
return false;
}
}
And it is as simple as that. You do not have to touch anything in your already created queries or connections. Just surrounding all your calls with BeginGlobalTransaction and CommitGlobalTransaction/RollbackGlobalTransaction is enough to have the DatabaseConnector to handle all the database transactions for you.
The instance transactions requires that you manage the SQL transaction manually. This comes in handy for more specific needs and are useful when working with transaction scopes and design patterns such as Unit Of Work.