segunda-feira, 20 de julho de 2015

DataBase Controle - Jade ORM

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Collections;
using System.Reflection;
using Lapuinka.EAA.Model;
using System.Diagnostics;
using Microsoft.Reporting.WebForms;
/*
       Author: Fabio Leandro Lapuinka
        Jade Framework ORM
*/
namespace Lapuinka.EAA.Controller.DataBase
{
    ///
    /// DBNull Convert Class
    ///

    public static class DBNullConvert
    {
        public enum DateInterval { Second, Minute, Hour, Day, Week, Month, Quarter, Year }
        public static bool ToBoolean(object value)
        {
            if (value is DBNull)
                return false;
            else
                return Convert.ToBoolean(value);

        }
        public static string ToString(object value)
        {
            if (value is DBNull)
                return string.Empty;
            else
                return value.ToString();

        }
        public static int ToInt(object value)
        {
            if (value is DBNull || value == null || value.ToString().Trim() ==string.Empty)
                return 0;
            else
                return Convert.ToInt32(value);

        }
        public static float ToFloat(object Value)
        {
            if (Value is DBNull)
                return 0;
            else
                return (float)Convert.ToDouble(Value);
        }
        public static long DateDiff(DateInterval Interval, System.DateTime StartDate, System.DateTime EndDate)
        {
            long lngDateDiffValue = 0;
            System.TimeSpan TS = new System.TimeSpan(EndDate.Ticks - StartDate.Ticks);
            switch (Interval)
            {
                case DateInterval.Day:
                    lngDateDiffValue = (long)TS.Days;
                    break;
                case DateInterval.Hour:
                    lngDateDiffValue = (long)TS.TotalHours;
                    break;
                case DateInterval.Minute:
                    lngDateDiffValue = (long)TS.TotalMinutes;
                    break;
                case DateInterval.Month:
                    lngDateDiffValue = (long)(TS.Days / 30);
                    break;
                case DateInterval.Quarter:
                    lngDateDiffValue = (long)((TS.Days / 30) / 3);
                    break;
                case DateInterval.Second:
                    lngDateDiffValue = (long)TS.TotalSeconds;
                    break;
                case DateInterval.Week:
                    lngDateDiffValue = (long)(TS.Days / 7);
                    break;
                case DateInterval.Year:
                    lngDateDiffValue = (long)(TS.Days / 365);
                    break;
            }
            return lngDateDiffValue;
        }
        public static bool CompareData(DateTime dataIni, DateTime dataEnd, int Minutes)
        {
            dataIni = dataIni.AddMinutes(Minutes);
            if (dataIni.Ticks <= dataEnd.Ticks)
                return true;
            else
                return false;

        }
    }

    #region DataBaseController
    ///
    ///Creation Date:      17-Nov-2009
    ///Name: DataBaseController
    ///Version:     1.0.0
    ///Purpose:     Provide a central point to get/set data from database.
    ///Author:     Lapuinka Fabio Leandro Lapuinka
    ///
    ///Revision History
    ///Version             Date           By Whom      Change Description
    ///----------    ------         ------------ ---------------------------------------------
    ///1.0.0         10-May-10    FLL          Documentation and Code Standards
    ///

    public class DataBaseController
    {
        #region Message

        #region Logger
        ///
        /// Logger Message to Analyse Framework Sequences...
        ///

        ///
        public static void Logger(string Source, string Message)
        {

            Debug.WriteLine(String.Format("\nDataBaseController.Logger  Date:[{0}]; Source:[{1}]; Message:[{2}]", System.DateTime.Now.ToString(), Source, Message));
        }
        #endregion

        #endregion

        #region ShowErrorMessage
        ///
        /// Shows the appropriate error message
        ///

        ///
        ///
        public static string ShowErrorMessage(SqlException ex)
        {

            #region DEBUG
#if DEBUG
            Logger("DataBaseController.ShowErrorMessage", ex.Message);
#endif
            #endregion

            switch (ex.Number)
            {
                case 514:
                        return getClearMessage(string.Format(Message.getMessage(Message.IDMessage.MSG_RECORD_DUPLICATE), ex.Number.ToString()));
                case 515:
                        return getClearMessage(string.Format(Message.getMessage(Message.IDMessage.MSG_REQUERIED_FIELDS), ex.Number.ToString(), ex.Procedure));
                case 547:
                        return getClearMessage(string.Format(Message.getMessage(Message.IDMessage.MSG_CONFLICTED_FOREIGNKEY), ex.Message));
                case 201:
                        return getClearMessage(string.Format(Message.getMessage(Message.IDMessage.MSG_REQUERIED_FIELDS), ex.Number.ToString(), ex.Procedure));
                case 2601:
                        return getClearMessage(string.Format(Message.getMessage(Message.IDMessage.MSG_RECORD_DUPLICATE), ex.Number.ToString()));
                default:
                        return getClearMessage(string.Format(Message.getMessage(Message.IDMessage.MSG_PROCEDURE_ERROR), ex.Number.ToString(), ex.Procedure, ex.Message));
            }
        }

        ///
        /// getClear Message
        ///

        ///
        ///
        public static string getClearMessage(string msg)
        {
            msg= msg.Replace("\n", "");
            msg = msg.Replace("\t", "");
            msg = msg.Replace("'", "");
            msg = msg.Replace("\r", "");
            msg = msg.Replace("/*", "");
            msg = msg.Replace("*/", "");
            return msg;
        }
        #endregion

        #region GetConnection
        ///
        /// Get Connection
        ///

        ///
        public static SqlConnection GetConnection()
        {
            SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            return con;
        }
        #endregion

        #region GetReportParams
        ///
        /// Get Parameters
        ///

        ///
        ///
        ///
        public static ReportParameter[] GetReportParameters(object objBusiness)
        {
            ArrayList list = new ArrayList();
            foreach (PropertyInfo info in objBusiness.GetType().GetProperties())
            {
                object[] customAttributes = info.GetCustomAttributes(typeof(IsReportInput), false);
                if (customAttributes.Length > 0)
                {
                   foreach (object oAtributte in customAttributes)
                    {
                        if (oAtributte is IsReportInput)
                        {
                              list.Add(new ReportParameter(info.Name,Convert.ToString(info.GetValue(objBusiness, null))));
                        }
                    }

                }
            }
            ReportParameter[] array = new ReportParameter[list.Count];
            list.CopyTo(array, 0);
            return array;
        }
        #endregion

        #region GetObjectParameters
        ///
        /// Get Parameters
        ///

        ///
        ///
        ///
        public static IDataParameter[] GetObjectParameters(string ProcedureTarget, object objBusiness)
        {
            Type tp = objBusiness.GetType();
            DataTable tb = ValidParameters(ProcedureTarget + tp.Name);
           
            ArrayList list = new ArrayList();
            InputNullValue nullAtributte = null;
            foreach (PropertyInfo info in objBusiness.GetType().GetProperties())
            {
                object[] customAttributes = info.GetCustomAttributes(typeof(InputNullValue), false);
                if (customAttributes.Length > 0)
                {
                    foreach (object oAtributte in customAttributes)
                    {
                        if (oAtributte is InputNullValue)
                        {
                            nullAtributte = (InputNullValue)oAtributte;
                        }
                    }
                }
                bool flag = true;
                customAttributes = info.GetCustomAttributes(typeof(Operation), false);
                if (customAttributes.Length > 0)
                {
                    foreach (object objOperation in customAttributes)
                    {
                        if (objOperation is Operation)
                        {
                            flag = ((Operation)objOperation).ExistProcedure(ProcedureTarget);
                        }
                    }
                }
                if (flag)
                {
                    customAttributes = info.GetCustomAttributes(typeof(OperationInput), false);
                    if (customAttributes.Length > 0)
                    {
                        foreach (object objCustomAtributte in customAttributes)
                        {
                            if (objCustomAtributte is OperationInput)
                            {
                                IDataParameter parameter = ((OperationInput)objCustomAtributte).GetParameter();
                                parameter.Value = info.GetValue(objBusiness, null);
                                if ((nullAtributte != null) && nullAtributte.IsNull(parameter.Value))
                                {
                                    parameter.Value = DBNull.Value;
                                }
                                if(tb.Select(String.Format("ColName='{0}'",parameter.ParameterName.ToUpper().Trim()))!=null)
                                    list.Add(parameter);
                            }
                        }
                    }
                }
            }
            if (list.Count > 0)
            {
                IDataParameter[] array = new IDataParameter[list.Count];


                list.CopyTo(array, 0);
                return array;
            }
            return null;
        }
        #endregion

        #region GetObject
        ///
        /// Get Parameters
        ///

        ///
        ///
        ///
        public static string Validator(string ProcedureTarget, object objBusiness)
        {
            StringBuilder msg = new StringBuilder();
            foreach (PropertyInfo info in objBusiness.GetType().GetProperties())
            {
                object[] customAttributes = info.GetCustomAttributes(typeof(RuleFromString), false);
                if (customAttributes.Length > 0)
                {
                    foreach (object objOperation in customAttributes)
                    {
                        if (objOperation is RuleFromString)
                        {
                            if (((RuleFromString)objOperation).ExistProcedure(ProcedureTarget))
                            {
                                string tmp = Convert.ToString(info.GetValue(objBusiness, null));
                                if(tmp!=string.Empty || tmp != null)
                                    msg.Append(tmp);

                            }
                        }
                       
                    }
                }

            }
            return msg.ToString();



        }
        #endregion

