[Open source] No SQL Tour-chloe.orm of the changes and additions

Nonsense

This is a lightweight, efficient. NET C # Database access framework (ORM). The query interface draws on LINQ (but does not support LINQ). With lambda expressions, you can easily perform operations such as multi-table join queries, group queries, aggregate function queries, insert data, delete, and update data that meet criteria, all in an object-oriented manner.

In the previous article, Chloe.orm for the first time outside the public, although in the repetition of the wheel, but still get some of the support and recognition of the park friends, LZ thank you very much! The main topics in this article are the query method for Chloe and the supported lambda notation, which is the topic of multi-table connection queries, inserting data, updating data, deleting data, and transaction support.

Navigation

• Prepare beforehand
• Multi-Table Connection query
• Inserting data
• Update data
• Delete data
• Transaction support

Chloe.orm Pre-preparation

Entity:

 Public enumgender{man = 1, woman}[table ("Users")] Public classuser{[Column (IsPrimaryKey =true)] [AutoIncrement] Public intId {Get;Set; } Public stringName {Get;Set; } PublicGender? Gender {Get;Set; } Public int? Age {Get;Set; } Public int? Cityid {Get;Set; } PublicDatetime? OpTime {Get;Set; }}

First, create a DbContext:

New Mssqlcontext (dbhelper.connectionstring);

Then create a iquery<t>:

iquery<user> q = Context. Query<user> ();

Multi-Table Connection query

In the previous article, we introduced the query function of Chloe. When Chloe executes a connection query, the connection is established (the Iquery<t> interface calls Innerjoin, Leftjoin, Rightjoin, and Fulljoin) to return a generic object that is ijoiningquery. If you have seen the framework source of the classmate may know that ijoiningquery the generic parameters of up to 5, and there are 5 generic parameters of the class (ijoiningquery<t1,t2,t3,t4,t5>) There is only a Select method, That is, you cannot continue to connect to other tables, with up to 5 tables to make connections, is that really the case? If you think so, you're wrong! Let's take a look at how to build more than 5 tables of connections.

Pseudo code:

//Assume that 5 tables have been established to connect the object to Jq_q1_q5Ijoiningquery<t1, T2, T3, T4, t5> jq_q1_q5 =NULL;//jq_q1_q5 calls the Select method and returns a Iquery<t> object that contains T1-t5 view_q1_q5var view_q1_q5 = jq_q1_q5. Select ((t1, T2, T3, T4, t5) = =New{T1 = t1, T2 = t2, T3 = t3, T4 = T4, T5 = t5});//Assume that the Iquery<t6> object of the 6th table is Q6Iquery<t6> Q6 =NULL;///At this time, VIEW_Q1_Q5 and Q6 establish a connection to return the Ijoiningquery object JQvar JQ = view_q1_q5. Innerjoin (Q6, (T1_T5, t6) = T1_t5. T5. XX = = t6.xxx);then we call the JQ Select method and return a Iquery<t> object view containing T1-T6. //view is also a Iquery<t> object, and the generic parameter is an anonymous object that contains all the information for the T1-T6 (no SQL query has been initiated at this time), and we can do whatever we have to get it. var view = JQ. Select ((T1_T5, T6) =New{T1 = T1_t5. T1, T2 = T1_t5. T2, T3 = T1_t5. T3, T4 = T1_t5. T4, T5 = T1_t5. T5, T6 = T6});//can directly detect all the information in the database T1-t6View. ToList ();//You can also select the fields we want in T1-t6View. Select (A =New{a.t1.xx, a.t2.xx, a.t3.xx/*...*/}). ToList ();

In this way, it is possible to achieve infinite table connections, which is less intuitive than Linq, but this is the best way I can think of. If any students have a better design, hope to share, greatly appreciated!

Chloe's other bright spot is the aggregate function query and the group query, these two queries are different from Linq, more to the standard SQL idea, with the connection query use, can deal with some slightly complex query, can look at the article using the advanced part, there is a simple introduction, the Connection address:/HTTP Www.cnblogs.com/so9527/p/5636216.html#more_usage.

Many of the ORM on the market just support single-table queries, flexible operation of connection queries and aggregate function queries, except EF and LINQ to SQL, which is one of the reasons I built wheels.

Insert Data Mode 1

Insert in the form of a lambda:

New Mssqlcontext (dbhelper.connectionstring); //Return primary key Id int id = (intnew User () {Name = "lu", age =, Gender = Gender.man, Cityid = 1, OpTi me = DateTime.Now}); / * * INSERT into [Users] ([Name],[age],[gender],[cityid],[optime]) VALUES (N ' Lu ', 18,1,1,getdate ()); SELECT @ @IDENTITY * /

Mode 2

New New User (); user. Name = "lunew DateTime" (1992, 1, 16); //will automatically set the auto-increment ID to user's id attribute on user = context. Insert (user); / * String @P_0 = "Lu";   Gender @P_1 = man;   Int32 @P_2 =;   Int32 @P_3 = 1;   DateTime @P_4 = "1992/1/16 0:00:00";   INSERT into [Users] ([Name],[gender],[age],[cityid],[optime]) VALUES (@P_0, @P_1, @P_2, @P_3, @P_4); SELECT @ @IDENTITY * /

