/// <summary> /// Creates the SQLite database from the schema read from the SQL Server. /// </summary> /// <param name="sqlitePath">The path to the generated DB file.</param> /// <param name="schema">The schema of the SQL server database.</param> /// <param name="password">The password to use for encrypting the DB or null if non is needed.</param> /// <param name="handler">A handle for progress notifications.</param> private static void CreateSQLiteDatabase(string sqlitePath, DatabaseSchema schema, string password, SqlConversionHandler handler, FailedViewDefinitionHandler viewFailureHandler) { _log.Debug("Creating SQLite database..."); // Create the SQLite database file SQLiteConnection.CreateFile(sqlitePath); _log.Debug("SQLite file was created successfully at [" + sqlitePath + "]"); // Connect to the newly created database string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password); using (SQLiteConnection conn = new SQLiteConnection(sqliteConnString)) { conn.Open(); // Create all tables in the new database int count = 0; foreach (TableSchema dt in schema.Tables) { try { AddSQLiteTable(conn, dt); } catch (Exception ex) { _log.Error("AddSQLiteTable failed", ex); throw; } count++; CheckCancelled(); handler(false, true, (int)(count * 50.0 / schema.Tables.Count), "Added table " + dt.TableName + " to the SQLite database"); _log.Debug("added schema for SQLite table [" + dt.TableName + "]"); } // foreach // Create all views in the new database count = 0; foreach (ViewSchema vs in schema.Views) { try { AddSQLiteView(conn, vs, viewFailureHandler); } catch (Exception ex) { _log.Error("AddSQLiteView failed", ex); throw; } // catch count++; CheckCancelled(); handler(false, true, 50+(int)(count * 50.0 / schema.Views.Count), "Added view " + vs.ViewName + " to the SQLite database"); _log.Debug("added schema for SQLite view [" + vs.ViewName + "]"); } // foreach } // using _log.Debug("finished adding all table/view schemas for SQLite database"); }
/// <summary> /// Reads the entire SQL Server DB schema using the specified connection string. /// </summary> /// <param name="connString">The connection string used for reading SQL Server schema.</param> /// <param name="handler">A handler for progress notifications.</param> /// <param name="selectionHandler">The selection handler which allows the user to select /// which tables to convert.</param> /// <returns>database schema objects for every table/view in the SQL Server database.</returns> private static DatabaseSchema ReadSqlServerSchema(string connString, SqlConversionHandler handler, SqlTableSelectionHandler selectionHandler) { // First step is to read the names of all tables in the database List<TableSchema> tables = new List<TableSchema>(); using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); List<string> tableNames = new List<string>(); List<string> tblschema = new List<string>(); // This command will read the names of all tables in the database SqlCommand cmd = new SqlCommand(@"select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'", conn); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { tableNames.Add((string)reader["TABLE_NAME"]); tblschema.Add((string)reader["TABLE_SCHEMA"]); } // while } // using // 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]; TableSchema ts = CreateTableSchema(conn, tname, tschma); CreateForeignKeySchema(conn, ts); tables.Add(ts); count++; CheckCancelled(); handler(false, true, (int)(count * 50.0 / tableNames.Count), "Parsed table " + tname); _log.Debug("parsed table schema for [" + tname + "]"); } // foreach } // using _log.Debug("finished parsing all tables in SQL Server schema"); // Allow the user a chance to select which tables to convert if (selectionHandler != null) { List<TableSchema> updated = selectionHandler(tables); if (updated != null) tables = updated; } // if 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>(); using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); SqlCommand cmd = new SqlCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", conn); using (SqlDataReader reader = cmd.ExecuteReader()) { int 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); _log.Debug("parsed view schema for [" + vs.ViewName + "]"); } // while } // using } // using DatabaseSchema ds = new DatabaseSchema(); ds.Tables = tables; ds.Views = views; return ds; }
/// <summary> /// Reads the entire SQL Server DB schema using the specified connection string. /// </summary> /// <param name="connString">The connection string used for reading SQL Server schema.</param> /// <param name="handler">A handler for progress notifications.</param> /// <param name="selectionHandler">The selection handler which allows the user to select /// which tables to convert.</param> /// <returns>database schema objects for every table/view in the SQL Server database.</returns> private static DatabaseSchema ReadOracleSchema(string connString, SqlConversionHandler handler,List<String> includedTables, List<String> existedTables) { // First step is to read the names of all tables in the database List<TableSchema> tables = new List<TableSchema>(); using (OracleConnection conn = new OracleConnection(connString)) { conn.Open(); // Next step is to use ADO APIs to query the schema of each table. int count = 0; for (int i = 0; i < includedTables.Count; i++) { string tname = includedTables[i]; TableSchema ts = CreateTableSchema(conn, tname, ""); //TODO Schema ts.Existed = existedTables.Contains(tname); //CreateForeignKeySchema(conn, ts); tables.Add(ts); count++; CheckCancelled(); handler(false, true, (int)(count * 50.0 / includedTables.Count), "Parsed table " + tname); _log.Debug("parsed table schema for [" + tname + "]"); } // foreach } // using _log.Debug("finished parsing all tables in SQL Server schema"); // 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>(); using (OracleConnection conn = new OracleConnection(connString)) { conn.Open(); OracleCommand cmd = new OracleCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", conn); using (OracleDataReader reader = cmd.ExecuteReader()) { int count = 0; while (reader.Read()) { ViewSchema vs = new ViewSchema(); if (reader["TABLE_NAME"] == DBNull.Value) continue; if (reader["VIEW_DEFINITION"] == DBNull.Value) continue; 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); _log.Debug("parsed view schema for [" + vs.ViewName + "]"); } // while } // using } // using */ DatabaseSchema ds = new DatabaseSchema(); ds.Tables = tables; // ds.Views = views; return ds; }