slightlyoff
asked on
Multiple Connections VB.NET SqlCommand
I'm creating a project that will read product prices from one database (3000+ products) and copy them to 4 other databases.
I'm curious if I'm heading about this the right way.
My thought:
1. Loop through each product in main database
2. Generate SQL statement
3. Connect to 1st DB
4. Execute SQL
5. Disconnect from 1st DB
6. Connect to 2nd DB
7. Execute SQL
8. Disconnect from 2nd DB
9. Connect to 3rd DB
10. Execute SQL
11. Disconnect from 3rd DB
12. Connect to 4th DB
13. Execute SQL
14. Disconnect from 4th DB
15. Loop to next product
Questions... Should I open the database connections before the loop and close them after the loop? Would this be best served in one try/catch block, or would it make more sense to do multiple blocks so I know what database the error (if any) happend at?
Thank for your help!
I'm curious if I'm heading about this the right way.
My thought:
1. Loop through each product in main database
2. Generate SQL statement
3. Connect to 1st DB
4. Execute SQL
5. Disconnect from 1st DB
6. Connect to 2nd DB
7. Execute SQL
8. Disconnect from 2nd DB
9. Connect to 3rd DB
10. Execute SQL
11. Disconnect from 3rd DB
12. Connect to 4th DB
13. Execute SQL
14. Disconnect from 4th DB
15. Loop to next product
Questions... Should I open the database connections before the loop and close them after the loop? Would this be best served in one try/catch block, or would it make more sense to do multiple blocks so I know what database the error (if any) happend at?
Thank for your help!
Sorry... The other way round!
INSERT INTO Database1.dbo.Store_Inform
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
INSERT INTO Database2.dbo.Store_Inform
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
INSERT INTO Database1.dbo.Store_Inform
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
INSERT INTO Database2.dbo.Store_Inform
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey Justin,
I am using SQL Server, but the trick is the databases are on different servers. So each store has a different connection string.
I am using SQL Server, but the trick is the databases are on different servers. So each store has a different connection string.
So therefore connect to the 1 database you are reading from. Use a transaction and then run all the INSERTS within that. Then you only have 1 connection to open and close. And as it's in a transaction then if something fails you won't get some data in the 1st DB and 2nd DB and nothing in the 3rd and 4th. Thanks.
ASKER
@with - by "save to memory", are you suggesting an array? I am only dealing with 4 fields in a record (different price levels and a product id), so maybe an array is the way to go for loading the data.
Just wondering if a 3000 item array is too big, but I'd rather do that than save it to a txt file or whatever on the hard drive...
Just wondering if a 3000 item array is too big, but I'd rather do that than save it to a txt file or whatever on the hard drive...
OK no worries.
You could read the data into a datatable and then loop through that and connect to each other database and insert the record then close the connection then connect to the other and close etc etc.
You could read the data into a datatable and then loop through that and connect to each other database and insert the record then close the connection then connect to the other and close etc etc.
A 3k array should be no problem at all if you're just talking about numeric data. I'd just keep it in memory, an array should work fine.
Quick note about transactions, you'd be performing 4 separate transactions, one to each "receiving" database. Each time you open a connection to the "receiving" database, you'd start a new transaction with that database, do all the inserts, then commit the transaction, and close the connection. If you want to enforce a rule that says "stop ALL processing on error", you'll need to a) detect the error (i.e., try/catch block) and in the catch, manually break out of your loop, to derail any remaining database inserts.
Quick note about transactions, you'd be performing 4 separate transactions, one to each "receiving" database. Each time you open a connection to the "receiving" database, you'd start a new transaction with that database, do all the inserts, then commit the transaction, and close the connection. If you want to enforce a rule that says "stop ALL processing on error", you'll need to a) detect the error (i.e., try/catch block) and in the catch, manually break out of your loop, to derail any remaining database inserts.
ASKER
Thanks so much for your help!
When you say "do all the inserts" i'm a little confused. I've been doign things a certain way, perhaps not the best way, but I'm not sure how I would load up all my update statements and then execute all at once.
Typically I would do this:
sqlCmd = New SqlCommand(iSql, myConn)
sqlCmd.ExecuteNonQuery()
over and over again.
When you say "do all the inserts" i'm a little confused. I've been doign things a certain way, perhaps not the best way, but I'm not sure how I would load up all my update statements and then execute all at once.
Typically I would do this:
sqlCmd = New SqlCommand(iSql, myConn)
sqlCmd.ExecuteNonQuery()
over and over again.
ExecuteNonQuery() can actually be called as often as you want; for example:
sqlCommand.CommandText = "Insert [RedShoe...]"
Try
sqlCmd.ExecuteNonQuery()
Catch ex
'The RedShoe won't insert!
But you can do this too:
sqlCommand.CommandText = "Insert [RedShoe...] Insert [BlueShoe...] Insert [BrownShoe...]"
Try
sqlCmd.ExecuteNonQuery()
Catch ex
'One of my 3 shoes won't insert!
If you need Transactions, then that changes the way this works (a little).
sqlCommand.CommandText = "Insert [RedShoe...]"
Try
sqlCmd.ExecuteNonQuery()
Catch ex
'The RedShoe won't insert!
But you can do this too:
sqlCommand.CommandText = "Insert [RedShoe...] Insert [BlueShoe...] Insert [BrownShoe...]"
Try
sqlCmd.ExecuteNonQuery()
Catch ex
'One of my 3 shoes won't insert!
If you need Transactions, then that changes the way this works (a little).
ASKER
Thank you for clearing that up. Makes perfect sense!
Like...
INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Database1.dbo.Sales_Inform
WHERE Year(Date) = 1998
INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Database2.dbo.Sales_Inform
WHERE Year(Date) = 1998
etc etc