Working with Datasets and Datatables.
Many Programmers who are well aware of Dotnet programming and those who don't have much Proficency in .Net Programming(i.e New to dotnet Programmers) know these two classes Dataset and Datatables.But Many of the Programmers may not know some special features exists in this two classes.That is Computing,filtering and sorting with in the Dataset and datatable classes.These things i want to describe in this Article with snippet of code lines
A Dataset and Datatable are all known to asp.net programmers.But some of the functions that exists in these two classes makes miracle for improving the Performance of websites and coding becomes much more easier and becomes more compact(i.e less lines of code).They are nothing but Select, Compute,Merge,Acceptchanges().so how to use these functions i will illustrate with snippet of code lines.
Datatable.select() or Dataset.tables[0].select() having four overload methods
in this.They are
1)datatable.Select()
Gets an array of all DataRow objects.
2)Datatable.select(string)
Gets an array of all DataRow objects that match the filter criteria.
3)Datatable.select(String, String)
Gets an array of all DataRow objects that match the filter criteria, in the specified sort order.
4)Select(String, String, DataViewRowState)
Gets an array of all DataRow objects that match the filter in the order of the sort that match the specified state
So i will describe each and every code in detail
datatable.Select()
DataTable table = DataSet1.Tables["Suppliers"];
DataRow[] rows = table.Select();
for(int i = 0; i < rows.Length ; i++)
{
Console.WriteLine(rows[i]["CompanyName"]);
}
Datatable.select(string)
DataTable table = DataSet1.Tables["Orders"];
string expression;
expression = "CreatedDate > '01/01/2013'";
DataRow[] foundRows;
MatchedRows = table.Select(expression);
for(int i = 0; i < foundRows.Length; i ++)
{
Console.WriteLine(foundRows[i].itemarray[0]);
}
Datatable.select(string,string)
string expression = "CompanyCode = 'A' or OrderID = 2";
string sortOrder = "CompanyName ASC";
DataRow[] foundRows;
foundRows = table.Select(expression, sortOrder);
for (int i = 0; i < foundRows.Length; i++)
Console.WriteLine(foundRows[i][2]);
Select Method (String, String, DataViewRowState)
same as the above but the only difference is
If the filter has a nullvalue than that will be excluded from the result.
DataRow[] foundRows =
customerTable.Select(expression, sortOrder,
DataViewRowState.Added);
Note:This compute method is very Useful when you are working with barcharts are pi-charts.
Now Datatable.compute for Sum
DataTable table;
table = dataSet.Tables["College"];
// Declare an object variable.
int sumObject;
sumObject = table.Compute("Sum(students)", "stID = 5");
Now Datatable.compute for Avg Marks in all subjects for a particular student in that college.
DataTable table;
table = dataSet.Tables["College"];
// Declare an object variable.
Decimal sumObject;
sumObject = table.Compute("Avg(Marks)", "stID = 5");
Now Datatable.compute for Maximum marks in one subject
DataTable table;
table = dataSet.Tables["College"];
// Declare an object variable.
int sumObject;
sumObject = table.Compute("Max(Marks)", "stID = 5");
Now Datatable.compute for Min total Minimum marks attained in that college
DataTable table;
table = dataSet.Tables["College"];
// Declare an object variable.
int sumObject;
sumObject = table.Compute("Min(Marks)", "stID = 5");
Now Datatable.compute for count for all students
DataTable table;
table = dataSet.Tables["College"];
// Declare an object variable.
int sumObject;
sumObject = table.Compute("Count(StID)", "");
we can calculate stdev (Standard Deviation ) With this compute() method
Merge() : Suppose if you have a datatable.The Programmer wants to create another dummy table for the original table then Merge operation will be very Useful.
The below clone() method will copy the columns from the original table and merge the rows in to the newly created table.
DataTable table2 = table1.Clone();
table1.Merge(table2);
Datatable.merge(table,bool) if true preserve changes in the current DataTable;otherwise false.
Accept Changes() :
Now accept changes Method is very Useful when you want to update the Column value with in the datatable.say for an ex if i want the first row value 0
of the first column after that call the acceptchange() that will ensure that you are first row value will be Updated
for(int i=0;i<1;i++)
{
dt.rows[i][0] = 0;
}
dt.Acceptchanges();
Follow
DataSet
1. Its connectionless. whenever you want fetch data from database. its connects indirectly to the database and create a virtual database in local system. then disconnected from database.
2. Its easily read and write data from virtual database.
3.Dataset may have more than one table called datatable
DataTable
A DataTable object represents a single table in the database. It has a name rows and columns.
There is not much difference between dataset and datatable, dataset is just the collection of datatables.
Datarow
-it can be defined as a collection of data or records
-a collection of datarow constitute a datatable
Datatable
- a datatable can be defined as a collection of rows and columns
DataSet holds one or more DataTables.
Example 1:
DataTable dt = new DataTable();
DataSet ds = new DataSet();
ds.Tables.Add(dt);
Example 2:
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataSet ds = new DataSet();
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);