Unit 3 Performing Data Access

Creating Templates and Data Binding Expressions:

In ASP.NET, templates and data binding expressions are essential for creating dynamic web pages
and displaying data from your data sources (like a database or an API). Here's an overview of
creating templates and using data binding expressions in ASP.NET Web Forms and ASP.NET MVC.

### 1. **Data Binding in ASP.NET Web Forms**

In ASP.NET Web Forms, data binding is typically done within controls like `GridView`, `Repeater`,
`ListView`, and `DropDownList`. These controls allow you to bind data from a data source (like a
database or a collection) to the control itself.

#### Example of Data Binding with a `Repeater` Control

```aspx
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<p><%# Eval("Name") %> - <%# Eval("Age") %></p>
</ItemTemplate>
</asp:Repeater>
```

In the above example:

- The `Eval()` function is used to bind data from a data source to the control.
- You use the `ItemTemplate` to define the structure of each item in the data.

#### Binding Data in Code-Behind

```csharp
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// Sample data source
var dataSource = new List<Person>
{
new Person { Name = "John", Age = 30 },
new Person { Name = "Alice", Age = 25 },
};

Repeater1.DataSource = dataSource;
Repeater1.DataBind();
}
}
```

In this case, the `Repeater` is bound to a list of `Person` objects in the code-behind.
### 2. **Data Binding in ASP.NET MVC**
In ASP.NET MVC, data binding is typically done through **Razor Views**. The controller passes the
model data to the view, and the data is displayed in the view using Razor syntax.

#### Example of Data Binding with Razor Syntax in a View

```csharp
@model List<Person>

@foreach (var person in Model)

{
<p>@person.Name - @person.Age</p>
}
```

In this example:
- The `@model` directive at the top of the Razor view specifies that the view will work with a
`List<Person>` model.
- Inside the `foreach` loop, `@person.Name` and `@person.Age` display the values from each person
in the list.

#### Controller Action

```csharp
public ActionResult Index()
{
var people = new List<Person>
{
new Person { Name = "John", Age = 30 },
new Person { Name = "Alice", Age = 25 },
};

return View(people);
}
```

Here, the `Index` action in the controller creates a list of `Person` objects and returns it to the view.

### 3. **Using Templates in ASP.NET Web Forms**

In ASP.NET Web Forms, templates allow you to define the layout and format for the data displayed
by a control like `GridView` or `ListView`.

#### Example: Defining Templates in a `ListView`

```aspx
<asp:ListView ID="ListView1" runat="server">
<ItemTemplate>
 <div class="item">
<h3><%# Eval("Name") %></h3>
<p><%# Eval("Description") %></p>
</div>
</ItemTemplate>
<EmptyDataTemplate>
<p>No items available.</p>
</EmptyDataTemplate>
</asp:ListView>
```

In the above example:

- The `ItemTemplate` defines the structure of each list item.
- The `EmptyDataTemplate` is shown if the data source is empty.

### 4. **Advanced Data Binding with Expressions**

You can use various expressions to format data in a more sophisticated way.

#### Example: Formatting Date and Currency in Web Forms

```aspx
<asp:Label ID="Label1" runat="server" Text='<%# Eval("DateOfBirth", "{0:MM/dd/yyyy}")
%>'></asp:Label>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("Price", "{0:C}") %>'></asp:Label>
```

Here, the `Eval` function is used to bind data, and format strings (`{0:MM/dd/yyyy}` and `{0:C}`) are
used to format the data (date and currency, respectively).

### 5. **Using Data Annotations for Validation in MVC**

In ASP.NET MVC, data annotations allow you to perform validation on the model before binding it to
a view.

#### Example of Data Annotations

```csharp
public class Person
{
[Required]
public string Name { get; set; }

[Range(1, 120)]
public int Age { get; set; }
}
```

These data annotations are used to specify validation rules. When you bind this model to a form,
ASP.NET MVC will automatically validate the fields based on these annotations.
Using SqldataSource Control:
The `SqlDataSource` control in ASP.NET Web Forms is used to connect a web page to a SQL Server
database. It provides a declarative way of accessing data, performing CRUD (Create, Read, Update,
Delete) operations, and binding data to controls such as `GridView`, `DropDownList`, `Repeater`, and
others.

