/Ixsd2Db/SchemaGeneration/Create_Alter.cs

/***

 * Ixsd2db - Infopath XSD to DB table create tool

 * CODEPLEX

 * AUTHER: SHARON JOSEPH

 * LEAD: MAULIK SONI

 **/

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.IO;

using System.Data.SqlClient;

using System.Xml;

using System.Xml.Schema;



namespace WindowsFormsApplication1

{

    public partial class Create_Alter : Form

    {

        string connectionString = " ";

        SqlConnection sqlCon;

        DataTable dt = new DataTable();

        DataColumn col1, col2, col3;



        public Create_Alter()

        {

            InitializeComponent();



            txtpassword.PasswordChar = '*';

            dataGridView1.DataSource = dt;

            col1 = new DataColumn();

            col1.ColumnName = "Table Name";

            dt.Columns.Add(col1);

            col2 = new DataColumn();

            col2.ColumnName = "Operation";

            dt.Columns.Add(col2);

            col3 = new DataColumn();

            col3.ColumnName = "Field Names";

            dt.Columns.Add(col3);

            dataGridView1.Visible = false;



        }



        private void btnCreateAlter_Click(object sender, EventArgs e)

        {

            try

            {





                if (txtFormTableName.Text == "" || txtFilePath.Text == "" || connectionString.Trim() == "")

                {

                    string message = "Please enter required fields";

                    string caption = "Missing Values";

                    MessageBoxButtons buttons = MessageBoxButtons.OK;

                    DialogResult result;

                    result = MessageBox.Show(this, message, caption, buttons);

                }

                else

                {

                    dataGridView1.Visible = true;

                    string fpath1 = "";



                    fpath1 = txtFilePath.Text;

                    string[] elementname = new string[10000];//for storing column names

                    string[] elementnull = new string[10000];//for storing null or not null values 

                    string[] elementtype = new string[10000];//for storing data types



                    int i = 0;

                    int name = 0, type = 0, nul = 0;

                    try

                    {

                        XmlTextReader reader = new XmlTextReader(fpath1);



                        while (reader.Read())

                        {

                            switch (reader.NodeType)

                            {

                                case XmlNodeType.Element: // The node is an element.

                                    if (reader.Name == "xsd:element")

                                    {



                                        while (reader.MoveToNextAttribute()) // Read the attributes.

                                        {

                                            if (reader.AttributeCount > 1)//the very first xsd:element has the form name and no other attributes

                                            {

                                                if (reader.Name == "ref" || reader.Name == "minoccurs")

                                                {

                                                    name = 0;

                                                }

                                                if (reader.Name == "name")

                                                {

                                                    elementname[i] = reader.Value;

                                                    name = 1;

                                                }

                                                if (reader.Name == "type")

                                                {

                                                    string xsd = "xsd:"; //the type is always stored with a xsd: in the xsd files, so strip the xsd: before storing the type in the columntype array

                                                    int firstcharacter12 = reader.Value.IndexOf(xsd);

                                                    firstcharacter12 = firstcharacter12 + xsd.Length;

                                                    string fieldtype12 = reader.Value.Substring(firstcharacter12);

                                                    elementtype[i] = fieldtype12;

                                                    type = 1;

                                                }

                                                if (reader.Name == "nillable")

                                                {

                                                    elementnull[i] = reader.Value;

                                                    nul = 1;

                                                }

                                            }

                                            else

                                            {

                                                name = 0;

                                            }



                                        }



                                        if (name == 1 && type == 0)

                                        {

                                            elementtype[i] = "Varchar(500)";

                                        }



                                        if (name == 1 && nul == 0)

                                        {

                                            elementnull[i] = "false";

                                        }

                                        if (name != 0)

                                        {

                                            i++;

                                        }

                                        name = 0;

                                        nul = 0;

                                        type = 0;



                                    }

                                    break;

                            }

                        }



                    }

                    catch (Exception ex)

                    {

                        MessageBox.Show(ex.Message);

                    }



                    string table = "";

                    string commandString = "";

                    string[] tablename = txtFormTableName.Text.Split(' '); //get the form name from user to construct the table name and split them into individual words to remove spaces

                    if (tablename.Length > 1)

                    {

                        for (int a = 0; a < tablename.Length; a++)

                        {

                            for (int b = 0; b < tablename[a].Length; b++) //check each character in each word for symbols that are not accepted by SQL

                            {

                                string c = tablename[a];

                                if (c[b] != '-')

                                {

                                    table += c[b];



                                }

                            }



                            if (tablename.Length - 1 != a)

                            {

                                table += "_";

                            }



                        }

                    }

                    else

                    {

                        table = txtFormTableName.Text; //if the form name is already just a single word, use that as the table name

                    }

                    //once the xsd is read, check if the table needs to be created or altered

                    string[] tablenames = new string[10000];

                    int t = 0;

                    SqlDataReader myReader = null;

                    SqlCommand myCommand = new SqlCommand("Select TABLE_NAME from Information_Schema.Tables", sqlCon); //get all the table names from the information schema table

                    myReader = myCommand.ExecuteReader();



                    while (myReader.Read())

                    {

                        tablenames[t] = myReader["TABLE_NAME"].ToString();

                        t++;



                    }

                    myReader.Close();

                    int alter = 0;

                    for (int v = 0; v < t; v++)

                    {

                        if (tablenames[v].Trim() == table.Trim()) //if table already exists then alter the table

                        {

                            //create = 0;

                            alter = 1;

                            break;

                        }



                    }



                    if (alter == 1)

                    {

                        //code to alter the table

                        SqlDataReader myReader1 = null;

                        string myCom;

                        myCom = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME ='"; //get all the column names for the selected table

                        myCom += table;

                        myCom += "'";

                        SqlCommand myCommand1 = new SqlCommand(myCom, sqlCon);

                        myReader1 = myCommand1.ExecuteReader();

                        string[] colnames = new string[10000];

                        int t1 = 0;

                        while (myReader1.Read()) //reading already existing column names within the database

                        {

                            t1++;

                            colnames[t1] = myReader1["COLUMN_NAME"].ToString();

                        }

                        myReader1.Close();

                        int yes = 0;

                        int ge = 0;

                        int e1 = i;

                        //check against the column names read from the xsd file

                        if (t1 != e1) //if there is a change in the xsd file as compared to the database - to avoid errorneous submissions by the user

                        {



                            if (t1 < e1) //to add a field - if the elements in xsd file is more than the columns in the database

                            {

                                txtusername.Text = "";

                                for (int pe = 0; pe < e1; pe++)//for every column name read from the xsd file 

                                {

                                    for (ge = 1; ge <= t1; ge++) //for every column name fetched from the database

                                    {



                                        if (colnames[ge] == elementname[pe]) //if there is match set yes to 1

                                        {



                                            yes = 1;



                                            break;

                                        }





                                    }



                                    if (yes == 0) //if yes was never set to 1 for this particular element, then add the column

                                    {





                                        //found a new column name

                                        commandString = "Alter Table ";

                                        commandString += table;

                                        commandString += " ";

                                        commandString += "ADD";

                                        commandString += " ";

                                        commandString += elementname[pe];

                                        commandString += " ";

                                        //mapping data types from XSD file to suitable SQL data types

                                        if (elementtype[pe] == "integer")

                                        {

                                            commandString += "INT";





                                        }

                                        if (elementtype[pe] == "dateTime")

                                        {

                                            commandString += "date";





                                        }

                                        if (elementtype[pe] == "string" || elementtype[pe] == "requiredString")

                                        {

                                            commandString += "VARCHAR(MAX)";



                                        }

                                        if (elementtype[pe] == "boolean")

                                        {

                                            commandString += "BIT";



                                        }

                                        if (elementtype[pe] == "double")

                                        {

                                            commandString += "float";



                                        }

                                        if (elementtype[pe] == "time")

                                        {

                                            commandString += "nvarchar(50)";



                                        }





                                        if (elementtype[pe] != "boolean" && elementtype[pe] != "time" && elementtype[pe] != "string" && elementtype[pe] != "integer" && elementtype[pe] != "requiredString" && elementtype[pe] != "double" && elementtype[pe] != "dateTime")

                                        {

                                            commandString += elementtype[pe];



                                        }

                                        commandString += " ";



                                        if (elementnull[pe] == "true")

                                        {

                                            commandString += " ";

                                            commandString += "NOT NULL";

                                        }

                                        else

                                        {

                                            commandString += "NULL";

                                        }



                                        DataRow myNewRow;

                                        myNewRow = dt.NewRow();

                                        myNewRow[col1] = table;

                                        myNewRow[col2] = "Column Addition";

                                        myNewRow[col3] = elementname[pe];

                                        dt.Rows.Add(myNewRow);



                                        SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

                                        sqlCmd = new SqlCommand(commandString, sqlCon);

                                        sqlCmd.ExecuteNonQuery();



                                    }

                                    else

                                    {

                                        yes = 0;

                                    }









                                }

                                string message = "Table Altered successfully - Columns were added";

                                string caption = "Alter Success";

                                MessageBoxButtons buttons = MessageBoxButtons.OK;

                                DialogResult result;

                                result = MessageBox.Show(this, message, caption, buttons);

                            }

                            if (e1 < t1)//to delete a field - if the number of columns in database is more than the number of elements in the xsd file

                            {



                                int pe;

                                for (pe = 1; pe <= t1; pe++) //for every column name read from the database

                                {

                                    for (ge = 0; ge < e1; ge++) //for every element name fetched from the xsd file

                                    {





                                        if (colnames[pe] == elementname[ge]) //check if the column name exists in the xsd file

                                        {



                                            yes = 1;

                                            //  break;

                                        }





                                    }

                                    if (yes == 0) //if column name does not exist in the xsd file, delete the column

                                    {

                                        commandString = "Alter Table ";

                                        commandString += table;

                                        commandString += " ";

                                        commandString += "DROP Column";

                                        commandString += " ";

                                        commandString += colnames[pe];



                                        try

                                        {

                                            DataRow myNewRow;

                                            myNewRow = dt.NewRow();

                                            myNewRow[col1] = table;

                                            myNewRow[col2] = "Column Deletion";

                                            myNewRow[col3] = colnames[pe];

                                            dt.Rows.Add(myNewRow);

                                            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

                                            sqlCmd = new SqlCommand(commandString, sqlCon);

                                            sqlCmd.ExecuteNonQuery();

                                        }

                                        catch (Exception ex)

                                        {

                                            MessageBox.Show(ex.Message);

                                        }





                                    }

                                    else

                                    {

                                        yes = 0;

                                    }



                                }



                                string message = "Table Altered successfully - Columns were deleted";

                                string caption = "Alter Success";

                                MessageBoxButtons buttons = MessageBoxButtons.OK;

                                DialogResult result;

                                result = MessageBox.Show(this, message, caption, buttons);

                            }

                        }

                        else

                        {

                            string message = "There were no changes detected in the xsd file - Did you forget generating a new XSD source file after making changes to the Infopath form?";

                            string caption = "Erroneous Submission";

                            MessageBoxButtons buttons = MessageBoxButtons.YesNo;

                            DialogResult result;

                            result = MessageBox.Show(this, message, caption, buttons);

                            if (result == DialogResult.Yes)

                            {

                                //this.Close();

                                string message1 = "Please update the XSD file and try again.";

                                string caption1 = "Erroneous Submission";

                                MessageBoxButtons buttons1 = MessageBoxButtons.OK;

                                DialogResult result1;

                                result1 = MessageBox.Show(this, message1, caption1, buttons1);



                            }

                            if (result == DialogResult.No)

                            {

                                string message1 = "There were still no changes detected in the xsd file. Please check the path where the XSD file resides and try again.";

                                string caption1 = "Erroneous Submission";

                                MessageBoxButtons buttons1 = MessageBoxButtons.OK;

                                DialogResult result1;

                                result1 = MessageBox.Show(this, message1, caption1, buttons1);

                            }



                        }



                    }



                    else//if table does not exist, create the  table

                    {



                        commandString += "CREATE TABLE ";

                        commandString += table;

                        commandString += "(";

                        //type conversions

                        int e1 = i;

                        e1--;

                        for (int g = 0; g <= e1; g++)

                        {



                            commandString += elementname[g];

                            commandString += " ";

                            if (elementtype[g] == "integer")

                            {

                                commandString += "INT";

                            }

                            if (elementtype[g] == "dateTime")

                            {

                                commandString += "date";





                            }

                            if (elementtype[g] == "string" || elementtype[g] == "requiredString")

                            {

                                commandString += "VARCHAR(500)";



                            }

                            if (elementtype[g] == "boolean")

                            {

                                commandString += "BIT";



                            }

                            if (elementtype[g] == "double")

                            {

                                commandString += "float";



                            }

                            if (elementtype[g] == "time")

                            {

                                commandString += "nvarchar(50)";



                            }





                            if (elementtype[g] != "boolean" && elementtype[g] != "time" && elementtype[g] != "string" && elementtype[g] != "integer" && elementtype[g] != "requiredString" && elementtype[g] != "double" && elementtype[g] != "dateTime")

                            {

                                commandString += elementtype[g];



                            }



                            if (elementnull[g] == "false")

                            {

                                commandString += " ";

                                commandString += "NOT NULL";

                            }





                            if (e1 != g)

                            {



                                commandString += ",";

                            }

                        }

                        commandString += ")";



                        //execute the query

                        DataRow myNewRow;

                        myNewRow = dt.NewRow();

                        myNewRow[col1] = table;

                        myNewRow[col2] = "Table Creation";

                        myNewRow[col3] = " ";

                        dt.Rows.Add(myNewRow);

                        SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

                        sqlCmd = new SqlCommand(commandString, sqlCon);



                        sqlCmd.ExecuteNonQuery();

                        //sqlCon.Close();

                        string message = "Table created successfully";

                        string caption = "Success";

                        MessageBoxButtons buttons = MessageBoxButtons.OK;

                        DialogResult result;

                        result = MessageBox.Show(this, message, caption, buttons);

                        // this.Close();





                    }

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }



        private void btnBrowse_Click(object sender, EventArgs e)

        {

            try

            {

                openFileDialog1.InitialDirectory = @"C:\";

                openFileDialog1.Title = "Select a File";

                openFileDialog1.Filter = "XSD files (*.xsd)|*.xsd";

                openFileDialog1.FileName = "";

                openFileDialog1.ShowDialog();

                if (openFileDialog1.FileName != "")

                {

                    txtFilePath.Text = openFileDialog1.FileName;

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }



        private void btnTestConnection_Click(object sender, EventArgs e)

        {

            try

            {

                connectionString = txtconnectionString.Text;

                if (connectionString.Trim() == "")

                {

                    connectionString = "Data Source=" + txtdatasource.Text.Trim() + ";Initial Catalog=" + txtdbname.Text.Trim() + ";User ID=" + txtusername.Text.Trim() + ";Password=" + txtpassword.Text.Trim();

                    if (txtdatasource.Text.Trim() == "" && txtusername.Text.Trim() == "" && txtpassword.Text.Trim() == "" && txtdbname.Text.Trim() == "")

                    {



                        string message = "You did not enter a Database Connection String.";

                        string caption = "No Connection string specified";

                        MessageBoxButtons buttons = MessageBoxButtons.OK;

                        DialogResult result;

                        result = MessageBox.Show(this, message, caption, buttons);

                        connectionString = "";

                    }

                    else

                    {

                        try

                        {

                            sqlCon = new SqlConnection(connectionString);

                            sqlCon.Open();

                            string message = "Connection Success";

                            string caption = "Successful Connection";

                            MessageBoxButtons buttons = MessageBoxButtons.OK;

                            DialogResult result;

                            result = MessageBox.Show(this, message, caption, buttons);



                        }

                        catch (Exception e2)

                        {

                            string message = e2.Message;

                            string caption = "Connection Failed";

                            MessageBoxButtons buttons = MessageBoxButtons.OK;

                            DialogResult result;

                            result = MessageBox.Show(this, message, caption, buttons);

                        }

                    }

                }

                else

                {

                    try

                    {

                        sqlCon = new SqlConnection(connectionString);

                        sqlCon.Open();

                        string message = "Connection Success";

                        string caption = "Successful Connection";

                        MessageBoxButtons buttons = MessageBoxButtons.OK;

                        DialogResult result;

                        result = MessageBox.Show(this, message, caption, buttons);



                    }

                    catch (Exception e2)

                    {

                        string message = e2.Message;

                        string caption = "Connection Failed";

                        MessageBoxButtons buttons = MessageBoxButtons.OK;

                        DialogResult result;

                        result = MessageBox.Show(this, message, caption, buttons);

                    }

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }



    }



}