Solved

Multiple Connections VB.NET SqlCommand

Posted on 2010-08-17
11
630 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 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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