Documentation

ReminderData

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace ReminderData
{
    public class Reminder
    {
     
        public class Event
        {
            public int Key { get; set; }
            public string Description { get; set; }
            public DateTime EventStart { get; set; }
            public DateTime EventEnd { get; set; }
            public DateTime RemindStart { get; set; }
            public bool EventDone { get; set; }
            public bool EventRenew { get; set; }
            public string EventRenewUnit { get; set; }
            public int EventRenewUnitNo { get; set; }
            public DateTime EventLastUpdated { get; set; }

        }
        public List AllEvents(string Connection)
        {
            return GetSqlData(Connection);
        }

        public List OpenEvents(string Connection)
        {
            return GetSqlData(Connection, "where EventDone = 0");
        }
        public List DueEvents(string Connection)
        {
            return GetSqlData(Connection, "where EventDone = 0 and EventRemind <= GetDate()");
        }

        private List GetSqlData(string connectString, string Where = "")
        {
            List Selected = new List();
            using (SqlConnection conn = new SqlConnection(connectString))
            {
                conn.Open();
                string SqlSelect = "SELECT * FROM Events";
                if (Where != "")
                {
                    SqlSelect += " " + Where;
                }
                SqlSelect += " Order by EventRemind";

                using (SqlCommand com = new SqlCommand(SqlSelect, conn))
                {
                    SqlDataReader reader = com.ExecuteReader();
                    while (reader.Read())
                    {
                        Selected.Add(new Event
                        {
                            Key = reader.GetInt32(0),
                            Description = reader.GetString(1),
                            EventStart = reader.GetDateTime(2),
                            EventEnd = reader.GetDateTime(3),
                            RemindStart = reader.GetDateTime(4),
                            EventDone = reader.GetBoolean(5),
                            EventRenew = reader.GetBoolean(6),
                            EventRenewUnit = reader.GetString(7),
                            EventRenewUnitNo = reader.GetInt32(8)
                        });
                    }
                }
                conn.Close();
            }
            return Selected;
        }
        private string ValidateSqlData(List sqlData)
        {
            string strError = "";
            DateTime dDate;

            if (sqlData.Count() < 9)
            {
                strError = "Too few columns passed";
            }
            else if (sqlData[1] == "")
            {
                strError = "Description missing";
            }
            else if (!DateTime.TryParse(sqlData[2], out dDate))
            {
                strError = "Invalid Start Date";
            }
            else if (!DateTime.TryParse(sqlData[3], out dDate))
            {
                strError = "Invalid End Date";
            }
            else if (!DateTime.TryParse(sqlData[4], out dDate))
            {
                strError = "Invalid Remind Date";
            }
            else if (sqlData[7] != "" && sqlData[7] != "Months" && sqlData[7] != "Years")
            {
                strError = "Renew Units must be BLANK, Months or Years";
            }

            return strError;
        }
        public string InsertSqlData(string Connection, List sqlData)
        {
            string strError = ValidateSqlData(sqlData);
            if (strError == "")
            {
                string sqlInsert = "INSERT INTO EVENTS (EventDesc, EventStart, EventEnd, EventRemind, EventDone, " +
                    "EventRenew, EventRenewUnit, EventRenewUnitNo) VALUES (" +
                    string.Join(",", sqlData.Skip(1).Select(x => string.Format("'{0}'", x))) + ")";
                using (SqlConnection conn = new SqlConnection(Connection))
                {
                    conn.Open();

                    using (SqlCommand com = new SqlCommand(sqlInsert, conn))
                    {
                        SqlDataReader reader = com.ExecuteReader();
                        while (reader.Read())
                        {

                        }
                    }
                    conn.Close();
                }
            }
            
            return strError;
        }

        public string UpdateSqlData(string Connection, List sqlData, List sqlData8)
        {
            string strError = ValidateSqlData(sqlData);
            if (strError == "")
            {
                string sqlUpdate = "UPDATE EVENTS SET EventDesc = '" + sqlData[1] + "', " +
                                                     "EventStart = '" + sqlData[2] + "', " +
                                                     "EventEnd = '" + sqlData[3] + "', " +
                                                     "EventRemind = '" + sqlData[4] + "', " +
                                                     "EventDone = '" + sqlData[5] + "', " +
                                                     "EventRenew = '" + sqlData[6] + "', " +
                                                     "EventRenewUnit = '" + sqlData[7] + "', " +
                                                     "EventRenewUnitNo = '" + sqlData[8] + "' " +
                                                     "where EventId = " + sqlData[0];
                using (SqlConnection conn = new SqlConnection(Connection))
                {
                    conn.Open();

                    using (SqlCommand com = new SqlCommand(sqlUpdate, conn))
                    {
                        SqlDataReader reader = com.ExecuteReader();
                        while (reader.Read())
                        {

                        }
                    }
                    conn.Close();
                }
                if (sqlData8.Count > 0)
                {

                    string sqlInsert = "INSERT INTO EVENTS (EventDesc, EventStart, EventEnd, EventRemind, EventDone, " +
                    "EventRenew, EventRenewUnit, EventRenewUnitNo) VALUES (" +
                    string.Join(",", sqlData8.Skip(1).Select(x => string.Format("'{0}'", x))) + ")";
                    // DUPLICATE CODE HERE ************************
                    using (SqlConnection conn = new SqlConnection(Connection))
                    {
                        conn.Open();

                        using (SqlCommand com = new SqlCommand(sqlInsert, conn))
                        {
                            SqlDataReader reader = com.ExecuteReader();
                            while (reader.Read())
                            {

                            }
                        }
                        conn.Close();
                    }
                    // DUPLICATE CODE HERE ************************

                }
            }

            return strError;
        }
    }
}