StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » InsertUsingStoredProcedure = True, leaves BO dirty

InsertUsingStoredProcedure = True, leaves BO dirty

Leonard P. — Wed, 03 Sep 2008 10:35:12 GMT

Hello,

I have a maintenance form. All data operations (update/insert/delete) used to work great, until I decided to handle inserts through a stored procedure.

Well, it's still kind of works, but after new row is added and [b][u]Save[/u][/b] is clicked, BO state remains dirty no matter what. So when form is closing "Would you like to save changes" dialog is displayed, even though user did click save before (many times).

Another thing I am noticing is that BO always sees new row as "New" even after Save is clicked and changes committed to the db, so if user clicks save multiple times it keeps adding the same record over and over again.

Please help. Thank you.

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Edhy Rijo — Wed, 03 Sep 2008 10:35:12 GMT

[quote][b]Marcia G Akins (09/03/2008)[/b][hr]rof,l > and < lol >!!!! With Andy's involvement in this project as DBA, you can well imagine that his reastion would be "over my dead body!". When we were watching things in SQL Profiler, he was very upset at seeing the framework make direct calls to SP_ExecuteSQL.
[/quote]

Hi Marcia,

Like in everything else, there are many ways to skin the cat, and once you get to know the tools available for SF (BOM, DDT, RBS) and use them then you will see the light at the end of the tunnel :P These tools are design with automation in mind, so the application can take care of itself automatically or with your assistance but being easy on your part. Please don't show this post to Andy :hehe:

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Marcia G Akins — Wed, 03 Sep 2008 09:50:29 GMT

Hi Charles.

>> Don't know if you looked at the DDT very much, but the SMO approach to schema changes - and the ability to easily add that into the app ala Stonefield update is pretty impressive. <<

< rof,l > and < lol >!!!! With Andy's involvement in this project as DBA, you can well imagine that his reastion would be "over my dead body!". When we were watching things in SQL Profiler, he was very upset at seeing the framework make direct calls to SP_ExecuteSQL.

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Charles R Hankey — Wed, 03 Sep 2008 09:45:40 GMT

I think here you'll find the biggest surprises are in the area of "Oh my god, they are already handling that" (e.g. bubbling up the sp errors, presenting a wizard to handle connection if one is not already established etc.) Wait till you see the message service and localization stuff. Roll based security very cool as well. Don't know if you looked at the DDT very much, but the SMO approach to schema changes - and the ability to easily add that into the app ala Stonefield update is pretty impressive.

I hope after a year of using this you come to the training class next year (and bring Andy) I think you'll get a real kick out of Trent and Ben and the rest of the crew. (would really enjoy hearing Andy and Ben get into SQL esoterica :) )

Anyway, I look forward to your insights and contributions here as I know you will bring a lot of experience and general common sense to using the product.

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Marcia G Akins — Wed, 03 Sep 2008 08:39:50 GMT

Hi Charles.

Well, having written my own VFP framework, I understand all too well that all frameworks make assumptions and in order to use the framework effectively, one has to understand what those assumptions are :D That is the learning curve and it can't be avoided.

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Charles R Hankey — Wed, 03 Sep 2008 08:26:54 GMT

I for one am very glad to see you here. I think we all agree good frameworks only get better when knowledgable, experienced and ... intense :D users bang on it and ask questions.

As I said before, I think the more you get to know the framework - and especially the Microfour folks - the more you are going to be delighted.

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Marcia G Akins — Tue, 02 Sep 2008 14:18:56 GMT

Hi Dustin.

>> As mentioned in the previous post, I'll include a note in the pertinent topics regarding SET NOCOUNT ON for the next release. Sorry for the confusion! <<

No worries :) I really am not rying to beat you up on this one. All in all, I am happy with my choice to go with Strataframe. It's a big product, so I expect to get a few bruises on the learning curve :)

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Dustin Taylor — Tue, 02 Sep 2008 14:14:25 GMT

Marcia,

As mentioned in the previous post, I'll include a note in the pertinent topics regarding SET NOCOUNT ON for the next release. Sorry for the confusion! :)

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Marcia G Akins — Mon, 01 Sep 2008 15:31:41 GMT

Hello Stataframe team Member:

>> you can't use the SET NOCOUNT ON command at the top of your sproc (StrataFrame uses the count to determine the number of rows affected.) <<

It would be awfully nice if this were actually in the Stataframe documentation. Since my husband is a SQL Server DBA, he writes all of my stored procedures. I just happened to find this accidentally after a day of pulling my hair out for no reason.

Since best practice in SQL Server is to SET NOCOUNT ON at the beginning of every stored procedure to improve performance and to use @@RowCount to return the row count, my husband does not understand why the framework relies on NOCOUNT being off.

Marcia

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Dustin Taylor — Fri, 22 Aug 2008 09:22:56 GMT

No problem, glad you got it working :)

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Leonard P. — Fri, 22 Aug 2008 09:12:14 GMT

Dustin, It was mine SP. SET NOCOUNT ON was the probelm.Thank you

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Dustin Taylor — Fri, 22 Aug 2008 08:39:18 GMT

Did you write these sprocs yourself or let the DDT generate them? If you wrote them yourself, you [i]can't[/i] use the SET NOCOUNT ON command at the top of your sproc (StrataFrame uses the count to determine the number of rows affected.)

RE: InsertUsingStoredProcedure = True, leaves BO dirty

Leonard P. — Fri, 22 Aug 2008 08:29:44 GMT

As a temporary work around I added code to _AfterSave even to accept changes on the current row:

[codesnippet]

private void customReport_AfterSave(MicroFour.StrataFrame.Data.AfterSaveUndoEventArgs e)
        {
            foreach (DataRow row in customReport.CurrentDataTable.Rows)
            {
                if (row.RowState == DataRowState.Added)
                {
                    row.AcceptChanges();
                }
            }

}

[/codesnippet]

I hope to find better way of handeling it withing the Framework.