ADO.NET Connection string Explained
Connection String is
a normal String representation which contains Database connection information
to establish connection between Database and your Application. The Connection
String includes parameters such as the name of the driver, Server name
and Database name, as well as security information such as user name and
password. Read more from connection string site here
Usually Data Providers use a connection string containing a collection of parameters to establish connection with the database through applications. The .NET Framework provides mainly three data providers, they are:
- Microsoft SQL Server
- OLEDB
- ODBC
We
will be looking at how to make a connection string to the above ADO.NET Data
Providers.
Microsoft SQL Server Connection String
connetionString="Data Source=ServerName;Initial
Catalog=Databasename;
User ID=UserName;Password=Password"
OLEDB Data Provider Connection String
connetionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=yourdatabasename.mdb;"
ODBC Connection String
connetionString="Driver={Microsoft
Access Driver (*.mdb)};
DBQ=yourdatabasename.mdb;"
We
will looking at how to use these ADO.NET Data Providers to establish connection
to the Data Source through C# language.
C#
SQL Server Connection
You
can connect your C# application to data in a SQL Server database using the .NET
Framework Data Provider for SQL Server. The first step in a C# application is
to create an instance of the Server object and to establish its connection to
an instance of Microsoft SQL Server.
The SqlConnection Object is handling the
part of physical communication between the C# application and the SQL Server Database.
An instance of the SqlConnection class in C# supports the Data Provider for SQL
Server Database. The SqlConnection instance takes Connection String as argument
and pass the value to the Constructor statement.
Sql Server connection string
connetionString="Data
Source=ServerName;
InitialCatalog=DatabaseName;User
ID=UserName;Password=Password"
If
you have a named instance of SQL Server, you'll need to add that as well.
"Server=localhost\sqlexpress"
When
the connection is established, SQL Commands will execute with the help of the
Connection Object and retrieve or manipulate the data in the database. Once the
Database activities is over, Connection should be closed and release the Data
Source resources.
The Close() method
The
Close() method in SqlConnection Class is used to close the Database Connection.
The Close method rolls back any pending transactions and releases the
Connection from the SQL Server Database.
A Sample C# Program that connect SQL Server using connection string.
Connection string from web.Config
<add name="DefaultConnection" connectionString="Data
Source=DESKTOP-EIRHPII;Initial Catalog=csharpnaija_employees;Integrated
Security=True" providerName="System.Data.SqlClient" />
Connection to Db test method
using System;
using System.Data.SqlClient;
namespace DatabaseFirstApproach.Models
{
public static class CsharpnaijaUtility
{
public static bool ConnectToDb(string connectString)
{
try
{
bool isConnected = false;
using(var conn = new
SqlConnection(connectString))
{
conn.Open();
if
(conn.State==System.Data.ConnectionState.Open)
{
isConnected = true;
}
}
return isConnected;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
Action method in Employee Controller
that verified if database connection is established
public
ActionResult Connect()
{
var connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
var isconnected = CsharpnaijaUtility.ConnectToDb(connString);
if (isconnected)
{
ViewBag.Connected = "Connected to the Database";
}
else
{
ViewBag.Connected = "Not Connected to the Database";
}
return View();
}
Connect
via an IP address
connetionString="Data
Source=IP_ADDRESS,PORT;
Network
Library=DBMSSOCN;Initial Catalog=DatabaseName;
User
ID=UserName;Password=Password"
1433
is the default port for SQL Server.
Trusted Connection from a CE device
connetionString="Data
Source=ServerName;
Initial
Catalog=DatabaseName;Integrated Security=SSPI;
User
ID=myDomain\UserName;Password=Password;
This
will only work on a CE device
Connecting to SQL Server using windows
authentication
"Server= localhost; Database= employeedetails;
Integrated Security=SSPI;"
Store Connection
String in Web.config
It is a good practice to store the connection
string for your application in a config file rather than a hard coded string in
your code. The examples of connection string in web.Config or app.config.
Connection string in .NET
config file
Do not use the appSettings section in web.config. Instead use the connectionStrings section in web.config.
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="myConnectionString" connectionString="server=localhost;database=myDb;uid=myUser;password=myPass;" />
</connectionStrings>
</configuration>
To read the connection string into your code, use the ConfigurationManager class.
string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
Remember to add a reference to the
System.Configuration component. Then include the namespace System.Configuration
to get access to the ConfigurationManager class.
// C# using System.Configuration;
Clear connection strings
coming from higher level config filesConfiguration files are hierarchical, with machine.config being at the highest level. You can clear connection strings defined earlier in the hierarchy to make sure any unwanted settings don't bubble down to your desired value.
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<clear /> <!-- clear all other
connection strings -->
<add name="myConnectionString" connectionString="server=localhost;database=myDb;uid=myUser;password=myPass;" />
</connectionStrings>
</configuration>
Adding <clear /> at top in the
connectionStrings section is usually a good practice to make sure no other
connection strings are bubbling down from a higher level configuration file.
Note
Always store the connection string in a config file. It's not any harder once you get used to it and you will benefit from it as it is much easier to change connection string properties when your application is in production.
Thanks. You are the best.
ReplyDeleteAm humbled
Delete