Hi there!

I try to modify the CRUD properties of a BO. The BO has a primary key of type Guid (uniqueidentifier in SQL Server) which I set its default value on the BO_SetDefaultValues event. I have set the PrimaryKeyIsAutoIncremented to False. I have set the InsertUsingStoredProcedure to True and also defined a stored procedure name in the InsertStoredProcedureName property. I have set the UpdateConcurrencyType to OptimisticsTimestamp and also defined a column name in the RowVersionOrTimestamp column. I have defined this column as an output parameter in the insert stored procedure.

I add a new row with the BO.Add method, edit some data in the form and then call the Save method. The row is added ok in the data base, but the IsDirty flag remains True. I can see in the CurrentDataTable that the row added with the BO.Add member has still a RowState of DataViewRowState.Added after the Save. I guess this is why IsDirty is True. Is this supposed to happen, or am I doing something wrong?

Thank you for your time.

George Nentidis

In general I have been getting very strange behavior when using rowVersion or timestamp concurrency with stored procedures. For instance in the update stored procedure, even though I return the new version of the either the rowVersion or the timestamp column, I get a conflict message prompting me to select which changes to keep.

I have used the SFSUsers_Insert, Update, Delete stored procedures as a model.

I have also noticed using the SQL Profiler some parameters passed to the stored procedure not mentioned anywhere in the documentation. Parameters who's name is ending in _ORGK. I assume that those are the original values of the row for comparison reasons.

Are there any samples available of how to use stored procedures for the Insert, Update, Delete operation of a BO, using either RowVersion or Timestamp concurrency? I have the feeling that I'm missing something.

Thank you for your time.

George Nentidis

George,

I would try a couple of things.  First, there is some documentation that explains the CRUD Stored Procs that are expected by the BOs.  If you use the Database Deployment Toolkit (DDT) you can have it produce these procs for you.  Look at the help topic in the DDT titled "Table CRUD Stored Procedures."

Next, create a profile and a database through the DDT and allow it to create some procs for you.  You can then look at these procs once they are deployed to a server and compare them to what you are producing.  This may give you a clue pretty quick once you see what we produce and use.

I have just found this and tried these options in the DDT. When I try to import my database in the DDT the primary key which is uniquieidentifier is imported as an Int type. I have to change it manually to Uniqueidentifier. When deploying back to the DB I get an exception saying that it is not allowed to modify the IsClustered property of the index.

As for the stored procedures, the DDT failes to create them during deployment of the package with an exception of "Operand type clash: int is incompatible with uniqueidentifier". My guess is that the error has to do with the primary key which is not an Int but a uniqueidentifier.

Those uniqueidentifiers don't seem to cooperate at all... Any suggestions?

Thank you for your response.

Ok, panic!!!

I have spend the whole day and didn't manage to save a single object correctly, have seen lots of exceptions, and still don't know what to do. Things should really not be that difficult, and there are some things that don't seem to work correctly. I could really use some help.

I have attached a RAR file with the stored procedured I have created, the screen shot of the conflict window, and a text file which contains a portion of the trace file from SQL Server Profiler.

Here are the issues I have gathered.

1. The DDT generates stored procedures that are just like those I have created with one exception: The update auto-generated procedure from the DDT, does not contain the @contactID_ORGPK parameter that the framework adds to the call. I found about this parameter by using SQL Server Profiler. There is no such column in my BO, and my guess is that this parameter contains the original value of the primary key for comparisson. Still it is not generated by the DDT nor is mentioned in the documentation. I have also compared my stored procedures with the ones described in the documentation you mentioned in your previous post.

2. After inserting a new row by a) calling the BO.Add b) modify the values c) BO.Save, my insert stored procedure is called correctly, the row is saved on the database, but the IsDirty flag of the BO is still true because the CurrentDataTable still has the new row in the DataViewRowState.Added state. Of course this has as a result, when trying to close the form for the "Save Changes dialog" to show.

