Wednesday, 20 June 2012

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


No comments:

Post a Comment