StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Implementing the Next ID Function

Implementing the Next ID Function

Bill Cunnien — Tue, 08 Jan 2008 13:59:10 GMT

When implementing the next ID for a non-primary key field, I would like to retrieve that incremental number during the save rather than at the SetDefaultValues (see thread). Here is what I did in my CustomersBO:

public override MicroFour.StrataFrame.Data.SaveUndoResult Save()
{
this.CustID = NextIDBO.GetNextID("CM");
return base.Save();
}

Is that right?

Then, within the NextIDBO, I am attempting to implement the function that will return the next ID based on the ID type passed in the argument. I am running into a syntax hurdle. For some reason, a public method in my NextIDBO cannot be seen by the CustomersBO. The function looks like this (full implementation code stripped for simplicity):

public int GetNextID(string pRecordType)
{
int mNextID = 0;
return mNextID;
}

Within this function I would run the appropriate stored procedure that returns a scalar value representing the next ID for the new record. Perhaps I have not had enough coffee this morning. What am I missing in the BOs so that the one can see the public method of the other?

Thanks,
Bill

RE: Implementing the Next ID Function

Bill Cunnien — Tue, 08 Jan 2008 13:59:10 GMT

'Twas an easy fix. The custid field has a default value of 0, so I just checked to see if the value was zero. If true, then grab the next ID. Whew! Major crisis quickly melts into a very minor code change.

RE: Implementing the Next ID Function

Bill Cunnien — Tue, 08 Jan 2008 13:47:46 GMT

[quote][b]Bill Cunnien (12/28/2007)[/b][hr]
private void CustomersBO_BeforeSave(MicroFour.StrataFrame.Data.BeforeSaveUndoEventArgs e)
{
NextIDBO mNextIDBO = new NextIDBO();
this.CustID = mNextIDBO.GetNextID("CM");
}
[/quote]

:crying:

Alas! This works even when editing an old customer record, too!! Very scary! I was in a demo to my manager when this reared its ugly head. How do I check to make sure that I am actually in Add mode as opposed to Edit mode while in the BO?

Thanks,
Bill

RE: Implementing the Next ID Function

Bill Cunnien — Fri, 28 Dec 2007 16:03:49 GMT

Just to wrap this up and put a nice bow on it, I found that this approach to the GetNextID function worked best:

public int GetNextID(string pRecordType)
{
    SqlCommand mSQL = new SqlCommand();
    mSQL.CommandType = CommandType.StoredProcedure;
    mSQL.CommandText = "spx_GetNextID";
    mSQL.Parameters.Add("@pRecordType", SqlDbType.Char);
    mSQL.Parameters[0].Value = pRecordType;
    int mNextID = (int)ExecuteScalar(mSQL);
    return mNextID;
}

The save function on the customer works like a charm, now!! :w00t::w00t::w00t: This is quite a simple approach to assigning an ID that auto-increments, yet is not a PK. In addition, since I am assigning the ID immediately before the save (rather than on the Add()), the numbers aren't being gobbled up without cause. I know one auditor who is going to like this.

Wonderful framework, gents! Excellent work. It really has saved me a ton of hours. I have actually converted months into days using this framework.

Happy New Year!
Bill

RE: Implementing the Next ID Function

Bill Cunnien — Fri, 28 Dec 2007 15:52:17 GMT

This is the method to run:

private void CustomersBO_BeforeSave(MicroFour.StrataFrame.Data.BeforeSaveUndoEventArgs e)
{
NextIDBO mNextIDBO = new NextIDBO();
this.CustID = mNextIDBO.GetNextID("CM");
}

Now, I just need to get my ExecuteScalar syntax correct.

RE: Implementing the Next ID Function

Bill Cunnien — Fri, 28 Dec 2007 15:20:58 GMT

In my CustomersBO I have the following:

public override MicroFour.StrataFrame.Data.SaveUndoResult Save()
{
    NextIDBO mNextIDBO = new NextIDBO();
    this.CustID = mNextIDBO.GetNextID("CM");
    return base.Save();
}

The NextIDBO derives from my NextID table that stores the numbering for various entities. The GetNextID function executes a scalar command that fires off a stored procedure that both grabs the appropriate value and then updates the NextID table. The function returns this value to the CustID property as the new ID immediately before saving.

This is not working. The Save() override never fires. Any thoughts?

Thanks,
Bill

RE: Implementing the Next ID Function

Bill Cunnien — Thu, 20 Dec 2007 15:40:51 GMT

[quote][b]Trent L. Taylor (12/20/2007)[/b][hr]In this case DO NOT use the AutoValues table and call a scalar method to get the MAX value of the PK (or whatever field that you need) and add one. This way you NEVER have to use a transaction and you will always get a sequential number.[/quote]

I will be testing this save routine shortly. I cannot use my PK fields (identity fields...by policy, not to be used for the record ID field). The MAX of a specific ID field is a good solution, but doesn't quite solve all of my business policy issues. It is possible for users to reset the numbering of a specific ID field. I know, that sounds strange, but from a development standpoint, I don't care what they put in that field as long as it is unique (which I can trap). I am simply providing a simple service by clicking the number up for them automatically. For that reason, I will still need the NextID table to help me out. If I work out the timing (set the number on save rather than on add), then transactions won't be necessary (I hope). It would just be nice, I suppose.

