A summary of the methods for executing the original SQL code in nhib.pdf.

Complex SQL queries are often used in the process of using nhib.pdf, but when hql is troublesome, we often think of using the original SQL for execution. But how can we use nhib.pdf to execute SQL statements? The problem is that the AdoTemplate method can execute SQL statements in nhib.pdf, but here we will introduce another method: CreateSQLQuery. The following examples are from networks.

 

 

Instance 1(Originated from metadata ):

 

 

Examples of CreateSQLQuery usage in nhibquery:

 

Involved tables:

 

 

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. Cake {
3. Id,
5. CakeName
7. ....
9. }
12. CakeSize {
14. CakeId,-is a foreign key, corresponding to the field Id of the Cake table
16. Size
18. }

Cake {</p> <p> Id, </p> <p> CakeName </p> <p> .... </P> <p >}</p> <p> CakeSize {</p> <p> CakeId,-is a foreign key, id of the field corresponding to the Cake table </p> <p> Size </p> <p >}< br/>

 

 

(ISession session = NHibernateHelper. GetCurrentSession ();)

 

Usage 1 (Return Value ):

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISQLQuery query = session. CreateSQLQuery ("select count (Id) as c from Cake"). AddScalar ("C", NHibernateUtil. Int32 );
2. Int c = Convert. ToInt32 (query. UniqueResult ());

ISQLQuery query = session. createSQLQuery ("select count (Id) as c from Cake "). addScalar ("C", NHibernateUtil. int32); <br/> int c = Convert. toInt32 (query. uniqueResult ());
Or int c = query. UniqueResult <int>; // This method finds that the SQL statement is executed twice, so it is not recommended.

 

Usage 2 (return object entity ):

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISQLQuery query = session. CreateSQLQuery ("select * from cake c"). AddEntity ("CAKE. DataTransfer. Entities. Cake ");

ISQLQuery query = session. CreateSQLQuery ("select * from cake c"). AddEntity ("CAKE. DataTransfer. Entities. Cake ");
Or

 

 

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISQLQuery query = session. CreateSQLQuery ("select * from cake c"). AddEntity ("c", "CAKE. DataTransfer. Entities. Cake ");

ISQLQuery query = session. CreateSQLQuery ("select * from cake c"). AddEntity ("c", "CAKE. DataTransfer. Entities. Cake ");
Or

 

 

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISQLQuery query = session. CreateSQLQuery ("select * from cake c"). AddEntity (typeof (Cake ));

ISQLQuery query = session. CreateSQLQuery ("select * from cake c"). AddEntity (typeof (Cake ));
Or

 

 

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISQLQuery query = session. CreateSQLQuery ("select * from cake c"). AddEntity ("c", typeof (Cake ));

