Sunday, 24 June 2012

Ado.net

Introduction

DataTable is a central object in the ADO.NET library. If you are working with ADO.NET - accessing data from database, you can not escape from DataTable. Other objects that use DataTable are DataSet and DataView. In this tutorials, I will explain how to work with DataTable. I have tried to cover most of the frequently used activity in the DataTable, I hope you will like it.
Creating a DataTable
To create a DataTable, you need to use System.Data namespace, generally when you create a new class or page, it is included by default by the Visual Studio. Lets write following code to create a DataTable object. Here, I have pased a string as the DataTable name while creating DataTable object.
// instantiate DataTableDataTable dTable = new DataTable("Dynamically_Generated");
Creating Columns in the DataTable
To create column in the DataTable, you need to use DataColumn object. Instantiate the DataColumn object and pass column name and its data type as parameter. Then call add method of DataTable column and pass the DataColumn object as parameter.
// create columns for the DataTable

DataColumn auto = new DataColumn("AutoID", typeof(System.Int32));

dTable.Columns.Add(auto);

// create another column

DataColumn name = new DataColumn("Name", typeof(string));

dTable.Columns.Add(name);

// create one more column

DataColumn address = new DataColumn("Address", typeof(string));

dTable.Columns.Add(address);
Specifying AutoIncrement column in the DataTable
To specify a column as AutoIncrement (naturally it should be an integer type of field only), you need to set some properties of the column like AutoIncrement, AutoIncrementSeed. See the code below, here I am setting the first column "AutoID" as autoincrement field. Whenever a new row will be added its value will automatically increase by 1 as I am specified AutoIncrementSeed value as 1.
// specify it as auto increment field

auto.AutoIncrement = true;

auto.AutoIncrementSeed = 1;

auto.ReadOnly = true;
If you want a particular column to be a unique column ie. you don't want duplicate records into that column, then set its Unique property to true like below.
auto.Unique = true;
Specifying Primary Key column in the DataTable
To set the primary key column in the DataTable, you need to create arrays of column and store column you want as primary key for the DataTable and set its PrimaryKey property to the column arrays. See the code below.
// create primary key on this fieldDataColumn[] pK = new DataColumn[1];

pK[0] = auto;

dTable.PrimaryKey = pK;
Till now we have created the DataTable, now lets populate the DataTable with some data.
Populating data into DataTable
There are two ways to populate DataTable.
Using DataRow object
Look at the code below, I have created a DataRow object above the loop and I am assiging its value to the dTable.NewRow() inside the loop. After specifying columns value, I am adding that row to the DataTable using dTable.Rows.Add method.
// populate the DataTable using DataRow objectDataRow row = null;

for (int i = 0; i < 5; i++)

{



row = dTable.NewRow();

row["AutoID"] = i + 1;

row["Name"] = i + " - Ram";

row["Address"] = "Ram Nagar, India - " + i;

dTable.Rows.Add(row);

}
Instead of using the column name, you can use ColumnIndex too, however it is not suggested as you might want to add a column in the mid of the table then you will need to change your code wherever you have specified the index of the column. Same applies while reading or writing values into Database column.
Asiging the value of column using Arrays
In following code, I have specified the values of every column as the array separated by comma (,) in the Add method of the dTable.Rows.
// manually adding rows using array of valuesdTable.Rows.Add(6, "Manual Data - 1", "Manual Address - 1, USA");

dTable.Rows.Add(7, "Manual Data - 2", "Manual Address - 2, USA");
Modifying data into DataTable
Modifying Row Data
To edit the data of the row, sets its column value using row index or by specifying the column name. In below example, I am updating the 3rd row of the DataTable as I have specified the row index as 2 (dTable.Rows[2]).
// modify certain values into the DataTabledTable.Rows[2]["AutoID"] = 20;

dTable.Rows[2]["Name"] = "Modified";

dTable.Rows[2]["Address"] = "Modified Address";

dTable.AcceptChanges();
Deleting Row
To delete a row into DataTable, call the rows.Delete() method followed by AcceptChanges() method. AcceptChanges() method commits all the changes made by you to the DataTable. Here Row[1] is the index of the row, in this case 2nd row will be deleted as in collection (here rows collection) count start from 0.
// Delete rowdTable.Rows[1].Delete();

dTable.AcceptChanges();

