Download DataAccess.zip
Introduction
This tutorial aims to show a basic approach for designing a
Basic Idea
I've allways loved working with reflection and attributes. So the idea came from them. Using attributes to define data objects, data fields in c# code, and mapping them with reflection to database tables, columns etc. After all, building queries any way, and executing with specified database type, thats all. You can find ADO.Net libraries of well-known databases easliy on their official sites. Here is a list of used in this tutorial:
MySql Connector for .Net
Oracle Data Provider for .Net
Why to Seperate Data Works from Database and Entity?
There could be several good reasons to have an independent data access layer, here some:
1. You write your data access layer once and use it in all other projects.
2. You may want to modify your entities/tables in future. Then if your data access layer is harcoded, you will have to modify your methods, classes etc.
3. After a very big discussion (it should be), you may want to change your database! If there isn't any swicth to turn it on, then you are in a big chaos.
etc.
How to?
Below you can see a simple scheme of the struct:
1. Isolate data works from database type
We will create a data access provider class has a specified database type and a connection string that could never been changed ones they are asigned in the constructor. Then we will design methods to create connections, execute queries, reading tables by using interfaces defined in ADO.Net. These interfaces are listed below:
IDbConnection IDbCommandIDataParameterIDataReader
The key to remove dependency from database types is using these interfaces. Because whole data providers in ADO.Net implements these interfaces to create connections, execute queries, raeding rows and all other things like MSSQL, MySql, Oracle, Sql Compact.
2. Isolate data works from entites
To address this issue we need to use reflection methods to get Type of the entitiy that will be mapped and find its properties, property types to map exactly as a database table. Also, we will get help from attributes to define database-specific properties like column name, table name, nullable or not, identifer or not etc. There will be two custom attributes listed below to handle these jobs, one for defining tables, other for defining columns:
DatabaseTableAttributeDatabaseColumnAttribute
Also we will use generic methods to seperate entites completly. This is a better way than passing type of entity to methods and boxing the return value back to entity type. For example, our select method's signature will be like this:
public List<T> SelectAll<T>() where T : new()
1. Mapping Types as Database Tables
1.1. Creating Attributes
In this part, we will create attributes to define database objects like tables and columns with acceptable properties (unique, nullable, identifier etc.). So, there will be two different attributes; one for table definitions and one for column definitions.
Here is the code for DatabaseTableAttribute to define tables. This will be used to help us when mapping/parsing classes with reflection. It has only a property to specify table name.
[AttributeUsage(AttributeTargets.Class, Inherited = false, AllowMultiple = false)]
public sealed class DatabaseTableAttribute : Attribute
{
public string TableName { get; set; }
} And below the code for DatabaseColumnAttribute to define columns. It has four properties that you can gues why they stand for easly. Also you can add more properties to get more realistic results. This attribute's usage will be similar as DatabaseTableAttribute. Both stand for helping to map types-tables and specifying database-specific fields.
[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]
public sealed class DatabaseColumnAttribute : Attribute
{
public string ColumnName { get; set; }
public bool IsIdentifier { get; set; }
public bool IsNullable { get; set; }
public bool IsAutoValue { get; set; }
} 1.2. Mapping Types
There are two different cases on the type/property that will be mapped:
1. The type/property uses attributes defined above.
2. Doesn't use.
We will design the mapper as accepts these two cases and behaves as it should. It means that, the mapper method will have default behaviors to map unspecified types/columns. For example; even if a type doesn't use DatabaseTableAttribute we will map its type name as table name. So, if your table name is same with your entity name, you won't have to use the attribute to define table name. But using attriubes will help to extract table scheme.
Here is the code for ColumnDefinition class to map columns. You can get what the properties are declared for easly on names. I want to focus on AssociatedPropertyName. This property holds the name of the property in the mapped classes for this column definition. It helps us on getting values when rows retrived from a data reader and mapping it back to it's entity.
public class ColumnDefinition
{
public string ColumnName { get; set; }
public string AssociatedPropertyName { get; set; }
public DbType DbType { get; set; }
public bool IsAutoValue { get; set; }
public bool IsIdentifier { get; set; }
public bool IsNullable { get; set; }
public ColumnDefinition()
{
IsAutoValue = false;
IsIdentifier = false;
IsNullable = true;
}
public ColumnDefinition(string columnName, string associatedPropertyName, DbType dbType, bool isAutoValue, bool isIdentifier, bool isNullable)
{
ColumnName = columnName;
AssociatedPropertyName = associatedPropertyName;
DbType = dbType;
IsAutoValue = isAutoValue;
IsIdentifier = isIdentifier;
IsNullable = isNullable;
}
} Here is the code for TableScheme class to map tables.
public class TableScheme
{
public string TableName { get; set; }
public List<ColumnDefinition> Columns { get; set; }
} To get TableScheme of a type we will need a method like shown below. GetTableSchemeFromType method takes a paremeter named type, and checks if the type uses our custom attributes. If uses, then parse it by user specified options defined in attributes. But if not, then parses it with default behaviors. Our default behaviors are:
- Use class name as table name if
DatabaseTable attribute isn't used or TableName isn't specified. - Use property name as column name if
DatabaseColumn attribute isn't used or ColumnName isn't specified. - If
DatabaseTable attribute isn't used, then never check for DatabaseColumn attribute and behave as default on properties.
private TableScheme GetTableSchemeFromType(Type type)
{
TableScheme dbTable = new TableScheme();
string tableName;
List<ColumnDefinition> columns = new List<ColumnDefinition>();
var attributes = type.GetCustomAttributes(typeof(DatabaseTableAttribute), false);
if (attributes.Length > 0)
{
var attr = attributes[0] as DatabaseTableAttribute;
tableName = string.IsNullOrEmpty(attr.TableName) ? type.Name : attr.TableName;
foreach (var prop in type.GetProperties())
{
var propAttributes = prop.GetCustomAttributes(typeof(DatabaseColumnAttribute), false);
if (propAttributes.Length > 0)
{
var propAttr = propAttributes[0] as DatabaseColumnAttribute;
string columnName = string.IsNullOrEmpty(propAttr.ColumnName) ? prop.Name : propAttr.ColumnName;
DbType dbType = Helper.DbTypeConverter.ConvertFromSystemType(prop.PropertyType);
bool isAutoValue = propAttr.IsAutoValue;
bool isIdenifier = propAttr.IsIdentifier;
bool isNullable = propAttr.IsNullable;
columns.Add(new ColumnDefinition(columnName, prop.Name, dbType, isAutoValue, isIdenifier, isNullable));
}
else
{
bool isIdentifier = prop.Name.ToUpper() == "ID" || prop.Name.ToUpper() == tableName.ToUpper() + "ID";
columns.Add(new ColumnDefinition(prop.Name, prop.Name, Helper.DbTypeConverter.ConvertFromSystemType(prop.PropertyType),
isIdentifier, isIdentifier, true));
}
}
}
else
{
tableName = type.Name;
foreach (var prop in type.GetProperties())
{
bool isIdentifier = prop.Name.ToUpper() == "ID" || prop.Name.ToUpper() == tableName.ToUpper() + "ID";
columns.Add(new ColumnDefinition(prop.Name, prop.Name, Helper.DbTypeConverter.ConvertFromSystemType(prop.PropertyType),
isIdentifier, isIdentifier, true));
}
}
dbTable.TableName = tableName;
dbTable.Columns = columns;
return dbTable;
} We will need to convert system types to DbType to use with parameters on commands. The code for helper class named DbTypeConverter to convert system type to DbType is shown below (from here):
public class DbTypeConverter
{
private static Dictionary<Type,DbType> typeMap = new Dictionary<Type, DbType>();
static DbTypeConverter()
{
InitTypes();
}
private static void InitTypes()
{
typeMap[typeof(byte)] = DbType.Byte;
typeMap[typeof(sbyte)] = DbType.SByte;
typeMap[typeof(short)] = DbType.Int16;
typeMap[typeof(ushort)] = DbType.UInt16;
typeMap[typeof(int)] = DbType.Int32;
typeMap[typeof(uint)] = DbType.UInt32;
typeMap[typeof(long)] = DbType.Int64;
typeMap[typeof(ulong)] = DbType.UInt64;
typeMap[typeof(float)] = DbType.Single;
typeMap[typeof(double)] = DbType.Double;
typeMap[typeof(decimal)] = DbType.Decimal;
typeMap[typeof(bool)] = DbType.Boolean;
typeMap[typeof(string)] = DbType.String;
typeMap[typeof(char)] = DbType.StringFixedLength;
typeMap[typeof(Guid)] = DbType.Guid;
typeMap[typeof(DateTime)] = DbType.DateTime;
typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;
typeMap[typeof(byte[])] = DbType.Binary;
typeMap[typeof(byte?)] = DbType.Byte;
typeMap[typeof(sbyte?)] = DbType.SByte;
typeMap[typeof(short?)] = DbType.Int16;
typeMap[typeof(ushort?)] = DbType.UInt16;
typeMap[typeof(int?)] = DbType.Int32;
typeMap[typeof(uint?)] = DbType.UInt32;
typeMap[typeof(long?)] = DbType.Int64;
typeMap[typeof(ulong?)] = DbType.UInt64;
typeMap[typeof(float?)] = DbType.Single;
typeMap[typeof(double?)] = DbType.Double;
typeMap[typeof(decimal?)] = DbType.Decimal;
typeMap[typeof(bool?)] = DbType.Boolean;
typeMap[typeof(char?)] = DbType.StringFixedLength;
typeMap[typeof(Guid?)] = DbType.Guid;
typeMap[typeof(DateTime?)] = DbType.DateTime;
typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;
}
public static DbType ConvertFromSystemType(Type systemType)
{
if (typeMap.ContainsKey(systemType))
return typeMap[systemType];
else
throw new InvalidCastException("The system type is not convertable.");
}
} 2. Isolated Data Accessing
Now, we will design a data access layer to take over whole database work. And it will be independent from database type like mssql, mysql, oracle, etc. So, we will use interfaces named IDbConnection, IDbCommand, IDataParameter, IDataReader to separate data operations from database type.
The constructor of DataAccessProvider class seems like this:
public DatabaseType Database { get; private set; }
public string ConnectionString { get; private set; }
public DataAccessProvider(string connectionString, DatabaseType databaseType)
{
Database = databaseType;
ConnectionString = connectionString;
using (var conn = CreateConnection())
{
conn.Open();
}
} The constructor takes connection string and databese type as parameters and tests the connection by a simple (very simple) way. The ConnectionString and Database properties are readonly. They will be used by default choices for much methos we will create later. Btw, DatabaseType is an enum that shown below:
public enum DatabaseType
{
MSSql,
MySql,
Oracle,
SqlCompact
} Here is our roadmap; create connections first with specified connection string and database type. Then create commands on these connection. When creating commands also create parameters to use. And use only the interfaces!
Now, let's write methods to create database connections. There are three overloaded methods name CreateConnection. First one uses connection string and database type passed the constructor of DataAccessProvider class. Second one gives an option to specify a temproray connection string to create a connection with database type passed in constructor. Third one takes the two required parameters together and creates a connection to move on.
public IDbConnection CreateConnection()
{
return CreateConnection(ConnectionString,Database);
}
public IDbConnection CreateConnection(string connectionString)
{
return CreateConnection(connectionString,Database);
}
public IDbConnection CreateConnection(string connectionString, DatabaseType database)
{
IDbConnection connection;
switch (database)
{
case DatabaseType.MSSql:
connection = new SqlConnection();
break;
case DatabaseType.MySql:
connection = new MySqlConnection();
break;
case DatabaseType.Oracle:
connection = new OracleConnection();
break;
case DatabaseType.SqlCompact:
connection = new SqlCeConnection();
break;
default:
connection = new SqlConnection();
break;
}
connection.ConnectionString = connectionString;
return connection;
} We will use same way to create commands. But there should be a method to get database type of the connection that the command will be created for. To do this, we have a method named GetDatabaseType that takes the connection as IDbConnection and finds its database type. You can see three overloaded CreateCommand methods below. First one passes an empty string and a null parameter to third method. Then the third method creates a command using default database type passed in constructor and sets command string as empty. Second one gives an option to specify a temproray connection to create a command with database type passed in constructor. Third one takes the two required parameters together and creates a command to move on.
public IDbCommand CreateCommand()
{
return CreateCommand(string.Empty, null);
}
public IDbCommand CreateCommand(IDbConnection connection)
{
return CreateCommand(string.Empty, connection);
}
public IDbCommand CreateCommand(string commandText, IDbConnection connection)
{
IDbCommand command;
DatabaseType database = connection == null ? Database : GetDatabaseType(connection);
switch (database)
{
case DatabaseType.MSSql:
command = new SqlCommand(commandText, (SqlConnection)connection);
break;
case DatabaseType.MySql:
command = new MySqlCommand(commandText, (MySqlConnection)connection);
break;
case DatabaseType.Oracle:
command = new OracleCommand(commandText, (OracleConnection)connection);
break;
case DatabaseType.SqlCompact:
command = new SqlCeCommand(commandText, (SqlCeConnection)connection);
break;
default:
command = new SqlCommand(commandText, (SqlConnection)connection);
break;
}
return command;
} Below, you can see the code of GetDatabaseType method as mentioned above:
private DatabaseType GetDatabaseType(IDbConnection connection)
{
DatabaseType database = DatabaseType.Unknown;
if (connection is SqlConnection)
{
database = DatabaseType.MSSql;
}
else if (connection is MySqlConnection)
{
database = DatabaseType.MySql;
}
else if (connection is OracleConnection)
{
database = DatabaseType.Oracle;
}
else if (connection is SqlCeConnection)
{
database = DatabaseType.SqlCompact;
}
return database;
} Next step: Create parameters. We have three methods overloaded here again. First one calls the third method and creates a parameter that's DbType is DbType.String, for default database type passed on constructor. Second one gives an option to specify parameter name, database value type, and value. Third one takes four paramters and creates a paramter for specified database.
public IDataParameter CreateParameter()
{
return CreateParameter("", DbType.String, null, Database);
}
public IDataParameter CreateParameter(string parameterName, DbType dbType, object value)
{
return CreateParameter(parameterName, dbType, value, Database);
}
public IDataParameter CreateParameter(string parameterName, DbType dbType, object value, DatabaseType database)
{
IDataParameter parameter = null;
switch (database)
{
case DatabaseType.MSSql:
parameter = new SqlParameter() { ParameterName = parameterName, DbType = dbType, Value = value };
break;
case DatabaseType.MySql:
parameter = new MySqlParameter() { ParameterName = parameterName, DbType = dbType, Value = value };
break;
case DatabaseType.Oracle:
parameter = new OracleParameter() { ParameterName = parameterName, DbType = dbType, Value = value };
break;
case DatabaseType.SqlCompact:
parameter = new SqlCeParameter() { ParameterName = parameterName, DbType = dbType, Value = value};
break;
case DatabaseType.Unknown:
break;
default:
parameter = new SqlParameter() { ParameterName = parameterName, DbType = dbType, Value = value };
break;
}
return parameter;
} At the current point, we can create connections, commands, and parameters without any dependency. So, let's use these methods. Now we will create ExecuteReader methods to execute queries and retrieve results as IDataReader. First one calls the second method with specified query string and an empty paramter list. Second one gives an option to specify parameters that could be created with our CreateParameters method described above. this second method creates a connection using CreateConnection method and creates a command on it with CreateCommand method. You can see that there isn't any variable or parameter like SqlDataReader or MySqlParameter. All works around interfaces!
public IDataReader ExecuteReader(string query)
{
return ExecuteReader(query,new List<IDataParameter>());
}
public IDataReader ExecuteReader(string query, List<IDataParameter> parameters)
{
IDbConnection connection = CreateConnection();
connection.Open();
IDbCommand command = CreateCommand(query, connection);
foreach (var param in parameters)
{
command.Parameters.Add(param);
}
return command.ExecuteReader(CommandBehavior.CloseConnection);
} Like on the ExecuteReader methods, we will create our ExecuteNonQuery methods for queries like insert, update and delete. There are two methods here. First one calls the second method with specified query string and an empty paramter list. Second one gives an option to specify parameters that could be created with our CreateParameters or somehow. Then creates a connection with our CreateConnection method and creates a command on this connection, adds the parameters to the command and executes it. You can see that these methods are independent from derived types as well.
public int ExecuteNonQuery(string query)
{
return ExecuteNonQuery(query,new List<IDataParameter>());
}
public int ExecuteNonQuery(string query, List<IDataParameter> parameters)
{
using (IDbConnection connection = CreateConnection())
{
connection.Open();
IDbCommand command = CreateCommand(query, connection);
foreach (var param in parameters)
{
command.Parameters.Add(param);
}
return command.ExecuteNonQuery();
}
} In this case, we can create connections and execute queries on that connections completly independent from database type. The next step is parsing data readers to entites, or may be to dynamic objects. Below is the method for parsing to entites named ParseDataReaderToEntityList. It takes two parameters; firts is the data reader could be retrived from our ExecuteReader method, second is a TableScheme mapped from an entity by using GetTableSchemeFromType method that takes a type and maps it as a table, mentioned above. Then uses AssociatedPropertyName and ColumnName properties defined in TableScheme to get values from data reader and set on the created instance of type T. After that adds the instance to the list and returns the list in the end.
private List<T> ParseDataReaderToEntityList<T>(IDataReader reader, TableScheme dbTable) where T : new()
{
Type type = typeof(T);
List<T> result = new List<T>();
while (reader.Read())
{
T t = new T();
foreach (var column in dbTable.Columns)
{
type.GetProperty(column.AssociatedPropertyName).SetValue(t, reader[column.ColumnName], null);
}
result.Add(t);
}
return result;
} Now let's use all of above to select all records from a table and map it to entity that matches with correct table. To do that we will create a generic method named SelectAll<T> as below. First one passes an empty string as where clause to second method. Then the second method handles the work. Second one takes a string parameter named whereClause to filter records on the query. This method builds the select query in a very simple way and executes it with our ExecuteReader method. Then parses the reader as T type using ParseDataReaderToEntityList<T> method and returns as result. Third one gives an option to specify completly custom select query and query parameters. Then makes what second one makes.
public List<T> SelectAll<T>() where T : new()
{
return SelectAll<T>(string.Empty);
}
public List<T> SelectAll<T>(string whereClause) where T : new()
{
List<T> result = new List<T>();
Type type = typeof(T);
TableScheme dbTable = GetTableSchemeFromType(type);
var query = "Select * From " + dbTable.TableName + " " + whereClause;
IDataReader reader = ExecuteReader(query);
result = ParseDataReaderToEntityList<T>(reader,dbTable);
return result;
}
public List<T> SelectAll<T>(string selectQuery, List<IDataParameter> parameters) where T: new()
{
List<T> result = new List<T>();
TableScheme dbTable = GetTableSchemeFromType(typeof(T));
IDataReader reader = ExecuteReader(selectQuery, parameters);
result = ParseDataReaderToEntityList<T>(reader, dbTable);
reader.Close();
return result;
} We may want to map data records to dynamic objects instead of entity classes. On the method below, we use our ExecuteReader method to retrive an IDataReader object with query string passed as method parameter. Then we loop all records in the reader and add each column of a record to an ExpandoObject with its value by following code. (An ExpandoObject represents an object whose members can be dynamically added and removed at run time.) See the code for SelectAll method here:
public List<dynamic> SelectAll(string tableName)
{
List<dynamic> result = new List<dynamic>();
using (IDataReader reader = ExecuteReader("Select * From "+ tableName))
{
while (reader.Read())
{
dynamic expando = new ExpandoObject();
for (int i = 0; i < reader.FieldCount; i++)
{
string columnName = reader.GetName(i);
((IDictionary<String, Object>)expando).Add(columnName, reader[columnName]);
}
result.Add(expando);
}
}
return result;
} I think you got it. Now you can create methods like Select, Update, Delete easily, by just efforting on building queries. Or never mind, just use our ExecuteNonQuery method with a query string.
Below, you can see a sample generic method to delete a record. This method takes a parameter named databaseObject type of T. Firstly gets TableScheme of the type, then checks if has an identifier column by a lamda expression. If has, then builds a query using this identifier in where clause; else, uses all columns in where clause to make an exact match to delete right one.
public int Delete<T>(T databaseObject) where T : new()
{
int result = new int();
Type type = typeof(T);
TableScheme dbTable = GetTableSchemeFromType(type);
List<IDataParameter> parameters = new List<IDataParameter>();
bool canExecute = false;
bool hasIdentifier = dbTable.Columns.Where((x) => { return x.IsIdentifier; }).Count() > 0;
StringBuilder queryBuilder = new StringBuilder("Delete From " + dbTable.TableName + " Where ");
if (hasIdentifier)
{
ColumnDefinition column = dbTable.Columns.Where((x) => { return x.IsIdentifier; }).First();
queryBuilder.Append(column.ColumnName + "= @" + column.ColumnName);
parameters.Add(CreateParameter("@" + column.ColumnName,
column.DbType,
type.GetProperty(column.AssociatedPropertyName).GetValue(databaseObject, null)));
canExecute = true;
}
else
{
foreach (var column in dbTable.Columns)
{
queryBuilder.Append(column.ColumnName + "= @" + column.ColumnName + " And ");
parameters.Add(CreateParameter("@" + column.ColumnName,
column.DbType,
type.GetProperty(column.AssociatedPropertyName).GetValue(databaseObject, null)));
if (!canExecute)
canExecute = true;
}
queryBuilder.Append("_END_");
queryBuilder = queryBuilder.Replace(" And _END_","");
}
if (canExecute)
{
result = ExecuteNonQuery(queryBuilder.ToString(), parameters);
}
return result;
} Lets make an iprovment. In the Select<T> method we passed where clause as string. But we can use a more language integrated way: Delegates. Here .Net framework has allready a delegate named Func<T,U> can be used. It's better to desribe it on code for me. Below you will see Update<T> methods overloaded. First one gets an object, builds a query and executes it. But the second one uses SelectAll<T> method to get all records and uses delegates to update specified records. Then you are able to use lamda expressions as method parameters. At this point its clear that calling first Update<T> method in a loop from second one for all matched record is not the best way if you care about (and must) performance. But of course it can be changed as you wish. A cache mechanism may solves the performance issue.
public int Update<T>(T databaseObject) where T : new()
{
int result = new int();
Type type = typeof(T);
TableScheme dbTable = GetTableSchemeFromType(type);
List<IDataParameter> parameters = new List<IDataParameter>();
StringBuilder queryBuilder = new StringBuilder("Update " + dbTable.TableName + " Set ");
StringBuilder whereClauseBuilder = new StringBuilder(" where ");
bool appendWhereClause = false;
foreach (var column in dbTable.Columns)
{
if (column.IsIdentifier)
{
whereClauseBuilder.Append(column.ColumnName);
whereClauseBuilder.Append(" = @");
whereClauseBuilder.Append(column.ColumnName);
whereClauseBuilder.Append(" and ");
parameters.Add(CreateParameter("@"+column.ColumnName,
column.DbType,
type.GetProperty(column.AssociatedPropertyName).GetValue(databaseObject,null)));
appendWhereClause = true;
}
else if (!column.IsAutoValue)
{
queryBuilder.Append(column.ColumnName);
queryBuilder.Append(" = @");
queryBuilder.Append(column.ColumnName);
queryBuilder.Append(",");
parameters.Add(CreateParameter("@" + column.ColumnName,
column.DbType,
type.GetProperty(column.AssociatedPropertyName).GetValue(databaseObject, null)));
}
}
queryBuilder.Append("|end");
queryBuilder.Replace(",|end", "");
whereClauseBuilder.Append("|end");
whereClauseBuilder.Replace(" and |end", "");
queryBuilder.Append(appendWhereClause ? whereClauseBuilder.ToString() : "");
result = ExecuteNonQuery(queryBuilder.ToString(), parameters);
return result;
}
public int Update<T>(Func<T, bool> selectionPredicate, Action<T> updatePredicate) where T:new()
{
int result = new int();
foreach (var item in Select(selectionPredicate))
{
updatePredicate(item);
result += Update<T>(item);
}
return result;
} Usage
The data access provider we create takes over data works and seperate from database type and entity. The usage is as below:
DataAccessProvider dap = new DataAccessProvider(Settings.Default.ConnStr, DatabaseType.MySql);
dap.Update<Test>(x => x.Name == "Halil", x => x.Name = "İbrahim");
Also I've attached the sample project to the article. It contains more methods like Select, Update, Delete in. Of course it hasn't complete and best solutions in, but i hope it will be usefull to understand how to. Thanks for reading.
Points of Interest
ADO.Net is usefull to seperate database operations from database types. I would recommend to use the interfaces instead of derived classes. Thus, code will be more flexible and be able to changed easily.
