Nhibernate操作原生SQL以及查询DataTable

使用Nhibernate时我们很方便实现实体映射，但是如果遇到复杂查询则显得力不从心，这是我们可以利用Nhibernate来操作原生sql来查询到DataTable来实现我们复杂的查询及其它操作。以下实例已经过调试，现与大家分享。

/// <summary>

/// 执行ExecuteNonQuery

/// </summary>

/// <param name="sql"></param>

public void ExecuteNonQuery(string sql)

{

    ISession session = null;

    ITransaction transaction = null;

    try

    {

        session = NHibernateHelper.GetCurrentSession();

        transaction = session.BeginTransaction();

        IDbCommand command = session.Connection.CreateCommand();

        transaction.Enlist(command);//注意此处要把command添加到事物中

        command.CommandText = sql;

        command.ExecuteNonQuery();

        transaction.Commit();

    }

    catch (Exception ex)

    {

        if (transaction != null)

        {

            transaction.Rollback();

        }

        throw ex;

    }

    finally

    {

        if (session != null)

        {

            session.Close();

        }

    }

}

 

/// <summary>

/// 填充DataSet

/// </summary>

/// <param name="sql"></param>

/// <returns></returns>

public static  DataSet ExecuteDataset(string sql)

{

    ISession session = null;

    DataSet ds = new DataSet();

    try

    {

        session = NHibernateHelper.GetCurrentSession();

        IDbCommand command = session.Connection.CreateCommand();

        command.CommandText = sql;

 

        IDataReader reader = command.ExecuteReader();

        DataTable result = new DataTable();

        //result.Load(reader);//此方法亦可

        DataTable schemaTable = reader.GetSchemaTable();

        for (int i = 0; i < schemaTable.Rows.Count; i++)

        {

            string columnName = schemaTable.Rows[i][0].ToString();

            result.Columns.Add(columnName);

        }

        while (reader.Read())

        {

            int fieldCount = reader.FieldCount;

            object[] values = new Object[fieldCount];

            for (int i = 0; i < fieldCount; i++)

            {

                values[i] = reader.GetValue(i);

            }

            result.Rows.Add(values);

        }

        ds.Tables.Add(result);

    }

    catch (Exception ex)

    {

        Debug.Assert(false);

    }

    finally

    {

        if (session != null)

        {

            session.Close();

        }

    }

    return ds;

}

 

/// <summary>

/// 填充DataSet（此方法亦可）

/// </summary>

/// <param name="sql"></param>

/// <returns></returns>

public DataSet ExecuteDataset(string sql)

{

    ISession session = null;

    DataSet ds = new DataSet();

    try

    {

        session = NHibernateHelper.GetCurrentSession();

        IDbCommand command = session.Connection.CreateCommand();

        command.CommandText = sql;

        SqlDataAdapter da = new SqlDataAdapter(cmd as SqlCommand);

        da.Fill(ds);

    }

    catch (Exception ex)

    {

        Debug.Assert(false);

    }

    finally

    {

        if (session != null)

        {

            session.Close();

        }

    }

    return ds;

}