Thursday 21 June 2012

.Net Beginners , C# Tips : Create database connection string using ConnectionStringBuilder

You can create database connection string using ConnectionStringBuilder class.
You can use the System.Data.Common.DbConnectionStringBuilder class or one of its strongly typed subclasses that form part of an ADO.NET data provider.
Here is the list of available DbConnectionStringBuilder implementations for standard data providers:
  • System.Data.Odbc.OdbcConnectionStringBuilder
  • System.Data.OleDb.OleDbConnectionStringBuilder
  • System.Data.OracleClient.OracleConnectionStringBuilder
  • System.Data.SqlClient.SqlConnectionStringBuilder
Each of these classes exposes properties for getting and setting the parameters for a connection string . To parse an existing connection string, pass it as an argument when creating the DbConnectionStringBuilder derived class, or set the ConnectionString property. If this string contains a keyword not supported by the type of connection, an ArgumentException will be thrown.

Here is example of this.
In this example we parse one connection string using SqlConnectionStringBuilder class and also we create other connection programmatically by setting SqlConnectionStringBuilder object's properties.

C# Example :
        string conString = @"Data Source=.\sqlexpress;" +
                            "Database=Northwind;Integrated Security=SSPI;" +
                            "Min Pool Size=5;Max Pool Size=15;Connection Reset=True;" +
                            "Connection Lifetime=600;";
        // Parse the SQL Server connection string and display it's properties
        
        SqlConnectionStringBuilder objSB1 = new SqlConnectionStringBuilder(conString);
        Response.Write("<b>Parsed SQL Connection String Parameters:</b>");
        Response.Write(" <br/>  Database Source = " + objSB1.DataSource);
        Response.Write(" <br/>  Database = " + objSB1.InitialCatalog);
        Response.Write(" <br/>  Use Integrated Security = " + objSB1.IntegratedSecurity);
        Response.Write(" <br/>  Min Pool Size = " + objSB1.MinPoolSize);
        Response.Write(" <br/>  Max Pool Size = " + objSB1.MaxPoolSize);
        Response.Write(" <br/>  Lifetime = " + objSB1.LoadBalanceTimeout);

        // Set properties of string builder object and display whole connection string using ConnectionString property.
        SqlConnectionStringBuilder objSB2 =new SqlConnectionStringBuilder(conString);
        objSB2.DataSource = @".\sqlexpress";
        objSB2.InitialCatalog = "Northwind";
        objSB2.IntegratedSecurity = true;
        objSB2.MinPoolSize = 5;
        objSB2.MaxPoolSize = 15;
        objSB2.LoadBalanceTimeout = 600;
        Response.Write(Environment.NewLine);
        Response.Write("<br/><br/><b>Constructed connection string:</b>");
        Response.Write(" <br/>  " + objSB2.ConnectionString);

VB.net Example :
        Dim conString As String = "Data Source=.\sqlexpress;" & _
                                "Database=Northwind;Integrated Security=SSPI;" & _
                                "Min Pool Size=5;Max Pool Size=15;Connection Reset=True;" & _
                                "Connection Lifetime=600;"

        ' Parse the SQL Server connection string and display it's properties
        Dim objSB1 As New SqlConnectionStringBuilder(conString)
        Response.Write("<b>Parsed SQL Connection String Parameters:</b>")
        Response.Write(" <br/>  Database Source = " + objSB1.DataSource)
        Response.Write(" <br/>  Database = " + objSB1.InitialCatalog)
        Response.Write(" <br/>  Use Integrated Security = " + objSB1.IntegratedSecurity)
        Response.Write(" <br/>  Min Pool Size = " + objSB1.MinPoolSize)
        Response.Write(" <br/>  Max Pool Size = " + objSB1.MaxPoolSize)
        Response.Write(" <br/>  Lifetime = " + objSB1.LoadBalanceTimeout)

        ' Set properties of string builder object and display whole connection string using ConnectionString property.
        Dim objSB2 As New SqlConnectionStringBuilder(conString)
        objSB2.DataSource = ".\sqlexpress"
        objSB2.InitialCatalog = "Northwind"
        objSB2.IntegratedSecurity = True
        objSB2.MinPoolSize = 5
        objSB2.MaxPoolSize = 15
        objSB2.LoadBalanceTimeout = 600
        Response.Write(Environment.NewLine)
        Response.Write("<br/><br/><b>Constructed connection string:</b>")
        Response.Write(" <br/>  " + objSB2.ConnectionString)

After the connection builder object build you get whole connection string using ConnectionString property and set this connection to SQLConnection object to connect to the database.


No comments:

Post a Comment