RE: [nhusers] OUTPUT parameters in stored procedure.

[Pete Appleton]

I think it's very easy to implement.

 

I'm sure the developers would appreciate a patch :)

 

From: nhu...@googlegroups.com [mailto:nhu...@googlegroups.com] On Behalf Of sash....@gmail.com
Sent: 22 April 2013 20:09
To: nhu...@googlegroups.com
Subject: [nhusers] OUTPUT parameters in stored procedure.

 

I know nhibernate doesn't support output parameters? but I don't understand why.

Why I can't write something like this:

 

<class name="Document">
   ...
  <sql-insert>exec createDocument ?,? OUT,?,?</sql-insert>
  ...

</class>

 

I think it's very easy to implement.

 

Now, for data access code I'm using pure ADO.NET. And my "documents" table has "CreateDate" column, which is set by server, and returns to my application by output parameter of stored procedure.

I know I can set "CreateDate" property of my POCO class manually but i don't want do it because I can't accurately synchronize client clock to the clock of server.

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com.
To post to this group, send email to nhu...@googlegroups.com.
Visit this group at http://groups.google.com/group/nhusers?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Pete Appleton]

NHibernate.SqlCommand.SqlInsert is a good starting point for working on the insert logic; you might also want to consider NHibernate.SqlCommand.SqlCommandImpl.Bind() which has some of the parameter binding logic.

 

Is there some business logic in your stored procedure?  Implementing 'OUT' parameters seems like an awful lot of work for what looks like a very simple/standard requirement on the surface.

 

/Pete

 

From: nhu...@googlegroups.com [mailto:nhu...@googlegroups.com] On Behalf Of sash....@gmail.com
Sent: 24 April 2013 15:28
To: nhu...@googlegroups.com
Subject: [nhusers] Re: OUTPUT parameters in stored procedure.

 

I tried to understand the source-code, but it's too difficult to understand relationships. I do not have much time for that. But just for developers I think it's not a problem. We need to parse query string and look for all entry of "OUT" keywords and respectively configure the "DBParameter".

Maybe developers can tell me where in the code of entity saving used "sql-insert". And then I think I will do that "patch".

 

 

Понеділок, 22 квітня 2013 р. 22:08:42 UTC+3 користувач sash....@gmail.com написав:

[Gunnar Liljas]

"Nevertheless, I can use OUTPUT parameters and I don't understand why they are not implemented in Hibernate yet.

I ask developers, why???"

It's a fringe usage scenario, only relevant in legacy databases, where the developer can't modify the schema/procedures. NHibernate goes to great lengths to provide for legacy databases, but there are limits to what it can do, especially when it comes to stored procedures. Remember that NHibernate is not a data access library.

Don't get me wrong. It's great that you have contributed, but "why???" is not a good way to market the contribution.

/G

2013/4/28 <sash....@gmail.com>

So, today I've implemented it by myself. It tooks only 3 hours.

What have I done:

1. To "interface IBinder " (namespace NHibernate.Id.Insert) add one more method:

    void ExtractOutValues(IDbCommand cm);

2. Implement that method in "private class GeneratedIdentifierBinder : IBinder " (of "public abstract class AbstractEntityPersister ") (namespace NHibernate.Persister.Entity )

   

public virtual void ExtractOutValues(IDbCommand ps)
{
    entityPersister.ReDehydrate(entity, null, fields, notNull, entityPersister.propertyColumnInsertable, 0, ps, session);
}

Where  "ReDehydrate" is:

protected int ReDehydrate(object entity, object id, object[] fields, bool[] includeProperty, bool[][] includeColumns, int j, IDbCommand st, ISessionImplementor session)
        {
            for (int i = 0; i < entityMetamodel.PropertySpan; i++)
            {
                if (includeProperty[i] && IsPropertyOfTable(i, j))
                {
                    try
                    {
                        this.SetPropertyValue(entity, i, ((IDbDataParameter)st.Parameters[i]).Value, EntityMode.Poco);
                    }
                    catch (Exception ex)
                    {
                        throw new PropertyValueException("Error redehydrating property value for", EntityName, entityMetamodel.PropertyNames[i], ex);
                    }
                }
            }
            return 0;
        }


4. And finally modify method "public object PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder)" 

    (public abstract class AbstractReturningDelegate)(namespace NHibernate.Id.Insert)


after:
    
    IDbCommand insert = Prepare(insertSQL, session);

add:
    
    foreach (IDbDataParameter prm in insert.Parameters)
    {
        prm.Direction = ParameterDirection.InputOutput;

    }



and change this:
    
    binder.BindValues(insert);
    return ExecuteAndExtract(insert, session);

to this:

    object res;
    binder.BindValues(insert);
    res = ExecuteAndExtract(insert, session);
    binder.ExtractOutValues(insert);
    return res;

5. The End!

It works and return my output parameters.

But after tran.Commit() operation it executes dynamically generated "UPDATE" statement instead of my stored procedure. It thinks that it's dirty.

For now, I don't know yet how to fix it.

Nevertheless, I can use OUTPUT parameters and I don't understand why they are not implemented in Hibernate yet.

I ask developers, why???

[Pete Appleton]

