/// <summary> /// /// </summary> /// <param name="name"></param> /// <typeparam name="TSql"></typeparam> /// <returns></returns> public TSql GetObject <TSql>(string name) where TSql : SqlObject { return (SqlObjects.OfType <TSql>().FirstOrDefault( _ => _.Name.ToLowerInvariant() == name.ToLowerInvariant() || _.FullName.ToLowerInvariant() == name.ToLowerInvariant())); }
/// <summary> /// /// </summary> /// <param name="name"></param> /// <returns></returns> public SqlObject GetObject(string name) { return (SqlObjects.FirstOrDefault( _ => _.Name.ToLowerInvariant() == name.ToLowerInvariant() || _.FullName.ToLowerInvariant() == name.ToLowerInvariant())); }
public static IEnumerable <SqlObjects> getSQLFunctionsList(string vOdbcName) { var vTables = SqlReadDataValue(vOdbcName, in2SqlLibrary.getSQLFunctions(getODBCProperties(vOdbcName, "DBType"))); foreach (var vCurrTable in vTables) { SqlObjects vTable = new SqlObjects(); vTable.Name = vCurrTable.ToString(); vTable.idTbl = vIdtbl; vIdtbl = vIdtbl + 1; yield return(vTable); } }
public static IEnumerable <SqlObjects> getViewList(string vOdbcName) { var vViews = SqlReadDataValue(vOdbcName, in2SqlLibrary.getSqlViews(getODBCProperties(vOdbcName, "DBType"))); foreach (var vCurrView in vViews) { SqlObjects vView = new SqlObjects(); vView.Name = vCurrView.ToString(); vView.idTbl = vIdtbl; vIdtbl = vIdtbl + 1; yield return(vView); } }
public static async Task <MultiReader> ExecuteMultiResultStoredProcedure(this SqlConnection dbConnection, string sqlStatement, params SqlParameter[] sqlParameters) { var dataSet = await SqlObjects.GetDataSet(dbConnection, sqlStatement, true, sqlParameters); return(new MultiReader(dataSet)); }
public void AddSqlObject(SqlObject sqlObject) { SqlObjects.Add(sqlObject); }
//private void initSqlExpressionDependencies(int serverObjectId, int databaseId) //{ // try // { // var serverObjectName = SqlServerObjects.First(so => so.ServerObjectId == serverObjectId).ServerObjectName; // var databaseName = SqlDatabases.First(d => d.ServerObjectId == serverObjectId && d.DatabaseId == databaseId).DatabaseName; // MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Loading expression dependencies from server object '{0}', database '{1}'... ", serverObjectName, databaseName)); // int counter = 0; // DateTime dt = DateTime.Now; // string query = QueryReader.Read("expression_dependencies.sql"); // ConnectionPool.GetConnection(serverObjectId).ChangeDatabase(databaseName); // SqlCommand cmd = (SqlCommand)ConnectionPool.GetConnection(serverObjectId).CreateCommand(); // cmd.CommandText = query; // cmd.CommandTimeout = UserSettings.Default.CommandTimeout; // cmd.CommandType = CommandType.Text; // SqlDataReader rdr = cmd.ExecuteReader(); // while (rdr.Read()) // { // counter++; // int referencedId = -1; // int.TryParse(rdr["referenced_id"].ToString(), out referencedId); // _sqlExpressionDependencies.Add(new MySqlExpressionDependency // { // ServerObjectId = serverObjectId, // ServerObjectName = serverObjectName, // DatabaseId = databaseId, // DatabaseName = databaseName, // ReferencingId = (int)rdr["referencing_id"], // ReferencedServerName = (string)rdr["referenced_server_name"], // ReferencedDatabaseName = (string)rdr["referenced_database_name"], // ReferencedSchemaName = (string)rdr["referenced_schema_name"], // ReferencedEntityName = (string)rdr["referenced_entity_name"], // ReferencedId = (referencedId != -1) ? referencedId : (int?)null // }); // } // rdr.Close(); // MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} expression dependencies.", counter), (DateTime.Now - dt).TotalSeconds); // } // catch (Exception ex) // { // MyOutput.NewMessage(EOutputMessageType.ERROR, string.Format("Error loading expression dependencies: {0}", ex.Message)); // var exForm = new ExceptionForm(ex); // exForm.ShowDialog(); // } //} private void initSqlExpressionDependencies(int serverObjectId, int databaseId) { var serverObjectName = SqlServerObjects.First(so => so.ServerObjectId == serverObjectId).ServerObjectName; var databaseName = SqlDatabases.First(d => d.ServerObjectId == serverObjectId && d.DatabaseId == databaseId).DatabaseName; MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Loading expression dependencies from server '{0}', database '{1}'... ", serverObjectName, databaseName)); int initialNumberOfRows = SqlExpressionDependencies.Length; DateTime dt = DateTime.Now; //DateTime timeCounterInner; DataTable ExpressionDependenciesDataTable = new DataTable(); string query = QueryReader.Read("expression_dependencies.sql"); var svr = SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId); var db = SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId && o.DatabaseId == databaseId); if (svr == null || db == null) { MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} dependencies.", SqlExpressionDependencies.Length - initialNumberOfRows), (DateTime.Now - dt).TotalSeconds); return; } ConnectionPool.GetConnection(serverObjectId).ChangeDatabase(databaseName); SqlCommand cmd = ConnectionPool.GetConnection(serverObjectId).CreateCommand(); cmd.CommandText = query; cmd.CommandTimeout = UserSettings.Default.CommandTimeout; cmd.CommandType = CommandType.Text; ExpressionDependenciesDataTable.Load(cmd.ExecuteReader()); //var results = from myRow in dataTable.AsEnumerable() // select new // { // ReferencingId = Convert.ToInt32(myRow["referencing_id"]), // ReferencedId = Convert.ToInt32(((myRow["referenced_id"] != DBNull.Value) ? Convert.ToInt32(myRow["referenced_id"]) : -1)), // ReferencedSchemaId = _objectList.First(o => o.ServerName.Equals(serverName, StringComparison.InvariantCultureIgnoreCase) // && o.DatabaseName.Equals(databaseName, StringComparison.InvariantCultureIgnoreCase) // && o.SchemaName.Equals((string)myRow["referenced_schema_name"], StringComparison.InvariantCultureIgnoreCase)).SchemaId, // ReferencedEntityName = (string)myRow["referenced_entity_name"] // }; var databaseDependencies = from myRow in ExpressionDependenciesDataTable.AsEnumerable() select new { ReferencingId = Convert.ToInt32(myRow["referencing_id"]), ReferencedId = Convert.ToInt32(((myRow["referenced_id"] != DBNull.Value) ? Convert.ToInt32(myRow["referenced_id"]) : -1)), ReferencedSchemaId = (SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId && o.DatabaseId == databaseId && o.SchemaName.Equals((string)myRow["referenced_schema_name"], StringComparison.InvariantCultureIgnoreCase)) != null) ? (SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId && o.DatabaseId == databaseId && o.SchemaName.Equals((string)myRow["referenced_schema_name"], StringComparison.InvariantCultureIgnoreCase)).SchemaId) : -1, ReferencedServerName = (string)myRow["referenced_server_name"], ReferencedDatabaseName = (string)myRow["referenced_database_name"], ReferencedSchemaName = (string)myRow["referenced_schema_name"], ReferencedEntityName = (string)myRow["referenced_entity_name"] }; var res = from dbDep in databaseDependencies join parentObj in SqlObjects.Where(p => p.ServerObjectId == serverObjectId && p.DatabaseId == databaseId) on dbDep.ReferencingId equals parentObj.ObjectId join childObj in SqlObjects.Where(c => c.ServerObjectId == serverObjectId && c.DatabaseId == databaseId) on new { SchemaId = dbDep.ReferencedSchemaId, ObjectId = dbDep.ReferencedId, ObjectName = dbDep.ReferencedEntityName } equals new { SchemaId = childObj.SchemaId, ObjectId = childObj.ObjectId, ObjectName = childObj.ObjectName } select new MySqlExpressionDependency { ServerObjectId = serverObjectId, DatabaseId = databaseId, ParentServerObjectId = parentObj.ServerObjectId, ParentDatabaseId = parentObj.DatabaseId, ParentObjectId = parentObj.ObjectId, ChildServerObjectId = childObj.ServerObjectId, ChildDatabaseId = childObj.DatabaseId, ChildObjectId = childObj.ObjectId, ChildObjectExists = true }; var uwq = from dbDep in databaseDependencies.Where(dd => dd.ReferencedId == -1 || dd.ReferencedSchemaId == -1) join parentObj in SqlObjects.Where(p => p.ServerObjectId == serverObjectId && p.DatabaseId == databaseId) on dbDep.ReferencingId equals parentObj.ObjectId select new MySqlExpressionDependency { ServerObjectId = serverObjectId, DatabaseId = databaseId, ParentServerObjectId = parentObj.ServerObjectId, ParentDatabaseId = parentObj.DatabaseId, ParentObjectId = parentObj.ObjectId, ChildServerObjectName = dbDep.ReferencedServerName, ChildDatabaseName = dbDep.ReferencedDatabaseName, ChildSchemaName = dbDep.ReferencedSchemaName, ChildObjectName = dbDep.ReferencedEntityName, ChildObjectExists = false }; _sqlExpressionDependencies.AddRange(res.Union(uwq)); MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} dependencies.", res.Count()), (DateTime.Now - dt).TotalSeconds); }