RoutineReader.cs

Go to the documentation of this file.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace RoutineAnalyzer
{
    class RoutineReader
    {
        #region Fields

        MySqlConnectionStringBuilder _builder;

        #endregion

        #region Constants

        //These are all parameterized MySQL statement that pull meta-data from the database on routines
        //NOTE: These are intended to only dissect procedures for now
        const string showProcQuery = "SHOW CREATE PROCEDURE {0}.{1}";
        const string showProcs = "select specific_name from INFORMATION_SCHEMA.routines  where routine_schema = '{0}' and routine_type = 'PROCEDURE'";
        const string procedureMarker = "PROCEDURE";

        #endregion

        #region Methods

        public RoutineReader(MySqlConnectionStringBuilder builder)
        {
            _builder = builder;
        }

        static string GetShowProcQuery(string schema, string procName)
        {
            return string.Format(showProcQuery, schema, procName);
        }

        static string GetShowAllProcsQuery(string schema)
        {
            return string.Format(showProcs, schema);
        }

        private static DataTable CreateTableFromCommand(MySqlCommand command)
        {
            using (MySqlDataReader reader = command.ExecuteReader())
            {
                //use a datatable to find the third column of the first row, that is the definition
                DataTable table = new DataTable();
                table.Load(reader);
                return table;
            }
        }

        DataTable GetAllProcNames(MySqlConnection connection)
        {
            using (MySqlCommand command = connection.CreateCommand())
            {
                command.CommandText = GetShowAllProcsQuery(_builder.Database);
                return CreateTableFromCommand(command);
            }
        }

        string GetProcDefintion(MySqlConnection connection, string procName)
        {
            //create a command and query the db for the proc definition
            using (MySqlCommand command = connection.CreateCommand())
            {
                command.CommandText = GetShowProcQuery(_builder.Database, procName);
                //use a datatable to find the third column of the first row, that is the definition
                DataTable table = CreateTableFromCommand(command);
                return table.Rows[0][2].ToString();
            }
        }

        public List<string> GetProcedureDefintions()
        {
            using (MySqlConnection connection = new MySqlConnection(_builder.ToString()))
            {
                connection.Open();
                DataTable table = GetAllProcNames(connection);
                List<string> procedures = new List<string>();
                foreach (DataRow row in table.Rows)
                {
                    //retrieve the whole definition
                    string definition = GetProcDefintion(connection, row[0].ToString());
                    //pull out just the first line
                    int firstNewline = definition.IndexOf("\n");
                    string paramters = definition.Substring(0, firstNewline + 1);
                    //remove all of the special single quotes
                    paramters = paramters.Replace("`", "");
                    //remove from the beginning to the end of the marker, plus one for the extra space
                    int startOfProcedure = paramters.IndexOf(procedureMarker);
                    paramters = paramters.Remove(0, startOfProcedure + procedureMarker.Length +1);
                    //add this to the list of procedures
                    procedures.Add(paramters);
                }

                return procedures;
            }
        }

        public DataTable GetProcedureDefinitionAsDataTable()
        {
            using (MySqlConnection connection = new MySqlConnection(_builder.ToString()))
            {
                connection.Open();
                return GetAllProcNames(connection);
            }
        }

        #endregion
    }
}