PDF.NETThe SOD framework is not only an ORM, but its ORM function is unique. I have introduced it many times in the blog, but it is all theoretical. Many beginners may still feel complex. In fact, the ORM of SOD is very simple.Let's take a step-by-step look at the popular Code First approach.
I. Preparations
1.1, add SOD package reference
Start with a console project (one that supports.NET 2.0) and add it using the Package ManagerPDF.NETSOD program reference:
PM> Install-Package PDF.NET.SOD
For more detailed usage instructions, refer to the instructions on the nuget website https://www.nuget.org/packages/PDF.NET/
1.2, Configure Data Connections
Create a new console project, add an application configuration file, and add a database connection configuration:
<?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="local" connectionString="Data Source=.;Initial Catalog=LocalDB;Integrated Security=True" providerName="SqlServer" /> </connectionStrings> </configuration>
The connection string above requires that you have SqlServer installed locally, that the framework is compatible with all versions over 2000, and that one database name is LocalDB.Of course, you can also modify it to your actual connection string.
This connection configuration will then be used in our query example.
Note: If the latest version of the SOD framework uses SqlServer, and the connection string specifies the name of the database but does not actually have it, the framework can automatically create the database. This function requires the support of the SOD's Code First function. See below "1.5, Create Database Tables" function.
The most basic configuration of the SOD framework requires only this place, which is simpler than EF.
If the connection configuration is SqlServer +EF Code First, the SOD framework can also use this connection string.
1.3, defining entity classes
For example, we'll use the most common user login, where a user entity class is required, assuming its definition looks like this:
public class User : EntityBase { public User() { TableName="Tb_User"; IdentityName = "UserID"; PrimaryKeys.Add("UserID"); } public int ID { get { return getProperty<int>("UserID"); } set { setProperty("UserID", value); } } public string Name { get { return getProperty<string>("Name"); } set { setProperty("Name", value, 50); } } public string Pwd { get { return getProperty<string>("Pwd"); } set { setProperty("Pwd", value, 50); } } public DateTime RegistedDate { get { return getProperty<DateTime>("RegistedDate"); } set { setProperty("RegistedDate", value); } } }
In the definition above, the IdentityName = "UserID" is specified within the constructor; this means that the table corresponding to the current entity class has an added column called UserID that can be used to get the value of the newly inserted added ID from the property corresponding to the added column whenever an entity class is inserted.
In addition, we find that the attributes of each entity class are written as follows:
public int ID { get { return getProperty<int>("UserID"); } set { setProperty("UserID", value); } }
The first parameter in the getProperty, setProperty method, is the field name corresponding to the property.
Therefore, such attributes, the SOD framework is called "persistent attributes".
As you can see, SOD entity classes are relatively simple and do not use attributes to declare database information, which means that you can modify database metadata, such as primary keys, self-added fields, table names, etc., for entity class projections at run time, without requiring reflection, which forms a simple and powerful basis for the SOD framework.
1.4, add data context of query object
Add one to the project LocalDbContext.cs File, which adds the following code to check the table Tb_Whether User exists or not, automatically creates one:
/// <summary> /// Local for testing SqlServer Database Context Class /// </summary> public class LocalDbContext : DbContext { public LocalDbContext() : base("local") { //local Is the connection string name } #region Implementation of parent abstract method protected override bool CheckAllTableExists() { //Create User Table CheckTableExists<User>(); return true; } #endregion }
In this example, SqlServer is used as an example of a query, and the program automatically uses the SqlServerDbContext sealed class for internal collaboration, depending on your AdoHelper instance type and, in the case of Oracle, the OracleDbContext sealed class.
If you do not need the Code First functionality of the ORM, this DbContext implementation class does not need to be written.
1.5, create database tables
OK, you're almost ready. Now you can write the following code in the Main method to start working:
LocalDbContext context = new LocalDbContext();//Automatically create tables
Ready, run this code, then look at the SqlServer administration tool and find the table Tb_User has already been created.
2. Increase, delete, change of ORM
The ORM function of the SOD framework is different from the ordinary ORM framework. There is no method for data query and persistence on the entity class of the SOD framework, so the entity class of the SOD framework is a "very pure" entity class. You can think of it as a data container or use it as DTO or ViewModel. For a more detailed explanation of this topic, see this article: DataSet's flexibility, entity class's convenience, DTO's efficiency: SOD framework's data container to create the ORM framework best suited for DDD>.
Before you can make a real data query, you have to have data, so let's test the data for additions and deletions.
2.1, Delete data
First delete the previous test data, you can use OQL to delete the batch data:
int count = 0; //Delete test data----------------------------------------------------- User user = new User(); OQL deleteQ = OQL.From(user) .Delete() .Where(cmp => cmp.Comparer(user.ID, ">", 0)) //For safety, do not take Where The condition is that not all data will be deleted .END; count= EntityQuery<User>.ExecuteOql(deleteQ, context.CurrentDataBase);
All data with user ID greater than 0 is deleted here. Data security mechanism is built in the framework. All data will not be deleted at will, so the above method is used to clear all data.
2.2, increase data
Mode 1, using the Add method of DbContext:
count = 0; User zhang_san = new User() { Name = "zhang san", Pwd = "123" }; count += context.Add<User>(zhang_san);//Use DbContext Insert data in
Mode 2, using OQL:
User li_si = new User() { Name = "li si", Pwd = "123" }; OQL insertQ= OQL.From(li_si) .Insert(li_si.Name);//Insert user name only, no password
OQL also needs an EntityQuery object to work with before it can execute. Continue with the following code:
AdoHelper db = MyDB.GetDBHelperByConnectionName("local"); EntityQuery<User> userQuery = new EntityQuery<User>(db); count += userQuery.ExecuteOql(insertQ);
The following is the result graph:
Mode 3, insert entity classes directly into EntityQuery:
User zhang_yeye = new User() { Name = "zhang yeye", Pwd = "456" }; count += EntityQuery<User>.Instance.Insert(zhang_yeye);//Using generics EntityQuery Insert data in
2.3, Modify data:
Mode 1, Update data with DbContext
li_si.Pwd = "123123"; count = context.Update<User>(li_si);//Use DbContext Update data in
Mode 2, Update specified data using OQL
li_si.Pwd = "123456"; OQL updateQ= OQL.From(li_si) .Update(li_si.Pwd) //Just update the password .END; count += EntityQuery<User>.Instance.ExecuteOql(updateQ);//Use OQL Update specified data in
Mode 3, modify data using generic EntityQuery
li_si.Pwd = "888888"; count += EntityQuery<User>.Instance.Update(li_si);//Using generics EntityQuery Modify data in
3. Data query of ORM
Now that we have the test data, we can test the data query of ORM. Here we use the example of user login to test. The framework provides six ways of data query.
3.1, easiest way
Assuming that the front end passes a User entity class object directly with a username and password set in between, there is now a login method that uses the object, which details as follows:
/// <summary> /// Log in using the user object, OQL The easiest and most common way to use it /// </summary> /// <param name="user"></param> /// <returns></returns> public bool Login(User user) { OQL q = OQL.From(user) .Select() .Where(user.Name, user.Pwd) //Verify login with username and password .END; User dbUser =q.ToEntity<User>();//ToEntity，OQL Extension Method return dbUser != null; //Query to user entity class to indicate successful login }
Here we use the ORM query language of the SOD framework, OQL, which is very similar to SQL in structure. You can think of OQL as an object-oriented SQL statement.
OQL expression uses the syntax of.From.....END, the chain method call of the object, as long as it is hit out correctly, so that students without the SQL base can quickly master the query syntax, and can also start data development immediately.
Note: In this case, the OQL extension method is used, so the following namespace needs to be referenced:
using PWMIS.Core.Extensions;
If you do not use the extension method, you can use the generic EntityQuery method, as shown in the following example.
3.2, EqualValue equivalence comparison using the OQLCompare object
/// <summary> /// Use the user object to log in, but use OQLCompare Object's EqualValue Equal Comparison /// </summary> /// <param name="user"></param> /// <returns></returns> public bool Login1(User user) { OQL q = OQL.From(user) .Select(user.ID) //Query only one attribute field ID .Where(cmp => cmp.EqualValue(user.Name) & cmp.EqualValue(user.Pwd)) .END; User dbUser = EntityQuery<User>.QueryObject(q); return dbUser != null; //Query to user entity class to indicate successful login }
As in Example 1, the Name and Pwd properties of the user object must have values in advance.This example does not use the OQL extension method.
3.3, EntityQuery generic query method
This example only makes improvements to Example 1. The emphasis is that the parameters of the login method are not user objects, but name and password parameters.
/// <summary> /// Log in using the username password parameter, using EntityQuery Generic Query Method /// </summary> /// <param name="name"></param> /// <param name="pwd"></param> /// <returns></returns> public bool Login2(string name, string pwd) { User user = new User() { Name = name, Pwd = pwd }; OQL q = OQL.From(user) .Select(user.ID) .Where(user.Name, user.Pwd) .END; User dbUser = EntityQuery<User>.QueryObject(q); return dbUser != null; //Query to user entity class to indicate successful login }
3.4, using the OQLConditon object as the query condition
/// <summary> /// Log in using the username password parameter, using an earlier instantiation OQL Object, and use OQLConditon Object is a query condition /// </summary> /// <param name="name"></param> /// <param name="pwd"></param> /// <returns></returns> public bool Login3(string name, string pwd) { User user = new User(); OQL q = new OQL(user); q.Select(user.ID).Where(q.Condition.AND(user.Name, "=", name).AND(user.Pwd, "=", pwd)); User dbUser = EntityQuery<User>.QueryObject(q); return dbUser != null; //Query to user entity class to indicate successful login }
This was an early form of conditional query in OQL. The disadvantage is that complex query conditions cannot be constructed.
3.5, operator overload
Operator overloading of OQLCompare simplifies comparison conditions as follows:
/// <summary> /// Log in using the username password parameter and compare query conditions using operator overload /// </summary> /// <param name="name"></param> /// <param name="pwd"></param> /// <returns></returns> public bool Login4(string name, string pwd) { User user = new User(); OQL q = OQL.From(user) .Select() .Where( cmp => cmp.Property(user.Name) == name & cmp.Property(user.Pwd) == pwd ) .END; User dbUser = EntityQuery<User>.QueryObject(q); return dbUser != null; //Query to user entity class to indicate successful login }
3.6, using generic OQL queries (GOQL)
Using generic OQL query (GOQL), the simplest way to use single entity class query has the disadvantage of not being able to perform "joined table query", that is, joined query of multiple entity classes.
/// <summary> /// Log in with the username password parameter, use generics OQL Query ( GOQL)，The simplest way to use a single entity class query. /// </summary> /// <param name="name"></param> /// <param name="pwd"></param> /// <returns></returns> public bool Login5(string name, string pwd) { User dbUser = OQL.From<User>() .Select() .Where((cmp, user) => cmp.Property(user.Name) == name & cmp.Property(user.Pwd) == pwd ) .END .ToObject(); return dbUser != null; //Query to user entity class to indicate successful login }
3.7, using entity class primary keys to query
The "primary key" field of the SOD entity class can be modified so that you can modify it at any time, just like the original primary key of the entity class, to populate the data. This example is to determine whether the current entity class is populated successfully to determine whether the user can log in.
/// <summary> /// Log in using the username password parameter, but populate the entity class based on its primary key and determine if it succeeds. /// </summary> /// <param name="name"></param> /// <param name="pwd"></param> /// <returns></returns> public bool Login6(string name, string pwd) { User user = new User(); user.PrimaryKeys.Clear(); user.PrimaryKeys.Add("Name"); user.PrimaryKeys.Add("Pwd"); user.Name = name; user.Pwd = pwd; bool result= EntityQuery<User>.Fill(user);//Static method, using default connection object return result; }
3.8, Query Multiple Data
The previous examples are just querying one data. If you need to query more than one data, it is also easy. See the following example, how to query all users with the surname of zhang:
/// <summary> /// Fuzzy query user, return user list, use OQLCompare Entrust /// </summary> /// <param name="likeName">User name to match</param> /// <returns>User List</returns> public List<User> FuzzyQueryUser(string likeName) { User user = new User(); OQL q = OQL.From(user) .Select() .Where(cmp => cmp.Comparer(user.Name, "like", likeName+"%") ) .END; List<User> users = EntityQuery<User>.QueryList(q); return users; }
Front end call:
//Query List var users=service.FuzzyQueryUser("zhang"); Console.WriteLine("Fuzzy Query for Last Name Zhang Users, Number:{0}",users.Count );
So to query multiple pieces of data, you only need to use the QueryList method of the generic EntityQuery object.Similarly, the framework provides you with an OQL object extension method to query list data directly.
3.9, Entity Class Joint Query
There are no more examples here, and my blog posts have been explained many times. Please refer to the following series of articles:
4. Design Principles of SOD Framework
The SOD framework is designed with the principle of "Rome is the road to Rome". It is a multi-mode solution. The development of data is not only SQL-MAP, ORM.Which mode of Data Control can return to the same destination for different purposes, which is fully reflected in OQL, such as the user login function above, which is implemented in seven ways, and in fact, there are three query modes which are not explained in this article. For the addition, deletion and change of entity classes, DbContext,OQL, generic EntityQuery and other ways are provided.
Therefore, the use of the SOD framework is very flexible, you can use it flexibly according to your preferences, habits, environment, and it is easy to expand. Therefore, compared to the ORM framework such as EF, the ORM function of the SOD framework is free, flexible, lightweight and easy to expand, but it does not hinder its power, such as query of table and database, batch data.Support for these "enterprise" data development needs, such as updates, inserts, modifications, direct support for database locks.
V. Related Resources
The source code for this article has been uploaded to an open source project in the frameworkPwmis.codeplex.comOn the website, you can view the complete source code at the following address:
http://pwmis.codeplex.com/SourceControl/latest#SOD/Test/SampleORMTest/Program.cs
Source download, can be viewed at the following address:
http://pwmis.codeplex.com/releases/view/117822 
Simple ORM Sample Program--Required for Beginners
For a more detailed and complete introduction to the framework, refer to the following article:
PDF.NETSOD Open Source Framework Red Pack Delivery Activity & Quick Start Guide for Beginners
For more complete and detailed information, see the frame's official website address:
The framework is already fully open source. See this article:
The year starts in spring, 2015:PDF.NETSOD Ver 5.1 Fully Open Source
In addition, the netizen Guangzhou-Sichenai wrote a basic introductory article in his "The Harbor of Giant Hung Gogh". PDF.NET Getting started first "Recommend you to see it.
Netizen Guangzhou-Yingu wrote an article, " SOD Rejuvenates Your Old Code "Describes how to transform the process of old-style rigid projects, we recommend you to look at it.
6. Latest Source Resource Address
CodePlex online : http://pwmis.codeplex.com/SourceControl/latest
CodePlex SVN : https://pwmis.codeplex.com/svn
GitHub : https://github.com/znlgis/PDF.NET-SOD
OSChina : http://git.oschina.net/dxzyx/SOD