### Basic Usage of `SqlDataSource` Control

The `SqlDataSource` control allows you to define the connection string and the SQL commands
(SELECT, INSERT, UPDATE, DELETE) directly in the markup, without the need for writing code in the
code-behind file.

Here’s a step-by-step guide on using the `SqlDataSource` control in your ASP.NET Web Forms page.

### 1. **Defining the SqlDataSource Control**

The `SqlDataSource` control needs a few key properties:

- **ConnectionString**: This defines the connection to the SQL Server database.
- **SelectCommand**: The SQL query or stored procedure used to retrieve data.
- **InsertCommand, UpdateCommand, DeleteCommand**: Optional, these are used to perform
insert, update, and delete operations.

### Example of a Basic `SqlDataSource` with a `GridView`:

```aspx
<asp:SqlDataSource
ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT Id, Name, Age FROM People"
UpdateCommand="UPDATE People SET Name = @Name, Age = @Age WHERE Id = @Id"
DeleteCommand="DELETE FROM People WHERE Id = @Id">
<UpdateParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Age" Type="Int32" />
<asp:Parameter Name="Id" Type="Int32" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="Id" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
```

### 2. **Binding SqlDataSource to a GridView**

After defining the `SqlDataSource`, it can be easily bound to a control like `GridView`. The `GridView`
will automatically generate columns based on the data retrieved by the `SqlDataSource`'s
`SelectCommand`.

```aspx
<asp:GridView
ID="GridView1"
runat="server"
AutoGenerateColumns="True"
DataSourceID="SqlDataSource1"
Width="600px"
AllowPaging="True">
</asp:GridView>
```

In this example:
- `DataSourceID` links the `GridView` to the `SqlDataSource` control.
- `AutoGenerateColumns="True"` means that the columns are generated automatically based on the
data source.

### 3. **Using Parameters in SqlDataSource**

You can use parameters to filter or manipulate the data. Parameters are used to protect against SQL
injection and allow for more dynamic queries.

#### Example: Using Parameters for Filtering Data

```aspx
<asp:SqlDataSource
ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT Id, Name, Age FROM People WHERE Age > @Age">
<SelectParameters>
<asp:Parameter Name="Age" Type="Int32" DefaultValue="30" />
</SelectParameters>
</asp:SqlDataSource>
```

In this case:
- The `SelectCommand` filters the `People` table to return only people older than a specified `Age`.
- The `SelectParameters` block defines a parameter (`Age`) that is passed into the query.

### 4. **Insert, Update, and Delete Operations**

You can use the `SqlDataSource` control to handle data manipulation like inserting, updating, and
deleting records.
#### Example: Insert Operation Using SqlDataSource

```aspx
<asp:SqlDataSource
ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
InsertCommand="INSERT INTO People (Name, Age) VALUES (@Name, @Age)">
<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Age" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
```

This `SqlDataSource` is set up to insert data into the `People` table. You can bind it to a form where
users enter `Name` and `Age`.

#### Example: Using `GridView` for Edit and Delete

```aspx
<asp:GridView
ID="GridView1"
runat="server"
DataSourceID="SqlDataSource1"
AutoGenerateColumns="False"
AllowPaging="True"
AllowSorting="True"
ShowFooter="True">
<Columns>
<asp:BoundField DataField="Id" HeaderText="ID" SortExpression="Id" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Age" HeaderText="Age" SortExpression="Age" />
<asp:CommandField ShowEditButton="True" ShowDeleteButton="True" />
</Columns>
</asp:GridView>
```

In this example:
- The `GridView` displays records from the `People` table.
- Users can edit or delete rows directly from the `GridView`.

### 5. **Handling SQL Errors and Exceptions**

You can handle errors in SQL commands using the `OnError` event or by setting
`EnableCaching="false"` if you don't want data caching.

For example, in the case of an error, you could log it or display a user-friendly message.
### 6. **Binding to Other Controls (DropDownList, ListBox)**

You can also bind the `SqlDataSource` to other controls such as `DropDownList` or `ListBox`.

#### Example: Binding SqlDataSource to a DropDownList

```aspx
<asp:DropDownList
ID="DropDownList1"
runat="server"
DataSourceID="SqlDataSource1"
DataTextField="Name"
DataValueField="Id">
</asp:DropDownList>
```

In this example:
- `DropDownList1` will show the `Name` column from the `People` table and use the `Id` as the
value.

### 7. **Caching Data Using SqlDataSource**

The `SqlDataSource` control supports caching, which can improve performance by storing results in
memory for a specified amount of time.

#### Example: Caching the Results

```aspx
<asp:SqlDataSource
ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT Id, Name, Age FROM People"
EnableCaching="True"
CacheDuration="60" />
```

Here:
- `EnableCaching="True"` enables caching.
- `CacheDuration="60"` specifies that the data will be cached for 60 seconds.

Creating Database Connections:

Creating a database connection in ASP.NET typically involves using a connection string to connect to
a database, usually SQL Server. The connection string specifies the server, database, and
authentication method used to access the database.
Here’s a step-by-step guide to setting up a database connection in ASP.NET.

### 1. **Setting Up the Connection String**

A connection string is stored in the `web.config` file (for Web Forms and MVC projects). It contains
the information necessary to establish a connection to your database.

Here’s how to add a connection string in `web.config`:

#### Example of a Connection String in `web.config`:

```xml
<configuration>
<connectionStrings>
<add name="MyConnectionString"
connectionString="Server=localhost;Database=MyDatabase;User
Id=myuser;Password=mypassword;"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
```

In this example:
- `Server`: The name or IP address of the SQL Server.
- `Database`: The name of the database to which you're connecting.
- `User Id`: The username used for authentication.
- `Password`: The password for the provided username.
- `providerName`: Specifies which data provider to use (`System.Data.SqlClient` for SQL Server).

You can use other authentication methods like **Windows Authentication** by changing the
connection string.

#### Example of Windows Authentication:

```xml
<configuration>
<connectionStrings>
<add name="MyConnectionString"
connectionString="Server=localhost;Database=MyDatabase;Integrated Security=True;"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
```

In this case, `Integrated Security=True` uses the credentials of the current Windows user to
authenticate with the database.
### 2. **Connecting to the Database in Code**

Once the connection string is set up in `web.config`, you can use it in your ASP.NET code. Here's how
to establish a connection and retrieve data from a SQL Server database.

#### Example: Connecting and Retrieving Data from SQL Server

##### Using ADO.NET in Code-Behind:

In your `Page_Load` or in a separate method, you can connect to the database and fetch data using
ADO.NET.

```csharp
using System;
using System.Data.SqlClient;
using System.Web.UI;

public partial class Default : Page

{
protected void Page_Load(object sender, EventArgs e)
{
string connectionString =
System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();

string query = "SELECT Id, Name, Age FROM People";

SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
Response.Write("ID: " + reader["Id"] + "<br>");
Response.Write("Name: " + reader["Name"] + "<br>");
Response.Write("Age: " + reader["Age"] + "<br>");
}
}
catch (Exception ex)
{
Response.Write("Error: " + ex.Message);
}
}
}
}
```
- **SqlConnection**: Opens a connection to the database.
- **SqlCommand**: Executes the SQL query.
- **SqlDataReader**: Reads the data returned by the query.

In the example, `connection.Open()` opens the database connection, and `ExecuteReader()` executes
the query and retrieves the data.

### 3. **Using `SqlDataSource` Control for Declarative Data Binding**

In Web Forms, you can avoid writing ADO.NET code by using the `SqlDataSource` control, which
allows for a declarative approach to database connections and queries.

#### Example: SqlDataSource Control in a Web Form

```aspx
<asp:SqlDataSource
ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT Id, Name, Age FROM People">
</asp:SqlDataSource>

<asp:GridView
ID="GridView1"
runat="server"
AutoGenerateColumns="True"
DataSourceID="SqlDataSource1">
</asp:GridView>
```

