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

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

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.

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.

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

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

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.

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

    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.

Looks like a bug... I'm not sure we have a unit test to cover this scenario, so I'll have to step through it and verify. 

Yes, this is a known issue and will be fixed within the next official release.

Since I had a backup of your database, I restored it and then started testing the DDT import.  The reason all of your data types were incorrectly imported was due to the user-defined data types, which the DDT doesn't support. So I changed the import to test for this and instead of returning an Integer as the default, it actually extracts the underlying data type from the User-Defined definition so that the type actually comes into the DDT correctly.  This will be included in the post I put out there at the end of the day.