/// <summary> /// 检测数据库中对象是否存在 /// </summary> /// <param name="name">对象名称</param> /// <param name="type">对象类别</param> /// <returns></returns> public bool ExistsObject(string name, DBObjectType type) { LibDataAccess dataAccess = new LibDataAccess(); string sql = ""; switch (type) { case DBObjectType.Table: sql = string.Format(@"select count(TABLE_NAME) FROM all_tables WHERE TABLE_NAME = '{0}'", name); break; case DBObjectType.Column: string[] nameArrar = name.Split('.'); sql = string.Format(@"select column_name from user_tab_columns where table_name=upper('{0}') and column_name=upper('{0}')", nameArrar[0], nameArrar[1]); break; default: break; } string _count = dataAccess.ExecuteScalar(sql).ToString(); return(_count == "0" ? false : true); }
public override string Serialize() { var sb = new StringBuilder(); XmlWriterSettings settings = new XmlWriterSettings() { OmitXmlDeclaration = true }; using (XmlWriter w = XmlWriter.Create(sb, settings)) { w.WriteStartElement(GetType().Name); base.Serialize(w); w.WriteAttributeString("name", Name); w.WriteAttributeString("databasename", databaseName.ToString()); w.WriteAttributeString("installationcode", installationCode.ToString()); w.WriteAttributeString("relateddbobject", relatedDBObject); w.WriteAttributeString("datasourcename", datasourceName); w.WriteAttributeString("dynamicuiname", dynamicUIName); w.WriteAttributeString("tablename", tableName); w.WriteAttributeString("dbobjecttype", DBObjectType.ToString()); foreach (QChangeRequest cr in Children) { w.WriteRaw(cr.Serialize()); } w.WriteEndElement(); w.Flush(); } return(sb.ToString()); }
public static string AddSchema(string text, DBObjectType objectType, string schema, string name) { text = text.Trim(); string operationPrefix = $"CREATE {objectType.ToString()}"; if (text.StartsWith(operationPrefix, StringComparison.InvariantCultureIgnoreCase)) { if (text.StartsWith($"{operationPrefix} {schema}.{name}", StringComparison.InvariantCultureIgnoreCase)) { // add brackets. text = Regex.Replace(text, $"{operationPrefix} {schema}.{name}", $"{operationPrefix} [{schema}].[{name}]", RegexOptions.IgnoreCase); } else if (text.StartsWith($"{operationPrefix} {name}", StringComparison.InvariantCultureIgnoreCase)) { // add schema and brackets. text = Regex.Replace(text, $"{operationPrefix} {name}", $"{operationPrefix} [{schema}].[{name}]", RegexOptions.IgnoreCase); } else if (text.StartsWith($"{operationPrefix} [{name}]", StringComparison.InvariantCultureIgnoreCase)) { // add schema. text = Regex.Replace(text, $"{operationPrefix} \\[{name}\\]", $"{operationPrefix} [{schema}].[{name}]", RegexOptions.IgnoreCase); } else if (text.StartsWith($"{operationPrefix} [{schema}].[{name}]", StringComparison.InvariantCultureIgnoreCase)) { // add schema. text = Regex.Replace(text, $"{operationPrefix} \\[{schema}\\].\\[{name}\\]", $"{operationPrefix} [{schema}].[{name}]", RegexOptions.IgnoreCase); } } return(text); }
/// <summary> /// 检测数据库中对象是否存在 /// </summary> /// <param name="name">对象名称</param> /// <param name="type">对象类别</param> /// <returns></returns> public bool ExistsObject(string name, DBObjectType type) { LibDataAccess dataAccess = new LibDataAccess(); string sql = ""; switch (type) { case DBObjectType.Table: sql = string.Format(@"SELECT name FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}]') AND type in (N'U')", name); break; case DBObjectType.Column: string[] nameArrar = name.Split('.'); sql = string.Format(@"SELECT name FROM sys.syscolumns WHERE name='{0}' and id=(SELECT MAX(id) FROM sys.objects WHERE object_id = OBJECT_ID(N'[{1}]') AND type in (N'U'))", nameArrar[1], nameArrar[0]); break; default: break; } string _name = dataAccess.ExecuteScalar(sql) as string; return(string.IsNullOrEmpty(_name) ? false : true); }
/// <summary> /// Gets the SQL objects. /// </summary> /// <param name="query">The query.</param> /// <param name="database">The database.</param> /// <returns></returns> private static List <SqlObject> GetSqlObjects(string query, string database, DBObjectType objectType) { var result = new List <SqlObject>(); var dbName = DatabaseService.GetCurrentDatabase(database); using (var dtObjectNames = DatabaseService.GetDataTable(query, database)) { foreach (DataRow row in dtObjectNames.Rows) { var s = new SqlObject(); s.Name = row["Name"].ToString().Trim(); s.Schema = row["Schema"].ToString().Trim(); s.Text = row["ProcedureText"].ToString(); s.DBName = dbName; s.ObjectType = objectType; s.Text = AddSchema(s.Text, s.ObjectType, s.Schema, s.Name); result.Add(s); } } return(result); }
public SqlObject() { Schema = "dbo"; Name = string.Empty; Text = string.Empty; DBName = string.Empty; ObjectType = DBObjectType.PROCEDURE; }
public static int EnsureKey(this IKeyMappingService mapping, DBObjectType ownerType, int ownerID) { var key = mapping.ResolveToServerKey(ownerType, ownerID); if (!key.HasValue) throw new KeyNotFoundException(string.Format("no Mapping for type {0}, id {1} found", ownerType, ownerID) ); else return key.Value; }
public void AddSchema_function_nochanges_brackets() { string expected = @"CREATE FUNCTION [dbo].[FY] ( @date datetime) returns integer as begin declare @retval integer, @fystartmonth tinyint set @fystartmonth=7 if @date is null set @retval=null else set @retval=(year(@date) + case when month(@date)>=@fystartmonth then 1 else 0 end) return @retval end"; string text = @" CREATE function [dbo].[FY] ( @date datetime) returns integer as begin declare @retval integer, @fystartmonth tinyint set @fystartmonth=7 if @date is null set @retval=null else set @retval=(year(@date) + case when month(@date)>=@fystartmonth then 1 else 0 end) return @retval end "; DBObjectType objectType = DBObjectType.FUNCTION; string schema = "dbo"; string name = "FY"; var result = MSSQL_Database_Tools.Core.Services.DatabaseService.AddSchema(text, objectType, schema, name); Assert.AreEqual(expected, result); }
/// <summary> /// Get array of ClrProperty from specified schema, database object and database object type. /// </summary> /// <param name="schema">Database schema.</param> /// <param name="dbObjectName">Database object name.</param> /// <param name="dbObjectType">Database object type name.</param> /// <returns>Array of ClrProperty.</returns> public ClrProperty[] GetClrProperties(string schema, string dbObjectName, DBObjectType dbObjectType) { SqlColumn[] sqlColumns = null; switch (dbObjectType) { case DBObjectType.Table: case DBObjectType.Views: case DBObjectType.Functions: sqlColumns = GetSqlColumns(schema, dbObjectName); break; case DBObjectType.StoredProcedure: //sqlColumns = GetSqlColumnsForStoredProcedure(schema, dbObjectName); break; return(GetClrPropertyForStoredProcedure(schema, dbObjectName)); case DBObjectType.UserDefinedTableTypes: sqlColumns = GetSqlColumnsForTableTypes(schema, dbObjectName); break; default: return(null); } if (sqlColumns == null) { return(null); } List <ClrProperty> list = new List <ClrProperty>(sqlColumns.Length); foreach (var sqlCol in sqlColumns) { list.Add(new ClrProperty() { Name = sqlCol.Name, PropertyType = GetClrType(sqlCol.SqlType, sqlCol.IsNullable), }); } return(list.ToArray()); }
/// <summary> /// 生成数据库对象Drop语句 /// </summary> /// <param name="dbName">数据库名称</param> /// <param name="dbObjectName">数据库对象名称</param> /// <param name="dbobjType">数据库对象类型(表、视图、存储过程、函数等)</param> /// <returns></returns> public string GetSqlDrop(string dbName, string dbObjectName, DBObjectType dbobjType) { string objType = string.Empty; switch (dbobjType) { case DBObjectType.TABLE: objType = "TABLE"; break; case DBObjectType.VIEW: objType = "VIEW"; break; case DBObjectType.PROCEDURE: objType = "PROCEDURE"; break; case DBObjectType.FUNCTION: objType = "FUNCTION"; break; case DBObjectType.USER: objType = "USER"; break; default: objType = string.Empty; break; } this.DbName = dbName; this.TableName = dbObjectName; StringPlus strsql = new StringPlus(); strsql.AppendLine("/****** Object: " + objType + " " + dbObjectName + " Script Date: " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "******/"); strsql.AppendLine("DROP " + objType + " " + dbObjectName); return(strsql.Value); }
/// <summary> /// Gets the return fields of the first result set of a procecure /// </summary> /// <param name="model">The procedure</param> /// <param name="persistSPResultSetPath">True to save those result sets in ResultSets Folder</param> /// <param name="fallBackSPExecutionParameters">If you set this parameter and sp_describe_first_result_set does not work, /// the procedure will get executed to retrieve results. Pay attention to provide wise options!</param> /// <returns></returns> public Task <IReadOnlyCollection <SqlFieldDescription> > GetResultSet(DbConnection dbConnection, DBObjectName model, DBObjectType dBObjectType, string?persistSPResultSetPath, IReadOnlyDictionary <string, object?>?fallBackSPExecutionParameters = null) { switch (dBObjectType) { case DBObjectType.StoredProcedure: return(GetSPResultSet(dbConnection, model, persistSPResultSetPath, fallBackSPExecutionParameters)); case DBObjectType.TableOrView: return(GetTableOrViewFields(dbConnection, model)); case DBObjectType.TableType: return(GetTableTypeFields(dbConnection, model)); case DBObjectType.TableValuedFunction: return(GetFunctionFields(dbConnection, model)); default: throw new NotSupportedException($"Db Type {dBObjectType} not supported"); } }
private void DumpObjectInfos() { if (HostServicesSingleton.HostServices == null || HostServicesSingleton.HostServices.ObjectExplorerService == null) { throw new InvalidOperationException("ObjectExplorer is not active or visible!"); } IList <ObjectExplorerNode> selNodes = HostServicesSingleton.HostServices.ObjectExplorerService.SelNodes; IDictionary <string, StringBuilder> dumpJobs = new Dictionary <string, StringBuilder>(); StringBuilder sb = null; string template = "{0}=[{1}].[{2}]"; string objType = String.Empty; string dbInfo = String.Empty; foreach (ObjectExplorerNode node in selNodes) { if (!DBObjectType.CanTypeBeDumpedForScriptingWizardUsage(node.Type)) { continue; } if (node.ConnParams == null) { continue; } dbInfo = node.DatabaseName + " on " + node.ConnParams.Server; if (dumpJobs.ContainsKey(dbInfo)) { sb = dumpJobs[dbInfo]; } else { sb = new StringBuilder(); dumpJobs.Add(dbInfo, sb); } objType = DbObjectListUtils.EncodeObjectExplorerNodeType(node.Type); sb.AppendLine(String.Format(template, objType, node.Owner, node.Name)); } string editorCaption = String.Empty; string errors = String.Empty; foreach (string info in dumpJobs.Keys) { sb = dumpJobs[info]; try { editorCaption = "Objects [" + info + "]"; HostServicesSingleton.HostServices.EditorServices.CreateTextEditor(editorCaption, sb.ToString()); } catch (Exception ex) { errors += "Error Type: " + ex.GetType().Name + ", Message: " + ex.Message + "\r\n"; } } if (!String.IsNullOrEmpty(errors)) { GenericErrorDialog.ShowError("Dump Error", "Some errors occured during object dump.", errors); } }
public void Instructions(DBObjectType objectType, DBAction action) { RenderText(scriptsGenerator.InstructionsForAutomaticRollback(objectType, action)); }