Introduction
In this article I am going to show you a simple DEMO application in C# for using MyBatis.NET as a datamapper for accessing SQL SERVER. Before diving into coding , let us see what MyBatis.NET is and why we should use it.
What is MyBatis.NET
MyBatis is a data mapping tool. It maps columns of a database query including stored procedure to properties of a business object. One definition of mapper is “an object that sets up communication between two independent objects. A Data Mapper is a "layer of mappers that moves data between objects and a database while keeping them independent of each other and the mapper itself.
MyBatis is a good choice when your application is layered and business layer can be separated from UI layer. You provide the database and the objects; MyBatis provides the mapping layer that goes between the two.
Why MyBatis.NET
.NET platform already provides a capable library for accessing databases, whether through SQL statements or stored procedures but several things are still hard to do well when using ADO.NET, including:
- Separating SQL code from programming code
- Passing input parameters to the library classes and extracting the output
- Separating data access classes from business logic classes
- Caching often-used data until it changes
- Managing transactions and threading
iBATIS DataMapper solves these problems -- and many more -- by using XML documents to create a mapping between a plain-old object and a SQL statement or a stored procedure. The "plain-old object" can be a IDictionary or property object.
Background
You might have seen everywhere the names ‘MyBatis’ and ‘iBatis’ are interchangeably used because MyBatis was developed with Apache Foundation under the name ‘iBatis’ until 19 May 2010 and then ‘iBatis’ project moved to Google Code with a new project name ‘MyBatis’. Originally MyBatis was developer for Java and recently the .NET version is called MyBatis.NET
Using the code
This is a simple DEMO application and it does not cover everything that MyBatis.NET can offer. It is a good starting point for someone who wants to use MyBatis.NET in their application as data mapper between a database and business objects. I am using SQL SERVER in this demo but you can use any other popular database that MyBatis supports.
The following diagram shows MyBatis.NET datamapper workflow.
First thing you need for the datamapper work is data map definition file (sqlMap.config). I have create the data map definition file as follows
<span style="color: rgb(17, 17, 17); font-family: 'Segoe UI', Arial, sans-serif; font-size: 14px;"><?xml version="1.0" encoding="utf-8" ?></span>
<sqlMapConfig xmlns="http://ibatis.apache.org/dataMapper"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<settings>
<setting useStatementNamespaces="false" />
<setting cacheModelsEnabled="true" />
<setting validateSqlMap="true" />
</settings>
<database>
<provider name="sqlServer2.0" />
<dataSource name="MtBatisSQL" connectionString="Data Source=MachineName\SQL2008R2;Initial Catalog=MyBatisNet;Integrated Security=True"/>
</database>
<sqlMaps>
<sqlMap embedded="sqlFile.xml, MyBatisDataMapper" />
</sqlMaps>
</sqlMapConfig>
In the first section it sets parameters and then sets the provider and datasource , finally the sql mapping. In this case all the mappings are stored in a seperate file sqlFile.xml which is given below.
<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="MyBatisApp" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<statements>
<statement id="FindDepartment" parameterClass="System.Int32" resultClass="System.String" >
SELECT Name
FROM Department
WHERE Id = #value#
</statement>
<procedure id="GetEmployees" parameterMap="get-employees-params" resultMap="get-employee-result">
GetEmployees
</procedure>
</statements>
<parameterMaps>
<parameterMap id="get-employees-params">
<parameter property="Id" column="DepartmentId" />
</parameterMap>
</parameterMaps>
<resultMaps>
<resultMap id="get-employee-result" class="model.Employee">
<result property="Id" column="Id" dbType="Int"/>
<result property="Name" column="Name" dbType="Varchar"/>
<result property="Age" column="Age" dbType="Varchar"/>
<result property="DepartmentId" column="DepartmentId" dbType="Int"/>
</resultMap>
</resultMaps>
</sqlMap>
As you can see, I have created one simple FindDepartment statement using SELECT query that takes department id as int and returns department name as a string.
Also there is a stored procedure GetEmployees that uses a parameterMap and resultsMap. A parameter map is used to map parameter property (id in this case) to the column parameter in the stored procedure (DepartmentId in this case) before executing the stored procedure. The results map on the another hand maps the results (columns in the results set) from stored procedure to the object (properties in the Employee object).
Before we start coding we need more more config file. Remember in the sqlMap.config file , in the database element , it requires provider along with datasource. The provider is nothing but the driver information to connect to the specified database. In the above config we have specified
<provider name="sqlServer2.0" />
we can have as many providers as you want and change the provider according to the enviroment delpoyed. The following is the provide we are going to use.
<?xml version="1.0" encoding="utf-8"?>
<providers
xmlns="http://ibatis.apache.org/providers"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<clear/>
<provider
name="sqlServer2.0"
enabled="true"
default="false"
description="Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0"
assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
connectionClass="System.Data.SqlClient.SqlConnection"
commandClass="System.Data.SqlClient.SqlCommand"
parameterClass="System.Data.SqlClient.SqlParameter"
parameterDbTypeClass="System.Data.SqlDbType"
parameterDbTypeProperty="SqlDbType"
dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"
commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"
usePositionalParameters = "false"
useParameterPrefixInSql = "true"
useParameterPrefixInParameter = "true"
parameterPrefix="@"
allowMARS="true"
/>
<provider
name="sqlServer4.0"
enabled="true"
default="true"
description="Microsoft SQL Server, provider V4.0.0.0 in framework .NET V4.0"
assemblyName="System.Data, Version=4.0.0.0, Culture=Neutral, PublicKeyToken=b77a5c561934e089"
connectionClass="System.Data.SqlClient.SqlConnection"
commandClass="System.Data.SqlClient.SqlCommand"
parameterClass="System.Data.SqlClient.SqlParameter"
parameterDbTypeClass="System.Data.SqlDbType"
parameterDbTypeProperty="SqlDbType"
dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"
commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"
usePositionalParameters = "false"
useParameterPrefixInSql = "true"
useParameterPrefixInParameter = "true"
parameterPrefix="@"
allowMARS="true"
/>
</providers>
Now let us start coding in C#. To access any mapped sql statements or stored procedures , we need SqlMapper instance from the configuration we have provided. The following code shows how to get SqlMapper instance
public static ISqlMapper EntityMapper
{
get
{
try
{
ISqlMapper mapper = Mapper.Instance();
return mapper;
}
catch (Exception ex)
{
throw ex;
}
}
}
The ISqlMapper is available from the referenced library IBatisNet.DataMapper.dll. Now the instance is available so we can execure the statement and storeprocedure as below
public static List<model.Employee> getEmployees(model.Department department)
{
ISqlMapper mapper = EntityMapper;
List<model.Employee> employees = mapper.QueryForList<model.Employee>("GetEmployees", department).ToList();
return employees;
}
public static string FindDepartment(int deptId){
ISqlMapper mapper = EntityMapper;
string str = mapper.QueryForObject<string>("FindDepartment", deptId);
return str;
}
string deptName = FindDepartment(1);
Console.WriteLine(deptName);
Console.Read();
model.Department dep = new model.Department();
dep.Id = 1;
List<model.Employee> emps = getEmployees(dep);
Console.ReadLine();
Either it is a statment or SP , it does not differentiate in terms of executing the code. The only difference is if the resulting type is a list then we have to use QueryForList rather than QueryForObject. This is just to show how MyBatis.NET can be used as a data mapper between database and business objects. MyBatis.NET can offer a lot more than what we have seen in this demo. It supports transactions including distributed transactions.
It can be clearly seen that the data access code is very clean and neat , and there is no SQL statements or store procedures sprinkled across the code to access database data. It is purely using objects and its methods.
Further Reading
https://mybatis.github.io/mybatis-3/
