Hi
A suggestion for those it may have interest.
Almost all of my classes use an integer identity value as primary key. I’ve used Rocky’s suggestion of how to generate a temporary id (see this article for more details: http://www.lhotka.net/Article.aspx?area=4&id=252e4f20-f202-4ba5-b6ff-4d629a2b7dcc)
However, it was a bit daunting to do this for every class, so I subclassed BusinessBase in a new IdBusinessBase, which looks like this:
<Serializable()> _
Public MustInherit Class IdBusinessBase(Of T As IdBusinessBase(Of T))
Inherits BusinessBase(Of T)
#Region " Temporary Integer Id "
'Shared temporary id for new objects
Private Shared _lastTempId As Integer
''' <summary>
''' Returns a unique temporary integer id for an object of type T.
''' </summary>
''' <returns></returns>
''' <remarks>
''' Use this method to get a temporary integer id for a new object, if the object uses an
''' identity field as it's primary key in the database.
''' The temporary id is shared between all classes of type T and is a continuous
''' negative number starting from -1.
''' </remarks>
Protected Function GetTemporaryId() As Integer
SyncLock Me
_lastTempId -= 1
End SyncLock
Return _lastTempId
End Function
#End Region
End Class
I the constructor of your BO you assign your PK field with the return value of this method:
Private Sub New()
_primaryKey = MyBase.GetTemporaryId()
End Sub
/Henrik
This is one of the first things I
added to my heavily modified version of the CSLA.NET 1.0 framework when it
first came out. It works out quite well;
however, there is a chance that you could get a duplicate key when you mix objects
that have a temp key with objects that have real keys. I have found that this does not happen very
often though. You might want to check
the value so it doesn't fall out of the range for integers. Compare it with
Integer.MinValue. If it falls out of
range reset the id to -1.
Thanks for your input malloc.
I hadn't thought of the id could get out of range. I'm so used to web apps where the webserver recycles the process every now and then (and thereby resetting the id back to -1). But of course in long running apps, this could happen. I'll add the check for Integer.MinValue to my code.
/Henrik
Henrik:
Protected Function GetTemporaryId() As Integer
SyncLock Me
_lastTempId -= 1
End SyncLock
Return _lastTempId
End Function
Yep, you're right. I overlooked that the return statement should be inside the SyncLock block.
I'm no champ in multi-threading and thread-safety and therefore I may sometimes overuse locking. However most of my applications have two front-ends, a winform and a web. When the code runs, without synclock, in a winform, there shouldn't be any problems, since these are normally single-threaded apps (or I'm at least aware of when I'm using multithreading). But the web app, running in IIS, will be multithreaded due to simultaneous requests, won't it?
Is SyncLock the fastest way to have thread-safety or are there other methods that are faster. I recall, from my software engineering classes, there are mutex, semaphores and monitors but I haven't really dug into the .NET framework to see if they are there (they probably are) or what their performance is like.
/Henrik
I looked up some of the System.Threading stuff in the .net framework help and found out that a SyncLock is actually a monitor behind the scenes. Futhermore a mutex is actually designed more for process synchronization than thread-sync.
I also found the Semaphore class, but this seems to be the slowest of all since the programmer needs to call Release for each WaitOne call, which would eventually require a Try-Finally block, which must be slower than the SyncLock mechanism.
/Henrik
![Wink [;)]](/emoticons/emotion-5.gif)
Sample code for an Insert:
DAL.ExecuteNonQuery(tr, CommandType.Text, DAO.Insert(mDescr, mName, ...
))Joe
Yes - I am sure it works.
The tr parameter is the transaction.
Joe
Yes - I am sure it works.
The tr parameter is the transaction.
Joe
I'm not so sure about that Joe. I had a similar problem a few weeks ago and both command were using the same db transaction and it did not work.
Guy,
Thanks for the response - and the heart attack. <g>
I was sure until I read your response. Now I have some doubt.
How do you know the tr is not carried forward and returns the correct answer?
After all, it has not been committed yet.
I could easily change it to the code sample you showed IF that "other" database (Oracle) was not involved in my platform. I would also need to know how to do it for Oracle. And to my knowledge Oracle does not support 2 statements in one "batch".
Looks like some research is required.
Thanks.
Joe
IF that "other" database (Oracle) was not involved in my platform.
You mean Obstacle 10g, right. LOL
http://www.cafepress.com/oraclehaters.15080672
Enjoy
Yes - "I hate Oracle." Trying very hard to drop support for it. Need to get a couple of clients to wise up first though.
I have been reading about Scope_Identity - most people think it is easiest to use the 2 statements in a batch. But there was one question like this:
===============================================
If I do the following:
- Connect to a SQL Server 2000 database (using SqlClient objects)
- Start a transaction
- INSERT a new row
- Run “SELECT Scope_Identity”
will I get a value specific to my transaction?
===============================================
Answer:
Specific to your transaction, otherwise it wouldn't be much use.
===============================================
I think I will post some sample code over in the SQL Server group and see what they have to say.
My code has worked correctly for a long time now - but you have me nervous that it won't always work correctly. Say under load or something.
Joe
===============================================
Some sample code showing 2 separate queries to get the Identity value: (the Insert ran in a different method but the connection was passed to this method - like I pass my tr.)
/// <summary>
/// This is called when a row is updated, and it retrieves the latest identity
/// value. Note that it has to use the same connection which I can get from args
/// Also note that I try both the sqlserver2000 and alternative just as an example.
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args) {
if (args.StatementType == StatementType.Insert) {
SqlConnection con = args.Command.Connection;
Object new_id = 0;
// sqlserver 2000 version
SqlCommand id_cmd = new SqlCommand("select scope_identity() as id", con);
new_id = id_cmd.ExecuteScalar();
Joe, take a look at this thread, which was one of my original thread :)
http://forums.lhotka.net/forums/thread/7101.aspx
You'll see that the tests I did a while back showed different results when using hard text sql command versus parametrized queries.
If you're using hard text sql commands then you are fine. However, if you're using parametrized queries YOU MUST include the scope_Identity inside the same call.
Keep in mind though that parametrized queries are _faster_ than hard text sql command because they will get compiled and cached - so it will be faster going forward on subsequent calls.
I am using hard text SQL queries, not parameterized queries.
I am aware of the risks/differences - except for the whole scope identity thing. <g>
Whew. Thanks.
Sorry about the clogged arteries I gave you; glad to see you’re out of the intensive care unit though :)
Best regards,
I found the reason for the issue with parameterized queries.
Answer posted here:
http://forums.lhotka.net/forums/thread/7101.aspx
Joe
This topic has deviated from the original issue, but I have a different solution to the temporary ID problem. I let the collection class assign temporary IDs.
I have a private int in the collection:
private
int _nextTempID = 0;I have one method in the collection used to add items to the collection and it has:
componentRec.SubmitListID = _nextTempID++;
this.Add(componentRec);And in the collection's dataportal_fetch I set the _nextTempID so that when I load existing data, I won't be assigning a tempID value that matches a real id:
while (xxx.Read()){
ComponentList info = new ComponentList(xxx); this.Add(info); if (info.SubmitListID > maxID){
_nextTempID = info.SubmitListID + 1;
}
Does anyone perusing this thread see a problem with this design? I did have to scope the "setter" of my unique SubmitListID of my component to be internal instead of private, but that seems a minor trade for the simplicity.
For it work and to be executed in the same transaction scope, both commands (the insert and the scope identity) must be sent all at once.
mKey = CInt(DAL.ExecuteScalar(tr, CommandType.Text, "<<Insert command here>> ; SELECT Scope_Identity()"))
Notice the semi-colon ';' between both commands. Doing it this way will work, this is how I have it setup. If they are sent in 2 distinctive calls to the database, they will NOT run in the scope.
Copyright (c) Marimer LLC