MDF TO XML Converter

Published on September 8 2010 by Inno Design

In now a days for data processing one need to use XML instead of any other relational data source.Because the targeted customer may not have relational compatible software. And if they include needful files then the package becomes heavy so its better to use xml file.The presented software solves this problem.

here’s the look of the software that we are going to design

The working is very simple. Following are the steps for processing

Building Connectionstring of choosen database
Extracting table names of the database
Generating XML file of the tables data with the same schema

Connection String:

A typical connection string consist of following values

Server’s name
Database’s name
Username and Password (in case of SQL authentication)

All the values has been taken using UI then the user will be required to press a button to extract all the table names inside chosen Database.

The click event of that button is like this

private void btnTables_Click(object sender, EventArgs e)
     {
         string con = "Data Source=";
         con = con + txtServerName.Text + ";";
         con = con + "Initial Catalog=" + txtDbName.Text + ";";

         if (!(string.IsNullOrEmpty(txtPwd.Text) || string.IsNullOrEmpty(txtUid.Text)))
         {
             con = con + "uid=" + txtUid.Text + ";" + "pwd=" + txtPwd.Text;
         }
         else
         {
             con = con + "Integrated Security=True;User Instance=True;";
         }
         SqlConnection connect = new SqlConnection(con);
         try
         {
             connect.Open();
             string cmd = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ORDER BY TABLE_NAME";
             SqlDataReader dst = SqlHelper.ExecuteReader(connect, CommandType.Text, cmd);
             comboBox1.Text = "Select Table";
             while (dst.Read())
             {
                 comboBox1.Items.Add(dst["TABLE_NAME"].ToString());
             }
         }
         catch (Exception ex)
         {
             MessageBox.Show(ex.Message);
         }

     }

con is our connection string.

in the try block we have given code for extracting tables name.

the querry for extracting all the table names is

“SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ORDER BY TABLE_NAME”

SqlHelper is a class which is available on internet, it increases the speed of processing.

If you don’t want to do this then you can implement it using the very conventional way that’s by creating SQLConnction and SQlcommand. here’s the code for that

private void btnTables_Click(object sender, EventArgs e)
     {
         string con = "Data Source=";
         con = con + txtServerName.Text + ";";
         con = con + "Initial Catalog=" + txtDbName.Text + ";";

         if (!(string.IsNullOrEmpty(txtPwd.Text) || string.IsNullOrEmpty(txtUid.Text)))
         {
             con = con + "uid=" + txtUid.Text + ";" + "pwd=" + txtPwd.Text;
         }
         else
         {
             con = con + "Integrated Security=True;User Instance=True;";
         }
         SqlConnection connect = new SqlConnection(con);
         try
         {
             string cmd = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ORDER BY TABLE_NAME";

             SqlCommand Scmd = new SqlCommand(cmd, connect);

             connect.Open();

             SqlDataReader dst = Scmd.ExecuteReader();
             comboBox1.Text = "Select Table";
             while (dst.Read())
             {
                 comboBox1.Items.Add(dst["TABLE_NAME"].ToString());
             }
         }
         catch (Exception ex)
         {
             MessageBox.Show(ex.Message);
         }

     }

So by now we are done with extracting Tables name.

now user will choose the table, and the path where he want XML file to be saved.

here’s click event of the button for generating XML file

private void btnConvert_Click(object sender, EventArgs e)
        {
            string con = "Data Source=";
            con = con + txtServerName.Text+";";
            con = con + "Initial Catalog=" + txtDbName.Text + ";";

            if (!(string.IsNullOrEmpty(txtPwd.Text) || string.IsNullOrEmpty(txtUid.Text)))
            {
                con = con + "uid=" + txtUid.Text + ";" + "pwd=" + txtPwd.Text;
            }
            else
            {
                con = con + "Integrated Security=True;User Instance=True;";
            }
            SqlConnection connect = new SqlConnection(con);
            try
            {
                string filename;
                connect.Open();

                string strcmd = "Select * from " + comboBox1.Text;
                DataSet dst = SqlHelper.ExecuteDataset(connect, CommandType.Text, strcmd);
                if(string.IsNullOrEmpty(txtLocation.Text))
                {
                    filename= "Xml_"+comboBox1.Text+".xml";
                }
                else
                {
                    filename = txtLocation.Text + "Xml_" + comboBox1.Text + ".xml";
                }
                FileStream stream = new FileStream(filename,FileMode.Create);
                dst.WriteXml(stream);
                connect.Close();
                MessageBox.Show("File Created");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }