public virtual DataTable GetProcedures(string[] restrictions)
{
DataTable dt = new DataTable("Procedures");
dt.Columns.Add(new DataColumn("SPECIFIC_NAME", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_CATALOG", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_SCHEMA", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_NAME", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_TYPE", typeof(string)));
dt.Columns.Add(new DataColumn("DTD_IDENTIFIER", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_BODY", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_DEFINITION", typeof(string)));
dt.Columns.Add(new DataColumn("EXTERNAL_NAME", typeof(string)));
dt.Columns.Add(new DataColumn("EXTERNAL_LANGUAGE", typeof(string)));
dt.Columns.Add(new DataColumn("PARAMETER_STYLE", typeof(string)));
dt.Columns.Add(new DataColumn("IS_DETERMINISTIC", typeof(string)));
dt.Columns.Add(new DataColumn("SQL_DATA_ACCESS", typeof(string)));
dt.Columns.Add(new DataColumn("SQL_PATH", typeof(string)));
dt.Columns.Add(new DataColumn("SECURITY_TYPE", typeof(string)));
dt.Columns.Add(new DataColumn("CREATED", typeof(DateTime)));
dt.Columns.Add(new DataColumn("LAST_ALTERED", typeof(DateTime)));
dt.Columns.Add(new DataColumn("SQL_MODE", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_COMMENT", typeof(string)));
dt.Columns.Add(new DataColumn("DEFINER", typeof(string)));
StringBuilder sql = new StringBuilder("SELECT * FROM mysql.proc WHERE 1=1");
if (restrictions != null)
{
if (restrictions.Length >= 2 && restrictions[1] != null)
sql.AppendFormat(CultureInfo.InvariantCulture,
" AND db LIKE '{0}'", restrictions[1]);
if (restrictions.Length >= 3 && restrictions[2] != null)
sql.AppendFormat(CultureInfo.InvariantCulture,
" AND name LIKE '{0}'", restrictions[2]);
if (restrictions.Length >= 4 && restrictions[3] != null)
sql.AppendFormat(CultureInfo.InvariantCulture,
" AND type LIKE '{0}'", restrictions[3]);
}
MySqlCommand cmd = new MySqlCommand(sql.ToString(), connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DataRow row = dt.NewRow();
row["SPECIFIC_NAME"] = reader.GetString("specific_name");
row["ROUTINE_CATALOG"] = DBNull.Value;
row["ROUTINE_SCHEMA"] = reader.GetString("db");
row["ROUTINE_NAME"] = reader.GetString("name");
string routineType = reader.GetString("type");
row["ROUTINE_TYPE"] = routineType;
row["DTD_IDENTIFIER"] = routineType.ToLower(CultureInfo.InvariantCulture) == "function" ?
(object)reader.GetString("returns") : DBNull.Value;
row["ROUTINE_BODY"] = "SQL";
row["ROUTINE_DEFINITION"] = reader.GetString("body");
row["EXTERNAL_NAME"] = DBNull.Value;
row["EXTERNAL_LANGUAGE"] = DBNull.Value;
row["PARAMETER_STYLE"] = "SQL";
row["IS_DETERMINISTIC"] = reader.GetString("is_deterministic");
row["SQL_DATA_ACCESS"] = reader.GetString("sql_data_access");
row["SQL_PATH"] = DBNull.Value;
row["SECURITY_TYPE"] = reader.GetString("security_type");
row["CREATED"] = reader.GetDateTime("created");
row["LAST_ALTERED"] = reader.GetDateTime("modified");
row["SQL_MODE"] = reader.GetString("sql_mode");
row["ROUTINE_COMMENT"] = reader.GetString("comment");
row["DEFINER"] = reader.GetString("definer");
dt.Rows.Add(row);
}
}
return dt;
}
