protected string GetKey(SqlObjectType type, object subType = null)
{
var result = "";
switch (type)
{
case SqlObjectType.Sproc:
var sprocType = SqlGeneratorHelper.GetSubType <SqlSprocType>(subType);
result = $"/Procedures/{sprocType}";
break;
case SqlObjectType.Udf:
var udfType = SqlGeneratorHelper.GetSubType <SqlUdfType>(subType);
result = $"/Functions/{udfType}";
break;
case SqlObjectType.Table:
var tblType = SqlGeneratorHelper.GetSubType <SqlTableType>(subType);
result = $"/Tables/{tblType}";
break;
case SqlObjectType.Type:
var udtType = SqlGeneratorHelper.GetSubType <SqlUdtType>(subType);
result = $"/Types/{udtType}";
break;
case SqlObjectType.View:
var viewType = SqlGeneratorHelper.GetSubType <SqlViewType>(subType);
result = $"/Views/{viewType}";
break;
case SqlObjectType.Constraint:
var constraintType = SqlGeneratorHelper.GetSubType <SqlConstraintType>(subType);
result = $"/Constraints/{constraintType}";
break;
case SqlObjectType.Key:
var keyType = SqlGeneratorHelper.GetSubType <SqlKeyType>(subType);
result = $"/Keys/{keyType}";
break;
case SqlObjectType.Index: result = "Indexes"; break;
case SqlObjectType.Assembly: result = "/Assemblies"; break;
case SqlObjectType.SecurityPolicy: result = "/SecurityPolicies"; break;
default:
result = $"/{type}s{(subType == null ? "": $"/{subType}")}";
break;
}
private void GenerateInternal(GenerationType generateType, SqlObjectType type, object subType, string keyword)
{
var nativeType = SqlGeneratorHelper.GetNativeType(subType);
if (string.IsNullOrEmpty(nativeType))
{
Logger.Warn($"Warning: {type}.{subType} is not supported yet. Generating script for {type}.{subType} objects skipped.");
}
else
{
var path = Options.GetPath(type, subType);
CreateDir(path);
Generate(path, generateType, type, nativeType, keyword);
}
}
private KeyValuePair <string, int> CountInternal(SqlObjectType type, object subType, string keyword)
{
var nativeType = SqlGeneratorHelper.GetNativeType(subType);
KeyValuePair <string, int> result;
if (string.IsNullOrEmpty(nativeType))
{
result = new KeyValuePair <string, int>(subType.ToString(), 0);
Logger.Warn($"Warning: {type}.{subType} is not supported yet. Counting {type}.{subType} objects skipped.");
}
else
{
var count = Count(nativeType, keyword);
result = new KeyValuePair <string, int>(subType.ToString(), count);
}
return(result);
}
public virtual string GetPath(SqlObjectType type, object subType = null)
{
var result = "";
switch (type)
{
case SqlObjectType.Sproc:
var sprocType = SqlGeneratorHelper.GetSubType <SqlSprocType>(subType);
result = $"/Procedures/{sprocType}";
break;
case SqlObjectType.Udf:
var udfType = SqlGeneratorHelper.GetSubType <SqlUdfType>(subType);
result = $"/Functions/{udfType}";
break;
case SqlObjectType.Table:
var tblType = SqlGeneratorHelper.GetSubType <SqlTableType>(subType);
result = $"/Tables/{tblType}";
break;
case SqlObjectType.Type:
var udtType = SqlGeneratorHelper.GetSubType <SqlUdtType>(subType);
result = $"/Types/{udtType}";
break;
case SqlObjectType.Trigger: result = $"/Triggers"; break;
case SqlObjectType.Index: result = "Indexes"; break;
case SqlObjectType.View:
var viewType = SqlGeneratorHelper.GetSubType <SqlViewType>(subType);
result = $"/Views/{viewType}";
break;
case SqlObjectType.Synonym: result = "/Synonyms"; break;
case SqlObjectType.Constraint:
var constraintType = SqlGeneratorHelper.GetSubType <SqlConstraintType>(subType);
result = $"/Constraints/{constraintType}";
break;
case SqlObjectType.FileGroup: result = "/FileGroups"; break;
case SqlObjectType.File: result = ""; break;
case SqlObjectType.Partition: result = "/File"; break;
case SqlObjectType.Assembly: result = "/Assemblies"; break;
case SqlObjectType.Rule: result = "/Rules"; break;
case SqlObjectType.Sequence: result = "/Sequences"; break;
case SqlObjectType.Diagram: result = "/Diagrams"; break;
case SqlObjectType.Schema: result = "/Schemas"; break;
case SqlObjectType.User: result = "/Users"; break;
case SqlObjectType.Role: result = "/Roles"; break;
case SqlObjectType.Permission: result = "/Permissions"; break;
case SqlObjectType.Certificate: result = "/Certificates"; break;
case SqlObjectType.SecurityPolicy: result = "/SecurityPolicies"; break;
case SqlObjectType.Key:
var keyType = SqlGeneratorHelper.GetSubType <SqlKeyType>(subType);
result = $"/Keys/{keyType}";
break;
default:
break;
}
return(BasePath + result);
}
public Dictionary <string, int> Count(SqlObjectType type, object subType = null, string keyword = "")
{
var result = new Dictionary <string, int>();
var strSubType = subType?.ToString();
KeyValuePair <string, int> kv;
switch (type)
{
case SqlObjectType.Sproc:
if (string.IsNullOrEmpty(strSubType) || string.Compare(strSubType, "all", true) == 0)
{
result = CountAll(type, typeof(SqlSprocType), keyword);
}
else
{
var sprocType = SqlGeneratorHelper.GetSubType <SqlSprocType>(subType);
if (sprocType.HasValue)
{
kv = CountInternal(type, sprocType.Value, keyword);
result.Add(kv.Key, kv.Value);
}
else
{
Logger.Danger($"No subtype specified or subtype ('{subType}') is invalid.");
}
}
break;
case SqlObjectType.Udf:
if (string.IsNullOrEmpty(strSubType) || string.Compare(strSubType, "all", true) == 0)
{
result = CountAll(type, typeof(SqlUdfType), keyword);
}
else
{
var udfType = SqlGeneratorHelper.GetSubType <SqlUdfType>(subType);
if (udfType.HasValue)
{
kv = CountInternal(type, udfType.Value, keyword);
result.Add(kv.Key, kv.Value);
}
else
{
Logger.Danger($"No subtype specified or subtype ('{subType}') is invalid.");
}
}
break;
case SqlObjectType.Table:
{
if (string.IsNullOrEmpty(strSubType) || string.Compare(strSubType, "all", true) == 0)
{
result = CountAll(type, typeof(SqlTableType), keyword);
}
else
{
var tableType = SqlGeneratorHelper.GetSubType <SqlTableType>(subType);
if (tableType.HasValue)
{
kv = CountInternal(type, tableType.Value, keyword);
result.Add(kv.Key, kv.Value);
}
else
{
Logger.Danger($"No subtype specified or subtype ('{subType}') is invalid.");
}
}
}
break;
case SqlObjectType.View:
kv = CountInternal(type, SqlViewType.Sql, keyword);
result.Add(kv.Key, kv.Value);
break;
case SqlObjectType.Trigger:
var triggerType = SqlGeneratorHelper.GetSubType <SqlTriggerType>(subType);
if (triggerType.HasValue)
{
kv = CountInternal(type, triggerType.Value, keyword);
result.Add(kv.Key, kv.Value);
}
else
{
Logger.Danger($"No subtype specified or subtype ('{subType}') is invalid.");
}
break;
}
return(result);
}
public void Generate(GenerationType generateType, SqlObjectType type, object subType = null, string keyword = "")
{
var strSubType = subType?.ToString();
switch (type)
{
case SqlObjectType.Sproc:
if (string.IsNullOrEmpty(strSubType) || string.Compare(strSubType, "all", true) == 0)
{
GenerateAll(generateType, type, typeof(SqlSprocType), keyword);
}
else
{
var sprocType = SqlGeneratorHelper.GetSubType <SqlSprocType>(subType);
if (sprocType.HasValue)
{
GenerateInternal(generateType, type, sprocType.Value, keyword);
}
else
{
Logger.Danger($"No subtype specified or subtype ('{subType}') is invalid.");
}
}
break;
case SqlObjectType.Udf:
if (string.IsNullOrEmpty(strSubType) || string.Compare(strSubType, "all", true) == 0)
{
GenerateAll(generateType, type, typeof(SqlUdfType), keyword);
}
else
{
var udfType = SqlGeneratorHelper.GetSubType <SqlUdfType>(subType);
if (udfType.HasValue)
{
GenerateInternal(generateType, type, udfType.Value, keyword);
}
else
{
Logger.Danger($"No subtype specified or subtype ('{subType}') is invalid.");
}
}
break;
case SqlObjectType.Table:
var sp_getDDL_exists = false;
using (var con = new SqlConnection(Options.ConnectionString))
{
using (var cmd = new SqlCommand($"use master;select case when exists(select 1 from sys.procedures where name = 'sp_GetDDL') then 1 else 0 end", con))
{
con.Open();
sp_getDDL_exists = ((int)cmd.ExecuteScalar()) == 1;
}
}
if (!sp_getDDL_exists)
{
Logger.Warn($"sp_GetDDL was not found. It is required to generate scripts of Tables. You can download it from https://github.com/ironcodev/SqlGenerator");
}
else
{
if (string.IsNullOrEmpty(strSubType) || string.Compare(strSubType, "all", true) == 0)
{
GenerateAll(generateType, type, typeof(SqlTableType), keyword);
}
else
{
var tableType = SqlGeneratorHelper.GetSubType <SqlTableType>(subType);
if (tableType.HasValue)
{
GenerateInternal(generateType, type, tableType.Value, keyword);
}
else
{
Logger.Danger($"No subtype specified or subtype ('{subType}') is invalid.");
}
}
}
break;
case SqlObjectType.View:
GenerateInternal(generateType, type, SqlViewType.Sql, keyword);
break;
case SqlObjectType.Trigger:
var triggerType = SqlGeneratorHelper.GetSubType <SqlTriggerType>(subType);
if (triggerType.HasValue)
{
GenerateInternal(generateType, type, triggerType.Value, keyword);
}
else
{
Logger.Danger($"No subtype specified or subtype ('{subType}') is invalid.");
}
break;
}
}
private void Generate(string path, GenerationType generateType, SqlObjectType type, string nativeType, string keyword)
{
try
{
Logger.Log($"-------------------------------------------------------------");
if (!string.IsNullOrEmpty(nativeType))
{
Logger.Log($"Generating {nativeType} objects in {path} ...");
using (var con = new SqlConnection(Options.ConnectionString))
{
using (var cmd = new SqlCommand($@" select schema_name(schema_id), name from sys.all_objects
where type_desc=@nativeType
and is_ms_shipped = 0
and (name like '%' + @keyword + '%' or len(rtrim(ltrim(isnull(@keyword, '')))) = 0)" , con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@nativeType", nativeType);
cmd.Parameters.AddWithValue("@keyword", keyword);
con.Open();
InitDatabase(con);
var sb = new StringBuilder();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var schema = reader[0]?.ToString();
var name = reader[1]?.ToString();
var text = "";
var error = false;
switch (generateType)
{
case GenerationType.Create:
text = @"
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
" + GetText(type, schema, name, out error);
text += "\n" + ObjectSeparator;
break;
case GenerationType.Drop:
text = $"drop {SqlGeneratorHelper.GetLogicalType(nativeType)} {schema}.{name}";
break;
case GenerationType.DropCreate:
text = $@"
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select 1 from sys.all_objects where name='{name}' and schema_id = schema_id('{schema}'))
drop {SqlGeneratorHelper.GetLogicalType(nativeType)} {schema}.{name}
go
";
text += GetText(type, schema, name, out error);
text += "\n" + ObjectSeparator;
break;
}
if (!error)
{
Writer.Options = new FileScriptWriterOptions
{
FileName = schema + "." + name + ".sql",
Path = Options.OutputPath + path,
OverwriteExisting = Options.OverwriteExisting
};
if (Writer.CanWrite())
{
Writer.Write(text);
Logger.Log($"{schema}.{name} generated");
}
else
{
Logger.Log($"generaing {schema}.{name} skipped");
}
}
}
}
}
}
}
else
{
Logger.Warn($"no native type given.");
}
}
catch (Exception e)
{
Logger.Danger(e, $"Generate('{path}', '{nativeType}', '{keyword}') Error");
}
}
