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