For simplicity in the past I've created databases in not completely normalized ways, like for a record with possibly multiple values for a given field I'd store them in the main table as a comma-delimited string, per post #6 here.
http://forums.aspfree.com/asp-development-5/sql-joins-156990.html#post462461
Per post #7, I've created a new project using this normalized db approach, but am curious about the overhead incurred in my vb.
My concern is that now, if a record has 8 different values associated with it, instead of updating one field in the main table with "1,2,3,4,5,6,7,8" I now one by one create 8 different OdbcCommand objects to update each associated row in the related table. If this is a record update and not an insert, for each value I need to first query the database and see if that value is already associated with that record, so the back and forth with the database seems greatly increased.
Is there a better way to do this? My approach seems bad to me, but I'm not sure how else it would be done. I am using access db so don't think I can run multiple sql commands at one time by seperating with a semicolon. I am also curious how it would be handled with SQL server as I use that often as well. Little help?
Was this answer helpful ?
Yes No
If you have to code setup correctly then it shouldn't be that much of an overhead.
The server and db driver should be able to cope with the increase of simple statements.
Depending on your database you could combine all of the queries into one execute statement.
Was this answer helpful ?
Yes No
maybe it's an idea of what setting up the code properly entails. Currently I'm creating quite a few seperate command objects, not sure if that's bad, and hit the table up to 10 times if an insert for 10 relations to an item exist.
Any pointers as to what setting the code up properly means?
Was this answer helpful ?
Yes No
why not show us how you're doing it now and then maybe we can see if there's a simpler way.
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by mehere why not show us how you're doing it now and then maybe we can see if there's a simpler way. |
Absolutely, thanks! Below is the actual code, but to hopefully keep this efficient for you, here is also pseudo code:
First do a lookup in the table to see if a given item attempting to be inserted already exists in the table.
If not, insert the item. Then, retrieve the autonumber id from the database for that new item. (I am sure there's a better way to do this, but have not found one yet that I've had luck with, so any pointers on this one would be appreciated)
Then, for each value associated with this record to be inserted into the related table, create yet another odbcCommand and to the insert.
Code:
Dim BID As Integer
If Page.IsValid Then
Dim LinkLookup As OdbcDataReader
Dim BidLookup As OdbcDataReader
Dim sSQL As String
sSQL = "SELECT bookmark_id FROM bookmark WHERE bookmark_url=" & Trim(txtLink.Value)
Dim myConnection As New OdbcConnection([Global].OdbcConnString)
Dim myCommand As New OdbcCommand(sSQL, myConnection)
myConnection.Open()
LinkLookup = myCommand.ExecuteReader()
myCommand.Dispose()
If LinkLookup.Read() Then
divLinkAlreadyExists.Visible = True
spanEnterTag.Visible = False
Else
sSQL = "INSERT INTO bookmark (bookmark_url, bookmark_title, bookmark_desc) VALUES (" & txtLink.Value & ", " & txtTitle.Value & ", " & txtDesc.Value)"
myCommand = New OdbcCommand(sSQL, myConnection)
myCommand.ExecuteNonQuery()
myCommand.Dispose()
sSQL = "SELECT MAX(bookmark_id) As bid FROM bookmark"
myCommand = New OdbcCommand(sSQL, myConnection)
BidLookup = myCommand.ExecuteReader()
myCommand.Dispose()
If BidLookup.Read() Then
BID = CInt(BidLookup.GetInt32(0))
sSQL = "INSERT INTO bookmark_X_categories (bookmark_id_FK, category_id_FK) VALUES (" & BID) & ", " & ddTags1.SelectedValue & ")"
myCommand = New OdbcCommand(sSQL, myConnection)
myCommand.ExecuteNonQuery()
myCommand.Dispose()
If (Trim(ddTags2.SelectedValue <> "") Then
sSQL = "INSERT INTO bookmark_X_categories (bookmark_id_FK, category_id_FK) VALUES (" & BID) & ", " & ddTags2.SelectedValue & ")"
myCommand = New OdbcCommand(sSQL, myConnection)
myCommand.ExecuteNonQuery()
myCommand.Dispose()
End If
If (Trim(ddTags3.SelectedValue <> "") Then
sSQL = "INSERT INTO bookmark_X_categories (bookmark_id_FK, category_id_FK) VALUES (" & BID) & ", " & ddTags3.SelectedValue & ")"
myCommand = New OdbcCommand(sSQL, myConnection)
myCommand.ExecuteNonQuery()
myCommand.Dispose()
End If
If (Trim(ddTags4.SelectedValue <> "") Then
sSQL = "INSERT INTO bookmark_X_categories (bookmark_id_FK, category_id_FK) VALUES (" & BID) & ", " & ddTags4.SelectedValue & ")"
myCommand = New OdbcCommand(sSQL, myConnection)
myCommand.ExecuteNonQuery()
myCommand.Dispose()
End If
If (Trim(ddTags5.SelectedValue <> "") Then
sSQL = "INSERT INTO bookmark_X_categories (bookmark_id_FK, category_id_FK) VALUES (" & BID) & ", " & ddTags5.SelectedValue & ")"
myCommand = New OdbcCommand(sSQL, myConnection)
myCommand.ExecuteNonQuery()
myCommand.Dispose()
End If
If (Trim(ddTags6.SelectedValue <> "") Then
sSQL = "INSERT INTO bookmark_X_categories (bookmark_id_FK, category_id_FK) VALUES (" & BID) & ", " & ddTags6.SelectedValue & ")"
myCommand = New OdbcCommand(sSQL, myConnection)
myCommand.ExecuteNonQuery()
myCommand.Dispose()
End If
If (Trim(ddTags7.SelectedValue <> "") Then
sSQL = "INSERT INTO bookmark_X_categories (bookmark_id_FK, category_id_FK) VALUES (" & BID) & ", " & ddTags7.SelectedValue & ")"
myCommand = New OdbcCommand(sSQL, myConnection)
myCommand.ExecuteNonQuery()
myCommand.Dispose()
End If
If (Trim(ddTags8.SelectedValue <> "") Then
sSQL = "INSERT INTO bookmark_X_categories (bookmark_id_FK, category_id_FK) VALUES (" & BID) & ", " & ddTags8.SelectedValue & ")"
myCommand = New OdbcCommand(sSQL, myConnection)
myCommand.ExecuteNonQuery()
myCommand.Dispose()
End If
Response.Redirect("add.aspx?status=success")
Else
divLinkAlreadyExists.Visible = True
divLinkAlreadyExists.InnerHtml = "Problem"
End If
End If
myConnection.Close()
End If
Thank you!
Was this answer helpful ?
Yes No
are there any thoughts on this code please? I know it's a bit lengthy but I am still interested if there's a more efficient way to do this, as it seems like a very common need, in a truly normalized database to have only one value in each db field, you'll need multiple entries in a table related to one record in another, so the proper way to insert multiple entries into one table with .net must be fairly common knowledge. Can anyone help me out with this? thx
Was this answer helpful ?
Yes No
I can't think of any way to get around multiple statements since you need to evaluate whether the item has a value first, but as for the next autonumber; you might look into the use of @@IDENTITY. Once your first insert is executed; the @@IDENTITY keeps track of the last id value inserted. You might try testing that. One more thing. IDENT_CURRENT('table name') will give the last identity used in any table with any scope. That might eliminate the need for you to rely on the MAX().
You can do multiple inserts in one statement, but you are also programmatically determining whether the value is empty first. There may be a way to create a better method to avoid the additional connections, but it's a matter of cost vs. savings. How much time are you saving for the cost? Is performance an issue with the current code?
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by Lauramc Is performance an issue with the current code? |
Thank you Lauramc, I actually just happened upon this reply now, sorry for the delay.
It's a low-volume personal application and so I havn't any performace issues per-se, but I'm just wanting to make sure I'm not doing things in a completely inefficient way, just to know. Your comments help, thanks.
Was this answer helpful ?
Yes No
Relational db, OdbcCommands
Instead of constantly using the the ODBCCommand, how about concatenating all the INSERT commands into one? So, you still do the normal IF checks before concatenating the string, but then you're away. How do we do this?
INSERT INTO Table_Name VALUES('Value1', 'Value2', 'Value3' etc) and ensure there's a space after the last bracket, and then continue concatenating,
INSERT INTO Table_Name VALUES('Value4', 'Value5', 'Value6' etc). I would advise trying to use .Nets StringBuilder as it uses less memory and should be quicker.
Then use one OdbcCommand and one ExecuteNonQuery
Hope it helps.
Was this answer helpful ?
Yes No