In this example:
- The `SqlDataSource` control is used to define the connection string (`ConnectionString`), the SQL
command (`SelectCommand`), and any parameters.
- The `GridView` is bound to the `SqlDataSource` using the `DataSourceID` property.

### 4. **Using Entity Framework (EF) for Data Access**

If you're working with a more complex database model or want to take advantage of ORM (Object-
Relational Mapping), you can use **Entity Framework** (EF) in ASP.NET to handle database
connections and data access.

#### Example: Using Entity Framework in ASP.NET Web Forms

1. **Install Entity Framework** via NuGet:

```bash
Install-Package EntityFramework
```

2. **Define the Model**:

Create a class that corresponds to a table in your database.

```csharp
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
```

3. **Define the DbContext**:

```csharp
using System.Data.Entity;

public class MyDbContext : DbContext

{
public DbSet<Person> People { get; set; }
}
```

4. **Connecting and Querying Data**:

```csharp
using (var context = new MyDbContext())
{
var people = context.People.ToList();
foreach (var person in people)
{
Response.Write("Name: " + person.Name + " Age: " + person.Age + "<br>");
}
}
```

In this case:
- `MyDbContext` is your context class that maps to your database.
- You can use LINQ queries (like `ToList()`) to retrieve and manipulate data.

### 5. **Best Practices for Database Connections**

- **Use Connection Pooling**: ADO.NET automatically handles connection pooling, which improves
performance by reusing existing connections rather than opening a new one every time.
- **Use `using` Statement**: Always wrap your database connections in a `using` statement to
ensure that the connection is properly closed and disposed of, even if an error occurs.
- **Avoid Hardcoding Credentials**: Store sensitive information, such as database passwords,
securely in your configuration files (like `web.config`), and use **Windows Authentication** when
possible.
Executing database commands in ASP.NET:
Executing database commands in ASP.NET can be done using **ADO.NET**, **SqlDataSource**, or
**Entity Framework**. I'll walk you through different approaches to execute SQL commands (e.g.,
SELECT, INSERT, UPDATE, DELETE) in a database.

### 1. **Executing SQL Commands Using ADO.NET**

ADO.NET allows you to execute SQL commands like `SELECT`, `INSERT`, `UPDATE`, and `DELETE`
directly. Here's how to do it using `SqlConnection`, `SqlCommand`, and `SqlDataReader` for `SELECT`
queries, and `ExecuteNonQuery` for `INSERT`, `UPDATE`, and `DELETE`.

#### 1.1 **Executing a SELECT Command**

To retrieve data from the database, you can use a `SELECT` command with a `SqlDataReader`.

```csharp
using System;
using System.Data.SqlClient;

public class DatabaseOperations

{
public void ExecuteSelectCommand()
{
string connectionString = "your_connection_string_here"; // Replace with your connection
string
string query = "SELECT Id, Name, Age FROM People";

using (SqlConnection conn = new SqlConnection(connectionString))

{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);

// Execute the command and retrieve data using SqlDataReader

SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("ID: " + reader["Id"] + ", Name: " + reader["Name"] + ", Age: " +
reader["Age"]);
}
}
catch (Exception ex)
 {
Console.WriteLine("Error: " + ex.Message);
}
}
}
}
```

- **`ExecuteReader`** is used for SELECT queries and returns a `SqlDataReader` to read the data
row by row.

#### 1.2 **Executing an INSERT, UPDATE, or DELETE Command**

For commands that modify the database (such as `INSERT`, `UPDATE`, or `DELETE`), you use
`ExecuteNonQuery`, which returns the number of rows affected by the command.

```csharp
public class DatabaseOperations
{
public void ExecuteNonQueryCommand()
{
string connectionString = "your_connection_string_here"; // Replace with your connection
string
string insertQuery = "INSERT INTO People (Name, Age) VALUES (@Name, @Age)";

using (SqlConnection conn = new SqlConnection(connectionString))

{
try
{
conn.Open();

// Define the SQL command

SqlCommand cmd = new SqlCommand(insertQuery, conn);

// Add parameters to prevent SQL injection

cmd.Parameters.AddWithValue("@Name", "John Doe");
cmd.Parameters.AddWithValue("@Age", 30);

// Execute the command

int rowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine("Rows Affected: " + rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
}
}
}
```

