예제 #1
0
        public void getSchema()
        {
            try {
                if (conectionString.Contains("Provider=") == true)
                {
                    //add logic
                }
                else if (conectionString.Contains("Driver=") == true)
                {
                    database = con.Database.ToString();
                    tables.Clear();
                    columns.Clear();
                    tables  = con.GetSchema("Tables");
                    columns = con.GetSchema("Columns");
                    if (conectionString.Contains("Microsoft Text Driver"))
                    {
                        databases.Columns.Add("Database");
                        databases.Rows.Add(database);
                    }
                    else
                    {
                        if (databases.Rows.Count <= 0)
                        {
                            databases.Columns.Add("Database");
                            OdbcCommand    oCmd = new OdbcCommand("SHOW DATABASES", con);
                            OdbcDataReader oDR  = oCmd.ExecuteReader();
                            if (oDR.HasRows == true)
                            {
                                while (oDR.Read())
                                {
                                    for (int i = 0; i < oDR.FieldCount; i++)
                                    {
                                        databases.Rows.Add(oDR.GetValue(i).ToString());
                                    }
                                }
                            }
                            oDR.Close();
                            oCmd.Dispose();
                        }
                    }
                }
                else
                {
                    database = mycon.Database.ToString();
                    tables.Clear();
                    columns.Clear();
                    tables  = mycon.GetSchema("Tables");
                    columns = mycon.GetSchema("Columns");
                    if (databases.Rows.Count <= 0)
                    {
                        databases.Columns.Add("Database");
                        MySqlCommand    oCmd = new MySqlCommand("SHOW DATABASES", mycon);
                        MySqlDataReader oDR  = oCmd.ExecuteReader();
                        if (oDR.HasRows == true)
                        {
                            while (oDR.Read())
                            {
                                for (int i = 0; i < oDR.FieldCount; i++)
                                {
                                    databases.Rows.Add(oDR.GetValue(i).ToString());
                                }
                            }
                        }
                        oDR.Close();
                        oCmd.Dispose();
                    }
                }

                if (conectionString.Contains("Microsoft Text Driver"))
                {
                    DBschema.ConnectionName = "QDSCSV";
                    //BEGIN SCHEMA INFO - own Classes Beta
                    foreach (DataRow bd in databases.Rows)
                    {
                        //if(bd[0].ToString() == database) {
                        DatabaseSchema tmpDB = new DatabaseSchema(bd[0].ToString());
                        tmpDB.ConnectionName = DBschema.ConnectionName;
                        foreach (DataRow tbl in tables.Rows)
                        {
                            if (tbl[0].ToString() == bd[0].ToString())
                            {
                                TableSchema tmpTable = new TableSchema(tbl[2].ToString());
                                tmpTable.ConnectionName = DBschema.ConnectionName;
                                tmpTable.DatabaseName   = tmpDB.DatabaseName;
                                if (tables.Columns.Contains("ENGINE") == true)
                                {
                                    tmpTable.Engine = "CSV";
                                }
                                foreach (DataRow col in columns.Rows)
                                {
                                    if (tbl["TABLE_NAME"].ToString() == col["TABLE_NAME"].ToString())
                                    {
                                        if (columns.Columns.Contains("COLUMN_NAME") == true)
                                        {
                                            ColumnSchema tmpCol = new ColumnSchema(col["COLUMN_NAME"].ToString());
                                            tmpCol.ConnectionName = DBschema.ConnectionName;
                                            tmpCol.DatabaseName   = tmpDB.DatabaseName;
                                            tmpCol.TableName      = tmpTable.TableName;
                                            if (columns.Columns.Contains("COLUMN_KEY") == true)
                                            {
                                                if (col["COLUMN_KEY"].ToString() == "PRI")
                                                {
                                                    tmpCol.PrimaryKey = true;
                                                }
                                                else
                                                {
                                                    tmpCol.PrimaryKey = false;
                                                }
                                            }
                                            if (columns.Columns.Contains("ORDINAL_POSITION") == true)
                                            {
                                                tmpCol.ColumnPosition = int.Parse(col["ORDINAL_POSITION"].ToString());
                                            }
                                            if (columns.Columns.Contains("TYPE_NAME") == true)
                                            {
                                                tmpCol.DataType = col["TYPE_NAME"].ToString();
                                            }
                                            if (columns.Columns.Contains("COLUMN_DEFAULT") == true)
                                            {
                                                tmpCol.DefaultValue = col["COLUMN_DEFAULT"].ToString();
                                            }
                                            if (columns.Columns.Contains("COLUMN_COMMENT") == true)
                                            {
                                                tmpCol.Comment = col["COLUMN_COMMENT"].ToString();
                                            }
                                            if (columns.Columns.Contains("IS_NULLABLE") == true)
                                            {
                                                if (col["IS_NULLABLE"].ToString() == "YES")
                                                {
                                                    tmpCol.NotNull = false;
                                                }
                                                else
                                                {
                                                    tmpCol.NotNull = true;
                                                }
                                            }
                                            if (columns.Columns.Contains("EXTRA") == true)
                                            {
                                                if (col["EXTRA"].ToString() == "auto_increment")
                                                {
                                                    tmpCol.AutoIncrement = true;
                                                }
                                                else
                                                {
                                                    tmpCol.AutoIncrement = false;
                                                }
                                            }

                                            tmpTable.addColumn(tmpCol);
                                        }
                                    }
                                }

                                tmpDB.addTable(tmpTable);
                            } //If Table = Table
                        }     //Foreach Table

                        DBschema.addDatabase(tmpDB);

                        //}
                    }//Foreach Database
                     //END SCHEMA INFO
                }
                else
                {
                    DBschema.ConnectionName = "QDS";
                    //BEGIN SCHEMA INFO - own Classes Beta
                    foreach (DataRow bd in databases.Rows)
                    {
                        //if(bd[0].ToString() == database) {
                        DatabaseSchema tmpDB = new DatabaseSchema(bd[0].ToString());
                        tmpDB.ConnectionName = DBschema.ConnectionName;
                        foreach (DataRow tbl in tables.Rows)
                        {
                            if (tbl["TABLE_SCHEMA"].ToString() == bd[0].ToString())
                            {
                                TableSchema tmpTable = new TableSchema(tbl["TABLE_NAME"].ToString());
                                tmpTable.ConnectionName = DBschema.ConnectionName;
                                tmpTable.DatabaseName   = tmpDB.DatabaseName;
                                if (tables.Columns.Contains("ENGINE") == true)
                                {
                                    tmpTable.Engine = tbl["ENGINE"].ToString();
                                }
                                if (tables.Columns.Contains("AUTO_INCREMENT") == true)
                                {
                                    int ai = 0;
                                    Int32.TryParse(tbl["AUTO_INCREMENT"].ToString(), out ai);
                                    tmpTable.AutoIncrement = ai;
                                }
                                foreach (DataRow col in columns.Rows)
                                {
                                    if (tbl["TABLE_NAME"].ToString() == col["TABLE_NAME"].ToString())
                                    {
                                        if (columns.Columns.Contains("COLUMN_NAME") == true)
                                        {
                                            ColumnSchema tmpCol = new ColumnSchema(col["COLUMN_NAME"].ToString());
                                            tmpCol.ConnectionName = DBschema.ConnectionName;
                                            tmpCol.DatabaseName   = tmpDB.DatabaseName;
                                            tmpCol.TableName      = tmpTable.TableName;
                                            if (columns.Columns.Contains("COLUMN_KEY") == true)
                                            {
                                                if (col["COLUMN_KEY"].ToString() == "PRI")
                                                {
                                                    tmpCol.PrimaryKey = true;
                                                }
                                                else
                                                {
                                                    tmpCol.PrimaryKey = false;
                                                }
                                            }
                                            if (columns.Columns.Contains("ORDINAL_POSITION") == true)
                                            {
                                                int idx = 0;
                                                Int32.TryParse(col["ORDINAL_POSITION"].ToString(), out idx);
                                                tmpCol.ColumnPosition = idx;
                                            }
                                            //if(columns.Columns.Contains("DATA_TYPE") == true) {
                                            //    tmpCol.DataType = col["DATA_TYPE"].ToString();
                                            //}
                                            if (columns.Columns.Contains("COLUMN_TYPE") == true)
                                            {
                                                tmpCol.DataType = col["COLUMN_TYPE"].ToString();
                                            }
                                            if (columns.Columns.Contains("COLUMN_DEFAULT") == true)
                                            {
                                                tmpCol.DefaultValue = col["COLUMN_DEFAULT"].ToString();
                                            }
                                            if (columns.Columns.Contains("COLUMN_COMMENT") == true)
                                            {
                                                tmpCol.Comment = col["COLUMN_COMMENT"].ToString();
                                            }
                                            if (columns.Columns.Contains("IS_NULLABLE") == true)
                                            {
                                                if (col["IS_NULLABLE"].ToString() == "YES")
                                                {
                                                    tmpCol.NotNull = false;
                                                }
                                                else
                                                {
                                                    tmpCol.NotNull = true;
                                                }
                                            }
                                            if (columns.Columns.Contains("EXTRA") == true)
                                            {
                                                if (col["EXTRA"].ToString() == "auto_increment")
                                                {
                                                    tmpCol.AutoIncrement = true;
                                                }
                                                else
                                                {
                                                    tmpCol.AutoIncrement = false;
                                                }
                                            }

                                            tmpTable.addColumn(tmpCol);
                                        }
                                    }
                                }

                                tmpDB.addTable(tmpTable);
                            } //If Table = Table
                        }     //Foreach Table

                        DBschema.addDatabase(tmpDB);

                        //}
                    }//Foreach Database
                     //END SCHEMA INFO
                }
            } catch (Exception e) {
                error = e.Message.ToString();
            }
        }
