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");
}
}
}
