How to Write ConnetionStrings in ADO.NET?

How many ways we can write a connection string in ADO.NET?

First we need to import a namespace as per our database. If database is SqlServer 2005/2008 choose System.Data.SqlClient in this namespace we have relavent classes to establish a connection string to Sqlserver database.

We can write connection string in  CodeBehind  or Web.Config/App.config *****

But more preferred is  write the connection in Config files

1.     Code Behind Model:

using System.Data.SqlClient;

In Sql server we can connection to database is 2 ways

1.       Windows Authencation                                                                2. Sql server Authencation

Windows Authencation Connection String:

      string str = @"Server=.\SQLEXPRESS;Database=PropercodeDB;Trusted_Connection=True;";

        SqlConnection con=new SqlConnection(str);

        con.Open();

        Label1.Text=" connection is succefully ";

                   

Sql server Authencation Connection String:

string str = "Database= wcfdb; user id^ =sa; Password = zolt123$;";

            SqlConnection con = new SqlConnection(str);

            con.Open();

            Response.Write("connection is succefullysdfasf");

We can write userid as 3 keywords to specify : User Id/user/uid

We have 2 keywords to specify password:  Password/pwd

2.     Web.config Model:

<connectionStrings>

    <add name="conname" connectionString="user=sa;pwd=123;database=ProperCodeDB;" providerName="System.Data.SqlClient"/>

  </connectionStrings>

In code behind

using System.Configuration;

using System.Data.SqlClient;

       string str=ConfigurationManager.ConnectionStrings["conname"].ConnectionString;

        SqlConnection con = new SqlConnection(str);

        con.Open();

        Label1.Text = "connection established succefully";

Note : mostly write connection string in Class declaration. Why because we can reuse this connection in many place of the codebehind in various scopes.

SqlConnection is a Sealed Class so we can create object to this class and this class id derived from DbConnection which implements IDbConnection interface.

                                                               

IDbConnection is interface which defines the members which are common to all databases

Like

public interface IDbConnection : IDisposable

{

string ConnectionString { get; set; }

int ConnectionTimeout { get; }

string Database { get; }

ConnectionState State { get; }

IDbTransaction BeginTransaction();

IDbTransaction BeginTransaction(IsolationLevel il);

void ChangeDatabase(string databaseName);

void Close();

 IDbCommand CreateCommand();

void Open();

}

DbConnection is abstract class which implements IDbConnection interface.all derived classes of DbConnection which uses the DbConnection implenentations in sealed classes.

So we can write connection like below also.

Web.config

<connectionStrings>

    <add name="conname" connectionString="user=sa;pwd=123;database=ProperCodeDB;" providerName="System.Data.SqlClient"/>

  </connectionStrings>

 In Code behind  As per our database.

Using System.Data.Common;

Using System.Data.SqlClient

        string str=ConfigurationManager.ConnectionStrings["conname"].ConnectionString;

        DbConnection con = new SqlConnection(str);

        con.Open();

        Label1.Text = "Connection Succefully Established";

 Using System.Data.Common

Using System.Data.OracleClient

          string str=ConfigurationManager.ConnectionStrings["conname"].ConnectionString;

        DbConnection con = new OracleClient(str);

        con.Open();

        Label1.Text = "Connection Succefully Established";

……………..ect.

Last but Most Preferable is FactoryPattern

 ConnectionStringSettings s=ConfigurationManager.ConnectionStrings["wcfcon"];

     DbProviderFactory f=DbProviderFactories.GetFactory(s.ProviderName);

           DbConnection cons = f.CreateConnection();

            cons.ConnectionString = s.ConnectionString;                     

            cons.Open();

            Response.Write("connection is succefullysdfasf");

Or           

ConnectionStringSettings s=ConfigurationManager.ConnectionStrings["wcfcon"];

SqlConnection cons = new SqlConnection(

 cons.Open();

Response.Write("connection is succefullysdfasf");