Пример #1
0
        private void RetrieveData()
        {
            bool lb_result = false;
            //写dgv_columns
            string ls_sql = "SELECT Table_Name, Field_Name,Field_Desc, Field_Type, IsPK,CanNull,ShowIndex,IsIdentity " +
                            " FROM IF_field_infor Where Table_Name ='" + _TableName + "'  order by ShowIndex";
            DataTable ldt = new DataTable();

            if (AccessDBop.SQLSelect(ls_sql, ref ldt))
            {
                dgv_columns.DataSource = ldt;
            }
            //写cb_CompareTable 下拉
            if (_TableType != "Z")
            {
                ls_sql = "select Table_Name,Table_desc From IF_Table_infor" +
                         " Where Table_Type = 'Z'";
                ldt = new DataTable();
                if (AccessDBop.SQLSelect(ls_sql, ref ldt))
                {
                    if (ldt.Rows.Count > 0)
                    {
                        cb_CompareTable.DisplayMember = "Table_desc";
                        cb_CompareTable.ValueMember   = "Table_Name";
                        cb_CompareTable.DataSource    = ldt;
                        cb_CompareTable.SelectedIndex = -1;
                        cb_CompareTable.SelectedIndex = 0;
                    }
                }
            }
            if (ucbTablePosition.Text == Project.DB_Alias)
            {
                ls_sql = "select Db_type,Db_name,Db_server,Db_User,Db_Password From IF_DBConnectionInfor" +
                         " Where bz = 0";
            }
            else if (ucbTablePosition.Text == Project.theirDB_Alias)
            {
                ls_sql = "select Db_type,Db_name,Db_server,Db_User,Db_Password From IF_DBConnectionInfor" +
                         " Where bz = 1";
            }
            else
            {
                ls_sql = "";
            }
            ldt = new DataTable();
            if (AccessDBop.SQLSelect(ls_sql, ref ldt))
            {
                if (ldt.Rows.Count > 0)
                {
                    //constr = "";
                    ls_type = ldt.Rows[0]["Db_type"].ToString();
                    if (ls_type == "sql")
                    {
                        //constr = "server = " + ldt.Rows[0]["Db_server"]
                        //        + ";database = " + ldt.Rows[0]["Db_name"]
                        //+ ";Uid = " + ldt.Rows[0]["Db_User"]
                        //+ ";pwd = " + ldt.Rows[0]["Db_Password"];
                        ls_sql = "SELECT Name FROM SysObjects Where XType in('U','V') ORDER BY Name";
                        ldt    = new DataTable();
                        if (ucbTablePosition.Text == Project.DB_Alias)
                        {
                            lb_result = SQLServerDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt, "");
                        }
                        else
                        {
                            lb_result = SQLServerDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt, "");
                        }
                        if (lb_result)
                        {
                            cb_DatabaseTable.DisplayMember = "Name";
                            cb_DatabaseTable.ValueMember   = "Name";
                            cb_DatabaseTable.DataSource    = ldt;
                            cb_DatabaseTable.SelectedIndex = -1;
                            cb_DatabaseTable.SelectedIndex = 0;
                        }
                        dgv_columns.Columns["ISIDENTITY"].Visible = true;
                    }
                    else if (ls_type == "ora")
                    {
                        //constr = "server = " + ldt.Rows[0]["Db_server"]
                        //        + ";Uid = " + ldt.Rows[0]["Db_User"]
                        //        + ";pwd = " + ldt.Rows[0]["Db_Password"];
                        ls_sql = ("SELECT table_name as Name from user_tables where tablespace_name in"
                                  + " ("
                                  + " select default_tablespace from dba_users"
                                  + " where username = '******'"
                                  + " ) UNION SELECT VIEW_NAME AS NAME FROM User_Views").ToUpper();
                        ldt = new DataTable();
                        if (ucbTablePosition.Text == Project.DB_Alias)
                        {
                            lb_result = OracleDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt);
                        }
                        else
                        {
                            lb_result = OracleDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt);
                        }
                        if (lb_result)
                        {
                            cb_DatabaseTable.DisplayMember = "Name";
                            cb_DatabaseTable.ValueMember   = "Name";
                            cb_DatabaseTable.DataSource    = ldt;
                            cb_DatabaseTable.SelectedIndex = -1;
                            cb_DatabaseTable.SelectedIndex = 0;
                        }
                        dgv_columns.Columns["ISIDENTITY"].Visible = false;
                    }
                    else if (ls_type == "mysql")
                    {
                        string db_name = ucbTablePosition.Text == Project.DB_Alias ? Project.DbName : Project.theirDbName;
                        ls_sql = "select table_name as name from INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE' and table_schema = '" + db_name + "'";
                        ldt    = new DataTable();
                        if (ucbTablePosition.Text == Project.DB_Alias)
                        {
                            lb_result = MySqlDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt);
                        }
                        else
                        {
                            lb_result = MySqlDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt);
                        }
                        if (lb_result)
                        {
                            cb_DatabaseTable.DisplayMember = "Name";
                            cb_DatabaseTable.ValueMember   = "Name";
                            cb_DatabaseTable.DataSource    = ldt;
                            cb_DatabaseTable.SelectedIndex = -1;
                            cb_DatabaseTable.SelectedIndex = 0;
                        }
                        dgv_columns.Columns["ISIDENTITY"].Visible = true;
                    }
                    else if (ls_type == "access")
                    {
                        //string db_name = ucbTablePosition.Text == Project.DB_Alias ? Project.DbName : Project.theirDbName;
                        //ls_sql = "SELECT name FROM MSYSOBJECTS WHERE TYPE=1 AND NAME NOT LIKE 'Msys*'";
                        //ldt = new DataTable();
                        //if (ucbTablePosition.Text == Project.DB_Alias)
                        //    lb_result = AccessDBop.SQLSelect(Project, ls_sql, ref ldt);
                        //else
                        //    lb_result = AccessDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt);
                        //if (lb_result)
                        //{
                        //    cb_DatabaseTable.DisplayMember = "Name";
                        //    cb_DatabaseTable.ValueMember = "Name";
                        //    cb_DatabaseTable.DataSource = ldt;
                        //    cb_DatabaseTable.SelectedIndex = -1;
                        //    cb_DatabaseTable.SelectedIndex = 0;
                        //}
                        //dgv_columns.Columns["ISIDENTITY"].Visible = true;
                        MessageBox.Show("暂不支持access");
                    }
                }
            }
        }