        #region GetObjectID
        ///
        /// Get Parameters
        ///

        ///
        ///
        ///
        public static int GetIDObject(object objBusiness)
        {
            foreach (PropertyInfo info in objBusiness.GetType().GetProperties())
            {
                object[] customAttributes = info.GetCustomAttributes(typeof(IsPrimaryKeyAttribute),false);
                if (customAttributes.Length > 0)
                {
                        return Convert.ToInt32(info.GetValue(objBusiness, null));
                       
                }
             
            }
            return -1;
        }
        ///
        /// Get Parameters
        ///

        ///
        ///
        ///
        public static void SetIDObject(object objBusiness, int ID)
        {
            foreach (PropertyInfo info in objBusiness.GetType().GetProperties())
            {
                object[] customAttributes = info.GetCustomAttributes(typeof(IsPrimaryKeyAttribute), false);
                if (customAttributes.Length > 0)
                {
                    info.SetValue(objBusiness, ID, null);

                }

            }

        }
        #endregion

        #region Save
        ///
        /// Save Object in Storage
        ///

        ///
        public static int Save(string SaveInput, object baseObject)
        {


            Type tp = baseObject.GetType();
            SqlConnection con = DataBaseController.GetConnection();


 

            try
            {
                DataTable result = new DataTable(tp.Name);
                con.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(SaveInput + tp.Name, con))
                {
                    #region  DEBUG Step 1
#if DEBUG
                    string strOut = adapter.SelectCommand.CommandText;
                    string split = string.Empty;
#endif
                    #endregion

                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    foreach (IDataParameter parameter2 in DataBaseController.GetObjectParameters(SaveInput, baseObject))
                    {
                        adapter.SelectCommand.Parameters.Add(parameter2);

                        #region  DEBUG Step 2
#if DEBUG
                        if (!(parameter2.Value is DBNull))
                            strOut += "\n" + split + string.Format("{0}={1}", parameter2.ParameterName, parameter2.Value);
                        else
                            strOut += "\n" + split + string.Format("{0}=null", parameter2.ParameterName);
                        split = ",";
#endif
                        #endregion
                    }

                    #region  DEBUG Step 3
#if DEBUG
                    Logger("DataBaseController.Save",strOut);
                    #endif
                    #endregion

                    adapter.Fill(result);
                }

                if (result.Rows.Count > 0)
                {
                    if (result.Rows[0]["id"] is DBNull)
                        return 0;
                    else
                        return Convert.ToInt32(result.Rows[0]["id"]);
                }

            }
           finally
            {
                if (con.State != ConnectionState.Closed)
                    con.Close();
            }

            return 0;
        }
        #endregion

        #region Pick
        ///
        /// Pick Object By ID
        ///

        ///
        ///
        ///
        public static void PickByID(string PickInput, object baseObject, int ID)
        {
            SetIDObject(baseObject,ID);
            Pick(PickInput, baseObject);
        }
        ///
        /// Pick Object
        ///

        ///
        ///
        public static void Pick(string PickInput, object baseObject)
        {
            SqlConnection con = DataBaseController.GetConnection();
            Type tp = baseObject.GetType();
            try
            {
                using (DataTable dataTableResult = new DataTable(tp.Name))
                {
                    con.Open();
                    using (SqlDataAdapter adapter = new SqlDataAdapter(PickInput + tp.Name, con))
                    {
                        #region  DEBUG Step 1
#if DEBUG
                        string strOut = adapter.SelectCommand.CommandText;
                        string split = string.Empty;
#endif
                        #endregion

                        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                        foreach (IDataParameter parameter2 in DataBaseController.GetObjectParameters(PickInput, baseObject))
                        {
                            adapter.SelectCommand.Parameters.Add(parameter2);

                            #region  DEBUG Step 2
#if DEBUG
                            if (!(parameter2.Value is DBNull))
                                strOut += "\n" + split + string.Format("{0}={1}", parameter2.ParameterName, parameter2.Value);
                            else
                                strOut += "\n" + split + string.Format("{0}=null", parameter2.ParameterName);
                            split = ",";
#endif
                            #endregion

                        }

                        #region  DEBUG Step 3
#if DEBUG
                        Logger("DataBaseController.Pick", strOut);
#endif
                        #endregion

                        adapter.Fill(dataTableResult);
                    }
                    if (dataTableResult.Rows.Count > 0)
                    {

                        foreach (PropertyInfo info in baseObject.GetType().GetProperties())
                        {
                            if (dataTableResult.Columns[info.Name]!=null)
                                if (!(dataTableResult.Rows[0][info.Name]is DBNull))
                                {
                                    info.SetValue(baseObject, dataTableResult.Rows[0][info.Name], null);
                                }
                        }
                    }
                }

            }
            finally
            {
                if (con.State != ConnectionState.Closed)
                    con.Close();
            }
        }
        #endregion

