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.