Beginning .Net : Working with DataReader with C# Examples and VB.Net Examples

If you need to process the records one by one or iterate the records from database table for that you need to use IDataReader instance. There are various implementation of IDataReader class. We are using System.Data.SqlClient.SqlDataReader class to iterate the records.
The IDataReader interface extends the System.Data.IDataRecord interface. These interfaces declare the functionality to access both the data and the structure of the data contained in the result set.

Here is the example.
In this example we execute two select queries first query is for to get product details and display it's records on screen, and second query is for get employee_master details and display it's table's metadata details on screen.

C# Examples :

        // Create a new SqlConnection object.
        using (SqlConnection objCon = new SqlConnection())
        {
            // Configure the SqlConnection object's connection string.
            objCon.ConnectionString =@"Data Source=JAYESH-PC\SQLEXPRESS;" + 
                                     "Initial Catalog=TempDatabase;" +  
                                     "User ID=sa;Password=ibmx206;";  
            using (SqlCommand objCommand = objCon.CreateCommand())
            {
                objCommand.CommandType = CommandType.Text;
                objCommand.CommandText = "SELECT product_id,product_name FROM product_master " +
                " ORDER BY product_name;SELECT * FROM employee_master";
                
                objCon.Open();
                // Execute the command and obtain a SqlReader.
                using (SqlDataReader objReader = objCommand.ExecuteReader())
                {
                    // Process the first set of results and display the
                    // content of the result set.
                    Response.Write("Prodct details (By Product Name).");

                    while (objReader.Read())
                    {
                        Response.Write(string.Format(" <br/> {0} - {1} ",
                        objReader["product_id"], // Use string index
                        objReader[1])); // Use ordinal index
                    }
                    Response.Write("<br/><br/>");
                    // Process the second set of results and display 
                    objReader.NextResult();
                    Response.Write("Employee Details.");
                    for (int field = 0; field < objReader.FieldCount; field++)
                    {
                        Response.Write(string.Format("<br/><b> Column Name:</b>{0}   <b>Data Type:</b>{1}",
                        objReader.GetName(field),
                        objReader.GetDataTypeName(field)));
                    }
                }
            }
        }

VB.net Examples :

        ' Create a new SqlConnection object.
        Using objCon As New SqlConnection()
            ' Configure the SqlConnection object's connection string.
            objCon.ConnectionString = "Data Source=JAYESH-PC\SQLEXPRESS;" & "Initial Catalog=TempDatabase;" & "User ID=sa;Password=ibmx206;"
            Using objCommand As SqlCommand = objCon.CreateCommand()
                objCommand.CommandType = CommandType.Text
                objCommand.CommandText = "SELECT product_id,product_name FROM product_master " & " ORDER BY product_name;SELECT * FROM employee_master"

                objCon.Open()
                ' Execute the command and obtain a SqlReader.
                Using objReader As SqlDataReader = objCommand.ExecuteReader()
                    ' Process the first set of results and display the
                    ' content of the result set.
                    Response.Write("Prodct details (By Product Name).")

                    While objReader.Read()
                        ' Use string index
                        ' Use ordinal index
                        Response.Write(String.Format(" <br/> {0} - {1} ", objReader("product_id"), objReader(1)))
                    End While
                    Response.Write("<br/><br/>")
                    ' Process the second set of results and display 
                    objReader.NextResult()
                    Response.Write("Employee Details.")
                    For field As Integer = 0 To objReader.FieldCount - 1
                        Response.Write(String.Format("<br/><b> Column Name:</b>{0}   <b>Data Type:</b>{1}", objReader.GetName(field), objReader.GetDataTypeName(field)))
                    Next
                End Using
            End Using
        End Using

Output :

You can access the SqlDataReder objects data using Field name, ordinal index and typed data methods. The typed data contains various methods like GetBoolean, GetByte, GetBytes, GetChar, GetChars, GetDateTime, GetDecimal, GetDouble, GetFloat, GetGuid, GetInt16, GetInt32, GetInt64, GetString, GetValue, and GetValues. Each of the these methods takes an integer argument that identifies the zero-based index of the column from which the data should be returned.

