/mojoPortal.Data.SqlCe/DBSystemLog.cs

//	Author:                 Joe Audette

//  Created:			    2011-07-23

//	Last Modified:		    2011-07-28

// 

// The use and distribution terms for this software are covered by the 

// Common Public License 1.0 (http://opensource.org/licenses/cpl.php)

// which can be found in the file CPL.TXT at the root of this distribution.

// By using this software in any fashion, you are agreeing to be bound by 

// the terms of this license.

//

// You must not remove this notice, or any other, from this software.



using System;

using System.Data;

using System.Data.SqlServerCe;

using System.Globalization;

using System.Text;



namespace mojoPortal.Data

{

    public static class DBSystemLog

    {

        private static String GetConnectionString()

        {

            return DBPortal.GetConnectionString();

        }





        /// <summary>

        /// Inserts a row in the mp_SystemLog table. Returns new integer id.

        /// </summary>

        /// <param name="logDate"> logDate </param>

        /// <param name="ipAddress"> ipAddress </param>

        /// <param name="culture"> culture </param>

        /// <param name="url"> url </param>

        /// <param name="shortUrl"> shortUrl </param>

        /// <param name="thread"> thread </param>

        /// <param name="logLevel"> logLevel </param>

        /// <param name="logger"> logger </param>

        /// <param name="message"> message </param>

        /// <returns>int</returns>

        public static int Create(

            DateTime logDate,

            string ipAddress,

            string culture,

            string url,

            string shortUrl,

            string thread,

            string logLevel,

            string logger,

            string message)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("INSERT INTO mp_SystemLog ");

            sqlCommand.Append("(");

            sqlCommand.Append("LogDate, ");

            sqlCommand.Append("IpAddress, ");

            sqlCommand.Append("Culture, ");

            sqlCommand.Append("Url, ");

            sqlCommand.Append("ShortUrl, ");

            sqlCommand.Append("Thread, ");

            sqlCommand.Append("LogLevel, ");

            sqlCommand.Append("Logger, ");

            sqlCommand.Append("Message ");

            sqlCommand.Append(")");



            sqlCommand.Append(" VALUES ");

            sqlCommand.Append("(");

            sqlCommand.Append("@LogDate, ");

            sqlCommand.Append("@IpAddress, ");

            sqlCommand.Append("@Culture, ");

            sqlCommand.Append("@Url, ");

            sqlCommand.Append("@ShortUrl, ");

            sqlCommand.Append("@Thread, ");

            sqlCommand.Append("@LogLevel, ");

            sqlCommand.Append("@Logger, ");

            sqlCommand.Append("@Message ");

            sqlCommand.Append(")");

            sqlCommand.Append(";");



            SqlCeParameter[] arParams = new SqlCeParameter[9];



            arParams[0] = new SqlCeParameter("@LogDate", SqlDbType.DateTime);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = logDate;



            arParams[1] = new SqlCeParameter("@IpAddress", SqlDbType.NVarChar, 50);

            arParams[1].Direction = ParameterDirection.Input;

            arParams[1].Value = ipAddress;



            arParams[2] = new SqlCeParameter("@Culture", SqlDbType.NVarChar, 10);

            arParams[2].Direction = ParameterDirection.Input;

            arParams[2].Value = culture;



            arParams[3] = new SqlCeParameter("@Url", SqlDbType.NText);

            arParams[3].Direction = ParameterDirection.Input;

            arParams[3].Value = url;



            arParams[4] = new SqlCeParameter("@ShortUrl", SqlDbType.NVarChar, 255);

            arParams[4].Direction = ParameterDirection.Input;

            arParams[4].Value = shortUrl;



            arParams[5] = new SqlCeParameter("@Thread", SqlDbType.NVarChar, 255);

            arParams[5].Direction = ParameterDirection.Input;

            arParams[5].Value = thread;



            arParams[6] = new SqlCeParameter("@LogLevel", SqlDbType.NVarChar, 20);

            arParams[6].Direction = ParameterDirection.Input;

            arParams[6].Value = logLevel;



            arParams[7] = new SqlCeParameter("@Logger", SqlDbType.NVarChar, 255);

            arParams[7].Direction = ParameterDirection.Input;

