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);
}}
Here we again built our Connection string and executed command for extracting all the data from chosen table.
we have taken that data as dataset.
We took a filestream to write xml file.
then we wrote the file using
dst.WriteXml(stream);
dst is object of our dataset after creating file close connection to databse and prompt for creation of file.
We have generated Xml file in the same way you can generate Binary File too. for that be with us and wait for next post.
Thank you.
Happy Coding.
P.S: Yu may download the project from here
