MES_Client/TGMES/Core.StlMes.Client.Judge/Commons/DataTableCreatorDesigner.cs

using Core.StlMes.Client.Judge.Forms;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Windows.Forms;

namespace Core.StlMes.Client.Judge.Commons
{
    public class DataTableCreatorDesigner : ComponentDesigner
    {

        public DataTableCreatorDesigner()
        {
            // 添加菜单到右键菜单和智能标记中。 
            DesignerVerb V2 = new DesignerVerb("生成DataTable结构", new EventHandler(OnGenerateButtonCode));
            this.Verbs.Add(V2);
        }

        public override void Initialize(IComponent component)
        {
            base.Initialize(component);
        }

        private void OnGenerateButtonCode(object sender, EventArgs e)
        {
            // 
            //在窗体上加入一个Button组件,host.RootComponent=Form 
            // 
            //取出窗体设计器 
            IDesignerHost host = (IDesignerHost)this.GetService(typeof(IDesignerHost));
            IComponentChangeService c = (IComponentChangeService)this.GetService(typeof(IComponentChangeService));

            Control control = (Control)host.RootComponent;//RootComponent: 根组件，指Form 
            DesignerTransaction tran = host.CreateTransaction();//创建事务 

            c.OnComponentChanging(control, null);//通知窗体正在新增控件服务  

            //创建table

            DataSet ds = (DataSet)host.CreateComponent(typeof(DataSet));
            DataTable dt = (DataTable)host.CreateComponent(typeof(DataTable));
            PopupTextBox popup = new PopupTextBox("", 99999)
            {
                Text = "请输入查询SQL"
            };
            if (popup.ShowDialog() == DialogResult.OK)
            {
                CreateDataTable(dt, host, popup.TextInfo);
                ds.Tables.Add(dt);
            }

            c.OnComponentChanged(control, null, null, null);//通知窗体修改服务已完成 
            if (dt.Columns.Count > 0)
            {
                tran.Commit();//提交事务 
            }
            else
            {
                tran.Cancel();
            }
        }

        private void CreateDataTable(DataTable dt, IDesignerHost host, string sql)
        {
            OracleDriver driver = new OracleDriver();
            try
            {
                driver.Connect("TGCXDB", "cxuser", "tgcxuser_2015");
                driver.Open();
                string sqlColumnInfo = @"SELECT T.COLUMN_NAME,
       T.DATA_TYPE,
       TO_CHAR(DECODE(T.DATA_TYPE,
                      'NUMBER',
                      NVL2(T.DATA_PRECISION, T.DATA_PRECISION, T.DATA_LENGTH),
                      T.DATA_LENGTH)) DATA_LENGTH,
       T.NULLABLE,
       NVL(T2.COMMENTS, T.COLUMN_NAME) COMMENTS,
       CASE (SELECT COUNT(1)
           FROM USER_CONSTRAINTS T3, USER_CONS_COLUMNS T4
          WHERE T3.TABLE_NAME = T.TABLE_NAME
            AND T3.CONSTRAINT_TYPE = 'P'
            AND T4.CONSTRAINT_NAME = T3.CONSTRAINT_NAME
            AND T4.COLUMN_NAME = T.COLUMN_NAME)
         WHEN 0 THEN
          '0'
         ELSE
          '1'
       END IS_PK
  FROM USER_TAB_COLS T, USER_COL_COMMENTS T2
 WHERE T.COLUMN_NAME = T2.COLUMN_NAME
   AND T.TABLE_NAME = T2.TABLE_NAME
   AND T.TABLE_NAME IN ('1'{0})
 ORDER BY T.SEGMENT_COLUMN_ID";

                sql = "SELECT * FROM (" + sql + ") WHERE ROWNUM <= 1";
                DataTable disDt = driver.ExecuteQuery(sql.ToUpper());
                for (int i = 0; i < disDt.Columns.Count; i++)
                {
                    DataColumn dc = (DataColumn)host.CreateComponent(typeof(DataColumn));
                    dc.ColumnName = GetLanguageFormat(disDt.Columns[i].ColumnName, true);
                    dt.Columns.Add(dc);
                }
                //TSqlEngine eg = new TSqlEngine();
                //List<TSqlEngine.AnalyzeResultSqlStruct> retSql = eg.AnalyzeTSql(sql);

                //List<DataTable> tables = new List<DataTable>();
                //MessageBox.Show(retSql.Count.ToString());
                //        Regex rg = new Regex("(?i)(?<=(?:from|into|update|join)\\s{1,1000}"  
                //+ "(?:\\w{1,1000}(?:\\s{0,1000},\\s{0,1000})?)?" // 重复这里, 可以多个from后面的表  
                //+ "(?:\\w{1,1000}(?:\\s{0,1000},\\s{0,1000})?)?"  
                //+ "(?:\\w{1,1000}(?:\\s{0,1000},\\s{0,1000})?)?"  
                //+ "(?:\\w{1,1000}(?:\\s{0,1000},\\s{0,1000})?)?"  
                //+ "(?:\\w{1,1000}(?:\\s{0,1000},\\s{0,1000})?)?"  
                //+ "(?:\\w{1,1000}(?:\\s{0,1000},\\s{0,1000})?)?"  
                //+ "(?:\\w{1,1000}(?:\\s{0,1000},\\s{0,1000})?)?"  
                //+ "(?:\\w{1,1000}(?:\\s{0,1000},\\s{0,1000})?)?"  
                //+ "(?:\\w{1,1000}(?:\\s{0,1000},\\s{0,1000})?)?"  
                //+ "(?:\\w{1,1000}(?:\\s{0,1000},\\s{0,1000})?)?"  
                //+ ")(\\w+)");
                //List<string> tableNames = new List<string>();
                //foreach (Match mt in rg.Matches(sql))
                //{
                //    tableNames.Add(mt.Value);
                //    MessageBox.Show(mt.Value);
                //}

                List<string> tableNames = new List<string>();
                List<string> sqls = sql.Split(new string[] { " ", "\r\n", "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
                string sqlTables = "";
                for (int i = sqls.Count - 1; i >= 0; i--)
                {
                    foreach (DataColumn col in disDt.Columns)
                    {
                        if (col.ColumnName == sqls[i])
                        {
                            sqls.RemoveAt(i);
                            break;
                        }
                    }
                    if (sqls[i].Contains("'")) continue;
                    sqlTables += ", '" + sqls[i] + "'";
                }
                //int interval = 0;
                //for (int i = 0; i < sqls.Length; i++)
                //{
                //    string str = sqls[i];
                //    if (str.StartsWith("(")) continue;
                //    if (i != 0)
                //    {
                //        if (sqls[i - 1] == "FROM")
                //        {
                //            tableNames.Add(str);
                //        }
                //        else if (sqls[i - 1] == "JOIN")
                //        {
                //            tableNames.Add(str);
                //        }

                //        bool isTable = false;
                //        for (int j = i; j >= 0; j--)
                //        {
                //            if (sqls[j] == "FROM" || sqls[j] == "JOIN") 
                //            {
                //                isTable = true;
                //            }
                //            else if (sqls[j].Contains("(")
                //                || sqls[j] == "SELECT")
                //            {
                //                isTable = false;
                //            }
                //        }
                //        if (isTable)
                //        {
                //            tableNames.Add(str);
                //        }

                //        //else if (i >= 2 && (sqls[i - 2] == "FROM" || sqls[i - 2] == "JOIN"))
                //        //{
                //        //     tableNames.Add(str);
                //        //}
                //        //else if (i >= 3 && (sqls[i - 3] == "FROM" || sqls[i - 3] == "JOIN"))
                //        //{
                //        //    tableNames.Add(str);
                //        //}

                //        //处理连接
                //        if (str == "ON")
                //        {
                //            i += 2;
                //            interval = 4;
                //        }
                //        else
                //        {
                //            interval = 0;
                //        }
                //    }
                //}
                //string[] tableNames = new string[] { "STL_INCISION", "QCM_LG_CHEMJUDGE" };


                string sss = string.Format(sqlColumnInfo, sqlTables);
                //MessageUtil.ShowTips(sss);
                DataTable table = driver.ExecuteQuery(sss);


                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    for (int k = 0; k < table.Rows.Count; k++)
                    {
                        string columnName = GetLanguageFormat(table.Rows[k]["COLUMN_NAME"].ToString(), true);
                        string columnDesc = table.Rows[k]["COMMENTS"].ToString();
                        if (columnName == dt.Columns[i].ColumnName)
                        {
                            dt.Columns[i].Caption = columnDesc;
                            break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                driver.Close();
            }
        }

        private string GetLanguageFormat(string strName, bool isLowerBegin)
        {
            string[] strs = strName.Split('_');
            strName = "";
            for (int i = 0; i < strs.Length; i++)
            {
                if (i == 0 && isLowerBegin)
                {
                    strName += strs[i].ToLower();
                }
                else
                {
                    strName += strs[i].Substring(0, 1).ToUpper() + strs[i].Substring(1).ToLower();
                }
            }
            return strName;
        }

        public class OracleDriver
        {
            //(ADDRESS_LIST =
            //					  (ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = 1521))
            //					)
            public OracleConnection _connect;
            public void Connect(string severName, string userName, string password)
            {
                string connectString = "";
                connectString += string.Format("Data Source={0};User Id={1};Password={2};", severName, userName, password);

                _connect = new OracleConnection(connectString);
            }

            public bool Open()
            {
                try
                {
                    _connect.Open();
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
                return true;
            }

            public void Close()
            {
                _connect.Close();
            }

            public DataTable ExecuteQuery(string sql)
            {
                OracleDataAdapter dtAdapter = new OracleDataAdapter(sql, _connect);
                DataTable dt = new DataTable();
                try
                {
                    dtAdapter.Fill(dt);
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
                return dt;
            }
        }
    }
}