            arParams[7].Value = logger;



            arParams[8] = new SqlCeParameter("@Message", SqlDbType.NText);

            arParams[8].Direction = ParameterDirection.Input;

            arParams[8].Value = message;





            int newId = Convert.ToInt32(SqlHelper.DoInsertGetIdentitiy(

                GetConnectionString(),

                CommandType.Text,

                sqlCommand.ToString(),

                arParams));



            return newId;





        }





        /// <summary>

        /// Deletes rows from the mp_SystemLog table. Returns true if rows deleted.

        /// </summary>

        public static void DeleteAll()

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("DELETE FROM mp_SystemLog ");

            //sqlCommand.Append("WHERE ");

            //sqlCommand.Append("ID = @ID ");

            sqlCommand.Append(";");



            //SqlCeParameter[] arParams = new SqlCeParameter[1];



            //arParams[0] = new SqlCeParameter("@ID", SqlDbType.Int);

            //arParams[0].Direction = ParameterDirection.Input;

            //arParams[0].Value = id;



            SqlHelper.ExecuteNonQuery(

                GetConnectionString(),

                CommandType.Text,

                sqlCommand.ToString(),

                null);

        }



        /// <summary>

        /// Deletes a row from the mp_SystemLog table. Returns true if row deleted.

        /// </summary>

        /// <param name="id"> id </param>

        /// <returns>bool</returns>

        public static bool Delete(int id)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("DELETE FROM mp_SystemLog ");

            sqlCommand.Append("WHERE ");

            sqlCommand.Append("ID = @ID ");

            sqlCommand.Append(";");



            SqlCeParameter[] arParams = new SqlCeParameter[1];



            arParams[0] = new SqlCeParameter("@ID", SqlDbType.Int);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = id;



            int rowsAffected = SqlHelper.ExecuteNonQuery(

                GetConnectionString(),

                CommandType.Text,

                sqlCommand.ToString(),

                arParams);



            return (rowsAffected > -1);



        }



        /// <summary>

        /// Deletes rows from the mp_SystemLog table. Returns true if rows deleted.

        /// </summary>

        /// <param name="id"> id </param>

        /// <returns>bool</returns>

        public static bool DeleteOlderThan(DateTime cutoffDate)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("DELETE FROM mp_SystemLog ");

            sqlCommand.Append("WHERE ");

            sqlCommand.Append("LogDate < @CutoffDate ");

            sqlCommand.Append(";");



            SqlCeParameter[] arParams = new SqlCeParameter[1];



            arParams[0] = new SqlCeParameter("@CutoffDate", SqlDbType.DateTime);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = cutoffDate;



            int rowsAffected = SqlHelper.ExecuteNonQuery(

                GetConnectionString(),

                CommandType.Text,

                sqlCommand.ToString(),

                arParams);



            return (rowsAffected > -1);



        }



        /// <summary>

        /// Deletes rows from the mp_SystemLog table. Returns true if rows deleted.

        /// </summary>

        /// <param name="id"> id </param>

        /// <returns>bool</returns>

        public static bool DeleteByLevel(string logLevel)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("DELETE FROM mp_SystemLog ");

            sqlCommand.Append("WHERE ");

            sqlCommand.Append("LogLevel = @LogLevel ");

            sqlCommand.Append(";");



            SqlCeParameter[] arParams = new SqlCeParameter[1];



            arParams[0] = new SqlCeParameter("@LogLevel", SqlDbType.NVarChar, 20);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = logLevel;



            int rowsAffected = SqlHelper.ExecuteNonQuery(

                GetConnectionString(),

                CommandType.Text,

                sqlCommand.ToString(),

                arParams);



            return (rowsAffected > -1);



        }



        /// <summary>

        /// Gets a count of rows in the mp_SystemLog table.

        /// </summary>

        public static int GetCount()

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("SELECT  Count(*) ");

            sqlCommand.Append("FROM	mp_SystemLog ");

            sqlCommand.Append(";");



            //SqlCeParameter[] arParams = new SqlCeParameter[1];



            //arParams[0] = new SqlCeParameter("@ApplicationID", SqlDbType.UniqueIdentifier);

            //arParams[0].Direction = ParameterDirection.Input;

            //arParams[0].Value = applicationId;



            return Convert.ToInt32(SqlHelper.ExecuteScalar(

                GetConnectionString(),

                CommandType.Text,

                sqlCommand.ToString(),

                null));

        }



        /// <summary>

        /// Gets a page of data from the mp_SystemLog table.

        /// </summary>

        /// <param name="pageNumber">The page number.</param>

        /// <param name="pageSize">Size of the page.</param>

        /// <param name="totalPages">total pages</param>

        public static IDataReader GetPageAscending(

            int pageNumber,

            int pageSize,

            out int totalPages)

        {

            int pageLowerBound = (pageSize * pageNumber) - pageSize;

            totalPages = 1;

            int totalRows = GetCount();



            if (pageSize > 0) totalPages = totalRows / pageSize;



            if (totalRows <= pageSize)

            {

                totalPages = 1;

            }

            else

            {

                int remainder;

                Math.DivRem(totalRows, pageSize, out remainder);

                if (remainder > 0)

                {

                    totalPages += 1;

                }

            }



            int offset = 0;

            if (pageNumber > 1) { offset = (pageSize * pageNumber) - pageSize; }



            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("SELECT * ");

            sqlCommand.Append("FROM	mp_SystemLog  ");

            //sqlCommand.Append("WHERE   ");



            sqlCommand.Append("ORDER BY ID  "); 

            //order by is required if using fetch and offset or an error will occur, uncomment it and put at least one column to sort by



            sqlCommand.Append("OFFSET " + offset.ToString(CultureInfo.InvariantCulture) + " ROWS ");

            sqlCommand.Append("FETCH NEXT " + pageSize.ToString(CultureInfo.InvariantCulture) + "ROWS ONLY ");



            sqlCommand.Append(";");





            //SqlCeParameter[] arParams = new SqlCeParameter[1];



            //arParams[0] = new SqlCeParameter("@ApplicationID", SqlDbType.UniqueIdentifier);

            //arParams[0].Direction = ParameterDirection.Input;

            //arParams[0].Value = applicationId;



            return SqlHelper.ExecuteReader(

                GetConnectionString(),

                CommandType.Text,

                sqlCommand.ToString(),

                null);



        }



        /// <summary>

        /// Gets a page of data from the mp_SystemLog table.

        /// </summary>

        /// <param name="pageNumber">The page number.</param>

        /// <param name="pageSize">Size of the page.</param>

        /// <param name="totalPages">total pages</param>

        public static IDataReader GetPageDescending(

            int pageNumber,

            int pageSize,

            out int totalPages)

        {

            int pageLowerBound = (pageSize * pageNumber) - pageSize;

            totalPages = 1;

            int totalRows = GetCount();



            if (pageSize > 0) totalPages = totalRows / pageSize;



            if (totalRows <= pageSize)

            {

                totalPages = 1;

            }

            else

            {

                int remainder;

                Math.DivRem(totalRows, pageSize, out remainder);

                if (remainder > 0)

                {

                    totalPages += 1;

                }

            }



            int offset = 0;

            if (pageNumber > 1) { offset = (pageSize * pageNumber) - pageSize; }



            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("SELECT * ");

            sqlCommand.Append("FROM	mp_SystemLog  ");

            //sqlCommand.Append("WHERE   ");



            sqlCommand.Append("ORDER BY ID DESC ");

            //order by is required if using fetch and offset or an error will occur, uncomment it and put at least one column to sort by



            sqlCommand.Append("OFFSET " + offset.ToString(CultureInfo.InvariantCulture) + " ROWS ");

            sqlCommand.Append("FETCH NEXT " + pageSize.ToString(CultureInfo.InvariantCulture) + "ROWS ONLY ");



            sqlCommand.Append(";");





            //SqlCeParameter[] arParams = new SqlCeParameter[1];



            //arParams[0] = new SqlCeParameter("@ApplicationID", SqlDbType.UniqueIdentifier);

            //arParams[0].Direction = ParameterDirection.Input;

            //arParams[0].Value = applicationId;



            return SqlHelper.ExecuteReader(

                GetConnectionString(),

                CommandType.Text,

                sqlCommand.ToString(),

                null);



        }







    }

}