As detailed on the Dapper Tutorial Site, Dapper is an object relational mapper (ORM), which means it handles the mapping between the code base and the data store. The setup is simple and explained effectively on the tutorial site. Hence, this post shall not describe the setup steps for Dapper, but instead show an example of how it can be used, in the context of a data import.
This example deals with the real world scenario of an application with a table of employees. The employee information that populates this table derives from a different database on the server, that is used by other applications within the hypothetical company. The importer must first get the data from the common database, then make any required calculations before finally passing data into the application database table. In addition, it must handle updates to users that have already been imported previously.
I will be using the Northwind database in this example, specifically the employees table. The Northwind employees table contains many details about each employee including their address and home phone number. Such information is not required for the employee table for the TestApp which we are importing into, hence we will only need to import a subset of the data.
In addition, you will notice that the fields that are common between the two tables are named slightly differently, for example Surname as opposed to LastName. This will need to be taken account of in the importer.
Firstly the connections and tables are established. The connection strings are located in the web.config of the project, but are not shown here.
The UpdateEmployeesTable is the public method in the importer. It starts by getting all of the employees from the Northwind Employee table. It then iterates through each employee. If the employee does not exist in the TestApp Employee table, then the AddNewEmployee method is called. If the employee already exists in TestApp then the UpdateExistingEmployee method is called.
This example deals with the real world scenario of an application with a table of employees. The employee information that populates this table derives from a different database on the server, that is used by other applications within the hypothetical company. The importer must first get the data from the common database, then make any required calculations before finally passing data into the application database table. In addition, it must handle updates to users that have already been imported previously.
I will be using the Northwind database in this example, specifically the employees table. The Northwind employees table contains many details about each employee including their address and home phone number. Such information is not required for the employee table for the TestApp which we are importing into, hence we will only need to import a subset of the data.
The Northwind Employees table vs TestApp Employees table
In addition, you will notice that the fields that are common between the two tables are named slightly differently, for example Surname as opposed to LastName. This will need to be taken account of in the importer.
Firstly the connections and tables are established. The connection strings are located in the web.config of the project, but are not shown here.
The UpdateEmployeesTable is the public method in the importer. It starts by getting all of the employees from the Northwind Employee table. It then iterates through each employee. If the employee does not exist in the TestApp Employee table, then the AddNewEmployee method is called. If the employee already exists in TestApp then the UpdateExistingEmployee method is called.
The EmployeeExistsInTestApp method uses the ExecuteScalar method to return a boolean value from the SQL query. Here, and in the methods to follow, interpolated strings are utilised.
In the AddNewEmployee method the process query is defined. Then the Execute method is called on the TestAppConnection wherein the properties from the Northwind employee are passed into the query.
The UpdateExistingEmployee method is similar, apart from the EmployeeID now forms part of the where clause in the query, as this is the key on which we make an update. The properties are passed in and the query is executed just like for the AddNewEmployee method.
Summary
This post has shown a very quick and easy example of how Dapper can be used to create and employee import. There are many methods that can be used as part of Dapper, however only a small subset are shown here. The advantage of using Dapper for an import like this, as opposed to a pure SQL script, is the ability to debug as it runs, and easily root out any causes of error. In this example, this isn't likely to happen, but in a more complex importer with multiple tables and more calculation methods, this can be invaluable.
Comments
Post a Comment