        #region Search
        ///
        /// Search Object
        ///

        ///
        public static List Search(string SearchInput, object baseObject)
        {
            List oList = new List();
            Type tp = baseObject.GetType();
            using (DataTable dataTableResult = SearchTable(SearchInput,baseObject))
            {
                if (dataTableResult.Rows.Count > 0)
                    foreach (DataRow row in dataTableResult.Rows)
                    {
                       System.Object obj = Activator.CreateInstance(tp);

                        foreach (PropertyInfo info in tp.GetProperties())
                        {
                            object[] customAttributes = info.GetCustomAttributes(typeof(OperationInput), false);
                            if (customAttributes.Length > 0)
                            {
                                foreach (object objcmdCustomAtributte in customAttributes)
                                {
                                    if (objcmdCustomAtributte is OperationInput)
                                    {
                                        IDataParameter parameter = ((OperationInput)objcmdCustomAtributte).GetParameter();
                                        if (dataTableResult.Columns[parameter.ParameterName.Replace("@", string.Empty)] != null)
                                            info.SetValue(obj, row[parameter.ParameterName.Replace("@", string.Empty)], null);
                                    }
                                }
                            }
                        }
                        oList.Add((T)obj);
                    }
            }

            return oList;
        }
        #endregion

        #region Lookup
        ///
        /// Search Object
        ///

        ///
        public static DataTable Lookup(string LookupInput, object baseObject)
        {
            Type tp = baseObject.GetType();
            SqlConnection con = DataBaseController.GetConnection();
            try
            {
                using (DataTable dataTableResult = new DataTable(tp.Name))
                {
                    con.Open();
                    using (SqlDataAdapter adapter = new SqlDataAdapter(LookupInput + tp.Name, con))
                    {
                        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                        #region DEBUG Step 1
#if DEBUG
                        string strOut = adapter.SelectCommand.CommandText;
                        string split = string.Empty;
#endif
                        #endregion

                        IDataParameter[] IDataParams = DataBaseController.GetObjectParameters(LookupInput, baseObject);

                        if (IDataParams != null)
                        {
                            foreach (IDataParameter parameter2 in IDataParams)
                            {
                                adapter.SelectCommand.Parameters.Add(parameter2);

                                #region  DEBUG Step 2
#if DEBUG
                                if (!(parameter2.Value is DBNull))
                                    strOut += "\n" + split + string.Format("{0}={1}", parameter2.ParameterName, parameter2.Value);
                                else
                                    strOut += "\n" + split + string.Format("{0}=null", parameter2.ParameterName);
                                split = ",";
#endif
                                #endregion
                            }
                        }

                        #region  DEBUG Step 3
#if DEBUG
                        Logger("DataBaseController.Lookup", strOut);
#endif
                        #endregion

                        adapter.Fill(dataTableResult);
                        return dataTableResult;
                    }
                }

            }

            finally
            {
                if (con.State != ConnectionState.Closed)
                    con.Close();
            }

        }
        #endregion

        #region QueryParams
        ///
        /// QueryParams
        ///

        ///
        ///
        ///
        public static string QueryParams(string SearchInput, object baseObject)
        {
            string strOut = "Type=" + baseObject.GetType().FullName+","+ baseObject.GetType().Assembly.FullName.Split(',')[0];
            string split = string.Empty;
            IDataParameter[] IDataParams = DataBaseController.GetObjectParameters(SearchInput, baseObject);
            if (IDataParams != null)
            {
                foreach (IDataParameter parameter2 in IDataParams)
                {
                    if (!(parameter2.Value is DBNull))
                        strOut += string.Format("&{0}={1}", parameter2.ParameterName.Replace("@",""), parameter2.Value);
                    else
                        strOut += string.Format("&{0}=", parameter2.ParameterName.Replace("@", ""));
                }
            }

            return strOut;
        }
        #endregion

        #region ValidParameters
        ///
        /// Read SQL Meta Data and compare with paremeters....
        ///

