ASP.NET Sql Server Connection

By: Rajesh P.S.

The SqlConnection object plays a vital role in facilitating the physical communication between an ASP.NET application and a SQL Server database. It serves as a crucial component within the Data Provider for SQL Server Database, enabling seamless data exchange between the application and the database.

SqlConnection class

When an instance of the SqlConnection class is created in an ASP.NET application, it establishes a reliable and secure connection to the SQL Server database. This connection acts as a conduit for executing SQL commands and queries, allowing the application to retrieve or manipulate data stored in the database. The execution of SQL commands is made possible with the assistance of the Command Object, which works in conjunction with the SqlConnection to carry out database operations.

VB.Net

Dim connectionString As String connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString

string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();

Close() method

Once the desired database activities, such as data retrieval or modification, are completed, it is essential to properly close the SqlConnection. The Close() method provided by the SqlConnection class serves this purpose, allowing for the termination of the database connection. When the Close() method is invoked, any pending transactions are rolled back, ensuring data integrity, and the connection is released from the SQL Server database. By closing the connection, valuable data source resources are freed, promoting efficient resource management within the application.

The following ASP.NET program connect to a database server and display the message in the Label control.

web.config

<?xml version="1.0"?> <configuration> <connectionStrings> <add name="SQLDbConnection" connectionString="Server=servername; Database=pubs; User Id=username; password=password" providerName="System.Data.SqlClient" /> </connectionStrings> </configuration>

You have to fill appropriate web.config database parameters

Default.aspx

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" /> </div> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> </form> </body> </html>

Full Source | C#

using System; using System.Data ; using System.Data.SqlClient ; using System.Configuration; public partial class _Default : System.Web.UI.Page { protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString(); SqlConnection connection = new SqlConnection(connectionString); connection.Open(); Label1.Text = "Connected to Database Server !!"; connection.Close(); } }

Full Source | VB.NET

Imports System.Data Imports System.Data.SqlClient Imports System.Configuration Partial Class _Default Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connectionString As String Dim connection As SqlConnection connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString connection = New SqlConnection(connectionString) connection.Open() Label1.Text = "Connected to Database Server !!" connection.Close() End Sub End Class

Conclusion

The SqlConnection object serves as a critical component in establishing and managing the communication between an ASP.NET application and a SQL Server database. It enables the execution of SQL commands and queries, facilitating the retrieval and manipulation of data. Once the necessary database operations are completed, the Close() method should be called to close the database connection, rollback any pending transactions, and release the database resources, promoting efficient and secure data access within the application.

Next > ASP.NET OLEDB Connection

Related Topics

More Related Topics.....