How to File Upload csv to SQL database on intranet VB.NET site

Question

I am having problems uploading CSV files to my SQL Database. I am trying to achieve this through the File Upload technique on an intranet site. The intranet site is for me and another user to have the ability to file upload these CSVs (in case one of us is out).

I've used the following;

    Dim errorList As String = String.Empty
    Dim returnValue As Integer = 0
    Dim SQLCon As New SqlClient.SqlConnection
    Dim SQLCmd As New SqlClient.SqlCommand
    Dim ErrString As String

    Dim countRecs As Integer = 0
    Dim batchid As Integer = GetNextBatchNumber("PartsImport")

    Using tf As New TextFieldParser(fileandpath)
        tf.TextFieldType = FileIO.FieldType.Delimited
        tf.SetDelimiters(",")


        SQLCon.ConnectionString = ConfigurationManager.ConnectionStrings("DB00ConnectionString").ConnectionString
        SQLCon.Open()
        SQLCmd.CommandType = CommandType.Text
        SQLCmd.Connection = SQLCon

        Dim recAdded As String = Now.ToString
        Dim row As String()
        While Not tf.EndOfData

            Try
                row = tf.ReadFields()
                Dim x As Integer = 0
                If countRecs <> 0 Then
                    Try
                      SQLCmd.CommandText = "insert into [Base].[PartsImport] " _
                      + " (ID,PartName,PartID,Price,ShipAddress) " _
                      + " values ('" + row(0) + "','" + row(1) + "','" _
                      + row(2) + "','" + row(3) + "','" + row(4) + "')"
                        SQLCmd.ExecuteNonQuery()

                    Catch ex As Exception
                        ErrString = "Error while Creating Batch Record..." & ex.Message
                    End Try
                End If

            Catch ex As MalformedLineException
                errorList = errorList + "Line " + countRecs + ex.Message & "is not valid and has been skipped." + vbCrLf
            End Try
            countRecs = countRecs + 1
        End While

        SQLCon.Close()
        SQLCon.Dispose()
        SQLCmd.Dispose()

When I click the form button to upload, it gives me a success message but when I look in the actual table, it is still blank.

Any ideas? Appreciate it

Thanks Dave

Answer 1

private void UploaddataFromCsv()
        {
            SqlConnection con = new SqlConnection(@"Data Source=local\SQLEXPRESS;Initial Catalog=databaseName;Persist Security Info=True;User ID=sa");
            string filepath = "C:\\params.csv";
            StreamReader sr = new StreamReader(filepath);
            string line = sr.ReadLine();
            string[] value = line.Split(',');
            DataTable dt = new DataTable();
            DataRow row;
            foreach (string dc in value)
            {
                dt.Columns.Add(new DataColumn(dc));
            }

            while ( !sr.EndOfStream )
            {
                value = sr.ReadLine().Split(',');
                if(value.Length == dt.Columns.Count)
                {
                    row = dt.NewRow();
                    row.ItemArray = value;
                    dt.Rows.Add(row);
                }
            }
            SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
            bc.DestinationTableName = "[Base].[PartsImport]";
            bc.BatchSize = dt.Rows.Count;
            con.Open();
            bc.WriteToServer(dt);
            bc.Close();
            con.Close();
        }