OLE DB Conection Dialog

  • Thread starter Thread starter Luis Arvayo
  • Start date Start date
L

Luis Arvayo

Hi,

In my app, I need to allow to end user to choose the conection parameters
for a SQL Database or Access database, then I need to show the same OLE DB
conection dialog used by Microsoft.

How to do that ?

Thanks in Advance
Luis Arvayo
 
Dear Luis Arvayo,

You have stated whether your Appliation is a Windows Based Application or
WebBased Application

If I consider your application as web based, then

in the web.config

<appSettings>
<add key="ConnectionStringSQLSever" Value="Provider=sqloledb;Data
Source=myServerName;Initial Catalog=myDatabaseName;User
Id=myUsername;Password=myPassword"" />

<add key="ConnectionStringMSAccess"
Value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myPath\myJet.mdb;User
ID=Admin;Password=" />

</appSettings>

In your Application for Accessing the appSettings Section

Dim appNameConnectionString As String

If UserSelectsSQLServer then

appNameConnectionString=ConfigurationSettings.AppSettings
("ConnectionStringSQLSeverName")

elseif UserSelectsSQLServer then

appNameConnectionString=ConfigurationSettings.AppSettings
("ConnectionStringMSAccess")

end if

This you have to achieve either through a combo box or Radio Button, But not
CheckBox(User may select both the check box!! or you have to write extra code
like if one checkbox is selected, other is de-selected)

NOTE: FOR THE SQL SERVER CONNECTION, INSTEAD OF USING OLEDB, IT IS BETTER TO
USE THE NATIVE PROVIDER WHICH WILL BE VERY FAST AND WHICH IS HIGHLY OPTIMISED
FOR THE SQL SERVER.

IN THAT CASE, YOUR CONNECTION STRING WILL BE:

The SQL Server .NET Data Provide allows you to connect to a Microsoft SQL
Server 7.0 or 2000 database. For Microsoft SQL Server 6.5 or earlier, use
the OLE DB .NET Data Provider with the "SQL Server OLE DB Provider"
(SQLOLEDB).

Note: The SQL Server .NET Data Provider knows which data provider it is.
Hence the "provider=" part of the connection string is not needed.

Using C#:

using System.Data.SqlClient;
....
SqlConnection oSQLConn = new SqlConnection();
oSQLConn.ConnectionString = "Data Source=(local);" +
"Initial Catalog=myDatabaseName;" +
"Integrated Security=SSPI";
// Or // "Server=(local);" +
// "Database=myDatabaseName;" +
// "Trusted_Connection=Yes";oSQLConn.Open();

************************************************************************************************************************

Using VB.NET:
=========

Imports System.Data.SqlClient
....
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"
oSQLConn.Open()

If connection to a remote server (via IP address):

oSQLConn.ConnectionString = "Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"
Where:
- "Network Library=DBMSSOCN" tells SqlConnection to use TCP/IP Q238949
- xxx.xxx.xxx.xxx is an IP address.
- 1433 is the default port number for SQL Server.
- You can also add "Encrypt=yes" for encryption

************************************************************************************************************************

IN CASE OF A WINFORMS APPLICATION, THAT IS WINDOWS APPLICATION, STORE THE

CONNECTION STRINGS IN EITHER IN XML FILE OR IN THE DATABASE OR EVEN A STATIC
PROPERTIES WHICH IS READONLY.

For Anything & Everything, Please Let Me Know,

Bye
Venkat_KL
 
Luis said:
Hi,

In my app, I need to allow to end user to choose the conection parameters
for a SQL Database or Access database, then I need to show the same OLE DB
conection dialog used by Microsoft.
Click to expand...

Step by step procedure (from
http://www.codeproject.com/cs/database/DataLinks.asp)

1. Reference ADODB in your project. This is required to read the COM
object passed back from DataLinks. This file is located here: c:\Program
Files\Microsoft.NET\Primary Interop Assemblies\adodb.dll
2. Reference DataLinks in your project. DataLinks used to be
MSDASC.dll, but has changed to OLEDB32.DLL (see KB). This file is
located here: C:\Program Files\Common Files\System\Ole DB\OLEDB32.DLL
3. Create a text box and a button on a Windows Form. In the button's
click event, use this code:

private void ButtonGetConnectionString_Click(object sender,
System.EventArgs e)
{
/*
Reference DataLinks
NOTE: Reference
C:\Program Files\Common Files\System\Ole DB\OLEDB32.DLL
(Was MSDASC.dll)
SEE:

http://support.microsoft.com:80/support/kb/articles/Q225/1/32.asp
*/
MSDASC.DataLinks dataLinks = new MSDASC.DataLinksClass();
//note that a reference to:
// c:\Program Files\Microsoft.NET\Primary Interop
Assemblies\adodb.dll
//is also required to read the ADODB._Connection result
ADODB._Connection connection;
//are we editing an existing connect string or getting a new one?
if(this.txtConnectionString.Text==String.Empty)
{
// get a new connection string
try
{
//Prompt user for new connect string
connection = (ADODB._Connection)dataLinks.PromptNew();
//read result
this.txtConnectionString.Text=
connection.ConnectionString.ToString();
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else
{
// edit connection string
connection=new ADODB.ConnectionClass();
connection.ConnectionString=this.txtConnectionString.Text;
//set local COM compatible data type
object oConnection=connection;
try
{
//prompt user to edit the given connect string
if((bool)dataLinks.PromptEdit(ref oConnection))
{
this.txtConnectionString.Text=
connection.ConnectionString;
}
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
 
Back
Top