ADO.NET connection string c# example
In this tutorial you will learn about DbConnection methods and how to setup ConnectionString property in ado.net, setup connection string details to establish connection, opening and closing DbConnection object
DbConnection object is the base class for all Ado.net connection object, here we see all examples with SqlConnection which is inherited from DbConnection class
using System.Data.Common;
public abstract class DbConnection : Component, IDbConnection, IDisposable
{}
Step 1:
First setup database connection string details in config file, ado net connection string in app config
<appSettings> <add key="databaseName" value="dbWebTrainingRoom"/> <add key="userid" value="db-username"/> <add key="password" value="db-password"/> <add key="serverName" value="10.125.145.125"/> </appSettings>
Step 2:
Read connection string details from config file and create a static string property in a separate class for better reusability of connection string,
this is how ado.net connectionstring for sqlclient will look like
public class Util
{
public static string ConnectionString {
get
{
string _databaseName= ConfigurationManager.AppSettings["databaseName"];
string _userid = ConfigurationManager.AppSettings["userid"];
string _password = ConfigurationManager.AppSettings["password"];
string _serverName = ConfigurationManager.AppSettings["serverName"];
return string.Format("user id={0};password={1};Data Source={2};Initial Catalog={3};Integrated Security=true;",
_userid, _password, _serverName, _databaseName);
}
}
}
Standard security
Provider=SQLNCLI11;Uid=username;Pwd=password;Server=server-ip-address;Database=databaseName;
Connection to a SQL Server instance
Provider=SQLNCLI11;Server=server-ip-address\dbInstanceName;Database=databaseName;Trusted_Connection=yes;
Step 3:
Now whenever creating a DbConnection object (in our example we will be creating SqlConnection object) use the connection string details from above static property
public void LoadDataDbCommand()
{
using (SqlConnection con = new SqlConnection(Util.ConnectionString))
{
con.Open();
con.Close();
}
}
Ideally after using any dbConnection object you should dispose them and set to null,
but if you are creating instance using(object) {}, then the moment your code execution is over and come out from that scope, the object is disposed automatically
Here is how to use transaction with current SqlConnection instance you can.
public void LoadDataDbCommand()
{
SqlTransaction objTrans = null;
using (SqlConnection con = new SqlConnection(Util.ConnectionString))
{
con.Open();
objTrans = con.BeginTransaction();
try
{
objTrans.Commit();
}
catch {
objTrans.Rollback();
}
finally
{
con.Close();
}
}
}
While updating data into multiple tables from your ado.net code, you need to make sure data consistency by using transaction object, learn more about how to use transaction with Ado.net Object