예제 #2
0
 public bool Equals(ColumnSchema obj)
 {
     return(obj != null && obj.GetHashCode() == this.GetHashCode());
 }
예제 #3
0
        public string SQLAlterTable(TableSchema newSchema)
        {
            string sqlh = "";
            string sql  = "";
            int    i    = 0;

            if (Columns != null)
            {
                if (newSchema.Columns != null)
                {
                    sqlh += "ALTER TABLE `" + DatabaseName + "`.`" + TableName + "`\n";
                    for (i = 0; i < newSchema.Columns.Count(); i++)
                    {
                        ColumnSchema col = newSchema.Columns[i];
                        if (C.ContainsKey(col.ColumnName) == false)
                        {
                            //ADD NEW COLUMN
                            if (sql == "")
                            {
                                sql += sqlh + "ADD COLUMN ";
                            }
                            else
                            {
                                sql += "ADD COLUMN ";
                            }
                            sql += "`" + col.ColumnName + "` ";
                            sql += col.DataType + " ";
                            //NN
                            if (col.NotNull == true)
                            {
                                sql += "NOT NULL ";
                            }
                            else
                            {
                                sql += "NULL ";
                            }
                            //DV
                            if (col.DefaultValue == "")
                            {
                                sql += "DEFAULT NULL ";
                            }
                            else
                            {
                                sql += "DEFAULT " + col.DefaultValue + " ";
                            }
                            //AI
                            if (col.AutoIncrement == true)
                            {
                                sql += "AUTO_INCREMENT ";
                            }
                            //Position
                            if (col.ColumnPosition == 0)
                            {
                                sql += "FIRST ";
                            }
                            else
                            {
                                sql += "AFTER `" + newSchema.Columns[col.ColumnPosition - 1].ColumnName + "`";
                            }
                            sql += "\n";
                        }
                        else
                        {
                            //CHANGE COLUMN
                            ColumnSchema oldcol = C[col.ColumnName];
                            if (col.ColumnName != oldcol.ColumnName ||
                                col.PrimaryKey != oldcol.PrimaryKey ||
                                col.DataType != oldcol.DataType ||
                                col.AutoIncrement != oldcol.AutoIncrement ||
                                col.NotNull != oldcol.NotNull ||
                                col.Unique != oldcol.Unique ||
                                col.UnSigned != oldcol.UnSigned ||
                                col.Generated != oldcol.Generated ||
                                col.DefaultValue != oldcol.DefaultValue ||
                                col.ColumnPosition != oldcol.ColumnPosition
                                )
                            {
                                if (sql == "")
                                {
                                    sql += sqlh + "CHANGE COLUMN ";
                                }
                                else
                                {
                                    sql += "CHANGE COLUMN ";
                                }
                                sql += "`" + col.ColumnName + "` ";
                                sql += "`" + col.ColumnName + "` ";
                                sql += col.DataType + " ";
                                //NN
                                if (col.NotNull == true)
                                {
                                    sql += "NOT NULL ";
                                }
                                else
                                {
                                    sql += "NULL ";
                                }
                                //DV
                                if (col.DefaultValue == "")
                                {
                                    sql += "DEFAULT NULL ";
                                }
                                else
                                {
                                    sql += "DEFAULT " + col.DefaultValue + " ";
                                }
                                //AI
                                if (col.AutoIncrement == true)
                                {
                                    sql += "AUTO_INCREMENT ";
                                }
                                //Position
                                if (col.ColumnPosition == oldcol.ColumnPosition)
                                {
                                    sql += "FIRST ";
                                }
                                else
                                {
                                    sql += "AFTER `" + newSchema.Columns[col.ColumnPosition - 1].ColumnName + "`";
                                }
                                sql += "\n";
                            }
                        }
                    }
                    if (sql.Length > 0)
                    {
                        sql  = sql.Substring(0, sql.Length - 1);
                        sql += ";\n";
                    }
                }
            }
            return(sql);
        }