private string ConvertSqlObjectType(SqlObjectType SqlObjectType) { if (SqlObjectType == SqlObjectType.Function) { return("FN' OR type='TF"); } else if (SqlObjectType == SqlObjectType.StoredProcedure) { return("P"); } else if (SqlObjectType == SqlObjectType.Table) { return("U"); } else if (SqlObjectType == SqlObjectType.TableType) { return("TT"); } else { throw new NotImplementedException("Unexpected SqlObjectType value"); } // all types - http://technet.microsoft.com/ru-ru/library/ms177596.aspx //FN = scalar function //TF = table function //string query = "select * from sysobjects where type='FN" + "' OR type='TF" + "'"; //FN' OR type='TF }
internal SqlStoredProcedure(string name, string owner, SqlObjectType storedProcedureType, DateTime createDate) { this.name = name; this.owner = owner; this.storedProcedureType = storedProcedureType; this.createDate = createDate; }
/// <summary> /// Initializes a new instance of the <see cref="TableDefinitionData" /> class. /// </summary> /// <param name="type">The type.</param> /// <param name="schemaID">The schema identifier.</param> /// <param name="name">The name.</param> /// <param name="ordinal">The ordinal.</param> /// <param name="columnName">Name of the column.</param> /// <param name="columnType">Type of the column.</param> /// <param name="columnSize">Size of the column.</param> /// <param name="isNullable">if set to <see langword="true" /> [is nullable].</param> /// <param name="tableTypeID">The ID of the associated <see cref="SqlType"/> if this table defines a <see cref="SqlType"/>.</param> public TableDefinitionData( SqlObjectType type, int schemaID, [NotNull] string name, int ordinal, [NotNull] string columnName, [NotNull] SqlType columnType, SqlTypeSize columnSize, bool isNullable, int?tableTypeID) { if (name == null) { throw new ArgumentNullException("name"); } if (columnName == null) { throw new ArgumentNullException("columnName"); } if (columnType == null) { throw new ArgumentNullException("columnType"); } Type = type; SchemaID = schemaID; Name = name; Column = new SqlColumn(ordinal, columnName, columnType, columnSize, isNullable); TableTypeID = tableTypeID; }
private void ExecuteOfTypeIfNotExists(DbContext dbContext, SqlObjectType sqlObjectType) { List <SqlScript> filteredScripts = Scripts .Where(p => p.SqlObjectType == sqlObjectType).ToList(); if (filteredScripts.Count > 0) { List <string> existingNames = GetSqlNamesOfType(dbContext, sqlObjectType); foreach (SqlScript script in filteredScripts) { try { if (!existingNames.Contains(script.SqlObjectName)) { dbContext.Database.ExecuteSqlRaw(script.ScriptText); } } catch (Exception exception) { string message = exception.Message + $" Executing script: {script.ScriptText}"; throw new Exception(message); } } } }
// Using this method is more efficient than checking for each object with CheckExistObjectInDatabase public bool CheckObjectsExistInDatabase(SqlObjectType type, ICollection <string> objectNames, bool checkCount) { if (objectNames == null) { throw new ArgumentNullException("objectNames"); } string format = "SELECT * FROM sys.objects WHERE type = '{0}'"; string sqlScript = string.Format(format, SqlObjectTypeToString(type)); List <string> results = ExecuteSqlStringQuery(sqlScript, this.Connection); HashSet <string> resultSet = new HashSet <string>(results); bool ret = true; foreach (string name in objectNames) { if (!resultSet.Contains(name)) { //Log.TraceInternal("[SqlInstance] Could not find sql object: name='{0}', type='{1}'", name, type.ToString()); ret = false; } } if (checkCount && results.Count != objectNames.Count) { //Log.TraceInternal("[SqlInstance] CheckCount mismatch: expected={0}, actual={1}", objectNames.Count, results.Count); ret = false; } return(ret); }
private static async Task <AssessmentResult <TResult> > CallAssessment <TResult>( string methodName, SqlObjectType sqlObjectType, LiveConnectionHelper.TestConnectionResult liveConnection) where TResult : AssessmentItemInfo { var connInfo = liveConnection.ConnectionInfo; AssessmentResult <TResult> response; using (var service = new SqlAssessmentService( TestServiceProvider.Instance.ConnectionService, TestServiceProvider.Instance.WorkspaceService)) { AddTestRules(service); string randomUri = Guid.NewGuid().ToString(); AssessmentParams requestParams = new AssessmentParams { OwnerUri = randomUri, TargetType = sqlObjectType }; ConnectParams connectParams = new ConnectParams { OwnerUri = requestParams.OwnerUri, Connection = connInfo.ConnectionDetails, Type = ConnectionType.Default }; var methodInfo = typeof(SqlAssessmentService).GetMethod( methodName, BindingFlags.Instance | BindingFlags.NonPublic); Assert.NotNull(methodInfo); var func = (AssessmentMethod <TResult>)Delegate.CreateDelegate( typeof(AssessmentMethod <TResult>), service, methodInfo); response = await service.CallAssessmentEngine <TResult>( requestParams, connectParams, randomUri, t => func(t)); } Assert.NotNull(response); if (response.Success) { Assert.All( response.Items, i => { Assert.Equal(sqlObjectType, i.TargetType); Assert.Contains(i.Level, AllowedSeverityLevels); }); } return(response); }
internal SqlTable(string name, string owner, SqlObjectType tableType, DateTime createDate) { this.name = name; this.owner = owner; this.tableType = tableType; this.createDate = createDate; }
/// <summary> /// Initializes a new instance of the <see cref="ProgramDefinitionData" /> class. /// </summary> /// <param name="type">The type.</param> /// <param name="schemaID">The schema identifier.</param> /// <param name="name">The name.</param> /// <param name="ordinal">The ordinal.</param> /// <param name="parameterName">Name of the parameter.</param> /// <param name="parameterType">Type of the parameter.</param> /// <param name="parameterSize">Size of the parameter.</param> /// <param name="parameterDirection">The parameter direction.</param> /// <param name="isReadonly">if set to <see langword="true" /> [is readonly].</param> public ProgramDefinitionData( SqlObjectType type, int schemaID, [NotNull] string name, int ordinal, [NotNull] string parameterName, [NotNull] SqlType parameterType, SqlTypeSize parameterSize, ParameterDirection parameterDirection, bool isReadonly) { if (name == null) { throw new ArgumentNullException("name"); } if (parameterName == null) { throw new ArgumentNullException("parameterName"); } if (parameterType == null) { throw new ArgumentNullException("parameterType"); } Type = type; SchemaID = schemaID; Name = name; Parameter = new SqlProgramParameter( ordinal, parameterName, parameterType, parameterSize, parameterDirection, isReadonly); }
/// <summary> /// Initializes a new instance of the <see cref="SqlView"/> class. /// </summary> /// <param _name="_name">The _name.</param> /// <param _name="_owner">The _owner.</param> /// <param _name="viewType">Type of the view.</param> /// <param _name="createDate">The create date.</param> internal SqlView(string name, string owner, SqlObjectType viewType, DateTime createDate) { _name = name; _owner = owner; _viewType = viewType; _createDate = createDate; }
public SqlObjectInfo(int schemaId, string schemaName, int objectId, string name, SqlObjectType type) { SchemaId = schemaId; SchemaName = schemaName; ObjectId = objectId; ObjectName = name; Type = type; }
private void GenerateAll(GenerationType generateType, SqlObjectType type, Type subType, string keyword) { var subTypes = Enum.GetValues(subType); foreach (var value in subTypes) { GenerateInternal(generateType, type, value, keyword); } }
/// <summary> /// Initializes a new instance of the <see cref="ProgramDefinitionData" /> class. /// </summary> /// <param name="type">The type.</param> /// <param name="schemaID">The schema identifier.</param> /// <param name="name">The name.</param> public ProgramDefinitionData( SqlObjectType type, int schemaID, [NotNull] string name) { if (name == null) throw new ArgumentNullException("name"); Type = type; SchemaID = schemaID; Name = name; }
public override string GetPath(SqlObjectType type, object subType = null) { var key = GetKey(type, subType); var result = !string.IsNullOrEmpty(key) ? GetOption(key, "").Replace("{BasePath}", BasePath) : ""; if (string.IsNullOrEmpty(result)) { result = base.GetPath(type, subType); } return(result); }
/// <summary> /// SQLSecure 3.1 (Barkha Khatri) creating permission query for Azure SQL DB using its predefined types /// </summary> /// <param name="otype">object type</param> /// <param name="oidbatch">major ids</param> /// <returns></returns> private static string createPermissionQueryAzureDB( SqlObjectType otype, List <int> oidbatch ) { Debug.Assert(oidbatch != null && oidbatch.Count > 0); StringBuilder query = new StringBuilder(); query.Append(@"SELECT classid = CAST (class AS int), major_id, minor_id, grantee_principal_id, grantor_principal_id, type, permission_name, isgrant = CASE WHEN state = 'G' THEN 'Y' WHEN state = 'W' THEN 'Y' ELSE 'N' END, iswithgrant = CASE WHEN state = 'W' THEN 'Y' ELSE 'N' END, isrevoke = CASE WHEN state = 'R' THEN 'Y' ELSE 'N' END, isdeny = CASE WHEN state = 'D' THEN 'Y' ELSE 'N' END FROM sys.database_permissions WHERE grantee_principal_id in ( select p.principal_id from sys.database_principals p inner join sys.sql_logins l on ((p.sid=l.sid) or p.type='E' or p.type='X')) and class = "); switch (otype) { case SqlObjectType.Database: query.Append("0"); break; case SqlObjectType.DatabasePrincipal: query.Append("4"); break; default: Debug.Assert(false); break; } query.Append("AND major_id IN ( "); for (int i = 0; i < oidbatch.Count; ++i) { query.Append(oidbatch[i].ToString()); query.Append((i == (oidbatch.Count - 1)) ? " )" : ", "); } return(query.ToString()); }
//execution public void ExecuteScripts(DbContext dbContext) { //replace fragments foreach (SqlScript script in Scripts) { if (script.Replacement == null) { continue; } foreach (KeyValuePair <string, string> item in script.Replacement) { if (script.ScriptText != null) { script.ScriptText = script.ScriptText.Replace(item.Key, item.Value); } if (script.SqlObjectName != null) { script.SqlObjectName = script.SqlObjectName.Replace(item.Key, item.Value); } } } // execute scripts bool connectionOpened = false; DbConnection connection = dbContext.Database.GetDbConnection(); if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); connectionOpened = true; } foreach (KeyValuePair <SqlObjectType, bool> sqlTypeOrder in _executeOrder) { SqlObjectType scriptType = sqlTypeOrder.Key; if (sqlTypeOrder.Value) { ExecuteOfTypeIfNotExists(dbContext, scriptType); } else { ExecuteOfType(dbContext, scriptType); } } if (connectionOpened) { connection.Close(); } }
public override bool Execute() { Trace.WriteIf(traceSwitch.TraceVerbose, "DBScriptTask invoked"); SqlObjectType objectTypes = ParseObjectTypeString(ObjectsToScript); SqlServerHelper.ScriptDB(ServerName, DatabaseName, DestinationDir, DependencyFileName, objectTypes, ObjectSaveOptions.AddObjectTypePrefix | ObjectSaveOptions.SaveEachObjectTypeToSeparateDir, this.Log); return(!Log.HasLoggedErrors); }
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; }
/// <summary> /// Initializes a new instance of the <see cref="ProgramDefinitionData" /> class. /// </summary> /// <param name="type">The type.</param> /// <param name="schemaID">The schema identifier.</param> /// <param name="name">The name.</param> public ProgramDefinitionData( SqlObjectType type, int schemaID, [NotNull] string name) { if (name == null) { throw new ArgumentNullException("name"); } Type = type; SchemaID = schemaID; Name = name; }
private Dictionary <string, int> CountAll(SqlObjectType type, Type subType, string keyword) { var result = new Dictionary <string, int>(); var subTypes = Enum.GetValues(subType); foreach (var value in subTypes) { var kv = CountInternal(type, value, keyword); result.Add(kv.Key, kv.Value); } return(result); }
public bool CheckNumberOfObjectsInDatabase(SqlObjectType type, int expected) { string format = "SELECT count(*) FROM sys.objects WHERE type = '{0}'"; string sqlScript = string.Format(format, SqlObjectTypeToString(type)); int count = ExecuteSqlStringScalarCount(format); if (count != expected) { //Log.TraceInternal("[SqlInstance] CheckNumberObjects mismatch, count: " + count + "; Expected: " + expected); return(false); } return(true); }
public static string GetTypeName(SqlObjectType type) { switch (type) { case SqlObjectType.Function: return("function"); case SqlObjectType.StoredProcedure: return("procedure"); case SqlObjectType.Table: return("table"); case SqlObjectType.View: return("view"); default: throw new InvalidOperationException($"Unknown Sql Object type: {type}"); } }
// ------------- Ctors ------------- #region Ctors public Rule( SqlInt32 ruleId, SqlInt32 objectType, SqlString scope, SqlString matchString ) { m_RuleId = ruleId; m_ObjectType = objectType; m_Scope = scope; m_MatchString = matchString; m_ObjectTypeEnum = getObjectClass(m_ObjectType.Value); m_ScopeEnum = getScope(m_Scope.Value); m_WildMatch = new Utility.WildMatch(m_MatchString.Value); }
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 string SqlObjectTypeToString(SqlObjectType type) { switch (type) { case SqlObjectType.Table: return("U"); case SqlObjectType.View: return("V"); case SqlObjectType.StoredProcedure: return("P"); default: throw new ArgumentException("SqlObjectType is not supported", "type"); } }
/// <summary> /// Initializes a new instance of the <see cref="SqlProgramDefinition" /> class. /// </summary> /// <param name="type">The type of program.</param> /// <param name="sqlSchema">The schema.</param> /// <param name="name">The <see cref="SqlProgramDefinition.Name">program name</see>.</param> /// <param name="parameters">The parameters.</param> internal SqlProgramDefinition( SqlObjectType type, [NotNull] SqlSchema sqlSchema, [NotNull] string name, [NotNull] params SqlProgramParameter[] parameters) : base(sqlSchema, name) { if (parameters == null) { throw new ArgumentNullException("parameters"); } Type = type; Name = name; Parameters = parameters; // ReSharper disable once PossibleNullReferenceException _parametersByName = parameters.ToDictionary(p => p.FullName, StringComparer.InvariantCultureIgnoreCase); }
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); }
//get existing db objects private List <string> GetSqlNamesOfType(DbContext dbContext, SqlObjectType sqlObjectType) { if (sqlObjectType == SqlObjectType.Index) { return(GetSqlNamesOfIndexes(dbContext)); } string sqlTypeString = ConvertSqlObjectType(sqlObjectType); string query = "select * from sysobjects where type='" + sqlTypeString + "'"; List <string> existingItems = new List <string>(); SqlConnection connection = (SqlConnection)dbContext.Database.GetDbConnection(); using (SqlCommand command = new SqlCommand(query, connection)) using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { if (!reader.IsDBNull(0)) { string nameString = (string)reader[0]; existingItems.Add(nameString.ToLower()); } } } //Table Type get actual names Regex tableTypeRegex = new Regex(@"tt_([\d\w]+)_[\d\w]+", RegexOptions.IgnoreCase); if (sqlObjectType == SqlObjectType.TableType) { for (int i = 0; i < existingItems.Count; i++) { Match match = tableTypeRegex.Match(existingItems[i]); if (match != null) { existingItems[i] = match.Groups[1].Value; } } } return(existingItems); }
public bool CheckExistObjectInDatabase(SqlObjectType type, string objectName) { if (string.IsNullOrEmpty(objectName)) { throw new ArgumentException("Object name can not be null or empty", "objectName"); } string format = "SELECT count(*) FROM sys.objects WHERE type = '{0}' and name = N'{1}'"; string sqlScript = string.Format(format, SqlObjectTypeToString(type), objectName); int count = ExecuteSqlStringScalarCount(sqlScript); if (count != 1) { //Log.TraceInternal("[SqlInstance] CheckExist count: " + count + "; SqlScript: " + sqlScript); return(false); } return(true); }
/// <summary> /// Проверяет учет объектов указанного типа в скрипте /// </summary> /// <param name="type"></param> /// <returns></returns> public bool Supports(SqlObjectType type) { if (IncludeSqlObjectTypes.HasFlag(type)) { return(true); } if (ExcludeSqlObjectTypes.HasFlag(type)) { return(false); } if (IncludeSqlObjectTypes != SqlObjectType.All) { return(false); } if (ExcludeSqlObjectTypes == SqlObjectType.All) { return(false); } return(true); }
private void ExecuteOfType(DbContext dbContext, SqlObjectType sqlObjectType) { List <SqlScript> filteredScripts = Scripts .Where(p => p.SqlObjectType == sqlObjectType).ToList(); if (filteredScripts.Count > 0) { foreach (SqlScript script in filteredScripts) { try { dbContext.Database.ExecuteSqlRaw(script.ScriptText); } catch (Exception exception) { string message = exception.Message + $" Executing script: {script.ScriptText}"; throw new Exception(message); } } } }
/// <summary> /// Initializes a new instance of the <see cref="SqlTableDefinition" /> class. /// </summary> /// <param name="type">The object <see cref="Type">type</see>.</param> /// <param name="sqlSchema">The schema.</param> /// <param name="name">The table/view name.</param> /// <param name="columns">The columns.</param> /// <param name="tableType">Type of the table.</param> internal SqlTableDefinition( SqlObjectType type, [NotNull] SqlSchema sqlSchema, [NotNull] string name, [NotNull] SqlColumn[] columns, [CanBeNull] SqlTableType tableType) : base(sqlSchema, name) // ReSharper restore PossibleNullReferenceException { if (columns == null) { throw new ArgumentNullException("columns"); } Type = type; Name = name; TableType = tableType; Columns = columns; Dictionary <string, SqlColumn> columnsByName = new Dictionary <string, SqlColumn>( columns.Length, StringComparer.InvariantCultureIgnoreCase); _columnsByName = columnsByName; SqlMetaData = new SqlMetaData[columns.Length]; if (tableType != null) { tableType.TableDefinition = this; } int i = 0; foreach (SqlColumn column in columns) { Debug.Assert(column != null); SqlMetaData[i++] = column.SqlMetaData; columnsByName[column.FullName] = column; } }
/// <summary> /// Initializes a new instance of the <see cref="ProgramDefinitionData" /> class. /// </summary> /// <param name="type">The type.</param> /// <param name="schemaID">The schema identifier.</param> /// <param name="name">The name.</param> /// <param name="ordinal">The ordinal.</param> /// <param name="parameterName">Name of the parameter.</param> /// <param name="parameterType">Type of the parameter.</param> /// <param name="parameterSize">Size of the parameter.</param> /// <param name="parameterDirection">The parameter direction.</param> /// <param name="isReadonly">if set to <see langword="true" /> [is readonly].</param> public ProgramDefinitionData( SqlObjectType type, int schemaID, [NotNull] string name, int ordinal, [NotNull] string parameterName, [NotNull] SqlType parameterType, SqlTypeSize parameterSize, ParameterDirection parameterDirection, bool isReadonly) { if (name == null) throw new ArgumentNullException("name"); if (parameterName == null) throw new ArgumentNullException("parameterName"); if (parameterType == null) throw new ArgumentNullException("parameterType"); Type = type; SchemaID = schemaID; Name = name; Parameter = new SqlProgramParameter( ordinal, parameterName, parameterType, parameterSize, parameterDirection, isReadonly); }
private DatabaseObject(string name, SqlObjectType type) { this.name = name; this.type = type; }
/// <summary> /// Initializes a new instance of the <see cref="TableDefinitionData" /> class. /// </summary> /// <param name="type">The type.</param> /// <param name="schemaID">The schema identifier.</param> /// <param name="name">The name.</param> /// <param name="ordinal">The ordinal.</param> /// <param name="columnName">Name of the column.</param> /// <param name="columnType">Type of the column.</param> /// <param name="columnSize">Size of the column.</param> /// <param name="isNullable">if set to <see langword="true" /> [is nullable].</param> /// <param name="tableTypeID">The ID of the associated <see cref="SqlType"/> if this table defines a <see cref="SqlType"/>.</param> public TableDefinitionData( SqlObjectType type, int schemaID, [NotNull] string name, int ordinal, [NotNull] string columnName, [NotNull] SqlType columnType, SqlTypeSize columnSize, bool isNullable, int? tableTypeID) { if (name == null) throw new ArgumentNullException("name"); if (columnName == null) throw new ArgumentNullException("columnName"); if (columnType == null) throw new ArgumentNullException("columnType"); Type = type; SchemaID = schemaID; Name = name; Column = new SqlColumn(ordinal, columnName, columnType, columnSize, isNullable); TableTypeID = tableTypeID; }
/// <summary>Initializes a new instance of <b>SqlObjectHeader</b> class.</summary> /// <param name="objectName">Object name.</param> /// <param name="objectType">Object type: table, view, stored procedure...</param> public SqlObjectHeader(string objectName, SqlObjectType objectType) { this.Name = objectName; this.Type = objectType; }
public static string GetCode(SqlObjectType type) { return dict2[type]; }