dimanche 30 octobre 2016

How to improve object and data layer - LINQ TO SQL

I have been using Layered Architecture in my web application. Can anyone please advise if my data layer classes (both Linq and booking class) can be improved?

I have just started learning about solid design principles and Dependency injections. Does my object or data layer classes achieve any of the SOLID principles?

Object Layer

        [Table()]
        public class Clerks
        {
            [Column(CanBeNull = false, IsPrimaryKey = true, IsDbGenerated = true)]
            public Int32 ClerkID { get; set; }
            [Column()]
            public string Name { get; set; }
            [Column()]
            public string Email { get; set; }
            [Column(IsVersion = true, IsDbGenerated = true)]
            public byte[] Version { get; set; }
        }

LINQ Class in Data Layer

            /// <summary>
        /// LinqDatabase Class extends DataContext
        /// Entry point for the LINQ to SQL framework
        /// </summary>
        public class LinqDatabase : DataContext
        {

            private const Int32 timeOut = 20;

            private static string connStr = ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString;



            public LinqDatabase(bool objTrack = false): base(connStr)
            {
                base.CommandTimeout = timeOut;
                //to track the original value and object identity
                base.ObjectTrackingEnabled = objTrack;
            }

            public Table<Clerks> Clerks;
            public Table<Authorities> Authorities;
            public Table<Bookings> Bookings;


            public static DataTable ToDataTable<T>(IEnumerable<T> collection, string tableName)
            {
                DataTable tbl = ToDataTable(collection);
                tbl.TableName = tableName;
                return tbl;
            }


            public static DataTable ToDataTable<T>(IEnumerable<T> collection)
            {
                DataTable dt = new DataTable();
                Type _t = typeof(T);
                PropertyInfo[] pia = _t.GetProperties();

                //Create the columns in the DataTable
                foreach (PropertyInfo pi in pia)
                {
                    dt.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType);
                }

                //Populate the table
                foreach (T item in collection)
                {
                    DataRow dr = dt.NewRow();
                    dr.BeginEdit();
                    foreach (PropertyInfo pi in pia)
                    {
                        dr[pi.Name] = pi.GetValue(item, null);
                    }
                    dr.EndEdit();
                    dt.Rows.Add(dr);
                }

                return dt;
            }
        }

Booking Data Layer Class

    public class BookingsDB
    {

        private LinqDatabase db = new LinqDatabase();

        public void Dispose()
        {
            db.Dispose();
        }

        public static void Insert(Bookings l)
        {
            LinqDatabase dbs = new LinqDatabase(true);

            try
            {
                dbs.Bookings.InsertOnSubmit(l);
                dbs.SubmitChanges();
            }
            catch (Exception ex)
            {
                //log error

            }
            finally
            {
                dbs.Dispose();
                dbs = null;
            }
        }


        /// <summary>
        /// Get Report in specific date range
        /// </summary>
        /// <param name=" p_startDate, p_EndDate">Passing date range to get a report</param>
        public DataTable GetReport(DateTime p_startDate, DateTime p_EndDate)
        {

            dynamic query = (from b in db.Bookings
                             join a in db.Authorities on b.AuthorityID equals a.AuthorityID
                             join c in db.Clerks on b.ClerkID equals c.ClerkID
                             where b.BookingDate >= p_startDate && b.BookingDate <= p_EndDate
                             orderby a.Name , b.BookingDate 
                             select new { AuthorityName = a.Name, b.BookingDate, ClerkName = c.Name, c.Email });

            return LinqDatabase.ToDataTable(query);

        }
    }

Aucun commentaire:

Enregistrer un commentaire