DLP Connectors Overview

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.

Installation

The official DLP Connectors Package can be installed in your projects just like any other NuGet package.

Installing via command line:

  1. Open the Nuget Package Manager Console: Tools > Nuget Package Manager > Package Manager Console
  2. Install the package with the following command:
PM> Install-Package Dlp.Connectors.dll

Installing using the graphical interface:

  1. Right click the Project you want to include the DLP Framework package.
  2. Choose Manage Nuget Packages... option.
  3. With nuget.org selected as the online source, search for DLP Connectors.
  4. You can now click the Install button to add the package to your selected project.

Using the DLP Connectors

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.

DatabaseConnector

The DatabaseConnector class provides all the necessary methods and utilities for access MSSQL databases with ease and simplicity.

ExecuteNonQuery()

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
  • query: Query to be executed. The parameter is mandatory.
  • parameters: [Optional] Query parameters. May be an entity or a dynamic object with the following the format: new {Param1 = value1, Param2 = value2, ...}.
Return value

Returns an integer with the number of rows affected by your query.

Examples

The 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);
	}
}

ExecuteScalar()

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
  • query: Query to be executed. This parameter is mandatory.
  • parameters: [Optional] Query parameters. May be an entity or a dynamic object with the following the format: new {Param1 = value1, Param2 = value2, ...}.
Return value

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.

Examples

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);
}

ExecuteReader()

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
  • query: Query to be executed. This parameter is mandatory.
  • pageNumber: Number of the page to be retrieved when the result is paginated.
  • pageSize: Number of rows to be returned by page.
  • orderByColumnName: Name of the colum to be used in the SQL ORDER BY clause.
  • sortDirection: The direction of the ordering results.
  • parameters: [Optional] Query parameters. May be an entity or a dynamic object with the following the format: new {Param1 = value1, Param2 = value2, ...}
Return value

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.

Examples

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.

BulkInsert()

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
  • tableName: Query to be executed. This parameter is mandatory.
  • collection: Number of the page to be retrieved when the result is paginated.
  • sqlBulkCopyOptions: [Optional] Number of rows to be returned by page.
Example

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);
	}
}

Advanced samples

In this section we demonstrate some common use scenarios using simple and complex object structures.

Working with JOIN

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 entity

Business 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();
	}
}

Working with inheritance

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();
	}
}

Dealing with ambiguity

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();
	}
}
Warning: Do not use aliases for this kind of situation or the mapper will not be able to correlate the table names with the properties. If you want to know more about aliases take a look at the Explicity property mapping section.

Explicity property mapping

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.

Database transactions

The DatabaseConnector supports SQL transactions in two ways: global transactions and instance transactions. The following sections describes each one.

Global Transactions

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:

  • BeginGlobalTransaction(string connectionString)
  • CommitGlobalTransaction()
  • RollbackGlobalTransaction()

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:

  • Risk of data inconsistency: if the CreateNewUser method succeeds but the CreateNewUserProfile fails, then we are going to have incomplete data stored in database. Usually requiring a manual work for deleting the created registry before we try again.
  • Return value is useless: when no problems occurs, the method will always return true. But if something goes wrong it will not return false. The method is goint to throw an exception. So whoever calls this method should have some logic to deal with true or exception, instead of true or false.

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.

Tip: Always have your sequential inserts and updates inside a database transaction. It is a good practice and prevents a lot of headache with data loss, integrity and orphan data in your database.

Instance Transactions

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.

The documentation for this section is not available yet. Check back later.