How to StreamRead CSV file and pass to DataTable then Bulk Copy SQL

Hello Experts,

I'm trying to StreamRead a CSV file, pass the values to a class object which get read into a DataTable then Bulk Copy to SQL.  Also, prior to passing the values to the object it checks sql for a duplicate date.  If a duplicate date is found then delete all rows with that date then bulk copy.  My proplem is that only one row get bulk copy of thousands of rows.

Below are my codes.  Thank you for your assistance.

CSV file

02/26/2014,57693,08,CBT ,001602,03/02/2014, 0 , 0 ,C, 18059 , 376244 
02/26/2014,23512,01,CBT ,001602,03/02/2014, 72 , 0 ,N, 18059 , 376244 
02/26/2014,45664,01,CBT ,001602,03/02/2014, 48 , 0 ,C, 18059 , 376244 
02/26/2014,58202,01,CBT ,001602,03/02/2014, 0 , 0 ,C, 18059 , 376244 
02/26/2014,34376,01,CBT ,001602,03/02/2014, 0 , 0 ,N, 18059 , 376244

Select allOpen in new window

ASPX

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>

 <telerik:RadAsyncUpload ID="ReportFileUploader" runat="server" Width="270px" ToolTip="Select Report File to Upload" 
            OnFileUploaded="ReportFileUploader_FileUploaded" MaxFileInputsCount="1" MultipleFileSelection="Automatic" 
            AllowedFileExtensions="txt" Skin="Forest" ></telerik:RadAsyncUpload>


          <br />
            <telerik:RadButton ID="UploadFileButton" runat="server" Text="Upload File" Skin="Hay" OnClick="UploadFileButton_Click"></telerik:RadButton>

Select allOpen in new window

ASPX.cs

protected void UploadFileButton_Click(object sender, EventArgs e)
        {
            //ValidFiles.Visible = true;

            UploadedFile _docUploader = ReportFileUploader.UploadedFiles[0];          
                       
            Stream filename = _docUploader.InputStream;

            ParseData(filename);
          
        }


 protected void ParseData(Stream filename)
        {
            string[] value;
            string reportDate = "";
            string ownerId = "";
            string nameSuffix = "";
            string parentInitial = "";
            string contractCode = "";
            string expirationDate = "";
            string storyLong = "";
            string storyShort = "";
            string comFlag = "";
            string interestCount = "";
            string afcOI = "";

            StreamReader streamReader = new StreamReader(filename);
            string row = "";

           while ((row = streamReader.ReadLine()) != null)           
            {
                value = row.Split(new char[] { ',' });
                reportDate = value[0];
                ownerId = value[1];
                nameSuffix = value[2];
                parentInitial = value[3];
                contractCode = value[4];
                expirationDate = value[5];
                storyLong = value[6];
                storyShort = value[7];
                comFlag = value[8];
                interestCount = value[9];
                afcOI = value[10];

                TitleFuture titleFuture = new TitleFuture();
                DateTime _reportDate;
                DateTime.TryParse(reportDate, out _reportDate);
                titleFuture.ReportDate = _reportDate;
                titleFuture.OwnerId = ownerId;
                titleFuture.NameSuffix = nameSuffix;
                titleFuture.ParentInitial = parentInitial;
                titleFuture.ContractCode  = contractCode ;
                DateTime _expirationDate;
                DateTime.TryParse(expirationDate, out _expirationDate);
                titleFuture.ExpirationDate = _expirationDate;
                titleFuture.StoryLong = storyLong;
                titleFuture.StoryShort = storyShort ;
                titleFuture.ComFlag = comFlag;
                titleFuture.InterestCount = interestCount;
                titleFuture.AFCOI = afcOI;


                DbReportDateRetriever _dbReportDateRetriever = new DbReportDateRetriever();
                _dbReportDateRetriever.GetReportDate(_reportDate);
                DateTime dbReportDateNow = _dbReportDateRetriever.dbReportDate;

                if (dbReportDateNow == _reportDate)
                {
                   DataUpdater _dataUpdater = new DataUpdater();
                    _dataUpdater.UpdateData(_reportDate);
                }

                titleFuture.InsertData(titleFuture);
                                                                         
            }
                      
        }

Select allOpen in new window

DataUpdater.cs

