SQL QUERY

andreazevedo · **Joined:** Thu Jul 06, 2006 4:40 pm **Posts:** 5

Hello,

Does NHibernate have any functionality to execute TSQL statments direct in database?

Thanks,

sergey · **Posted:** Fri Jul 07, 2006 4:52 pm

If you mean updates/inserts or queries returning simple values, then ADO.NET is just fine for that and NHibernate doesn't provide any duplicate API. If you mean queries returning persistent objects, NHibernate supports them (see Native SQL queries in the documentation).

andreazevedo · **Joined:** Thu Jul 06, 2006 4:40 pm **Posts:** 5

Ok...
So how can i execute a SELECT and return to me all dates in a table grouped by dates...
The T-SQL query would be like that:
SELECT date FROM table GROUP BY date

How can i do that with NHibernate?

And can I execute procedures with NHibernate?

Thank you.

luedi · **Joined:** Tue Feb 21, 2006 9:50 am **Posts:** 107

andreazevedo wrote:

Ok...
So how can i execute a SELECT and return to me all dates in a table grouped by dates...
The T-SQL query would be like that:
SELECT date FROM table GROUP BY date

How can i do that with NHibernate?

Code:

string sql = "SELECT date FROM table GROUP BY date";
ISession session = mSessionProvider.GetSession();
IQuery sqlQuery = session.CreateSQLQuery(sql, "vw",  pType);
IList result = sqlQuery.List();

But pay attention with the date format. With the Sql-Server 2000 / 2005 you may run in problems if the Windows client and the SqlServer use different languages. In our environment we have an english Sql-Server and a German Windows. We solve this problem by using the CONVERT() function and translating the date value to a string with american date format. This works with any language installation of the Sql-Server.

Code:

private string GetSqlDate(DateTime pDate)
{
  // translate date to US-format
  return "CONVERT(datetime, '" + pDate.ToString("MM/dd/yyyy") + "', 101)";
}

Quote:

And can I execute procedures with NHibernate?

In principle it should work with an SQLQuery. The problem is, that you don't have explicit support for Stored Procedures in NHibernate. If you want to pass parameters you have to concat them in the SQL. We have done some tests but came to the decision to call stored procedures directly via ADO.NET. You can use the same ConnectString as NHibernate:

Code:

protected string GetHibernateConnectString() 
{
// Use the same DB connectstring as NHibernate (read from App.config)
  NameValueCollection dbConfig = (NameValueCollection)ConfigurationSettings.GetConfig("nhibernate");
  return dbConfig["hibernate.connection.connection_string"].ToString();
}

Regards
Klaus

sergey · **Posted:** Sat Jul 08, 2006 4:57 am

Code:

ISession session = sessionFactory.OpenSession();
IDbConnection connection = session.Connection;

// Do whatever you want with the connection here - queries, updates, etc.

session.Close();

andreazevedo · **Joined:** Thu Jul 06, 2006 4:40 pm **Posts:** 5

But should I pass in pType?
I must create a new type (with only date) and pass it?
Thanks.

andreazevedo · **Joined:** Thu Jul 06, 2006 4:40 pm **Posts:** 5

Hello,

I've made a class (in a separete dll) that executes procedures using the same session and transaction that NHibernate uses.
What must I do? Post it in JIRA? Any ideas?

Thanks

sergey · **Posted:** Tue Jul 11, 2006 2:42 pm

A better place would be the community area of the Wiki, since this won't be integrated into NHibernate.

andreazevedo · **Joined:** Thu Jul 06, 2006 4:40 pm **Posts:** 5

And what is the link to access it?
Thanks.

sergey · **Posted:** Tue Jul 11, 2006 5:00 pm

http://www.hibernate.org/37.html (accessible by clicking Wiki Community Area link on www.hibernate.org).