Sometimes, you may need to perform SQL statements directly, stored procedures, etc., but NHibernate does not provide a method that allows us to perform SQL statements, but you can implement it through some indirect methods.
Idriver interface
The iDriver interface is the drive of the data accessed, with different drives for different data providers (SqlClient, OLEDB, etc.), with SQLClient, and the OLEDB corresponds to OLEDBDriver.
The iDriver interface is used to get the connection object, the command object, and format the command text.
2. Take the database connection object
To perform SQL, you must get the IDBConnection object, which can be obtained by the session factory. It should be noted that the iSessionFactory interface does not provide an operation related to the connection object, which is defined by the iSessionFactoryImplementor interface.
ISessionFactoryImplementor inherits from iSessionFactory, and the session factory for the session factory implements these two interfaces.
The code for obtaining the connection object is as follows:
ISessionFactoryImplementor factory = (iSessionFactoryImplementor) cfg.buildsessionFactory ();
IDBConnection conn = factory.openConnection ();
The OpenConnection method gets the IDBConnection object from the connection provider ConnectionProvider, and the connection provider creates IDBConnection through the Driver object.
3. Get the IDBCommand object
Inside NHIBERNATE, data operation is done by IDBCommand object, using a Command object prevents injection attack and processing some special characters.
Get the code of the IDBCommand object:
IDBCommand cmd = factory.connectionProvider.driver.createCommand ();
Some people may ask, directly new sqlcommand () is not enough (if there is anything in Sqlclient), do you have anything?
That's right, this is really ok, NHibernate is also doing this. But if we do it directly, then the code is not well portability. If you change the database connection mode, you need to change the code, and you don't have a need to change any code. Of course, the SQL statement is except.
As for the parameters, you can handle it through idbcommand.createparameter, here is not much.
4. Example
Next, a method of executing a SQL statement in NHibernate is ExecuteSQL.
Public IList ExecuteSQL (String Query) {
IList result = new arraylist ();
ISessionFactoryImplementor s = (iSessionFactoryImplementor) cfg.buildsessionFactory ();
IDBCommand cmd = S.ConnectionProvider.driver.createCommand ();
cmd.commandtext = query;
IDBConnection conn = S.OpenConnection ();
Try {
cmd.connection = conn;
IDataReader RS = cmd.executeReader (); while (rs.read ()) {
INT FIELDCOUNT = RS.FIELDCOUNT;
Object [] value = new object [fieldcount];
For (int i = 0; i VALUES [I] = rs.getvalue (i); Result.Add (VALUES); } } Finally { S.CloseConnection (conn); } Return Result; } Method of executing a stored procedure. Public IList ExecutestRedProc (String SPName, Icollection Paraminfos) { IList result = new arraylist (); ISessionFactoryImplementor s = (iSessionFactoryImplementor) cfg.buildsessionFactory (); IDBCommand cmd = S.ConnectionProvider.driver.createCommand (); CMD.CommandText = SPNAME; cmd.commandtype = commandtype.storedProcedure; // Add parameters IF (paraminfos! = null) { Foreach (paraminfo info in paraminfos) { IdbdataParameter Parameter = cmd.createParameter (); Parameter.ParameterName = info.name; // driver.formatnameforsql (Info.name); Parameter.Value = Info.Value; Cmd.Parameters.Add (parameter); } } IDBConnection conn = S.OpenConnection (); Try { cmd.connection = conn; IDataReader RS = cmd.executeReader (); While (RS.Read ()) { INT FIELDCOUNT = RS.FIELDCOUNT; Object [] value = new object [fieldcount]; For (int i = 0; i VALUES [I] = rs.getvalue (i); Result.Add (VALUES); } } Finally { S.CloseConnection (conn); } Return Result; } Where paraminfo is the structure of the storage parameter information, it is defined as follows: Public struct paraminfo { Public String Name; Public Object Value; } The return result is consistent with the result of NHibernate's Query (returns Object []).
