Manipulating Datasets

[Anil Gupte]

How can I manipulate Datasets, such as getting a specific row based on a key
column value. Of course, I could loop through all the rows in the entire
dataset each time I needed something, but is there an easier way? I want to
be able to execute the equivalent of SQL statements including insert, delete
etc.

Any ideas? Thanx,

 

[sloan]

You have to

1. Perform a .Select
2. Change the data
3. (Sometimes) commit the changes



Let's say you have a strong dataset called EmployeeDS with an
Employee(table). EmpID(int),LastName(string),FirstName(string)


EmployeeDS ds = //populate the ds somehow with employees

DataRow[] rows = ds.Select("EmpID=123");
//vb dim rows as DataRows() = ds.Select("EmpID=123");
//or
//DataRow[]rows = ds.Select("LastName='Smith'");
//or vb
//dim rows as DataRows() = ds.Select("LastName='Smith'");

now you have an array of rows

you loop over them, but you'll have to cast them

dim i as integer
for i = 0 to rows.Length - 1 //or .Count?
EmployeeDS.EmployeeRow currentRow = ctype(rows(i),
EmployeeDS.EmployeeRow
currentRow.LastName = "Jones" //change the data

next i

Console.Writeline (ds.GetXml())



Something like that.

 

[Steve Gerrard]

How can I manipulate Datasets, such as getting a specific row based
on a key column value. Of course, I could loop through all the rows
in the entire dataset each time I needed something, but is there an
easier way? I want to be able to execute the equivalent of SQL
statements including insert, delete etc.

Any ideas? Thanx,

Use DataViews. You can create one from any table:
Dim MyView As DataView = _
New DataView(SomeTable,"A Filter","A Sort", CurrentRows)

You can set a row filter, which is like a SQL where clause.
You can set a sort, like a SQL order by.
Once a sort is set, you can use Find() to get the first row, or an array of
rows, that have specified values in the sort column(s).
Once you get a row, you can delete it, or modify any of its column values.
You can create a new row using SomeTable.NewRow, set its values, and add it
using SomeTable.Rows.Add.

It's a mini-database.

 

[Anil Gupte]

I thought DataViews were read-only? I am using VB.Net 2003

 

[Anil Gupte]

Thats cool! Sounds doable - I will try it.

--
Anil Gupte
www.keeninc.net
www.icinema.com

You have to

1. Perform a .Select
2. Change the data
3. (Sometimes) commit the changes



Let's say you have a strong dataset called EmployeeDS with an
Employee(table). EmpID(int),LastName(string),FirstName(string)


EmployeeDS ds = //populate the ds somehow with employees

DataRow[] rows = ds.Select("EmpID=123");
//vb dim rows as DataRows() = ds.Select("EmpID=123");
//or
//DataRow[]rows = ds.Select("LastName='Smith'");
//or vb
//dim rows as DataRows() = ds.Select("LastName='Smith'");

now you have an array of rows

you loop over them, but you'll have to cast them

dim i as integer
for i = 0 to rows.Length - 1 //or .Count?
EmployeeDS.EmployeeRow currentRow = ctype(rows(i),
EmployeeDS.EmployeeRow
currentRow.LastName = "Jones" //change the data

next i

Console.Writeline (ds.GetXml())



Something like that.

How can I manipulate Datasets, such as getting a specific row based on a
key column value. Of course, I could loop through all the rows in the
entire dataset each time I needed something, but is there an easier way?
I want to be able to execute the equivalent of SQL statements including
insert, delete etc.

Any ideas? Thanx,

 

[Steve Gerrard]

I thought DataViews were read-only? I am using VB.Net 2003

How so? A DataView is just a another look at rows in a DataTable.

Some ways to modify data in through a DataView:

Dim DV As DataView = New DataView(MyTable)
DV.Item(3).Item("CheckFlag") = 1

Dim drv As DataRowView
drv = DV.Item(3)
drv("CheckFlag") = 1

For Each drv In DV
drv("CheckFlag") = 1
Next drv

Dim nID As Integer = 23
dv.Sort = "EmpID"
drv = dv.FindRows(nID).GetValue(0)
drv("CheckFlag") = 1

' etc...

 

[Cor Ligthert[MVP]]

Steve,

It's a mini-database.

It is certainly not or should add to your text, "single user", this has
given very much misunderstandings in past.

One of the disadvantages is than even that you have to read and write
forever the complete XML file while it is not changeble on disk.

Cor

 

[Cor Ligthert[MVP]]

Anil-

A little addition to your two other answers,

http://msdn2.microsoft.com/en-us/library/bb386977.aspx

-Cor

 

[Anil Gupte]

From Sams Teach Yourself Visual Basic in 21 Days on page 714:

"Dataviews are much like read-only datasets. Because they're read-only,
they work like snapshots of your data, and they provide you with faster
access to that data than datasets can. The term "data view" means just
that - a view of your data - and they're usually used as containers for a
subset of your data."

 

[Cor Ligthert[MVP]]

From Sams Teach Yourself Visual Basic in 21 Days on page 714:

"Dataviews are much like read-only datasets.

Completely wrong, the dataview is the class from the datatable property
defaultview. It set the sequence and filter of a datatable. However you can
create as well more views by instance to make it possible to use the
datatable with more controls.

You can use it to add, delete, update datarows in any way you want including
by the seperated items of the datarow.

Cor