- **`ExecuteNonQuery`** executes SQL commands that don't return data, such as `INSERT`,
`UPDATE`, and `DELETE`.
- Parameters are added using `cmd.Parameters.AddWithValue()` to avoid SQL injection.

#### 1.3 **Executing a Stored Procedure**

You can also execute a stored procedure by setting the `CommandType` to `StoredProcedure`.

```csharp
public class DatabaseOperations
{
public void ExecuteStoredProcedure()
{
string connectionString = "your_connection_string_here"; // Replace with your connection
string
string storedProc = "InsertPerson";

using (SqlConnection conn = new SqlConnection(connectionString))

{
try
{
conn.Open();

// Define the stored procedure command

SqlCommand cmd = new SqlCommand(storedProc, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;

// Add parameters
cmd.Parameters.AddWithValue("@Name", "Jane Doe");
cmd.Parameters.AddWithValue("@Age", 28);

// Execute the command

int rowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine("Rows Affected: " + rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
}
}
}
```

- **`CommandType.StoredProcedure`** is used to execute a stored procedure.

- Parameters are added to the command using `AddWithValue()` or `Add()` depending on the type of
the parameter.

---

### 2. **Using SqlDataSource Control in Web Forms**

If you're working with Web Forms and prefer declarative data access, you can use the
`SqlDataSource` control to execute SQL commands for binding data and performing CRUD
operations.

#### 2.1 **SQL Command for Select, Insert, Update, Delete Using SqlDataSource**

```aspx
<asp:SqlDataSource
ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT Id, Name, Age FROM People"
InsertCommand="INSERT INTO People (Name, Age) VALUES (@Name, @Age)"
UpdateCommand="UPDATE People SET Name = @Name, Age = @Age WHERE Id = @Id"
DeleteCommand="DELETE FROM People WHERE Id = @Id">

<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Age" Type="Int32" />
</InsertParameters>

<UpdateParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Age" Type="Int32" />
<asp:Parameter Name="Id" Type="Int32" />
</UpdateParameters>

<DeleteParameters>
<asp:Parameter Name="Id" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
```

- In this example:
- `SelectCommand` retrieves data.
- `InsertCommand` adds a new record.
- `UpdateCommand` updates an existing record.
- `DeleteCommand` deletes a record.
- Parameters are used to avoid SQL injection.

#### 2.2 **Binding SqlDataSource to GridView for CRUD Operations**

You can easily bind a `GridView` or other controls to `SqlDataSource` for displaying, editing, and
deleting data.

```aspx
<asp:GridView
ID="GridView1"
runat="server"
DataSourceID="SqlDataSource1"
AutoGenerateColumns="True"
AllowPaging="True"
AllowSorting="True"
ShowFooter="True">
</asp:GridView>
```

- The `GridView` is automatically bound to the `SqlDataSource`, and you can configure it to allow
editing, deleting, and paging.

---

### 3. **Using Entity Framework (EF) to Execute Commands**

Entity Framework (EF) provides a higher-level approach to data access. You can use EF to perform
database commands like `INSERT`, `UPDATE`, `DELETE`, and more.

#### 3.1 **Executing Commands Using Entity Framework**

In EF, you typically use the `DbContext` class to interact with the database.

```csharp
using System;
using System.Linq;
using System.Data.Entity;

public class DatabaseOperations

{
public void ExecuteEFCommands()
{
using (var context = new MyDbContext())
{
// Insert a new person
var newPerson = new Person { Name = "John Doe", Age = 25 };
context.People.Add(newPerson);
context.SaveChanges(); // Commit the transaction

// Update an existing person

var personToUpdate = context.People.FirstOrDefault(p => p.Name == "John Doe");
 if (personToUpdate != null)
{
personToUpdate.Age = 26;
context.SaveChanges();
}

// Delete a person
var personToDelete = context.People.FirstOrDefault(p => p.Name == "John Doe");
if (personToDelete != null)
{
context.People.Remove(personToDelete);
context.SaveChanges();
}
}
}
}
```

