用AR執行sql

摘自： http://forum.castleproject.org/viewtopic.php?t=52

Hi, I got somewhat the same problem. I need to run T-SQL commands, as we are new to ActiveRecord and NHibernate, and would like a quick "workaround" when we can't find our ways

I refered to http://castleproject.org/index.php/ActiveRecord:Using_HQL and made a derived class from ActiveRecordBaseQuery.

Code:

Public Class SqlNonQuery     Inherits ActiveRecordBaseQuery     Private _commandText As String     Public Property CommandText() As String         Get             Return _commandText         End Get         Set(ByVal Value As String)             _commandText = Value         End Set     End Property     Public Sub New(ByVal commandText As String)         MyBase.New(GetType(User))         _commandText = commandText     End Sub     Public Overrides Function Execute(ByVal session As NHibernate.ISession) As Object         Dim cmd As IDbCommand = session.Connection.CreateCommand()         cmd.CommandText = CommandText         cmd.ExecuteNonQuery()         Return Nothing     End Function End Class ''' usage ''' Dim q As SqlNonQuery("ANY SQL STATEMENT") ExecuteQuery(q)

But still have the same problem as Nick, need to pass some arbitrary ActiveRecord class type for the ActiveRecordBaseQuery constructor.

If you have better ways, please advice.. Thanks.

Ok, I figured out something that works:

Code:

ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder(); ISession session = holder.CreateSession(typeof(User)); IDbCommand cmd = session.Connection.CreateCommand(); cmd.CommandText = "ALTER TABLE stops ALTER description TYPE text;"; cmd.ExecuteNonQuery();              holder.ReleaseSession(session);

However it's a lot of code, and two things that bug me are:

1) Must give CreateSession some arbitrary ActiveRecord class type (type "User" in the example)
2) GetSessionFactoryHolder() seems to be a "temporary" method, not intended to be used

This disscuss is old though, I post the idea to solve above problem. It maybe help someone who has same problem.

Code:

// Create query SimpleQuery<object[]> query = new SimpleQuery<object[]>(                 typeof(TopCalled),                 @"select RecID, count(Called_Number) as frequency, Called_Number                                        from detail_record                                        group by Called_Number                                        order by frequency desc"); // Execute query object[][] results = query.Execute();

It also might work that you use TopCalled class instead of "object[]" if the class has "operator=(object[])".