How to use SqlConnection in asp.net c#

SqlConnection

SqlConnection represents an open connection to a SQL Server database. this object represents a unique session to a sql server data source. it works with client/server database system such as Microsoft SQL Server database. sqlconnection used together with SqlDataAdapter and SqlCommand object. SqlDataAdapter represent a set of data commands and a database connection that are used to fill the DataSet and update a sql server database. SqlCommand represents a Transact-SQL-Statement or stored procedure to execute against a sql server database.

We can create an sqlconnection instance by following constructors SqlConnection(), SqlConnection(String) and SqlConnection(String,SqlCredential). SqlConnection() initialize a new instance of SqlConnection class and SqlConnection(String) requires a string that contains the connection string to initialize a new SqlConnection instance.

SqlConnection ConnectionString property get or set the string used to open a database. ConnectionTimeout, Database, Credential are also important properties.

There are many useful methods exists on SqlConnection class such as BeginTransaction(), ClearPool, Close, Open, CreateCommand, Dispose() etc.

We need to close the sql server database connection by calling SqlConnection Close or Dispose. if we want to ensure that connection are always closed we need to open the connection inside of a using block.

Following c# example ado.net code section describe you more about SqlConnection.

SqlConnectionExample.aspx


<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>

<!DOCTYPE html>

<script runat="server">
    protected void Page_Load(object sender, System.EventArgs e) { 
        if(!Page.IsPostBack){
            SqlConnection myConnection;
            SqlCommand myCommand;
            SqlDataAdapter myAdapter;
            DataTable myTable;

            myConnection = new SqlConnection();
            myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

            myCommand = new SqlCommand();
            myCommand.CommandText = "SELECT CategoryID, CategoryName, Description FROM Categories";
            myCommand.CommandType = CommandType.Text;
            myCommand.Connection = myConnection;

            myTable = new DataTable();
            myAdapter = new SqlDataAdapter();
            myAdapter.SelectCommand = myCommand;
            myAdapter.Fill(myTable);

            GridView1.DataSource = myTable.DefaultView;
            GridView1.DataBind();

            myAdapter.Dispose();
            myCommand.Dispose();
            myConnection.Dispose();
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>asp.net SqlConnection example: how to use SqlConnection</title>
</head>
<body>
    <form id="form1" runat="server">
    <h2 style="color:Red;">SqlConnection Example</h2>
    <div>
        <asp:GridView 
            ID="GridView1"
            runat="server"
            BackColor="AliceBlue"
            ForeColor="HotPink"
            HeaderStyle-BackColor="HotPink"
            HeaderStyle-ForeColor="AntiqueWhite"
            BorderColor="LightPink"
            GridLines="Horizontal"
            Font-Italic="true"
            >
        </asp:GridView>
    </div>
    </form>
</body>
</html>

More c# examples