·
ADO.NET
DataView - Related Contents
How
to create a DataView
DataView provides different views of the data
stored in a DataTable. DataView can
be used to sort, filter, and search in a DataTable , additionally we can add
new rows and modify the content in a DataTable. DataViews can be created and
configured both design time and run time . Changes made to a DataView affect
the underlying DataTable automatically, and changes made to the underlying
DataTable automatically affect any DataView objects that are viewing the
DataTable.
We
can create DataView in two different ways. We can use the DataView constructor,
or you can create a reference to the DefaultView property of the DataTable. The
DataView constructor can be empty, or it can take either a DataTable as a
single argument, or a DataTable along with filter criteria, sort criteria, and
a row state filter.
dataView = dataSet.Tables(0).DefaultView
The
following source code shows how to create a DataView in VB.NET. Create a new
VB.NET project and drag a DataGridView and a Button on default Form Form1 , and copy
and paste the following Source Code on button click event.
Imports
System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As DataView
Dim sql As String
connetionString = "Data Source=ServerName;Initial
Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "Select * from product"
connection = New
SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(sql,
connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Create
DataView")
adapter.Dispose()
command.Dispose()
connection.Close()
dv = ds.Tables(0).DefaultView
DataGridView1.DataSource = dv
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
How
to sort DataView
The
DataView provides different views of the data stored in a DataTable. DataView can
be used to sort, filter, and search data in a DataTable , additionally we can
add new rows and modify the content in a DataTable. DataViews can be created
and configured at both design time and run time . Changes made to a DataView
affect the underlying DataTable automatically, and changes made to the
underlying DataTable automatically affect any DataView objects that are viewing
that DataTable.
We
can sort single or multiple fields in a DataView , also we can specify the sort
order like ASC (ascending) and DESC (descending) . The following example
creates a View and apply sort on the column Product_Price in descending order.
Create a new VB.NET project and drag aDataGridView and a Button on default Form Form1 , and copy
and paste the following Source Code on button click event.
Imports
System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As DataView
Dim sql As String
connetionString = "Data Source=ServerName;Initial
Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "Select * from product"
connection = New
SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(sql,
connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Sort
DataView")
adapter.Dispose()
command.Dispose()
connection.Close()
dv = New DataView(ds.Tables(0),
"Product_Price > 100", "Product_Price Desc",
DataViewRowState.CurrentRows)
DataGridView1.DataSource = dv
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End
Sub
End Class
How
to Filter DataView
DataView
enables you to create different views of the data stored in a DataTable. That is we
can customize the views of data from a DataTable. DataView can be used to sort,
filter, and search a DataTable , additionally we can add new rows and modify
the content in a DataTable. We can create DataView in two ways. Either we can
use the DataView constructor, or we can create a reference to the DefaultView
property of the DataTable. Also we can create multiple DataViews for any given
DataTable.
The
following source code creates a view that shows all Product Details where the
Product Price less than 500. Create a new VB.NET project and drag a DataGridView and a Button on default Form Form1 , and copy
and paste the following Source Code on button click event.
Imports
System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As DataView
Dim sql As String
connetionString = "Data Source=ServerName;Initial
Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "Select * from product"
connection = New
SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(sql,
connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Filter
DataView")
adapter.Dispose()
command.Dispose()
connection.Close()
dv = New DataView(ds.Tables(0),
"Product_Price < = 500", "Product_Name",
DataViewRowState.CurrentRows)
DataGridView1.DataSource = dv
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
How
to Find a row in DataView
The
DataView provides different views of the data stored in a DataTable. The
constructor for the DataView class initializes a new instance of the DataView
class and accepts the DataTable as an argument. We can create DataView in two
ways. Either we can use the DataView constructor, or we can create a reference
to the DefaultView property of the DataTable. We can create multiple DataViews
for any given DataTable.
We
can search in a DataView according to the sort key values by using the Find
method . The Find method returns an integer with the index of the DataRowView
that matches the search criteria. If more than one row matches the search
criteria, only the index of the first matching DataRowView is returned. If no
matches are found, Find returns -1
Dim index As Integer = DataView.Find("Product5")
Imports
System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As DataView
Dim sql As String
connetionString = "Data Source=ServerName;Initial
Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "Select * from product"
connection = New SqlConnection(connectionString)
Try
connection.Open()
command = New SqlCommand(sql,
connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Find Row
DataView")
adapter.Dispose()
command.Dispose()
connection.Close()
dv = New DataView(ds.Tables(0))
dv.Sort = "Product_Name"
Dim index As Integer =
dv.Find("Product5")
If index = -1 Then
MsgBox("Item Not
Found")
Else
MsgBox(dv(index)("Product_id").ToString() & " " &
dv(index)("Product_Name").ToString())
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
How
to add new rows in DataView
The
DataView provides different views of the data stored in a DataTable. DataViews
can be created and configured at both design time and run time . We can create
DataView in two ways. Either we can use the DataView constructor, or we can
create a reference to the DefaultView property of the DataTable. We can create
multiple DataViews for any given DataTable.
We
can add new rows in the DataView using AddNew method in the DataView. The
following source code shows how to add new row in a DataView . Create a new VB.NET project and drag a DataGridView and a Button on default Form Form1 , and copy
and paste the following Source Code on button click event.
Imports
System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As DataView
Dim sql As String
connetionString = "Data Source=ServerName;Initial
Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "Select * from product"
connection = New
SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(sql,
connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Add
New")
adapter.Dispose()
command.Dispose()
connection.Close()
dv = New DataView(ds.Tables(0))
Dim newRow As DataRowView =
dv.AddNew()
newRow("Product_ID") = 7
newRow("Product_Name") =
"Product 7"
newRow("Product_Price") =
111
newRow.EndEdit()
dv.Sort = "product_id"
DataGridView1.DataSource = dv
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
How
to update rows in DataView
The
DataView provides different views of the data stored in a DataTable. DataView can
be used to sort, filter, and search a DataTable , additionally we can add new
rows and modify the content in a DataTable. We can create DataView in two ways.
Either we can use the DataView constructor, or we can create a reference to the
DefaultView property of the DataTable. Also we can create multiple DataViews
for any given DataTable.
We
can update the data in a DataView . The following source code shows how to
update data in a DataView . Create a new VB.NET project and drag a DataGridView and a Button on default Form Form1 , and copy
and paste the following Source Code on button click event.
Imports
System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As DataView
Dim sql As String
connetionString = "Data Source=ServerName;Initial
Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "Select * from product"
connection = New
SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(sql,
connection)
adapter.SelectCommand = command
adapter.Fill(ds,
"Update")
adapter.Dispose()
command.Dispose()
connection.Close()
dv = New DataView(ds.Tables(0),
"", "Product_Name", DataViewRowState.CurrentRows)
Dim index As Integer =
dv.Find("Product5")
If index = -1 Then
MsgBox("Product not
found")
Else
dv(index)("Product_Name") = "Product11"
MsgBox("Product Updated
!")
End If
DataGridView1.DataSource = dv
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
How
to delete rows in DataView
The
DataView provides different views of the data stored in a DataTable. DataView can
be used to sort, filter, and search data in a DataTable , additionally we can
add new rows and modify the content in a DataTable. Also we can delete the data
from the DataView . The following source code shows how to delete the data from
DataView.
Imports
System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As DataView
Dim sql As String
connetionString = "Data Source=ServerName;Initial
Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "Select * from product"
connection = New
SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(sql,
connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Delete
Row")
adapter.Dispose()
command.Dispose()
connection.Close()
dv = New DataView(ds.Tables(0),
"", "Product_ID", DataViewRowState.CurrentRows)
dv.Table.Rows(3).Delete()
DataGridView1.DataSource = dv
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
How
to create a new DataTable from DataView
DataView provides different views of the data
stored in a DataTable. That is we
can customize the views of data from a DataTable. Changes made to a DataView
affect the underlying DataTable automatically, and changes made to the
underlying DataTable automatically affect any DataView objects that are viewing
that DataTable. We can create a new DataTable from the DataView . We can use
the ToTable method to copy all the rows and columns, or a subset of the data
into a new DataTable.
Imports
System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dv As DataView
Dim sql As String
connetionString = "Data Source=ServerName;Initial
Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "Select * from product"
connection = New
SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(sql,
connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Copy to
DataTable")
adapter.Dispose()
command.Dispose()
connection.Close()
dv = New DataView(ds.Tables(0),
"Product_Price <= 200", "Product_ID",
DataViewRowState.CurrentRows)
Dim dTable As DataTable
dTable = dv.ToTable
DataGridView1.DataSource = dTable
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