The bit I'm struggling to understand is why you need to use a stored procedure for the insert operation - is there a lot of logic in this procedure that's hard to handle elsewhere? If that isn't the case then I think that your initial requirement (population of the CreateDate column by the DB server) can easily be met by just mapping the CreateDate property as 'generated' and applying a default to the column?

-----Original Message-----
From: nhu...@googlegroups.com on behalf of sash....@gmail.com
Sent: Mon 4/29/2013 8:26 AM
To: nhu...@googlegroups.com
Subject: [nhusers] Re: OUTPUT parameters in stored procedure.

OK

I asked "why???" because I really didn't understand why.

I have another idea how to retrieve what we want and don't use OUTPUT parameters.
What if to sql-insert add another attribute (list of properties). Example (mapping by code):

m.SqlInsert("exec dbo.document_insert ?,?,?,?,?", new[] { t.CreateDate, t.EditDate });

And at the end of "document_insert" write this:

SELECT SCOPE_IDENTITY, @CreateDate, @EditDate


I think it's elegant way of problem solving.

So, what will you say?


?????????, 22 ?????? 2013 ?. 22:08:42 UTC+3 ?????????? sash....@gmail.com ???????:

[Gunnar Liljas]

"You may ask - why it can not deal with the application itself? Because of Reverse-Engineering (every-one can see .net code by using ILSpy or Reflector tool). And begin to connect to the server using something such as "Management Studio" and do everything that pleases. And that's just the above-mentioned mechanism will not allow him to do so."

Fair enough. That's a perfectly fine reason to use stored procedures. However, the vast majority of OR-mappers are heavily geared towards solving the problem "how can I use a database to store and query my objects", not "how can I use objects to store and query things that are in my database". Even those OR-mappers who claim otherwise will usually be a bit fiddly when it comes to stored procedures, imposing things like naming restrictions, all-or-nothing (all CRUD operations must be either non-SP or SP) etc. NHibernate may not be the best of the bunch, but it's certainly one of the best.

In this particular case, the problem really isn't stored procedures, but instead being able to get a DB generated value back to NHibernate, and Pete's suggestion solves just that.

"And by the way: "sql server - stored procedures advantages and best advantage" [http://blog.sqlauthority.com/2007/04/13/sql-server-stored-procedures-advantages-and-best-advantage/]"

A good article, if it wasn't for the "main advantage" being "Execution Plan Retention and Reuse can only be achieved using Stored Procedure", which is just plain untrue, and it was untrue in 2007 as well.

/G

2013/4/29 <sash....@gmail.com>

"The bit I'm struggling to understand is why you need to use a stored procedure for the insert operation"

OK. Let me explain something.

1. My application is WinForm type.

2. It uses mssql-server built-in security mechanism. Everyone has the appropriate sql-server user. Each user assigning specific sql-server roles. Which in turn have the right to execute certain stored procedures.

3. In the stored procedure it is possible to examine additional security conditions (for example, possibility of the user to create(insert) document by additional condition) and to perform additional operation.

You may ask - why it can not deal with the application itself? Because of Reverse-Engineering (every-one can see .net code by using ILSpy or Reflector tool). And begin to connect to the server using something such as "Management Studio" and do everything that pleases. And that's just the above-mentioned mechanism will not allow him to do so.

And by the way: "sql server - stored procedures advantages and best advantage" [http://blog.sqlauthority.com/2007/04/13/sql-server-stored-procedures-advantages-and-best-advantage/]

If that isn't the case then I think that your initial requirement (population of the CreateDate column by the DB server) can easily be met by just mapping the CreateDate property as 'generated' and applying a default to the column? 

I knew nothing about this feature. But thanks any way.

So, am I do something wrong? How do you make security strength and performance applications?

Понеділок, 29 квітня 2013 р. 17:25:20 UTC+3 користувач PeteA написав:

[Gunnar Liljas]

"I don't know what are you talking about. BLToolkit has very good support of stored procedures. Without any restrictions."

I didn't mention BLToolkit, and it's very different from NHibernate and other OR-mappers. I do believe that it could be a good fit for you (or one of the micro-ORMs, like Dapper), if your main goal is to have cleaner data access.

Best regards

Gunnar

2013/4/30 <sash....@gmail.com>

Well, I just need to simplify my work. As i said before: for now, I'm using ADO.NET with DataTables, DataSets etc. I'm tired to go with it.

I would like to deal with objects and as you said: "how can I use objects to store and query things that are in my database"  - is maybe my choice.

Even those OR-mappers who claim otherwise will usually be a bit fiddly when it comes to stored procedures, imposing things like naming restrictions, all-or-nothing (all CRUD operations must be either non-SP or SP) etc.

I don't know what are you talking about. BLToolkit has very good support of stored procedures. Without any restrictions.

I liked NHibernate. I've read a tons of articles about it. I like it's very good extensibility. But I don't know how to do what I need. As I understand: NHibernate is very good for web applications but not a desktop (which has to deal with sp).

A good article, if it wasn't for the "main advantage" being "Execution Plan Retention and Reuse can only be achieved using Stored Procedure", which is just plain untrue, and it was untrue in 2007 as well.

Yes. I'm fully agree with you.

I hope, I will migrate to BLToolkit with less pain.

Вівторок, 30 квітня 2013 р. 01:39:12 UTC+3 користувач Gunnar Liljas написав: