DTS

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using Topdeep.DataImportExport.Config;
using System.Data.SqlClient;

namespace DbSchemaBuilder
{
    public partial class MainForm : Form
    {
        private DataBaseSchemaConfigSection schemaConfig = null;

        private DataBaseSchemaSection currentSchemaSection = null;

        private Configuration config;

        private string currentConfigFile = string.Empty;

        private DataBaseTableSection currentTableSection = null;

        public MainForm()
        {
            InitializeComponent();
        }

        private void toolStripMenuItemExit_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void toolStripMenuItemOpen_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                currentConfigFile = openFileDialog1.FileName;
                OpenConfigFile();
            }
        }

        private void OpenConfigFile()
        {
            ExeConfigurationFileMap filemap = new ExeConfigurationFileMap();
            filemap.ExeConfigFilename = this.currentConfigFile;
            config = ConfigurationManager.OpenMappedExeConfiguration(filemap, ConfigurationUserLevel.None);
            if (config != null)
            {
                schemaConfig = (DataBaseSchemaConfigSection)config.Sections["DataBaseSchemaConfig"];
                if (schemaConfig == null)
                {
                    schemaConfig = new DataBaseSchemaConfigSection();
                    config.Sections.Add("DataBaseSchemaConfig", schemaConfig);
                }
            }
            else
            {
                schemaConfig = null;
            }
            LoadSchemaConfig();
        }

        private void LoadSchemaConfig()
        {
            listBoxSchema.Items.Clear();
            if (schemaConfig == null) return;
            foreach (DataBaseSchemaSection schemaSection in schemaConfig.SchemaCollection)
            {
                listBoxSchema.Items.Add(schemaSection);
            }
        }

        private void listBoxSchema_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBoxSchema.SelectedIndex < 0) return;
            DataBaseSchemaSection schemaSection = listBoxSchema.SelectedItem as DataBaseSchemaSection;
            currentSchemaSection = schemaSection;
            LoadSchemaSection(schemaSection);
        }

        private void LoadSchemaSection(DataBaseSchemaSection schemaSection)
        {
            ShowSchemaInfo(schemaSection);
            treeViewSchema.ExpandAll();
        }

        private void ShowSchemaInfo(DataBaseSchemaSection schemaSection)
        {
            treeViewSchema.Nodes.Clear();
            if (schemaSection == null) return;
            TreeNode tablesRoot = treeViewSchema.Nodes.Add("表");
            for (int i = 0; i < schemaSection.TableCollection.Count; i++)
            {
                DataBaseTableSection tableSection = schemaSection.TableCollection[i];
                ShowTableInfo(tablesRoot, tableSection);
            }
        }

        private void ShowTableInfo(TreeNode tablesRoot, DataBaseTableSection tableSection)
        {
            TreeNode tableNode = tablesRoot.Nodes.Add(tableSection.TableDesc + " [" + tableSection.TableName + "]");
            tableNode.Tag = tableSection;
            for (int i = 0; i < tableSection.ColumnCollection.Count; i++)
            {
                DataBaseColumnSection columnSection = tableSection.ColumnCollection[i];
                ShowColumnInfo(tableNode, columnSection);
            }
        }

        private void ShowColumnInfo(TreeNode tableNode, DataBaseColumnSection columnSection)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(columnSection.ColumnDesc);
            sb.Append(" [");
            sb.Append(columnSection.ColumnName);
            sb.Append("] ");
            if (columnSection.IsIdentity)
            {
                sb.Append(" (Identity) ");
            }
            sb.Append(columnSection.ColumnType.ToString());
            tableNode.Nodes.Add(sb.ToString()).Tag = columnSection;
        }

        private void toolStripMenuItemAddTable_Click(object sender, EventArgs e)
        {
            if (currentSchemaSection == null) return;
            SchemaTableEditForm dlg = new SchemaTableEditForm();
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                DataBaseTableSection tableSection = new DataBaseTableSection();
                tableSection.TableName = dlg.TableName;
                tableSection.TableDesc = dlg.TableDesc;
                try
                {
                    currentSchemaSection.TableCollection.Add(tableSection);
                    LoadSchemaSection(currentSchemaSection);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

        }

        private void treeViewSchema_MouseClick(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                if (treeViewSchema.SelectedNode == null) return;
                //if (treeViewSchema.SelectedNode.Level != 0) return;
                if (treeViewSchema.SelectedNode.Level == 0)
                {
                    contextMenuStrip1.Show(treeViewSchema, e.X, e.Y);
                }

                if (treeViewSchema.SelectedNode.Level == 1)
                {
                    contextMenuStrip2.Show(treeViewSchema, e.X, e.Y);
                }
                if (treeViewSchema.SelectedNode.Level == 2)
                {
                    contextMenuStrip3.Show(treeViewSchema, e.X, e.Y);
                }
                else
                {
                    return;
                }

            }

        }

        private void ToolStripMenuItemAlterTable_Click(object sender, EventArgs e)
        {
            if (currentSchemaSection == null) return;
            SchemaTableEditForm dlg = new SchemaTableEditForm();
            DataBaseTableSection tableSection = treeViewSchema.SelectedNode.Tag as DataBaseTableSection;
            dlg.TableName = tableSection.TableName;
            dlg.TableDesc = tableSection.TableDesc;

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    tableSection.TableName = dlg.TableName;
                    tableSection.TableDesc = dlg.TableDesc;
                    LoadSchemaSection(currentSchemaSection);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

        }

        private void ToolStripMenuItemDeleteTable_Click(object sender, EventArgs e)
        {

            if (currentSchemaSection == null) return;
            DataBaseTableSection tableSection = treeViewSchema.SelectedNode.Tag as DataBaseTableSection;
            DialogResult result;
            result = MessageBox.Show("确定要删除表吗？", "提示信息", MessageBoxButtons.YesNo);
            if (result == DialogResult.Yes)
            {
                //currentSchemaSection.TableCollection
                //treeViewSchema.SelectedNode.Remove();
                currentSchemaSection.TableCollection.Remove(tableSection);
                LoadSchemaSection(currentSchemaSection);
            }
            else
            {
                return;
            }
        }

       

        private void toolStripMenuItemAddColumn_Click(object sender, EventArgs e)
        {
            SchemaColumnEditForm dlg = new SchemaColumnEditForm();
            currentTableSection = treeViewSchema.SelectedNode.Tag as DataBaseTableSection;
            DataBaseColumnSection columnSection = new DataBaseColumnSection();
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                columnSection.ColumnName = dlg.ColumnName;
                columnSection.ColumnDesc = dlg.ColumnDesc;
                columnSection.ColumnType = dlg.ColumnType;
                columnSection.IsIdentity = dlg.ColumnIdentify;

            }
            try
            {
                currentTableSection.ColumnCollection.Add(columnSection);
                LoadSchemaSection(currentSchemaSection);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void toolStripMenuItemAlterColumn_Click(object sender, EventArgs e)
        {
            if (currentSchemaSection == null) return;
            SchemaColumnEditForm dlg = new SchemaColumnEditForm();
            DataBaseColumnSection columnSection = treeViewSchema.SelectedNode.Tag as DataBaseColumnSection;
            dlg.ColumnName = columnSection.ColumnName;
            dlg.ColumnDesc = columnSection.ColumnDesc;
            dlg.ColumnType = columnSection.ColumnType;
            dlg.ColumnIdentify = columnSection.IsIdentity;

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    columnSection.ColumnName = dlg.ColumnName;
                    columnSection.ColumnDesc = dlg.ColumnDesc;
                    columnSection.ColumnType = dlg.ColumnType;
                    columnSection.IsIdentity = dlg.ColumnIdentify;
                    LoadSchemaSection(currentSchemaSection);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

        }

        private void toolStripMenuItemDeleteColumn_Click(object sender, EventArgs e)
        {
            if (currentSchemaSection == null) return;
            DataBaseColumnSection columnSection = treeViewSchema.SelectedNode.Tag as DataBaseColumnSection;
            currentTableSection = treeViewSchema.SelectedNode.Parent.Tag as DataBaseTableSection;
            DialogResult result;
            result = MessageBox.Show("确定要删除字段吗？", "提示信息", MessageBoxButtons.YesNo);
            if (result == DialogResult.Yes)
            {

                currentTableSection.ColumnCollection.Remove(columnSection);
                LoadSchemaSection(currentSchemaSection);
            }
            else
            {
                return;
            }

        }

        private void toolStripMenuItemAddSchema_Click(object sender, EventArgs e)
        {

            SchemaEditForm dlg = new SchemaEditForm();
            // schemaConfig = listBoxSchema .SelectedValue  as DataBaseSchemaConfigSection  ;
            DataBaseSchemaSection schemaSection = new DataBaseSchemaSection();
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                schemaSection.SchemaName = dlg.SchemaName;
                schemaSection.SchemaVersion = dlg.SchemaVersion;
                schemaSection.SchemaFlag = dlg.SchemaFlag;
            }
            try
            {
                schemaConfig.SchemaCollection.Add(schemaSection);

                LoadSchemaConfig();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void toolStripMenuItemAlterSchema_Click(object sender, EventArgs e)
        {
            SchemaEditForm dlg = new SchemaEditForm();
            DataBaseSchemaSection schemaSection;
            schemaSection = listBoxSchema.SelectedItem as DataBaseSchemaSection;
            dlg.SchemaName = schemaSection.SchemaName;
            dlg.SchemaVersion = schemaSection.SchemaVersion;
            dlg.SchemaFlag = schemaSection.SchemaFlag;

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    schemaSection.SchemaName = dlg.SchemaName;
                    schemaSection.SchemaVersion = dlg.SchemaVersion;
                    schemaSection.SchemaFlag = dlg.SchemaFlag;
                    LoadSchemaConfig();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void toolStripMenuItemDeleteSchema_Click(object sender, EventArgs e)
        {
            if (currentSchemaSection == null) return;
            DataBaseSchemaSection schemaSection;
            schemaSection = listBoxSchema.SelectedItem as DataBaseSchemaSection;
            DialogResult result;
            result = MessageBox.Show("确定要删除架构吗？", "提示信息", MessageBoxButtons.YesNo);
            if (result == DialogResult.Yes)
            {
                schemaConfig.SchemaCollection.Remove(schemaSection);
                LoadSchemaConfig();
                LoadSchemaSection(null);
            }
            else
            {
                return;
            }
        }

        private void toolStripMenuItemSave_Click(object sender, EventArgs e)
        {
            if (config == null) return;
            config.Save();
        }

       

        private void toolStripMenuItemNew_Click(object sender, EventArgs e)
        {
            if (config != null)
            {
                //if 配置文件修改过了，提示保存
                DialogResult ret = MessageBox.Show("是否保存当前的修改", "警告信息", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning);
                if (ret == DialogResult.Cancel) return;
                if (ret == DialogResult.Yes)
                {
                    config.Save();
                }
            }

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                currentConfigFile = saveFileDialog1.FileName;
                OpenConfigFile();
            }
        }

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

        private void toolStripMenuItemImportDbSchema_Click(object sender, EventArgs e)
        {
            if (currentSchemaSection == null) return;
            DataBaseSchemaSection schemaSection = listBoxSchema.SelectedItem as DataBaseSchemaSection;
            if (schemaSection == null) return;
            #region 获取数据库连接
            string connectionString;
            DbInfoForm dlg = new DbInfoForm();
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                connectionString = dlg.ConnectionString;
            }
            else
            {
                return;
            }
            #endregion
            #region 连接数据库并获取数据架构填充或修改当前的数据架构
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                #region 获得所有的表
                string[] restrictions = new string[4];
                restrictions[3] = "BASE TABLE";
                DataTable table = connection.GetSchema("Tables", restrictions);
                // Display the contents of the table.
                //DisplayData(table,treeViewSchema  );
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow dr = table.Rows[i];
                    string tblName = dr["table_name"].ToString().Trim().ToUpper();
                    DataBaseTableSection tblSection = null;
                    bool find = false;
                    foreach (DataBaseTableSection tableSection in schemaSection.TableCollection)
                    {
                        if (tableSection.TableName.Equals(tblName, StringComparison.CurrentCultureIgnoreCase))
                        {
                            find = true;
                            tableSection.TableName = tblName;
                            tblSection = tableSection;
                            break;
                        }
                    }
                    if (!find)
                    {
                        tblSection = new DataBaseTableSection();
                        tblSection.TableName = tblName;
                        tblSection.TableDesc = "";
                        schemaSection.TableCollection.Add(tblSection);
                    }
                    #region 获取字段信息
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "select * from " + tblSection.TableName + " where 0 = 1";
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataTable tblTable = new DataTable();
                    da.FillSchema(tblTable, SchemaType.Source);
                    foreach (DataColumn column in tblTable.Columns)
                    {
                        bool findColumn = false;
                        string columnName = column.ColumnName.Trim().ToUpper();
                        DataBaseColumnSection colSection = null;
                        foreach (DataBaseColumnSection columnSection in tblSection.ColumnCollection)
                        {
                            if (columnSection.ColumnName.Equals(columnName, StringComparison.CurrentCultureIgnoreCase))
                            {
                                find = true;
                                colSection = columnSection;
                                colSection.ColumnName = columnName;
                                break;
                            }
                        }
                        if (!findColumn)
                        {
                            colSection = new DataBaseColumnSection();
                            colSection.ColumnName = columnName;
                            colSection.ColumnDesc = "";
                            tblSection.ColumnCollection.Add(colSection);
                        }
                        colSection.ColumnType = column.DataType;
                        colSection.IsIdentity = column.AutoIncrement;
                    }
                    #endregion
                }
                #endregion
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                connection.Close();
                LoadSchemaSection(schemaSection);
            }
            #endregion
        }

    }
}