Solved

Multiple Connections VB.NET SqlCommand

Posted on 2010-08-17
11
622 Views
Last Modified: 2012-05-10
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!
0
Comment
Question by:slightlyoff
  • 4
  • 4
  • 3
11 Comments
 
LVL 4

Expert Comment

by:justin-clarke
ID: 33456075
Are you using SQL Server as the database? As you can insert into other databases tables all in one query for each database.

Like...

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Database1.dbo.Sales_Information
WHERE Year(Date) = 1998

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Database2.dbo.Sales_Information
WHERE Year(Date) = 1998

etc etc
0
 
LVL 4

Expert Comment

by:justin-clarke
ID: 33456078
Sorry... The other way round!

INSERT INTO Database1.dbo.Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

INSERT INTO Database2.dbo.Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
0
 
LVL 3

Accepted Solution

by:
with earned 500 total points
ID: 33456176
Hi slightlyoff,

I would suggest this approach:

1. Connect to 1st database, get *all* 3000+ records; keep them in memory or save them to disk somewhere, and close the connection to the 1st database.

If these databases have the same schema (table structure), perhaps you can now create a single SQL execution plan before proceeding, by looping the 3000+ records at this time.

2. Loop through database connections 2 - 4
3. Open the next connection
4. Either loop through records & execute SQL (or alternatively, execute the pre-written SQL mentioned above).  You can absolutely put part this in a Try block, that never hurts.
5. Close the connection

Typically you want to want to avoid excessive opening & closing of database connections.  It's usually best to open the connection, do as much work as quickly possible, and close the connection.  It should be more performant that way, and be more conducive to employing transactions, should you need to.
0
 
LVL 1

Author Comment

by:slightlyoff
ID: 33456188
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.
0
 
LVL 4

Expert Comment

by:justin-clarke
ID: 33456200
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:slightlyoff
ID: 33456237
@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...

0
 
LVL 4

Expert Comment

by:justin-clarke
ID: 33456243
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.
0
 
LVL 3

Expert Comment

by:with
ID: 33456304
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.
0
 
LVL 1

Author Comment

by:slightlyoff
ID: 33456368
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.
0
 
LVL 3

Expert Comment

by:with
ID: 33457595
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).
0
 
LVL 1

Author Closing Comment

by:slightlyoff
ID: 33457784
Thank you for clearing that up.  Makes perfect sense!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now