        ///
        ///
        public static DataTable ValidParameters(string ProcedureTarget)
         {
             string strQuery = string.Format(@"select ObjectName=s.name,ColName=ltrim(rtrim(upper(c.name))),ColType=t.name ,ColLength=c.length,ColXprec=c.xprec,ColScacel=c.scale,ColIsNull= c.isnullable from sysobjects s (nolock) inner join syscolumns c (nolock) on s.id=c.id inner join systypes t (nolock) on t.xtype=c.xtype where s.name = '{0}'", ProcedureTarget);
               
                SqlConnection con = DataBaseController.GetConnection();
                try
                {
                    using (DataTable dataTableResult = new DataTable(ProcedureTarget))
                    {
                        con.Open();
                        using (SqlDataAdapter adapter = new SqlDataAdapter(strQuery, con))
                        {
                            adapter.SelectCommand.CommandType = CommandType.Text ;

                            adapter.Fill(dataTableResult);
                            return dataTableResult;
                        }
                    }

                }
                 finally
                {
                    if (con.State != ConnectionState.Closed)
                        con.Close();
                }


         }
       
        #endregion


        #region Search
        ///
        /// Search Object
        ///

        ///
        public static DataTable SearchTable(string SearchInput, object baseObject)
        {
            Type tp = baseObject.GetType();
            SqlConnection con = DataBaseController.GetConnection();
            try
            {
                using (DataTable dataTableResult = new DataTable(tp.Name))
                {
                    con.Open();
                    using (SqlDataAdapter adapter = new SqlDataAdapter(SearchInput + tp.Name, con))
                    {
                        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

                        #region DEBUG Step 1
#if DEBUG
                        string strOut = adapter.SelectCommand.CommandText;
                        string split = string.Empty;
#endif
                        #endregion

                        IDataParameter[] IDataParams = DataBaseController.GetObjectParameters(SearchInput, baseObject);

                        if (IDataParams != null)
                        {
                            foreach (IDataParameter parameter2 in IDataParams)
                            {
                                adapter.SelectCommand.Parameters.Add(parameter2);

                                #region DEBUG Step 2
#if DEBUG
                                if (!(parameter2.Value is DBNull))
                                    strOut += "\n" + split + string.Format("{0}={1}", parameter2.ParameterName, parameter2.Value);
                                else
                                    strOut += "\n" + split + string.Format("{0}=null", parameter2.ParameterName);
                                split = ",";
#endif
                                #endregion

                            }
                        }

                        #region DEBUG Step 3
#if DEBUG
                        Logger("DataBaseController.SearchTable", strOut);
#endif
                        #endregion

                        adapter.Fill(dataTableResult);
                        return dataTableResult;
                    }
                }

            }
             finally
            {
                if (con.State != ConnectionState.Closed)
                    con.Close();
            }

        }
        #endregion

        #region Delete
        ///
        /// Logical Delete Current Object
        ///

        ///
        public static bool Delete(string DeleteInput, object baseObject)
        {
            Type tp = baseObject.GetType();
            SqlConnection con = DataBaseController.GetConnection();
            try
            {
                DataTable result = new DataTable(tp.Name);
                con.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(DeleteInput + tp.Name, con))
                {
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    IDataParameter[] IDataParams = DataBaseController.GetObjectParameters(DeleteInput, baseObject);
                   
                    #region DEBUG Step 1
#if DEBUG
                    string strOut = adapter.SelectCommand.CommandText;
                    string split = string.Empty;
#endif
                    #endregion

                    if (IDataParams != null)
                    {
                        foreach (IDataParameter parameter2 in IDataParams)
                        {
                            adapter.SelectCommand.Parameters.Add(parameter2);

                            #region DEBUG Step 2
#if DEBUG
                            if (!(parameter2.Value is DBNull))
                                strOut += "\n" + split + string.Format("{0}={1}", parameter2.ParameterName, parameter2.Value);
                            else
                                strOut += "\n" + split + string.Format("{0}=null", parameter2.ParameterName);
                            split = ",";
#endif
                            #endregion
                        }


                    }

                    #region DEBUG Step 3
#if DEBUG
                    Logger("DataBaseController.Delete", strOut);
#endif
                    #endregion

                    adapter.Fill(result);
                }

                if (result.Rows.Count > 0)
                    return Convert.ToBoolean(result.Rows[0]["ret"]);

            }
            finally
            {
                if (con.State != ConnectionState.Closed)
                    con.Close();
            }

            return false;
        }

        #endregion
    }
    #endregion

}



Postar um comentário