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