Connection String Example

Connection String

ASP.NET, SQL Server

1.      Assuming we have a database “Test” on SQL Server and we have to access data from this “Test” database to our ASP.NET website. For this we need a connection between database and our website.

2.      So to access data to/from database (SQL SERVER) we need, connection string to the database.

3.      All database connection properties can be provided in web.config file as follows :

 

<connectionStrings>

<add providerName="System.Data.SqlClient" name="dbConnect"

connectionString="Initial Catalog=Test;

Server=ComputerName\SQLSEVER;

Pooling=Yes;

Integrated Security=True;"/>   

    </connectionStrings>

 

Details:

providerName : name of the data provider for data connection

name : name given to the connection string for accessing in code

connectionString

Initial Catalog=name of the database

Server=Machine name\SQL server ;

Pooling=Yes; (when a new connection is requested by application, if connection is present in pool use it instead of creating new one. Max Pool Size/Min Pool Size can be set i.e. maximum / minimum connections allowed in a Pool)

Integrated Security=True (Windows Authentication)

                                 False (SQL Authentication) 

4.      To access this connection string from our code, we write following :

string  connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnect"].ConnectionString;

 

5.      Complete sample Code to access data :

using System.Data.SqlClient;

using System.Data;

 

public class clsConnect

{

    //Variable Declarations

    string connectionString = "";

    DataSet ds;

      public clsConnect()

      {

        connectionString = System.Configuration.ConfigurationManager.ConnectionStrings  

                                       ["dbConnect"].ConnectionString;

      }

    public DataSet getUserData()

    {

        ds = new DataSet();

        try

        {

            SqlConnection con=new SqlConnection(connectionString);

            con.Open();

            SqlDataAdapter da = new SqlDataAdapter("select * from [dbo].[tbl]", con);

            da.Fill(ds);

            con.Close();

        }

        catch (Exception ex)

        {

                  throw;

 

  }

        return ds;   

    }

}

 

We can use this dataset data to perform several data operations.