Пример #2
0
        private void InsertPublicRowsFromDataBase(string as_formtype_id)
        {
            //if (_TableName != "") return;
            string ls_sql = "";

            if (ls_type == "sql")
            {
                ls_sql = "select "
                         + " '' as Table_Name"
                         + " ,A.column_name as Field_Name"
                         + " ,'' as Field_Desc"
                         + " , data_type + case  when character_maximum_length Is null or character_maximum_length > 8000 then '' else '(' + convert(varchar(10),character_maximum_length) + ')'  end as Field_Type"
                         + " ,case  when B.COLUMN_NAME Is null then 'N' else 'Y' end as IsPK"
                         + " ,case  when A.IS_NULLABLE = 'No' then 'N' else 'Y' end as CanNull"
                         + " ,0 as ShowIndex"
                         + " ,case  when  COLUMNPROPERTY(OBJECT_ID('" + as_formtype_id + "'),A.COLUMN_NAME,'IsIdentity') = 1 then 'Y' else 'N' end as IsIdentity"
                         + " from  information_schema.columns A"
                         + " left join"
                         + " INFORMATION_SCHEMA .KEY_COLUMN_USAGE B"
                         + " on "
                         + " A.table_name = B.table_name"
                         + " and A.column_name = B.column_name"
                         + " where A.table_name='" + as_formtype_id + "'";
            }
            else if (ls_type == "ora")
            {
                ls_sql = "SELECT '' AS TABLENAME, B.COLUMN_NAME AS FIELDNAME, COMMENTS AS FIELD_DESC,"
                         + " CASE  WHEN (B.DATA_TYPE = 'INTERGER') OR (B.DATA_TYPE = 'FLOAT') OR (B.DATA_TYPE = 'DATE') THEN  B.DATA_TYPE "
                         + " WHEN B.DATA_TYPE = 'NUMBER' THEN  B.DATA_TYPE  || '(' || TO_CHAR(B.DATA_LENGTH) || ',' || TO_CHAR(DATA_SCALE) || ')'"
                         + " ELSE   B.DATA_TYPE || '(' || B.DATA_LENGTH || ')' END AS FIELD_TYPE,"
                         + " CASE WHEN C.COLUMN_NAME IS NULL THEN 'N' ELSE 'Y' END AS ISPK,"
                         + " B.NULLABLE AS CANNULL,"
                         + " 0 AS SHOWINDEX,"
                         + " 'N' AS ISIDENTITY"
                         + " FROM USER_COL_COMMENTS A INNER JOIN USER_TAB_COLUMNS B"
                         + " ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME"
                         + " AND A.TABLE_NAME = '" + as_formtype_id + "'"
                         + " LEFT JOIN"
                         + " ("
                         + " SELECT * FROM DBA_CONS_COLUMNS "
                         + " WHERE CONSTRAINT_NAME "
                         + " IN (SELECT CONSTRAINT_NAME "
                         + " FROM DBA_CONSTRAINTS"
                         + " WHERE OWNER='SCM_MAIN'"
                         + " AND CONSTRAINT_TYPE='P'"
                         + " AND TABLE_NAME = '" + as_formtype_id + "'"
                         + " )"
                         + " ORDER BY CONSTRAINT_NAME,POSITION"
                         + " ) C"
                         + " ON A.TABLE_NAME = C.TABLE_NAME"
                         + " AND B.COLUMN_NAME = C.COLUMN_NAME";
            }
            else if (ls_type == "mysql")
            {
                ls_sql = "select  '' as Table_Name, column_name  as Field_Name,'' as Field_Desc ,column_type  as Field_Type,"
                         + " case  when COLUMN_KEY = 'PRI' then 'Y' else 'N' end as IsPK,"
                         + " case  when IS_NULLABLE = 'No' then 'N' else 'Y' end as CanNull,"
                         + " 0 as ShowIndex,"
                         + " 'N' as IsIdentity"
                         + " from Information_schema.columns"
                         + " where table_Name ='" + as_formtype_id + "'";
            }
            else if (ls_type == "access")
            {
                MessageBox.Show("暂不支持自动获取!");
            }
            DataTable ldt       = new DataTable();
            bool      lb_result = false;

            if (ls_type == "sql")
            {
                if (ucbTablePosition.Text == Project.DB_Alias)
                {
                    lb_result = SQLServerDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt, "");
                }
                else
                {
                    lb_result = SQLServerDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt, "");
                }
            }
            else if (ls_type == "ora")
            {
                if (ucbTablePosition.Text == Project.DB_Alias)
                {
                    lb_result = OracleDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt);
                }
                else
                {
                    lb_result = OracleDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt);
                }
            }
            else if (ls_type == "mysql")
            {
                if (ucbTablePosition.Text == Project.DB_Alias)
                {
                    lb_result = MySqlDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt);
                }
                else
                {
                    lb_result = MySqlDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt);
                }
            }
            if (lb_result)
            {
                DataTable ldt_old = (DataTable)dgv_columns.DataSource;
                for (int i = ldt_old.Rows.Count - 1; i >= 0; i--)
                {
                    if (ldt_old.Rows[i].RowState != DataRowState.Deleted)
                    {
                        ldt_old.Rows[i].Delete();
                    }
                }

                foreach (DataRow dr in ldt.Rows)
                {
                    DataRow ldr = ldt_old.NewRow();
                    ldr[1] = dr[1];
                    ldr[2] = dr[2];
                    ldr[3] = dr[3];
                    ldr[4] = dr[4];
                    ldr[5] = dr[5];
                    ldr[6] = dr[6];
                    ldr[7] = dr[7];
                    ldt_old.Rows.Add(ldr);
                }
            }
        }