- **`Add()`**: Adds a new entity.

- **`FirstOrDefault()`**: Retrieves a single entity based on a condition.
- **`Remove()`**: Deletes an entity.
- **`SaveChanges()`**: Saves changes to the database.

---

### 4. **Best Practices for Executing Database Commands**

- **Use Parameters**: Always use parameters in your SQL queries to prevent SQL injection attacks.
- **Use `using` Statements**: Ensure that database connections are properly disposed of by
wrapping them in a `using` block.
- **Use Transactions**: For multiple database operations that must succeed or fail together, use
transactions to ensure consistency.
- **Handle Exceptions**: Always handle potential database connection errors and SQL exceptions
gracefully.

Using **parameters** with the `SqlDataSource` control in ASP.NET is a great way to dynamically
interact with a database while avoiding SQL injection vulnerabilities. Parameters allow you to pass
values into SQL commands like `SELECT`, `INSERT`, `UPDATE`, and `DELETE` queries, making your
database operations more flexible and secure.

### 1. **What are Parameters in SqlDataSource?**

Parameters in `SqlDataSource` are placeholders in SQL queries that are replaced with actual values
at runtime. They provide a safe way to pass data to SQL commands.

### 2. **Defining Parameters in SqlDataSource**

To use parameters with `SqlDataSource`, you need to:

- Declare the parameters in your SQL command.
- Define the parameters in the `SelectParameters`, `InsertParameters`, `UpdateParameters`, or
`DeleteParameters` sections of the `SqlDataSource` control.

### Example 1: Using Parameters with a `SELECT` Command

Let's assume we have a table called `People` with columns `Id`, `Name`, and `Age`. We want to
retrieve data based on the `Age` parameter.

#### 1.1 **SqlDataSource for SELECT Command with Parameters**

```aspx
<asp:SqlDataSource
ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT Id, Name, Age FROM People WHERE Age > @Age">

<SelectParameters>
<asp:Parameter Name="Age" Type="Int32" DefaultValue="30" />
</SelectParameters>
</asp:SqlDataSource>

<asp:GridView
ID="GridView1"
runat="server"
DataSourceID="SqlDataSource1"
AutoGenerateColumns="True" />
```

#### Explanation:
- **`SelectCommand`**: The SQL query has a parameter `@Age`. The query will return records
where the `Age` column is greater than the value provided in the parameter.
- **`SelectParameters`**: Defines the parameter `Age` with a default value of `30`. This means when
the page loads, it will fetch people older than 30.

### 3. **Using Parameters Dynamically**

You may want to change the value of parameters dynamically (e.g., when a user submits a form).

#### 3.1 **Changing Parameters Dynamically in Code-Behind**

To change the parameter dynamically, you can do so in the code-behind file.

```csharp
protected void Page_Load(object sender, EventArgs e)
{
// Check if it's not a postback
 if (!IsPostBack)
{
SqlDataSource1.SelectParameters["Age"].DefaultValue = "40"; // Change the parameter value
to 40
GridView1.DataBind(); // Rebind the GridView with the new data
}
}
```

In this example, the parameter `Age` is updated to 40 when the page loads for the first time, and
then the `GridView` is re-bound to reflect the new data.

using ASP.NET parameters with the SqlDataSource Control:

### 4. **Using Parameters in `INSERT`, `UPDATE`, and `DELETE` Commands**

You can use parameters with `InsertCommand`, `UpdateCommand`, and `DeleteCommand` to

perform database operations.

#### 4.1 **Example for `INSERT` Command with Parameters**

```aspx
<asp:SqlDataSource
ID="SqlDataSource2"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
InsertCommand="INSERT INTO People (Name, Age) VALUES (@Name, @Age)">

<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Age" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>

<asp:Button ID="InsertButton" runat="server" Text="Insert" OnClick="InsertButton_Click" />

```

