本节讲述怎么样利用Nhibernate执行SQL和存储过程
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="query">sql语句</param>
/// <returns></returns>
protected IList ExecuteSQL( string query)
{
IList result = new ArrayList();
ISessionFactoryImplementor s = (ISessionFactoryImplementor)cfg.BuildSessionFactory();
IDbCommand cmd = ((NHibernate.Driver.SqlClientDriver)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[] values = new Object[fieldCount];
for ( int i = 0; i < fieldCount; i++)
values[i] = rs.GetValue(i);
result.Add(values);
}
}
finally
{
s.CloseConnection(conn);
}
return result;
}
/// 执行sql语句
/// </summary>
/// <param name="query">sql语句</param>
/// <returns></returns>
protected IList ExecuteSQL( string query)
{
IList result = new ArrayList();
ISessionFactoryImplementor s = (ISessionFactoryImplementor)cfg.BuildSessionFactory();
IDbCommand cmd = ((NHibernate.Driver.SqlClientDriver)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[] values = new Object[fieldCount];
for ( int i = 0; i < fieldCount; i++)
values[i] = rs.GetValue(i);
result.Add(values);
}
}
finally
{
s.CloseConnection(conn);
}
return result;
}
这个是执行sql语句的,我们再看看执行存储过程的
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <param name="paramInfos">存储过程(字段-值)对</param>
/// <returns></returns>
protected IList ExecuteStoredProc( string spName, Dictionary< string, string> paramInfos)
{
IList result = new ArrayList();
ISessionFactoryImplementor s = (ISessionFactoryImplementor)cfg.BuildSessionFactory();
IDbCommand cmd = ((NHibernate.Driver.SqlClientDriver)s.ConnectionProvider.Driver).CreateCommand();
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
// 加入参数
if (paramInfos != null)
{
foreach (KeyValuePair< string, string> de in paramInfos)
{
IDbDataParameter parameter = cmd.CreateParameter();
parameter.ParameterName = de.Key.ToString(); // driver.FormatNameForSql( info.Name );
parameter.Value = de.Value;
cmd.Parameters.Add(parameter);
}
}
IDbConnection conn = s.OpenConnection();
try
{
cmd.Connection = conn;
IDataReader rs = cmd.ExecuteReader();
while (rs.Read())
{
int fieldCount = rs.FieldCount;
List< object> list = new List< object>();
object[] values = new Object[fieldCount];
for ( int i = 0; i < fieldCount; i++)
values[i] = rs.GetValue(i);
result.Add(values);
}
}
finally
{
s.CloseConnection(conn);
}
return result;
}
/// 执行存储过程
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <param name="paramInfos">存储过程(字段-值)对</param>
/// <returns></returns>
protected IList ExecuteStoredProc( string spName, Dictionary< string, string> paramInfos)
{
IList result = new ArrayList();
ISessionFactoryImplementor s = (ISessionFactoryImplementor)cfg.BuildSessionFactory();
IDbCommand cmd = ((NHibernate.Driver.SqlClientDriver)s.ConnectionProvider.Driver).CreateCommand();
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
// 加入参数
if (paramInfos != null)
{
foreach (KeyValuePair< string, string> de in paramInfos)
{
IDbDataParameter parameter = cmd.CreateParameter();
parameter.ParameterName = de.Key.ToString(); // driver.FormatNameForSql( info.Name );
parameter.Value = de.Value;
cmd.Parameters.Add(parameter);
}
}
IDbConnection conn = s.OpenConnection();
try
{
cmd.Connection = conn;
IDataReader rs = cmd.ExecuteReader();
while (rs.Read())
{
int fieldCount = rs.FieldCount;
List< object> list = new List< object>();
object[] values = new Object[fieldCount];
for ( int i = 0; i < fieldCount; i++)
values[i] = rs.GetValue(i);
result.Add(values);
}
}
finally
{
s.CloseConnection(conn);
}
return result;
}
需要引入的命名空间:
using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Web.UI;
using System.Reflection;
using System.Data;
using NHibernate.Engine;
using NHibernate;
using NHibernate.Cfg;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Web.UI;
using System.Reflection;
using System.Data;
using NHibernate.Engine;
using NHibernate;
using NHibernate.Cfg;
ok,我们再看我写的Controller
public ActionResult Details()
{
SpringContext.init();
SS_CodeDao daoHelper=SpringContext.Context.GetObject( "DaoHelper") as SS_CodeDao;
IList list = daoHelper.ExecuteSQLQuery( "select * from SS_CODE where len(C_ENAME)>5");
ViewData.Model = list;
return View();
}
{
SpringContext.init();
SS_CodeDao daoHelper=SpringContext.Context.GetObject( "DaoHelper") as SS_CodeDao;
IList list = daoHelper.ExecuteSQLQuery( "select * from SS_CODE where len(C_ENAME)>5");
ViewData.Model = list;
return View();
}
返回给页面一个IList,实际上是一个ArrayList。我们看看页面怎么获取
<%@ Page Title=
"" Language="C#
" MasterPageFile="~/Views/Shared/Site.Master
" Inherits="System.Web.Mvc.ViewPage<dynamic>" %>
<%@ Import Namespace= "Dao" %>
<%@ Import Namespace= "System.Web.UI" %>
<asp:Content ID= "Content1" ContentPlaceHolderID= "TitleContent" runat= "server">
Details
</asp:Content>
<asp:Content ID= "Content2" ContentPlaceHolderID= "MainContent" runat= "server">
<h2>
Details</h2>
<fieldset>
<legend>Fields</legend>
<% IList list = ViewData.Model; %>
<% for (var i = 0; i < list.Count; i++)
{
object[] obj = ( object[])list[i];
for ( int j = 0; j < obj.Length; j++)
{
%>
<div class= "display-label">
英文名称:</div>
<div class= "display-field">
<%: obj[0] %></div>
<div class= "display-label">
中文名称:</div>
<div class= "display-field">
<%: obj[1]%></div>
<div class= "display-label">
参数:</div>
<div class= "display-field">
<%: obj[2]%></div>
<div class= "display-label">
显示内容:</div>
<div class= "display-field">
<%: obj[3]%></div>
<div class= "display-label">
备注:</div>
<div class= "display-field">
<%: obj[4]%></div>
<%
}
%>
<%} %>
</fieldset>
</asp:Content>
<%@ Import Namespace= "Dao" %>
<%@ Import Namespace= "System.Web.UI" %>
<asp:Content ID= "Content1" ContentPlaceHolderID= "TitleContent" runat= "server">
Details
</asp:Content>
<asp:Content ID= "Content2" ContentPlaceHolderID= "MainContent" runat= "server">
<h2>
Details</h2>
<fieldset>
<legend>Fields</legend>
<% IList list = ViewData.Model; %>
<% for (var i = 0; i < list.Count; i++)
{
object[] obj = ( object[])list[i];
for ( int j = 0; j < obj.Length; j++)
{
%>
<div class= "display-label">
英文名称:</div>
<div class= "display-field">
<%: obj[0] %></div>
<div class= "display-label">
中文名称:</div>
<div class= "display-field">
<%: obj[1]%></div>
<div class= "display-label">
参数:</div>
<div class= "display-field">
<%: obj[2]%></div>
<div class= "display-label">
显示内容:</div>
<div class= "display-field">
<%: obj[3]%></div>
<div class= "display-label">
备注:</div>
<div class= "display-field">
<%: obj[4]%></div>
<%
}
%>
<%} %>
</fieldset>
</asp:Content>
运行结果如下
或者可以采用如下的方式
<sql-query name="存储过程名称">
<return-scalar column="参数1" type="类型"/>
<return-scalar column="" type="类型"/>
exec 存储过程名称 ?, ?
</sql-query>
public IList ParamStoredProcedure()
{
return session.GetNamedQuery( "存储过程名称")
.SetInt64(0, 值)
.SetInt64(1, 值)
.List();
}
{
return session.GetNamedQuery( "存储过程名称")
.SetInt64(0, 值)
.SetInt64(1, 值)
.List();
}
本文转自 BruceAndLee 51CTO博客,原文链接:http://blog.51cto.com/leelei/324431,如需转载请自行联系原作者