3.1 After editing a row and calling BO.Save, my update stored procedure is called, but a conflict window is displayed, even though there is no conflict. You can see a screen-shot of the window in the attached RAR file. If I click the Save button on the window, again the same window is displayed with the values of the Version column increased by one. The same thing goes on and on. The same happens for both the RowVersion and Timestamp cases.

3.2 Each time my update stored procedure is called, the framework makes another SELECT statement immediatelly after the call to my stored procedure. You can see the SELECT statement in the "SQL Profiler Trace.txt" file in the attached RAR file. Is this correct? What's the point of specifying a stored procedure if another SELECT statement will follow?

4. This is not very important for me right now, but when importing in the DDT my table which contains a primary key of type UniqueIdentifier, it is imported actually as a type of Int.

I really hope I'm doing something wrong. I am about to give up the whole stored procedures idea, but still...

Thank you for your time,

George Nentidis

End of panic... It's a nice thing to provide the source code of the framework. Not the easiest way to find a solution but better than nothing.

1. The _ORGPK parameters are added if the PrimaryKeyIsAutoIncrement is False. It is used only in the update stored procedure. More such parameters are added if the concurrecy is set to OptimisticAllFields.

2, 3. The whole problem was caused because of the SET NOCOUNT ON statement existed in my stored procedures. Unfortunatelly if you create a stored procedure using the SQL 2005 Server Management Studio, this line is automatically added. The framework could not get the number of rows affected and all those bad things happened.

4. Is still true.

Perhaps you should add something about all these in the documentation or even better to provider a sample of using the BOs with stored procedures.

Thanks once again,

George Nentidis

1) I'm glad you got that working

2, 3) Yep, when you use the SQL Server Management Studio 2005 template to create a new sproc, it does add that SET NOCOUNT ON.  Supposedly, it's to help you improve performance because it tells SQL Server that it doesn't have to keep track of the number of records that are modified.  If you're not creating a CRUD stored procedure, then this is great... there's no need to keep track of modified records, but if you're modifying records, then yes, you will need to remove that call from the sproc template before saving your sproc.

4) I will add a bug for that.  Thanks.

4) In the meantime, the workaround would be to import your database and change the primary keys back to UniqueIdentifier BEFORE you deploy the database through the DDT.  Once a PK column is created, it must be dropped and recreated to change it.

Continuing the above case I came accross another issue:

When starting using transactions with my save, I have decided to test the RollBack transaction case. My code looks exactly like the one in the documentation, with an addition of the throw statement:

   try {
        BusinessLayer.TransactionBegin("", IsolationLevel.ReadUncommitted);
        mContactBO.Save(true);
        throw new ArgumentException();
        BusinessLayer.TransactionCommit("");
        }
    catch(Exception x) {
        Debug.WriteLine(x.Message);
        BusinessLayer.TransactionRollback("");
        }

The transaction rolls back fine, nothing is changed in the DB. When I try to save the changes again, the Save method of the BO gets into an infinite loop where the framework keeps calling my update stored procedure, and then a select statement. This keeps going on and on until it crushes with a StackOverflow exception. The update stored procedure is exactly the same with the one DDT generates.

Any ideas?

I am new to this framework, and I try to do exactly what the documentation says, but I keep getting into all those issues. The documentation does not help a lot and I keep falling behind of schedule.

Can anyone help me with this?

Thank you

Also while working with transactions, I've seen that when saving a child object, this will also save its parent. But since in transactions the IsDirty flag is not touched, the parent object will be saved twice. For instance the following code:

    try {
        BusinessLayer.TransactionBegin("", IsolationLevel.ReadUncommitted);
        mContactBO.Save(true);
        mContactAddressesBO.Save(true);
        BusinessLayer.TransactionCommit("");
        }
    catch(Exception x) {
        BusinessLayer.TransactionRollback("");
        }

will actually call the update or insert stored procedure of the mContactBO twice. Which might cause problems in the case of insertion.