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 systems such as Microsoft SQL Server database. SqlConnection is used together with SqlDataAdapter and SqlCommand object.
SqlDataAdapter represents 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 a SqlConnection instance by following constructors SqlConnection(), SqlConnection(String), and SqlConnection(String, SqlCredential). SqlConnection() initializes 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 gets or sets the string used to open a database. ConnectionTimeout, Database, and Credential are also important properties.
There are many useful methods that exist in 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 the connection is always closed we need to open the connection inside of a using block.
The following C# example ado.net code section describes 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>
