My first few CSLA projects were all done with SQL Server and, for the most part, I used GUIDs and didn't have to work to get the object's DB-generated ID value at the end of the DataPortal_Update routine as a result.
I'm doing this with CSLA 1.5, but it really isn't a version-specific question. (I promise to get to 2.1 early next year but I can't endure the learning curve before year-end ... and by then I'll have the supplement to the book, right?).
In my old VB6, non-CSLA days, I would simply have set the recordset's .Bookmark property to .LastModified after the insert occurred, and read the value of the autonumber field quite easily.
Now I'm using an OleDb provider with ADO.NET in DataPortal_Update and am scratching my head about something that should be awfully easy, shouldn't it?
So what are you folks doing out there to get the ID value of an autonumber field after inserting a row for your editable root object into your JET table?
Thanks in advance.
I've actually read a fair amount of it already (although I haven't looked at it in about a month). Obviously I didn't get THAT far <g>. (My bookmark is in "Completing The Framework.")
So, since you brought up the fact that I would find it there, I went straight to my PDF copy of it and searched on JET and only got the one hit at the bottom of page 13 (actually pg 37 of the PDF, of course).
I see find section on page 446 where he talks about using an Identity Column in Sql Server. But I just can't locate anything about how to do it with JET.
Since you chose the words "directly covered in the new book" I'm sure you can provide a page reference can't you?
Q, nice to talk to you again, it's been a while :) I'm not sure if this applies with JET, but this is how you do it with SQL Server.
http://forums.lhotka.net/forums/thread/7038.aspx
Hope this helps...
Nice to hear from you, too, Guy.
I checked out the thread you referenced and I expect I'll look at it again early next year. But it seems quite specific to Sql2K5, at least with the quick scan I gave it.
I'm afraid the JET db engine doesn't support queries of nearly this kind of sophistication. I may have to try in a public Access newsgroup. (Groan <g>)
Or maybe I'll just make a REALLY ugly object to solve my problem. In case you have any other suggestions, I'll explain a bit more...
This is basically a Cash Receipt and Customer story. For my UI I only need the Receipt info to be editable and I show some Customer properties that don't get edited. I'm using separate objects for this and Receipt holds a property with the Customer's ID, of course.
I need to print some of the Customer properties on the printed receipt along with the data entered on the screen, but the information is in separate objects which is going to be a problem for the DevExpress xtraReport which only wants ONE .DataSource. (Sure, easy for you to say I should have thought a bit more at design time <g>)
So my options seem to be
(1) change Receipt structure to include a full-fledged property for the customer object. Then I just have a heirarchial datasource and xtraReport is hopefully going to understand it even though the child isn't a collection. (Haven't experimented with that as you can tell.)
(2) save the cash receipt, grab its db-generated ID and use it in a query (that joins the receipt and customer tables) to populate a fast read-only object to use for the report - which is, of course, why I'm asking my question here!! This would be a snap if I could do it AT ALL (since MyGeneration would do the bulk of the work)! <g>
(3) Make a really ugly Receipt object to replace the one I have that actually DOES hold the Customer properties so that I have a single object datasource for the report.
Options (1) and (3) wouldn't be so bad really as the DP code could certainly populate all the necessary fields of the Receipt table and, of course, ignore the customer data at Save time (but not at Fetch time). But I've actually put a fair amount of handcrafted code into this Receipt BO and don't relish generating a new object as per option 3 OR putting still more hand code into this one as per option 1. My oh my, how MyGeneration has changed my tolerance for code-based solutions! <g>
Maybe I'm missing another option that is either more elegant or less manual effort than I'm imagining here (or both!).
The Jet 4.0 OLE DB Provider supports "SELECT @@IDENTITY" queries with Jet 4.0-formatted databases. See the following article for more information:
http://support.microsoft.com/kb/232144/EN-US/
Gareth
Thanks for this Gareth.
Using the example in the article I added the following code after my Insert command fired (note, I'm still "With-ing" with the OleDbCommand object and that this is CSLA 1.5):
.
CommandType = CommandType.Text.
CommandText = "SELECT @@Identity".
Parameters.Clear() Dim sdr As New SafeDataReader(.ExecuteReader) If sdr.Read() ThenMe.CashReceiptID = sdr.GetInt32(0) sdr.Close() End If
And the proper value was assigned to my object's ID property (and, of course, it was subsequently Marked Old). This seems to have solved the issue for me quite nicely. I have a lingering doubt about how the engine knows which record's Identity I want, though. If the application got busy and two users created cash receipts at almost the same time, might they both get the same Identity (of the later insert)? Frankly, this is a LOW volume app in any event, so this is a small worry. But I think I'll feel a lot safer if I restrict this technique to such applications. And this is not burden since I would use SqlServer for anything with significant volume, in any case.
Thanks a ton!!
Kahn:
SELECT MAx(ID) FROM tblCustomer
The SELECT MAX(ID)... is a very dangerous proposition. It will cause the database engine to perform a table scan and this is a huge hit on performance. Of course, if performance is not an issue for you then I guess this is fine.
But wait a minute.... this will still not guarantee that the max value returned is _yours_. Food for thoughts...
"INSERT Into tblCustomers; SELECT @@Identity" is the better alternative.
This is my 1 + 1 = 2 cents here :)
By the way, while researching the question, I also came up with another article reference:
http://support.microsoft.com/default.aspx/kb/221931
But all the articles pretty much use ADO (sic; the com-based one) or even datasets to retrieve the value. By modifying the code in the article you suggested (which also used ADO, but with a recordset) to use a data reader with a .Net OleDb Command object, I didn't have to add a reference to my app for that other data access model. This seems a pretty significant advantage to the technique, in my opinion.
If it's dangerous in high volume environments, it won't be helpful for many. But for those who can live with it, it seems such an improvement over the need for either ADO or a .NET dataset object, that I just thought it was worth an addition post to call attention to it.
Thanks again for the help.
Q,
"I have a lingering doubt about how the engine knows which record's Identity I want, though."
I do not know about OLEDB but in SQL Server, the Connection is the top level of the scope and Scope_Identity is returned when you do not use parameterized queries. This was the issue that I had to do some research on last week so there is a thread out there with more info. Perhaps JET uses a similar technique and knows which value to return as long as the connection is still open.
Joe
Hi, Joe.
I'm having a hard time understanding how this information could be useful. The only time I'd ever be using a query that doesn't have a parameter would be when I'm either retrieving all the records from a source table (or tables) or I can hard code my WHERE clause because I'm building a list in a known-at-design-time context. Clearly I'm not interested in any identity information after retrieving multiple records.
And I'll always be using parameterized queries when I'm doing updates because I need them to pass the object values for the target field values, right? And this is, of course, the kind of query I've run when I do need that identity information.
So, if I understand your posting the "when you do not use parameterized queries" rules out the likelihood that I can depend on scope identity being maintained (assuming JET does it at all in first place, of course), right?
That said, it is certainly working for me in all my testing so far! And I sure am curious about why. I'm just terribly confused, I guess.
Copyright (c) Marimer LLC