コード例 #1
0
        protected override void LoadTables()
        {
            using (DataTable dataTable = sqliteConnection.GetSchema(SQLiteMetaDataCollectionNames.Tables, new string[] { currentCatalogName })) {
                foreach (DataRow row in dataTable.Rows)
                {
                    if (TablesToLoad.Contains(row["TABLE_NAME"].ToString()))
                    {
                        SourceSchema.Catalogs[currentCatalogName].Tables.Add(row["TABLE_NAME"].ToString(), new SQLite.Table(row));
                    }
                }
            }

            foreach (SQLite.Table table in SourceSchema.Catalogs[currentCatalogName].Tables.Values)
            {
                currentTableName = table.Name;
                LoadColumns();
                LoadForeignKeys();
                LoadIndexes();
                //    LoadTriggers();
            }
        }
コード例 #2
0
        protected override void LoadTables()
        {
            using (DataTable dataTable = sqlConnection.GetSchema(SqlClientMetaDataCollectionNames.Tables, new string[] { null, currentSchemaName, null, "BASE TABLE" })) {
                foreach (DataRow row in dataTable.Rows)
                {
                    if (!defaultIgnores.Contains(row["TABLE_NAME"].ToString()))
                    {
                        if (TablesToLoad.Contains(row["TABLE_NAME"].ToString()))
                        {
                            SourceSchema.Schemas[currentSchemaName].Tables.Add(row["TABLE_NAME"].ToString(), new SqlServer.Table(row));
                        }
                    }
                }
            }

            foreach (SqlServer.Table table in SourceSchema.Schemas[currentSchemaName].Tables.Values)
            {
                currentTableName = table.TableName;
                LoadColumns();
                LoadForeignKeys();
                LoadIndexes();
                //LoadTriggers();
            }
        }
