public void UpdateBatchStatusRecordTest()
        {
            BatchStatus status = new BatchStatus()
            {
                BatchName = "TESTBatch",
                BatchId = Guid.NewGuid().ToString(),
                Status = "Failed",
                StartTime = DateTime.Now,
                BatchRefrence = "Refrence",
                Comment = "Starting"
            };

            _handler.CreateBatchStatusRecord(status);

            IList<BatchStatus> statusList = _handler.GetUnhandledFailedBatches();

            foreach(var stat in statusList)
            {
                _handler.SetBatchHandled(stat);
            }

            IList<BatchStatus> statusList2 = _handler.GetUnhandledFailedBatches();

            Assert.True(statusList2.Count == 0);
        }
        public bool UpdateBatchStatusRecord(BatchStatus status)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(GetUpdateCommand()))
                    {
                        cmd.Connection = conn;
                        cmd.Parameters.Clear();
                        SetParameters(cmd, status);

                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (SqlException exc)
                        {
                            Console.WriteLine("Error writing " + status.ToString() + " to database.   Exception: " + exc.Message);
                            throw exc;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                return false;
            }

            return true;
        }
        public void GetUnhandledFailedBatchesTest()
        {
            BatchStatus status = new BatchStatus()
            {
                BatchName = "TESTBatch",
                BatchId = Guid.NewGuid().ToString(),
                Status = "Failed",
                StartTime = DateTime.Now,
                BatchRefrence = "Refrence",
                Comment = "Starting"
            };

            _handler.CreateBatchStatusRecord(status);

            IList<BatchStatus> _statusList = _handler.GetUnhandledFailedBatches();
            Assert.True(_statusList.Count > 0);

            _handler.SetBatchHandled(status);
        }
        public void RunDataLoader()
        {
            string currentClassName = this.GetType().Name;
            string batchrefrence = Initialize();
            

             _status = new BatchStatus() { BatchName = currentClassName, BatchId = _guid, BatchRefrence = batchrefrence, StartTime = DateTime.Now, Comment = "", Status = "Started" };
            _statusHandler.CreateBatchStatusRecord(_status);

            try
            {
                SetStatusLoad(_status);
                LoadData();

                SetStatusTransform(_status);
                TransformData();

                SetStatusSaving(_status);
                SaveData();

                SetStatusCleanUp(_status);
                CleanUp();

                SetStatusFinished(_status);
            }
            catch (DataLoaderException dx)
            {
                _status.Status = dx.Status;
                _status.Comment = dx.Message;
                _status.FinishTime = DateTime.Now;
                _statusHandler.UpdateBatchStatusRecord(_status);
            }
            catch (Exception ex)
            {
                _status.FinishTime = DateTime.Now;
                _status.Status = "Failed";
                _status.Comment = ex.Message;
                _statusHandler.UpdateBatchStatusRecord(_status);
            }
        }
 private void SetStatusLoad(BatchStatus status)
 {
     status.StartLoadTime = DateTime.Now;
     status.Status = "Loading";
     _statusHandler.UpdateBatchStatusRecord(status);
 }
 private void SetStatusTransform(BatchStatus status)
 {
     status.StartTransformTime = DateTime.Now;
     status.Status = "Transforming";
     _statusHandler.UpdateBatchStatusRecord(status);
 }
 private void SetStatusSaving(BatchStatus status)
 {
     status.StartSaveTime = DateTime.Now;
     status.Status = "Saving";
     _statusHandler.UpdateBatchStatusRecord(status);
 }
 private void SetStatusCleanUp(BatchStatus status)
 {
     status.StartCleanupTime = DateTime.Now;
     status.Status = "Cleaning up";
     _statusHandler.UpdateBatchStatusRecord(status);
 }
 private void SetStatusFinished(BatchStatus status)
 {
     status.FinishTime = DateTime.Now;
     status.Status = "Finished";
     _statusHandler.UpdateBatchStatusRecord(status);
 }
        private void SetParameters(SqlCommand cmd, BatchStatus status)
        {
            cmd.Parameters.Add("@batchname", System.Data.SqlDbType.NChar).Value = status.BatchName;
            cmd.Parameters.Add("@batchid", System.Data.SqlDbType.NChar).Value = status.BatchId;
            cmd.Parameters.Add("@start_time", System.Data.SqlDbType.DateTime).Value = status.StartTime;

            cmd.Parameters.Add("@batchrefrence", System.Data.SqlDbType.NChar).Value = status.BatchRefrence;


            if (status.StartLoadTime != null)
                cmd.Parameters.Add("@start_load_time", System.Data.SqlDbType.DateTime).Value = status.StartLoadTime;
            else
                cmd.Parameters.Add("@start_load_time", System.Data.SqlDbType.DateTime).Value = DBNull.Value;

            if (status.StartTransformTime != null)
                cmd.Parameters.Add("@start_transform_time", System.Data.SqlDbType.DateTime).Value = status.StartTransformTime;
            else
                cmd.Parameters.Add("@start_transform_time", System.Data.SqlDbType.DateTime).Value = DBNull.Value;

            if (status.StartSaveTime != null)
                cmd.Parameters.Add("@start_save_time", System.Data.SqlDbType.DateTime).Value = status.StartSaveTime;
            else
                cmd.Parameters.Add("@start_save_time", System.Data.SqlDbType.DateTime).Value = DBNull.Value;

            if (status.StartCleanupTime != null)
                cmd.Parameters.Add("@start_cleanup_time", System.Data.SqlDbType.DateTime).Value = status.StartCleanupTime;
            else
                cmd.Parameters.Add("@start_cleanup_time", System.Data.SqlDbType.DateTime).Value = DBNull.Value;

            if (status.FinishTime != null)
                cmd.Parameters.Add("@finish_time", System.Data.SqlDbType.DateTime).Value = status.FinishTime;
            else
                cmd.Parameters.Add("@finish_time", System.Data.SqlDbType.DateTime).Value = DBNull.Value;

            if (status.IsHandled != null)
                cmd.Parameters.Add("@is_handled", System.Data.SqlDbType.SmallInt).Value = status.IsHandled;
            else
                cmd.Parameters.Add("@is_handled", System.Data.SqlDbType.SmallInt).Value = DBNull.Value;

            cmd.Parameters.Add("@comment", System.Data.SqlDbType.Text).Value = status.Comment;
            cmd.Parameters.Add("@status", System.Data.SqlDbType.NChar).Value = status.Status;
        }
 private void SetBatchHandledCommandParams(SqlCommand cmd, BatchStatus status)
 {
     cmd.Parameters.Add("@batchname", System.Data.SqlDbType.NChar).Value = status.BatchName;
     cmd.Parameters.Add("@batchid", System.Data.SqlDbType.NChar).Value = status.BatchId;
 }
        public BatchStatus GetStatusRecord(string batchname, string batchid)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GetStatusRecordCommand()))
                {
                    cmd.Connection = conn;
                    cmd.Parameters.Clear();

                    SetStatusRecordCommandParams(cmd, batchname, batchid);

                    try
                    {
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            BatchStatus status = new BatchStatus()
                            {
                                BatchName = reader.GetString(0).Trim(),
                                BatchId = reader.GetString(1).Trim(),
                                BatchRefrence = reader.IsDBNull(2) ? null : reader.GetString(2).Trim(),
                                StartTime = reader.IsDBNull(3) ? null : reader.GetDateTime(3) as DateTime?,
                                StartLoadTime = reader.IsDBNull(4) ? null : reader.GetDateTime(4) as DateTime?,
                                StartTransformTime = reader.IsDBNull(5) ? null : reader.GetDateTime(5) as DateTime?,
                                StartSaveTime = reader.IsDBNull(6) ? null : reader.GetDateTime(6) as DateTime?,
                                StartCleanupTime = reader.IsDBNull(7) ? null : reader.GetDateTime(7) as DateTime?,
                                FinishTime = reader.IsDBNull(8) ? null : reader.GetDateTime(8) as DateTime?,
                                Comment = reader.IsDBNull(9) ? "" : reader.GetString(9).Trim(),
                                Status = reader.IsDBNull(10) ? "" : reader.GetString(10).Trim()

                            };
                            return status;
                        }

                        //If nothing is found
                        return null;
                    }
                    catch (SqlException exc)
                    {
                        Console.WriteLine("Error checking if batch exists.   Exception: " + exc.Message);
                        throw exc;
                    }
                }
            }
        }
 public bool SetBatchHandled(BatchStatus status)
 {
     if ((status.IsHandled == null) || (status.IsHandled == false))
     {
         status.IsHandled = true;
     }
     return UpdateBatchStatusRecord(status);
 }
        public IList<BatchStatus> GetUnhandledFailedBatches()
        {

            List<BatchStatus> batches = new List<BatchStatus>();

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GetUnhandledFailedBatchesQuery()))
                {
                    cmd.Connection = conn;
                    cmd.Parameters.Clear();

                    try
                    {
                        SqlDataReader reader = cmd.ExecuteReader();
                        while(reader.Read())
                        {
                            BatchStatus status = new BatchStatus()
                            {
                                BatchName = reader.GetString(0).Trim(),
                                BatchId = reader.GetString(1).Trim(),
                                BatchRefrence = reader.IsDBNull(2) ? null : reader.GetString(2).Trim(),
                                StartTime = reader.IsDBNull(3) ? null : reader.GetDateTime(3) as DateTime?,
                                StartLoadTime = reader.IsDBNull(4) ? null : reader.GetDateTime(4) as DateTime?,
                                StartTransformTime = reader.IsDBNull(5) ? null : reader.GetDateTime(5) as DateTime?,
                                StartSaveTime = reader.IsDBNull(6) ? null : reader.GetDateTime(6) as DateTime?,
                                StartCleanupTime = reader.IsDBNull(7) ? null : reader.GetDateTime(7) as DateTime?,
                                FinishTime = reader.IsDBNull(8) ? null : reader.GetDateTime(8) as DateTime?,
                                Comment = reader.IsDBNull(9) ? "" : reader.GetString(9).Trim(),
                                Status = reader.IsDBNull(10) ? "" : reader.GetString(10).Trim()

                            };
                            batches.Add(status);
                        }
                        return batches;
                    }
                    catch (SqlException exc)
                    {
                        Console.WriteLine("Error checking if batch exists.   Exception: " + exc.Message);
                        throw exc;
                    }
                }
            }
        }
        public void GetBatchStatusRecordTest()
        {
            BatchStatus status = new BatchStatus()
            {
                BatchName = "TESTBatch",
                BatchId = Guid.NewGuid().ToString(),
                Status = "Failed",
                StartTime = DateTime.Now,
                BatchRefrence = "Refrence",
                Comment = "Starting"
            };

            _handler.CreateBatchStatusRecord(status);


            BatchStatus status2 = _handler.GetStatusRecord(status.BatchName, status.BatchId);


            Assert.True(status2.Status == "Failed");
        }