Use of DataView in ASP.NET.

A DataView is used for customized view of the data from a DataTable.

Example:

C#

using System.Data; using System.Data.SqlClient; namespace Employee { public partial class Emp_Details : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { SqlConnection connection = new SqlConnection("server=myDB;uid=sa;pwd=password;database=master"); connection.Open(); SqlCommand command = new SqlCommand("select * from employee",connection); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = command; DataSet ds = new DataSet(); da.Fill(ds,"employee"); DataView dv = new DataView(ds.Tables["employee"]); dv.RowFilter = "emp_age<30"; dv.Sort = "First_Name ASC"; EmpDetails.DataSource = dv; EmpDetails.DataBind(); connection.Close() } } }

---

VB

Imports System.Data Imports System.Data.SqlClient Partial Public Class Emp_Details       Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim connetion As SqlConnection = New SqlConnection("server=myDB;uid=sa;pwd=password;database=master") connection.Open() Dim command As SqlCommand = New SqlCommand("select * from employee", connection) Dim da As SqlDataAdapter = New SqlDataAdapter() da.SelectCommand = command Dim ds As DataSet = New DataSet() da.Fill(ds, "employee") Dim dv As DataView = New DataView(ds.Tables("employee")) dv.RowFilter = "emp_age<30" dv.Sort = "First_Name ASC" EmpDetails.DataSource = dv EmpDetails.DataBind() connection.Close() End Sub End Class

Description

dv.RowFilter = "emp_age<30" retrieves records of those employees whose age is less than 30. dv.Sort = "First_Name ASC" Sorts column First_Name of table employee in ascending order. EmpDetails is a GridView ID.

How to display retrieved data from database in GridView?

GridView is a control that can be used for viewing the records in a tabular format. A GridView can be created using the following syntax:

<asp:gridview id="UserDetails" runat="server"></asp:gridview>

The following examples illustrates how to view data in GridView in C#:

C#

Example 1:

using System.Data; using System.Data.SqlClient; namespace Registration { public partial class User_Details : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { SqlConnection connection = new SqlConnection("server=myDB;uid=sa;pwd=password;database=master"); connection.Open(); SqlCommand command = new SqlCommand("select * from User_Registration",connection); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = command; DataSet ds = new DataSet(); da.Fill(ds,"User_Registration"); UserDetails.DataSource = ds.Tables["User_Registration"].DefaultView; UserDetails.DataBind(); connection.Close(); } } }

Example 2:

using System.Data; using System.Data.SqlClient; namespace Registration { public partial class User_Details : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { SqlConnection connection = new SqlConnection("Server=myDB; uid=sa;pwd=password;database=master"); connection.Open(); SqlCommand command = new SqlCommand("select UserName,FirstName,LastName,Email from User_Registration", connection); DataTable dt = new DataTable(); dt.Columns.Add("User Name"); dt.Columns.Add("First Name"); dt.Columns.Add("Last Name"); dt.Columns.Add("Email ID"); DataRow dr = dt.NewRow(); SqlDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) { dr["User Name"] = dataReader.GetValue(0).ToString(); dr["First Name"]=dataReader.GetValue(1).ToString(); dr["Last Name"]=dataReader.GetValue(2).ToString(); dr["Email ID"] = dataReader.GetValue(3).ToString(); dt.Rows.Add(dr); dr = dt.NewRow(); UserDetails.DataSource = dataReader; UserDetails.DataBind(); } dataReader.Close(); connection.Close(); } } }

---

The following examples illustrates how to view data in GridView in VB: 

VB 

Example 1:

Imports System.Data Imports System.Data.SqlClient Partial Public Class User_Details               Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim connection As SqlConnection = New SqlConnection("server=myDB;uid=sa;pwd=password;database=master") connection.Open() Dim command As SqlCommand = New SqlCommand("select * from User_Registration", connection) Dim da As SqlDataAdapter = New SqlDataAdapter() da.SelectCommand = command Dim ds As DataSet = New DataSet() da.Fill(ds, "User_Registration") UserDetails.DataSource = ds.Tables("User_Registration").DefaultView UserDetails.DataBind() con.Close() End Sub End Class

Example 2:

Imports System.Data Imports System.Data.SqlClient Partial Public Class User_Details               Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim connection As SqlConnection = New SqlConnection("server=myDB;uid=sa;pwd=password;database=master") connection.Open() Dim command As SqlCommand = New SqlCommand("select UserName,FirstName,LastName,Email from User_Registration", connection) Dim dtable As DataTable = New DataTable() dtable.Columns.Add("User Name") dtable.Columns.Add("First Name") dtable.Columns.Add("Last Name") dtable.Columns.Add("Email ID") Dim drow As DataRow = dtable.NewRow() Dim dataReader As SqlDataReader = command.ExecuteReader() While (dataReader.Read()) drow("User Name") = dataReader.GetValue(0).ToString() drow("First Name") = dataReader.GetValue(1).ToString() drow("Last Name") = dataReader.GetValue(2).ToString() drow("Email ID") = dataReader.GetValue(3).ToString() dtable.Rows.Add(drow) drow = dtable.NewRow() End While UserDetails.DataSource = dtable UserDetails.DataBind() dataReader.Close() con.Close() End Sub End Class

How to create Registration Form in ASP.NET?

To become a member of any website, user has to fill his/her details in the Registration Form. Lets assume that we have a Registration Form as shown below:

First of all we set the following properties:

Field Name	Controls	Properties
First Name	Label	ID	F_Name
		Text	First Name
	TextBox	ID	FName
		TextMode	SingleLine
Middle Name	Label	ID	M_Name
		Text	Middle Name
	TextBox	ID	MName
		TextMode	SingleLine
Last Name	Label	ID	L_Name
		Text	Last Name
	TextBox	ID	LName
		TextMode	SingleLine
Email ID	Label	ID	Email
		Text	Email ID
	TextBox	ID	EmailID
		TextMode	SingleLine
User Name	Label	ID	User
		Text	User Name
	TextBox	ID	UserName
		TextMode	SingleLine
Password	Label	ID	Pass
		Text	Password
	TextBox	ID	Password
		TextMode	Password
Confirm Password	Label	ID	CPass
		Text	Confirm Password
	TextBox	ID	CPassword
		TextMode	Password
Date of Birth	Label	ID	DOB
		Text	Date of Birth
	DropDownList	ID	Day
		Items	(Collection)...
	DropDownList	ID	Month
		Items	(Collection)...
	DropDownList	ID	Year
		Items	(Collection)...
Gender	Label	ID	Gen
		Text	Gender
	RadioButtonList	ID	Gender
		Items	(Collection)...
		TextAlign	Right
Address	Label	ID	Addr
		Text	Address
	TextBox	ID	Address
		TextMode	MultiLine
City	Label	ID	City_Name
		Text	City
	DropDownList	ID	City
		AutoPostBack	True
		Items	(Collection)...
State	Label	ID	State_Name
		Text	State
		Visible	False
	DropDownList	ID	State
		Visible	False
Country	Label	ID	Country_Name
		Text	Country
		Visible	False
	DropDownList	ID	Country
		Visible	False
Pincode	Label	ID	PinNum
		Text	Pincode
		Visible	False
	TextBox	ID	Pincode
		Visible	False
Mobile No.	Label	ID	Mobile_No
		Text	Mobile No.
	TextBox	ID	Mobile
		TextMode	SingleLine

Validations to be performed:

• Field Mark (*) are necessary.
• First Name, Middle Name & Last Name can contain only alphabets(A-Z).
• Pincode and Mobile No. can contain only numbers.
• Password should be alphanumeric.
• Password and Confirm Password should be same.
• Password should be in the range between 8 to15 characters.

Validators to be used:

• RequiredFieldValidator
• CompareValidator
• RegularExpressionValidator
• ValidationSummary

Field Name	Validator	Properties
First Name	RequiredFieldValidator	ID	FName_Validator
		ControlToValidate	FName
		Display	None
		ErrorMessage	First Name cannot be blank
	RegularExpressionValidator	ID	FirstNameValidator
		ControlToValidate	FName
		Display	None
		ErrorMessage	First Name can contain only alphabets.
		ValidationExpression	^([A-Za-z])+$
Middle Name	RegularExpressionValidator	ID	MName_Validator
		ControlToValidate	MName
		Display	None
		ErrorMessage	Middle Name can contain only alphabets
		ValidationExpression	^([A-Za-z])+$
Last Name	RequiredFieldValidator	ID	LName_Validator
		ControlToValidate	LName
		Display	None
		ErrorMessage	Last Name cannot be blank
	RegularExpressionValidator	ID	LastNameValidator
		ControlToValidate	LName
		Display	None
		ErrorMessage	Last Name can contain only alphabets.
		ValidationExpression	^([A-Za-z])+$
Email ID	RequiredFieldValidator	ID	EmailValidator
		ControlToValidate	EmailID
		Display	None
		ErrorMessage	Email ID cannot be blank
	RegularExpressionValidator	ID	EmailID_Validator
		ControlToValidate	EmailID
		Display	None
		ErrorMessage	Please type the valid Email Address
		ValidationExpression	^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+$
User Name	RequiredFieldValidator	ID	User_Validator
		ControlToValidate	UserName
		Display	None
		ErrorMessage	User Name cannot be blank
Password	RequiredFieldValidator	ID	Pass_Validator
		ControlToValidate	Password
		Display	None
		ErrorMessage	Password cannot be blank
	RegularExpressionValidator	ID	Password_Validator
		ControlToValidate	Password
		Display	None
		ErrorMessage	Password can't be less than 8 & more than 15 characters.
		ValidationExpression	^[\w]{8,15}$
	RegularExpressionValidator	ID	PassValidator
		ControlToValidate	Password
		Display	None
		ErrorMessage	Password is an alphanumeric field.
		ValidationExpression	^(\w+\d)$
Confirm Password	RequiredFieldValidator	ID	CPass_Validator
		ControlToValidate	CPassword
		Display	None
		ErrorMessage	Please Re-type the Password
	CompareValidator	ID	CPassword_Validator
		ControlToCompare	Password
		ControlToValidate	CPassword
		Display	None
		ErrorMessage	The two passwords do not match
Date of Birth	RequiredFieldValidator	ID	Day_Validator
		ControlToValidate	Day
		Display	None
		ErrorMessage	Please Select Day of Birth
		InitialValue	0
	RequiredFieldValidator	ID	Month_Validator
		ControlToValidate	Month
		Display	None
		ErrorMessage	Please Select Month of Birth
		InitialValue	0
	RequiredFieldValidator	ID	Year_Validator
		ControlToValidate	Year
		Display	None
		ErrorMessage	Please Select Year of Birth
		InitialValue	0
Gender	RequiredFieldValidator	ID	Gender_Validator
		ControlToValidate	Gender
		Display	None
		ErrorMessage	Please Select Gender
Address	RequiredFieldValidator	ID	Address_Validator
		ControlToValidate	Address
		Display	None
		ErrorMessage	Address cannot be blank
City	RequiredFieldValidator	ID	City_Validator
		ControlToValidate	City
		Display	None
		ErrorMessage	Please Select City
		InitialValue	0
Pincode	RequiredFieldValidator	ID	Pin_Validator
		ControlToValidate	Pincode
		Display	None
		ErrorMessage	Pincode cannot be blank
	RegularExpressionValidator	ID	Pincode_Validator
		ControlToValidate	Pincode
		Display	None
		ErrorMessage	Pincode can contain only numbers
		ValidationExpression	^([0-9])+$
Mobile No.	RequiredFieldValidator	ID	MobileNum_Validator
		ControlToValidate	Mobile
		Display	None
		ErrorMessage	Mobile No. cannot be blank
	RegularExpressionValidator	ID	Mobile_Validator
		ControlToValidate	Mobile
		Display	None
		ErrorMessage	Mobile No. can contain only numbers
		ValidationExpression	^([0-9])+$
ValidationSummary		ID	Summary
		ShowMessageBox	True
		ShowSummary	False

(Note: You can see post How to set RequiredFieldValidator on DropDownList in ASP.NET? for validation on DropDownList).

After setting all the properties, create table in Sql Server and add the following code in the codebehind (.cs or .vb) file:

create table User_Registration(First_Name varchar(15),Middle_Name varchar(15),LastName varchar(15),Email_ID varchar(40),UserName varchar(25),Password varchar(15),Date_of_Birth varchar(10),Gender varchar(6),Address varchar(50),City varchar(15),State varchar(20),Country varchar(20),Pincode varchar(10),Mobile_No varchar(10), primary key(UserName));

C#

