Generate XML from SQL Server 2008 and update the database from XML

• Download test.zip - 411.2 KB

Introduction

My tutorial is going to show the code and steps for creating a Windows Forms application that:

1. Connects to a SQL Server database with a connection string provided in a XML file (that way you fill in the connection string only once and let the program connect to the database(DB) whenever it needs to).
2. Shows in a datagridview the DB contents.
3. Generates a XML file with the DB structure and data.
4. Updates the DB from a XML file.

For the record: 

The structure for my database is:

Background

People should be familiar with MySQL queries and SQL Server 2008.

When I first used the DataSet and DataAdapter in C#, I was completelly puzzled what it actually meant.

After I worked around it a little I realised that I can picture it as being a way for c# for reading the structure of the database in it. As if you had an ERD (Entity Relational Diagram) loaded in your program.

Using the code

First of all, we should set up our form.

Drag into it the following:

1. A gridview
2. Three buttons
3. A label

It can look like this:

The layout is explained in this picture:

1. Show database fields in Datagridview

This can be done really dynamically.

Folow the tutorial here: http://www.youtube.com/watch?v=F_Ycn3Ck9tA.

Binding the datagridview to a data source cand be don with this line of code also:

dataGridView1.DataSource = dsSelectSimpluStatic.Tables[0]; 

 where dsSelectSimpluStatic is out DataSet which we filled with the DataAdapter and Tables[0] is the table we want to put in there by index. 

All our application code is here

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Xml;
using System.Data.Sql;
using System.IO;
using MySql.Data.Types;

namespace TransferDate
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.persoanaTableAdapter.Fill(this.persoanaTestDataSet.Persoana);
        }

        static private string GetConnectionString()
        {
            string Username=null;
            string Parola=null;
            string BD = null;

            DataSet dsConexiune = new DataSet();
            
            dsConexiune.ReadXml("conexiune.xml");

            string Server =dsConexiune.Tables["Date"].Rows[0]["Server"].ToString();

            Username = dsConexiune.Tables["Date"].Rows[0]["Username"].ToString();

            Parola = dsConexiune.Tables["Date"].Rows[0]["Parola"].ToString();

            BD = dsConexiune.Tables["Date"].Rows[0]["BazaDate"].ToString();

            return "Data Source=" + Server + 
              ";Database="+BD+";User ID=" + 
              Username + " ;Password=" + Parola;
            //return "server=" + Server +
            //    ";database="+BD+";uid=" +
            // Username + " ;pwd=" + Parola;

        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.InitialDirectory = @"c:\";
            fdlg.RestoreDirectory = true;
            fdlg.ShowDialog();
            
            string stringconexiune = GetConnectionString();

            SqlConnection conexiune = new SqlConnection(stringconexiune);

            conexiune.Open();
          
            //string ScriereXML;
            SqlCommand incarcaPersoana = new SqlCommand(
              "select Nume,Prenume,CNP,Telefon,id_pers from Persoana", conexiune);

            DataSet dsPersoana = new DataSet();

            SqlDataAdapter daPers = new SqlDataAdapter(
              "select Nume,Prenume,CNP,Telefon,id_pers from Persoana", conexiune);
            daPers.Fill(dsPersoana);


            System.IO.StreamWriter xmlSW1 = new System.IO.StreamWriter(fdlg.FileName);
            dsPersoana.WriteXml(xmlSW1, XmlWriteMode.IgnoreSchema);
            xmlSW1.Close();

            MessageBox.Show("XML generat cu succes!");

            conexiune.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {   
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.InitialDirectory = @"c:\";
            fdlg.RestoreDirectory = true;
            fdlg.ShowDialog();

            string stringconexiune = GetConnectionString();

            SqlConnection conexiune = new SqlConnection(stringconexiune);
            conexiune.Open();


            DataSet dsCitireXML = new DataSet();
            dsCitireXML.ReadXml(fdlg.FileName, XmlReadMode.ReadSchema);

            string updateStr = "update Persoana set Nume=@Nume," + 
              "Prenume=@Prenume,Telefon=@Telefon,CNP=@CNP where id_pers=@id_pers";
            SqlCommand updateCmd = new SqlCommand(updateStr, conexiune);


            SqlParameter pTelefon = new SqlParameter();
            pTelefon.ParameterName = "@Telefon";

            SqlParameter pCNP = new SqlParameter();
            pCNP.ParameterName = "@CNP";

            SqlParameter pNume = new SqlParameter();
            pNume.ParameterName = "@Nume";

            SqlParameter pPrenume = new SqlParameter();
            pPrenume.ParameterName = "@Prenume";

            SqlParameter pid_pers = new SqlParameter();
            pid_pers.ParameterName = "@id_pers";
                
            updateCmd.Parameters.Add(pNume);
            updateCmd.Parameters.Add(pPrenume);
            updateCmd.Parameters.Add(pTelefon);
            updateCmd.Parameters.Add(pCNP);
            updateCmd.Parameters.Add(pid_pers);
            
            for(int i=0;i<dsCitireXML.Tables["Table"].Rows.Count;i++)
            {
                pNume.Value = dsCitireXML.Tables["Table"].Rows[i]["Nume"];
                pPrenume.Value = dsCitireXML.Tables["Table"].Rows[i]["Prenume"];
                pCNP.Value = dsCitireXML.Tables["Table"].Rows[i]["CNP"];
                pTelefon.Value = dsCitireXML.Tables["Table"].Rows[i]["Telefon"];
                pid_pers.Value = dsCitireXML.Tables["Table"].Rows[i]["id_pers"];
                updateCmd.ExecuteNonQuery();
            }
            conexiune.Close();
        }
    }
}

Now let's explain

1. We add our XML and MySql references:
C#

using System.Data.SqlClient; 
using System.Xml; 

2. Write code for connecting to our database.
1. We first write a function for getting our connectionstring from the XML:

After declaring our variables we read our connection xml (conexiune.xml) data into our dataset. (1)

Now we access our data in the dataset so we assign it to our variables for the connection string. We access the tables in the dataset (since the dataset is basically a ERD in the programming language it is composed of tables and relationships between them). In this scenario we have only one table named "Persoana" (Person in English) and in our XML the name of our first tree is "Date" (Data in English). So basically we are telling our program to look for the table with the name "Date" that was read from our XML. The rows are just the same concept as if we were to imagine a relational database structure, so the attributes are our rows and we want to get the connection data from our rows and not the columns (columns would be if we wanted all the servers or all the databases or a collection of the same thing, but this is not the case here). The only index that worked well was "0" ,I assume it is because in the xml structure that is the index the "Date" occupies (don't know for sure). The "Server", "Username" etc. in the parenthesis are the names of the xml tags which values I want to access. And finally I do a .ToString() to it all since I have strings on the left side. (2)

Finally we return our connection string.(3)

C#

static private string GetConnectionString()  {
    //these are the variables used for containing the username,password(Parola) and Database Name(B//D)(1)
    string Username=null;
    string Parola=null;
    string BD = null;
    //we declare our DataSet for our connection
    DataSet dsConexiune = new DataSet();
     
    dsConexiune.ReadXml("conexiune.xml");  
    //here we acces the tables(2) 
    string Server =dsConexiune.Tables["Date"].Rows[0]["Server"].ToString();

    Username = dsConexiune.Tables["Date"].Rows[0]["Username"].ToString();
    Parola = dsConexiune.Tables["Date"].Rows[0]["Parola"].ToString();
    BD = dsConexiune.Tables["Date"].Rows[0]["BazaDate"].ToString();

    //(3) 
    return "Data Source="+Server + ";Database="+BD+";User ID="+Username + " ;Password=" + Parola;
}

conexiune.xml is our xml document which contains our connection settings.

In this line of code we asign the server name by accesing the XML.

C#

string Server =dsConexiune.Tables["Date"].Rows[0]["Server"].ToString();

"Date" is the name of my XML tree that I have,and "Server" is the name of the value I want to acces.

This is a screenshot of how the connection XML looks like:



2. After that we transfer the connection string to all of our functions so that we do not need to re-login our database.

3. Next write text for generating our XML.

For more user interaction we use the OpenFileDialog to provide the program with a xml's address in which to write the data. (1)

We open our connection to the DB. (2)

After opening the db connection we declare a SQL command (incarcaPersoana=loadPerson) to select the Name, Surname,CNP,Telephone and id_pers(the unique identifier) from out table Persoana(Person) via our connection to the database (conexiune). We continue by declaring another dataset for our person. The SqlAdapter is like the person you tell what you want to load from the database in the dataset.It's like giving instructions to a person on how to make a healthy breakfast.

I could have put as an argument for the SqlDataAdapter the incarcaPersoana command or just declare as a string (they both should work).The Fill method is used to populate our dataset.

So now I am managing information from the DB to the XML file.

Using a StreamWriter I write the database structure and data to the address of the xml file provided via the FileDialog at the beginning of the program. Using the WriteXml method of the dataset I write generate the XML file. At the end of the program I show a message saying that the XML file has been successfully generated ("XML generat cu succes!").Then I close the DB connection.(3)

C#

private void button2_Click(object sender, EventArgs e)
{
    //(1)
    OpenFileDialog fdlg = new OpenFileDialog();
    fdlg.InitialDirectory = @"c:\";
    fdlg.RestoreDirectory = true;
    fdlg.ShowDialog();       
    //(2) 
    string stringconexiune = GetConnectionString();

    SqlConnection conexiune = new SqlConnection(stringconexiune);
    //(3)
    conexiune.Open();
        
    //string ScriereXML;
    SqlCommand incarcaPersoana = new SqlCommand(
      "select Nume,Prenume,CNP,Telefon,id_pers from Persoana", conexiune);

    DataSet dsPersoana = new DataSet();

    SqlDataAdapter daPers = new SqlDataAdapter(
      "select Nume,Prenume,CNP,Telefon,id_pers from Persoana", conexiune);
    daPers.Fill(dsPersoana);


    System.IO.StreamWriter xmlSW1 = new System.IO.StreamWriter(fdlg.FileName);
    dsPersoana.WriteXml(xmlSW1, XmlWriteMode.IgnoreSchema);
    xmlSW1.Close();

    MessageBox.Show("XML generat cu succes!");

    conexiune.Close();
}

4. We write code for updating our DB via the XML.

We start again by opening a FileDialog to select our XML source file (Remember! The xml must have the same structure as your database-->else it won't work). (1)

Then we connect to our database as before. (2)

This time we want to update our database. This can become difficult when having a foreign key since these can not be over written. So basically I have constructed a parametrised query where I don't update my id_pers fk I just use it to go through the database. (3)

What follows after the SqlCommand is how to further declare our parameters. We instantiate and give them a name. (4)

Now every one of those parameters need to be linked somehow to the updated command we had earlier so that is the code to do it. (5)

After words we go through the each row in the dataset and update the database (nonquery is for commands executed on the db). And finally we close our connection.(6)

C#

private void button3_Click(object sender, EventArgs e)
{
//(1)
    OpenFileDialog fdlg = new OpenFileDialog();
    fdlg.InitialDirectory = @"c:\";
    fdlg.RestoreDirectory = true;
    fdlg.ShowDialog();
//(2)
    string stringconexiune = GetConnectionString();

    SqlConnection conexiune = new SqlConnection(stringconexiune);
    conexiune.Open();

//(3)
    DataSet dsCitireXML = new DataSet();
    dsCitireXML.ReadXml(fdlg.FileName, XmlReadMode.ReadSchema);
    string updateStr = "update Persoana set Nume=@Nume," +
      "Prenume=@Prenume,Telefon=@Telefon,CNP=@CNP where id_pers=@id_pers";
    SqlCommand updateCmd = new SqlCommand(updateStr, conexiune);

//(4)
    SqlParameter pTelefon = new SqlParameter();
    pTelefon.ParameterName = "@Telefon";

    SqlParameter pCNP = new SqlParameter();
    pCNP.ParameterName = "@CNP";

    SqlParameter pNume = new SqlParameter();
    pNume.ParameterName = "@Nume";

    SqlParameter pPrenume = new SqlParameter();
    pPrenume.ParameterName = "@Prenume";

    SqlParameter pid_pers = new SqlParameter();
    pid_pers.ParameterName = "@id_pers";
//(5)    
        updateCmd.Parameters.Add(pNume);
        updateCmd.Parameters.Add(pPrenume);
        updateCmd.Parameters.Add(pTelefon);
        updateCmd.Parameters.Add(pCNP);
        updateCmd.Parameters.Add(pid_pers);
//(6)       
    for(int i=0;i<dsCitireXML.Tables["Table"].Rows.Count;i++)
    {
        pNume.Value = dsCitireXML.Tables["Table"].Rows[i]["Nume"];

        pPrenume.Value = dsCitireXML.Tables["Table"].Rows[i]["Prenume"];

        pCNP.Value = dsCitireXML.Tables["Table"].Rows[i]["CNP"];

        pTelefon.Value = dsCitireXML.Tables["Table"].Rows[i]["Telefon"];

        pid_pers.Value = dsCitireXML.Tables["Table"].Rows[i]["id_pers"];

        updateCmd.ExecuteNonQuery();

    }
    conexiune.Close();
}

Hope it helped you in your projects.

Have fun :)