[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Connection strings for two different sources (Excel & MS SQL)

Posted on 2014-03-19
2
Medium Priority
?
313 Views
Last Modified: 2014-04-13
I have a connection string to read from a SQL data base and another to read from an Excel spreadsheet. However how do I handle a situation where I want to read records from an excel spreadsheet and insert them into my SQL database

example
Insert into DCR_transaction_master  SELECT * from [DCR_transaction_master$] where T2_transactionId='&TransactionId'

What connection string do I provide with this type of statement which is reading from Excel and writing to SQL DB?

regards
Pat
0
Comment
Question by:pclarke7
2 Comments
 
LVL 20

Accepted Solution

by:
Daniel Van Der Werken earned 1500 total points
ID: 39939548
Pat:

This article seems to provide a really good way to do what you want, and it also shows the use of multiple connection strings, as you are asking about that:
http://code.msdn.microsoft.com/office/Imoprt-Data-from-Excel-to-705ecfcd

However, if that's not enough, I would do it this way. It might not be the fastest or most efficient, but it would work:

1. Write a method to insert the data into the SQL database using the connection string using SQLConnection and SQLCommand. Use the "using" statement:

Using (SqlConnection conn = new SqlConnection("SQL DB Connection String))
{
   // do the insert using a SqlCommand
}

Have this method take in the appropriate data structure (a Class or Struct as you would define it).

Then, in a separate method, read each row from the Excel sheet and populate the structure or class. Pass in that populated structure/class to the method that inserts it into SQL.
0
 

Author Closing Comment

by:pclarke7
ID: 39997785
Thanks Dan7el,
apologies for delay in replying

regards
Pat
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

831 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