Nhibernate调用mssql和mysql的存储过程

表

 public class Customer
    {
        public virtual int CustomerId { get; set; }
        public virtual string FirstName { get; set; }
        public virtual string LastName { get; set; }
    }

 

 

测试数据

insert into Customer values(1,'张','三');
insert into Customer values(2,'张','四');
insert into Customer values(3,'张','五')

 

 

存储过程（前2个MSSQL 后2个MYSQL)

sql 版本的 

CREATE PROCEDURE [dbo].[SelectCustomer]
AS
Begin
Select CustomerId,FirstName, LastName from Customer
end
GO

CREATE PROCEDURE [dbo].[CustomerDelete]
(
 @CustomerId int
)
AS

 DELETE
 FROM   [Customer]
 WHERE 
  [CustomerId] = @CustomerId

 RETURN @@Error
GO

MySQL   版本的

CREATE PROCEDURE CustomerSelect()
Begin
Select CustomerId,FirstName, LastName from Customer ;
end

CREATE PROCEDURE test.`CustomerDelete`(in arg1 int)
begin
 DELETE
 FROM   Customer
 WHERE 
  CustomerId = arg1 ;
end;

 

存储过程的映射（前2个MSSQL 后2个MYSQL)

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <sql-query name="SelectCustomer">
    <return-scalar column="CustomerId" type="Int32" />
    <return-scalar column="FirstName" type="String" />
    <return-scalar column="LastName" type="String" />
    exec SelectCustomer 
  </sql-query>

  <sql-query name="CustomerDelete">
    exec CustomerDelete  :CustomerId
  </sql-query>

 

  <sql-query name="CustomerSelect">
    <return-scalar column="CustomerId" type="Int32" />
    <return-scalar column="FirstName" type="String" />
    <return-scalar column="LastName" type="String" />
    call CustomerSelect
  </sql-query>

  <sql-query name="DeleteCustomer">
    call CustomerDelete(?)
  </sql-query>
</hibernate-mapping>

BLL层调用方法

using System;
using System.Collections.Generic;
using System.Collections;

using System.Linq;
using System.Text;
using NHibernate;
using NHibernate.Cfg;
using Model;
using System.Web;
using NHibernate.Criterion;

namespace ConsoleNH
{
    public class CustomerProcBLL
    {

        private string path = @"D:\My Documents\Visual Studio 2008\Projects\ConsoleNH\ConsoleNH\hibernate.cfg.xml";

        // inner join 后面的orderer
        public IList SelectCustomer()
        {
            Configuration cfg = new Configuration().Configure(path);
            ISession session = cfg.BuildSessionFactory().OpenSession();
            return  session.GetNamedQuery("SelectCustomer").List();
        }

        public bool  CustomerDelete(int CustomerId)
        {
            Configuration cfg = new Configuration().Configure(path);
            ISession session = cfg.BuildSessionFactory().OpenSession();
       
            IList l =  session.GetNamedQuery("CustomerDelete").SetInt32("CustomerId", CustomerId).List();             
            //error
            //session.GetNamedQuery("CustomerDelete").SetInt32("CustomerId", CustomerId);           
 
            return true;
        }

 

        public IList CustomerSelect()
        {
            Configuration cfg = new Configuration().Configure(path);
            ISession session = cfg.BuildSessionFactory().OpenSession();
            return session.GetNamedQuery("CustomerSelect").List();
        }

 

        public bool DeleteCustomer(int CustomerId)
        {
            Configuration cfg = new Configuration().Configure(path);
            ISession session = cfg.BuildSessionFactory().OpenSession();

            IList l = session.GetNamedQuery("DeleteCustomer").SetInt32(0, CustomerId).List();
            //error
            //session.GetNamedQuery("CustomerDelete").SetInt32("CustomerId", CustomerId);           

            return true;
        }
       

    }
}

 

 

        static void Main(string[] args)
        {

            CustomerProcBLL cpb = new CustomerProcBLL();

            //IList ls = cpb.SelectCustomer();
            //bool fmssql = cpb.CustomerDelete(2);

            //IList ls1 = cpb.CustomerSelect();
            //bool fmysql = cpb.DeleteCustomer(3);
        }