コード例 #3
0
        protected override TableSchema CreateTableSchema(IDbConnection conn, string tableName, string tschma)
        {
            TableSchema res = new TableSchema();

            res.TableName       = tableName;
            res.TableSchemaName = tschma;
            res.DataAction      = TablesToLoad.Find((TableToLoad t) => t.SqlServerFullName.ToLower().Equals((tschma + "." + tableName).ToLower())).SQLiteDataAction;
            res.Columns         = new List <ColumnSchema>();
            SqlCommand cmd = new SqlCommand(@"SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE, " +
                                            @" (columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')) AS [IDENT], " +
                                            @"CHARACTER_MAXIMUM_LENGTH AS CSIZE " +
                                            "FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" + tschma + "' AND TABLE_NAME = '" + tableName + "' ORDER BY " +
                                            "ORDINAL_POSITION ASC", (SqlConnection)conn);

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    object tmp = reader["COLUMN_NAME"];
                    if (tmp is DBNull)
                    {
                        continue;
                    }
                    string colName = (string)reader["COLUMN_NAME"];

                    tmp = reader["COLUMN_DEFAULT"];
                    string colDefault;
                    if (tmp is DBNull)
                    {
                        colDefault = string.Empty;
                    }
                    else
                    {
                        colDefault = (string)tmp;
                    }

                    tmp = reader["IS_NULLABLE"];
                    bool   isNullable = ((string)tmp == "YES");
                    string dataType   = (string)reader["DATA_TYPE"];
                    bool   isIdentity = false;
                    if (reader["IDENT"] != DBNull.Value)
                    {
                        isIdentity = ((int)reader["IDENT"]) == 1 ? true : false;
                    }
                    int length = reader["CSIZE"] != DBNull.Value ? Convert.ToInt32(reader["CSIZE"]) : 0;

                    ValidateDataType(dataType);

                    // Note that not all data type names need to be converted because
                    // SQLite establishes type affinity by searching certain strings
                    // in the type name. For example - everything containing the string
                    // 'int' in its type name will be assigned an INTEGER affinity
                    if (dataType == "timestamp")
                    {
                        dataType = "blob";
                    }
                    else if (dataType == "datetime" || dataType == "smalldatetime")
                    {
                        dataType = "datetime";
                    }
                    else if (dataType == "decimal")
                    {
                        dataType = "numeric";
                    }
                    else if (dataType == "money" || dataType == "smallmoney")
                    {
                        dataType = "numeric";
                    }
                    else if (dataType == "binary" || dataType == "varbinary" ||
                             dataType == "image")
                    {
                        dataType = "blob";
                    }
                    else if (dataType == "tinyint")
                    {
                        dataType = "smallint";
                    }
                    else if (dataType == "bigint")
                    {
                        dataType = "integer";
                    }
                    else if (dataType == "sql_variant")
                    {
                        dataType = "blob";
                    }
                    else if (dataType == "xml")
                    {
                        dataType = "varchar";
                    }
                    else if (dataType == "uniqueidentifier")
                    {
                        dataType = "guid";
                    }
                    else if (dataType == "ntext")
                    {
                        dataType = "text";
                    }
                    else if (dataType == "nchar")
                    {
                        dataType = "char";
                    }
                    else if (dataType == "datetime2")
                    {
                        dataType = "datetime2";
                    }
                    else if (dataType == "date")
                    {
                        dataType = "date";
                    }
                    else if (dataType == "time")
                    {
                        dataType = "time";
                    }

                    if (dataType == "bit" || dataType == "int")
                    {
                        if (colDefault == "('False')")
                        {
                            colDefault = "(0)";
                        }
                        else if (colDefault == "('True')")
                        {
                            colDefault = "(1)";
                        }
                    }

                    colDefault = FixDefaultValueString(colDefault);

                    ColumnSchema col = new ColumnSchema();
                    col.ColumnName   = colName;
                    col.ColumnType   = dataType;
                    col.Length       = length;
                    col.IsNullable   = isNullable;
                    col.IsIdentity   = isIdentity;
                    col.DefaultValue = AdjustDefaultValue(colDefault);
                    res.Columns.Add(col);
                }
            }

            // Find PRIMARY KEY information
            SqlCommand cmd2 = new SqlCommand(@"EXEC sp_pkeys '" + tableName + "'", (SqlConnection)conn);

            using (SqlDataReader reader = cmd2.ExecuteReader())
            {
                res.PrimaryKey = new List <string>();
                while (reader.Read())
                {
                    string colName = (string)reader["COLUMN_NAME"];
                    res.PrimaryKey.Add(colName);
                }
            }

            // Find COLLATE information for all columns in the table
            SqlCommand cmd4 = new SqlCommand(
                @"EXEC sp_tablecollations '" + tschma + "." + tableName + "'", (SqlConnection)conn);

            using (SqlDataReader reader = cmd4.ExecuteReader())
            {
                while (reader.Read())
                {
                    bool?  isCaseSensitive = null;
                    string colName         = (string)reader["name"];
                    if (reader["tds_collation"] != DBNull.Value)
                    {
                        byte[] mask = (byte[])reader["tds_collation"];
                        if ((mask[2] & 0x10) != 0)
                        {
                            isCaseSensitive = false;
                        }
                        else
                        {
                            isCaseSensitive = true;
                        }
                    }

                    if (isCaseSensitive.HasValue)
                    {
                        // Update the corresponding column schema.
                        foreach (ColumnSchema csc in res.Columns)
                        {
                            if (csc.ColumnName == colName)
                            {
                                csc.IsCaseSensitivite = isCaseSensitive;
                                break;
                            }
                        }
                    }
                }
            }

            try
            {
                // Find index information
                SqlCommand cmd3 = new SqlCommand(
                    @"exec sp_helpindex '" + tschma + "." + tableName + "'", (SqlConnection)conn);
                using (SqlDataReader reader = cmd3.ExecuteReader())
                {
                    res.Indexes = new List <IndexSchema>();
                    while (reader.Read())
                    {
                        string indexName = (string)reader["index_name"];
                        string desc      = (string)reader["index_description"];
                        string keys      = (string)reader["index_keys"];

                        // Don't add the index if it is actually a primary key index
                        if (desc.Contains("primary key"))
                        {
                            continue;
                        }

                        IndexSchema index = BuildIndexSchema(indexName, desc, keys);
                        res.Indexes.Add(index);
                    }
                }
            }
            catch (Exception ex)
            {
                //Logging.Log(LogLevel.Warn, "failed to read index information for table [" + tableName + "]");
            }

            return(res);
        }