Filtering data from DataTable
To filter records from the DataTable, use Select method and pass necessary filter expression. In below code, the 1st line will simply filter all rows whose AutoID value is greater than 5. The 2nd line of the code filters the DataTable whose AutoID value is greater than 5 after sorting it. 
DataRow[] rows = dTable.Select(" AutoID > 5");

DataRow[] rows1 = dTable.Select(" AutoID > 5", "AuotID ASC");
Note that Select method of the DataTable returns the array of rows that matche the filter expression. If you want to loop through all the filtered rows, you can use foreach loop as shown below. In this code, I am adding all the filtered rows into another DataTable.
foreach (DataRow thisRow in rows)

{



// add values into the datatable

dTable1.Rows.Add(thisRow.ItemArray);

}
Working with Aggregate functions (Updated on 18-Nov-08)
We can use almost all aggregate functions with DataTable, however the syntax is bit different than standard SQL.
Suppose we need to get the maximum value of a particular column, we can get it in the following way.
DataRow[] rows22 = dTable.Select("AutoID = max(AutoID)");

string str = "MaxAutoID: " + rows22[0]["AutoID"].ToString();
To get the sum of a particular column, we can use Compute method of the DataTable. Compute method of the DataTable takes two argument. The first argument is the expression to compute and second is the filter to limit the rows that evaluate in the expression. If we don't want any filteration (if we need only the sum of the AutoID column for all rows), we can leave the second parameter as blank ("").
object objSum = dTable.Compute("sum(AutoID)", "AutoID > 7");

string sum = "Sum: " + objSum.ToString();

// To get sum of AutoID for all rows of the DataTable

object objSum = dTable.Compute("sum(AutoID)", "");

Sorting data of DataTable
Oops !. There is no direct way of sorting DataTable rows like filtering (Select method to filter DataRows).
There are two ways you can do this.
Using DataView
See the code below. I have created a DataView object by passing my DataTable as parameter, so my DataView will have all the data of the DataTable. Now, simply call the Sort method of the DataView and pass the sort expression. Your DataView object have sorted records now, You can either directly specify the Source of the Data controls object like GridView, DataList to bind the data or if you need to loop through its data you can use ForEach loop as below.
// Sorting DataTableDataView dataView = new DataView(dTable);

dataView.Sort = " AutoID DESC, Name DESC";

foreach (DataRowView view in dataView)

{



Response.Write(view["Address"].ToString());

}
Using DataTable.Select() method
Yes, you can sort all the rows using Select method too provided you have not specified any filter expression. If you will specify the filter expression, ofcourse your rows will be sorted but filter will also be applied. A small drawback of this way of sorting is that it will return array of DataRows as descibed earlier so if you are planning to bind it to the Data controls like GridView or DataList you will have for form a DataTable by looping through because directly binding arrays of rows to the Data controls will not give desired results.
DataRow[] rows = dTable.Select("", "AutoID DESC");
Writing and Reading XmlSchema of the DataTable
If you need XmlSchema of the DataTabe, you can use WriteXmlSchema to write and ReadXmlSchema to read it. There are several overloads methods of both methods and you can pass filename, stream, TextReader, XmlReader etc. as the parameter. In this code, the schema will be written to the .xml file and will be read from there.
// creating schema definition of the DataTabledTable.WriteXmlSchema(Server.MapPath("~/DataTableSchema.xml"));

// Reading XmlSchema from the xml file we just created

DataTable dTableXmlSchema = new DataTable();

dTableXmlSchema.ReadXmlSchema(Server.MapPath("~/DataTableSchema.xml"));
Reading/Writing from/to Xml
If you have a scenario, where you need to write the data of the DataTable into xml format, you can use WriteXml method of the DataTable. Note that WriteXml method will not work if you will not specify the name of the DataTable object while creating it. Look at the first code block above, I have passed "Dynamically_Generated" string while creating the instance of the DataTable. If you will not specify the name of the DataTable then you will get error as WriteXml method will not be able to serialize the data without it.
// Note: In order to write the DataTable into XML, // you must define the name of the DataTable while creating it

// Also if you are planning to read back this XML into DataTable, you should define the XmlWriteMode.WriteSchema too 

// Otherwise ReadXml method will not understand simple xml file 

dTable.WriteXml(Server.MapPath("~/DataTable.xml"), XmlWriteMode.WriteSchema);

// Loading Data from XML into DataTable

