Creating True OLEDB Connectivity

Hello,

I have an application that needs to be able to connect to any OLEDB enabled database.

The application utilizes the MSDASC.dll connection screen to generate the connection strings.  That works fine.  

Currently, the various connection string fields are stored in the application's database "Provider", "Data Source", "UserID", "Initial Catalog", etc.  So, when the user needs to connect to that database again, the application puts the connection string back together using each field.  This also is working.

My problem is that I need a simple way to store all of the connection strings.  If we continue to use this method for storing the connection strings, I'm going to have to write a routine to piece the connection string back together for every kind of OLEDB database that exists.  (e.g. MS Access uses Provider, Data Source, User ID and Password while MS SQL uses Provider,  Data Source, Initial Catalog, Integrated Security, etc)

This would kind of cut down on the connectability, as users would only be able to connect to platforms for which I had specifically written code.

Can anyone think of a better way to store and retreive the connection string information?

Thanks in advance,
-Torrwin
LVL 13
TorrwinAsked:
Who is Participating?
 
Bob LearnedCommented:
I don't understand what the Select Case has to do with the MSDASC Link Builder dialog.  You can get the connection string that dialog creates, and store that.

Bob
0
 
Bob LearnedCommented:
Persist them to an XML file.

Bob
0
 
TorrwinAuthor Commented:
Ok, but that doesn't solve the problem of piecing the connection string back together by platform does it?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
Bob LearnedCommented:
I don't know about piecing them together, but I do know about building dynamic strings with String.Format:

Dim connectionString As String = "Data Source={0};UID={1};Pwd={2}", dataSource, userID, password)

Bob
0
 
TorrwinAuthor Commented:
That is not the problem.  The problem is that for every different platform, you need a different connection string.  So, right now I have code similar to this:

Select Case Provider
     Case SQL
          sConnStr = "Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;"
     Case Oracle
          sConnStr = "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;"
     Case Access
          sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;"
     Case MySQL
          sConnStr = "Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;"
...etc...
End Select

While MSDASC can setup a connection to any OLEDB enabled datasource, we'll only be able to re-connect later to those datasources which are specifically listed in the SELECT statement above.  Thus, we can't label our product as able to connect to any OLEDB enabled datasource.

How can we better store the connection strings so that they will automatically rebuild themselves, and thus eliminate the SELECT statement?
0
 
TorrwinAuthor Commented:
Bob,

Your confusion on this subject led me to find that "ConnectionString" was a property of the MSDASC dialog, which answers my question.  The program (which I was given) stored the individual pieces such as "Provider", "DataSource", "Username", and "Password".  It then tried to piece these back together instead of just storing the entire connection string in the first place.

So, should I ask for this question to be deleted or would you like me to award you the points since you led me in the right direction?

Thanks for the help as always,
-Torrwin
0
 
Bob LearnedCommented:
Torrwin,

If I was helpful, and/or answered your question, then feel free to accept the comment that best helped you.  If not, feel free to ask for a PAQ/Refund.

Bob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.