//this method is used to insert the recipients of a single ImportJob into ListContactMaster and ContactMaster table
        public void ProcessSingleImportRecipients(DataRow dr)
        {
            bool isFirstRowHeader = false;
            bool isSendNotificationEmail = false;
            char[] delimiterChar = null;
            DataTable dtFileData = null;
            string[] strFileData = null;
            StreamReader sr = null;
            int JobID = 0;
            try
            {
                Logger.logdata(logforimportrecipients, "************** in ProcessSingleImportRecipients(..) method **************");
                Logger.logdata(logforimportrecipients, string.Format("ProcessSingleImportRecipients(..) has been started at {0}", DateTime.Now.ToString()));

                Logger.logdata(logforimportrecipients, string.Format("Import process satarted for job with ID: {0} at {1}", dr["ID"].ToString(), DateTime.Now.ToString()));
                //Get the filename of imported file.
                string filename = dr["GuidFilename"].ToString();
                ImportJobID = dr["ID"].ToString();
                DateTime importStartTime = DateTime.Now;
                if (File.Exists(Path.Combine(importFilePath, filename)))
                {
                    //update import job status
                    ImportJobPrimaryKey objImportJobPrimaryKey = new ImportJobPrimaryKey(Convert.ToInt64(ImportJobID));
                    ImportJob objImportJob = ImportJob.SelectOne(objImportJobPrimaryKey, connectionString);
                    objImportJob.Status = Common.ImportStatus.InProcess.ToString();
                    objImportJob.Update();
                    //Insert Import History
                    ReplyPositiveBL.ImportHistory objImportHistory = new ReplyPositiveBL.ImportHistory(connectionString);
                    objImportHistory.ImportJobID = Convert.ToInt64(ImportJobID);
                    objImportHistory.Staus = "Processing";
                    objImportHistory.StatusMessage = "File import is in processing.";
                    //objImportHistory.UpdatedCount = updatedCount;
                    //objImportHistory.InsertedCount = insertedCount;
                    objImportHistory.StartTime = importStartTime;
                    //objImportHistory.EndTime = DateTime.Now;
                    objImportHistory.InsertandReturnID(ref JobID);

                    isSendNotificationEmail = Convert.ToBoolean(dr["Notification"].ToString());
                    isFirstRowHeader = Convert.ToBoolean(dr["IsFileHasHeader"].ToString());
                    delimiterChar = getSeparatorChar(dr["FileType"].ToString());
                    Logger.logdata(logforimportrecipients, string.Format("import filepath: {0}", Path.Combine(importFilePath, filename)));
                    //Read the import file  content.
                    FileStream Stream = new FileStream(Path.Combine(importFilePath, filename), FileMode.Open, FileAccess.Read);
                    Logger.logdata(logforimportrecipients, string.Format("stream length: {0}", Stream.Length));
                    //convert file stream to datatable.
                    if (dr["FileType"].ToString().Equals("Excel(.xls, .xlsx)"))
                    {
                        if (filename.EndsWith(".xls"))
                            dtFileData = getDataTableOfExcel(Stream, filename, false);
                        else
                        {
                            string path = Path.Combine(importFilePath, filename);
                            System.Data.DataTable Contacts = new System.Data.DataTable();

                            ReadXlsx.Application xlApp;
                            ReadXlsx.Workbook xlWorkBook;
                            ReadXlsx.Worksheet xlWorkSheet;

                            xlApp = new ReadXlsx.ApplicationClass();
                            xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, ReadXlsx.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                            xlWorkSheet = (ReadXlsx.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                            int r;
                            int c;
                            int intRows;
                            int intCols;
                            ReadXlsx.Range excelCell = xlWorkSheet.UsedRange;
                            Object[,] values = (Object[,])excelCell.Value2;
                            intRows = values.GetLength(0);
                            if (intRows != 0)
                            {
                                intCols = values.GetLength(1);
                                if (intCols != 0)
                                {
                                    for (c = 1; c <= intCols; c++)
                                    {
                                        Contacts.Columns.Add(new DataColumn("Column" + c));
                                    }
                                    for (r = 1; r <= intRows; r++)
                                    {
                                        DataRow tblRow = Contacts.NewRow();
                                        for (c = 0; c < Contacts.Columns.Count; c++)
                                        {
                                            tblRow[c] = (values[r, (c + 1)] == null) ? string.Empty : values[r, (c + 1)];

                                        }
                                        Contacts.Rows.Add(tblRow);
                                    }
                                }
                            }
                            dtFileData = Contacts;
                            xlWorkSheet = null;
                            xlWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
                            xlWorkBook = null;
                            xlApp.Quit();
                            xlApp = null;
                        }
                    }
                    else
                    {
                        sr = new StreamReader(Stream);
                        strFileData = sr.ReadToEnd().Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                        dtFileData = convertFileToDatatable(strFileData, isFirstRowHeader, delimiterChar);
                    }

                    //remove header from datatable if first row is header.
                    if (isFirstRowHeader)
                    {
                        dtFileData.Rows.RemoveAt(0);
                    }

                    //get fieldmap

                    sqlQry = "select * from Fieldmap where ImportJobID =" + ImportJobID;
                    ds = getDataset(sqlQry);
                    DataTable dtFieldMap = ds.Tables[0];

                    //The following code is for removing Unmapped columns while importing a file to List
                    if (dtFileData.Columns.Count != dtFieldMap.Rows.Count)
                    {
                        string[] ActualColumns = (from dc in dtFileData.Columns.Cast<DataColumn>() select dc.ColumnName).ToArray();
                        string[] MappedColumns = dtFieldMap.AsEnumerable().Select(row => row.Field<string>("SourceField")).ToArray();
                        string[] UnMappedColumns = ActualColumns.Except(MappedColumns).ToArray();
                        foreach (string clm in UnMappedColumns)
                        {
                            if (dtFileData.Columns.Contains(clm))
                                dtFileData.Columns.Remove(clm);
                        }
                    }
                    //Bulk insert the import file content in to temp table.
                    //insertImportingContactsInToTemp(dtFileData, dtFieldMap, ImportJobID);

                    //Logger.logdata(logforimportrecipients, string.Format("imported file records are bulk inserted to temp table: {0}", dtFileData.Rows.Count));

                    // process file data
                    if (dtFileData.Rows.Count > 0)
                    {
                        Logger.logdata(logforimportrecipients, string.Format("total number of records found in imported file: {0}", dtFileData.Rows.Count));
                        string emailAddressColumn = dtFieldMap.Select("DestinationField='EmailAddress'")[0]["SourceField"].ToString();

                        var v = from item in dtFieldMap.AsEnumerable() where item["DestinationField"].ToString() == "IsActive" select item;
                        foreach (var obj in v)
                        {
                            IsActiveField = dtFieldMap.Select("DestinationField='IsActive'")[0]["SourceField"].ToString();
                        }

                        DataTable dtValidEmailData = checkInValidEmailAddresses(dtFileData, emailAddressColumn, IsActiveField);
                        //to select distinct rows
                        //dtValidEmailData = dtValidEmailData.DefaultView.ToTable(true, Columns);
                        dtValidEmailData = RemoveDuplicateRows(dtValidEmailData, emailAddressColumn, dtFieldMap);
                        Logger.logdata(logforimportrecipients, string.Format("valid recipients count in selected file: {0}", dtValidEmailData.Rows.Count));
                        int totalRecipientsFound = dtFileData.Rows.Count;
                        int validRecipientsFound = dtValidEmailData.Rows.Count;
                        int inValidRecipientsFound = totalRecipientsFound - validRecipientsFound;
                        int insertedCount = 0;
                        int updatedCount = 0;
                        if (dtValidEmailData.Rows.Count == 0)
                        {
                            //throw new Exception("Please select proper file to import.");
                        }
                        //upsert into contactmaster and listcontactmaster based on importlimit.
                        var AllRecords = from item in dtValidEmailData.AsEnumerable() select item;
                        Logger.logdata(logforimportrecipients, string.Format("all records count in selected file: {0}", AllRecords.Count()));
                        int nextRecords = 0;
                        int importLimit = Convert.ToInt32(ConfigurationManager.AppSettings["ImportRecipientsLimit"].ToString());
                        Logger.logdata(logforimportrecipients, string.Format("Import limit: {0}", importLimit));
                        while (true)
                        {
                            Logger.logdata(logforimportrecipients, string.Format("looping records: {0}", nextRecords));
                            var top1000 = AllRecords.Skip(nextRecords).Take(importLimit);
                            if (top1000.Count() == 0)
                            {
                                break;
                            }
                            else
                            {
                                DataTable dtContacts = dtValidEmailData.Clone();
                                top1000.CopyToDataTable(dtContacts, LoadOption.PreserveChanges);
                                DataTable dtListContactMasterResult = ImportRecordsToContactMaster(dtContacts, dtFieldMap, dr);
                                insertedCount += Convert.ToInt32(dtListContactMasterResult.Compute("Count(Status)", "Status='INSERT' and ListMasterID=" + Convert.ToInt64(dr["ListID"].ToString())));
                                updatedCount += Convert.ToInt32(dtListContactMasterResult.Compute("Count(Status)", "Status='UPDATE' and ListMasterID=" + Convert.ToInt64(dr["ListID"].ToString())));
                                nextRecords = nextRecords + importLimit;
                            }
                        }
                        Logger.logdata(logforimportrecipients, string.Format("total number of records inserted: {0}", insertedCount));
                        Logger.logdata(logforimportrecipients, string.Format("total number of records found updated: {0}", updatedCount));

                        //build html string to send notification email.
                        //get listname of listid``````````````
                        ListMasterPrimaryKey objListMasterPrimaryKey = new ListMasterPrimaryKey(Convert.ToInt64(dr["ListID"]));
                        ListMaster objListmaster = ListMaster.SelectOne(objListMasterPrimaryKey, connectionString);

                        StringBuilder objStringBuilder = new StringBuilder();
                        objStringBuilder.Append("<table width=100%>");
                        objStringBuilder.Append("<tr><td>" + string.Format("Imported file: <b>{0}</b>.", dr["Filename"].ToString()) + "</td></tr>");
                        objStringBuilder.Append("<tr><td>" + string.Format("Total number of recipients found: <b>{0}</b>.", totalRecipientsFound) + "</td></tr>");
                        objStringBuilder.Append("<tr><td>" + string.Format("Number of valid recipients found: <b>{0}</b>.", validRecipientsFound) + "</td></tr>");
                        objStringBuilder.Append("<tr><td>" + string.Format("Number of InValid recipients found: <b>{0}</b>.", inValidRecipientsFound) + "</td></tr>");
                        objStringBuilder.Append("<br>");
                        objStringBuilder.Append("<br>");
                        objStringBuilder.Append("<tr><td><b>ListName</b></td><td><b>Inserted</b></td><td><b>Updated</b></td>");
                        objStringBuilder.Append("<tr><td colspan=3><hr></td></tr>");
                        objStringBuilder.Append("<tr><td>" + objListmaster.ListName + "</td><td>" + insertedCount + "</td><td>" + updatedCount + "</td></tr>");

                        //update import job status
                        //ImportJobPrimaryKey
                        objImportJobPrimaryKey = new ImportJobPrimaryKey(Convert.ToInt64(ImportJobID));
                        //ImportJob
                        objImportJob = ImportJob.SelectOne(objImportJobPrimaryKey, connectionString);
                        objImportJob.Status = Common.ImportStatus.Success.ToString();
                        objImportJob.Update();

                        //Insert Import History
                        //ReplyPositiveBL.ImportHistory
                        objImportHistory = new ReplyPositiveBL.ImportHistory(connectionString);
                        objImportHistory.ImportJobID = Convert.ToInt64(ImportJobID);
                        objImportHistory.Staus = "SUCCESS";
                        objImportHistory.StatusMessage = "File import process completed successfully.";
                        objImportHistory.UpdatedCount = updatedCount;
                        objImportHistory.InsertedCount = insertedCount;
                        objImportHistory.InvalidCount = inValidRecipientsFound;
                        objImportHistory.StartTime = importStartTime;
                        objImportHistory.ID = JobID;
                        objImportHistory.EndTime = DateTime.Now;

                        objImportHistory.Update();

                        //send notification email.
                        if (isSendNotificationEmail)
                            sendNotificationMail(objStringBuilder.ToString(), dr["NotificationEmailIDs"].ToString(), filename);
                    }
                }
                Logger.logdata(logforimportrecipients, string.Format("Import process ended for job with ID: {0} at {1}", dr["ID"].ToString(), DateTime.Now.ToString()));
                Logger.logdata(logforimportrecipients, string.Format("ProcessSingleImportRecipients(..) method has been ended at {0}", DateTime.Now.ToString()));
                Logger.logdata(logforimportrecipients, "************** Exit ProcessSingleImportRecipients(..) method **************");
            }
            catch (Exception ex)
            {
                //update import job status
                ImportJobPrimaryKey objImportJobPrimaryKey = new ImportJobPrimaryKey(Convert.ToInt64(ImportJobID));
                ImportJob objImportJob = ImportJob.SelectOne(objImportJobPrimaryKey, connectionString);
                objImportJob.Status = Common.ImportStatus.Error.ToString();
                objImportJob.Update();
                throw ex;
            }
        }
        /// <summary>
        /// Populates the fields for multiple objects from the columns found in an open reader.
        /// </summary>
        ///
        /// <param name="rdr" type="IDataReader">An object that implements the IDataReader interface</param>
        ///
        /// <returns>Object of ImportHistories</returns>
        ///
        /// <remarks>
        ///
        /// <RevisionHistory>
        /// Author				Date			Description
        /// DLGenerator			11/21/2009 4:19:25 PM		Created function
        /// 
        /// </RevisionHistory>
        ///
        /// </remarks>
        ///
        internal static ImportHistories PopulateObjectsFromReader(IDataReader rdr, string ConnectionString)
        {
            ImportHistories list = new ImportHistories();

            while (rdr.Read())
            {
                ImportHistory obj = new ImportHistory(ConnectionString);
                PopulateObjectFromReader(obj,rdr);
                list.Add(obj);
            }
            return list;
        }
        /// <summary>
        /// Populates the fields for multiple objects from the columns found in an open reader.
        /// </summary>
        ///
        /// <param name="rdr" type="IDataReader">An object that implements the IDataReader interface</param>
        ///
        /// <returns>Object of ImportHistories</returns>
        ///
        /// <remarks>
        ///
        /// <RevisionHistory>
        /// Author				Date			Description
        /// DLGenerator			11/21/2009 4:19:25 PM		Created function
        /// 
        /// </RevisionHistory>
        ///
        /// </remarks>
        ///
        internal static ImportHistories PopulateObjectsFromReaderWithCheckingReader(IDataReader rdr, DatabaseHelper oDatabaseHelper, string ConnectionString)
        {
            ImportHistories list = new ImportHistories();

            if (rdr.Read())
            {
                ImportHistory obj = new ImportHistory(ConnectionString);
                PopulateObjectFromReader(obj, rdr);
                list.Add(obj);
                while (rdr.Read())
                {
                    obj = new ImportHistory(ConnectionString);
                    PopulateObjectFromReader(obj, rdr);
                    list.Add(obj);
                }
                oDatabaseHelper.Dispose();
                return list;
            }
            else
            {
                oDatabaseHelper.Dispose();
                return null;
            }
        }
        /// <summary>
        /// This method will return an object representing the record matching the primary key information specified.
        /// </summary>
        ///
        /// <param name="pk" type="ImportHistoryPrimaryKey">Primary Key information based on which data is to be fetched.</param>
        ///
        /// <returns>object of class ImportHistory</returns>
        ///
        /// <remarks>
        ///
        /// <RevisionHistory>
        /// Author				Date			Description
        /// DLGenerator			11/21/2009 4:19:25 PM		Created function
        /// 
        /// </RevisionHistory>
        ///
        /// </remarks>
        ///
        public static ImportHistory SelectOne(ImportHistoryPrimaryKey pk, string ConnectionString)
        {
            DatabaseHelper oDatabaseHelper = new DatabaseHelper(ConnectionString);
            bool ExecutionState = false;

            // Pass the values of all key parameters to the stored procedure.
            System.Collections.Specialized.NameValueCollection nvc = pk.GetKeysAndValues();
            foreach (string key in nvc.Keys)
            {
                oDatabaseHelper.AddParameter("@" + key,nvc[key] );
            }
            // The parameter '@ErrorCode' will contain the status after execution of the stored procedure.
            oDatabaseHelper.AddParameter("@ErrorCode", -1, System.Data.ParameterDirection.Output);

            IDataReader dr=oDatabaseHelper.ExecuteReader("sp_ImportHistory_SelectbyPrimaryKey", ref ExecutionState);
            if (dr.Read())
            {
                ImportHistory obj=new ImportHistory(ConnectionString);
                PopulateObjectFromReader(obj,dr);
                dr.Close();
                oDatabaseHelper.Dispose();
                return obj;
            }
            else
            {
                dr.Close();
                oDatabaseHelper.Dispose();
                return null;
            }
        }