-
Jul 29th, 2005, 06:53 AM
#1
Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
If you have a datatable, and lets say this has a field UserID, which is an Identity column in the DB, then when you update this using a dataadapter the new UserID value is not inserted into the rows in the datatable.
For updating a datatable you can simpley use:
VB Code:
Public Sub Update(ByVal SQL As String, ByRef Data As DataTable)
Dim Comm As New SqlCommand(SQL, Connection)
Dim da As New SqlDataAdapter(Comm)
Dim db As New SqlCommandBuilder(da)
da.Update(Data)
CloseConnection()
End Sub
Where SQL is like:
Code:
SELECT UserID, Username, Password FROM Users
and where Connection is a function to get and open a connection to the SQL Server DB.
Using the above function it's quite easy to update, insert and delete rows as the SQLCommandBuilder does most of the hard work for you and creates the Update, insert and delete command objects.
This however is not good enough, as I want to see the userID field values. No idea why ADO.NET doesn't do this automatically for you.
What I decided to do is to scrap the SQL param.
I can manually build up the Update and Insert statements by looping through the columns in the datatable.
While looping, I check for an identity column. If there is one then:
Code:
SELECT @@SCOPE_IDENTITY AS UserID
Is tagged to the end of the insert statement.
I modified the above code so that it is like:
VB Code:
Public Sub Update(ByRef Data As DataTable)
Dim da As New SqlDataAdapter
da.InsertCommand = BuildInsertCommand(Data)
da.UpdateCommand = BuildUpdateCommand(Data)
da.Update(Data)
CloseConnection()
End Sub
Private Function BuildUpdateCommand(ByRef Data As DataTable) As SqlCommand
Dim NewCmd As New SqlCommand
NewCmd.Connection = Connection
Dim SetString As String
Dim Identity As String
For Each Col As DataColumn In Data.Columns
If Col.AutoIncrement Then
Identity = Col.ColumnName & " = @" & Col.ColumnName
Else
If Not (SetString = String.Empty) Then
SetString &= ", "
End If
SetString &= Col.ColumnName & " = @" & Col.ColumnName
End If
Dim Param As New SqlParameter
Param.ParameterName = "@" & Col.ColumnName
Param.SourceColumn = Col.ColumnName
NewCmd.Parameters.Add(Param)
Next Col
Dim CmdText As String = "UPDATE " & Data.TableName & " SET " & SetString & " WHERE " & Identity
NewCmd.CommandText = CmdText
Return NewCmd
End Function
Private Function BuildInsertCommand(ByRef Data As DataTable) As SqlCommand
Dim NewCmd As New SqlCommand
NewCmd.Connection = Connection
Dim Fields As String
Dim Values As String
Dim Identity As String
For Each Col As DataColumn In Data.Columns
If Col.AutoIncrement Then
Identity = "SELECT @@SCOPE_IDENTITY AS " & Col.ColumnName
Else
If Not (Fields = String.Empty) Then
Fields &= ", "
End If
Fields &= Col.ColumnName
If Not (Values = String.Empty) Then
Values &= ", "
End If
Values &= "@" & Col.ColumnName
Dim Param As New SqlParameter
Param.ParameterName = "@" & Col.ColumnName
Param.SourceColumn = Col.ColumnName
NewCmd.Parameters.Add(Param)
End If
Next Col
Dim CmdText As String = "INSERT INTO " & Data.TableName & "(" & Fields & ") VALUES(" & Values & ")"
If Not (Identity Is Nothing) Then
CmdText &= "; " & Identity
End If
NewCmd.CommandText = CmdText
Return NewCmd
End Function
When saving my Users datatable this produces an insert command text of:
VB Code:
INSERT INTO Users(Username, Password) Values(@Username, @Password); SELECT @@SCOPE_IDENTITY As UserID
The command param object allows you to bind rows to an @Varible by doing:
VB Code:
Dim Param As New SqlParameter
Param.ParameterName = "@" & Col.ColumnName
Param.SourceColumn = Col.ColumnName
NewCmd.Parameters.Add(Param)
Now when I save my DB the new identity value is added to datatable.
Perfect.
I am howveer making some assumptions here:
In both functions I am assuming that the tablename in the DB is the same as the tablename of the datatable.
In the update command function I am using the Identity column to specify which rows to update. This is used to create the WHERE clause of my update statement. I should however change this to use unique columns to build up the WHERE clause. Not figured that out yet.
WOka
Last edited by Wokawidget; Jul 29th, 2005 at 07:45 AM.
-
Jul 29th, 2005, 07:00 AM
#2
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
I am also assuming that the name of the datatable is exactly the same as in the DB. the however may not always be the case.
So, we need to manually specify the table it wishes to update.
VB Code:
Public Function BuildInsertCommand(ByRef Data As DataTable, ByVal TableName As String) As SQLCommand
'blah blah blah
'This line needs to be modified to use the passed param and not the datatable tablename
Dim CmdText As String = "INSERT INTO " & TableName & "(" & Fields & ") VALUES(" & Values & ")"
'blah blah blah
End Sub
This change also needs to be done to the BuildUpdateCommand function.
However, since the datatable tablename is more often than not the same as the DBs tablename then it seems a little excessive to always manually pass in a table name, we could just use the datatables tablename like in my 1st post.
What we can do here is we can overload the Buildcommand functions.
VB Code:
Public Overloads Function BuildInsertCommand(ByRef Data As DataTable) As SQLCommand
Return BuildInsertCommand(Data, Data.TableName)
End Function
This gives us a little more flexibility when it comes to saving the datatable.
Hope this helps.
Woka
-
Jul 29th, 2005, 07:38 AM
#3
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
If using SQL Server, it may sometimes be best to use <a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp">@@SCOPE_IDENTITY</a>. This returns the IDENTITY within the current context. This is especialy true if you have triggers which insert into other tables. This is because @@IDENTITY returns the LAST ADDED Identity field.... and if you have triggers, that won't be from the table you jsut inserted. @@SCOPE_IDENTITY on the other hand will return the right identity from the table just inserted into (but not from the triggers.) Did that make sense?
Tg
-
Jul 29th, 2005, 07:43 AM
#4
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Yea. I saw something on that last night. Was going to look into it this afternoon.
Just thought I'd get my code up and running. OK...I will change my 1st post.
Thanks.
Woka
-
Sep 19th, 2006, 04:17 PM
#5
Banned
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Rather than using DYNAMIC HTML you should take advantage of stored procedures, just a thought. Dynamic html is vulnerable to SQL Injection, no security mechanism, and the code is a bit more difficult to read. Generally you want all INSERTS / UPDATES / DELETES to be performed on the server side of things.
-
Sep 19th, 2006, 04:24 PM
#6
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Jak - what the heck? Who said anything about DHTML? This was all about getting the last ID inserted....
-tg
-
Sep 19th, 2006, 04:43 PM
#7
Banned
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
No look at the code (the vb code pages) he is using static / dynamic html within his code. It is best to leave that to the db side of things. Sorry that is what I meant, not sure how you missed it.
-
Sep 19th, 2006, 04:45 PM
#8
Banned
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
BTW returning SCOPE_IDENTITY can be sent back as an OUTPUT parameter in 2k, and the new 2k5 (SQL Server) allows you to perform SELECT output.ID FROM ... statements.
-
Sep 19th, 2006, 05:05 PM
#9
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Okay, what-ever..... I must be as blind as a bat, because I don't see anything about (D)(X)HTML anywhere. All I see is VB code. Even still, it's not pertinant to this thread.
-tg
-
Sep 19th, 2006, 06:06 PM
#10
Banned
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
You dont see the INSERT and SELECT statements within his code? That is running or executing SQL he creates static SQL in his code, and then uses variables or parameters within the program to construct SQL...and how does it get run EXECUTE ??? See my point about stored procedures and security.
If you still don't understand feel free to PM, I don't want to go off topic.
-
Sep 19th, 2006, 06:44 PM
#11
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Jimminy Christmas! THAT's what you are on about. Of course DYNAMIC SQL is bad, we all know that.... but that's outside the scope of things here...
Your quote read:
Rather than using DYNAMIC HTML you should take advantage of stored procedures, just a thought. Dynamic html
Last I checked, HTML and SQL were NOT the same thing.... now, have things changed? Okay... so, do you know see why I am confused?
-tg
-
Sep 19th, 2006, 07:00 PM
#12
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Hahaha this was confusing
If I did:
VB Code:
Dim SQL As String = "SELECT * FROM Users WHERE UserID = " & txtUserID.Text
Then, yes, I would agree with your comments regarding SQL injections blah blah.
However, I have not used this method in my code. Using the command object can prevent SQL injections.
OK, point taken, stored procedures...not all DB's have the ability to run SP's...and sometimes SP's are just plain outright overkill.
SQL 2005 has made life very easy regarding writting DB code, but it is still relatively new...and certainly wasn't released when I made this post
Woka
-
Sep 19th, 2006, 07:04 PM
#13
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Originally Posted by techgnome
Jimminy Christmas!
I wouldn't say Dynamic SQL is bad as such, it has it's places.
It's all about what technologies are available at your disposal at any one given time that determinds how things are done.
It also depends on the timescale you've been given for an app, and also the network environment. Maybe the customer hasn't decided what DB to use yet.
The list is endless.
Point being, there are always 2 ways to skin a cat, and neither way is incorrect. You use a knife...I prefer a spoon.
Woooof
-
Sep 20th, 2006, 08:43 AM
#14
Banned
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Originally Posted by techgnome
Jimminy Christmas! THAT's what you are on about. Of course DYNAMIC SQL is bad, we all know that.... but that's outside the scope of things here...
Your quote read:
Last I checked, HTML and SQL were NOT the same thing.... now, have things changed? Okay... so, do you know see why I am confused?
-tg
Sorry bout that I meant to say Dynamic SQL. And it should never be an option. IT is bad practice...if you are going to do any type of sql on the coding side of things (Away from the db) please at least use the parameters collection and ado.net to at least eliminate any possibility of sql injection, or bad code ftm.
-
Sep 20th, 2006, 08:56 AM
#15
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Like I said...Not all DB's can use Stored Procedures.
And, you'll notice I did use the parameters collection.
Was this you just wanting a random rant? Hahaha
Woof
-
Sep 20th, 2006, 09:07 AM
#16
Banned
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
If they dont have procedures they definately have a way to avoid sql injection. I did notice you used the parameters class, which was good, however you did use dynamic sql. Oracle and SQL server both can handle stored procedures.
-
Sep 20th, 2006, 09:22 AM
#17
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Yes I was using dynamic sql, but with params, but as explained this is sometimes unavoidable.
Yes, both Oracle and SQL Server have SP's at their disposal...but not everyone uses these DB's I am afraid...we do not always live in an ideal world
Woka
-
Aug 21st, 2012, 03:01 PM
#18
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
I know this is kinda old but I just wanted to say a) I think you did a great job on this,however
b) SELECT @@SCOPE_IDENTITY doesn't work in SQL Server 2008 instead you need to use SELECT SCOPE_IDENTITY()
Not sure when this changed but figured I'd share the change, otherwise the code worked great for me.
-
Aug 21st, 2012, 03:42 PM
#19
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
Last edited by ProphetBeal; Aug 22nd, 2012 at 09:12 AM.
Reason: Double post
-
Aug 21st, 2012, 05:11 PM
#20
Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET
That's because it never changed... It's always been @@IDENTITY and SCOPE_IDENTITY() ... what's changed apparently has been my memory and recall... apparently I must have been smoking something the day I posted that... I also notice the link doesn't work either.
So here's a better link.
http://msdn.microsoft.com/en-us/library/ms190315.aspx
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|