Anatomy object framework (3)-interaction with databases

A basic function of the Entity Framework is to interact with the database. Of course, some existing basic technologies, such as dotnet ado. net, can be used to interact with the database. There are several key points:

1) encapsulate the underlying database interaction channel, which can use the existing database access technology, such as ADO. net.

2) construct SQL statements that interact with the database, such as adding, deleting, modifying, and querying. If the SQL statements are well performed, you must support associated queries. Of course, it is a little difficult to achieve esql in AEF.

The following is an example. Here I only construct the insert statement and implement the function of inserting entities into the current database. (There are many examples of accessing and encapsulating the underlying database ).
For the sake of simplicity, we assume that the class name of the object class is the table name of the corresponding table in the database, and the attribute of the object corresponds to the field in the table in the database. The following code is used:

// Entity class.
Public class amodel
{
Public String field1 {Get; set ;}
Public String field2 {Get; set ;}
Public int age {Get; set ;}
Public string name {Get; set ;}
}
/// <Summary>
/// SQL statement execution. Common classes or abstract interfaces can be used to facilitate access to different databases.
/// </Summary>
Public static class databasehelper
{
Public static void executesql (string strsql, sqlparameter [] Params)
{
;//......................
}
}
/// <Summary>
/// Basic operations on the object database. Here, we only simulate the case where the object is inserted into the database. Other operations are similar. If you update the object, you can use keywords as the condition, the conflict check field can also be included in the condition.
/// </Summary>
Public static class dboperation
{
Public static void insertmodel <t> (T Model)
{

// Here, the SQL statement and parameter construction results can be cached, so it is not necessary to construct them each time to reduce performance loss.
Dictionary <string, string> thepp = new dictionary <string, string> ();
Dictionary <string, sqldbtype> thept = new dictionary <string, sqldbtype> ();
String thesql = sqlbuilderhelper. builderinsertsql <t> (thepp, thept );
List <sqlparameter> theparams = new list <sqlparameter> ();
Foreach (VAR item in thepp)
{
Sqlparameter Thep = new sqlparameter ();
Thep. sqldbtype = thept [item. Key];
Thep. parametername = item. value;
Thep. value = getpropertyvalue (model, item. Key );
Theparams. Add (Thep );
}
Databasehelper. executesql (thesql. tostring (), theparams. toarray ());

}

// Obtain the property value based on the property name and put it in a helper class.
Private Static object getpropertyvalue <t> (T model, string propertyname)
{
Type thetype = model. GetType ();
Propertyinfo Pi = thetype. getproperty (propertyname );
Return pi. getvalue (model, null );
}
}
/// <Summary>
/// SQL and parameter builder. When constructing an SQL statement,Here, the default class name is the database table name, and the attribute name is the database field name. However, when constructing such a framework, you can use the meta attribute or configuration file to implement this function.. For example, linqtosql and dbcontext adopt the meta attribute. AEF and hibernate adopt the configuration file in XML format, but these things are designed to better and more accurately construct SQL statements.

/// SQL statements need to know the table name, field name, field type, and comparison between the language type and field type.
/// </Summary>
Public class sqlbuilderhelper
{
Public static string builderinsertsql <t> (Dictionary <string, string> propertyandparams, Dictionary <string, sqldbtype> thepanddbtype)
{
Type thetype = typeof (t );
Propertyinfo [] theproperties = thetype. getproperties ();
Stringbuilder thesql1 = new stringbuilder ();
Stringbuilder thesql2 = new stringbuilder ();
Propertyandparams. Clear ();
// In fact, you can also use the meta attribute to determine which attributes need to be inserted, which attributes can only be read, and the update mode (whether to assign values to the number type for update or incremental update).
Foreach (var p in theproperties)
{
If (thesql1.tostring () = "")
{
Thesql1.append (P. Name. toupper ());
Thesql2.append ("@" + P. Name. toupper ());
}
Else
{
Thesql1.append ("," + P. Name. toupper ());
Thesql2.append (", @" + P. Name. toupper ());
}
Propertyandparams. Add (P. Name, "@" + P. Name. toupper ());
Thepanddbtype. Add (P. Name, getdbtype (P. propertytype ));

}
Return "insert into" + thetype. Name. toupper () + "(" + thesql1.tostring () + ") values (" + thesql2.tostring () + ")";
}

// The comparison between the C # type and the database type can be completed through the map file or the meta attribute. I personally think that the use of the Meta attribute is better, although the meta attribute is not very flexible,

// But it reduces the dependency on the constructed files. It is advantageous in large projects, at least for ease of management.
Private Static sqldbtype getdbtype (type)
{
If (type = typeof (INT ))
{
Return sqldbtype. Int;
}
Return sqldbtype. nvarchar;
}
}
Sometimes we call an object a conceptual model, while the storage structure of an object in a database is called a physical model, there is a ing relationship between the two models (ing between concept names and physical names, ing between object attributes and field names, and ing between objects ), this is actually the so-called ORM (Object Relational Model ). In the preceding example, the ing is implemented using naming conventions. In fact, you can use configuration files or metadata to complete the ing. For large projects, configuration file management is also a problem, so I think it is better to use metadata.

The above implementation is just a simple example. In practice, you need to consider a lot of things, for example, You Need To encapsulate database differences (underlying access differences, SQL statement dialects ), the comparison between entities and databases (MAP), SQL statements and parameter cache problems (it is not necessary to construct them every time), and so on. However, the basic principles are the same in any case, the key technology is mainly the application of reflection mechanisms.

Next, we will introduce object management functions, such as caching, loading, adding, and deleting ....