How to change NewDataSet xml root name while creating xml using WriteXml() method of Dataset?

This is default behavior of WriteXml() method when we create xml then it adds  <NewDataSet> node as root xml node and <Table> as child node. In this article, I am explaining- How to change <NewDataSet> xml root name while creating xml using WriteXml() method of Dataset?

Let first see the default case-

Suppose you we have one DataSet object ds and we are filling it will data source, like this-

Dataset ds= new DataSet();
ds= datasource;

After that we are using WriteXml() method to create XML, like this-

ds.WriteXml("FileName");

Then in this case output will be like this-

<NewDataSet>
  <Table>
    <Emp_ID>717</Emp_ID>
    <Emp_Name>Jitendra Faye</Emp_Name>
    <Salary>35000</Salary>
  </Table>
----------
----------
----------
</NewDataSet>

Here we can see that <NewDataSet> and   <Table> is default tag here.

Now to change these default tag follow this code, Here I am changing <NewDataSet> tag as <EmployeeRecords> tag and <Table> tag as <Employee> tag.

Here is code-

Dataset ds= new DataSet();
ds= datasource;

//Giving Name to DataSet, which will be displayed as root tag

ds.DataSetName="EmployeeRecords";

//Giving Name to DataTable, which will be displayed as child tag

ds.Tables[0].TableName = "Employee";

//Creating XML file
ds.WriteXml("FilePath");

Now after executing this above code generated XML file will be like this-

<EmployeeRecords>
  <Employee>
    <Emp_ID>717</Emp_ID>
    <Emp_Name>Jitendra Faye</Emp_Name>
    <Salary>35000</Salary>
  </Employee>
----------
----------
----------
</EmployeeRecords>


Here we can see that DataSet name is coming as root tag and DataTable name is coming as child tag.

Note-

Before giving name to DataTable (ds.Tables[0].TableName) first check that it should not be null otherwise it will cause an error.



Thanks

How to compare DataTables?

Data Tables can be easily compared. This comparison can be done using Merge () and GetChanges() methods of DataTable class object.

In this article, I am taking 3 DataTable class object- dataTable1, dataTable2 and dataTable3
Here dataTable3 will hold the comparison result of dataTable1 and dataTable2.

Here is simple code-

//Merging datatable2 to data table 
dataTable1.Merge(dataTable2);

//Getting changes in dataTable3
 DataTable dataTable3= dataTable2.GetChanges();


In above lines of code, first I am merging datatTable1 and dataTable2 and combined result will be stored in dataTable1. Finally to get changes I am using GetChanges() method of dataTable1 object.

DataTable dataTable3= dataTable2.GetChanges();

Here GetChanges() method will return one DataTable class object and  returned result will be stored in dataTable3 object.



Thanks

How to search record in DataTable?

DataTable-

DataTable is server side object in .Net which is collection of Rows and Columns. It represents the in-memory table's data. It can hold data of any single table but using join we can hold data from multiple table. In programming it is used to hold data after getting data from database.

Generally we use DataTable to hold result-set (query's result), but in some of the cases we want to perform searching operation on DataTable. Here I am explaining - How to search record in DataTable?

If we want to search any data in DataTable object then it can be done by using looping, but this is not a good approach because DataTable have some inbuilt methods to complete this task.

There are following methods-

1. Find ()  - Using this method we can perform searching on DataTable based on Primary  key, it   
                    returns DataRow object.

2. Select() - Using this method we can perform searching on DataTable based on search criteria   
                    means by using column name we can search , it returns array of DataRow object.

Here I am explaining both methods, Follow this code-
 
 private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                //Creating DataTable object
                DataTable dtRecords = new DataTable();

                //Creating DataColumn object
                DataColumn colName = new DataColumn("Name");
                DataColumn colID = new DataColumn("ID");

                //Adding DataColumns to DataTable
                dtRecords.Columns.Add(colID);
                dtRecords.Columns.Add(colName);

                //Creating Array of DataColumn to set primary key for DataTable
                DataColumn[] dataColsID = new DataColumn[1];
                dataColsID[0] = colID;

                //Setting primary key
                dtRecords.PrimaryKey = dataColsID;

                //Adding static data to DataTable by creating DataRow object
                DataRow row1 = dtRecords.NewRow();
                row1["ID"] = 1;
                row1["Name"] = "Rajendra";
                dtRecords.Rows.Add(row1);

                DataRow row2 = dtRecords.NewRow();
                row2["ID"] = 2;
                row2["Name"] = "Rakesh";
                dtRecords.Rows.Add(row2);

                DataRow row3 = dtRecords.NewRow();
                row3["ID"] = 3;
                row3["Name"] = "Pankaj";
                dtRecords.Rows.Add(row3);

                DataRow row4 = dtRecords.NewRow();
                row4["ID"] = 4;
                row4["Name"] = "Sandeep";
                dtRecords.Rows.Add(row4);

                DataRow row5 = dtRecords.NewRow();
                row5["ID"] = 5;
                row5["Name"] = "Rajendra";
                dtRecords.Rows.Add(row5);

                //Find() Method

                //Finding data in DataTable based on primary key using Find() method
                DataRow FindResult = dtRecords.Rows.Find("3");

                if (FindResult != null)
                {
                   //Showing result to grid
                    DataTable dtTemp = new DataTable();
                    dtTemp = dtRecords.Clone();
                    dtTemp.Rows.Add(FindResult.ItemArray);
                    dataGridFind.DataSource = dtTemp;
                }

               //Select () Method

               //Selecting data from DataTable based on search criteria using Select() method
                DataRow[] SelectResult = dtRecords.Select("name='Rajendra'");

                if (SelectResult != null)
                {
                   //Showing result to grid
                    DataTable dtTemp = SelectResult.CopyToDataTable();
                    dataGridSelect.DataSource = dtTemp;
                }
            }
            catch (Exception)
            {
                //Handle exception here
                throw;
            }
 }