The first way to approach SQL is to have a choice of insertions. The second way is to insert all the mapped fields. For the insertion of data requirements, these two ways I think should be satisfied.

Update Data Mode 1

Updated as Lambda:

New New User () {Name = A.name, age = A.age +, Gender = Gender.man, OpTime = DateTime.Now}, a = = a.ID = 1); / * UPDATE [Users] SET [name]=[users]. [Name],[age]= ([users].[ Age] +, [Gender]=1,[optime]=getdate () where [Users].  [Id] = 1 *//batch update // for all female friends young 10 years old new User () {age = a.age-10, OpTime = DateTime.Now}, a = = A.gender = = Gender.woman); /* UPDATE [Users] SET [age]= ([users].[ Age], [Optime]=getdate () where [Users]. [Gender] = 2 * /

Mode 2

Mssqlcontext context =NewMssqlcontext (dbhelper.connectionstring); User User =NewUser (); user. Id = 1;user. Name = "Lu"; user. Age = 28;user. Gender = Gender.man;user. OpTime = Datetime.now;context. Update (user);//Updates all mapped fields/ * String @P_0 = "Lu";   Gender @P_1 = man;   Int32 @P_2 = 28;   nullable<int32> @P_3 = NULL;   DateTime @P_4 = "2016/7/8 11:28:27";   Int32 @P_5 = 1; UPDATE [Users] SET [name][email protected]_0,[gender][email protected]_1,[age][email Protected]_2,[cityid][email Protected]_3,[optime][email protected]_4 WHERE [Users]. [Id] = @P_5 * // * * Supports updating only properties that have changed property values * /Context. trackentity (user);//Tracking entities in contextUser. Name = user. Name + "1"; context. Update (user);//This will only update the modified fields/ * String @P_0 = "LU1";   Int32 @P_1 = 1; UPDATE [Users] SET [Name][email protected]_0 WHERE [users]. [Id] = @P_1 * /

Like the first way you can batch update data methods, I think in real development is necessary, and indispensable.

Before, the colleague uses the EF to develop a function, needs the batch update, his practice is to find all the data that satisfies the condition, then assigns the value, finally unifies SaveChanges (), the development time because the data quantity is small, did not discover the problem. Deployment on-line after the user casually click on the save, this method of execution will be a long time, an instant Meng, not much to say why, you understand! While someone based EF has made an EF extension class library that supports bulk updates, it's not easy to take risks into the project after all. Then splicing a batch Update SQL chant, the original ecological implementation. Oh! Think of spelling sql, mind 10,000 grass Muddy road!!! All. net4.5+ era,. NET Core is out, still clinging to handwritten SQL? It's a time-consuming and laborious job for a programmer to quit! Although I write a very good SQL, but I do not have the time when I will not be able to spell SQL. Project development stage, I am how fast how to do the principle, there can be 10 minutes to solve the problem of the way, I will not choose 10 minutes + 1 seconds of other methods! Not one more second! As for optimization, that is the funeral.
It is one of the reasons why I build the wheel by doing my own work and customizing the ideal function according to the requirements.

Delete Data Mode 1

Delete as lambda:

New Mssqlcontext (dbhelper.connectionstring); context. Delete<user> (A = a.ID = = 1); / * * DELETE [users] WHERE [users]. [Id] = 1 *///Bulk Delete //Delete all non-male and female users null); / * * DELETE [users] WHERE [users]. [Gender] is NULL * /

Mode 2

New New User (); user. Id = 1;context. Delete (user); / * * Int32 @P_0 = 1;   DELETE [Users] WHERE [users]. [Id] = @P_0 * /

Deleting data is as simple as this.

Transaction support

The Chloe transaction operation method is in the Dbcontext.currentsession attribute:

New Mssqlcontext (dbhelper.connectionstring); idbsession dbsession = context. Currentsession; Try {    dbsession.begintransaction ();    //to do somethings    ... Dbsession.committransaction ();} Catch {    dbsession.rollbacktransaction ();}

Transactions are a must-have feature for each ORM. Too simple, originally did not want to write in, think, write, make up a word.

Conclusion

Chloe.orm main convenient, efficient, whether it is Idbcontext or iquery<t> interface, design is extremely simple, basic one can understand, a use will. And do not rely on any configuration, easy to get started, pass a database connection string can run, I like this kind of idiot development. One thing I'm very puzzled about is that C # is a highly simplified language, and many people say that Microsoft is making it more and more silly to develop a programmer. This is the development of language progress Ah, why should we refuse! Do those people like to toss? Say not good, if a language really put a programmer to "silly", I think, that programmer is certainly not the best programmer! Pull away, hey.

Faith in the spirit of open source sharing, promote domestic open source business development, Chloe.orm fully open source, follow the Apache2.0 agreement.

Github:https://github.com/shuxinqin/chloe

Open source China: http://www.oschina.net/p/chloe-orm

PS: Little brother, cultural, technical Foundation Limited, there will be shortcomings, look at the road big God a lot of guidance, thank you very much. Also please have the objection of the classmate, point against the meantime, trouble also leave a suggestion to leave, OK? Thank you.

[Open source] No SQL Tour-chloe.orm of the changes and additions