コード例 #4
0
        protected override DatabaseSchema ReadSourceSchema(SqlConnection sqlServerConnection, SqlConversionHandler handler,
                                                           SqlTableSelectionHandler selectionHandler)
        {
            // First step is to read the names of all tables in the database
            List <TableSchema> tables = new List <TableSchema>();

            sqlServerConnection.Open();

            //Logging.Log(LogLevel.Debug, "SQL Server Connection initialized");

            List <string> tableNames = new List <string>();
            List <string> tblschema  = new List <string>();

            // Initialize in clause for schemas and tables.
            //Logging.Log(LogLevel.Debug, "Intializing schemas and tables to load where clauses");
            string schemasToLoad = Utilities.ContainsSchemaInfo(TablesToLoad) ? Utilities.ConvertTableToLoadToInClause(TablesToLoad, true, "TABLE_SCHEMA").ToUpper() : string.Empty;
            //if (schemasToLoad != string.Empty) //Logging.Log(LogLevel.Debug, string.Format("Schemas IN clause: {0}", schemasToLoad));
            string tablesToLoad = TablesToLoad.Count > 0 ? Utilities.ConvertTableToLoadToInClause(TablesToLoad, false, "TABLE_NAME").ToUpper() : string.Empty;
            //if (tablesToLoad != string.Empty) //Logging.Log(LogLevel.Debug, string.Format("Tables IN clause: {0}", tablesToLoad));

            string whereClause = string.Empty;

            if (schemasToLoad != string.Empty && tablesToLoad != string.Empty)
            {
                whereClause = string.Format(" AND {0} AND {1} ", schemasToLoad, tablesToLoad);
            }
            else if (schemasToLoad != string.Empty && tablesToLoad == string.Empty)
            {
                whereClause = string.Format(" AND {0} ", schemasToLoad);
            }
            else if (schemasToLoad == string.Empty && tablesToLoad != string.Empty)
            {
                whereClause = string.Format(" AND {0} ", tablesToLoad);
            }

            //Logging.Log(LogLevel.Debug, "Intializing SQL statement");
            // This command will read the names of all tables in the database
            string sqlQuery = string.Format(@"select * from INFORMATION_SCHEMA.TABLES  where TABLE_TYPE = 'BASE TABLE'{0}", whereClause);
            //Logging.Log(LogLevel.Debug, string.Format("Sql Server INFORMATION_SCHEMA.TABLES query: \n\n{0}\n\n", sqlQuery));

            SqlCommand cmd = new SqlCommand(sqlQuery, sqlServerConnection);

            //Logging.Log(LogLevel.Debug, "SqlCommand initialized");
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    tableNames.Add((string)reader["TABLE_NAME"]);
                    tblschema.Add((string)reader["TABLE_SCHEMA"]);
                }
            }

            // Next step is to use ADO APIs to query the schema of each table.
            int count = 0;

            for (int i = 0; i < tableNames.Count; i++)
            {
                string tname    = tableNames[i];
                string tschma   = tblschema[i];
                string fullName = tschma + "." + tname;
                // Load only the tables in TablesToLoad.
                if (TablesToLoad.Count > 0 && !TablesToLoad.Exists(t => t.SqlServerFullName.ToLower() == fullName.ToLower()))
                {
                    continue;
                }

                TableSchema ts = CreateTableSchema(sqlServerConnection, tname, tschma);
                CreateForeignKeySchema(sqlServerConnection, ts);
                tables.Add(ts);
                count++;
                CheckCancelled();
                handler(false, true, (int)(count * 50.0 / tableNames.Count), "Parsed table " + tname);

                //Logging.Log(LogLevel.Debug, "parsed table schema for [" + tname + "]");
            }
            sqlServerConnection.Close();

            //Logging.Log(LogLevel.Debug, "finished parsing all tables in SQL Server schema");

            // Allow the user a chance to select which tables to convert, only if the TablesToLoad list isn't defined.
            if (selectionHandler != null && TablesToLoad.Count > 0)
            {
                List <TableSchema> updated = selectionHandler(tables);
                if (updated != null)
                {
                    tables = updated;
                }
            }

            Regex removedbo = new Regex(@"dbo\.", RegexOptions.Compiled | RegexOptions.IgnoreCase);

            // Continue and read all of the views in the database
            List <ViewSchema> views = new List <ViewSchema>();

            sqlServerConnection.Open();
            cmd = new SqlCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION  from INFORMATION_SCHEMA.VIEWS", sqlServerConnection);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                count = 0;
                while (reader.Read())
                {
                    ViewSchema vs = new ViewSchema();
                    vs.ViewName = (string)reader["TABLE_NAME"];
                    vs.ViewSQL  = (string)reader["VIEW_DEFINITION"];

                    // Remove all ".dbo" strings from the view definition
                    vs.ViewSQL = removedbo.Replace(vs.ViewSQL, string.Empty);

                    views.Add(vs);

                    count++;
                    CheckCancelled();
                    handler(false, true, 50 + (int)(count * 50.0 / views.Count), "Parsed view " + vs.ViewName);

                    //Logging.Log(LogLevel.Debug, "parsed view schema for [" + vs.ViewName + "]");
                }
            }
            sqlServerConnection.Close();

            DatabaseSchema ds = new DatabaseSchema();

            ds.Tables = tables;
            ds.Views  = views;
            return(ds);
        }