Connection String
A connection string serves as a vital source of information that enables a data provider to establish effective communication with a specific database. This string comprises a collection of essential parameters, including the driver's name, the server name, the database name, and security credentials like the username and password.
Within the field of ADO.NET, an ADO.NET Data Provider assumes a crucial role as a specialized class capable of establishing communication with a particular type of database or data store. These Data Providers typically rely on connection strings, which encompass a comprehensive set of parameters, to establish a connection between the application and the database. Through the utilization of these connection strings, applications can effectively establish and manage connections with the database, enabling seamless data access and manipulation.
The .NET Framework provides mainly three data providers, they are
- .NET DataProvider(SQL Server);
- OLEDB
- ODBC
The connection string acts as a configuration blueprint, providing the necessary details and specifications for the Data Provider to establish a successful connection with the desired database. By encapsulating essential information such as the driver, server name, database name, and security credentials, the connection string empowers the Data Provider to establish a secure and efficient line of communication, ensuring that the application can interact with the database effectively.
From the following section you can find different types of providers and its connection strings in detail.
Microsoft SQL Server
.NET DataProvider - Standard Connection
using System.Data.SqlClient;
ConnectionString = "Data Source=servername;" +
"Initial Catalog=databasename;User id=uasername;Password=password;";
SqlConnection connection ;
connection = new SqlConnection(ConnectionString);
connection.Open();
.NET DataProvider - Trusted Connection
using System.Data.SqlClient;
ConnectionString = "Data Source=servername;" +
"Initial Catalog=databasebame;"Integrated Security=SSPI;";
SqlConnection connection ;
connection = new SqlConnection(ConnectionString);
connection.Open();
.NET DataProvider - using IP Address
using System.Data.SqlClient;
ConnectionString = "Network Library=DBMSSOCN;" +
"Data Source=your IP Address,1433;Initial Catalog=datadasename;" +
"User Id=username;Password=password;";
SqlConnection connection ;
connection = new SqlConnection(ConnectionString);
connection.Open();
OleDb - Standard Connection
using System.Data.OleDb;
ConnectionString = "Driver=SQLOLEDB;Data Source=servername;" +
"Initial Catalog=databasename;User id=username;Password=password;";
OleDbConnection connection ;
connection = new OleDbConnection(ConnectionString);
connection.Open();
OleDb - Trusted Connection
using System.Data.OleDb;
ConnectionString = "Driver=SQLOLEDB;Data Source=servername;" +
"Initial Catalog=databasename;Integrated Security=SSPI;";
OleDbConnection connection ;
connection = new OleDbConnection(ConnectionString);
connection.Open();
ODBC DSN
using System.Data.Odbc;
ConnectionString = "Dsn=yourdsnname;Uid=username;Pwd=password;";
OdbcConnection connection ;
connection = new OdbcConnection(ConnectionString);
connection.Open();
ODBC - Standard Connection
using System.Data.Odbc;
ConnectionString = "Driver={SQL Server};" +
Server=servername;DataBase=databasename;" +
"Uid=username;Pwd=password;";
OdbcConnection connection ;
connection = new OdbcConnection(ConnectionString);
connection.Open();
ODBC - Trusted Connection
using System.Data.Odbc;
ConnectionString = "Driver={SQL Server};Server=ServerName;" +
"DataBase=DataBaseName;Trusted_Connection=Yes;";
OdbcConnection connection ;
connection = new OdbcConnection(ConnectionString);
connection.Open();
MySql
.NET DataProvider - MySQL Connector/Net
using MySql.Data.MySqlClient;
ConnectionString = "server=localhost;database=testDB;" +
"uid=root;pwd=abc123;";
MySqlConnection connection ;
connection = new MySqlConnection(ConnectionString);
connection.Open();
Specifying TCP port
using MySql.Data.MySqlClient;
ConnectionString = "Server=IP Address;Port=1234;" +
Database=myDataBase;Uid=myUsername;Pwd=myPassword;"
MySqlConnection connection ;
connection = new MySqlConnection(ConnectionString);
connection.Open();
OleDb
using System.Data.OleDb;
ConnectionString = "Provider=MySqlProvider;Data Source=servername;" +
"User id=UserName;Password=Secret;"
OleDbConnection connection ;
connection = new OleDbConnection(ConnectionString);
connection.Open();
ODBC DSN
using System.Data.Odbc;
ConnectionString = "Dsn=DSNName;Uid=username;Pwd=password;";
OdbcConnection connection ;
connection = new OdbcConnection(ConnectionString);
connection.Open();
ODBC Local(MyODBC Driver )
using System.Data.Odbc;
ConnectionString = "Driver={MySql};Server=localhost;" +
"Option=16834;DataBase=databasename;"
OdbcConnection connection ;
connection = new OdbcConnection(ConnectionString);
connection.Open();
ODBC 3.51 Driver
using System.Data.Odbc;
ConnectionString = "DRIVER={MySql ODBC 3.51 Driver};SERVER=servername;" +
"DATABASE=databasename;USER=username;PASSWORD=password;"
OdbcConnection connection ;
connection = new OdbcConnection(ConnectionString);
connection.Open();
Oracle
.NET DataProvider from Microsoft
using System.Data.OracleClient;
ConnectionString = "Data Source=ServerName;User id=UserName;" +
"Password=password;";
OracleConnection connection ;
connection = new OracleConnection(ConnectionString);
connection.Open();
OleDb - Oracle Driver
using System.Data.OleDb;
ConnectionString = "Driver=OraOLEDB.Oracle;Data Source=servername;" +
"User id=username;Password=passowrd;";
OleDbConnection connection ;
connection = new OleDbConnection(ConnectionString);
connection.Open();
OleDb - Microsoft Driver
using System.Data.OleDb;
ConnectionString = "Driver=MSDAORA;Data Source=servername;" +
"User id=username;Password=password;";
OleDbConnection connection ;
connection = new OleDbConnection(ConnectionString);
connection.Open();
Microsoft Access
OleDb with MS Jet
using System.Data.OleDb;
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=c:\mypath\myDb.mdb;Database Password=password;"
OleDbConnection connection ;
connection = new OleDbConnection(ConnectionString);
connection.Open();
ODBC DSN
using System.Data.Odbc;
ConnectionString = "Dsn=DsnName";
OdbcConnection connection ;
connection = new OdbcConnection(ConnectionString);
connection.Open();
ODBC - Exclusive Use
using System.Data.Odbc;
ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" +
"Dbq=c:\path\pub.mdb;Exclusive=1;Uid=Admin;Pwd=password;";
OdbcConnection connection ;
connection = new OdbcConnection(ConnectionString);
connection.Open();
Web.config and ConnectionString
In ADO.NET, a common approach is to store them in the Web.config file. This file serves as a central location for storing configuration settings for web applications. Within the Web.config file, you can define a section specifically for connection strings by creating a <connectionStrings> element.
To define connection strings within the <connectionStrings> element, you can create child elements for each connection string entry. Each child element typically contains attributes specifying the name of the connection string and its associated value, which includes details such as the driver, server name, database name, and security credentials.
<configuration>
<connectionStrings>
<add name="SQLDbConnection"
connectionString="Server=SQlServerName; Database=YouDatabaseName; User Id=userid; password= password"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
You can access the connectionstring value at run time in your ASP.NET application as shown in the following example.
VB.Net
Dim conn As String = ConfigurationManager.ConnectionStrings("SQLDbConnection")
C#
string conn = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();
By storing connection strings in the Web.config file, you centralize and manage these crucial configuration entries in one place. This approach offers several benefits, including easier maintenance and the ability to modify connection string values without modifying the application code. Furthermore, this approach enables you to reference the configuration entries in data source controls, allowing for seamless integration of connection strings within your application's data access components.
Conclusion
A connection string acts as a key element in establishing successful communication between a data provider and a specific database. It contains a set of parameters that guide the Data Provider in establishing a connection, while also encompassing critical details like the driver, server name, database name, and security credentials. By utilizing connection strings, ADO.NET Data Providers can establish robust and secure connections with databases, enabling seamless data access and manipulation within applications.