DataTable dTableXml = new DataTable();

dTableXml.ReadXml(Server.MapPath("~/DataTable.xml"));
If you are planning to read the xml you have just created into the DataTable sometime later then you need to specify XmlWriteMode.WriteSchema too as the 2nd parameter while calling WriteXml method of the DataTable otherwise normally WriteXml method doesn't write schema of the DataTable. In the abscence of the schema, you will get error (DataTable does not support schema inference from Xml) while calling ReadXml method of the DataTable.
---------------------------------------------------------------------------------------------------------------

DataView RowFilter Syntax [C#]

This example describes syntax of DataView.RowFil­ter expression. It shows how to correctly build expression string (without „SQL injection“) using methods to escape values.

Column names

If a column name contains any of these special characters ~ ( ) # \ / = > < + - * % & | ^ ' " [ ], you must enclose the column name within square brackets [ ]. If a column name contains right bracket ] or backslash \, escape it with backslash (\] or \\).
[C#]
dataView.RowFilter = "id = 10";      // no special character in column name "id"
dataView.RowFilter = "$id = 10";     // no special character in column name "$id"
dataView.RowFilter = "[#id] = 10";   // special character "#" in column name "#id"
dataView.RowFilter = "[[id\]] = 10"; // special characters in column name "[id]"

Literals

String values are enclosed within single quotes ' '. If the string contains single quote ', the quote must be doubled.
[C#]
dataView.RowFilter = "Name = 'John'"        // string value
dataView.RowFilter = "Name = 'John ''A'''"  // string with single quotes "John 'A'"

dataView.RowFilter = String.Format("Name = '{0}'", "John 'A'".Replace("'", "''"));
Number values are not enclosed within any characters. The values should be the same as is the result of int.ToString() or float.ToString() method for invariant or English culture.
[C#]
dataView.RowFilter = "Year = 2008"          // integer value
dataView.RowFilter = "Price = 1199.9"       // float value

dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.NumberFormat,
                     "Price = {0}", 1199.9f);
Date values are enclosed within sharp characters # #. The date format is the same as is the result of DateTime.ToString() method for invariant or English culture.
[C#]
dataView.RowFilter = "Date = #12/31/2008#"          // date value (time is 00:00:00)
dataView.RowFilter = "Date = #2008-12-31#"          // also this format is supported
dataView.RowFilter = "Date = #12/31/2008 16:44:58#" // date and time value

dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.DateTimeFormat,
                     "Date = #{0}#", new DateTime(2008, 12, 31, 16, 44, 58));
Alternatively you can enclose all values within single quotes ' '. It means you can use string values for numbers or date time values. In this case the current culture is used to convert the string to the specific value.
[C#]
dataView.RowFilter = "Date = '12/31/2008 16:44:58'" // if current culture is English
dataView.RowFilter = "Date = '31.12.2008 16:44:58'" // if current culture is German

dataView.RowFilter = "Price = '1199.90'"            // if current culture is English
dataView.RowFilter = "Price = '1199,90'"            // if current culture is German

Comparison operators

Equal, not equal, less, greater operators are used to include only values that suit to a comparison expression. You can use these operators = <> < <= > >=.
Note: String comparison is culture-sensitive, it uses CultureInfo from DataTable.Locale property of related table (dataView.Table.Locale). If the property is not explicitly set, its default value is DataSet.Locale (and its default value is current system culture Thread.Curren­tThread.Curren­tCulture).
[C#]
dataView.RowFilter = "Num = 10"             // number is equal to 10
dataView.RowFilter = "Date < #1/1/2008#"    // date is less than 1/1/2008
dataView.RowFilter = "Name <> 'John'"       // string is not equal to 'John'
dataView.RowFilter = "Name >= 'Jo'"         // string comparison
Operator IN is used to include only values from the list. You can use the operator for all data types, such as numbers or strings.
[C#]
dataView.RowFilter = "Id IN (1, 2, 3)"                    // integer values
dataView.RowFilter = "Price IN (1.0, 9.9, 11.5)"          // float values
dataView.RowFilter = "Name IN ('John', 'Jim', 'Tom')"     // string values
dataView.RowFilter = "Date IN (#12/31/2008#, #1/1/2009#)" // date time values

dataView.RowFilter = "Id NOT IN (1, 2, 3)"  // values not from the list
Operator LIKE is used to include only values that match a pattern with wildcards. Wildcard character is * or %, it can be at the beginning of a pattern '*value', at the end 'value*', or at both '*value*'. Wildcard in the middle of a patern 'va*lue' is not allowed.
[C#]
dataView.RowFilter = "Name LIKE 'j*'"       // values that start with 'j'
dataView.RowFilter = "Name LIKE '%jo%'"     // values that contain 'jo'

dataView.RowFilter = "Name NOT LIKE 'j*'"   // values that don't start with 'j'
If a pattern in a LIKE clause contains any of these special characters * % [ ], those characters must be escaped in brackets [ ] like this [*], [%], [[] or []].
[C#]
dataView.RowFilter = "Name LIKE '[*]*'"     // values that starts with '*'
dataView.RowFilter = "Name LIKE '[[]*'"     // values that starts with '['
The following method escapes a text value for usage in a LIKE clause.
[C#]
public static string EscapeLikeValue(string valueWithoutWildcards)
{
  StringBuilder sb = new StringBuilder();
  for (int i = 0; i < valueWithoutWildcards.Length; i++)
  {
    char c = valueWithoutWildcards[i];
    if (c == '*' || c == '%' || c == '[' || c == ']')
      sb.Append("[").Append(c).Append("]");
    else if (c == '\'')
      sb.Append("''");
    else
      sb.Append(c);
  }
  return sb.ToString();
}
[C#]
// select all that starts with the value string (in this case with "*")
string value = "*";
// the dataView.RowFilter will be: "Name LIKE '[*]*'"
dataView.RowFilter = String.Format("Name LIKE '{0}*'", EscapeLikeValue(value));

Boolean operators

Boolean operators AND, OR and NOT are used to concatenate expressions. Operator NOT has precedence over AND operator and it has precedence over OR operator.
[C#]
// operator AND has precedence over OR operator, parenthesis are needed
dataView.RowFilter = "City = 'Tokyo' AND (Age < 20 OR Age > 60)";

// following examples do the same
dataView.RowFilter = "City <> 'Tokyo' AND City <> 'Paris'";
dataView.RowFilter = "NOT City = 'Tokyo' AND NOT City = 'Paris'";
dataView.RowFilter = "NOT (City = 'Tokyo' OR City = 'Paris')";
dataView.RowFilter = "City NOT IN ('Tokyo', 'Paris')";

Arithmetic and string operators

Arithmetic operators are addition +, subtraction -, multiplication *, division / and modulus %.
[C#]
dataView.RowFilter = "MotherAge - Age < 20";   // people with young mother
dataView.RowFilter = "Age % 10 = 0";           // people with decennial birthday
There is also one string operator concatenation +.

Parent-Child Relation Referencing

A parent table can be referenced in an expression using parent column name with Parent. prefix. A column in a child table can be referenced using child column name with Child. prefix.
The reference to the child column must be in an aggregate function because child relationships may return multiple rows. For example expression SUM(Child.Price) returns sum of all prices in child table related to the row in parent table.
If a table has more than one child relation, the prefix must contain relation name. For example expression Child(OrdersToItemsRelation).Price references to column Price in child table using relation named OrdersToItemsRe­lation.

Aggregate Functions

There are supported following aggregate functions SUM, COUNT, MIN, MAX, AVG (average), STDEV (statistical standard deviation) and VAR (statistical variance).
This example shows aggregate function performed on a single table.
[C#]
// select people with above-average salary
dataView.RowFilter = "Salary > AVG(Salary)";
Following example shows aggregate functions performed on two tables which have parent-child relation. Suppose there are tables Orders and Items with the parent-child relation.
[C#]
// select orders which have more than 5 items
dataView.RowFilter = "COUNT(Child.IdOrder) > 5";

// select orders which total price (sum of items prices) is greater or equal $500
dataView.RowFilter = "SUM(Child.Price) >= 500";

Functions

There are also supported following functions. Detailed description can be found here DataColumn.Ex­pression.
  • CONVERT – converts particular expression to a specified .NET Framework type
  • LEN – gets the length of a string
  • ISNULL – checks an expression and either returns the checked expression or a replacement value
  • IIF – gets one of two values depending on the result of a logical expression
  • TRIM – removes all leading and trailing blank characters like \r, \n, \t, ‚ ‘
  • SUBSTRING – gets a sub-string of a specified length, starting at a specified point in the string
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)