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