Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Multiple Connections VB.NET SqlCommand

Posted on 2010-08-17
11
Medium Priority
?
632 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

609 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