DataBase
Step 1: Set Up Your SQL Server Database
1. Create a SQL Server Database:
Open SQL Server Management Studio (SSMS).
Connect to your SQL Server instance.
Right-click on the Databases folder and select New Database .
Name your database and click OK.
2. Create a Table:
In your new database, create a table. For example, create a table named
Users with columns Id, Username , and Password .
CREATE TABLE Users (
Id INT PRIMARY KEY IDENTITY,
Username NVARCHAR(50),
Password NVARCHAR(50)
);
Step 2: Create an ASP.NET Web Forms Application
1. Open Visual Studio:
Create a new project by selecting File -> New -> Project.
2. Select ASP.NET Web Application:
Choose ASP.NET Web Application (.NET Framework) .
3. Select Web Forms Template:
Select the Web Forms template and click Create.
4. Add a Master Page:
Right-click on your project in Solution Explorer.
Select Add -> New Item.
Choose Web Forms Master Page , name it Site1.Master , and click Add.
Step 3: Configure the Connection String
1. Add Connection String to Web.config:
Open the Web.config file in your project.
Add the connection string inside the <configuration> section.
<configuration>
<connectionStrings>
<add name="MyDbConnection"
connectionString="Server=YOUR_SERVER_NAME;Database=YOUR_DATABASE_NA
ME;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
...
</configuration>
Step 4: Create a Data Access Layer (DAL)
1. Add a Data Access Class:
Right-click on your project.
Select Add -> Class and name it DataAccess.cs .
2. Implement Data Access Methods:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public class DataAccess
{
private string connectionString =
ConfigurationManager.ConnectionStrings["MyDbConnection"].ConnectionString;
public DataTable GetUsers()
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
string query = "SELECT * FROM Users";
SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
public void AddUser(string username, string password)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
string query = "INSERT INTO Users (Username, Password) VALUES
(@Username, @Password)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@Username", username);
cmd.Parameters.AddWithValue("@Password", password);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
Step 5: Create Web Forms and Use Master Page
1. Add a Web Form:
Right-click on your project.
Select Add -> Web Form using Master Page .
Name it Default.aspx and select Site1.Master as the master page.
2. Design the Web Form:
Open Default.aspx and add a GridView and a Button to display and add
users.
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
<style type="text/css">
.form-container {
width: 50%;
margin: 0 auto;
padding: 20px;
border: 1px solid #ccc;
border-radius: 8px;
background-color: #f9f9f9;
}
</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1"
runat="server">
<div class="form-container">
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="True"></asp:GridView>
<asp:Button ID="Button1" runat="server" Text="Load Users"
OnClick="Button1_Click" />
</div>
</asp:Content>
3. Code-Behind (Default.aspx.cs):
using System;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
DataAccess dataAccess = new DataAccess();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadUsers();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
LoadUsers();
}
private void LoadUsers()
{
DataTable dt = dataAccess.GetUsers();
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
Step 6: Run the Application
Press F5 to build and run your application.
Your Default.aspx page should load with a GridView that displays users from the
Users table in your SQL Server database.
Summary
1. Setup SQL Server Database: Create the database and table.
2. Create ASP.NET Web Forms Application: Set up the project and master page.
3. Configure Connection String: Add the connection string to Web.config.
4. Data Access Layer (DAL): Implement methods to interact with the database.
5. Web Forms with Master Page: Design and implement forms to interact with the
data.
This process provides a basic example of connecting SQL Server to an ASP.NET Web
Forms application with a master page. You can expand upon this foundation by adding
more features and handling more complex data operations as needed.
