public void GenerateData(DbInfo dbInfo, TextWriter textWriter) { var serverConn = new ServerConnection(dbInfo.Server, dbInfo.UserId, dbInfo.Password); Server srv = new Server(serverConn); Database db = srv.Databases[dbInfo.DbName]; if (db == null) { throw new Exception($"Database {dbInfo.DbName} not exists."); } Scripter scripter = new Scripter(srv); scripter.Options.ScriptData = true; scripter.Options.ScriptSchema = false; textWriter.WriteLine("-----------------------------------------"); textWriter.WriteLine("-----------------data--------------------"); textWriter.WriteLine("-----------------------------------------"); foreach (Urn table in getTableUrnWithDependence(db)) { Console.WriteLine(); var sc = scripter.EnumScript(new Urn[] { table }); foreach (string st in sc) { Console.WriteLine(st); textWriter.WriteLine(st); } if (sc.Count() > 0) { textWriter.WriteLine("GO"); } } }
public static void Generate(DbInfo dbInfo, bool includeData, bool seperateSchemaAndData, string outputDir) { Directory.CreateDirectory(outputDir); string schemaFileName = null, dataFileName = null; TextWriter twForSchema = null, twForData = null; try { if (seperateSchemaAndData) { schemaFileName = Path.Combine(outputDir, $"{dbInfo.DbName}-schema.sql"); twForSchema = File.CreateText(schemaFileName); dataFileName = Path.Combine(outputDir, $"{dbInfo.DbName}-data.sql"); twForData = File.CreateText(dataFileName); } else { schemaFileName = dataFileName = Path.Combine(outputDir, $"{dbInfo.DbName}.sql"); twForSchema = twForData = File.CreateText(schemaFileName); } IScriptService scriptService = new SmoService(); scriptService.GenerateSchema(dbInfo, twForSchema); twForSchema.Flush(); if (includeData) { scriptService.GenerateData(dbInfo, twForData); twForData.Flush(); } } catch (Exception e) { // clean up try { twForSchema?.Close(); twForData?.Close(); File.Delete(schemaFileName); File.Delete(dataFileName); } catch { } throw e; } finally { twForSchema?.Dispose(); twForData?.Dispose(); } }
public void GenerateSchema(DbInfo dbInfo, TextWriter textWriter) { var serverConn = new ServerConnection(dbInfo.Server, dbInfo.UserId, dbInfo.Password); Server srv = new Server(serverConn); Database db = srv.Databases[dbInfo.DbName]; if (db == null) { throw new Exception($"Database {dbInfo.DbName} not exists."); } Scripter scripter = new Scripter(srv); scripter.Options.NoCollation = true; scripter.Options.Indexes = true; scripter.Options.ClusteredIndexes = true; scripter.Options.NonClusteredIndexes = true; scripter.Options.DriAll = true; scripter.Options.DriAllConstraints = true; scripter.Options.Triggers = true; scripter.Options.FullTextIndexes = true; scripter.Options.AllowSystemObjects = false; scripter.Options.WithDependencies = false; scripter.Options.TargetDatabaseEngineEdition = DatabaseEngineEdition.Standard; scripter.Options.TargetDatabaseEngineType = DatabaseEngineType.Standalone; scripter.Options.ExtendedProperties = true; scripter.Options.ScriptDrops = false; scripter.Options.ScriptSchema = true; scripter.Options.ScriptData = false; textWriter.WriteLine("-----------------------------------------"); textWriter.WriteLine("-----------------schema------------------"); textWriter.WriteLine("-----------------------------------------"); foreach (Urn table in getTableUrnWithDependence(db)) { System.Collections.Specialized.StringCollection sc = scripter.Script(new Urn[] { table }); foreach (string st in sc) { Console.WriteLine(st); textWriter.WriteLine(st); textWriter.WriteLine("GO"); } } var smoProcedures = db.StoredProcedures.Cast <StoredProcedure>().Where(t => !t.IsSystemObject).ToList(); textWriter.WriteLine("-----------------------------------------"); textWriter.WriteLine("-----------------procedure---------------"); textWriter.WriteLine("-----------------------------------------"); foreach (var procedureSmo in smoProcedures) { System.Collections.Specialized.StringCollection sc = scripter.Script(new Urn[] { procedureSmo.Urn }); foreach (string st in sc) { Console.WriteLine(st); textWriter.WriteLine(st); textWriter.WriteLine("GO"); } } }