Thanks a lot for the feedback, Trent! Much appreciated!
Bill

RE: Implementing the Next ID Function

Trent L. Taylor — Thu, 20 Dec 2007 14:35:41 GMT

[quote]When a user clicks 'Add', is a new record created in the database?[/quote]

No. Only in the BO. Anything that you do relating to .NET is going to be in a disconnected environment.

[quote]Or, is the new record actually created when the 'Save' button is clicked? [/quote]

Yes, when the Save() of the BO is called is when the records are actually committed to the server.

[quote]however, there are several cases in which our auditors expect numeric integrity (no gaps, consistent, etc.). [/quote]

That's fine. In this case DO NOT use the AutoValues table and call a scalar method to get the MAX value of the PK (or whatever field that you need) and add one. This way you NEVER have to use a transaction and you will always get a sequential number.

RE: Implementing the Next ID Function

Bill Cunnien — Thu, 20 Dec 2007 14:09:46 GMT

Perhaps I need a bit of clarity. When a user clicks 'Add', is a new record created in the database? Or, is the new record actually created when the 'Save' button is clicked?

Also, we may just have a philosophical difference regarding these auto-incremental numbering. In some cases, the number really does not matter (packing lists, work orders, etc.); however, there are several cases in which our auditors expect numeric integrity (no gaps, consistent, etc.). For example, PO numbers, invoice numbers, sales order numbers, and so on. So, in order to lessen the opportunity of burning numbers, I thought it would be best to assign the number at the moment of saving the record. I can do away with the transaction part of things. No biggee. But, I will need to do what I can to preserve the numbering integrity.

I think I have the business object setup similar to what you have. I am calling the GetNextID(string pRecordType) from the Save method on the form. The business object for the NextID table will run the stored procedure to return the next value for that type and up the increment by one. Just before the call to save, I set the ID field of the business object to the ID returned from the NextID table. In theory, this should work fine, alas! it does not. I am sure that I am missing something quite simple. :unsure:

Thanks,
Bill

RE: Implementing the Next ID Function

Trent L. Taylor — Thu, 20 Dec 2007 09:50:31 GMT

Bill,

I think that you may be making this more difficult that you need to. We have the same need in our medical software to have fields auto-incremented on fields that are not the PK. We created an AutoValues table and then created a shared class to wrap this so that we do not have to ever talk to the BO directly when assigning a value.

First of all, there is not any reason to put an autovalues table on a transaction. This is just going to hand out a new ID (just like the Auto-Incrementing fields in SQL) to the table in which is requesting it. If the record of the table that received the value does not commit for whatever reason, that is OK and that value will just never be used again.

First, create your AutoValues table. We use the same table for all of our auto-incrementing needs aside from the PK field.

Sample Autovalues Table

av_pk - int - (Enum of the Type of Record)
av_NextValue - int - (The next value that will be served up)

Sample AutoValues Class (Pseudo code)

Public Enum AutoValueType As Integer
CustomerCode = 0
PartsCode = 1
End Enum

Public Class AutoValues

Private Shared Function GetNextAutoValue(valueType As AutoValueType) As Integer
    '-- Create your scalar query method here as well as your update call to update the value.
    '    You may have to create an INSERT as well if the record does not exist.
End Function

'-- Returns the next customer code
Public Shared Function GetNextCustomerCode() As Integer
    Return GetNextAutoValue(AutoValueType.CustomerCode)
End Function

End Class

Implementation within the BO

Private Sub SetDefaultValues() Handles Me.SetDefaultValues
Me.cs_Code = AutoValues.GetNextCustomerCode()
End Sub

This turns everything into an object model access versus trying to re-code this everytime that you want to get another value. Also it creates a single entry point.

RE: Implementing the Next ID Function

Bill Cunnien — Wed, 19 Dec 2007 16:00:57 GMT

I finally got back around to testing this. It does not seem to work...at least, not from the transactional override. I will try to override simply the Save method and see what happens.

RE: Implementing the Next ID Function

Bill Cunnien — Wed, 19 Dec 2007 09:20:05 GMT

The extra cup of coffee helped. :w00t:

public override MicroFour.StrataFrame.Data.SaveUndoResult Save(bool Transactional)
{
    NextIDBO mNextIDBO = new NextIDBO();
    this.CustID = mNextIDBO.GetNextID("CM");
    return base.Save(Transactional);
}

If there is a sproc involved in the retrieval of the ID, and I am wrapping the save in a transaction, if the save fails will the rollback involve the NextIDBO sproc call, too? I assume that it will, but I'd like to be sure.

Also, after playing with the syntax of the NextIDBO, I came up with this:

public int GetNextID(string pRecordType)
{
int mNextID = (int)ExecuteScalar("spx_GetNextID('" + pRecordType + "')");
return mNextID;
}

I really thought there would be more to it. Naturally, the spx_GetNextID references the sproc to retrieve and update the ID number. Is this really all I have to do to make this incremental non-primary key field to update (and any others in the app, too)? If so, I am blown away.