After the use of the data reader, you must have to call its Close method so that you can use the database connection again. IDataReader extends System.IDisposable interface, so that each data reader class implements the Dispose method. Dispose automatically calls Close method.

This article is very useful for .Net Beginners.

Note : Give Us your valuable feedback in comments. Give your suggestions in articles so we can update our articles accordingly that.

Beginning .Net : Use Parameter to Execute SQL query or Stored Procedure on database in C# Examples and VB.Net Examples

This is very managed and safe way to use parameter to execute SQL query and Stored Procedure. By doing this you can avoid sql injection type of problems. We need to set the argument of sql query or  stored procedure to use parameters in a SQL command object.You need to create a parameter object for this.
All parameter objects implement the System.Data.IDataParameter interface.
After that you can configure the parameter object’s values, data types,  and directions, and add them to the command object’s parameter collection using the IDbCommand.Parameters.Add method.

 The ODBC, OLE DB, and SQL Server CE data providers support positional parameters, the location of each argument is identified by a question mark "?".
For example, the below query identifies two locations to be substituted with parameter values:

UPDATE product_master SET product_name= ? WHERE product_id = ?

The SQL Server and Oracle data providers support named parameters, which allow to identify
each parameter location using a name preceded by the at symbol "@".
For example :
UPDATE product_master SET product_name= @product_name=  WHERE product_id = @product_id

To specify the parameter values into a command, we must create parameter objects of the specific type and add them to the command object’s parameter collection accessible through the Parameters property.
Here is example for this.
In this example we take two functions , first function is for to execute sql query and second function is for execute stored procedure.