#### 4.2 **Code-Behind to Insert Data**

In the code-behind, you can programmatically set the values for the parameters before executing
the command.

```csharp
protected void InsertButton_Click(object sender, EventArgs e)
{
SqlDataSource2.InsertParameters["Name"].DefaultValue = "Alice";
SqlDataSource2.InsertParameters["Age"].DefaultValue = "25";

SqlDataSource2.Insert(); // Executes the insert command

}
```

#### Explanation:
- **`InsertCommand`**: The SQL query for inserting a new record into the `People` table.
- **`InsertParameters`**: Defines parameters for `Name` and `Age`, which will be passed into the
`INSERT` SQL statement.
- **`InsertButton_Click`**: In the code-behind, we set the values of `Name` and `Age` parameters
dynamically before executing the insert command.

#### 4.3 **Example for `UPDATE` Command with Parameters**

```aspx
<asp:SqlDataSource
ID="SqlDataSource3"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
UpdateCommand="UPDATE People SET Name = @Name, Age = @Age WHERE Id = @Id">

<UpdateParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Age" Type="Int32" />
<asp:Parameter Name="Id" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>

<asp:Button ID="UpdateButton" runat="server" Text="Update" OnClick="UpdateButton_Click" />

```

#### 4.4 **Code-Behind to Update Data**

```csharp
protected void UpdateButton_Click(object sender, EventArgs e)
{
SqlDataSource3.UpdateParameters["Name"].DefaultValue = "Bob";
SqlDataSource3.UpdateParameters["Age"].DefaultValue = "35";
SqlDataSource3.UpdateParameters["Id"].DefaultValue = "1"; // Update the record with ID = 1

SqlDataSource3.Update(); // Executes the update command

}
```

#### Explanation:
- **`UpdateCommand`**: The SQL query for updating the `People` table. The parameters are used
to safely update the `Name`, `Age`, and `Id` of a record.
- **`UpdateParameters`**: Parameters for `Name`, `Age`, and `Id`.
- In the code-behind, the values are dynamically set before executing the update.
#### 4.5 **Example for `DELETE` Command with Parameters**

```aspx
<asp:SqlDataSource
ID="SqlDataSource4"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
DeleteCommand="DELETE FROM People WHERE Id = @Id">

<DeleteParameters>
<asp:Parameter Name="Id" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>

<asp:Button ID="DeleteButton" runat="server" Text="Delete" OnClick="DeleteButton_Click" />

```

#### 4.6 **Code-Behind to Delete Data**

```csharp
protected void DeleteButton_Click(object sender, EventArgs e)
{
SqlDataSource4.DeleteParameters["Id"].DefaultValue = "1"; // Delete the record with Id = 1

SqlDataSource4.Delete(); // Executes the delete command

}
```

#### Explanation:
- **`DeleteCommand`**: The SQL query for deleting a record from the `People` table.
- **`DeleteParameters`**: The parameter `Id` is used to specify which record to delete.
- In the code-behind, we dynamically set the value for `Id` and execute the delete command.

### 5. **Binding Parameters Dynamically in a Control (e.g., GridView)**

If you're using a `GridView` to display data with parameters, you can set the parameter value based
on user input, such as selecting a value from a `DropDownList` or entering a value in a `TextBox`.

#### Example: Using a `DropDownList` to Filter Data with Parameters

```aspx
<asp:DropDownList ID="AgeDropDownList" runat="server">
<asp:ListItem Value="20">20</asp:ListItem>
<asp:ListItem Value="30">30</asp:ListItem>
<asp:ListItem Value="40">40</asp:ListItem>
</asp:DropDownList>

<asp:SqlDataSource
 ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT Id, Name, Age FROM People WHERE Age > @Age">

<SelectParameters>
<asp:Parameter Name="Age" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

<asp:GridView
ID="GridView1"
runat="server"
DataSourceID="SqlDataSource1"
AutoGenerateColumns="True" />

<asp:Button ID="FilterButton" runat="server" Text="Filter" OnClick="FilterButton_Click" />

```

