Using C#:
using System.Data.SqlClient;
...
SqlConnection oSQLConn = new SqlConnection();
oSQLConn.ConnectionString = "Data Source=(local);" +
"Initial Catalog=mySQLServerDBName;" +
"Integrated Security=SSPI";
oSQLConn.Open();
Using VB.NET:
Imports System.Data.SqlClient
...
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=mySQLServerDBName;" & _
"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=mySQLServerDBName;" & _
"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. Q269882 and Q287932
- You can also add "Encrypt=yes" for encryption
OLE DB .NET Data Provider (System.Data.OleDb)
The OLE DB .NET Data Provider uses native OLE DB through COM interop to enable data access.
To use the OLE DB .NET Data Provider, you must also use an OLE DB provider (e.g. SQLOLEDB, MSDAORA, or Microsoft.JET.OLEDB.4.0).
For IBM AS/400 OLE DB Provider
' VB.NET
Dim oOleDbConnection As OleDb.OleDbConnection
Dim sConnString As String = _
"Provider=IBMDA400.DataSource.1;" & _
"Data source=myAS400DbName;" & _
"User Id=myUsername;" & _
"Password=myPassword"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()
For JET OLE DB Provider
' VB.NET
Dim oOleDbConnection As OleDb.OleDbConnection
Dim sConnString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\myPath\myJet.mdb;" & _
"User ID=Admin;" & _
"Password="
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()
For Oracle OLE DB Provider
' VB.NET
Dim oOleDbConnection As OleDb.OleDbConnection
Dim sConnString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User ID=myUsername;" & _
"Password=myPassword"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()
For SQL Server OLE DB Provider
' VB.NET
Dim oOleDbConnection As OleDb.OleDbConnection
Dim sConnString As String = _
"Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()
For Sybase ASE OLE DB Provider
' VB.NET
Dim oOleDbConnection As OleDb.OleDbConnection
Dim sConnString As String = _
"Provider=Sybase ASE OLE DB Provider;" & _
"Data Source=MyDataSourceName;" & _
"Server Name=MyServerName;" & _
"Database=MyDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()
For more information, see: System.Data.OleDb Namespace and .NET Data Providers
ODBC .NET Data Provider (System.Data.ODBC)
The ODBC .NET Data Provider is an add-on component to the .NET 1.0 Framework SDK. It provides access to native ODBC drivers the same way the OLE DB .NET Data Provider provides access to native OLE DB providers.
For SQL Server ODBC Driver
' VB.NET
Dim oODBCConnection As Odbc.OdbcConnection
Dim sConnString As String = _
"Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Uid=MyUsername;" & _
"Pwd=MyPassword"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
For Oracle ODBC Driver
' VB.NET
Dim oODBCConnection As Odbc.OdbcConnection
Dim sConnString As String = _
"Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
For Access (JET) ODBC Driver
' VB.NET
Dim oODBCConnection As Odbc.OdbcConnection
Dim sConnString As String = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Uid=Admin;" & _
"Pwd="
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
For Sybase System 11 ODBC Driver
// C#
string myConnStr = "Driver={Sybase System 11};" +
"SRVR=mySybaseServerName;" +
"DB=myDatabaseName;" +
"UID=myUsername;" +
"PWD=myPassword";
OdbcConnection myConnection = new OdbcConnection(myConnStr);
myConnection.Open();
For all other ODBC Drivers
' VB.NET
Dim oODBCConnection As Odbc.OdbcConnection
Dim sConnString As String = "Dsn=myDsn;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
For more information, see: ODBC .Net Data Provider
.NET Framework Data Provider for Oracle (System.Data.OracleClient)
The .NET Framework Data Provider for Oracle is an add-on component to the .NET Framework that provides access to an Oracle database using the Oracle Call Interface (OCI) as provided by Oracle Client software.
Using C#:
using System.Data.OracleClient;
OracleConnection oOracleConn = new OracleConnection();
oOracleConn.ConnectionString = "Data Source=Oracle8i;" +
"Integrated Security=SSPI";
oOracleConn.Open();
Using VB.NET:
Imports System.Data.OracleClient
Dim oOracleConn As OracleConnection = New OracleConnection()
oOracleConn.ConnectionString = "Data Source=Oracle8i;" & _
"Integrated Security=SSPI";
oOracleConn.Open()
Note: You must have the Oracle 8i Release 3 (8.1.7) Client or later installed in order for this provider to work correctly.
Note: You must have the RTM version of the .NET Framework installed in order for this provider to work correctly.
Note: There are known Oracle 7.3, Oracle 8.0, and Oracle9i client and server problems in this beta release. The server-side issues should be resolved in the final release of the product. However, Oracle 7.3 client will not be supported.
MySQL .NET Native Provider
The MySQL .NET Native Provider is an add-on component to the .NET Framework that allows you to access the MySQL database through the native protocol, without going through OLE DB.
Using C#
using EID.MySqlClient;
MySqlConnection oMySqlConn = new MySqlConnection();
oMySqlConn.ConnectionString = "Data Source=localhost;" +
"Database=mySQLDatabase;" +
"User ID=myUsername;" +
"Password=myPassword;" +
"Command Logging=false";
oMySqlConn.Open();
Using VB.NET
Imports EID.MySqlClient
Dim oMySqlConn As MySqlConnection = New MySqlConnection()
oMySqlConn.ConnectionString = "Data Source=localhost;" & _
"Database=mySQLDatabase;" & _
"User ID=myUsername;" & _
"Password=myPassword;" & _
"Command Logging=false"
oMySqlConn.Open()
Source: http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#ODBCManagedProvider
-------------------Done here--------------------