NHibernate Tour (16): Explore the use of stored procedures in nhibernate (medium)

The contents of this section

Introduced

Example analysis

2. Creating objects

3. Update objects

Conclusion

Introduced

In the previous article, how to use the template provided by Mygeneration to create stored procedures and delete the use of object stored procedures, this next describes how to use stored procedures in NHibernate to create objects, update the entire detailed process of the object, these are all in the practical use of accumulated experience, Related to the use of error information, how to modify the stored procedures, and compare the use of the different points of the stored procedures, and unofficial comparison of authoritative information, so please refer to this article, if you have not come and fancy a, then hurry to see it.

Example analysis 2. Creating objects

STEP1: To compare, we first execute the createcustomertest () test method, not using the stored procedure to create the object to generate SQL statements as follows:

INSERT INTO Customer (Version, Firstname, Lastname) VALUES (@p0, @p1, @p2);
select SCOPE_IDENTITY(); @p0 = '1', @p1 = 'YJing', @p2 = 'Lee'

STEP2: Modify mapping file Add stored procedure, open Customer.hbm.xml mapping file, add <sql-insert> node under class element, call Customerinsert stored procedure, Customerinsert The stored procedure has four parameters, which are represented here with four question marks:

<sql-insert>exec CustomerInsert ?,?,?,?</sql-insert>

STEP3: Execute createcustomertest () test method with Error "NHibernate.Exceptions.GenericADOException:could not insert: [ Domainmodel.entities.customer][sql:exec Customerinsert?,?,?,?] ; System.Data.SqlClient.SqlException: Parameterized query ' (@p0 int, @p1 nvarchar (3), @p2 nvarchar (7), @p3 int) exec customerin ' Required parameters ' @ P3 ', but not supplied with this parameter ', this should be a parameter problem, take a closer look at the original stored procedure, there are some problems with the parameter location.

STEP4: Modify the Customerinsert stored procedure, remove set NOCOUNT on and move the parameter position, the code snippet is as follows:

ALTER PROCEDURE [dbo].[CustomerInsert]
(
　　@Version int,
　　@Firstname nvarchar(50) = NULL,
　　@Lastname nvarchar(50) = NULL,
　　@CustomerId int = NULL OUTPUT
)
AS
　　INSERT INTO [Customer]
　　(
　　　　[Version],
　　　　[Firstname],
　　　　[Lastname]
　　)
　　VALUES
　　(
　　　　@Version,
　　　　@Firstname,
　　　　@Lastname
　　)
　　SELECT @CustomerId = SCOPE_IDENTITY();
　　RETURN @@Error