public void ExportData(string connectionString, string outputDirectory, bool verbose)
{
ScriptingOptions _options = new ScriptingOptions()
{
AllowSystemObjects = false,
ScriptData = true,
ScriptSchema = false
};
// connection
using (SqlConnection _connection = new SqlConnection(connectionString))
{
ServerConnection _serverConn = new ServerConnection(_connection);
Server _server = new Server(_serverConn);
_server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
Database _db = _server.Databases[_connection.Database];
Scripter _scripter = new Scripter(_server);
_scripter.Options = _options;
foreach (Table _table in _db.Tables)
{
if (!_table.IsSystemObject)
{
string _outputPath = this.CreateOutputFile(outputDirectory, _table);
this.ScriptTable(_scripter, _table, _outputPath, false);
}
}
}
}
private string ScriptTableSchema()
{
string sqlScript = "";
StringBuilder sb = new StringBuilder();
ScriptingOptions options = new ScriptingOptions();
options.NoCollation = true;
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
options.IncludeIfNotExists = true;
options.Triggers = true;
options.SchemaQualify = true;
StringCollection coll = SelectedTable.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
sb.AppendLine("GO");
sqlScript = sb.ToString();
return(sqlScript);
}
public string Script(ScriptingOptions so)
{
StringBuilder sbScript = new StringBuilder();
sbScript.Append("CREATE FULLTEXT CATALOG [" + Name + "]" + System.Environment.NewLine);
if (so.ServerMajorVersion < 10)
{
if (FileGroup != "")
{
sbScript.Append("\tON FILEGROUP [" + FileGroup + "]," + System.Environment.NewLine);
}
if (so.FullTextCatalogPath)
{
if (Path != "")
{
sbScript.Append("\tIN PATH '" + Path + "'," + System.Environment.NewLine);
}
}
}
sbScript.Append("\tWITH ACCENT_SENSITIVITY = ");
if (IsAccentSensitive)
{
sbScript.Append("ON," + System.Environment.NewLine);
}
else
{
sbScript.Append("OFF," + System.Environment.NewLine);
}
if (IsDefault)
{
sbScript.Append("\tAS DEFAULT, " + System.Environment.NewLine);
}
sbScript.Append("\tAUTHORIZATION [" + Principal + "]");
return(sbScript.ToString());
}
private void UploadDatabaseBackupScript(GoogleDriveManager googleDriveManager)
{
var connectionString = ConfigurationManager.ConnectionStrings["Gallery"].ToString();
var server = new Server(new ServerConnection(new SqlConnection(connectionString)));
var database = server.Databases[new SqlConnectionStringBuilder(connectionString).InitialCatalog];
var options = new ScriptingOptions
{
ScriptData = true,
ScriptSchema = true,
ScriptDrops = false,
Indexes = true,
IncludeHeaders = true
};
byte[] bytes = null;
using (var ms = new MemoryStream())
{
TextWriter tw = new StreamWriter(ms);
foreach (Table table in database.Tables)
{
foreach (var statement in table.EnumScript(options))
{
tw.WriteLine(statement);
}
}
tw.Flush();
ms.Position = 0;
bytes = ms.ToArray();
}
googleDriveManager.Upload($"#Backup#{DateTime.Now:yyyy-MM-dd_HH-mm-ss}.sql", bytes);
}
private static void WriteInsertions(Stream stream, Table table)
{
var tempFileName = Path.GetTempFileName();
try
{
var options = new ScriptingOptions
{
AppendToFile = true,
EnforceScriptingOptions = true,
FileName = tempFileName,
ScriptData = true,
ScriptSchema = false,
Encoding = DEFAULT_ENCODING,
NoCommandTerminator = true,
};
_ = table.EnumScript(options);
using var tempFileStream = File.OpenRead(tempFileName);
// Skipping encoding preamble bytes.
tempFileStream.Position = DEFAULT_ENCODING.GetPreamble().Length;
tempFileStream.CopyTo(stream);
}
finally
{
if (File.Exists(tempFileName))
{
File.Delete(tempFileName);
}
}
}
/// <summary>
/// Generating Database script from given connection string.
/// </summary>
/// <param name="connectionString">SqlServer connection string.</param>
/// <param name="fileName">Filename</param>
/// <returns></returns>
public async Task GenerateDatabaseScript(string connectionString, string fileName)
{
using (var sql = new SqlConnection(connectionString))
{
var server = new Server(new ServerConnection(sql));
var db = server.Databases[sql.Database];
var options = new ScriptingOptions()
{
FileName = fileName,
EnforceScriptingOptions = true,
WithDependencies = false,
IncludeHeaders = true,
ScriptDrops = false,
AppendToFile = true,
ScriptSchema = true,
ScriptData = true,
Indexes = false,
ScriptOwner = true,
};
await Task.Factory.StartNew(() =>
{
foreach (Table tb in db.Tables)
{
db.Tables[tb.Name].EnumScript(options);
}
});
}
}
public static void scriptTable(string tableName, bool scriptData)
{
ScriptingOptions opts = new ScriptingOptions()
{
ScriptData = scriptData,
ScriptDrops = false,
ScriptSchema = true,
IncludeDatabaseContext = true,
FileName = string.Format(@"{0}\{1}_{2}{3}.sql",
scriptDir,
tableName,
DateTime.Now.ToString("hhMMddyyyy"),
(!scriptData) ? "_so" : string.Empty)
};
if (db != null)
{
if (db.Tables.Contains(tableName))
{
db.Tables[tableName].EnumScript(opts);
}
else
{
throw new KeyNotFoundException(string.Format("Database object does not contain table with key: {0}", tableName));
}
}
else
{
throw new NullReferenceException("Database object is null!")
{
Source = "Grimoire.Utilities.Database.db"
}
};
}
public void Backup()
{
StringCollection stringCollection = new StringCollection();
ScriptingOptions scriptOptions = new ScriptingOptions
{
IncludeDatabaseContext = true
};
String script = "";
String jobName = "";
foreach (Job job in server.JobServer.Jobs)
{
script = "";
jobName = job.Name.ToString();
stringCollection = job.Script(scriptOptions);
foreach (string s in stringCollection)
{
script += s;
}
jobName = string.Join("_", jobName.Split(Path.GetInvalidFileNameChars()));
TextWriter tw = new StreamWriter(dirPath + jobName + @".sql");
tw.Write(script);
tw.Close();
}
}
public NameValueCollection TestUser()
{
NameValueCollection users = new NameValueCollection();
StringBuilder sb = new StringBuilder();
if (ConnectToServer())
{
this.smoDatabase = this.smoServer.Databases[this.data.DatabaseName];
if (this.smoDatabase == null)
{
return(users);
}
for (int i = 1; i < this.smoDatabase.Users.Count; i++)
{
sb.Length = 0;
User user = this.smoDatabase.Users[i];
ScriptingOptions options = new ScriptingOptions();
options.Permissions = true;
options.PrimaryObject = true;
options.LoginSid = true;
options.AnsiFile = true;
StringCollection coll = user.Script(options);
foreach (string s in coll)
{
sb.AppendLine(s);
sb.AppendLine("GO");
sb.AppendLine("\r\n");
}
users.Add(user.Name, sb.ToString());
}
}
return(users);
}
public static void ScriptUserDefinedFunctions(Database sDatabase, ScriptingOptions cOption, ScriptingOptions rOption, string funcName, string schemaName)
{
UserDefinedFunction userFunction = sDatabase.UserDefinedFunctions[funcName, schemaName];
userFunction.Script(cOption);
userFunction.Script(rOption);
}
public static Biml BuildBiml( BimlRequest request )
{
// Configure SQL SMO
var server = new Server( request.ServerName );
var scriptingOptions = new ScriptingOptions { Encoding = Encoding.UTF8 };
server.Script( scriptingOptions );
var database = new Microsoft.SqlServer.Management.Smo.Database( server, request.DatabaseName );
database.Refresh();
var bimlService = new BimlService();
var output = new Biml();
// Selectively build sections
if ( request.HasConnections )
output.Connections = bimlService.GetConnections( server, database );
if ( request.HasDatabases )
output.Databases = bimlService.GetDatabases( database );
if( request.HasSchemas )
output.Schemas = bimlService.GetSchemas( database );
if (request.HasTables)
{
output.Tables = bimlService.GetTables( database, request.HasFactsAndDimensions );
}
if (request.HasFactsAndDimensions)
{
output.Facts = bimlService.GetFacts( database );
output.Dimensions = bimlService.GetDimensions( database );
}
return output;
}
private string ScriptSchemas()
{
StringBuilder sb = new StringBuilder();
ScriptingOptions option = new ScriptingOptions();
option.IncludeIfNotExists = true;
option.IncludeHeaders = true;
sb.AppendLine("--================ Start of Schemas script ( " + DateTime.Now + " ) ================");
foreach (Schema schema in SelectedDB.Schemas)
{
if (!schema.IsSystemObject)
{
StringCollection coll = schema.Script(option);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
sb.AppendLine("GO");
}
}
sb.AppendLine("--================ End of Schemas script ( " + DateTime.Now + " ) ================");
sb.AppendLine(Environment.NewLine);
SQLScriptSchemas = sb.ToString();
return(SQLScriptSchemas);
}
static void Main(string[] args)
{
Server srv = new Server();
// really you would get these from config or elsewhere:
srv.ConnectionContext.Login = "******";
srv.ConnectionContext.Password = "******";
srv.ConnectionContext.ServerInstance = "ServerName";
string dbName = "DatabaseName";
Database db = new Database();
db = srv.Databases[dbName];
StringBuilder sb = new StringBuilder();
foreach (Table tbl in db.Tables)
{
ScriptingOptions options = new ScriptingOptions();
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
StringCollection coll = tbl.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
}
System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
fs.Write(sb.ToString());
fs.Close();
}
private void ScriptIndexes(Microsoft.SqlServer.Management.Smo.Table tableToScript)
{
ScriptingOptions options = new ScriptingOptions();
options.ScriptDrops = true;
options.ScriptSchema = true;
options.NoCollation = true;
options.ClusteredIndexes = true;
options.Default = true;
options.NonClusteredIndexes = true;
options.IncludeIfNotExists = true;
options.Indexes = true;
if (tableToScript.Indexes.Count > 0)
{
StringBuilder resultScript = new StringBuilder(string.Empty);
StringCollection coll = null;
foreach (Index ix in tableToScript.Indexes)
{
coll = ix.Script();
foreach (string str in coll)
{
resultScript.Append(str);
resultScript.Append(Environment.NewLine);
}
}
SQLScriptIndexes += resultScript;
SQLScriptIndexes += Environment.NewLine;
}
}
/// <summary>
/// Scripts a <see cref="IScriptable" /> along with provided options.
/// </summary>
/// <param name="scriptableObject">Object to script.</param>
/// <param name="dropOptions">Drop options; null will ommit.</param>
/// <param name="createOptions">Create options.</param>
/// <param name="scrubScript">Value indicating whether or not to scrub the script to make it more readable and remove issues that prvent running.</param>
/// <returns>Scripted object as a string.</returns>
public static string Script(IScriptable scriptableObject, ScriptingOptions dropOptions, ScriptingOptions createOptions, bool scrubScript)
{
new { scriptableObject }.AsArg().Must().NotBeNull();
new { dropOptions }.AsArg().Must().NotBeNull();
new { createOptions }.AsArg().Must().NotBeNull();
StringCollection dropScript = null;
if (dropOptions != null)
{
dropScript = scriptableObject.Script(dropOptions);
dropScript.Add("GO");
}
var createScript = scriptableObject.Script(createOptions);
createScript.Add("GO");
if (scrubScript)
{
ScrubScript(createScript);
}
return(StringCollectionToSingleString(dropScript) + StringCollectionToSingleString(createScript));
}
/// <summary>
/// Initializes a new instance of the <see cref="DatabaseScripter"/> class.
/// </summary>
/// <param name="parsedDatabase">The parsed database.</param>
/// <param name="connectionString">The connection string.</param>
public DatabaseScripter(ScriptParser parsedDatabase, string connectionString)
{
this.parsedDatabase = parsedDatabase;
SqlConnection connection = null;
try
{
connection = new SqlConnection(connectionString);
ServerConnection serverConnection = new ServerConnection(connection);
server = new Server(serverConnection);
database = server.Databases[connection.Database];
}
catch (Exception ex)
{
throw new ApplicationException("Unable to connect to database at " + connectionString, ex);
}
if (database == null)
{
throw new ApplicationException("Database " + connection.Database + " not found.");
}
scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.DriAllConstraints = true;
scriptOptions.DriIncludeSystemNames = true;
scriptOptions.DriIndexes = true;
scriptOptions.FullTextIndexes = true;
scriptOptions.Indexes = true;
scriptOptions.Permissions = true;
scriptOptions.SchemaQualify = true;
scriptOptions.SchemaQualifyForeignKeysReferences = true;
scriptOptions.Statistics = false;
scriptOptions.Triggers = true;
}
private static void GetFunctionScript(string database)
{
functions.Columns.Add("FUNCTION_SCRIPT", typeof(String));
ServerConnection serverConnection = new ServerConnection(Utility.DBConnection);
Server server = new Server(serverConnection);
Database db = server.Databases[database];
ScriptingOptions so = new ScriptingOptions();
so.ChangeTracking = true;
so.ClusteredIndexes = true;
so.ExtendedProperties = true;
foreach (UserDefinedFunction func in db.UserDefinedFunctions)
{
if (!func.IsSystemObject)
{
StringCollection script = func.Script(so);
string[] scriptArray = new string[script.Count];
script.CopyTo(scriptArray, 0);
DataRow tableRow = (functions.Select("SPECIFIC_NAME = '" + func.Name + "'"))[0];
for (int i = 0; i < scriptArray.Length; i++)
{
//scriptArray[i] = Utility.SplitString(scriptArray[i], 150);
scriptArray[i] = scriptArray[i].Replace("@level0type", "\n\t@level0type");
}
tableRow["FUNCTION_SCRIPT"] = string.Join(Environment.NewLine, scriptArray);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("Data Source=" + txtSunucu.Text + ";Initial Catalog=" + txtDatabase.Text + ";MultipleActiveResultSets=True;App=EntityFramework;User ID=" + txtUser.Text + ";Password="******"");
var baglanti = new ServerConnection(builder.DataSource, builder.UserID, builder.Password);
Server svr = new Server(baglanti);
Microsoft.SqlServer.Management.Smo.Database dbs = svr.Databases[builder.InitialCatalog];
ScriptingOptions options = new ScriptingOptions
{
ScriptData = true,
ScriptDrops = false,
FileName = FileName,
EnforceScriptingOptions = true,
ScriptSchema = true,
IncludeHeaders = true,
AppendToFile = true,
Indexes = true,
ScriptDataCompression = true,
WithDependencies = true
};
foreach (Table tbl in dbs.Tables)
{
tbl.EnumScript(options);
}
MessageBox.Show(DateTime.Now.ToShortDateString() + " Yedek alma işlemi tamamlandı", "Uyarı !", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception)
{
MessageBox.Show("Hata", "Bir hata oluştu. İşlem gerçekleştirilemedi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
string RecreateObject(IScriptable scriptableObject, ScriptingOptions sCO)
{
StringBuilder sb = new StringBuilder();
// Generate Drop script
ScriptingOptions so = new ScriptingOptions();
so.ScriptDrops = true;
so.IncludeIfNotExists = true;
foreach (string stmt in scriptableObject.Script(so))
{
sb.AppendLine(stmt);
}
// Add batch separator
sb.AppendLine("GO");
// Now generate Crate script and add it to the resulting ScriptCollection
sCO.ScriptDrops = false;
sCO.IncludeIfNotExists = false;
foreach (string stmt in scriptableObject.Script(sCO))
{
sb.AppendLine(stmt);
if (stmt.Contains("SET ANSI_NULLS") || stmt.Contains("SET QUOTED_IDENTIFIER"))
{
sb.AppendLine("GO");
}
}
//for (int i = 0; i < sc.Count; i++)
// sb.Append(sc[i]);
return(sb.ToString());
}
public static void ScriptTables(Database sDatabase, ScriptingOptions cOption, ScriptingOptions rOption, string tableName, string schemaName)
{
Table table = sDatabase.Tables[tableName, schemaName];
table.Script(cOption);
table.Script(rOption);
}
public static void ScriptStoredProcedure(Database sDatabase, ScriptingOptions cOption, ScriptingOptions rOption, string procName, string schemaName)
{
StoredProcedure storedProcedure = sDatabase.StoredProcedures[procName, schemaName];
storedProcedure.Script(cOption);
storedProcedure.Script(rOption);
}
//Генерация скриптов для представлений
private static void GenerateViewScript(Server myServer, string path)
{
Directory.CreateDirectory(path + @"\Views\");
string text = "";
Scripter scripter = new Scripter(myServer);
Database myAdventureWorks = myServer.Databases[DBName];
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = false;
scriptOptions.IncludeIfNotExists = false;
scriptOptions.Encoding = System.Text.Encoding.GetEncoding(1251);
foreach (Microsoft.SqlServer.Management.Smo.View myView in myAdventureWorks.Views)
{
if (myView.IsSystemObject)
{
continue;
}
StringCollection ProcedureScripts = myView.Script(scriptOptions);
string newSql = "";
foreach (string script in ProcedureScripts)
{
newSql = newSql + script + newline;
text = text + script + newline;
}
File.WriteAllText(path + @"\Views\" + myView.Name + ".sql", newSql, System.Text.Encoding.GetEncoding(1251));
}
File.WriteAllText(path + @"\" + "AllView.sql", text, System.Text.Encoding.GetEncoding(1251));
}
protected ScripterBase(ICollection objects, DatabaseObjectType objectType)
{
ObjectType = objectType;
Objects = objects;
OutputFolder = Path.Combine(Program.Options.OutputDirectory, objectType.ToString());
Filter = new Regex(Program.Options.Filter, RegexOptions.Compiled | RegexOptions.IgnoreCase);
ScriptOptionsDrop = new ScriptingOptions
{
ScriptDrops = true,
IncludeIfNotExists = true,
AllowSystemObjects = false,
NoCommandTerminator = false,
AgentJobId = false,
Statistics = false,
ContinueScriptingOnError = true
};
ScriptOptionsCreate = new ScriptingOptions
{
ScriptDrops = false,
SchemaQualify = true,
AllowSystemObjects = false,
NoCommandTerminator = false,
ContinueScriptingOnError = true
};
lock (SyncRoot)
{
if (OutputFolder.IndexOf("\\", System.StringComparison.Ordinal) > -1 && !Directory.Exists(OutputFolder))
Directory.CreateDirectory(OutputFolder);
}
}
static void Main(string[] args)
{
// connect to default instance on local machine
Server server = new Server(".");
ScriptingOptions so = new ScriptingOptions();
so.Triggers = true;
Microsoft.SqlServer.Management.Smo.Transfer tr = new Transfer();
// Note: Make sure that srcdb exists on local machine's default instance
tr.Database = server.Databases["srcdb"];
tr.DestinationDatabase = "targetdb";
tr.DestinationServer = ".";
tr.DestinationLoginSecure = true;
tr.Options = so;
// Enumerate scripts
List<string> scripts = tr.EnumScriptTransfer().ToList<string>();
// print generated scripts to console
foreach (string script in scripts)
{
Console.WriteLine(script);
Console.WriteLine("GO");
}
}
static void Main(string[] args)
{
Server server = new Server("localhost");
Database database = server.Databases["SmoDemoDatabase"];
Table table = database.Tables["Customers"];
ScriptingOptions tableScriptingOptions = new ScriptingOptions();
tableScriptingOptions.Add(ScriptOption.DriNonClustered);
StringCollection tableScript = table.Script(tableScriptingOptions);
foreach (var scriptString in tableScript)
{
Console.WriteLine(scriptString);
}
Console.WriteLine();
Console.WriteLine();
StringCollection databaseScript = database.Script();
foreach (var scriptString in databaseScript)
{
Console.Write(scriptString);
}
Console.WriteLine();
Console.WriteLine();
Console.WriteLine("Finished");
Console.ReadKey();
}
private string ScriptLinkedServers(string outputFilePath, string filePrefix)
{
string strRetVal = "";
if (!this.IncludeLinkedServers)
{
strRetVal = "Linked server scripts were not included.";
return(strRetVal);
}
try
{
Scripter scr = new Scripter(DBServer);
GetSystemObjectsProperty();
LinkedServer[] linkedServers = new LinkedServer[this.DBServer.LinkedServers.Count];
this.DBServer.LinkedServers.CopyTo(linkedServers, 0);
ScriptingOptions options = GetDBScriptingOptions(false);
options.FileName = GetFileName(this.DBServer.Name, outputFilePath, filePrefix);
scr.Options = options;
scr.Script(linkedServers);
strRetVal = "Susccessfully generated Linked server script for Server:" + this.DBServer.Name + " and saved to file:" + options.FileName;
}
catch (Exception ex)
{
strRetVal = "Error generating Linked server script for server:" + this.DBServer.Name + " Error: " + ex.Message;
if (ex.InnerException != null)
{
strRetVal = ex.InnerException.Message + System.Environment.NewLine + strRetVal;
}
}
return(strRetVal);
}
private string GetScript(ScriptingOptions options, StringCollection stringCollection)
{
StringBuilder sb = new StringBuilder();
foreach (var item in stringCollection)
{
sb.Append(item);
if (options != null && !options.NoCommandTerminator)
{
//Ensure the batch separator is always on a new line (to avoid syntax errors)
//but don't write an extra if we already have one as this can affect definitions
//of objects such as Stored Procedures (see TFS#9125366)
sb.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}{2}",
item.EndsWith(Environment.NewLine) ? string.Empty : Environment.NewLine,
CommonConstants.DefaultBatchSeperator,
Environment.NewLine);
}
else
{
sb.AppendFormat(CultureInfo.InvariantCulture, Environment.NewLine);
}
}
return(sb.ToString());
}
private ScriptingOptions SetOptions(IDictionary inParams)
{
var so = new ScriptingOptions();
var sd = SwappableDictionary.FromIDictionary(inParams);
so.Encoding = _enc;
if (sd.ContainsKey(ENCODING))
{
sd.Remove(ENCODING);
}
this.ReplaceParameters(ref sd);
PropertyInfo[] allProps = so.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
for (int i = 0; i < allProps.Length; i++)
{
PropertyInfo pi = allProps[i];
if (sd.TryGetKey(pi.Name, StringComparison.CurrentCultureIgnoreCase, out string realKey) &&
this.HasWritableValue(sd[realKey], out object write))
{
pi.SetValue(so, write);
}
}
return(so);
}
public override string GenerateCreateViewDdl(DatabaseConnection databaseConnection, string database, string schema, string viewName)
{
if (databaseConnection == null)
{
throw new ArgumentNullException("databaseConnection");
}
if (database == null)
{
throw new ArgumentNullException("database");
}
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (viewName == null)
{
throw new ArgumentNullException("viewName");
}
var options = new ScriptingOptions();
options.ClusteredIndexes = true;
options.DriAll = true;
options.FullTextIndexes = true;
options.IncludeDatabaseContext = true;
options.IncludeHeaders = true;
options.Indexes = true;
options.SchemaQualify = true;
options.Triggers = true;
options.XmlIndexes = true;
options.ScriptBatchTerminator = true;
options.BatchSize = 1;
return(GenerateViewDdlInternal(databaseConnection, database, schema, viewName, options));
}
// Scripts an item in right window if it is scriptable
private void DependenciesTreeView_AfterSelect(object sender, TreeViewEventArgs e)
{
this.ScriptTextBox.Clear();
if (e.Node.Tag == null)
{
return;
}
// Script an object
SqlSmoObject o = this.sqlServerSelection.GetSmoObject((Urn)e.Node.Tag);
ScriptingOptions so = new ScriptingOptions();
so.DriAll = true;
so.Indexes = true;
so.IncludeHeaders = true;
so.Permissions = true;
so.PrimaryObject = true;
so.SchemaQualify = true;
so.Triggers = true;
IScriptable scriptableObject = o as IScriptable;
if (scriptableObject != null)
{
StringBuilder sb = new StringBuilder();
foreach (string s in scriptableObject.Script(so))
{
sb.Append(s);
sb.Append(Environment.NewLine);
}
this.ScriptTextBox.Text = sb.ToString();
}
}
public static void CreateTempTable()
{
DropTempTable();
ScriptingOptions options = new ScriptingOptions
{
Default = true,
NoIdentities = true,
DriAll = false,
DriPrimaryKey = false
};
StringCollection script = PrimaryTable.Script(options);
var sb = new StringBuilder();
foreach (string str in script)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
string tempScript = sb.ToString().Replace("MEASURING_DATA", "MEASURING_DATA_TEMP");
tempScript = tempScript.ToString().Replace("IDENTITY(1, 1)", "");
try {
RunSqlScript(tempScript);
}
catch (Exception e) { }
}
public static void Script(string[] target, Database db
, string output, bool progress, SqlServerVersion sql_version
, bool do_version)//.Version100)
{
if (null == db)
{
throw new ScripterException("Invalid Database");
}
_do_version = do_version;
Scripter scripter = new Scripter(db.Parent);
ScriptingOptions op = new ScriptingOptions
{
AllowSystemObjects = false
,
WithDependencies = false
, ClusteredIndexes = true
, Indexes = true
, DriAllConstraints = true
//,
//, DriAll = true
, TargetServerVersion = sql_version
};
System.Console.WriteLine("Target Version {0}", op.TargetServerVersion);
scripter.Options = op;
Script(target, db, scripter, output, progress);
}
public string GetTableDescription(string pDatabaseName, string pSchemaName, string pTableName, string connectionString)
{
connectionString = ConnectionHelper.RemoveProviderFromConnectionString(connectionString);
Server server = new Server(new ServerConnection(new SqlConnection(connectionString)));
Database db = server.Databases[pDatabaseName];
Table t = db.Tables[pTableName, pSchemaName];
ScriptingOptions baseOptions = new ScriptingOptions();
baseOptions.NoCollation = true;
baseOptions.SchemaQualify = true;
baseOptions.DriDefaults = true;
baseOptions.IncludeHeaders = false;
baseOptions.DriPrimaryKey = true;
baseOptions.ExtendedProperties = true;
// baseOptions.DriAll = true;
//baseOptions.Indexes = true;
//baseOptions.DriAllKeys = true;
//baseOptions.SchemaQualifyForeignKeysReferences = true;
baseOptions.EnforceScriptingOptions = true;
StringCollection yaziDizisi = t.Script(baseOptions);
return(StringOlustur(yaziDizisi));
}
public string Script(ScriptingOptions so)
{
StringBuilder sbScript = new StringBuilder();
if (so.ScriptAnsiNulls)
{
sbScript.Append("SET ANSI_NULLS ON" + System.Environment.NewLine);
sbScript.Append("GO" + System.Environment.NewLine);
}
if (so.ScriptQuotedIdentifiers)
{
sbScript.Append("SET QUOTED_IDENTIFIER ON" + System.Environment.NewLine);
sbScript.Append("GO" + System.Environment.NewLine);
}
sbScript.Append(Definition.Trim() + System.Environment.NewLine + "GO" + System.Environment.NewLine);
if (so.ScriptAnsiNulls)
{
sbScript.Append("SET ANSI_NULLS OFF" + System.Environment.NewLine);
sbScript.Append("GO" + System.Environment.NewLine);
}
if (so.ScriptQuotedIdentifiers)
{
sbScript.Append("SET QUOTED_IDENTIFIER OFF" + System.Environment.NewLine);
sbScript.Append("GO" + System.Environment.NewLine);
}
return(sbScript.ToString().Trim());
}
static void Main3copy(string[] args)
{
string templateDbName = "MyTestDB";
string templateServer = "localhost";
Server server = new Server(templateServer);
Database templateDb = server.Databases[templateDbName];
//--------方式1-----------
//Scripter scripter = new Scripter(server);
//Urn[] databaseUrns = new Urn[] {templateDb.Urn};
//StringCollection sqlStrs = scripter.Script(databaseUrns);
//Console.WriteLine("begin writeFiles。。。");
//string sqlFilePath = @"D:\sqlScript_DB.sql";
//using (StreamWriter sw = new StreamWriter(sqlFilePath, false, Encoding.UTF8))
//{
// foreach (var sql in sqlStrs)
// {
// sw.WriteLine(sql);
// }
//}
//--------方式1-----------
//--------方式2-----------
ScriptingOptions sOption = new ScriptingOptions();
sOption.DriAll = true;
sOption.ScriptData = true;
sOption.ScriptSchema = true;
sOption.ScriptDrops = false;
int count = 1;
foreach (Table tb in templateDb.Tables)
{
IEnumerable<string> sqlStrs = tb.EnumScript(sOption);
Console.WriteLine("begin writeFiles。。。"+(count++));
string sqlFilePath = @"D:\sqlScript_tb_insert.sql";
using (StreamWriter sw = new StreamWriter(sqlFilePath, true, Encoding.UTF8))
{
foreach (var sql in sqlStrs)
{
sw.WriteLine(sql);
sw.WriteLine("GO");
}
}
}
//--------方式2-----------
Console.WriteLine("end。。。");
Console.ReadKey();
}
static DbTable()
{
scriptingOptions = new ScriptingOptions();
scriptingOptions.AllowSystemObjects = false;
scriptingOptions.IncludeHeaders = false;
scriptingOptions.IncludeIfNotExists = true;
scriptingOptions.DriAll = true;
}
protected virtual IEnumerable<string> GetSmoTableDataScript(Table table)
{
ScriptingOptions so = new ScriptingOptions();
so.ScriptSchema = false;
so.ScriptData = true;
so.ScriptDrops = false;
so.IncludeHeaders = true;
return table.EnumScript(so);
}
public void GenerateTableSript(ServerModel server, ServerModel depserver, List<TableModel> tablelist)
{
//Server srv = new Server(new ServerConnection(ConnectionManager.Connection(server)));
Server srv = new Server();
srv.ConnectionContext.LoginSecure = false;
srv.ConnectionContext.Login = server.Username;
srv.ConnectionContext.Password = server.Password;
srv.ConnectionContext.ServerInstance = server.ServerName;
string dbName = server.Database;
Database db = new Database();
db = srv.Databases[dbName];
StringBuilder sb = new StringBuilder();
List<Table> newtables = new List<Table>();
List<TableModel> modtables = new List<TableModel>();
foreach (var tbl in tablelist)
{
if (!CheckTable(depserver, tbl.TableName))
{
newtables.Add(db.Tables[tbl.TableName]);
}
else
{
modtables.Add(tbl);
}
}
foreach (Table tbl in newtables)
{
ScriptingOptions options = new ScriptingOptions();
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
StringCollection coll = tbl.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
}
System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
fs.Write(sb.ToString());
fs.Close();
}
static void Main2(string[] args)
{
string templateDbName = "MyTestDB";
string templateServer = "localhost";
//string templateDbName = "SemDissectorGlobalManagement";
//string templateServer = "10.200.50.173";
Server server = new Server(templateServer);
Database templateDb = server.Databases[templateDbName];
//--------方式1-----------
//Scripter scripter = new Scripter(server);
//Urn[] databaseUrns = new Urn[] {templateDb.Urn};
//StringCollection sqlStrs = scripter.Script(databaseUrns);
//Console.WriteLine("begin writeFiles。。。");
//string sqlFilePath = @"D:\sqlScript_DB.sql";
//using (StreamWriter sw = new StreamWriter(sqlFilePath, false, Encoding.UTF8))
//{
// foreach (var sql in sqlStrs)
// {
// sw.WriteLine(sql);
// }
//}
//--------方式1-----------
//--------方式2-----------
ScriptingOptions sOption = new ScriptingOptions();
sOption.DriAll = true;
StringCollection sqlStrs = templateDb.Script(sOption);
Console.WriteLine("begin writeFiles。。。");
string sqlFilePath = @"D:\sqlScript_DB2.sql";
using (StreamWriter sw = new StreamWriter(sqlFilePath, false, Encoding.UTF8))
{
foreach (var sql in sqlStrs)
{
sw.WriteLine(sql);
sw.WriteLine("GO");
}
}
//--------方式2-----------
Console.WriteLine("end。。。");
Console.ReadKey();
}
private void ScriptDdlTriggers(bool verbose, Database db, ScriptingOptions so, string outputDirectory)
{
string programmability = Path.Combine(outputDirectory, "Programmability");
// if (!Directory.Exists(programmability)) Directory.CreateDirectory(programmability);
string triggers = Path.Combine(programmability, "Database Triggers");
// if (!Directory.Exists(triggers)) Directory.CreateDirectory(triggers);
foreach (DatabaseDdlTrigger smo in db.Triggers)
{
if ((IncludeSystemObjects || !smo.IsSystemObject) && !smo.IsEncrypted)
{
if (!FilterExists() || MatchesFilter(FilterType.DdlTrigger, smo.Name))
{
using (StreamWriter sw = GetStreamWriter(Path.Combine(triggers, FixUpFileName(smo.Name) + ".sql"), false))
{
if (verbose) Console.Error.WriteLine("[{0}]: [{1}]", db.Name, smo.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(smo.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(smo.Script(so), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
}
}
}
private void ScriptAssemblies(bool verbose, Database db, ScriptingOptions so, string outputDirectory)
{
string programmability = Path.Combine(outputDirectory, "Programmability");
string assemblies = Path.Combine(programmability, "Assemblies");
string dropAssemblies = Path.Combine(assemblies, "Drop");
// if (!Directory.Exists(programmability)) Directory.CreateDirectory(programmability);
// if (!Directory.Exists(assemblies)) Directory.CreateDirectory(assemblies);
// if (!Directory.Exists(dropAssemblies)) Directory.CreateDirectory(dropAssemblies);
foreach (SqlAssembly smo in db.Assemblies)
{
if (!CreateOnly)
{
using (StreamWriter sw = GetStreamWriter(Path.Combine(dropAssemblies, FixUpFileName(smo.Name) + ".DROP.sql"), false))
{
if (verbose) Console.Error.WriteLine("[{0}]: DROP [{1}]", db.Name, smo.Name);
so.ScriptDrops = so.IncludeIfNotExists = true;
//
// need to drop any objects that depend on
// this assembly before dropping the assembly!
//
foreach (UserDefinedFunction ss in db.UserDefinedFunctions)
{
if (ss.AssemblyName == smo.Name)
{
WriteScript(ss.Script(so), sw);
}
}
foreach (StoredProcedure ss in db.StoredProcedures)
{
if (ss.AssemblyName == smo.Name)
{
WriteScript(ss.Script(so), sw);
}
}
foreach (UserDefinedType ss in db.UserDefinedTypes)
{
if (ss.AssemblyName == smo.Name)
{
WriteScript(ss.Script(so), sw);
}
}
WriteScript(smo.Script(so), sw);
}
}
using (StreamWriter sw = GetStreamWriter(Path.Combine(assemblies, FixUpFileName(smo.Name) + ".sql"), false))
{
if (verbose) Console.Error.WriteLine("[{0}]: [{1}]", db.Name, smo.Name);
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(smo.Script(so), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
}
// TODO: maybe pass in the databaseOutputDirectory instead of calculating it in here?
private void GenerateDatabaseScript(Database db, string outputDirectory, bool purgeDirectory,
DataScriptingFormat dataScriptingFormat, bool verbose, bool scriptProperties, Server server)
{
Properties = scriptProperties;
// Output folder
var databaseOutputDirectory = string.Empty;
if (outputDirectory != null)
{
databaseOutputDirectory = Path.Combine(outputDirectory, FixUpFileName(db.Name));
if (Directory.Exists(databaseOutputDirectory))
{
if (purgeDirectory)
{
if (verbose) Console.Error.Write("Purging database directory...");
PurgeDirectory(databaseOutputDirectory, "*.sql");
if (verbose) Console.Error.WriteLine("done.");
}
}
else
{
Directory.CreateDirectory(databaseOutputDirectory);
}
}
var so = new ScriptingOptions
{
Default = true,
DriDefaults = true,
DriUniqueKeys = true,
Bindings = true,
Permissions = Permissions,
NoCollation = NoCollation,
Statistics = Statistics,
IncludeDatabaseContext = IncludeDatabase
};
ScriptTables(verbose, db, so, databaseOutputDirectory, dataScriptingFormat, server);
ScriptDefaults(verbose, db, so, databaseOutputDirectory);
ScriptRules(verbose, db, so, databaseOutputDirectory);
ScriptUddts(verbose, db, so, databaseOutputDirectory);
ScriptUdfs(verbose, db, so, databaseOutputDirectory);
ScriptViews(verbose, db, so, databaseOutputDirectory);
ScriptSprocs(verbose, db, so, databaseOutputDirectory);
if (db.Version >= 9 &&
db.CompatibilityLevel >= CompatibilityLevel.Version90)
{
ScriptUdts(verbose, db, so, databaseOutputDirectory);
ScriptSchemas(verbose, db, so, databaseOutputDirectory);
ScriptDdlTriggers(verbose, db, so, databaseOutputDirectory);
//ScriptAssemblies(verbose, db, so, databaseOutputDirectory);
}
}
private void ScriptViews(bool verbose, Database db, ScriptingOptions so, string outputDirectory)
{
string views = Path.Combine(outputDirectory, "Views");
// if (!Directory.Exists(views)) Directory.CreateDirectory(views);
foreach (View smo in db.Views)
{
if ((IncludeSystemObjects || !smo.IsSystemObject) && !smo.IsEncrypted)
{
if (!FilterExists() || MatchesFilter(FilterType.View, smo.Name))
{
using (StreamWriter sw = GetStreamWriter(Path.Combine(views, GetScriptFileName(smo)), false))
{
if (verbose) Console.Error.WriteLine("[{0}].[{1}].[{2}]", db.Name, smo.Schema, smo.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(smo.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(smo.Script(so), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
if (db.Version >= 8 && db.CompatibilityLevel >= CompatibilityLevel.Version80)
{
ScriptIndexes(smo, verbose, db, so, views);
}
}
}
}
}
private void btnScript_Click(object sender, EventArgs e)
{
// Server srv = new Server(txtServerName.Text);
Server srv;
if (chkUseWindowsAuthentication.Checked)
srv = new Server(txtServerName.Text);
else
srv = new Server(new ServerConnection(txtServerName.Text, txtUsername.Text, txtPassword.Text));
Database db = srv.Databases[ddlDatabases.SelectedItem.ToString()];
string objectType = "";
string objectName = "";
string schema = "";
txtResult.Text = "";
result = new StringBuilder();
dgAvailableObjects.EndEdit();
int count = 0;
int totalObjects = CountChecks();
toolStripProgressBar1.Maximum = totalObjects;
result.EnsureCapacity(totalObjects * 4000);
// Delete the file if it already exists
if (File.Exists(txtSaveLocation.Text))
File.Delete(txtSaveLocation.Text);
ScriptingOptions baseOptions = new ScriptingOptions();
baseOptions.IncludeHeaders = chkIncludeHeaders.Checked;
baseOptions.Indexes = chkIndexes.Checked;
baseOptions.DriAllKeys = chkKeys.Checked;
baseOptions.NoCollation = !chkCollation.Checked;
baseOptions.SchemaQualify = chkSchemaQualifyCreates.Checked;
baseOptions.SchemaQualifyForeignKeysReferences = chkSchemaQualifyFK.Checked;
baseOptions.Permissions = chkPermissions.Checked;
if (rdoOneFile.Checked || rdoOnePerObject.Checked)
{
baseOptions.FileName = txtSaveLocation.Text;
baseOptions.AppendToFile = true;
}
ScriptingOptions dropOptions = new ScriptingOptions();
dropOptions.ScriptDrops = true;
dropOptions.IncludeIfNotExists = chkExistance.Checked;
dropOptions.SchemaQualify = chkSchemaQualifyDrops.Checked;
if (rdoOneFile.Checked || rdoOnePerObject.Checked)
{
dropOptions.FileName = txtSaveLocation.Text;
dropOptions.AppendToFile = true;
}
// process each checked object
foreach (DataGridViewRow r in dgAvailableObjects.Rows)
{
if (r.Cells[0].Value.ToString() == "True")
{
count++;
toolStripProgressBar1.Value = count;
objectType = r.Cells[3].Value.ToString();
objectName = r.Cells[2].Value.ToString();
schema = r.Cells[1].Value.ToString();
string fileName = "";
if (rdoOnePerObject.Checked)
{
if (schema.Length > 0)
{
if (objectType == "ASSEMBLY")
{
fileName = Path.Combine(txtSaveLocation.Text, schema + "." + objectName + ".ASL");
}
if (objectType == "SQL_TRIGGER")
{
fileName = Path.Combine(txtSaveLocation.Text, schema + "." + objectName + ".trg");
}
if (objectType == "USER_TABLE")
{
fileName = Path.Combine(txtSaveLocation.Text, schema + "." + objectName + ".tbl");
}
if (objectType == "SQL_STORED_PROCEDURE")
{
fileName = Path.Combine(txtSaveLocation.Text, schema + "." + objectName + ".prc");
}
if (objectType == "CLR_STORED_PROCEDURE")
{
fileName = Path.Combine(txtSaveLocation.Text, schema + "." + objectName + ".clrprc");
}
if (objectType == "VIEW")
{
fileName = Path.Combine(txtSaveLocation.Text, schema + "." + objectName + ".vwv");
}
if (objectType == "CLR_SCALAR_FUNCTION" || objectType == "CLR_SCALAR_FUNCTION"
|| objectType == "CLR_TABLE_VALUED_FUNCTION" || objectType == "SQL_INLINE_TABLE_VALUED_FUNCTION"
|| objectType == "SQL_TABLE_VALUED_FUNCTION" || objectType == "SQL_SCALAR_FUNCTION" )
{
fileName = Path.Combine(txtSaveLocation.Text, schema + "." + objectName + ".fnc");
}
}
if (!rdoOnePerObject.Checked)
{
fileName = Path.Combine(txtSaveLocation.Text, objectName + ".sql");
}
if (File.Exists(fileName))
File.Delete(fileName);
baseOptions.FileName = fileName;
dropOptions.FileName = fileName;
}
if (schema.Length > 0)
toolStripStatusLabel1.Text = schema + "." + objectName + "...";
else
toolStripStatusLabel1.Text = objectName + "...";
if (objectType == "ASSEMBLY")
{
SqlAssembly a = db.Assemblies[objectName];
if (chkDrop.Checked)
{
AddLines(a.Script(dropOptions));
AddGo();
}
if (chkCreate.Checked)
{
AddLines(a.Script(baseOptions));
AddGo();
}
a = null;
}
//Change
if (objectType == "SQL_TRIGGER")
{
foreach (Table t in db.Tables)
if ( t.Triggers.Contains(objectName))
{
if (chkDrop.Checked)
{
AddLines(t.Triggers[objectName].Script(dropOptions));
AddGo();
}
if (chkCreate.Checked)
{
AddLines(t.Triggers[objectName].Script(baseOptions));
AddGo();
}
}
}
if (objectType == "USER_TABLE" )
{
Table t = db.Tables[objectName, schema];
if (chkDrop.Checked)
{
AddLines(t.Script(dropOptions));
AddGo();
}
if (chkCreate.Checked)
{
AddLines(t.Script(baseOptions));
AddGo();
}
t = null;
}
if (objectType == "SQL_STORED_PROCEDURE" || objectType == "CLR_STORED_PROCEDURE")
{
StoredProcedure sp = db.StoredProcedures[objectName, schema];
if (chkDrop.Checked)
{
AddLines(sp.Script(dropOptions));
AddGo();
}
if (chkCreate.Checked)
{
AddLines(sp.Script(baseOptions));
AddGo();
}
sp = null;
}
if (objectType == "VIEW")
{
Microsoft.SqlServer.Management.Smo.View v = db.Views[objectName, schema];
if (chkDrop.Checked)
{
AddLines(v.Script(dropOptions));
AddGo();
}
if (chkCreate.Checked)
{
AddLines(v.Script(baseOptions));
AddGo();
}
v = null;
}
if (objectType == "CLR_SCALAR_FUNCTION" || objectType == "CLR_SCALAR_FUNCTION"
|| objectType == "CLR_TABLE_VALUED_FUNCTION" || objectType == "SQL_INLINE_TABLE_VALUED_FUNCTION"
|| objectType == "SQL_TABLE_VALUED_FUNCTION" || objectType == "SQL_SCALAR_FUNCTION" )
{
UserDefinedFunction udf = db.UserDefinedFunctions[objectName, schema];
if (chkDrop.Checked)
{
AddLines(udf.Script(dropOptions));
AddGo();
}
if (chkCreate.Checked)
{
AddLines(udf.Script(baseOptions));
AddGo();
}
udf = null;
}
Application.DoEvents();
}
}
txtResult.MaxLength = result.Length + 100;
txtResult.Text = result.ToString();
toolStripStatusLabel1.Text = "Completed";
tabControl1.SelectedTab = tabResult;
Application.DoEvents();
}
private void ScriptTable(bool verbose, Database db, ScriptingOptions so, string tables, Table table, string triggers, string fullTextIndexes, string foreignKeys, string constraints)
{
string fileName = Path.Combine(tables, GetScriptFileName(table));
#region Table Definition
using (StreamWriter sw = GetStreamWriter(fileName, false))
{
if (verbose) Console.Error.WriteLine("[{0}].[{1}].[{2}]", db.Name, table.Schema, table.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(table.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(table.Script(so), sw);
if (Properties)
{
ScriptProperties(table, sw);
}
}
#endregion
#region Triggers
foreach (Trigger smo in table.Triggers)
{
if ((IncludeSystemObjects || !smo.IsSystemObject) && !smo.IsEncrypted)
{
if (!TableOneFile)
fileName = Path.Combine(triggers, GetScriptFileName(table, smo));
using (StreamWriter sw = GetStreamWriter(fileName, TableOneFile))
{
if (verbose) Console.Error.WriteLine("[{0}].[{1}].[{2}]: [{3}]", db.Name, table.Schema, table.Name, smo.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(smo.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(smo.Script(so), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
}
#endregion
ScriptIndexes(table, verbose, db, so, tables);
#region Full Text Indexes
if (table.FullTextIndex != null)
{
if (!TableOneFile)
fileName = Path.Combine(fullTextIndexes, GetScriptFileName(table));
using (StreamWriter sw = GetStreamWriter(fileName, TableOneFile))
{
if (verbose) Console.Error.WriteLine("[{0}].[{1}].[{2}]: full-text index", db.Name, table.Schema, table.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(table.FullTextIndex.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(table.FullTextIndex.Script(so), sw);
}
}
#endregion
#region Foreign Keys
foreach (ForeignKey smo in table.ForeignKeys)
{
if (!TableOneFile)
fileName = Path.Combine(foreignKeys, GetScriptFileName(table, smo));
using (StreamWriter sw = GetStreamWriter(fileName, TableOneFile))
{
if (verbose) Console.Error.WriteLine("[{0}].[{1}].[{2}]: [{3}]", db.Name, table.Schema, table.Name, smo.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
}
WriteScript(smo.Script(), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
#endregion
#region Constraints
foreach (Check smo in table.Checks)
{
if (!TableOneFile)
fileName = Path.Combine(constraints, GetScriptFileName(table, smo));
using (StreamWriter sw = GetStreamWriter(fileName, TableOneFile))
{
if (verbose) Console.Error.WriteLine("[{0}].[{1}].[{2}]: [{3}]", db.Name, table.Schema, table.Name, smo.Name);
WriteScript(smo.Script(), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
#endregion
}
private void ScriptSchemas(bool verbose, Database db, ScriptingOptions so, string outputDirectory)
{
string schemas = Path.Combine(outputDirectory, "Schemas");
// if (!Directory.Exists(schemas)) Directory.CreateDirectory(schemas);
foreach (Schema smo in db.Schemas)
{
if (IncludeSystemObjects || !smo.IsSystemObject)
{
if (!FilterExists() || MatchesFilter(FilterType.Schema, smo.Name))
{
using (StreamWriter sw = GetStreamWriter(Path.Combine(schemas, FixUpFileName(smo.Name) + ".sql"), false))
{
if (verbose) Console.Error.WriteLine("[{0}]: [{1}]", db.Name, smo.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(smo.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(smo.Script(so), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
}
}
}
public void ReadFromDB(Database db, DirectoryInfo dir)
{
this.Database = db;
this.RootDirectory = dir;
//* Stored Procedures
DirectoryInfo procDirectory = RootDirectory.CreateSubdirectory(DatabaseObjectTypes.StoredProcedure.ToString());
foreach (StoredProcedure proc in Database.StoredProcedures)
{
if (!proc.IsSystemObject)
{
FileStream file = File.Create(procDirectory.FullName + "/" + proc.Name + ".sql");
StreamWriter writer = new StreamWriter(file);
ScriptingOptions so = new ScriptingOptions();
so.ScriptBatchTerminator = true;
try
{
foreach (string line in proc.Script(so))
{
//* Kludge, c.f. http://dbaspot.com/sqlserver-programming/421701-smo-scripting-set-commands.html
if (line.Contains("CREATE PROCEDURE")) writer.WriteLine("GO");
writer.WriteLine(line);
}
writer.Flush();
}
finally
{
writer.Close();
}
}
}
//* Functions
DirectoryInfo functionDirectory = RootDirectory.CreateSubdirectory(DatabaseObjectTypes.UserDefinedFunction.ToString());
foreach (UserDefinedFunction function in Database.UserDefinedFunctions)
{
if (!function.IsSystemObject)
{
FileStream file = File.Create(functionDirectory.FullName + "/" + function.Name + ".sql");
StreamWriter writer = new StreamWriter(file);
try
{
foreach (string line in function.Script())
{
//* Kludge, c.f. http://dbaspot.com/sqlserver-programming/421701-smo-scripting-set-commands.html
if (line.Contains("CREATE FUNCTION")) writer.WriteLine("GO");
writer.WriteLine(line);
}
writer.Flush();
}
finally
{
writer.Close();
}
}
}
//* Triggers
DirectoryInfo triggerDirectory = RootDirectory.CreateSubdirectory("Triggers");
foreach (Trigger trigger in Database.Triggers)
{
if (!trigger.IsSystemObject)
{
FileStream file = File.Create(triggerDirectory.FullName + "/" + trigger.Name + ".sql");
StreamWriter writer = new StreamWriter(file);
try
{
foreach (string line in trigger.Script())
{
//* Kludge, c.f. http://dbaspot.com/sqlserver-programming/421701-smo-scripting-set-commands.html
if (line.Contains("CREATE TRIGGER")) writer.WriteLine("GO");
writer.WriteLine(line);
}
writer.Flush();
}
finally
{
writer.Close();
}
}
}
//* Indexes
DirectoryInfo indexesDirectory = RootDirectory.CreateSubdirectory("Indexes");
foreach (Table table in Database.Tables)
{
if (!table.IsSystemObject)
{
foreach (Index index in table.Indexes)
{
FileStream file = File.Create(indexesDirectory.FullName + "/" + index.Name + ".sql");
StreamWriter writer = new StreamWriter(file);
try
{
foreach (string line in index.Script())
{
//* Kludge, c.f. http://dbaspot.com/sqlserver-programming/421701-smo-scripting-set-commands.html
if (line.Contains("CREATE INDEX")) writer.WriteLine("GO");
writer.WriteLine(line);
}
writer.Flush();
}
finally
{
writer.Close();
}
}
}
}
//* Tables
DirectoryInfo tableDir = RootDirectory.CreateSubdirectory("Tables");
foreach (Table table in Database.Tables)
{
StringBuilder script = new StringBuilder();
script.Append("IF NOT EXISTS ")
.Append("(SELECT * FROM SYS.TABLES T JOIN SYS.SCHEMAS S ")
.Append("ON T.SCHEMA_ID = S.SCHEMA_ID ")
.Append("WHERE T.NAME = '" + table.Name + "' AND S.NAME = '" + table.Schema + "')\r\n")
.Append("BEGIN\r\n")
.Append("\tCREATE TABLE [" + table.Name + "] ( \r\n");
bool hasPrimaryKey = false;
foreach (Index index in table.Indexes) if (index.IndexKeyType == IndexKeyType.DriPrimaryKey) hasPrimaryKey = true;
if (hasPrimaryKey)
{
string indexDefinitions = "";
//* CREATE TABLE script will have only the primary key(s)
foreach (Index index in table.Indexes)
{
if (index.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
foreach (IndexedColumn column in index.IndexedColumns)
{
if (!string.IsNullOrEmpty(indexDefinitions)) indexDefinitions += ", \r\n";
indexDefinitions += column.Name + " " + table.Columns[column.Name].DataType.Name;
if (table.Columns[column.Name].DataType.SqlDataType == SqlDataType.VarChar
|| table.Columns[column.Name].DataType.SqlDataType == SqlDataType.NVarChar)
indexDefinitions += " (" + table.Columns[column.Name].DataType.MaximumLength + ")";
indexDefinitions += " NOT NULL";
}
}
}
script.Append("\t" + indexDefinitions + "\r\n");
script.Append("\t)\r\n END \r\n GO");
//* Append the rest of the columns as ALTER TABLE statements
foreach (Column column in table.Columns)
{
script.Append("\r\n \r\n")
.Append("IF NOT EXISTS (SELECT * FROM SYS.COLUMNS WHERE NAME = '" + column.Name + "' AND Object_ID = Object_ID('" + table.Name + "'))")
.Append("\r\n\tALTER TABLE [" + table.Name + "] add [" + column.Name + "] [" + column.DataType.ToString() + "] ")
.Append((column.DataType.SqlDataType == SqlDataType.VarChar || column.DataType.SqlDataType == SqlDataType.NVarChar) ? "(" + column.DataType.MaximumLength.ToString() + ")" : "")
.Append(column.Nullable ? " NULL" : " NOT NULL")
.Append("\r\nGO\r\n");
//* Append alter statement if column type is a varchar
if (column.DataType.SqlDataType == SqlDataType.VarChar || column.DataType.SqlDataType == SqlDataType.NVarChar)
script.Append("\r\nIF (SELECT max_length FROM SYS.COLUMNS WHERE NAME = '" + column.Name + "' AND Object_ID = Object_ID('" + table.Name + "')) <> " + column.DataType.MaximumLength.ToString())
.Append("\r\n\tALTER TABLE [" + table.Name + "] ALTER COLUMN [" + column.Name + "] " + column.DataType.ToString())
.Append("(" + column.DataType.MaximumLength.ToString() + ")")
.Append(column.Nullable ? " NULL" : " NOT NULL");
}
}
else
{
//* there is no primary key so create the table with just the first column, then check for each column and
// add it if it doesn't already exist
script.Append("\t" + table.Columns[0].Name + " " + table.Columns[table.Columns[0].Name].DataType.Name);
if (table.Columns[table.Columns[0].Name].DataType.SqlDataType == SqlDataType.VarChar
|| table.Columns[table.Columns[0].Name].DataType.SqlDataType == SqlDataType.NVarChar)
script.Append(" (" + table.Columns[table.Columns[0].Name].DataType.MaximumLength + ")" + "\r\n");
script.Append("\t)\r\n END \r\n GO");
//* Append the rest of the columns as ALTER TABLE statements
foreach (Column column in table.Columns)
{
script.Append("\r\n \r\n")
.Append("IF NOT EXISTS (SELECT * FROM SYS.COLUMNS WHERE NAME = '" + column.Name + "' AND Object_ID = Object_ID('" + table.Name + "'))")
.Append("\r\n\tALTER TABLE [" + table.Name + "] add [" + column.Name + "] [" + column.DataType.ToString() + "] ")
.Append((column.DataType.SqlDataType == SqlDataType.VarChar || column.DataType.SqlDataType == SqlDataType.NVarChar) ? "(" + column.DataType.MaximumLength.ToString() + ")" : "")
.Append(column.Nullable ? " NULL" : " NOT NULL")
.Append("\r\nGO\r\n");
//* Append alter statement if column type is a varchar
if (column.DataType.SqlDataType == SqlDataType.VarChar || column.DataType.SqlDataType == SqlDataType.NVarChar)
script.Append("\r\nIF (SELECT max_length FROM SYS.COLUMNS WHERE NAME = '" + column.Name + "' AND Object_ID = Object_ID('" + table.Name + "')) <> " + column.DataType.MaximumLength.ToString())
.Append("\r\n\tALTER TABLE [" + table.Name + "] ALTER COLUMN [" + column.Name + "] " + column.DataType.ToString())
.Append("(" + column.DataType.MaximumLength.ToString() + ")")
.Append(column.Nullable ? " NULL" : " NOT NULL");
}
}
FileStream file = File.Create(tableDir.FullName + "/" + table.Name + ".sql");
StreamWriter writer = new StreamWriter(file);
try
{
writer.WriteLine(script);
writer.Flush();
}
finally
{
writer.Close();
}
}
//* Unpack ignore list
List<string> ignoreList = new List<string>();
if (File.Exists(RootDirectory.FullName + "\\Metadata\\IgnoreList.txt"))
{
StreamReader reader = new StreamReader(File.OpenRead(RootDirectory.FullName + "\\Metadata\\IgnoreList.txt"));
string currentLine = reader.ReadLine();
while (currentLine != null)
{
if (!currentLine.StartsWith("#"))
ignoreList.Add(currentLine);
currentLine = reader.ReadLine();
}
}
//* Table contents
DirectoryInfo tableContentsDir = RootDirectory.CreateSubdirectory("TableContents");
foreach (Table table in Database.Tables)
{
if (!ignoreList.Contains(table.Name))
{
//* Declare table variable
string script = "";
if (table.RowCount > 0)
{
foreach (Column column in table.Columns)
{
if (string.IsNullOrEmpty(script)) script += "DECLARE @TABLE TABLE("; else script += ",\r\n";
script += "\t[" + column.Name + "] " + column.DataType;
if (column.DataType.SqlDataType == SqlDataType.VarChar || column.DataType.SqlDataType == SqlDataType.NVarChar) script += "(" + column.DataType.MaximumLength.ToString() + ")";
}
script += "\r\n)\r\n";
script += "INSERT INTO @TABLE \r\n";
string columnList = "";
foreach (Column column in table.Columns)
{
if (!string.IsNullOrEmpty(columnList)) columnList += ", ";
columnList += "[" + column.Name + "]";
}
//* Get table contents and append to script
DataSet dataset = Database.ExecuteWithResults("select " + columnList + " from [" + table.Name + "]");
foreach (DataRow row in dataset.Tables[0].Rows)
{
string rowData = "";
foreach (Column column in table.Columns)
{
if (string.IsNullOrEmpty(rowData)) rowData += "\r\n SELECT "; else rowData += ", ";
if (column.DataType.SqlDataType == SqlDataType.VarChar
|| column.DataType.SqlDataType == SqlDataType.NVarChar
|| column.DataType.SqlDataType == SqlDataType.UniqueIdentifier)
rowData += "'";
if (column.DataType.SqlDataType == SqlDataType.Bit)
{
if (bool.Parse(row[column.Name].ToString()))
rowData += "1";
else
rowData += "0";
}
else
rowData += row[column.Name].ToString();
if (column.DataType.SqlDataType == SqlDataType.VarChar
|| column.DataType.SqlDataType == SqlDataType.NVarChar
|| column.DataType.SqlDataType == SqlDataType.UniqueIdentifier)
rowData += "'";
}
script += rowData + "\r\n";
//* Append UNION ALL as long as this is not the last row
if (dataset.Tables[0].Rows.IndexOf(row) != dataset.Tables[0].Rows.Count - 1) script += "\r\n UNION ALL";
}
script += "\r\n\r\n";
script += "MERGE [" + table.Name + "] as target\r\n";
script += "USING @Table as source\r\n";
script += "ON ";
//* If there is a primary key, use it; if not, just make sure the record is there.
string onClause = "";
foreach (Index index in table.Indexes)
{
if (index.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
foreach (IndexedColumn column in index.IndexedColumns)
{
if (!string.IsNullOrEmpty(onClause)) onClause += " and ";
onClause += "target.[" + column.Name + "] = source.[" + column.Name + "]";
}
}
}
//* If there is no primary key attempt to match ALL columns
// NB: of course if we go this route and there is a match, it won't do anything since the row already
// exists. This will be in place only if there is no match, so we can ensure that the
// record is created.
if (string.IsNullOrEmpty(onClause))
{
foreach (Column column in table.Columns)
{
if (!string.IsNullOrEmpty(onClause)) onClause += ", ";
onClause += "target.[" + column.Name + "] = source.[" + column.Name + "]";
}
}
script += onClause + "\r\n";
script += "WHEN MATCHED THEN UPDATE SET\r\n";
string matchedClause = "";
foreach (Column column in table.Columns)
{
if (!string.IsNullOrEmpty(matchedClause)) matchedClause += ", \r\n";
matchedClause += "target.[" + column.Name + "] = source.[" + column.Name + "]";
}
script += matchedClause + "\r\n";
script += "WHEN NOT MATCHED BY target THEN\r\n";
script += "INSERT (";
string notMatchedTargetClause = "";
string notMatchedTargetClauseValues = "";
foreach (Column column in table.Columns)
{
if (!string.IsNullOrEmpty(notMatchedTargetClause)) notMatchedTargetClause += ", ";
notMatchedTargetClause += column.Name;
if (!string.IsNullOrEmpty(notMatchedTargetClauseValues)) notMatchedTargetClauseValues += ", ";
notMatchedTargetClauseValues += "source.[" + column.Name + "]";
}
script += notMatchedTargetClause + ") \r\n";
script += "VALUES (" + notMatchedTargetClauseValues + ")";
//* TODO: Only delete if the Cleanup flag is set to true; cleanup flag doesn't exist yet...
// script += "WHEN NOT MATCHED BY SOURCE THEN DELETE";
//* Merge statements must always end with a semicolon
script += ";";
}
else
{
//* TODO: If the Cleanup option is specified, delete all rows from table
script = "-- This table has no records";
}
FileStream file = File.Create(tableContentsDir.FullName + "/" + table.Name + ".sql");
StreamWriter writer = new StreamWriter(file);
try
{
writer.WriteLine(script);
writer.Flush();
}
finally
{
writer.Close();
}
}
else
{
//* this table is on the ignore list; create the file but only add a comment
StreamWriter writer = File.CreateText(tableContentsDir.FullName + "/" + table.Name + ".sql");
try
{
writer.WriteLine("--* This table was on the ignore list and therefore was not scripted.");
writer.Flush();
}
finally
{
writer.Close();
}
}
}
//* Table Dependancy Map
DirectoryInfo metadataDir = RootDirectory.CreateSubdirectory("Metadata");
XmlWriter tableXmlWriter = XmlWriter.Create(File.Create(metadataDir.FullName + "/TableDependancyMap.xml"));
XmlDocument tableDependancyMap = GetTableDependancyMap();
try
{
tableDependancyMap.WriteTo(tableXmlWriter);
tableXmlWriter.Flush();
}
finally
{
tableXmlWriter.Close();
}
//* Dependancy Map
XmlWriter xmlWriter = XmlWriter.Create(File.Create(metadataDir.FullName + "/DependancyMap.xml"));
XmlDocument dependancyMap = GetDependancyMap();
try
{
dependancyMap.WriteTo(xmlWriter);
xmlWriter.Flush();
}
finally
{
xmlWriter.Close();
}
}
/// <summary>
/// Dump views to stream.
/// </summary>
/// <param name="database">Database to write views for</param>
private void dumpViews(Database database)
{
foreach (View view in database.Views) {
if (view.IsSystemObject)
continue;
ScriptingOptions options = new ScriptingOptions();
// Do not verify the existance of objects when including them
// in the script.
options.IncludeIfNotExists = true;
StringCollection script = view.Script(options);
writer.write(script);
}
}
private IEnumerable<string> ScriptDatabase(Database database)
{
ScriptingOptions options = new ScriptingOptions();
options.DriAll = true;
options.ClusteredIndexes = true;
options.Default = true;
options.Indexes = true;
options.IncludeHeaders = true;
options.DriDefaults = true;
options.IncludeHeaders = false;
//options.FileName = Path.GetTempFileName();
//Console.WriteLine("Scripted database to: {0}", options.FileName);
Scripter scripter = new Scripter(ServerSmo) { Options = options };
return scripter.EnumScript(database.Tables.OfType<Table>().ToArray())
.Concat(scripter.EnumScript(database.UserDefinedTableTypes.OfType<UserDefinedTableType>().ToArray()));
}
string RecreateObject(IScriptable scriptableObject, ScriptingOptions sCO)
{
StringBuilder sb = new StringBuilder();
// Generate Drop script
ScriptingOptions so = new ScriptingOptions();
so.ScriptDrops = true;
so.IncludeIfNotExists = true;
foreach (string stmt in scriptableObject.Script(so))
{
sb.AppendLine(stmt);
}
// Add batch separator
sb.AppendLine("GO");
// Now generate Crate script and add it to the resulting ScriptCollection
sCO.ScriptDrops = false;
sCO.IncludeIfNotExists = false;
foreach (string stmt in scriptableObject.Script(sCO))
{
sb.AppendLine(stmt);
if (stmt.Contains("SET ANSI_NULLS") || stmt.Contains("SET QUOTED_IDENTIFIER"))
sb.AppendLine("GO");
}
//for (int i = 0; i < sc.Count; i++)
// sb.Append(sc[i]);
return sb.ToString();
}
/// <summary>
/// Process a database to create SVN files using all of the existing settings
/// </summary>
public void Process()
{
// CommitChangesToSubversion(pSVNDirectory);
// Make sure the svn directory end with a \
if (!pSVNDirectory.EndsWith(@"\"))
pSVNDirectory = pSVNDirectory + @"\";
// Get a sql server instance
//SQLServer srv = new SQLServerClass();
ServerConnection conn = new ServerConnection();
Server srv = null;
ScriptingOptions scriptO = new ScriptingOptions();
scriptO.IncludeHeaders = false;
scriptO.ToFileOnly = false;
try
{
// Determine if we need to use a trusted connection
if (pUserLogin.Trim() == string.Empty && pPassword.Trim() == string.Empty)
{
conn.LoginSecure = true;
// srv.Connect(pSQLServer, null, null);
}
else
{
conn.LoginSecure = false;
conn.Login = pUserLogin;
conn.Password = pPassword;
//Server svr = new Server(conn);
//Console.WriteLine(svr.Name + " " + svr.Information.VersionString);
}
conn.ServerInstance = pSQLServer;
//srv.Connect(pSQLServer, pUserLogin, pPassword);
srv = new Server(conn);
// Get a 2005 database instance
//Database2 db = (Database2)srv.Databases.Item(pDatabaseName, "dbo");
Database db = srv.Databases[pDatabaseName];
//If we are processing stored procedures
if (pStoredProcedures)
{
string directoryForOutput = string.Format(@"{0}{1}", SVNDirectory, "procedure");
// Verify output directory exists and create if it doesn't
if (!System.IO.Directory.Exists(directoryForOutput))
System.IO.Directory.CreateDirectory(directoryForOutput);
// Loop through all of the procedures
foreach (StoredProcedure sp in db.StoredProcedures)
{
if (!sp.IsSystemObject) // If it's not a system object
{
// Inform the calling program
NotifyCaller(string.Format("Processing procedure {0}.{1}", sp.Schema, sp.Name));
// Define the file name
string fileName = string.Format(@"{0}\{1}.{2}.sql", directoryForOutput, sp.Schema, sp.Name, "procedure");
// generate the script to a string
//StringCollection scriptOutput = sp.Script(scriptO);
//string scriptOutput = sp.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops
// | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default, null, SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);
// Attempt to write
WriteData(fileName, RecreateObject(sp, scriptO));
}
}
// Now determine if we need to delete any files
foreach (string checkFileName in System.IO.Directory.GetFiles(directoryForOutput))
{
//srv.Databases["Galaxy4"].StoredProcedures.Contains("AddNewDomain");
string[] FileChecker = checkFileName.Replace(directoryForOutput, "").Replace(@"\", "").Split(".".ToCharArray());
NotifyCaller(string.Format("Verifying {0}.{1}", FileChecker[0], FileChecker[1]));
if (!db.StoredProcedures.Contains(FileChecker[1], FileChecker[0]))
System.IO.File.Delete(checkFileName);
}
}
// If we are processing tables
if (pTables)
{
string directoryForOutput = string.Format(@"{0}{1}", SVNDirectory, "table");
// Verify output directory exists and create if it doesn't
if (!System.IO.Directory.Exists(directoryForOutput))
System.IO.Directory.CreateDirectory(directoryForOutput);
// Loop through all the tables
foreach (Table tb in db.Tables)
{
if (!tb.IsSystemObject) // If it's not a system object
{
// Inform the calling program
NotifyCaller(string.Format("Processing table {0}.{1}", tb.Schema, tb.Name));
//// Process Indices
//foreach (SQLDMO.Index2 idx in tb.Indexes)
//{
// NotifyCaller(string.Format("Processing table index {0}.{1}.{2}", tb.Schema, tb.Name, idx.Name));
// string idxFileName = string.Format(@"{0}\{1}.{2}.{3}.sql", directoryForOutput, tb.Schema, tb.Name, idx.Name);
// string idxScriptOutput = "";
// idxScriptOutput = idx.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops
// | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default
// , null, SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default
// );
// WriteData(idxFileName, idxScriptOutput);
//}
// Define the file name
string fileName = string.Format(@"{0}\{1}.{2}.sql", directoryForOutput, tb.Schema, tb.Name);
// Which script we generate depends on whether we are processing full text indexing or not
scriptO.DriAll = true;
scriptO.DriIndexes = true;
scriptO.FullTextCatalogs = tb.FullTextIndex != null;
scriptO.FullTextIndexes = tb.FullTextIndex != null;
scriptO.NonClusteredIndexes = true;
scriptO.ClusteredIndexes = true;
scriptO.Indexes = true;
// Attempt to write
WriteData(fileName, RecreateObject(tb, scriptO));
}
}
// Now determine if we need to delete any files
foreach (string checkFileName in System.IO.Directory.GetFiles(directoryForOutput))
{
string[] FileChecker = checkFileName.Replace(directoryForOutput, "").Replace(@"\", "").Split(".".ToCharArray());
NotifyCaller(string.Format("Verifying {0}.{1}", FileChecker[0], FileChecker[1]));
if (!db.Tables.Contains(FileChecker[1], FileChecker[0]))
System.IO.File.Delete(checkFileName);
}
}
//If we are processing user defined functions
if (pFunctions)
{
string directoryForOutput = string.Format(@"{0}{1}", SVNDirectory, "user-defined-function");
// Verify output directory exists and create if it doesn't
if (!System.IO.Directory.Exists(directoryForOutput))
System.IO.Directory.CreateDirectory(directoryForOutput);
// Loop through all of the user defined functions
foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
{
if (!udf.IsSystemObject) // If it's not a system object
{
// Inform the calling program
NotifyCaller(string.Format("Processing user-defined-function {0}.{1}", udf.Schema, udf.Name));
// Define the file name
string fileName = string.Format(@"{0}\{1}.{2}.sql", directoryForOutput, udf.Schema, udf.Name);
// generate the script to a string
// Attempt to write
WriteData(fileName, RecreateObject(udf, scriptO));
}
}
// Now determine if we need to delete any files
foreach (string checkFileName in System.IO.Directory.GetFiles(directoryForOutput))
{
string[] FileChecker = checkFileName.Replace(directoryForOutput, "").Replace(@"\", "").Split(".".ToCharArray());
NotifyCaller(string.Format("Verifying {0}.{1}", FileChecker[0], FileChecker[1]));
if (!db.UserDefinedFunctions.Contains(FileChecker[1], FileChecker[0]))
System.IO.File.Delete(checkFileName);
}
}
//If we are processing Full Text Indices
if (pFullTextIndices)
{
string directoryForOutput = string.Format(@"{0}{1}", SVNDirectory, "full-text-catalog");
// Verify output directory exists and create if it doesn't
if (!System.IO.Directory.Exists(directoryForOutput))
System.IO.Directory.CreateDirectory(directoryForOutput);
// Loop through all of the Catalogs
foreach (FullTextCatalog ftc in db.FullTextCatalogs)
{
// Inform the calling program
NotifyCaller(string.Format("Processing Full Text Catalog {0}", ftc.Name));
// Define the file name
string fileName = string.Format(@"{0}\{1}.sql", directoryForOutput, ftc.Name);
scriptO.FullTextIndexes = true;
scriptO.FullTextCatalogs = true;
// generate the script to a string
WriteData(fileName, RecreateObject(ftc, scriptO));
}
// Now determine if we need to delete any files
foreach (string checkFileName in System.IO.Directory.GetFiles(directoryForOutput))
{
string[] FileChecker = checkFileName.Replace(directoryForOutput, "").Replace(@"\", "").Split(".".ToCharArray());
NotifyCaller(string.Format("Verifying {0}.{1}", FileChecker[0], FileChecker[1]));
if (!db.FullTextCatalogs.Contains(FileChecker[0]))
System.IO.File.Delete(checkFileName);
}
}
//If we are processing views
if (pViews)
{
string directoryForOutput = string.Format(@"{0}{1}", SVNDirectory, "view");
// Verify output directory exists
if (!System.IO.Directory.Exists(directoryForOutput))
System.IO.Directory.CreateDirectory(directoryForOutput);
// Loop through all of the views
foreach (View vw in db.Views)
{
if (!vw.IsSystemObject) // If it's not a system object
{
// Inform the calling program
NotifyCaller(string.Format("Processing view {0}.{1}", vw.Schema, vw.Name));
// Define the file name
string fileName = string.Format(@"{0}\{1}.{2}.sql", directoryForOutput, vw.Schema, vw.Name);
// generate the script to a string
// Attempt to write
WriteData(fileName, RecreateObject(vw, scriptO));
}
}
// Now determine if we need to delete any files
foreach (string checkFileName in System.IO.Directory.GetFiles(directoryForOutput))
{
string[] FileChecker = checkFileName.Replace(directoryForOutput, "").Replace(@"\", "").Split(".".ToCharArray());
NotifyCaller(string.Format("Verifying {0}.{1}", FileChecker[0], FileChecker[1]));
if (!db.Views.Contains(FileChecker[1], FileChecker[0]))
System.IO.File.Delete(checkFileName);
}
}
//If we are processing User Defined Data Types
if (pUDDT)
{
string directoryForOutput = string.Format(@"{0}{1}", SVNDirectory, "user-defined-data-type");
// Verify output directory exists
if (!System.IO.Directory.Exists(directoryForOutput))
System.IO.Directory.CreateDirectory(directoryForOutput);
// Loop through all of the views
foreach (UserDefinedDataType ud in db.UserDefinedDataTypes)
{
// Inform the calling program
NotifyCaller(string.Format("Processing user defined data type {0}.{1}", ud.Schema, ud.Name));
// Define the file name
string fileName = string.Format(@"{0}\{1}.{2}.sql", directoryForOutput, ud.Schema, ud.Name);
// generate the script to a string
// Attempt to write
WriteData(fileName, RecreateObject(ud, scriptO));
}
// Now determine if we need to delete any files
foreach (string checkFileName in System.IO.Directory.GetFiles(directoryForOutput))
{
string[] FileChecker = checkFileName.Replace(directoryForOutput, "").Replace(@"\", "").Split(".".ToCharArray());
NotifyCaller(string.Format("Verifying {0}.{1}", FileChecker[0], FileChecker[1]));
if (!db.UserDefinedTypes.Contains(FileChecker[1], FileChecker[0]))
System.IO.File.Delete(checkFileName);
}
}
/*
//If we are processing Service Brokers
if (pServiceBroker )
{
string directoryForOutput = string.Format(@"{0}{1}", SVNDirectory, "service-broker");
// Verify output directory exists
if (!System.IO.Directory.Exists(directoryForOutput))
System.IO.Directory.CreateDirectory(directoryForOutput);
// Microsoft.SqlServer.Management.Smo.Broker.ServiceQueue
Microsoft.SqlServer.Management.Smo.Broker.ServiceBroker sb = new Microsoft.SqlServer.Management.Smo.Broker.ServiceBroker();
//Start with Message Types
directoryForOutput = string.Format(@"{0}{1}", SVNDirectory, @"service-broker\message-types");
if (!System.IO.Directory.Exists(directoryForOutput))
System.IO.Directory.CreateDirectory(directoryForOutput);
// Loop through all of the views
foreach (UserDefinedDataType ud in db.UserDefinedDataTypes)
{
// Inform the calling program
NotifyCaller(string.Format("Processing user defined data type {0}.{1}", ud.Schema, ud.Name));
// Define the file name
string fileName = string.Format(@"{0}\{1}.{2}.sql", directoryForOutput, ud.Schema, ud.Name);
// generate the script to a string
// Attempt to write
WriteData(fileName, RecreateObject(ud, scriptO));
}
// Now determine if we need to delete any files
foreach (string checkFileName in System.IO.Directory.GetFiles(directoryForOutput))
{
string[] FileChecker = checkFileName.Replace(directoryForOutput, "").Replace(@"\", "").Split(".".ToCharArray());
NotifyCaller(string.Format("Verifying {0}.{1}", FileChecker[0], FileChecker[1]));
if (!db.UserDefinedTypes.Contains(FileChecker[1], FileChecker[0]))
System.IO.File.Delete(checkFileName);
}
}
*/
}
catch (Exception ex)
{
NotifyCaller(ex.ToString());
}
// Subversion
try { CommitChangesToSubversion(pSVNDirectory); NotifyCaller("Commited Changes"); }
catch (Exception ex) { NotifyCaller(ex.ToString()); }
}
// Scripts an item in right window if it is scriptable
private void DependenciesTreeView_AfterSelect(object sender, TreeViewEventArgs e)
{
this.ScriptTextBox.Clear();
if (e.Node.Tag == null)
{
return;
}
// Script an object
SqlSmoObject o = this.sqlServerSelection.GetSmoObject((Urn)e.Node.Tag);
ScriptingOptions so = new ScriptingOptions();
so.DriAll = true;
so.Indexes = true;
so.IncludeHeaders = true;
so.Permissions = true;
so.PrimaryObject = true;
so.SchemaQualify = true;
so.Triggers = true;
IScriptable scriptableObject = o as IScriptable;
if (scriptableObject != null)
{
StringBuilder sb = new StringBuilder();
foreach (string s in scriptableObject.Script(so))
{
sb.Append(s);
sb.Append(Environment.NewLine);
}
this.ScriptTextBox.Text = sb.ToString();
}
}
private void ScriptIndexes(TableViewBase tableOrView, bool verbose, Database db, ScriptingOptions so, string tablesOrViewsOutputDirectory)
{
var indexes = Path.Combine(tablesOrViewsOutputDirectory, "Indexes");
var primaryKeys = Path.Combine(tablesOrViewsOutputDirectory, "PrimaryKeys");
var uniqueKeys = Path.Combine(tablesOrViewsOutputDirectory, "UniqueKeys");
var fileName = Path.Combine(tablesOrViewsOutputDirectory, GetScriptFileName(tableOrView));
foreach (Index smo in tableOrView.Indexes)
{
if (IncludeSystemObjects || !smo.IsSystemObject)
{
string dir =
(smo.IndexKeyType == IndexKeyType.DriPrimaryKey) ? primaryKeys :
(smo.IndexKeyType == IndexKeyType.DriUniqueKey) ? uniqueKeys : indexes;
if (!TableOneFile)
fileName = Path.Combine(dir, GetScriptFileName(tableOrView, smo));
using (StreamWriter sw = GetStreamWriter(fileName, TableOneFile))
{
if (verbose) Console.Error.WriteLine("[{0}].[{1}].[{2}]: [{3}]", db.Name, tableOrView.Schema, tableOrView.Name, smo.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(smo.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(smo.Script(so), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
}
}
public string GenerateTableData(ServerModel server, DataGenType gentype, List<TableModel> tables)
{
try
{
var output = new StringBuilder();
if (gentype == DataGenType.Truncate)
{
foreach (var t in tables)
{
var scpt = GetDataHeaderQuery(gentype, t.TableName);
output.AppendLine(scpt);
}
}
else
{
Server srv = new Server();
srv.ConnectionContext.LoginSecure = false;
srv.ConnectionContext.Login = server.Username;
srv.ConnectionContext.Password = server.Password;
srv.ConnectionContext.ServerInstance = server.ServerName;
Database genDb = srv.Databases[server.Database];
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptData = true;
scriptOptions.ScriptSchema = false;
Scripter scripter = new Scripter(srv) { Options = scriptOptions };
foreach (var t in tables)
{
var gen = new StringBuilder();
var tbl = genDb.Tables[t.TableName, "dbo"];
var script = scripter.EnumScript(new SqlSmoObject[] { tbl });
foreach (var line in script)
gen.AppendLine(line);
var scpt = GetDataHeaderQuery(gentype, t.TableName);
scpt = scpt.Replace("{query}", gen.ToString());
output.AppendLine(scpt);
}
}
return output.ToString();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private void ScriptRules(bool verbose, Database db, ScriptingOptions so, string outputDirectory)
{
string programmability = Path.Combine(outputDirectory, "Programmability");
string rules = Path.Combine(programmability, "Rules");
//if (!Directory.Exists(programmability)) Directory.CreateDirectory(programmability);
//if (!Directory.Exists(rules)) Directory.CreateDirectory(rules);
foreach (Rule smo in db.Rules)
{
if (!FilterExists() || MatchesFilter(FilterType.Rule, smo.Name))
{
using (StreamWriter sw = GetStreamWriter(Path.Combine(rules, GetScriptFileName(smo)), false))
{
if (verbose) Console.Error.WriteLine("[{0}].[{1}].[{2}]", db.Name, smo.Schema, smo.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(smo.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(smo.Script(so), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
}
}
private void ScriptTables(bool verbose, Database db, ScriptingOptions so, string outputDirectory, DataScriptingFormat dataScriptingFormat, Server server)
{
string data = Path.Combine(outputDirectory, "Data");
string tables = Path.Combine(outputDirectory, "Tables");
string constraints = Path.Combine(tables, "Constraints");
string foreignKeys = Path.Combine(tables, "ForeignKeys");
string fullTextIndexes = Path.Combine(tables, "FullTextIndexes");
string triggers = Path.Combine(tables, "Triggers");
foreach (Table table in db.Tables)
{
if (IncludeSystemObjects || !table.IsSystemObject)
{
if (!FilterExists() || MatchesFilter(FilterType.Table, table.Name))
{
ScriptTable(verbose, db, so, tables, table, triggers, fullTextIndexes, foreignKeys, constraints);
}
#region Script Data
if (MatchesTableDataFilters(db.Name, table.Name))
{
ScriptTableData(db, table, verbose, data, dataScriptingFormat, server);
}
#endregion
}
}
}
private void ScriptSprocs(bool verbose, Database db, ScriptingOptions so, string outputDirectory)
{
string programmability = Path.Combine(outputDirectory, "Programmability");
string sprocs = Path.Combine(programmability, "StoredProcedures");
// if (!Directory.Exists(programmability)) Directory.CreateDirectory(programmability);
// if (!Directory.Exists(sprocs)) Directory.CreateDirectory(sprocs);
foreach (StoredProcedure smo in db.StoredProcedures)
{
if ((IncludeSystemObjects || !smo.IsSystemObject) && !smo.IsEncrypted)
{
if (!FilterExists() || MatchesFilter(FilterType.StoredProcedure, smo.Name))
{
using (StreamWriter sw = GetStreamWriter(Path.Combine(sprocs, GetScriptFileName(smo)), false))
{
if (verbose) Console.Error.WriteLine("[{0}].[{1}].[{2}]", db.Name, smo.Schema, smo.Name);
if (ScriptAsCreate)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(smo.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
if (ScriptAsCreate)
{
WriteScript(smo.Script(so), sw);
}
else
{
WriteScript(smo.Script(so), sw, "CREATE PROC", "ALTER PROC");
}
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
}
}
}
private void ScriptUdfs(bool verbose, Database db, ScriptingOptions so, string outputDirectory)
{
string programmability = Path.Combine(outputDirectory, "Programmability");
string udfs = Path.Combine(programmability, "Functions");
//if (!Directory.Exists(programmability)) Directory.CreateDirectory(programmability);
//if (!Directory.Exists(udfs)) Directory.CreateDirectory(udfs);
foreach (UserDefinedFunction smo in db.UserDefinedFunctions)
{
if ((IncludeSystemObjects || !smo.IsSystemObject) && !smo.IsEncrypted)
{
if (!FilterExists() || MatchesFilter(FilterType.UserDefinedFunction, smo.Name))
{
using (StreamWriter sw = GetStreamWriter(Path.Combine(udfs, GetScriptFileName(smo)), false))
{
if (verbose) Console.Error.WriteLine("[{0}].[{1}].[{2}]", db.Name, smo.Schema, smo.Name);
if (!CreateOnly)
{
so.ScriptDrops = so.IncludeIfNotExists = true;
WriteScript(smo.Script(so), sw);
}
so.ScriptDrops = so.IncludeIfNotExists = false;
WriteScript(smo.Script(so), sw);
if (Properties)
{
ScriptProperties(smo, sw);
}
}
}
}
}
}
private void Script()
{
if (!this.VerifyDatabase())
{
return;
}
if (this.OutputFilePath == null)
{
this.Log.LogError("OutputFilePath is required");
return;
}
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting Database: {0} to: {1}", this.DatabaseItem.ItemSpec, this.OutputFilePath.GetMetadata("FullPath")));
Microsoft.SqlServer.Management.Smo.Database db = this.sqlServer.Databases[this.DatabaseItem.ItemSpec];
// Script the database
ScriptingOptions opt = new ScriptingOptions { Bindings = true, ClusteredIndexes = true, ExtendedProperties = true, FullTextCatalogs = true, FullTextIndexes = true, IncludeDatabaseContext = true, IncludeDatabaseRoleMemberships = true, IncludeHeaders = true, Indexes = true, LoginSid = true, Permissions = true, Triggers = true, XmlIndexes = true };
opt.IncludeHeaders = false;
opt.ToFileOnly = true;
opt.NoCollation = false;
opt.FileName = this.OutputFilePath.GetMetadata("FullPath");
db.Script(opt);
// now we append to file
opt.AppendToFile = true;
foreach (Login o in this.sqlServer.Logins)
{
if (!o.IsSystemObject)
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting Login: {0}", o.Name));
o.Script(opt);
}
}
foreach (Table o in db.Tables)
{
if (!o.IsSystemObject)
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting Table: {0}", o.Name));
o.Script(opt);
}
}
foreach (Rule o in db.Rules)
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting Rule: {0}", o.Name));
o.Script(opt);
}
foreach (Default o in db.Defaults)
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting Default: {0}", o.Name));
o.Script(opt);
}
foreach (StoredProcedure o in db.StoredProcedures)
{
if (!o.IsSystemObject)
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting StoredProcedure: {0}", o.Name));
o.Script(opt);
}
}
foreach (View o in db.Views)
{
if (!o.IsSystemObject)
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting View: {0}", o.Name));
o.Script(opt);
}
}
}
private void bwCalculation_DoWork(object sender, DoWorkEventArgs e)
{
_workCompletedWithoutError = false;
var conn = new ServerConnection(ConnectionManager.GetInstance().GetSqlConnection());
//myLog.WriteEntry("--connection--"+conn.ToString());
try
{
_sbScript = new StringBuilder();
var server = new Server(conn);
//myLog.WriteEntry("--server initalized--" + server.ToString());
var db = server.Databases[AppSettings.DatabaseName];
//myLog.WriteEntry("--get db--" + db.ToString());
//myLog.WriteEntry("--sp contain check--" + db.StoredProcedures.Contains("sp_generate_inserts").ToString());
if (!db.StoredProcedures.Contains("sp_generate_inserts"))
{
// myLog.WriteEntry("--get into spgenerate and call SpGenerate--" );
try
{
db.ExecuteNonQuery(SpGenerate());
}
catch(Exception ex)
{
//myLog.WriteEntry("--error on excute SpGenerate()--"+ex.Message+"--trace--"+ex.StackTrace+"--spgenerate out--"+SpGenerate());
}
}
var scripter = new Scripter(server);
//myLog.WriteEntry("--scripter initalized--" + scripter.ToString());
scripter.ScriptingProgress += new ProgressReportEventHandler(ScriptingProgressEventHandler);
var so = new ScriptingOptions
{
IncludeIfNotExists = false,
IncludeHeaders = false,
Permissions = false,
ExtendedProperties = false,
ScriptDrops = false,
IncludeDatabaseContext = false,
NoCollation = false,
NoFileGroup = false,
NoIdentities = false,
TargetServerVersion = SqlServerVersion.Version90
};
scripter.Options = so;
SetProgressBarText(1);
SetProgressBarValue(0);
//myLog.WriteEntry("--scripter options set--" + scripter.Options.ToString());
this.Invoke(new MethodInvoker(() => progressBar1.Maximum = db.Tables.Count));
server.SetDefaultInitFields(typeof (Table), "IsSystemObject");
//myLog.WriteEntry("--init start looping tables--");
foreach (Table tb in db.Tables)
{
//myLog.WriteEntry("--start looping tables--" + tb.Name);
ProgressBarPerformStep();
if (!tb.IsSystemObject)
{
_sbScript.Append(ScriptObject(new Urn[] {tb.Urn}, scripter));
}
}
SetProgressBarValue(0);
SetProgressBarText(2);
this.Invoke(new MethodInvoker(() => progressBar1.Maximum = db.UserDefinedFunctions.Count));
server.SetDefaultInitFields(typeof (UserDefinedFunction), "IsSystemObject");
foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
{
ProgressBarPerformStep();
if (!udf.IsSystemObject)
{
_sbScript.Append(ScriptObject(new Urn[] {udf.Urn}, scripter));
}
}
SetProgressBarValue(0);
SetProgressBarText(3);
this.Invoke(new MethodInvoker(() => progressBar1.Maximum = db.StoredProcedures.Count));
server.SetDefaultInitFields(typeof (StoredProcedure), "IsSystemObject");
foreach (StoredProcedure sp in db.StoredProcedures)
{
ProgressBarPerformStep();
if (!sp.IsSystemObject)
{
_sbScript.Append(ScriptObject(new Urn[] {sp.Urn}, scripter));
}
}
SetProgressBarValue(0);
SetProgressBarText(4);
this.Invoke(new MethodInvoker(delegate
{
lblProgress.Text = "Script Views";
}));
server.SetDefaultInitFields(typeof (Microsoft.SqlServer.Management.Smo.View), "IsSystemObject");
List<SqlSmoObject> views = db.Views.Cast<View>().Cast<SqlSmoObject>().ToList();
var sviews = SortViews(db, views);
this.Invoke(new MethodInvoker(() => progressBar1.Maximum = sviews.Count));
foreach (var v in sviews)
{
ProgressBarPerformStep();
if (!v.IsSystemObject)
{
_sbScript.Append(ScriptObject(new Urn[] {v.Urn}, scripter));
}
}
SetProgressBarText(5);
this.Invoke(new MethodInvoker(() => lblProgress.Text = "Execute Generate Insert Data"));
GenereateData(db, scripter, _sbScript);
_workCompletedWithoutError = true;
}
catch (Exception err)
{
//myLog.WriteEntry("--/ /--" + err.ToString());
MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Disconnect();
}
}