ISQLQuery query = session. CreateSQLQuery ("select * from cake c"). AddEntity ("c", typeof (Cake); <br/>

 

Or

 

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISQLQuery query = session. CreateSQLQuery ("select * from cake c"). AddEntity ("c", typeof (Cake), LockMode. Write );
3. IList <Cake> c = query. List <Cake> ();

ISQLQuery query = session. createSQLQuery ("select * from cake c "). addEntity ("c", typeof (Cake), LockMode. write); </p> <p> IList <Cake> c = query. list <Cake> (); <br/>

 

 

 

Usage 3 (table join query ):

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISQLQuery query = session. CreateSQLQuery ("select cs. * from cake c join CakeSize cs on cs. CakeId = c. Id ")
2. . AddEntity ("cs", typeof (CakeSize ));
3. IList <CakeSize> cs = query. List <CakeSize> ();

ISQLQuery query = session. createSQLQuery ("select cs. * from cake c join CakeSize cs on cs. cakeId = c. id ") <br/>. addEntity ("cs", typeof (CakeSize); <br/> IList <CakeSize> cs = query. list <CakeSize> ();

 

 

 

The above is a common usage. Through this example, I believe you already have a number in your mind. You should also know how to operate this SQL statement :)

 

However, some problems are inevitable during the process, such:

 

Example 2 (Problem description ):

 

Run the following SQL statement:

 

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. Select CreateTime, Address, Password, (case when EmailType = 0 then 'gmail 'when EmailType = 1 then 'yahoo' when EmailType = 2 then 'hotmail' else 'other' end) as EmailType from MailInfo

Select CreateTime, Address, Password, (case when EmailType = 0 then 'gmail 'when EmailType = 1 then 'yahoo' when EmailType = 2 then 'hotmail' else 'other' end) as EmailType from MailInfo <br/>

 

 

1. If you use the session. CreateQuery Method for execution, you may encounter the following error:

 

Error: undefined alias or unknown mapping

 

 

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISession session = DAORepository. ance. DbSession;
2. String queryString = "select CreateTime, Address, Password, (case when EmailType = 0 then 'gmail 'when EmailType = 1 then 'yahoo' when EmailType = 2 then 'hotmail' else 'other' end) as EmailType from MailInfo where status = 0 Order by CreateTime desc ";
3. IQuery query = session. CreateQuery (queryString;
4. IList lst = query. List ();

ISession session = DAORepository. balance ance. dbSession; <br/> string queryString = "select CreateTime, Address, Password, (case when EmailType = 0 then 'gmail 'when EmailType = 1 then 'yahoo' when EmailType = 2 then 'hotmail' else 'other' end) as EmailType from MailInfo where status = 0 Order by CreateTime desc "; <br/> IQuery query = session. createQuery (queryString; <br/> IList lst = query. list ();

 

 

 

2. Use the CreateSQLQuery method to execute:

 

Error: Return types of SQL query were not specified...

 

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISession session = DAORepository. ance. DbSession;
2. String queryString = "select CreateTime, Address, Password, (case when EmailType = 0 then 'gmail 'when EmailType = 1 then 'yahoo' when EmailType = 2 then 'hotmail' else 'other' end) as EmailType from MailInfo where status = 0 Order by CreateTime desc ";
3. ISQLQuery query = session. CreateSQLQuery (queryString );
4. IList lst = query. List ();

ISession session = DAORepository. balance ance. dbSession; <br/> string queryString = "select CreateTime, Address, Password, (case when EmailType = 0 then 'gmail 'when EmailType = 1 then 'yahoo' when EmailType = 2 then 'hotmail' else 'other' end) as EmailType from MailInfo where status = 0 Order by CreateTime desc "; <br/> ISQLQuery query = session. createSQLQuery (queryString); <br/> IList lst = query. list ();

 

 

3. Use the CreateSQLQuery method and specify the type of the returned field value, that is, use the AddScalar method to specify the field value type:

 

Error: cocould not execute query...

 

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISession session = DAORepository. ance. DbSession;
2. String queryString = "select CreateTime, Address, Password, (case when EmailType = 0 then 'gmail 'when EmailType = 1 then 'yahoo' when EmailType = 2 then 'hotmail' else 'other' end) as EmailType from MailInfo where status = 0 Order by CreateTime desc ";
3. ISQLQuery query = session. CreateSQLQuery (queryString). AddScalar ("CreateTime", NHibernateUtil. String)
4. . AddScalar ("Address", NHibernateUtil. String)
5. . AddScalar ("Password", NHibernateUtil. String)
6. . AddScalar ("EmailType", NHibernateUtil. Int32 );
7. IList lst = query. List ();

ISession session = DAORepository. balance ance. dbSession; <br/> string queryString = "select CreateTime, Address, Password, (case when EmailType = 0 then 'gmail 'when EmailType = 1 then 'yahoo' when EmailType = 2 then 'hotmail' else 'other' end) as EmailType from MailInfo where status = 0 Order by CreateTime desc "; <br/> ISQLQuery query = session. createSQLQuery (queryString ). addScalar ("CreateTime", NHibernateUtil. string) <br/>. addScalar ("Address", NHibernateUtil. string) <br/>. addScalar ("Password", NHibernateUtil. string) <br/>. addScalar ("EmailType", NHibernateUtil. int32); <br/> IList lst = query. list ();

 

 

Note: The database type corresponding to the EmailType field is int. Here I misunderstand that it should also be mapped to NHibernateUtil. Int32;

 

 

4. Solve the problem:

Modify an attribute in the parameters of the AddScalar method:

 

[C-sharp: showcolumns] View plain Copy Print ?··· · 50 ······· · 90 ····· · 140 · 150

1. ISession session = DAORepository. ance. DbSession;
2. String queryString = "select CreateTime, Address, Password, (case when EmailType = 0 then 'gmail 'when EmailType = 1 then 'yahoo' when EmailType = 2 then 'hotmail' else 'other' end) as EmailType from MailInfo where status = 0 Order by CreateTime desc ";
3. ISQLQuery query = session. CreateSQLQuery (queryString). AddScalar ("CreateTime", NHibernateUtil. String)
4. . AddScalar ("Address", NHibernateUtil. String)
5. . AddScalar ("Password", NHibernateUtil. String)
6. . AddScalar ("EmailType", NHibernateUtil. String );
7. IList lst = query. List ();

ISession session = DAORepository. balance ance. dbSession; <br/> string queryString = "select CreateTime, Address, Password, (case when EmailType = 0 then 'gmail 'when EmailType = 1 then 'yahoo' when EmailType = 2 then 'hotmail' else 'other' end) as EmailType from MailInfo where status = 0 Order by CreateTime desc "; <br/> ISQLQuery query = session. createSQLQuery (queryString ). addScalar ("CreateTime", NHibernateUtil. string) <br/>. addScalar ("Address", NHibernateUtil. string) <br/>. addScalar ("Password", NHibernateUtil. string) <br/>. addScalar ("EmailType", NHibernateUtil. string); <br/> IList lst = query. list ();

 

 

Note: Here I change the EmailType type ing to NHibernateUtil. String,It is actually OK if it is consistent with the EmailType Value Type of the returned result..

 

Well, I believe you have read the above example and have some feelings. Finally, I would like to thank the authors mentioned in this article again and share their experiences with them.

 

 

 

 

 

 

 

 

 

This article mainly draws from:

Http://blog.csdn.net/canduecho/archive/2009/05/04/4149930.aspx

Http://hi.baidu.com/shuhaicaiyun/blog/item/62f7118112eee1dfbd3e1e51.html