#### Code-Behind to Change Parameter Based on User Selection:

```csharp
protected void FilterButton_Click(object sender, EventArgs e)
{
SqlDataSource1.SelectParameters["Age"].DefaultValue = AgeDropDownList.SelectedValue;
GridView1.DataBind(); // Rebind the GridView with the filtered data
}
```

#### Explanation:
- When the user selects an age from the `DropDownList` and clicks the `FilterButton`, the parameter
`Age` is updated to the selected value.
- The `GridView` is then rebound with the filtered data.

Catch Database Data Using SqlDataSource

To **catch database data** using the `SqlDataSource` control in ASP.NET, you generally need to
**retrieve data** from a database by executing a `SELECT` query. This data can then be **bound**
to various data-bound controls such as `GridView`, `Repeater`, `DropDownList`, etc.

Here’s how you can **retrieve data** from the database using the `SqlDataSource` control and
display it in a `GridView`:

### Steps to Catch Database Data Using SqlDataSource

1. **Set up the `SqlDataSource` Control**:

- Specify a connection string.
- Define a `SELECT` command to fetch the required data from the database.
 - Bind the data to a control (e.g., `GridView` or `Repeater`).

2. **Retrieve and Display Data**:

- Once the data is fetched using the `SELECT` command, you can bind it to a control like `GridView`
for displaying it in tabular format.

### Example 1: Catching Database Data with `SqlDataSource` and Binding it to a `GridView`

#### Web.config - Define Connection String

You should define the connection string in the `Web.config` file to store your database credentials
securely.

```xml
<configuration>
<connectionStrings>
<add name="MyConnectionString"
connectionString="Data Source=YourServerName;Initial
Catalog=YourDatabaseName;Integrated Security=True;"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
```

#### ASPX Page - GridView and SqlDataSource Control

You can add the `SqlDataSource` and `GridView` controls in the ASPX page to catch and display data
from the database.

```html
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True"
DataSourceID="SqlDataSource1" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT * FROM YourTableName" />
```

### Explanation of Code:

1. **`GridView` Control**: The `GridView` control is used to display the data. The `DataSourceID`
property binds the `GridView` to the `SqlDataSource1` control. The `AutoGenerateColumns="True"`
property automatically generates columns based on the data retrieved.

2. **`SqlDataSource` Control**: The `SqlDataSource` control is configured with:

- **ConnectionString**: It refers to the connection string defined in the `Web.config` file
(`MyConnectionString`).
- **SelectCommand**: The SQL query (`SELECT * FROM YourTableName`) that retrieves the data
from your database.

#### Programmatically Accessing Data with `SqlDataSource`

You can also programmatically interact with the `SqlDataSource` control to retrieve data, using the
`Select()` method.

Here’s an example:

```csharp
using System;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class SqlDataSourceExample : System.Web.UI.Page

{
protected void Page_Load(object sender, EventArgs e)
{
// Initialize the SqlDataSource control
SqlDataSource sqlDataSource = new SqlDataSource();

// Set the connection string

sqlDataSource.ConnectionString =
System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString();

// Set the SQL query to retrieve data

sqlDataSource.SelectCommand = "SELECT * FROM YourTableName";

// Retrieve data
var data = sqlDataSource.Select(DataSourceSelectArguments.Empty);

// Bind the data to the GridView control

GridView1.DataSource = data;
GridView1.DataBind();
}
}
```

### Explanation:
- **SqlDataSource**: The `SqlDataSource` control is created dynamically in the code-behind file.
- **Connection String**: The `ConnectionString` is fetched from the `Web.config` file.
- **SelectCommand**: The SQL command (SELECT) is specified to fetch data from the
`YourTableName` table.
- **Select Method**: The `Select()` method is called to execute the `SELECT` query. The
`DataSourceSelectArguments.Empty` parameter is used to indicate that no additional arguments are
passed (you can modify it if needed for paging or filtering).
- **DataBinding**: The `GridView1.DataSource` is set to the data returned by the `Select()` method,
and `GridView1.DataBind()` is used to display the data.