C# Examples :

    public void ParameterizedCommand(SqlConnection objCon, int product_id, string product_name)
    {
        // Create a new command.
        using (SqlCommand com = objCon.CreateCommand())
        {
            com.CommandType = CommandType.Text;
            com.CommandText = "UPDATE product_master SET product_name = @product_name WHERE product_id = @product_id";
            // Create a SqlParameter object.
            SqlParameter objParam = com.CreateParameter();
            objParam.ParameterName = "@product_name";
            objParam.SqlDbType = SqlDbType.VarChar;
            objParam.Value = product_name;
            com.Parameters.Add(objParam);
            
            com.Parameters.Add("@product_id", SqlDbType.Int).Value = product_id;
            //Execute command
            int result = com.ExecuteNonQuery();
            if (result == 1)
            {
                Response.Write("Product title updated.");
            }
            else
            {
                Response.Write("Product title not updated.");
            }
        }
    }
    public void ParameterizedStoredProcedure(SqlConnection objCon, string product_name)
    {
        // Create and configure a new command.
        using (SqlCommand com = objCon.CreateCommand())
        {
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "sp_product_details_by_product_name";
            // Create a SqlParameter object .
            com.Parameters.Add("@product_id", SqlDbType.VarChar).Value =product_name;
            
            // Execute command.
            using (IDataReader objReader = com.ExecuteReader())
            {
                Response.Write( string.Format( "Product Name ({0}).", product_name));
                while (objReader.Read())
                {
                    // Display the product details.
                    Response.Write( string.Format( " {0} = {1}", objReader["product_id"], objReader["product_name"]));
                }
            }
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {

        // Create a new SqlConnection object.
        using (SqlConnection objCon = new SqlConnection())
        {
            // Configure the SqlConnection object's connection string.
            objCon.ConnectionString = @"Data Source=.\SQLEXPRESS;" + // SQL Server instance , You can also set remote database
                                         "Initial Catalog=TempDatabase;" + // the sample TempDatabase DB
                                         "User ID=sa;Password=ibmx206;";  //Set Credential

            // Open the database connection and execute the example
            // commands through the connection.
            objCon.Open();
            ParameterizedCommand(objCon, 8,"KeyBoard");
            
            ParameterizedStoredProcedure(objCon, "CPU");
            
        };
    }

VB.net Examples :

    Public Sub ParameterizedCommand(ByVal objCon As SqlConnection, ByVal product_id As Integer, ByVal product_name As String)
        ' Create a new command.
        Using com As SqlCommand = objCon.CreateCommand()
            com.CommandType = CommandType.Text
            com.CommandText = "UPDATE product_master SET product_name = @product_name WHERE product_id = @product_id"
            ' Create a SqlParameter object.
            Dim objParam As SqlParameter = com.CreateParameter()
            objParam.ParameterName = "@product_name"
            objParam.SqlDbType = SqlDbType.VarChar
            objParam.Value = product_name
            com.Parameters.Add(objParam)

            com.Parameters.Add("@product_id", SqlDbType.Int).Value = product_id
            'Execute command
            Dim result As Integer = com.ExecuteNonQuery()
            If result = 1 Then
                Response.Write("Product title updated.")
            Else
                Response.Write("Product title not updated.")
            End If
        End Using
    End Sub
    Public Sub ParameterizedStoredProcedure(ByVal objCon As SqlConnection, ByVal product_name As String)
        ' Create and configure a new command.
        Using com As SqlCommand = objCon.CreateCommand()
            com.CommandType = CommandType.StoredProcedure
            com.CommandText = "sp_product_details_by_product_name"
            ' Create a SqlParameter object .
            com.Parameters.Add("@product_id", SqlDbType.VarChar).Value = product_name

            ' Execute command.
            Using objReader As IDataReader = com.ExecuteReader()
                Response.Write(String.Format("Product Name ({0}).", product_name))
                While objReader.Read()
                    ' Display the product details.
                    Response.Write(String.Format(" {0} = {1}", objReader("product_id"), objReader("product_name")))
                End While
            End Using
        End Using
    End Sub
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ' Create a new SqlConnection object.
        Using objCon As New SqlConnection()
            ' Configure the SqlConnection object's connection string.
            objCon.ConnectionString = "Data Source=.\SQLEXPRESS;" & _
                                      "Initial Catalog=TempDatabase;" & _
                                      "User ID=sa;Password=sa;"
            ' Open the database connection and execute the example
            ' commands through the connection.
            objCon.Open()
            ParameterizedCommand(objCon, 8, "KeyBoard")


            ParameterizedStoredProcedure(objCon, "CPU")
        End Using

    End Sub

 You can also directly use SQLParameter class object with SQLCommand and SQLConnection objects.


In this article we are using both Text and StoredProcedure as command type. 

Note : Give Us your valuable feedback in comments. Give your suggestions in articles so we can update our articles accordingly that.

Beginning .Net: Execute SQL query or Stored Procedure on database in C# .Net Programming and VB.Net Programming

 This is very useful article for .Net Beginners Who want to querying database server using programming .
You can create a command object appropriate to the type of database that you use. All command objects implement the System.Data.IDbCommand interface. Configure the command object by setting its CommandType and CommandText properties. Execute the command using the ExecuteNonQuery, ExecuteReader, or ExecuteScalar method, depending on the type of command and its expected results.

The IDbCommand interface represents a database command, and each data provider includes a unique implementation.
Here is the list of IDbCommand implementations for some standard data providers:

• System.Data.SqlClient.SqlCommand
• System.Data.Odbc.OdbcCommand
• System.Data.OleDb.OleDbCommand
• System.Data.OracleClient.OracleCommand
• System.Data.SqlServerCe.SqlCeCommand

To execute a command on a database, you must have an open connection and a properly configured command object appropriate to the type of database you are accessing.

Here are some common command object properties.

Property	Description
CommandText	Command text contains SQL command or stored procedure name to execute. The content of the CommandText property must be compatible with the value you specify in the CommandType property.
CommandTimeout	An integer that specifies the number of seconds to wait for the command to return before timing out and raising an exception. Defaults is 30 seconds.
CommandType	A value of the System.Data.CommandType enumeration that specifies the type of command represented by the command object.For stored procedure you specify StoredProcedure and when you want to execute a SQL text command than you specify Text value. If you are using the OLE DB data provider, you can specify TableDirect when you want to return the entire contents of one or more tables.
Connection	An IDbConnection instance that provides the connection to the database on which you will execute the command. If you create the command using the IDbConnection.CreateCommand method, this property will be automatically set to the IDbConnection instance from which you created the command. You can also create connection object of specific connection class like SQLConnection.
Parameters	A System.Data.IDataParameterCollection instance containing the set of parameters to substitute into the command.
Transaction	A System.Data.IDbTransaction instance representing the transaction into which to enlist the command.

Once you have configured your command object details, you can execute it in a various ways, depending on the nature of the command, the type of data returned by the command, and the format in which you want to process the data.

• To execute a command that does not return database data Like INSERT, DELETE, or CREATE TABLE etc.. , call ExecuteNonQuery. For the INSERT, UPDATE and DELETE commands, the ExecuteNonQuery method returns an int that specifies the number of rows affected by the command. For other commands, like CREATE TABLE, ExecuteNonQuery returns the value -1.
• To execute a command that returns a result set, such as a SELECT statement or stored procedure, use the ExecuteReader or other DataAdapter method. ExecuteReader returns an IDataReader instance through which you have access to the result data. Most data providers also allow you to execute multiple SQL commands in a single call to the ExecuteReader method. 
• If you want to execute a query but only need the value from the first column of the first row of result data, use the ExecuteScalar method. The value is returned as an object reference that you must cast to the correct type.

Here are example of ExecuteNonQuery , ExecuteReader and ExecuteScalar Methods.

C# Example :

    public void ExecuteNonQueryOnDatabase(IDbConnection objCon)
    {
        // Create and configure a new command.
        IDbCommand objCom = objCon.CreateCommand();
        objCom.CommandType = CommandType.Text;
        /////Create Update command for update product name of product_id = 8
        objCom.CommandText = "UPDATE product_master SET product_name = 'KeyBoard'" +
        " WHERE Product_id = 8";
        // Execute the command and process the result.
        int result = objCom.ExecuteNonQuery();
        if (result == 1)
        {
            Response.Write("Product title updated.");
        }
        else
        {
             Response.Write("Product title not updated.");
        }
    }
    public void ExecuteReaderOnDatabase(IDbConnection objCon)
    {
        // Create and configure a new command.
        IDbCommand objCom = objCon.CreateCommand();
        objCom.CommandType = CommandType.StoredProcedure;
        objCom.CommandText = "SP_GET_ALL_PRODUCTS";
      

        // Execute the command and process the results using reader.
        using (IDataReader reader = objCom.ExecuteReader())
        {
            Response.Write("Get All Products.");
            while (reader.Read())
            {
                // Display the product details.
                 Response.Write(reader["product_id"] + " = " + reader["product_name"]);
            }
        }
    }
    public  void ExecuteScalarOnDatabase(IDbConnection objCon)
    {
        // Create and configure a new command.
        IDbCommand objCom = objCon.CreateCommand();
        objCom.CommandType = CommandType.Text;
        objCom.CommandText = "SELECT COUNT(*) FROM product_master";
        // Execute the command and cast the result.
        int result = (int)objCom.ExecuteScalar();
         Response.Write("Product count = " + result);
    }
    protected void Page_Load(object sender, EventArgs e)
    {

        // Create a new SqlConnection object.
        using (SqlConnection objCon = new SqlConnection())
        {
            // Configure the SqlConnection object's connection string.
            objCon.ConnectionString =@"Data Source=.\SQLEXPRESS;" + // SQL Server instance , You can also set remote database
                                         "Initial Catalog=TempDatabase;" + // the sample TempDatabase DB
                                         "User ID=sa;Password=sa;";  //Set Credential
                                 
            // Open the database connection and execute the example
            // commands through the connection.
            objCon.Open();
            ExecuteNonQueryOnDatabase(objCon);
            Response.Write(Environment.NewLine);
            ExecuteReaderOnDatabase(objCon);
            Response.Write(Environment.NewLine);
            ExecuteScalarOnDatabase(objCon);
        };
    }

VB.net Example :

    Public Sub ExecuteNonQueryOnDatabase(ByVal objCon As IDbConnection)
        ' Create and configure a new command.
        Dim objCom As IDbCommand = objCon.CreateCommand()
        objCom.CommandType = CommandType.Text
        ''''Create Update command for update product name of product_id = 8
        objCom.CommandText = "UPDATE product_master SET product_name = 'KeyBoard'" & " WHERE Product_id = 8"
        ' Execute the command and process the result.
        Dim result As Integer = objCom.ExecuteNonQuery()
        If result = 1 Then
            Response.Write("Product title updated.")
        Else
            Response.Write("Product title not updated.")
        End If
    End Sub
    Public Sub ExecuteReaderOnDatabase(ByVal objCon As IDbConnection)
        ' Create and configure a new command.
        Dim objCom As IDbCommand = objCon.CreateCommand()
        objCom.CommandType = CommandType.StoredProcedure
        objCom.CommandText = "SP_GET_ALL_PRODUCTS"

        ' Execute the command and process the results using reader.
        Using reader As IDataReader = objCom.ExecuteReader()
            Response.Write("Get All Products.")
            While reader.Read()
                ' Display the product details.
                Response.Write(reader("product_id") + " = " + reader("product_name"))
            End While
        End Using
    End Sub
    Public Sub ExecuteScalarOnDatabase(ByVal objCon As IDbConnection)
        ' Create and configure a new command.
        Dim objCom As IDbCommand = objCon.CreateCommand()
        objCom.CommandType = CommandType.Text
        objCom.CommandText = "SELECT COUNT(*) FROM product_master"
        ' Execute the command and cast the result.
        Dim result As Integer = CInt(objCom.ExecuteScalar())
        Response.Write("Product count = " & result)
    End Sub


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ' Create a new SqlConnection object.
        Using objCon As New SqlConnection()
            ' Configure the SqlConnection object's connection string.
            ' SQL Server instance , You can also set remote database
            ' the sample TempDatabase DB
            objCon.ConnectionString = "Data Source=.\SQLEXPRESS;" & _
                "Initial Catalog=TempDatabase;" & _
                "User ID=sa;Password=sa;" 
            objCon.Open()
            ExecuteNonQueryOnDatabase(objCon)
            Response.Write(Environment.NewLine)
            ExecuteReaderOnDatabase(objCon)
            Response.Write(Environment.NewLine)
            ExecuteScalarOnDatabase(objCon)
        End Using

    End Sub

In this article we are using both Text and StoredProcedure as command type. 

Note : Give Us your valuable feedback in comments. Give your suggestions in articles so we can update our articles accordingly that.

Beginning .Net : Store and Retrieve database connection string in C# .Net Programming and VB.Net Programming

As a .Net beginners you need to know about how and where to store database connection string and how to retrieve this database connection string.
Store database connection string in Web.Config file is a good way.
In Web.Config file there is "<configuration>" section , In this section there is "<connectionStrings>" section. In this section you can add your connection string. You can add connection string using add tag.

Here are sample example of web.config file in which we store connection string.

Web.Config file :

<configuration>
    <connectionStrings>
        <add name="NorthWindConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;;User ID=sa;Password=sa;" providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

In this we can set the connection string name and connection string values and provider name. In this example we set name as "NorthWindConnectionString" and provider as "System.Data.SqlClient" for SQL Sever. You can set different provider for different database servers ie ORACLE , SYBASE etc...

Now we are retrivieing this stored database connection string in our code. The easiest way to read this connection string is to use the ConnectionStrings property of the System.Configuration.ConfigurationManager class. Specify the name of the connection string you want as the property index will return a System.Configuration.ConnectionStringSettings object. You can use ConnectionString property of System.Configuration.ConnectionStringSettings object.

Here are example of read database connection string from web.config into server side code.

C# Example :

        System.Configuration.ConnectionStringSettings objConnSetting= System.Configuration.ConfigurationManager.ConnectionStrings["NorthWindConnectionString"];
        string conString = objConnSetting.ConnectionString;
        SqlConnection objConn = new SqlConnection();
        objConn.ConnectionString = objConnSetting.ConnectionString;
        objConn.Open();
        /////Do you database stuff
        objConn.Close();

VB.net Example :

        Dim objConnSetting As System.Configuration.ConnectionStringSettings = System.Configuration.ConfigurationManager.ConnectionStrings("NorthWindConnectionString")
        Dim conString As String = objConnSetting.ConnectionString

        Dim objConn As New SqlConnection()
        objConn.ConnectionString = objConnSetting.ConnectionString
        objConn.Open()
        ''''Do you database stuff
        objConn.Close()

.Net Beginners , C# Tips : Optional and Named Parameters in C#

.Net 4.0 Framework provide Optional and Named Parameters in C# also. Previously it was supported in VB.Net.
Now you do not need to write unnecessary overload methods , With the help of the Optional method you can achieve this.

Here is example of this.

C# Example :

    void DisplayString(int id, string title = "Information", string message = "No Message")
    {
        Response.Write("ID : " +  Convert.ToString( id));
        Response.Write("<br/>Title : " + title);
        Response.Write("<br/>Message : " + message);

    }
    protected void Page_Load(object sender, EventArgs e)
    {
        DisplayString(10, message: "Hi");
    }

In this example we create one method name "DisplayString" this methods has three parameters , First is Id , Second is Title , This parameter has default value "Information" and Third parameter message , This parameter has also default value "No Message". Now if we called this method with this parameters:

DisplayString(10, message: "Hi"); 

It will display output like this.
Output : 

In this output we pass id as 10 , we do not pass second argument so it takes its default value "Information" , and as second parameter we set name of parameter message , so this parameter property do not take it's default values , it takes pass value "Hi".

.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.

.Net Beginners , C# Tips : How to connect database using C#.Net and VB.Net

You need to open a connection to a database on regular bases for database operations.
For that you need to create a connection object.
The IDbConnection interface represents a database connection, and each data provider implement interface uniquely.
Here is the list of some IDbConnection implementations for standard data providers:

• System.Data.SqlClient.SqlConnection
• System.Data.SqlServerCe.SqlCeConnection
• System.Data.Odbc.OdbcConnection
• System.Data.OleDb.OleDbConnection
• System.Data.OracleClient.OracleConnection

You need to configure a connection string property of connection object.
You need to provide certain things in connection string property like , Server address , authentication details etc...
Different data providers has different methodology.

Here is example of this.
In this example we connect to SQL Server using SqlConnection class , this class is available in System.Data.SqlClient namespace, and display SQL Server Information. After the utilization of open connection you must have to close this connection using Close method otherwise the connection remain open.

C# Example :

        SqlConnection con = new SqlConnection();
        con.ConnectionString =
                                @"Data Source=.\SQLEXPRESS;" + // SQL Server instance , You can also set remote database
                                 "Initial Catalog=Northwind;" + // the sample Northwind DB
                                 "User ID=sa;Password=sa;";  //Set Credential
                                 
        // Open the database connection.
        con.Open();

        if (con.State == ConnectionState.Open) //Check database connection state
        {
            Response.Write("<b>SqlConnection Information:</b>");
            Response.Write(" <br/> Connection State = " + con.State);
            Response.Write("<br/> Connection String = " + con.ConnectionString);
            Response.Write("<br/> Database Source = " + con.DataSource);
            Response.Write("<br/> Database = " + con.Database);
            Response.Write("<br/> Server Version = " + con.ServerVersion);
            Response.Write("<br/> Workstation Id = " + con.WorkstationId);
            Response.Write("<br/> Timeout = " + con.ConnectionTimeout);
            Response.Write("<br/> Packet Size = " + con.PacketSize);
        }
        else
        {
            Response.Write("SqlConnection failed to open.");
            Response.Write(" Connection State = " + con.State);
        }
        con.Close();
        con.Dispose();

VB.net Example :

        Dim con As New SqlConnection()
        
        con.ConnectionString = "Data Source=.\SQLEXPRESS;" & _
                               "Initial Catalog=Northwind;" & _
                               "User ID=sa;Password=ibmx206;"

        ' Open the database connection.
        con.Open()

        If con.State = ConnectionState.Open Then 'Check database connection state
            Response.Write("SqlConnection Information:")
            Response.Write(" <br/> Connection State = " & con.State)
            Response.Write("<br/> Connection String = " & con.ConnectionString)
            Response.Write("<br/> Database Source = " & con.DataSource)
            Response.Write("<br/> Database = " & con.Database)
            Response.Write("<br/> Server Version = " & con.ServerVersion)
            Response.Write("<br/> Workstation Id = " & con.WorkstationId)
            Response.Write("<br/> Timeout = " & con.ConnectionTimeout)
            Response.Write("<br/> Packet Size = " & con.PacketSize)
        Else
            Response.Write("SqlConnection failed to open.")
            Response.Write(" Connection State = " & con.State)
        End If
        con.Close()
        con.Dispose()

Here is output of this code.
Output : 

(To view original image , click on image)