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();