using System.Data; using System.Data.SqlClient; public partial class Registration : System.Web.UI.Page { SqlConnection con = new SqlConnection("server=myDB;uid=sa;pwd=password;database=master"); protected void City_SelectedIndexChanged(object sender, EventArgs e) { if (Page.IsPostBack) { Password.Attributes.Add("value",Password.Text); //Retain Password Value on PostBack CPassword.Attributes.Add("value",CPassword.Text); //Retain CPassword Value on PostBack Gender.Attributes.Add("value",Gender.SelectedValue); //Retain Gender SelectedValue on PostBack RetrieveCity(City.SelectedItem.Text); } } protected void Submit_Click(object sender, EventArgs e) { String[] fieldValues = new String[14]; fieldValues[0]=FName.Text; fieldValues[1]=MName.Text; fieldValues[2]=LName.Text; fieldValues[3]=EmailID.Text; fieldValues[4]=UserName.Text; fieldValues[5]=Password.Text; fieldValues[6]=Day.SelectedItem.Value+"-"+Month.SelectedItem.Value+"-"+ Year.SelectedItem.Value; fieldValues[7] = Gender.SelectedValue; fieldValues[8]=Address.Text; fieldValues[9]=City.SelectedItem.Value; fieldValues[10]=State.SelectedItem.Value; fieldValues[11]=Country.SelectedItem.Value; fieldValues[12]=Pincode.Text; fieldValues[13] = Mobile.Text; insertUserDetail("User_Registration",fieldValues); } protected void RetrieveCity(String value) { if (value == "Select") { State_Name.Visible = false; State.Visible = false; Country_Name.Visible = false; Country.Visible = false; PinNum.Visible = false; Pincode.Visible = false; } else { con.Open(); SqlCommand com = new SqlCommand("select State,Country from User_Location where City='" + City.SelectedValue + "'", con); SqlDataReader dr = com.ExecuteReader(); while (dr.Read()) { State_Name.Visible = true; State.Visible = true; Country_Name.Visible = true; Country.Visible = true; PinNum.Visible = true; Pincode.Visible = true; State.Items.Add(dr.GetValue(0).ToString()); State.SelectedValue = dr.GetValue(0).ToString(); if (State.Items.Count > 1) { State.Items.RemoveAt(0); // If user change city value more than once then it will add respective value and remove the previous value. } Country.Items.Add(dr.GetValue(1).ToString()); Country.SelectedValue = dr.GetValue(1).ToString(); if (Country.Items.Count > 1) { Country.Items.RemoveAt(0); } } dr.Close(); con.Close(); } } protected void insertUserDetail(String tableName,Array insertArray) { con.Open(); String strValues = ""; foreach (String strName in insertArray) { if (strValues.Length == 0) { strValues = strName; } else { strValues = strValues + "'" + "," + "'" + strName; } } String strInsert = "insert into " + tableName + " values('" + strValues.ToString() + "')"; SqlCommand com = new SqlCommand(strInsert,con); com.ExecuteNonQuery(); Response.Write("<script>alert('Your Registration is Successful');</script>"); } } }

VB

Imports System.Data Imports System.Data.SqlClient Partial Public Class Registration Inherits System.Web.UI.Page Dim con As SqlConnection = New SqlConnection("server=myDB;uid=sa;pwd=password;database=master") Protected Sub Submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Submit.Click Dim fieldValues(13) As String fieldValues(0) = FName.Text fieldValues(1) = MName.Text fieldValues(2) = LName.Text fieldValues(3) = EmailID.Text fieldValues(4) = UserName.Text fieldValues(5) = Password.Text fieldValues(6) = Day.SelectedItem.Value + "-" + Month.SelectedItem.Value + "-" + Year.SelectedItem.Value fieldValues(7) = Gender.SelectedValue fieldValues(8) = Address.Text fieldValues(9) = City.SelectedItem.Value fieldValues(10) = State.SelectedItem.Value fieldValues(11) = Country.SelectedItem.Value fieldValues(12) = Pincode.Text fieldValues(13) = Mobile.Text insertUserDetail("User_Registration", fieldValues) End Sub Protected Sub City_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles City.SelectedIndexChanged If Page.IsPostBack Then Password.Attributes.Add("value", Password.Text) 'Retain Password Value on PostBack CPassword.Attributes.Add("value", CPassword.Text) 'Retain CPassword Value on PostBack Gender.Attributes.Add("value", Gender.SelectedValue) 'Retain Gender Selected Value on PostBack RetrieveCity(City.SelectedItem.Text) End If End Sub Protected Sub RetrieveCity(ByVal value As String) If value = "Select" Then State_Name.Visible = False State.Visible = False Country_Name.Visible = False Country.Visible = False PinNum.Visible = False Pincode.Visible = False Else con.Open() Dim com As SqlCommand = New SqlCommand("select State,Country from User_Location where City='" + City.SelectedValue + "'", con) Dim dr As SqlDataReader = com.ExecuteReader() While dr.Read State_Name.Visible = True State.Visible = True Country_Name.Visible = True Country.Visible = True PinNum.Visible = True Pincode.Visible = True State.Items.Add(dr.GetValue(0).ToString()) State.SelectedValue = dr.GetValue(0).ToString() Country.Items.Add(dr.GetValue(1).ToString()) Country.SelectedValue = dr.GetValue(1).ToString() If State.Items.Count > 1 Then State.Items.RemoveAt(0) End If If Country.Items.Count > 1 Then Country.Items.RemoveAt(0) End If End While dr.Close() End If con.Close() End Sub Protected Sub insertUserDetail(ByVal tableName As String, ByVal insertArray As Array) con.Open() Dim strValues As String = "" For Each strName As String In insertArray If strValues.Length = 0 Then strValues = strName Else strValues = strValues + "'" + "," + "'" + strName End If Next Dim strInsert As String = "insert into " + tableName + " values('" + strValues.ToString + "')" Dim com As SqlCommand = New SqlCommand(strInsert, con) com.ExecuteNonQuery() Response.Write("<script>alert('Your Registration is Successful');</script>") End Sub End Class