Fetch DB to Xml then bind to Asp.net Server control

Posted by Venkat | Labels: , ,

Today I am going to see how to bind or get the Data from the DB to XML file. so XML is also a
Datasource to store the data or content and you can get the data from xml file easily,it also
improves server performance. ie: instead of creating connection and request the DB to fetch the data
, Getting the Data , Closing Connection. so this operation occurs multiple times or as per
user needs.

Now what i am doing here is , first get the Table Data From DB to the XML file.
first i had created one xml file called Test.xml - to place the Employee Tables Data.

Then OnButton_Click Event i have written the code to bind the data to XML file.

Ex: i am getting the Employee Table from DB bind to the XML file.

Here is the Code :

Include Namespace

using System.IO;
using System.Data.SqlClient;


protected void Button1_Click(object sender, EventArgs e)
    {
        SqlCommand command = new SqlCommand();
        command.CommandText = "Select * from Employees";
        command.CommandType = CommandType.Text;
        command.Connection = con;
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        da.Fill(ds, "Emp");
       
        // Get a StreamWriter object
        StreamWriter xmlDoc = new StreamWriter(Server.MapPath("~/Test.xml"), false);

        // Apply the WriteXml method to write an XML document
        ds.WriteXml(xmlDoc, XmlWriteMode .WriteSchema );
        xmlDoc.Close();

       
    }
After that - i am going to bind the all the employeename and empid to the Dropdownlist.

Here is the Code : so we get the emp details on the XML file , we have to retrieve the data
from the XML file using Dataset because - Dataset has two method ReadXML and WriteXML ie: able
to read or write the data from the XML.

so after read the data from XML to Dataset, now we have all the emp details on the Dataset ds,
. just bind as its to the Dropdownlist datasource.


 DataSet ds = new DataSet();

 ds.ReadXml(Server.MapPath("~/Testdo.xml"));

DropDownlist1.DataSource = ds;
DropDownlist1.DataTextField = "empname";
DropDownlist1.DataValueField = "empid";
DropDownlist1.DataBind();
suppose i want to filter the employees whose salary is greater than 20000

write like this

dt = ds.Tables[0];
  DataRow[] dr ;
        dr = dt.Select("salary >= '20000'");

 DataTable fDt = new DataTable();
        fDt.Columns.Add("empName");
        fDt.Columns.Add("empId");

 foreach (DataRow dr1 in dr)
        {
            DataRow newrow = fDt.NewRow(); 
            newrow[0] = dr1[0];  // here you have to give correct index for the empid or empname field name
            newrow[1] = dr1[1];
           

            fDt.Rows.Add(newrow);
        }

dropdownlist2.DataSource = fDt;
DropDownlist1.DataTextField = "empname";
DropDownlist1.DataValueField = "empid";
DropDownlist1.DataBind();

PayOffers.in