SQL Executing Monitor code

Keywords: C#, Sql Execute Listener

This is a Text_Log_Tracer of SQL Execution.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Diagnostics;

 
namespace WindowsApplication2
{
    /// <summary>
    /// This is a Text_Log_Tracer of SQL Execute.
    /// </summary>
    class SqlExecuteTracer : IDisposable
    {
        #region Fields
        private readonly string m_ErrorLogFileSuffix = "SqlError.txt";
        private readonly string m_SqlLogFileSuffix = "SqlLog.txt";
        private string m_ErrorLogFile;
        private string m_SqlLogFile;
        private TextWriterTraceListener m_SqlLogListener;
        private bool m_LogSqlExecute = false;
        #endregion


        #region Property
        /// <summary>
        /// Determine whether or not log Sql statement execution
        /// </summary>
        /// <remarks>This is a switch for method of TraceSqlToLog()</remarks>
        public bool LogSqlExecute
        {
            get { return m_LogSqlExecute; }
            set
            {
                m_LogSqlExecute = value;
                if (m_LogSqlExecute)
                {
                    if (m_SqlLogListener == null)
                        m_SqlLogListener = new TextWriterTraceListener(m_SqlLogFile);
                }
            }
        }
        #endregion


        #region Private methods

        private string GetAssemblyFileWithoutExt()
        {
            //C:\Documents and Settings\ctsuser\My Documents\SharpDevelop Projects\asdf\bin\Debug\asdf.exe
            string AssemblyFile = System.Reflection.Assembly.GetExecutingAssembly().Location;

            return System.IO.Path.GetDirectoryName(AssemblyFile)
                 + System.IO.Path.DirectorySeparatorChar
                 + System.IO.Path.GetFileNameWithoutExtension(AssemblyFile);
        }

        /*
        private string GetSqlFromCommand(SqlCommand cmd)
        {
            string sql = cmd.CommandText;
            if (cmd.Parameters.Count > 0)
            {
                DelphiStrList strList = new DelphiStrList();
                foreach (SqlParameter pmt in cmd.Parameters)
                {
                    strList.Add(pmt.ParameterName + "=" + pmt.Value.ToString());
                }
                sql = sql + Environment.NewLine;
                sql = sql + strList.MakeItemsToText(Environment.NewLine);
            }

            return sql;
        }
        */
        private string GetSqlFromCommand(IDbCommand cmd)
        {
            string sql = cmd.CommandText;
            if (cmd.Parameters.Count > 0)
            {
                SortedList<string, string> strList = new SortedList<string, string>();
                //DelphiStrList strList = new DelphiStrList();
                foreach (IDbDataParameter pmt in cmd.Parameters)
                {
                    if (pmt.Value != null)
                        strList.Add(pmt.ParameterName,"="+pmt.Value.ToString()) ;
                        //strList.Add(pmt.ParameterName + "=" + pmt.Value.ToString());
                    else
                        strList.Add(pmt.ParameterName,"=NULL") ;
                        //strList.Add(pmt.ParameterName + "=NULL");

                }
                sql = sql + Environment.NewLine;
                
                ///sql = sql + strList.MakeItemsToText(Environment.NewLine);
            }


            return sql;
        }


        private void TraceSqlToLogListener(string sql, string ErrorInfo, TraceListener listener)
        {
            listener.WriteLine("===================================");
            listener.WriteLine(DateTime.Now.ToString());
            if (String.IsNullOrEmpty(ErrorInfo) == false)
            {
                listener.WriteLine("Error Info:");
                listener.WriteLine(ErrorInfo);
                listener.WriteLine("SQL:");
            }
            listener.WriteLine(sql);
            listener.WriteLine("===================================");
            listener.WriteLine("");
            listener.Flush();
        }


        #endregion


        #region Public methods
        public SqlExecuteTracer()
        {
            string assemblyFileWithoutExt = GetAssemblyFileWithoutExt();
            m_ErrorLogFile = assemblyFileWithoutExt + "_" + m_ErrorLogFileSuffix;
            m_SqlLogFile = assemblyFileWithoutExt + "_" + m_SqlLogFileSuffix;
        }



        public void TraceErrorSqlToLog(string sql, string ErrorMessage)
        {
            using (TraceListener listener = new TextWriterTraceListener(m_ErrorLogFile))
            {
                TraceSqlToLogListener(sql, ErrorMessage, listener);
                listener.Close();
            }
        }



        public void TraceErrorSqlToLog(IDbCommand cmd, string ErrorMessage)
        {
            using (TraceListener listener = new TextWriterTraceListener(m_ErrorLogFile))
            {
                string sql = GetSqlFromCommand(cmd);
                TraceSqlToLogListener(sql, ErrorMessage, listener);
                listener.Close();
            }
        }


        public void TraceSqlToLog(string sql)
        {
            if (m_LogSqlExecute == false)
                return;
            TraceSqlToLogListener(sql, null, m_SqlLogListener);
        }



        public void TraceSqlToLog(IDbCommand cmd)
        {
            if (m_LogSqlExecute == false)
                return;

            string sql = GetSqlFromCommand(cmd);
            TraceSqlToLogListener(sql, null, m_SqlLogListener);
        }

        #endregion


        #region implementation IDisposable

        private bool disposed = false;

        public void Dispose()
        {
            Dispose(true);
            // This object will be cleaned up by the Dispose method.
            // Therefore, you should call GC.SupressFinalize to
            // take this object off the finalization queue 
            // and prevent finalization code for this object
            // from executing a second time.
            GC.SuppressFinalize(this);

        }

        private void Dispose(bool disposing)
        {
            // Check to see if Dispose has already been called.
            if (!this.disposed)
            {
                // If disposing equals true, dispose all managed 
                // and unmanaged resources.
                //if (disposing)
                //{
                //    // Dispose managed resources.
                //    component.Dispose();
                //}

                // Call the appropriate methods to clean up 
                // unmanaged resources here.
                // If disposing is false, 
                // only the following code is executed.
                if (m_LogSqlExecute)
                    m_SqlLogListener.Close();
            }
            disposed = true;
        }


        #endregion
    }
}