A series of data operations

Time:2020-8-14

Preface

In previous articles, we learned how to use ADO.NET Access database, how to modify and add data. How to get data through dataset and DataAdapter, we will try to implement a simple ORM framework or ORM tool class in this article.

Related knowledge points:

  • Reflection (primary)
  • ADO.NET Existing knowledge

1. ORM

So, the question is, what is ORM? ORM is the full name of object relational mapping, which is translated as object relational mapping. It is a technology to save objects to database by describing the mapping relationship between objects and databases.

In C ා, the Entity Framework was once brilliant, overshadowing the glory of other ORM frameworks (even now it is).

Later, it gradually emerged, in addition to some other ORM frameworks, which further enriched the market. Therefore, there are some popular ones as follows:

  • Dapper a lightweight ORM framework
  • Entity Framework / Entity Framework core
  • The. Net version of hibernate on the NHibernate Java platform
  • wait

Well, these are some of the frameworks that I found recently, which are still being updated by the creative team. Of course, there are many other interesting and easy-to-use ORM frameworks. Welcome to add ha.

The main purpose of this article is not to introduce these frameworks, but to understand the underlying core by implementing an ORM like framework ourselves.

2. Design

Let’s first analyze, if we design a tool class for conversion between entity objects and databases, what functions should it have?

  • Mapping relationship between an attribute and a database field
  • Add, delete, modify and query SQL templates
  • Conversion between query results and objects

3. Implementation

First, declare a class. Because it cannot support only one type, all database related methods of this class are generic methods, or this class is a generic class, so it is defined as a generic class

public class OrmUtil<T>
{
}

We agree in advance that the class name is the table name, and the attribute name is the column name of the table, so we can quickly get the following contents:

/// <summary>
///Type instance of T
/// </summary>
private Type dType;
/// <summary>
///Attribute table of T
/// </summary>
private PropertyInfo[] properties;
public OrmUtil()
{
    dType = typeof(T);
    properties = dType.GetProperties();
}

Declare a database connection:

public SqlConnection Connection { get; set; }

Create a private method to check if the connection is available:

/// <summary>
///Check that the connection is available
/// </summary>
/// <returns></returns>
private bool CheckConnection()
{
    return Connection?.State == ConnectionState.Open;
}

The preparation work is completed, and then we start to write the specific business method:

Insert:

public int Insert(T entity)
{
    If (! Checkconnection()) return - 1; // check status
    var insert = $"insert into {dType.Name}({string.Join(",", properties.Select(t => t.Name))})";
    var values = properties.Select(p => p.GetValue(entity));
    var commandText = $"{insert} values('{string.Join("','", values)}')";

    var command = Connection.CreateCommand();
    command.CommandText = commandText;
    var result = command.ExecuteNonQuery();
    return result;
}

First, the SQL is spliced according to the mapping between the attribute name and the column name, and then the SQL command is executed.

Update:

public int Update(T entity,string keyName,object keyValue)
{
    if (!CheckConnection()) return -1;
    var setValues = properties.ToDictionary(p => p.Name, p => $"'{p.GetValue(entity)}'");
    var setSql = string.Join(",", setValues.Select(pair=>$"{pair.Key}='{pair.Value}'"));
    var sql = $"update {dType.Name} set {setSql} where {keyName} = '{keyValue}'";
    var command = Connection.CreateCommand();
    command.CommandText = sql;
    return command.ExecuteNonQuery();
}

Update needs to pay attention to how to correctly splice the assigned SQL.

Delete:

Delete objects that meet the criteria:

public int Delete(T entity)
{
    if (!CheckConnection()) return -1;
    var querySet = properties.Select(p => $"{p.Name} = '{p.GetValue(entity)}'");
    var sql = $"delete from {dType.Name} where {string.Join(" and ", querySet)}";
    var command = Connection.CreateCommand();
    command.CommandText = sql;
    return command.ExecuteNonQuery();
}

Sometimes, according to the actual business, in most cases, the elements corresponding to the primary key or all elements that meet a certain condition are deleted. It’s just a demonstration here. You can try to transform it yourself.

Search:

First create a tool to convert datatable to object

private List<T> Convert(DataTable table)
{
    var list = new List<T>( table.Rows.Count ); // state the capacity in advance
    foreach(DataRow row in table.AsEnumerable())
    {
        T entity = Activator.CreateInstance<T>();
        foreach(var p in properties)
        {
            if (! table.Columns.Contains (p.name)) continue; // ignore if the attribute name is not in the table
            p.SetValue(entity, row[p.Name]);
        }
        list.Add(entity);
    }
    return list;
}

OK, let’s write a query method:

public List<T> SearchAll()
{
    var adapter = new SqlDataAdapter($"select * from {dType.Name}", Connection);
    var set = new DataSet();
    adapter.Fill(set);
    return Convert(set.Tables[0]);
}

Such a simple ORM framework has taken shape in this way. Of course, the bottom layer of ORM is more complex than this one. Because different databases need to be supported, connection can’t simply be a SqlConnection, or the underlying layer doesn’t use datatable as cleverly as we do.

In fact, the conversion from datatable to class object is a little more complicated than what I wrote, because I have to judge whether the property is readable or writable.

4. Summary

Here I made a proposal to attract jade, and led my friends to conceive a simple ORM framework, which also made everyone have a certain impression. Well, that’s all for today. meanwhile ADO.NET Now that we have finished, we are going to start the Entity Framework. Of course, dataset and DataAdapter are not finished. This part of the content may be supplemented in the following fanwai chapter.

Please pay more attention to my blog Mr. Gao’s cabin

A series of data operations