public class DataUpdater
    {

        public DateTime _dbReportDateNow { get; set; }

        public void UpdateData(DateTime _dbReportDateNow)
        {
            using (SqlConnection conn = DbConnection.DbConnectionManager())
            {
                SqlCommand cmd = new SqlCommand("dbo.spDataUpdater", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@dbReportDateNow", _dbReportDateNow);
                cmd.ExecuteNonQuery();
               
                conn.Close();
            }      
        }
    }

Select allOpen in new window

DbReportDataRetriever.cs

public class DbReportDateRetriever
    {
        public DateTime dbReportDate { get; set; }

        public void GetReportDate(DateTime dbReportDate)
        {


            using (SqlConnection conn = DbConnection.DbConnectionManager())
            {
                try
                {                                 
                
                SqlCommand cmd = new SqlCommand("dbo.spGetReportDate", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@dbReportDate", dbReportDate);

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    //this.dbReportDate = Convert.ToDateTime(reader["Report_Date"]);
                    this.dbReportDate = reader.GetDateTime(0);
                   
                }
                reader.Close();
                conn.Close();
            }            
            
            catch (Exception ex)
            {
            
            }

            }
            
        }
    }

Select allOpen in new window

TitleFuture.cs

    public class TitleFuture
    {
        public int RecordId { get; set; }
        public DateTime ReportDate { get; set; }
        public string OwnerId { get; set; }
        public string NameSuffix { get; set; }
        public string ParentInitial { get; set; }
        public string ContractCode { get; set; }
        public DateTime ExpirationDate { get; set; }
        public string StoryLong { get; set; }
        public string StoryShort { get; set; }
        public string ComFlag { get; set; }
        public string InterestCount { get; set; }
        public string AFCOI { get; set; }


        public void InsertData(TitleFuture _titleFuture)
        {
            

            DataTable titleFutureData = new DataTable("TitleFutureData");

            DataColumn recordId = new DataColumn();
            recordId.ColumnName = "Record_ID";


            DataColumn reportDate = new DataColumn();
            reportDate.ColumnName = "Report_Date";

            DataColumn ownerId = new DataColumn();
            ownerId.ColumnName = "Owner_Id";

            DataColumn nameSuffix = new DataColumn();
            nameSuffix.ColumnName = "Name_Suffix";

            DataColumn parentInitial = new DataColumn();
            parentInitial.ColumnName = "Parent_Initial";

            DataColumn contractCode = new DataColumn();
            contractCode.ColumnName = "Contract_Code";

            DataColumn expirationDate = new DataColumn();
            expirationDate.ColumnName = "Expiration_Date";

            DataColumn storyLong = new DataColumn();
            storyLong.ColumnName = "Story_Long";

            DataColumn storyShort = new DataColumn();
            storyShort.ColumnName = "Story_Short";

            DataColumn comFlag = new DataColumn();
            comFlag.ColumnName = "Com_Flag";

            DataColumn interestCount = new DataColumn();
            interestCount.ColumnName = "Interest_Count";

            DataColumn afcOI = new DataColumn();
            afcOI.ColumnName = "AFC_OI";

            titleOnlyData.Columns.Add(recordId);
            titleOnlyData.Columns.Add(reportDate);
            titleOnlyData.Columns.Add(ownerId);
            titleOnlyData.Columns.Add(nameSuffix);
            titleOnlyData.Columns.Add(parentInitial);
            titleOnlyData.Columns.Add(contractCode);
            titleOnlyData.Columns.Add(expirationDate);
            titleOnlyData.Columns.Add(storyLong);
            titleOnlyData.Columns.Add(storyShort);
            titleOnlyData.Columns.Add(comFlag);
            titleOnlyData.Columns.Add(interestCount);
            titleOnlyData.Columns.Add(afcOI);


            DataRow titleFutureDataRow = titleOnlyData.NewRow();
            titleFutureDataRow["Record_ID"] = "";
            titleFutureDataRow["Report_Date"] = _titleFuture.ReportDate;
            titleFutureDataRow["Owner_Id"] = _titleFuture.OwnerId;
            titleFutureDataRow["Name_Suffix"] = _titleFuture.TraderSuffix;
            titleFutureDataRow["Parent_Initial"] = _titleFuture.parentInitial;
            titleFutureDataRow["Contract_Code"] = _titleFuture.ContractCode;
            titleFutureDataRow["Expiration_Date"] = _titleFuture.ExpirationDate;
            titleFutureDataRow["Story_Long"] = _titleFuture.StoryLong;
            titleFutureDataRow["Story_Short"] = _titleFuture.StoryShort;
            titleFutureDataRow["Com_Flag"] = _titleFuture.ComFlag;
            titleFutureDataRow["Interest_Count"] = _titleFuture.InterestCount;
            titleFutureDataRow["AFC_OI"] = _titleFuture.AFCOI;

            titleOnlyData.Rows.Add(titleFutureDataRow);

           
            using (SqlConnection conn = DbConnection.DbConnectionManager())
            {
                //conn.Open();

                using (SqlBulkCopy sql = new SqlBulkCopy(conn))
                {
                    sql.DestinationTableName = titleOnlyData.TableName;

                    foreach (var column in titleOnlyData.Columns)
                        sql.ColumnMappings.Add(column.ToString(), column.ToString());

                    sql.WriteToServer(titleOnlyData);

                    conn.Close();
                    
                }

                

            }

        }
        

    }

Select allOpen in new window