Datatable Operation Part -One

Hi all
The DataTable is a central object in the ADO.NET library.
Dot Net allows us to create our own Tables and work with them. The DataTable is an in-memory representation of a block of data. We can create our own tables in code using a DataSet and the types defined in the System.Data.OleDb or System.Data.SqlClient namespaces.
Here we are going to discuss some common and daily life useful operation on datatable.
You can check more detail at msdn:



1)Read All row from datatable

Code:

---

private void ReadAllRow(DataTable dataTable)
{
//First method
foreach (DataRow dataRow in dataTable.Rows)
{
//dataRow["ColumnName"].ToString();
//Use Datarow with column name for more operation
}
//Second Method
for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
{
//dataTable.Rows[rowIndex]["ColumnName"].ToString();
//Use Datatable row
}
}

---

2) Read All column from datatable

Code:

---

//This will show how to read all column
private void ReadAllColumn(DataTable dataTable)
{
foreach (DataRow dataRow in dataTable.Rows)
{
foreach (DataColumn column in dataTable.Columns)
{
Console.WriteLine(dataRow["ColumnName"]);
}
}
}

---

3) Datatable Copy
//Copies both the structure and data for this DataTable.

Code:

---

private void CreateNewDataTableWithData(DataTable dataTable)
{
DataTable newDataTable = new DataTable();
newDataTable = dataTable.Copy();
dataTable.AcceptChanges();//Commit All changes in datatable
}

---

4) Datatable Clone

Code:

---

//Clones the structure of the DataTable, including all DataTable schemas and constraints.
private void CreateNewDataTableWithStructure(DataTable dataTable)
{
DataTable newDataTable = new DataTable();
newDataTable = dataTable.Clone();
dataTable.AcceptChanges();//Commit All changes in datatable
}

---

5) Datatable Filtering

Code:

---

private void FilterDataTable(DataTable dataTable)
{
string query = "ID=3";
DataTable newDataTable = new DataTable();
dataTable.DefaultView.RowFilter = query;
newDataTable = dataTable.DefaultView.ToTable();
newDataTable.AcceptChanges();
//Now newDatatable contaion only filter record
}

---

6) Select row in Datatable

Code:

---

private void SelectRowInDataTable(DataTable dataTable)
{
DataRow[] dataRow;
string query = "ID=3";
dataRow = dataTable.Select(query);
//Now dataRow array contain filter record
}

---

7) Accept and reject changes in datatable

Code:

---

private void AcceptOrRejectChanges(DataTable dataTable)
    {
        // If there are errors, try to reconcile.
        if (dataTable.HasErrors)
        {
            if (Reconcile(dataTable))
            {
                // Fixed all errors.
                dataTable.AcceptChanges();
                //Commits all the changes made to this table since the last time AcceptChanges was called.

            }
            else
            {
                // Couldn'table fix all errors.
                dataTable.RejectChanges();
                //Rolls back all changes that have been made to the table since it was loaded, or the last time AcceptChanges was called
            }
        }
        else
            // If no errors, AcceptChanges.
            dataTable.AcceptChanges();
    }

    private bool Reconcile(DataTable thisTable)
    {
        foreach (DataRow row in thisTable.Rows)
        {
            //Insert code to try to reconcile error.

            // If there are still errors return immediately
            // since the caller rejects all changes upon error.
            if (row.HasErrors)
                return false;
        }
        return true;
    }

---

8) Clear datatable

Code:

---

//The following example clears the table of all data.
private void ClearTable(DataTable dataTable)
{
try
{
dataTable.Clear();
}
catch (DataException e)
{
// Process exception and return.
Console.WriteLine(e);
}
}

---

9) SUM, COUNT FUNCTION IN DataTable

Code:

---

private void Compute(DataTable dataTable)
{
// Declare an object variable.
object sumObject1, sumObject2;
//sums the values of a column named "Total for employee id 5
sumObject1 = dataTable.Compute("Sum(Total)", "EmpID = 5");
//This work as "SELECT SUM(Total) FROM Table1 WHERE EmpID = 5"
sumObject2 = dataTable.Compute("Quantity * Unit", "EmpID <>
}

---

10) Create Datareader using datatable

Code:

---

private void CrateDataReader(DataTable dataTable)
{
DataTableReader reader = dataTable.CreateDataReader();
}

---

11) GENERATE XML using Datatble

Code:

---

public void GenerateXMLMenu(DataTable dataTable)
{
string XMLPath = "C:/Test.xml";
try
{
//string fileName = HttpContext.Current.Server.MapPath(XMLPath);
string fileName = XMLPath;
dataTable.WriteXml(fileName, XmlWriteMode.WriteSchema);
}
catch (Exception exInner)
{
throw exInner;
}
}

---

12) Raad XML in Datatble

Code:

---

public DataTable ReadXMLMenu(DataTable dataTable)
{
string XMLPath = "C:/Test.xml";
try
{
dataTable.ReadXmlSchema(XMLPath);
dataTable.AcceptChanges();
dataTable.ReadXml(XMLPath);
dataTable.AcceptChanges();
return dataTable;
}
catch (Exception exInner)
{
throw exInner;
}
}

---

Above was some common function, wait for my next article for more detail on datatable.


Happy Programming!!!!!

Quote:

---

Originally Posted by shakti5385

7) Accept and reject changes in datatable

Code:

---

private void AcceptOrRejectChanges(DataTable dataTable)
{
// If there are errors.
if (dataTable.HasErrors)
{
// Fixed all errors.
dataTable.AcceptChanges();
//Commits all the changes made to this table since the last time AcceptChanges was called.
}
else
{
// Couldn'table fix all errors.
dataTable.RejectChanges();
//Rolls back all changes that have been made to the table since it was loaded, or the last time AcceptChanges was called
}
}

---

---

I don't get why you're only accepting changes IF there's an error?

Quote:

---

Originally Posted by RudiVisser

I don't get why you're only accepting changes IF there's an error?

---

Thanks changes done :)