public void ChangeStoredProcedureSchema(StoredProcedureInfo obj, string newschema)
{
var oldObj = _database.FindStoredProcedure(obj);
if (oldObj != null)
{
oldObj.FullName = new NameWithSchema(newschema, oldObj.FullName.Name);
}
}
public void RenameStoredProcedure(StoredProcedureInfo obj, string newname)
{
var oldObj = _database.FindStoredProcedure(obj);
if (oldObj != null)
{
oldObj.FullName = new NameWithSchema(oldObj.FullName.Schema, newname);
}
}
public void AlterStoredProcedure(StoredProcedureInfo obj)
{
var oldObj = _database.FindStoredProcedure(obj);
if (oldObj != null)
{
string gid = oldObj.GroupId;
oldObj.Assign(obj);
oldObj.GroupId = gid;
}
}
private string GenerateMethod(StoredProcedureInfo routine)
{
var sb = new StringBuilder();
//sb.Append($"public object {routine.RoutineName}(");
//// Generate the parameter list.
//foreach (var param in parameters)
//{
// sb.Append($"{param.DotNetModifier} {param.DotNetType} {param.Name}");
// if (param != parameters.Last())
// {
// sb.Append(", ");
// }
//}
sb.AppendLine($"public IBM.Data.DB2.DB2DataReader {routine.DotNetDragText} {{");
// Generate the method body.
sb.AppendLine($"\t\tusing (var cmd = new IBM.Data.DB2.DB2Command(\"{routine.RoutineName}\", Connection as IBM.Data.DB2.DB2Connection)) {{");
sb.AppendLine("\t\t\tcmd.CommandType = System.Data.CommandType.StoredProcedure;");
var parameters = routine.GetParameters().ToArray();
for (var index = 0; index < parameters.Length; index++)
{
if (parameters[index].Direction == System.Data.ParameterDirection.Output)
{
sb.AppendLine($"\t\t\tcmd.Parameters.Add(new IBM.Data.DB2.DB2Parameter(\"{parameters[index].Name}\", IBM.Data.DB2.DB2Type.{DB2TypeFactory.GetDB2TypeFromString(parameters[index].DB2Type)})).Direction = System.Data.ParameterDirection.{parameters[index].Direction};");
//sb.AppendLine($"\tcmd.Parameters[cmd.Parameters.Count - 1].Direction = System.Data.ParameterDirection.{parameters[index].Direction};");
}
else
{
sb.AppendLine($"\t\t\tcmd.Parameters.Add(new IBM.Data.DB2.DB2Parameter(\"{parameters[index].Name}\", {parameters[index].Name})).Direction = System.Data.ParameterDirection.{parameters[index].Direction};");
//sb.AppendLine($"\tcmd.Parameters[cmd.Parameters.Count - 1].Direction = System.Data.ParameterDirection.{parameters[index].Direction};");
}
}
sb.AppendLine("\t\t\tvar reader = cmd.ExecuteReader();");
var outputParams = parameters.Where(x => x.Direction != System.Data.ParameterDirection.Input);
foreach (var param in outputParams)
{
sb.AppendLine($"\t\t\t{param.Name}=({param.DotNetType})(cmd.Parameters[\"{param.Name}\"].Value);");
}
sb.AppendLine("\t\t\treturn reader; } }");
return(sb.ToString());
}
private static void ApplyDefaultParameters(StoredProcedureInfo procedure, String definition)
{
// HACK: detecting default parameters (mssql not provide it)
String commentPattern = "(--[^\r\n]*[\r\n])";
String definitionPattern = "(PROCEDURE|PROC)\\s+(.*)\\sAS\\s";
// Remove comment
definition = Regex.Replace(definition, commentPattern, "", RegexOptions.CultureInvariant);
definition = definition.Trim();
// Capture parameters
Match m = Regex.Match(definition, definitionPattern, RegexOptions.IgnoreCase | RegexOptions.CultureInvariant | RegexOptions.Singleline);
if (m.Success)
{
String group2 = m.Groups[2].Value;
int firstAtCharacter = group2.IndexOf('@');
if (firstAtCharacter >= 0)
{
if (firstAtCharacter > 0)
{
group2 = group2.Substring(firstAtCharacter);
}
group2 = Regex.Replace(group2, "\\s+", " ").Trim();
String[] parameterDefinitions = group2.Split(',');
foreach (String parameterDefinition in parameterDefinitions)
{
String parameterDefinitionCleaned = parameterDefinition.Trim();
String parameterName = parameterDefinitionCleaned.Substring(0, parameterDefinitionCleaned.IndexOf(' ')).Trim();
bool hasDefaultValue = parameterDefinitionCleaned.Contains("=");
StoredProcedureParameterInfo parameterInfo = procedure.GetParameterByName(parameterName);
if (parameterInfo == null)
{
parameterInfo = new StoredProcedureParameterInfo();
parameterInfo.Name = parameterName;
}
parameterInfo.HasDefault = hasDefaultValue;
}
}
}
}
public static StoredProcedureInfo BuildStoredProcedureInfo(object storedProcedure)
{
Collection <StoredProcedureParameterInfo> parameterInfo = BuildStoredProcedureParameterInfo(storedProcedure);
string sql = BuildSql(storedProcedure, parameterInfo);
SqlParameter[] sqlParameters = BuildSqlParameters(storedProcedure, parameterInfo);
var info = new StoredProcedureInfo()
{
Sql = sql,
SqlParameters = sqlParameters
};
return(info);
}
public virtual void AlterStoredProcedure(StoredProcedureInfo obj)
{
WriteRaw(Regex.Replace(obj.CreateSql, @"create\s+procedure", "ALTER PROCEDURE", RegexOptions.IgnoreCase));
EndCommand();
}
public virtual void DropStoredProcedure(StoredProcedureInfo obj, bool testIfExists)
{
PutCmd("^drop ^procedure %f", obj.FullName);
}
public virtual void CreateStoredProcedure(StoredProcedureInfo obj)
{
WriteRaw(obj.CreateSql);
EndCommand();
}
public static List <StoredProcedureInfo> GetStoredProcedures(SqlConnection connection)
{
const String QUERY_PROCEDURES = @"SELECT p.[object_id], p.[name], s.[name] AS [schema_name], m.[definition] FROM [sys].[procedures] p INNER JOIN [sys].[schemas] s ON s.[schema_id] = p.[schema_id] LEFT JOIN [sys].[sql_modules] m ON m.[object_id] = p.[object_id] ORDER BY p.[name]";
const String QUERY_PROCEDURE_PARAMETERS = @"SELECT p.[name], p.[is_output], t.[name] FROM [sys].[parameters] p INNER JOIN [sys].[types] t on t.[user_type_id] = p.[user_type_id] WHERE p.[object_id] = @ObjectID ORDER BY p.[parameter_id]";
const String QUERY_PARAMETER_OBJECTID = "@ObjectID";
List <StoredProcedureInfo> procedures = new List <StoredProcedureInfo>();
using (SqlCommand tableCommand = new SqlCommand(QUERY_PROCEDURES, connection))
{
using (SqlDataReader reader = tableCommand.ExecuteReader())
{
while (reader.Read())
{
StoredProcedureInfo storedProcedureInfo = new StoredProcedureInfo();
storedProcedureInfo.ObjectID = reader.GetInt32(0);
storedProcedureInfo.Name = reader.GetString(1);
storedProcedureInfo.Schema = reader.GetString(2);
String definition = reader.GetString(3);
if (!String.IsNullOrWhiteSpace(definition))
{
ApplyDefaultParameters(storedProcedureInfo, definition);
}
procedures.Add(storedProcedureInfo);
}
}
}
foreach (var p in procedures)
{
using (SqlCommand parameterCommand = new SqlCommand(QUERY_PROCEDURE_PARAMETERS, connection))
{
parameterCommand.Parameters.AddWithValue(QUERY_PARAMETER_OBJECTID, p.ObjectID);
using (SqlDataReader reader = parameterCommand.ExecuteReader())
{
while (reader.Read())
{
StoredProcedureParameterInfo parameterInfo = p.GetParameterByName(reader.GetString(0));
if (parameterInfo == null)
{
parameterInfo = new StoredProcedureParameterInfo();
p.Parameters.Add(parameterInfo);
}
parameterInfo.Name = reader.GetString(0).Replace("@", "");
parameterInfo.IsOutput = reader.GetBoolean(1);
parameterInfo.DbType = SqlDbTypeFrom(reader.GetString(2));
parameterInfo.Type = TypeFrom(parameterInfo.DbType);
parameterInfo.MappingMethodName = MappingMethod(parameterInfo.DbType);
parameterInfo.FullTypeName = TypeFullName(parameterInfo.Type, parameterInfo.HasDefault);
parameterInfo.LocalVariableName = ToLocalVariableName(parameterInfo.Name);
parameterInfo.ParameterName = ToParameterName(parameterInfo.Name);
parameterInfo.LocalParameterVariableName = ToLocalParameterName(parameterInfo.Name);
}
}
}
}
return(procedures);
}
public override void ChangeStoredProcedureSchema(StoredProcedureInfo obj, string newschema)
{
ChangeObjectSchema(obj, newschema);
}
public void DropStoredProcedure(StoredProcedureInfo obj, bool testIfExists)
{
_database.StoredProcedures.RemoveAll(v => v.FullName == obj.FullName);
}
public void CreateStoredProcedure(StoredProcedureInfo obj)
{
_database.StoredProcedures.Add(obj.CloneStoredProcedure(_database));
}
public static void GenerateStoredProcedureMethod(CodeStringBuilder code, StoredProcedureInfo storedProcedureInfo)
{
String parameters = "";
foreach (StoredProcedureParameterInfo parameterInfo in storedProcedureInfo.Parameters)
{
if (parameters.Length > 0)
{
parameters += ", ";
}
if (parameterInfo.IsOutput)
{
parameters += "out ";
}
parameters += parameterInfo.FullTypeName;
parameters += " ";
parameters += parameterInfo.LocalVariableName;
}
code.CodeBlockBegin("public void Execute{0}({1})", storedProcedureInfo.Name, parameters);
code.AppendLine("BeginTransaction();");
code.AppendLine();
code.CodeBlockBegin("try");
code.CodeBlockBegin("using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(\"[{0}].[{1}]\"))", storedProcedureInfo.Schema, storedProcedureInfo.Name);
code.AppendLine("command.CommandType = System.Data.CommandType.StoredProcedure;");
code.AppendLine();
code.CodeBlockBegin("try");
code.AppendLine("PopConnection(command);");
code.AppendLine();
foreach (StoredProcedureParameterInfo parameterInfo in storedProcedureInfo.Parameters)
{
GenerateSqlParameter(code,
parameterInfo.LocalVariableName,
parameterInfo.LocalParameterVariableName,
parameterInfo.ParameterName,
parameterInfo.DbType,
parameterInfo.HasDefault,
parameterInfo.IsOutput ? System.Data.ParameterDirection.Output : System.Data.ParameterDirection.Input,
-1);
code.AppendLine();
}
code.AppendLine("command.ExecuteNonQuery();");
code.AppendLine();
foreach (StoredProcedureParameterInfo parameterInfo in storedProcedureInfo.Parameters)
{
if (parameterInfo.IsOutput == false)
{
continue;
}
code.AppendFormat("{0} = ({1}){2}.Value;", parameterInfo.LocalVariableName, parameterInfo.FullTypeName, parameterInfo.LocalParameterVariableName);
code.AppendLine();
}
// end of try
code.CodeBlockEnd();
code.CodeBlockBegin("finally");
code.Append("PushConnection(command);");
code.CodeBlockEnd();
// end of using
code.CodeBlockEnd();
// end of try
code.AppendLine();
code.Append("CommitTransaction();");
code.CodeBlockEnd();
code.CodeBlockBegin("catch");
code.AppendLine("RollbackTransaction();");
code.AppendLine("throw;");
code.CodeBlockEnd();
code.CodeBlockEnd();
}
protected override void DoRunAnalysis()
{
foreach (var table in Structure.Tables)
{
_tables[table.FullName] = table;
_tablesById[table.ObjectId] = table;
}
foreach (var view in Structure.Views)
{
_viewsById[view.ObjectId] = view;
}
var dialect = Factory.CreateDialect();
if (FilterOptions.AnyTables && IsTablesPhase)
{
Timer("tables...");
try
{
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = CreateQuery("tables.sql", tables: true);
using (var reader = cmd.ExecuteReader())
{
int modifyIndex = reader.GetOrdinal("modify_date");
int createIndex = reader.GetOrdinal("modify_date");
while (reader.Read())
{
string tname = reader.SafeString("TableName");
string schema = reader.SafeString("SchemaName");
string id = reader.SafeString("object_id");
DateTime modify = reader.GetDateTime(modifyIndex);
DateTime create = reader.GetDateTime(createIndex);
if (_tablesById.ContainsKey(id))
{
var table = _tablesById[id];
table.FullName = new NameWithSchema(schema, tname);
table.ModifyDate = modify;
}
else
{
var table = new TableInfo(Structure)
{
FullName = new NameWithSchema(schema, tname),
ObjectId = id,
ModifyDate = modify,
CreateDate = create,
};
Structure.Tables.Add(table);
_tables[table.FullName] = table;
_tablesById[table.ObjectId] = table;
}
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading tables", err);
}
Timer("columns...");
try
{
using (var cmd = Connection.CreateCommand())
{
string sql = CreateQuery("columns.sql", tables: true);
if (ServerVersion.Is_2008() && String.IsNullOrEmpty(LinkedServerName))
{
sql = sql.Replace("#2008#", ",c.is_sparse");
}
else
{
sql = sql.Replace("#2008#", "");
}
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader())
{
int sparseIndex;
try { sparseIndex = reader.GetOrdinal("is_sparse"); }
catch { sparseIndex = -1; }
while (reader.Read())
{
string tid = reader.SafeString("object_id");
if (!_tablesById.ContainsKey(tid))
{
continue;
}
var table = _tablesById[tid];
var col = new ColumnInfo(table);
col.Name = reader.SafeString("column_name");
col.NotNull = reader.SafeString("is_nullable") != "True";
col.DataType = reader.SafeString("type_name");
string dataTypeName = col.DataType;
int bytelen = reader.SafeString("max_length").SafeIntParse();
int length = 0;
if (col.DataType.ToLower().Contains("nchar") || col.DataType.ToLower().Contains("nvarchar"))
{
length = bytelen >= 0 ? bytelen / 2 : bytelen;
}
else if (col.DataType.ToLower().Contains("char") || col.DataType.ToLower().Contains("binary"))
{
length = bytelen;
}
int precision = reader.SafeString("precision").SafeIntParse();
int scale = reader.SafeString("scale").SafeIntParse();
var dt = col.DataType.ToLower();
if (dt.Contains("char") || dt.Contains("binary"))
{
if (length > 0)
{
col.DataType += $"({length})";
}
if (length < 0)
{
col.DataType += $"(max)";
}
}
if (dt.Contains("num") || dt.Contains("dec"))
{
col.DataType += $"({precision},{scale})";
}
col.DefaultValue = SimplifyExpression(reader.SafeString("default_value"));
col.DefaultConstraint = reader.SafeString("default_constraint");
col.AutoIncrement = reader.SafeString("is_identity") == "True";
col.ComputedExpression = SimplifyExpression(reader.SafeString("computed_expression"));
col.IsPersisted = reader.SafeString("is_persisted") == "True";
col.IsSparse = sparseIndex >= 0 ? reader[sparseIndex]?.ToString()?.ToLower() == "true" : false;
col.ObjectId = reader.SafeString("column_id");
col.CommonType = AnalyseType(dataTypeName, length, precision, scale);
table.Columns.Add(col);
if (String.IsNullOrWhiteSpace(col.ComputedExpression))
{
col.ComputedExpression = null;
}
if (String.IsNullOrWhiteSpace(col.DefaultValue))
{
col.DefaultValue = null;
col.DefaultConstraint = null;
}
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading columns", err);
}
try
{
Timer("primary keys...");
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = CreateQuery("primary_keys.sql", tables: true);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string table = reader.SafeString("TableName");
string schema = reader.SafeString("SchemaName");
string column = reader.SafeString("ColumnName");
string cnt = reader.SafeString("ConstraintName");
var t = _tables[new NameWithSchema(schema, table)];
t.ColumnByName(column).PrimaryKey = true;
if (t.PrimaryKey == null)
{
t.PrimaryKey = new PrimaryKeyInfo(t);
t.PrimaryKey.ConstraintName = cnt;
}
t.PrimaryKey.Columns.Add(new ColumnReference {
RefColumn = t.ColumnByName(column)
});
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading primary keys", err);
}
try
{
Timer("foreign keys...");
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = CreateQuery("foreign_keys.sql", tables: true);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string fktable = reader.SafeString("FK_Table");
string fkcolumn = reader.SafeString("FK_Column");
string fkschema = reader.SafeString("FK_Schema");
string pktable = reader.SafeString("IX_Table");
if (String.IsNullOrEmpty(pktable))
{
pktable = reader.SafeString("PK_Table");
}
string pkcolumn = reader.SafeString("IX_Column");
//if (String.IsNullOrEmpty(pkcolumn)) pkcolumn = reader.SafeString("PK_Column");
string pkschema = reader.SafeString("IX_Schema");
if (String.IsNullOrEmpty(pkschema))
{
pkschema = reader.SafeString("PK_Schema");
}
string deleteAction = reader.SafeString("Delete_Action");
string updateAction = reader.SafeString("Update_Action");
string cname = reader.SafeString("Constraint_Name");
var fkt = _tables[new NameWithSchema(fkschema, fktable)];
var pkt = _tables[new NameWithSchema(pkschema, pktable)];
var fk = fkt.ForeignKeys.Find(f => f.ConstraintName == cname);
if (fk == null)
{
fk = new ForeignKeyInfo(fkt)
{
ConstraintName = cname, RefTable = pkt
};
fkt.ForeignKeys.Add(fk);
fk.OnDeleteAction = ForeignKeyActionExtension.FromSqlName(deleteAction);
fk.OnUpdateAction = ForeignKeyActionExtension.FromSqlName(updateAction);
}
fk.Columns.Add(new ColumnReference
{
RefColumn = fkt.ColumnByName(fkcolumn)
});
fk.RefColumns.Add(new ColumnReference
{
RefColumn = pkt.ColumnByName(pkcolumn)
});
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading foreign keys", err);
}
var indexById = new Dictionary <string, ColumnsConstraintInfo>();
try
{
Timer("indexes...");
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = CreateQuery("getindexes.sql", tables: true);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string oid = reader.SafeString("object_id");
string ixname = reader.SafeString("ix_name");
string typedesc = (reader.SafeString("type_desc") ?? "").ToLower();
bool isunique = reader.SafeString("is_unique") == "True";
string indexid = reader.SafeString("index_id");
bool isUniqueConstraint = reader.SafeString("is_unique_constraint") == "True";
var table = _tablesById.Get(oid);
if (table == null)
{
continue;
}
if (isUniqueConstraint)
{
var unique = new UniqueInfo(table);
unique.ObjectId = indexid;
unique.ConstraintName = ixname;
indexById[oid + "|" + indexid] = unique;
table.Uniques.Add(unique);
}
else
{
var index = new IndexInfo(table);
index.ObjectId = indexid;
index.IsUnique = isunique;
index.ConstraintName = ixname;
switch (typedesc)
{
case "clustered":
index.IndexType = DbIndexType.Clustered;
break;
case "xml":
index.IndexType = DbIndexType.Xml;
break;
case "spatial":
index.IndexType = DbIndexType.Spatial;
break;
case "fulltext":
index.IndexType = DbIndexType.Fulltext;
break;
}
indexById[oid + "|" + indexid] = index;
table.Indexes.Add(index);
}
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading indexes", err);
}
try
{
Timer("index columns...");
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = CreateQuery("getindexcols.sql", tables: true);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string oid = reader.SafeString("object_id");
string indexid = reader.SafeString("index_id");
string colid = reader.SafeString("column_id");
bool desc = reader.SafeString("is_descending_key") == "True";
bool inc = reader.SafeString("is_included_column") == "True";
var index = indexById.Get(oid + "|" + indexid);
if (index == null)
{
continue;
}
var col = index.OwnerTable.Columns.FirstOrDefault(x => x.ObjectId == colid);
if (col == null)
{
continue;
}
index.Columns.Add(new ColumnReference
{
RefColumn = col,
IsDescending = desc,
IsIncluded = inc,
});
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading index columns", err);
}
try
{
Timer("check constraints...");
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = CreateQuery("getchecks.sql", tables: true);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string oid = reader.SafeString("object_id");
string name = reader.SafeString("name");
string def = SimplifyExpression(reader.SafeString("definition"));
var table = _tablesById.Get(oid);
if (table == null)
{
continue;
}
var check = new CheckInfo(table)
{
ConstraintName = name,
Definition = def,
};
table.Checks.Add(check);
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading check constraints", err);
}
}
var objs = new Dictionary <NameWithSchema, string>();
if ((FilterOptions.AnyFunctions || FilterOptions.AnyStoredProcedures || FilterOptions.AnyViews || FilterOptions.AnyTriggers) && (IsViewsPhase || IsFunctionsPhase))
{
try
{
Timer("sql code...");
// load code text
using (var cmd = Connection.CreateCommand())
{
string sql;
if (Phase == DatabaseAnalysePhase.All)
{
sql = CreateQuery("loadsqlcode.sql", views: true, procedures: true, functions: true, triggers: true);
sql = sql.Replace("#TYPECOND#", "1=1");
}
else
{
sql = CreateQuery("loadsqlcode.sql", views: IsViewsPhase, procedures: IsFunctionsPhase, functions: IsFunctionsPhase, triggers: IsFunctionsPhase);
var types = new List <string>();
if (IsViewsPhase)
{
types.Add("V");
}
if (IsFunctionsPhase)
{
types.Add("P");
types.Add("IF");
types.Add("FN");
types.Add("TF");
types.Add("TR");
}
sql = sql.Replace("#TYPECOND#", "s.type in (" + types.Select(x => "'" + x + "'").CreateDelimitedText(",") + ")");
}
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader())
{
NameWithSchema lastName = null;
while (reader.Read())
{
var name = new NameWithSchema(reader.SafeString("OBJ_SCHEMA"), reader.SafeString("OBJ_NAME"));
string text = reader.SafeString("CODE_TEXT") ?? "";
if (lastName != null && name == lastName)
{
objs[name] += text;
}
else
{
lastName = name;
objs[name] = text;
}
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading SQL code", err);
}
}
if (FilterOptions.AnyViews && IsViewsPhase)
{
try
{
Timer("views...");
// load views
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = CreateQuery("loadviews.sql", views: true);
using (var reader = cmd.ExecuteReader())
{
int modifyIndex = reader.GetOrdinal("modify_date");
int createIndex = reader.GetOrdinal("modify_date");
while (reader.Read())
{
var name = new NameWithSchema(reader.SafeString("Schema"), reader.SafeString("Name"));
string id = reader.SafeString("object_id");
DateTime modify = reader.GetDateTime(modifyIndex);
DateTime create = reader.GetDateTime(createIndex);
var view = new ViewInfo(Structure)
{
FullName = name,
ObjectId = id,
ModifyDate = modify,
CreateDate = create,
};
if (objs.ContainsKey(name))
{
view.CreateSql = objs[name];
}
Structure.Views.Add(view);
_viewsById[view.ObjectId] = view;
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading views", err);
}
}
if (FilterOptions.AnyTriggers && IsFunctionsPhase)
{
try
{
Timer("triggers...");
// load triggers
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = CreateQuery("gettriggers.sql", triggers: true);
using (var reader = cmd.ExecuteReader())
{
int modifyIndex = reader.GetOrdinal("modify_date");
int createIndex = reader.GetOrdinal("modify_date");
while (reader.Read())
{
var name = new NameWithSchema(reader.SafeString("schema"), reader.SafeString("name"));
string id = reader.SafeString("object_id");
string parentId = reader.SafeString("parent_id");
DateTime modify = reader.GetDateTime(modifyIndex);
DateTime create = reader.GetDateTime(createIndex);
var trg = new TriggerInfo(Structure)
{
FullName = name,
ObjectId = id,
ModifyDate = modify,
CreateDate = create,
};
if (objs.ContainsKey(name))
{
trg.CreateSql = objs[name];
}
trg.RelatedTable = _tablesById.Get(parentId);
trg.RelatedView = _viewsById.Get(parentId);
Structure.Triggers.Add(trg);
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading triggers", err);
}
}
if ((FilterOptions.AnyStoredProcedures || FilterOptions.AnyFunctions) && IsFunctionsPhase)
{
try
{
Timer("programmables...");
var programmables = new Dictionary <NameWithSchema, ProgrammableInfo>();
// load procedures and functions
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = CreateQuery("programmables.sql", procedures: true, functions: true);
using (var reader = cmd.ExecuteReader())
{
int modifyIndex = reader.GetOrdinal("modify_date");
int createIndex = reader.GetOrdinal("modify_date");
while (reader.Read())
{
var name = new NameWithSchema(reader.SafeString("schema"), reader.SafeString("name"));
string id = reader.SafeString("object_id");
DateTime modify = reader.GetDateTime(modifyIndex);
DateTime create = reader.GetDateTime(createIndex);
ProgrammableInfo info = null;
string type = reader.SafeString("type");
switch (type.Trim())
{
case "P":
info = new StoredProcedureInfo(Structure);
break;
case "FN":
info = new FunctionInfo(Structure)
{
HasTableResult = false,
};
break;
case "IF":
case "TF":
info = new FunctionInfo(Structure)
{
HasTableResult = true,
};
break;
}
if (info == null)
{
continue;
}
info.ObjectId = id;
info.CreateDate = create;
info.ModifyDate = modify;
programmables[name] = info;
info.FullName = name;
if (objs.ContainsKey(name))
{
info.CreateSql = objs[name];
}
if (info is StoredProcedureInfo)
{
Structure.StoredProcedures.Add((StoredProcedureInfo)info);
}
if (info is FunctionInfo)
{
Structure.Functions.Add((FunctionInfo)info);
}
}
}
}
Timer("parameters...");
try
{
// load parameters
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = CreateQuery("parameters.sql", procedures: true, functions: true);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var name = new NameWithSchema(reader.SafeString("SPECIFIC_SCHEMA"), reader.SafeString("SPECIFIC_NAME"));
if (!programmables.ContainsKey(name))
{
continue;
}
var prg = programmables[name];
if (reader.SafeString("IS_RESULT") == "YES")
{
var func = prg as FunctionInfo;
if (func == null)
{
continue;
}
func.ResultType = reader.SafeString("DATA_TYPE");
continue;
}
var arg = new ParameterInfo(prg);
prg.Parameters.Add(arg);
arg.DataType = reader.SafeString("DATA_TYPE");
arg.Name = reader.SafeString("PARAMETER_NAME");
arg.IsOutput = reader.SafeString("PARAMETER_MODE") == "OUT";
int precision = reader.SafeString("NUMERIC_PRECISION").SafeIntParse();
int scale = reader.SafeString("NUMERIC_SCALE").SafeIntParse();
int length = reader.SafeString("CHARACTER_MAXIMUM_LENGTH").SafeIntParse();
arg.CommonType = AnalyseType(arg.DataType, length, precision, scale);
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading parameters", err);
}
}
catch (Exception err)
{
AddErrorReport("Error loading programmables", err);
}
}
Timer("view structure...");
if (FilterOptions.AnyViews && IsViewsPhase)
{
try
{
// load view structure
foreach (var view in Structure.Views)
{
if (FilterOptions.ViewFilter != null && !FilterOptions.ViewFilter.Contains(view.ObjectId))
{
continue;
}
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = SqlServerLinkedServer.ReplaceLinkedServer("SELECT * FROM [SERVER]." + dialect.QuoteFullName(view.FullName), LinkedServerName, DatabaseName);
try
{
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
{
var queryInfo = reader.GetQueryResultInfo();
view.QueryInfo = queryInfo;
}
}
catch (Exception err)
{
view.QueryInfo = null;
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading view structure", err);
}
}
if (FilterOptions.GlobalSettings && IsSettingsPhase)
{
try
{
Timer("default schema...");
// load default schema
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = "SELECT SCHEMA_NAME()";
Structure.DefaultSchema = cmd.ExecuteScalar().ToString();
}
}
catch (Exception err)
{
AddErrorReport("Error loading defgault schema", err);
}
}
if (IsSettingsPhase)
{
try
{
Timer("schemas...");
// load schemas
using (var cmd = Connection.CreateCommand())
{
Structure.Schemas.Clear();
cmd.CommandText = CreateQuery("getschemas.sql");
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string name = reader.SafeString("name");
string id = reader.SafeString("schema_id");
Structure.Schemas.Add(new SchemaInfo(Structure)
{
ObjectId = id,
Name = name,
});
}
}
}
}
catch (Exception err)
{
AddErrorReport("Error loading schemas", err);
}
}
Timer("done...");
//Structure.FixPrimaryKeys();
}
public virtual void ChangeStoredProcedureSchema(StoredProcedureInfo obj, string newschema)
{
throw new System.NotImplementedException();
}
public virtual void RenameStoredProcedure(StoredProcedureInfo obj, string newname)
{
throw new System.NotImplementedException();
}
public override void RenameStoredProcedure(StoredProcedureInfo obj, string newname)
{
RenameObject(obj, newname);
}