Note -  

Before using Find () of DataTable it is must that we need to Primary key for DataTable object. Because Find () method works based on primary key value only.

In above code, I have created on DataTable object (dtRecords) using DataTable class, and I have filled this DataTable object with some static records. For this I have used DataColumn and DataRow classes. I have set Primary key column for DataTable object, so that we can use Find () method over DataTable.

//Setting primary key
 dtRecords.PrimaryKey = dataColsID;

Here  dataColsID is array of DataColumn. After setting primary key to DataTable, I have Find () method like this-

//Finding data in DataTable based on primary key using Find () method
 DataRow FindResult = dtRecords.Rows.Find("3");

Here Find method will return result as DataRow object, for fetching data from DataTable object using Find () method we need to pass primary key value because based on that it will return result.

For getting data using Select () method, I have written code like this-

//Selecting data from DataTable based on search criteria using Select () method
DataRow[] SelectResult = dtRecords.Select("name='Rajendra'");

Select () method works based on search criteria, Here ("name='Rajendra'“) is search criteria for Select () method, it returns array of DataRow object.

Finally to show result I have used two separate DataGridVIew controls and output will be like this-

As we can see in image, For Find () method, one record in coming because in DataTable object we have only one record with ID=3. And for Select () Method, 2 records are coming because we have 2 records in DataTable obejct with name = "Rajendra".

To perform search operation on DataTable Find() and Select() methods can be used but both methods have different criteria. 

Note - 

1. If we have primary key in table then Find() and Select() both methods can be used
2. If we don't have primary key then we can use only Select() method.


Thanks

How to convert DataTable to HTML Table?

Asp.Net provides different data controls to display data on page but in some cases if you don't want to use those controls then we can simply use Custom table to display data on page. Here I am explaining, How we can convert DataTable (data source) to HTML table?

Asp.Net provides classes like Table, TableRow, TableCell to create table using code, we can easily create table using these classes and we can display data in created table. In this example, I have Used Table, TableRow, TableCell Classes to create Table.

Here is code-

protected void btnGetData_Click(object sender, EventArgs e)
    {
        //Code to get data from database
        SqlConnection cn = new SqlConnection("Connection string");
        cn.Open();
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("select ID,Name from tabEmployee", cn);
        da.Fill(dt);
      
        //Code to show Data table’s data to HTML table
        Table tbl = new Table();
        tbl.CellPadding = 0;
        tbl.CellSpacing = 0;

       //Flag to add Header for HTML table
        bool AddedColumnName = false;

        //Looping for getting Data table’s data
        foreach (DataRow dtRow in dt.Rows)
        {
            TableRow row = new TableRow();
            foreach (DataColumn col in dt.Columns)
            {
                //Adding heading to HTML table
                if (AddedColumnName == false)
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.BackColor = System.Drawing.Color.Green;
                    cell.ForeColor = System.Drawing.Color.White;
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    cell.Text = col.ColumnName;
                    row.Cells.Add(cell);
                }
                //Adding data to HTML table
                else
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.Text = dtRow[col].ToString();
                    row.Cells.Add(cell);
                }
            }

            tbl.Rows.Add(row);
            AddedColumnName = true;
        }

        //Adding created HTML table to Panel control
        pnlData.Controls.Add(tbl);
    }

In this code, I have read data from database and stored on one data source- DataTable.
Now using looping, I have iterated all the data to create HTML table. First I am creating header for the table, for this I have written code like this- 

//Adding heading to HTML table
                if (AddedColumnName == false)
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.BackColor = System.Drawing.Color.Green;
                    cell.ForeColor = System.Drawing.Color.White;
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    cell.Text = col.ColumnName;
                    row.Cells.Add(cell);
                }

This line of code will add header to the table by getting column names from result set, These column names cab be changed in select query.

Now to add data of result set I have used this code- 

//Adding data to HTML table
                else
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.Text = dtRow[col].ToString();
                    row.Cells.Add(cell);
                }

This code will be executed for each row in result set except header row. For creating HTML table, I have used Table, TableRow and TableCell classes, in foreach loop for every row in DataTable I am creating object for TableRow class and for every column object for TableCell class.

To add data to HTML Table cell using this code-

row.Cells.Add(cell);

Here "row" is object of TableRow and "cell" is object of TableCell class.
 
Output-

Thanks