First ASP.NET Database Program
The following ASP.NET database program is built upon the "pubs" database. In the event that you do not possess the pubs database on your SQL server, please proceed to the provided URL for further guidance and instructions.
http://www.codeproject.com/KB/database/InstallingNorthwindAndPub.aspx
Once you have successfully set up the pubs database on your SQL server, the next step is to create a connection string within your Web.config file.
To create a connection string, you will need to provide the following information:
- SqlServer Name
- Database Name
- Username
- Password
connectionString="Server=yourservername; Database=pubs; User Id=your userid; password=you password"
Once you have gathered the necessary information, you will need to create or modify your web.config file accordingly. Below is an example of the content you can include in your web.config file. Please ensure to fill in the appropriate parameter values based on your specific configuration:
<?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>
Here we are going to connect the database and count the number of rows in the publishers table.
The sql command is : SELECT COUNT(*) FROM publishers
In order to retrieve the connection string value from web.config file , we code like the following :
VB.Net
Dim connectionString As String
connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString
C#
string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();
After we retrieve the connection string , we will create a command object for execute the sql and read the result using SqlDataReader.
VB.Net
Dim command As SqlCommand
Dim dataReader As SqlDataReader
command = New SqlCommand("SELECT COUNT(*) FROM publishers", connection)
dataReader = command.ExecuteReader
C#
SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM publishers", connection);
SqlDataReader dataReader = command.ExecuteReader();
Finally we retrieve the result in a string and show it in a Label control.
VB.Net
Label1.Text = "Rows Count : " & dataReader(0).ToString()
C#
Label1.Text = "Rows Count : " + dataReader[0].ToString();
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();
SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM publishers", connection);
SqlDataReader dataReader = command.ExecuteReader();
dataReader.Read();
Label1.Text = "Rows Count : " + dataReader[0].ToString();
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
Dim command As SqlCommand
Dim dataReader As SqlDataReader
connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString
connection = New SqlConnection(connectionString)
connection.Open()
command = New SqlCommand("SELECT COUNT(*) FROM publishers", connection)
dataReader = command.ExecuteReader
dataReader.Read()
Label1.Text = "Rows Count : " & dataReader(0).ToString()
connection.Close()
End Sub
End Class