Binding XML Data In DropDownList After Showing Table Data In Grid View And Download In Excel

In this article, we will discuss how to bind an XML File Data to a ComboBox Control. From combo box, we need to show the table data into GridView.

Introduction

Here, we will learn how to bind XML data to a Combobox control. Prior to starting, let's see what XML is.

What is XML?
  • XML stands for eXtensible Markup Language
  • XML is a markup language much like HTML
  • XML was designed to store and transport data
  • XML was designed to be self-descriptive 
  • It was primarily designed to carry/transport data whereas HTML is used to display/present data although it is much like HTML.
  • XML documents contain structured information. It is based on tags. (See Note.xml file.)

    xml
I am taking an Employee Example and I will add all four of these  titles in my combo box. For this, first, open your Visual Studio and design a form like below.

XML
 
After this, write the below code in your .cs file. For XmlTextReader, we need to import some namespaces.
  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.IO;  
  5. using System.Windows.Forms;  
  6. using System.Xml;  
  7. In form load iam calling BindDropDown method  
  8. private void Form1_Load(object sender, EventArgs e) {  
  9.     BindDropDown();  
  10. }  
  11. BindDropDown Method: public void BindDropDown() {  
  12.     /*Here for testing put your xml file in debuger folder*/  
  13.     XmlTextReader xmdatareader = new XmlTextReader("Xml/Tablenames.xml");  
  14.     DataSet _objdataset = new DataSet();  
  15.     _objdataset.ReadXml(xmdatareader);  
  16.     comboBox1.DataSource = _objdataset.Tables[0];  
  17.     comboBox1.DisplayMember = "title_Text";  
  18.     comboBox1.ValueMember = "title_Text";  
  19. }  
In BindDropDown method, we are giving our XML file path. Actually, it is in my Debug folder - /XML/Employee.xml

XML
 
After this, I am taking the DataSet. From this dataset, I am reading my XML file. After reading this, we are binding this to combobox.
  1. comboBox1.DataSource = _objdataset.Tables[0];  
  2. comboBox1.DisplayMember = "title_Text";  
  3. comboBox1.ValueMember = "title_Text";  
Here, title_Text is my column name. Now, my combo box is bound with XML data.

XML
 
Now, if I select anything, I need to display that in my GridView. Here is code for that.
  1. private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)  
  2. {  
  3.     string selectedtable = Convert.ToString(comboBox1.SelectedValue);  
  4.     string constring = "data source=SQLSERVERNAME;initial catalog=khadar;persist security info=True;user id=sa;password=qwaszx@123";  
  5.     if (!string.IsNullOrEmpty(selectedtable) && selectedtable != "System.Data.DataRowView")  
  6.     {  
  7.         string query = "select top 15 * from dbo." + selectedtable;  
  8.         SqlConnection con = new SqlConnection(constring);  
  9.         con.Open();  
  10.         SqlCommand cmd = new SqlCommand();  
  11.         cmd.CommandText = query;  
  12.         cmd.CommandType = CommandType.Text;  
  13.         SqlDataAdapter adapter = new SqlDataAdapter(query, con);  
  14.         DataSet ds = new DataSet();  
  15.         DataTable dt = new DataTable();  
  16.         adapter.Fill(dt);  
  17.         // this.dataGrid1.SetDataBinding(ds,"MyTable"); or  
  18.         dataGridView1.DataSource = dt;  
  19.     }  
  20. }  
Give your connection string and now, we have bound this in grid view as well. Now, I need to download this in Excel format.

XML
 
And, we need to add a dll file to download this in Excel. Go to Solution Explorer and add References and add the below-mentioned dll.

XML 

You can also try installing it in Visual Studio via Package Manager.

Run Install-Package Microsoft.Office.Interop.Excel in the Package Console. This will automatically add it as a project reference.

After this, we need to write a code in Button Click event.

Code
  1. private void ExpoertToExcel_Click(object sender, EventArgs e)  
  2. {  
  3.     Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();  
  4.     // creating new WorkBook within Excel application  
  5.     Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);  
  6.     // creating new Excelsheet in workbook  
  7.     Microsoft.Office.Interop.Excel._Worksheet worksheet = null;  
  8.     // see the excel sheet behind the program  
  9.     app.Visible = true;  
  10.     // get the reference of first sheet. By default its name is Sheet1.  
  11.     // store its reference to worksheet  
  12.     worksheet = workbook.Sheets["Sheet1"];  
  13.     worksheet = workbook.ActiveSheet;  
  14.     // changing the name of active sheet  
  15.     worksheet.Name = "Exported from gridview";  
  16.     // storing header part in Excel  
  17.     for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) {  
  18.         worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;  
  19.     }  
  20.     // storing Each row and column value to excel sheet  
  21.     for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) {  
  22.         for (int j = 0; j < dataGridView1.Columns.Count; j++) {  
  23.             worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();  
  24.         }  
  25.     }  
  26.     // save the application  
  27.     workbook.SaveAs("C:\\Users\\khadarbasha.shaik\\Documentsoutput.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);  
  28.     // Exit from the application  
  29.     app.Quit();  
  30. }  
This code will extract the Excel in dcuments.

Please feel free to comment. If you have any doubts, please do contact me.
X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now