Custom DAL Class SQL ORM ASP .NET

(common.DataObject may be of your choosing or may simply replace with dynamic)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Data.SqlClient;

using AIS.Common; //this is a common namespace I use in examples throughout my site
using System.Reflection;
using System.Dynamic;

//TODO: consider returning ienumerable in sp return values for lazy eval vs .tolist immediate eval
namespace AIS.DAL.AppName
{
    public static class StoredProcedures
    {
        public delegate void ErrorHandler(Exception ex);
        /// <summary>
        /// If no custom error handling is bound to this event, exceptions will be thrown back up to the calling function.
        /// If custom handling is bound to this event, ensure it does not perform a redirect or kill the thread unless you intend to abort the procedural
        /// steps following the method/function call which threw the error.
        /// </summary>
        public static event ErrorHandler HandleError;

        #region Unique Procedures
        public static List<Common.DataObject> LoadUserSessions_All(dynamic o)
        {
            return ExecuteRead("an_get_db_fn1", o);
        }

        public static List<Common.DataObject> LoadUserSessionsDetails_LiveStream(dynamic o)
        {
            return ExecuteRead("an_get_db_fn2", o);
        }

        public static List<Common.DataObject> LoadUserSessionsDetails_Live(dynamic o)
        {
            return ExecuteRead("an_get_db_fn3", o);
        }

        public static int LogChat()
        {
            return ExecuteScalar("an_get_db_fn4", null);
        }

        public static int LogError()
        {
            return ExecuteScalar("an_get_db_fn5", null);
        }
        #endregion

        //TODO: consider hiding from external assemblies which would require strong mappings above
        #region Execution Logic
        public static List<Common.DataObject> ExecuteRead(string procedurename, dynamic param)
        {
            try
            {
                SqlDataSource sds = new SqlDataSource();
                sds.ConnectionString = ConfigValues.TrainingPortalConnectionString;
                sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
                sds.SelectCommand = procedurename;

                if (param != null)
                {
                    foreach (PropertyInfo pi in param.GetType().GetProperties())
                    {
                        object pval = pi.GetValue(param, null);
                        if (pval != null)
                        {
                            sds.SelectParameters.Add(pi.Name, pval.ToString());
                        }
                    }
                }

                List<Common.DataObject> results = new List<Common.DataObject>();
                //sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => Load_AddResult<dynamic>(o, ref results));
                sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => Load_AddResult<Common.DataObject>(o, ref results));

                return results;
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
                return null;
            }
        }

        public static void Load_AddResult<t>(Common.DataObject o, ref List<t> results)
        {
            try
            {
                t r = (t)Activator.CreateInstance(typeof(t));

                foreach (PropertyInfo pi in typeof(t).GetProperties())
                {
                    object v = o[pi.Name].ToString();
                    Type pt = Type.GetType(pi.PropertyType.FullName);
                    //try { pi.SetValue(r, Convert.ChangeType(v, pt), null); }
                    //catch (Exception ex) { HandleError_Condensed(ex); }

                    o.Add(pi.Name, Convert.ChangeType(v, pt));
                }

                results.Add(r);
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
            }
        }

        //public static void Load_AddResult<t>(dynamic o, ref List<t> results)
        //{
        //    try
        //    {
        //        t r = (t)Activator.CreateInstance(typeof(t));

        //        foreach (PropertyInfo pi in typeof(t).GetProperties())
        //        {
        //            object v = o[pi.Name].ToString();
        //            Type pt = Type.GetType(pi.PropertyType.FullName);
        //            try { pi.SetValue(r, Convert.ChangeType(v, pt), null); }
        //            catch (Exception ex) { HandleError_Condensed(ex); }
        //        }

        //        results.Add(r);
        //    }
        //    catch (Exception ex)
        //    {
        //        HandleError_Condensed(ex);
        //    }
        //}

        public static void ExecuteNonScalar(string procedurename, dynamic param)
        {
            try
            {
                ExecuteScalar(procedurename, param);
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
            }
        }

        public static int ExecuteScalar(string procedurename, dynamic param)
        {
            try
            {
                SqlDataSource sds = new SqlDataSource();
                sds.ConnectionString = ConfigValues.TrainingPortalConnectionString;
                sds.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure;
                sds.UpdateCommand = procedurename;

                if (param != null)
                {
                    foreach (PropertyInfo pi in param.GetType().GetProperties())
                    {
                        object pval = pi.GetValue(param, null);
                        if (pval != null)
                        {
                            sds.SelectParameters.Add(pi.Name, pval.ToString());
                        }
                    }
                }

                return sds.Update();
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
                return 1; //1 signifies error in tsql
            }
        }
        #endregion

        private static void HandleError_Condensed(Exception ex)
        {
            if (HandleError != null) { HandleError(ex); } else { throw new Exception(ex.Message, ex); } 
        }
    }
}

About Ronnie Diaz

Ronnie Diaz is a software engineer and tech consultant. Ronnie started his career in front-end and back-end development for companies in ecommerce, service industries and remote education. This work transitioned from traditional desktop client-server applications through early cloud development. Software included human resource management and service technician workflows, online retail e-commerce and electronic ordering and fulfillment, IVR customer relational systems, and video streaming remote learning SCORM web applications. Hands on server experience and software performance optimization led to creation of a startup business focused on collocated data center services and continued experience with video streaming hardware and software. This led to a career in Amazon Prime Video where Ronnie is currently employed, building software and systems which stream live sports and events for millions of viewers around the world.

Posted on December 13, 2013, in .NET Reference, Programming & Development and tagged , , , , , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: