Free Web Development Online Tutorials, Learn to Code
ado.net sql connection string example, ado.net connection string username password

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
{}

Ado.net DbConnection Example

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);
        }
    }
}
SQL Server Native Client 11.0 OLE DB Provider

Standard security

Provider=SQLNCLI11;Uid=username;Pwd=password;Server=server-ip-address;Database=databaseName;
for tursted connection, you need to specify Trusted_Connection=yes;

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();
                }      
    }
}

Learn more about how to use transaction with Ado.net Object

Comment
Name
Email
Website
Subscribe
 
learn ado.net
ADO.NET Connection Strings C# Example
Ado.net Interview Questions Answers

Ado.Net C# Examples | Join Asp.Net MVC Course