public IEnumerable <SqlDatabaseObject> FindContentAndObjects(string word) { var objectsAndContents = new List <SqlDatabaseObject>(); try { var query = new StringBuilder(); query.Append(" SELECT ").Append(Environment.NewLine); query.Append(" SYS.OBJECTS.NAME ").Append(Environment.NewLine); query.Append($" ,CASE TYPE WHEN 'P' THEN '{ SqlDatabaseObjectType.Procedure }' ").Append(Environment.NewLine); query.Append($" WHEN 'TR' THEN '{ SqlDatabaseObjectType.Trigger }' ").Append(Environment.NewLine); query.Append($" WHEN 'FN' THEN '{ SqlDatabaseObjectType.ScalarFunction }' ").Append(Environment.NewLine); query.Append($" WHEN 'IS' THEN '{ SqlDatabaseObjectType.ScalarFunction }' ").Append(Environment.NewLine); query.Append($" WHEN 'V' THEN '{ SqlDatabaseObjectType.View }' ").Append(Environment.NewLine); query.Append($" WHEN 'TF' THEN '{ SqlDatabaseObjectType.TableFunction }' ").Append(Environment.NewLine); query.Append($" WHEN 'IF' THEN '{ SqlDatabaseObjectType.TableFunction }' ").Append(Environment.NewLine); query.Append($" WHEN 'U' THEN '{ SqlDatabaseObjectType.Table }' ").Append(Environment.NewLine); query.Append($" ELSE '{ SqlDatabaseObjectType.Unknown }' ").Append(Environment.NewLine); query.Append(" END AS TYPE ").Append(Environment.NewLine); query.Append(" ,MAX(SYS.OBJECTS.CREATE_DATE) AS CREATE_DATE ").Append(Environment.NewLine); query.Append(" ,MAX(SYS.OBJECTS.MODIFY_DATE) AS MODIFY_DATE ").Append(Environment.NewLine); query.Append(" ,MAX(DB_NAME()) AS [DATABASE] ").Append(Environment.NewLine); query.Append(" FROM ").Append(Environment.NewLine); query.Append(" SYS.OBJECTS ").Append(Environment.NewLine); query.Append(" LEFT JOIN SYSCOMMENTS ON ").Append(Environment.NewLine); query.Append($" SYS.OBJECTS.OBJECT_ID = SYSCOMMENTS.ID ").Append(Environment.NewLine); query.Append(" WHERE ").Append(Environment.NewLine); query.Append($" SYSCOMMENTS.TEXT LIKE '%{ word }%' OR SYS.OBJECTS.NAME LIKE '%{ word }%' ").Append(Environment.NewLine); query.Append(" GROUP BY ").Append(Environment.NewLine); query.Append(" SYS.OBJECTS.NAME, SYS.OBJECTS.TYPE ").Append(Environment.NewLine); using (SqlConnection connection = new SqlConnection(_setting.StringConnection)) { connection.Open(); using (SqlCommand command = connection.CreateCommand()) { command.CommandText = query.ToString(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Enum.TryParse <SqlDatabaseObjectType>(reader["TYPE"].ToString(), out var convertedType); if (convertedType != SqlDatabaseObjectType.Unknown) { var databaseObj = new SqlDatabaseObject( reader["NAME"].ToString(), GetObjectBody(reader["NAME"].ToString()), Convert.ToDateTime(reader["CREATE_DATE"]), Convert.ToDateTime(reader["MODIFY_DATE"]), convertedType, reader["DATABASE"].ToString()); objectsAndContents.Add(databaseObj); } } } } } } catch (Exception error) { MessageBox.Show( $"Error trying to find objects and contents in database.\n\nError Message: { error.Message } \n\nStack Trace: { error.StackTrace }", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(objectsAndContents); }
public IEnumerable <SqlDatabaseObject> GetObjectsFromDb() { var objects = new List <SqlDatabaseObject>(); try { var query = new StringBuilder(); query.Append(" SELECT ").Append(Environment.NewLine); query.Append(" NAME ").Append(Environment.NewLine); query.Append($" ,CASE TYPE WHEN 'P' THEN '{ SqlDatabaseObjectType.Procedure }' ").Append(Environment.NewLine); query.Append($" WHEN 'TR' THEN '{ SqlDatabaseObjectType.Trigger }' ").Append(Environment.NewLine); query.Append($" WHEN 'FN' THEN '{ SqlDatabaseObjectType.ScalarFunction }' ").Append(Environment.NewLine); query.Append($" WHEN 'IS' THEN '{ SqlDatabaseObjectType.ScalarFunction }' ").Append(Environment.NewLine); query.Append($" WHEN 'V' THEN '{ SqlDatabaseObjectType.View }' ").Append(Environment.NewLine); query.Append($" WHEN 'TF' THEN '{ SqlDatabaseObjectType.TableFunction }' ").Append(Environment.NewLine); query.Append($" WHEN 'IF' THEN '{ SqlDatabaseObjectType.TableFunction }' ").Append(Environment.NewLine); query.Append($" WHEN 'U' THEN '{ SqlDatabaseObjectType.Table }' ").Append(Environment.NewLine); query.Append($" ELSE '{ SqlDatabaseObjectType.Unknown }' ").Append(Environment.NewLine); query.Append(" END AS TYPE ").Append(Environment.NewLine); query.Append(" ,SYS.OBJECTS.CREATE_DATE ").Append(Environment.NewLine); query.Append(" ,SYS.OBJECTS.MODIFY_DATE ").Append(Environment.NewLine); query.Append(" ,DB_NAME() AS [DATABASE] ").Append(Environment.NewLine); query.Append(" FROM ").Append(Environment.NewLine); query.Append(" SYS.OBJECTS ").Append(Environment.NewLine); query.Append(" WHERE ").Append(Environment.NewLine); query.Append(" TYPE IN ('P', 'TR', 'TF', 'FN', 'V', 'IS', 'IF', 'U') ").Append(Environment.NewLine); query.Append(" ORDER BY ").Append(Environment.NewLine); query.Append(" TYPE ").Append(Environment.NewLine); using (SqlConnection connection = new SqlConnection(_setting.StringConnection)) { connection.Open(); using (SqlCommand command = connection.CreateCommand()) { command.CommandText = query.ToString(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Enum.TryParse <SqlDatabaseObjectType>(reader["TYPE"].ToString(), out var convertedType); if (convertedType != SqlDatabaseObjectType.Unknown) { var databaseObj = new SqlDatabaseObject( reader["NAME"].ToString(), GetObjectBody(reader["NAME"].ToString()), Convert.ToDateTime(reader["CREATE_DATE"]), Convert.ToDateTime(reader["MODIFY_DATE"]), convertedType, reader["DATABASE"].ToString()); objects.Add(databaseObj); } } } } } } catch (Exception error) { MessageBox.Show( $"Error trying to list all objects from database.\n\nError Message: { error.Message } \n\nStack Trace: { error.StackTrace }", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(objects); }