ADO.NET DataView in ASP.NET

A DataView provides various views of the data stored in a DataTable. Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression. That is we can customize the views of data from a DataTable.

Creating Table in SQL Server Database

Now create a table named UserDetail with the columns UserID and UserName. The table looks as below.

Now insert some data into the table.

Now create a new web application project in Visual Studio 2010. Now add the following namespace.

using System.Data.SqlClient;
using System.Data;

Now write the connection string to connect to the database.

string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;";

Here in aspx code, I used a DataGrid.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication120.WebForm1" %>
<!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 runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>

Now we create a simple application showing the SQL Server Table Data in the GridView. The following code is a simple code without using DataView.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
namespace WebApplication120 {
public partial class WebForm1: System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e) {
show();
}
private void show() {
{
SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");
string strSQL = "Select * from UserDetail";
SqlDataAdapter dt = new SqlDataAdapter(strSQL, con);
DataSet ds = new DataSet();
dt.Fill(ds, "UserDetail");
con.Close();
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}
}

Creating a DataView

To convert a DataSet to a DataView in ASP.Net using C# code, you can initialize the DataView class object by accessing the DefaultView property via DataTable collection of DataSet. DefaultView property enables you to convert the DataSet to DataView.

SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");
string strSQL = "Select * from UserDetail";
SqlDataAdapter dt = new SqlDataAdapter(strSQL, con);
DataSet ds = new DataSet();
dt.Fill(ds, "UserDetail");
con.Close();
DataView dv = new DataView();
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();

Now run the application.

Adding new row in the DataView

We can add new rows in the DataView using AddNew() method in the DataView. The following C# source code shows how to add a new row in a DataView.

SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");
string strSQL = "Select * from UserDetail";
SqlDataAdapter dt = new SqlDataAdapter(strSQL, con);
DataSet ds = new DataSet();
dt.Fill(ds, "UserDetail");
con.Close();
DataView dv = new DataView(ds.Tables[0]);
DataRowView newrow = dv.AddNew();
newrow["UserID"] = 7;
newrow["UserName"] = "ram";
newrow.EndEdit();
GridView1.DataSource = dv;
GridView1.DataBind();

Now run the application. we will see row has been added in the DataView.

Delete rows in a DataView

We can add new rows in the DataView using Delete() method in the DataView. The following C# source code shows how to delete rows in a DataView.

SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");
string strSQL = "Select * from UserDetail";
SqlDataAdapter dt = new SqlDataAdapter(strSQL, con);
DataSet ds = new DataSet();
dt.Fill(ds, "UserDetail");
con.Close();
DataView dv = new DataView(ds.Tables[0],"", "UserID", DataViewRowState .CurrentRows);
dv.Table.Rows[4].Delete();
GridView1.DataSource = dv;
GridView1.DataBind();

Now run the application.

Resources