private static string RewriteWithSimpleUniNotation(string command, DatabaseEngineType dbtype)
{
var paramchar = GetPrefixOfOuterParameter(dbtype);
var realcommand = command;
if (realcommand.Contains("~"))
{
realcommand = command.Replace("~", paramchar.ToString());
}
switch (dbtype)
{
case DatabaseEngineType.Postgres:
goto case DatabaseEngineType.Oracle;
case DatabaseEngineType.Oracle:
realcommand = realcommand.Replace("[", "\"");
realcommand = realcommand.Replace("]", "\"");
break;
case DatabaseEngineType.MySql:
realcommand = realcommand.Replace("[", "`");
realcommand = realcommand.Replace("]", "`");
break;
}
return(realcommand);
}
private string InternalPrepareQueryText(DatabaseEngineType dbtype)
{
var result = new StringBuilder();
result.Append(DatabaseExtensions.GetCallCommandPrefix(CommandType, dbtype));
result.Append(" ");
AppendQuotedName(dbtype, result);
result.Append(" ");
var writeBraces = DatabaseExtensions.IsRequireBracesToCall(dbtype);
if (writeBraces)
{
result.Append("( ");
}
if (null != _paramdict)
{
WriteParameters(dbtype, result);
}
if (writeBraces)
{
result.Append(" )");
}
return(result.ToString());
}
public void CanCreateValidQueryOnAnyDatabaseCall(SqlCommandType calltype, DatabaseEngineType dbtype, string result)
{
var q = new UniSqlQuery(
"x", "y", new { a = "~x", b = "~", c = 23, d = "u" }, calltype
);
Assert.AreEqual(result, q.PrepareQueryText(dbtype));
}
public static string TempTable_Insert(string tempTableName, string value, DatabaseEngineType databaseEngineType)
{
return(databaseEngineType switch
{
DatabaseEngineType.MySQL => $"insert into `{tempTableName}`(`id`) values({value});",
DatabaseEngineType.SQLite => $"insert into temp.{tempTableName}(\"id\") values({value});",
_ => "",
});
private static string GetQuotedName(string name, DatabaseEngineType dbtype)
{
if (name.Contains("."))
{
var sandn = name.Split('.');
return(sandn[0] + "." + QuoteIdentifier(sandn[1], dbtype));
}
else
{
return(QuoteIdentifier(name, dbtype));
}
}
public static string Name(DatabaseEngineType engineType)
{
switch (engineType)
{
case DatabaseEngineType.SqlServer:
return SqlServer;
case DatabaseEngineType.Oracle:
return Oracle;
default:
throw new ArgumentOutOfRangeException("engineType");
}
}
private void WriteParameters(DatabaseEngineType dbtype, StringBuilder result)
{
var writeAssigns = DatabaseExtensions.IsSupportNamedParameters(dbtype);
var innerPrefix = DatabaseExtensions.GetPrefixOfInnerParameter(dbtype);
var outerPrefix = DatabaseExtensions.GetPrefixOfOuterParameter(dbtype);
var assignSymbol = DatabaseExtensions.GetAssignOperator(dbtype);
bool first = true;
foreach (var pair in _paramdict)
{
if (!first)
{
result.Append(", ");
}
first = false;
if (writeAssigns)
{
result.Append(innerPrefix);
result.Append(pair.Key);
result.Append(" ");
result.Append(assignSymbol);
result.Append(" ");
}
var item = pair.Value;
if (null == item || item is DBNull)
{
result.Append("null");
}
if (item is string)
{
var stritem = item as string;
if (stritem.StartsWith("~"))
{
result.Append(outerPrefix);
result.Append(stritem.Substring(1));
}
else
{
result.Append("'" + stritem.Replace("'", "''") + "'");
}
}
else
{
var anyitem = item.ToString();
if (!Regex.IsMatch(anyitem, @"^[\w\d\.]+$", RegexOptions.Compiled))
{
anyitem = "'" + anyitem.Replace("'", "''") + "'";
}
result.Append(anyitem);
}
}
}
/// <summary>
/// Подготовка текста DbCommand
/// </summary>
/// <param name="dbtype"></param>
/// <returns></returns>
public string PrepareQueryText(DatabaseEngineType dbtype)
{
var idx = (int)dbtype;
if (_invalid)
{
_textcache = new string[5];
}
if (string.IsNullOrWhiteSpace(_textcache[idx]))
{
_textcache[idx] = InternalPrepareQueryText(dbtype);
}
return(_textcache[idx]);
}
public static string TempTable_Select_All(string tempTableName, DatabaseEngineType databaseEngineType)
{
switch (databaseEngineType)
{
case DatabaseEngineType.MySQL:
return($"select `id` from `{tempTableName}`;");
case DatabaseEngineType.SQLite:
return($"select id from temp.{tempTableName};");
case DatabaseEngineType.MSSQLSERVER:
default:
return("");
}
}
public static string TempTable_Drop(string tempTableName, DatabaseEngineType databaseEngineType)
{
switch (databaseEngineType)
{
case DatabaseEngineType.MySQL:
return($"drop temporary table if exists `{tempTableName}`;");
case DatabaseEngineType.SQLite:
return($"drop table if EXISTS temp.{tempTableName};");
case DatabaseEngineType.MSSQLSERVER:
default:
return("");
}
}
public static string TempTable_Create(string tempTableName, DatabaseEngineType databaseEngineType)
{
switch (databaseEngineType)
{
case DatabaseEngineType.MySQL:
return($"create temporary table `{tempTableName}` ( `id` int not null);");
case DatabaseEngineType.SQLite:
return($"create temporary table {tempTableName} (\"id\" integer not null);");
case DatabaseEngineType.MSSQLSERVER:
default:
return("");
}
}
public static string FoundChanges_Statement(DatabaseEngineType databaseEngineType)
{
switch (databaseEngineType)
{
case DatabaseEngineType.MySQL:
return($" found_rows() ");
case DatabaseEngineType.SQLite:
return($" changes() ");
case DatabaseEngineType.MSSQLSERVER:
default:
return("");
}
}
public static string GetLastInsertIdStatement(DatabaseEngineType databaseEngineType)
{
switch (databaseEngineType)
{
case DatabaseEngineType.SQLite:
return("last_insert_rowid()");
case DatabaseEngineType.MySQL:
return("last_insert_id()");
case DatabaseEngineType.MSSQLSERVER:
default:
return("");
}
}
public static string TempTable_Insert(string tempTableName, string value, DatabaseEngineType databaseEngineType)
{
switch (databaseEngineType)
{
case DatabaseEngineType.MySQL:
return($"insert into `{tempTableName}`(`id`) values({value});");
case DatabaseEngineType.SQLite:
return($"insert into temp.{tempTableName}(\"id\") values({value});");
case DatabaseEngineType.MSSQLSERVER:
default:
return("");
}
}
protected DatabaseConnectionBase(DatabaseEngineType engineType, string name, string description,
string engineName, string serverName, string userName, string password, string dataSourceDescription,
string dataSourceValue, string databaseDescription, string databaseValue)
{
EngineType = engineType;
Name = name;
Description = description;
EngineName = engineName;
ServerName = serverName;
UserName = userName;
Password = password;
DataSourceDescription = dataSourceDescription;
DataSourceValue = dataSourceValue;
DatabaseDescription = databaseDescription;
DatabaseValue = databaseValue;
}
/// <summary>
/// Rewrites unified sql to match different database notations
/// </summary>
/// <param name="command"></param>
/// <param name="dbtype"></param>
/// <returns></returns>
/// <remarks>
/// source
/// UNICALL MY.NAME | a = ~x , b = ~x , ~z
/// where tilda ~ marks named parameters
/// will be converted to :
/// exec MY.NAME @a=@x, @b=@y, @z in T-SQL
/// select MY.NAME ( a := :x, b := :y, :z ) in PG
/// select MY.NAME ( a => :x, b=>:y, :z) in ORACLE
/// CALL MY.NAME ( ?x, ?y, ?z) in MySQL (doesn't support named)
/// </remarks>
public static string RewriteSql(string command, DatabaseEngineType dbtype)
{
var result = "";
if (command.StartsWith("UNI"))
{
result = RewriteSqlByUniNotation(command, dbtype);
}
else
{
result = RewriteWithSimpleUniNotation(command, dbtype);
}
//for MySql need correction due it's not support schemas
if (dbtype == DatabaseEngineType.MySql)
{
result = Regex.Replace(result, @"(\w+)\.`([^`]+)`", "`$1_$2`", RegexOptions.Compiled);
}
return(result);
}
public static string CreateAddTemplate(DatabaseEntityDef modelDef, DatabaseEngineType engineType)
{
StringBuilder args = new StringBuilder();
StringBuilder selectArgs = new StringBuilder();
StringBuilder values = new StringBuilder();
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
if (info.IsTableProperty)
{
selectArgs.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName);
if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime")
{
continue;
}
args.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName);
values.AppendFormat(GlobalSettings.Culture, " {0},", info.DbParameterizedName);
}
}
if (selectArgs.Length > 0)
{
selectArgs.Remove(selectArgs.Length - 1, 1);
}
if (args.Length > 0)
{
args.Remove(args.Length - 1, 1);
}
if (values.Length > 0)
{
values.Remove(values.Length - 1, 1);
}
DatabaseEntityPropertyDef idProperty = modelDef.GetProperty("Id") !;
return($"insert into {modelDef.DbTableReservedName}({args}) values({values});select {selectArgs} from {modelDef.DbTableReservedName} where {idProperty.DbReservedName} = {GetLastInsertIdStatement(engineType)};");
}
/// <summary>
/// True - если движок поддерживает именованные параметры в вызове функций
/// </summary>
/// <param name="dbtype"></param>
/// <returns></returns>
public static bool IsSupportNamedParameters(DatabaseEngineType dbtype)
{
switch (dbtype)
{
case DatabaseEngineType.SqlServer:
return(true);
case DatabaseEngineType.Postgres:
return(true);
case DatabaseEngineType.MySql:
return(false);
case DatabaseEngineType.Oracle:
return(true);
default:
return(true);
}
}
/// <summary>
/// Возвращает оператор присвоения параметров
/// </summary>
/// <param name="dbtype"></param>
/// <returns></returns>
public static string GetAssignOperator(DatabaseEngineType dbtype)
{
switch (dbtype)
{
case DatabaseEngineType.SqlServer:
return("=");
case DatabaseEngineType.Postgres:
return(":=");
case DatabaseEngineType.MySql:
return("=");
case DatabaseEngineType.Oracle:
return("=>");
default:
return("=");
}
}
private void AppendQuotedName(DatabaseEngineType dbtype, StringBuilder result)
{
if (!string.IsNullOrWhiteSpace(Schema))
{
if (dbtype == DatabaseEngineType.MySql)
{
result.Append(DatabaseExtensions.QuoteIdentifier(Schema + "_" + Name, dbtype));
}
else
{
result.Append(DatabaseExtensions.QuoteIdentifier(Schema, dbtype));
result.Append(".");
result.Append(DatabaseExtensions.QuoteIdentifier(Name, dbtype));
}
}
else
{
result.Append(DatabaseExtensions.QuoteIdentifier(Name, dbtype));
}
}
/// <summary>
/// Делает защищенное имя идентификатора
/// </summary>
/// <param name="name"></param>
/// <param name="dbtype"></param>
/// <returns></returns>
public static string QuoteIdentifier(string name, DatabaseEngineType dbtype)
{
switch (dbtype)
{
case DatabaseEngineType.SqlServer:
return("[" + name + "]");
case DatabaseEngineType.Postgres:
return("\"" + name + "\"");
case DatabaseEngineType.MySql:
return("`" + name + "`");
case DatabaseEngineType.Oracle:
return("\"" + name + "\"");
default:
return(name); //no quoting for unknown db
}
}
/// <summary>
/// Возвращает префикс для именованного внешнего параметра
/// </summary>
/// <param name="dbtype"></param>
/// <returns></returns>
public static char GetPrefixOfOuterParameter(DatabaseEngineType dbtype)
{
switch (dbtype)
{
case DatabaseEngineType.SqlServer:
return('@');
case DatabaseEngineType.Postgres:
return(':');
case DatabaseEngineType.MySql:
return('?');
case DatabaseEngineType.Oracle:
return(':');
default:
return(':');
}
}
/// <summary>
/// Возвращает префикс для именованного (внутреннего) параметра
/// </summary>
/// <param name="dbtype"></param>
/// <returns></returns>
public static string GetPrefixOfInnerParameter(DatabaseEngineType dbtype)
{
switch (dbtype)
{
case DatabaseEngineType.SqlServer:
return("@");
case DatabaseEngineType.Postgres:
return(String.Empty);
case DatabaseEngineType.MySql:
return(String.Empty);
case DatabaseEngineType.Oracle:
return(String.Empty);
default:
return(String.Empty);
}
}
/// <summary>
/// Возвращает префикс всей команды до собственно имени процедуры/функции
/// </summary>
/// <param name="cmdtype"></param>
/// <param name="dbtype"></param>
/// <returns></returns>
public static string GetCallCommandPrefix(SqlCommandType cmdtype, DatabaseEngineType dbtype)
{
if (dbtype == DatabaseEngineType.SqlServer)
{
return("EXEC");
}
if (dbtype == DatabaseEngineType.MySql)
{
if (cmdtype == SqlCommandType.Call)
{
return("CALL");
}
return("CALL ALL");
}
if (cmdtype == SqlCommandType.Call)
{
return("SELECT");
}
return("SELECT * FROM");
}
/// <summary>
/// True - параметры запроса должны закрываться скобками
/// </summary>
/// <param name="dbtype"></param>
/// <returns></returns>
public static bool IsRequireBracesToCall(DatabaseEngineType dbtype)
{
switch (dbtype)
{
case DatabaseEngineType.SqlServer:
return(false);
case DatabaseEngineType.Postgres:
return(true);
case DatabaseEngineType.MySql:
return(true);
case DatabaseEngineType.Oracle:
return(true);
default:
return(true);
}
}
private static string RewriteSqlByUniNotation(string command, DatabaseEngineType dbtype)
{
var mainsplit = command.Split('|');
var commandAndName = mainsplit[0].Split(' ');
var paramlist = "";
if (mainsplit.Length == 2)
{
paramlist = mainsplit[1].Trim();
}
var requireBraces = IsRequireBracesToCall(dbtype);
var cmdbuilder = new StringBuilder();
cmdbuilder.Append(GetCallCommandPrefix(DetermineCommandType(commandAndName[0].Trim()), dbtype));
cmdbuilder.Append(" ");
cmdbuilder.Append(GetQuotedName(commandAndName[1].Trim(), dbtype));
if (requireBraces)
{
cmdbuilder.Append(" ( ");
}
else
{
cmdbuilder.Append(" ");
}
if (!String.IsNullOrWhiteSpace(paramlist))
{
cmdbuilder.Append(RewriteParamList(paramlist, IsSupportNamedParameters(dbtype),
GetPrefixOfInnerParameter(dbtype),
GetPrefixOfOuterParameter(dbtype), GetAssignOperator(dbtype)));
}
if (requireBraces)
{
cmdbuilder.Append(" )");
}
return(cmdbuilder.ToString());
}
private void WriteParameters(DatabaseEngineType dbtype, StringBuilder result) {
var writeAssigns = DatabaseExtensions.IsSupportNamedParameters(dbtype);
var innerPrefix = DatabaseExtensions.GetPrefixOfInnerParameter(dbtype);
var outerPrefix = DatabaseExtensions.GetPrefixOfOuterParameter(dbtype);
var assignSymbol = DatabaseExtensions.GetAssignOperator(dbtype);
bool first = true;
foreach (var pair in _paramdict) {
if (!first) {
result.Append(", ");
}
first = false;
if (writeAssigns) {
result.Append(innerPrefix);
result.Append(pair.Key);
result.Append(" ");
result.Append(assignSymbol);
result.Append(" ");
}
var item = pair.Value;
if (null==item || item is DBNull) {
result.Append("null");
}
if (item is string) {
var stritem = item as string;
if (stritem.StartsWith("~")) {
result.Append(outerPrefix);
result.Append(stritem.Substring(1));
}
else {
result.Append("'" + stritem.Replace("'", "''") + "'");
}
}
else {
var anyitem = item.ToString();
if (!Regex.IsMatch(anyitem, @"^[\w\d\.]+$",RegexOptions.Compiled)) {
anyitem = "'" + anyitem.Replace("'", "''") + "'";
}
result.Append(anyitem);
}
}
}
private void AppendQuotedName(DatabaseEngineType dbtype, StringBuilder result) {
if (!string.IsNullOrWhiteSpace(Schema)) {
if (dbtype == DatabaseEngineType.MySql) {
result.Append(DatabaseExtensions.QuoteIdentifier(Schema + "_" + Name, dbtype));
}
else {
result.Append(DatabaseExtensions.QuoteIdentifier(Schema, dbtype));
result.Append(".");
result.Append(DatabaseExtensions.QuoteIdentifier(Name, dbtype));
}
}
else {
result.Append(DatabaseExtensions.QuoteIdentifier(Name, dbtype));
}
}
public static void Main(string[] args)
{
var dataSource = args.Length > 0 ? args[0] : "localhost"; // 172.17.0.5,1433 192.168.56.1,1435, mydb.database.windows.net,1433
var dbName = args.Length > 1 ? args[1] : "master";
var user = args.Length > 2 ? args[2] : "sa";
var outputFolderSql = args.Length > 3 ? args[3] : "";
Console.WriteLine("Password for user {0} for database {1} at {2}: ", user, dbName, dataSource);
var password = GetPassword();
SqlServerVersion targetServerVersion = SqlServerVersion.Version140;
DatabaseEngineEdition targetDatabaseEngineEdition = DatabaseEngineEdition.Standard;
DatabaseEngineType targetDatabaseEngineType = DatabaseEngineType.Standalone;
String outputFileBase = dbName + "@" + @dataSource + "_" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss");
String outputFileSql = outputFileBase + ".sql";
String outputFilePathSql = Path.Combine(outputFolderSql, outputFileSql);
Console.WriteLine("Connecting to database... ");
var connectionString = new SqlConnectionStringBuilder
{
PersistSecurityInfo = true, // Needed - see https://github.com/Microsoft/sqltoolsservice/issues/372#issuecomment-307190530
DataSource = @dataSource,
UserID = user,
Password = password,
InitialCatalog = dbName,
PacketSize = 4096, // default is 8000, however 4096 gives much better performance when accessing azure database
//IntegratedSecurity = true, // Log in using SQL authentication or Windows authentication
//MultipleActiveResultSets = false,
//Encrypt = false,
//TrustServerCertificate = false,
};
var sqlConnection = new SqlConnection(connectionString.ConnectionString);
var serverConnection = new ServerConnection(sqlConnection);
var sqlServer = new Server(serverConnection);
//sqlServer.ConnectionContext.BatchSeparator = "GO";
// Improve performance by just loading fields we need
sqlServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(Table), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(View), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(Trigger), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(SqlAssembly), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(Default), false);
sqlServer.SetDefaultInitFields(typeof(Rule), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedAggregate), false);
sqlServer.SetDefaultInitFields(typeof(Synonym), false);
sqlServer.SetDefaultInitFields(typeof(Sequence), false);
sqlServer.SetDefaultInitFields(typeof(SecurityPolicy), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedDataType), false);
sqlServer.SetDefaultInitFields(typeof(XmlSchemaCollection), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedType), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedTableType), false);
sqlServer.SetDefaultInitFields(typeof(PartitionScheme), false);
sqlServer.SetDefaultInitFields(typeof(PartitionFunction), false);
sqlServer.SetDefaultInitFields(typeof(PlanGuide), false);
sqlServer.SetDefaultInitFields(typeof(FullTextCatalog), false);
var db = sqlServer.Databases[dbName];
Console.WriteLine("Connected!");
Console.WriteLine("Generating script for database " + dbName + ". This may take a while...");
// ### First script "CREATE DATABASE ..."
var createDbScriptOptions = DefaultOptions(targetServerVersion, targetDatabaseEngineEdition, targetDatabaseEngineType);
createDbScriptOptions.FileName = outputFilePathSql;
createDbScriptOptions.ScriptData = false;
createDbScriptOptions.WithDependencies = false;
db.Script(createDbScriptOptions);
Console.WriteLine("Collecting objects...");
// ### Now script all the objects within the database
var urns = new UrnCollection();
Console.WriteLine("...Tables");
foreach (Table obj in db.Tables)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...Views");
foreach (View obj in db.Views)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...UserDefinedFunctions");
foreach (UserDefinedFunction obj in db.UserDefinedFunctions)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...Defaults");
foreach (Default obj in db.Defaults)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Rules");
foreach (Rule obj in db.Rules)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Triggers");
foreach (Trigger obj in db.Triggers)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...UserDefinedAggregates");
foreach (UserDefinedAggregate obj in db.UserDefinedAggregates)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Synonyms");
foreach (Synonym obj in db.Synonyms)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Sequences");
foreach (Sequence obj in db.Sequences)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...SecurityPolicies");
foreach (SecurityPolicy obj in db.SecurityPolicies)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...UserDefinedDataTypes");
foreach (UserDefinedDataType obj in db.UserDefinedDataTypes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...XmlSchemaCollections");
foreach (XmlSchemaCollection obj in db.XmlSchemaCollections)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...UserDefinedTypes");
foreach (UserDefinedType obj in db.UserDefinedTypes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...UserDefinedTableTypes");
foreach (UserDefinedTableType obj in db.UserDefinedTableTypes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...PartitionSchemes");
foreach (PartitionScheme obj in db.PartitionSchemes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...PartitionFunctions");
foreach (PartitionFunction obj in db.PartitionFunctions)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...PlanGuides");
foreach (PlanGuide obj in db.PlanGuides)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Assemblies");
foreach (SqlAssembly obj in db.Assemblies)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...StoredProcedures");
foreach (StoredProcedure obj in db.StoredProcedures)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
var ftUrns = new UrnCollection();
Console.WriteLine("...FullTextCatalogs");
foreach (FullTextCatalog obj in db.FullTextCatalogs)
{
Console.WriteLine(" -> " + obj.Name);
ftUrns.Add(obj.Urn);
}
var scripter = new Scripter(sqlServer)
{
PrefetchObjects = true, // some sources suggest this may speed things up
};
scripter.Options = DefaultOptions(targetServerVersion, targetDatabaseEngineEdition, targetDatabaseEngineType);
scripter.Options.FileName = outputFilePathSql;
scripter.ScriptingProgress += (o, e) => Console.WriteLine("Scripting: {0}", e.Current);
scripter.ScriptingError += (o, e) => Console.WriteLine("Error: {0}", e.Current);
scripter.Options.WithDependencies = false;
scripter.EnumScript(ftUrns);
scripter.Options.WithDependencies = true;
scripter.EnumScript(urns);
Console.WriteLine("Scripting finished! See File:");
Console.WriteLine(outputFilePathSql);
// And now we also could create a backup (but be aware the file is saved ON THE SERVER
// (Maybe we could save it locally somehow via DeviceType.Pipe, DeviceType.Url, DeviceType.VirtualDevice, etc...)
//String outputFileBak = outputFileBase + ".bak";
//String outputFilePathBak = outputFileBak;
//Console.WriteLine();
//Console.WriteLine("And now we create a backup...");
//var backup = new Backup
//{
// Action = BackupActionType.Database,
// Database = dbName
//};
//backup.Devices.Add(new BackupDeviceItem(outputFilePathBak, DeviceType.File));
//backup.Initialize = true;
//backup.Checksum = true;
//backup.ContinueAfterError = false;
//backup.Incremental = false;
//backup.PercentCompleteNotification = 1;
//backup.LogTruncation = BackupTruncateLogType.Truncate;
//backup.PercentComplete += (s, e) => Console.WriteLine("Backup: {0}", e.Message);
//backup.Complete += (s, e) => Console.WriteLine("{0} See File: {1}", e.Error.Message, outputFilePathBak);
//backup.SqlBackupAsync(sqlServer);
// ----
// An alternative would be using the internal class ScriptPublishWizard via reflection (because we can't access internal stuff)
// ###################################################################
//new Microsoft.SqlServer.Management.SqlScriptPublish.ScriptPublishWizard();
//var assembly = Assembly.LoadFrom(@"C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.SqlServer.Management.SqlScriptPublishUI.dll");
// ----
// Bonus: Display all the configuration options.
// ###################################################################
//foreach (ConfigProperty p in sqlServer.Configuration.Properties)
//{
// Console.WriteLine(p.DisplayName + " " + p.ConfigValue + " " + p.RunValue);
//}
//Console.WriteLine("There are " + sqlServer.Configuration.Properties.Count.ToString() + " configuration options.");
////Display the maximum and minimum values for ShowAdvancedOptions.
//int min = sqlServer.Configuration.ShowAdvancedOptions.Minimum;
//int max = sqlServer.Configuration.ShowAdvancedOptions.Maximum;
//Console.WriteLine("Minimum and Maximum values are " + min + " and " + max + ".");
sqlConnection.Close();
}
public void UnifiedSqlRewritingTest(string src, DatabaseEngineType dbtype, string result)
{
Assert.AreEqual(result, DatabaseExtensions.RewriteSql(src, dbtype));
}
public void UnifiedSqlRewritingTest(string src, DatabaseEngineType dbtype, string result) {
Assert.AreEqual(result,DatabaseExtensions.RewriteSql(src,dbtype));
}
/// <summary>
/// See https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions_properties.aspx
/// </summary>
/// <param name="options">ScriptingOptions to modify - if null a new ScriptingOptions object will be used instead.</param>
/// <returns>A ScriptingOptions with default values set</returns>
private static ScriptingOptions DefaultOptions(SqlServerVersion targetServerVersion, DatabaseEngineEdition targetDatabaseEngineEdition, DatabaseEngineType targetDatabaseEngineType)
{
var o = new ScriptingOptions();
// General
o.AllowSystemObjects = false;
o.DdlHeaderOnly = false;
o.DdlBodyOnly = false;
// Here you could disable scripting absolute file paths
o.NoFileGroup = false;
o.IncludeFullTextCatalogRootPath = true;
// Output
o.ScriptBatchTerminator = true;
o.BatchSize = 100;
o.NoCommandTerminator = false;
o.AnsiFile = false;
o.Encoding = Encoding.UTF8;
o.ToFileOnly = true;
// "General" from the SSMS UI
o.AnsiPadding = false;
o.AppendToFile = true;
o.IncludeIfNotExists = false;
o.ContinueScriptingOnError = false;
o.ConvertUserDefinedDataTypesToBaseType = false;
o.WithDependencies = true;
o.IncludeHeaders = true;
o.DriIncludeSystemNames = false;
// Include unsupported statements - doesn't exist...
o.SchemaQualify = true;
o.Bindings = false;
o.NoCollation = true;
o.DriDefaults = true;
o.ScriptDrops = false;
o.ExtendedProperties = true;
o.TargetServerVersion = targetServerVersion;
o.TargetDatabaseEngineEdition = targetDatabaseEngineEdition;
o.TargetDatabaseEngineType = targetDatabaseEngineType;
o.LoginSid = false;
o.Permissions = false;
o.ScriptOwner = false;
o.Statistics = false;
o.IncludeDatabaseContext = true;
o.ScriptSchema = true;
o.ScriptData = true;
// "Table view options" from the SSMS UI
o.ChangeTracking = false;
o.DriChecks = true;
o.ScriptDataCompression = false;
o.DriForeignKeys = true;
o.FullTextIndexes = true;
o.Indexes = true;
o.PrimaryObject = true;
o.Triggers = true;
o.DriUniqueKeys = true;
o.NoIdentities = true;
o.IncludeDatabaseRoleMemberships = false;
o.NoMailProfilePrincipals = true;
o.NoMailProfileAccounts = true;
o.NoExecuteAs = true;
o.TimestampToBinary = false;
o.NoFileStreamColumn = false;
o.NoFileStream = false;
o.NoViewColumns = false;
o.NoVardecimal = false;
o.EnforceScriptingOptions = false;
o.OptimizerData = false;
o.NoXmlNamespaces = false;
o.NoTablePartitioningSchemes = false;
o.NoIndexPartitioningSchemes = false;
o.NonClusteredIndexes = true;
o.ClusteredIndexes = true;
o.XmlIndexes = true;
o.SchemaQualifyForeignKeysReferences = true;
o.FullTextStopLists = true;
o.FullTextCatalogs = true;
o.NoAssemblies = false;
o.AgentAlertJob = true;
o.AgentNotify = true;
o.AgentJobId = true;
o.Default = true;
o.DriAll = true;
o.DriAllConstraints = true;
o.DriAllKeys = true;
o.DriIndexes = true;
o.DriPrimaryKey = true;
o.DriClustered = true;
o.DriNonClustered = true;
o.DriWithNoCheck = false;
return(o);
}
private string InternalPrepareQueryText(DatabaseEngineType dbtype) {
var result = new StringBuilder();
result.Append(DatabaseExtensions.GetCallCommandPrefix(CommandType, dbtype));
result.Append(" ");
AppendQuotedName(dbtype, result);
result.Append(" ");
var writeBraces = DatabaseExtensions.IsRequireBracesToCall(dbtype);
if (writeBraces) {
result.Append("( ");
}
if (null != _paramdict) {
WriteParameters(dbtype, result);
}
if (writeBraces) {
result.Append(" )");
}
return result.ToString();
}
public static string CreateAddOrUpdateTemplate(DatabaseEntityDef modelDef, DatabaseEngineType engineType)
{
StringBuilder args = new StringBuilder();
StringBuilder selectArgs = new StringBuilder();
StringBuilder values = new StringBuilder();
StringBuilder exceptGuidAndFixedVersionUpdatePairs = new StringBuilder();
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
if (info.IsTableProperty)
{
selectArgs.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName);
if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime")
{
continue;
}
args.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName);
values.AppendFormat(GlobalSettings.Culture, " {0},", info.DbParameterizedName);
}
}
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
if (info.IsTableProperty)
{
if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "Version" || info.PropertyName == "Guid" || info.PropertyName == "LastTime" || info.PropertyName == "Deleted")
{
continue;
}
exceptGuidAndFixedVersionUpdatePairs.Append($" {info.DbReservedName}={info.DbParameterizedName},");
}
}
DatabaseEntityPropertyDef versionPropertyDef = modelDef.GetProperty("Version") !;
exceptGuidAndFixedVersionUpdatePairs.Append($" {versionPropertyDef.DbReservedName}={versionPropertyDef.DbReservedName}+1,");
if (selectArgs.Length > 0)
{
selectArgs.Remove(selectArgs.Length - 1, 1);
}
if (args.Length > 0)
{
args.Remove(args.Length - 1, 1);
}
if (values.Length > 0)
{
values.Remove(values.Length - 1, 1);
}
if (exceptGuidAndFixedVersionUpdatePairs.Length > 0)
{
exceptGuidAndFixedVersionUpdatePairs.Remove(exceptGuidAndFixedVersionUpdatePairs.Length - 1, 1);
}
DatabaseEntityPropertyDef guidProperty = modelDef.GetProperty("Guid") !;
return($"insert into {modelDef.DbTableReservedName}({args}) values({values}) {OnDuplicateKeyUpdateStatement(engineType)} {exceptGuidAndFixedVersionUpdatePairs};select {selectArgs} from {modelDef.DbTableReservedName} where {guidProperty.DbReservedName} = {guidProperty.DbParameterizedName};");
}
/// <summary>
/// Подготовка текста DbCommand
/// </summary>
/// <param name="dbtype"></param>
/// <returns></returns>
public string PrepareQueryText(DatabaseEngineType dbtype) {
var idx = (int) dbtype;
if (_invalid) {
_textcache = new string[5];
}
if( string.IsNullOrWhiteSpace(_textcache[idx])) {
_textcache[idx] = InternalPrepareQueryText(dbtype);
}
return _textcache[idx];
}
