cannot read a csv file and insert it into the database.

[miscluce]

Hi

can anyone check this to see if I did this right becausea im getting nothing inserted in my table. thanks

my csv file looks like this into four columns:

PAULK Pauly's Bar Paul Kimmel The Fat Man
PAULK Pauly's Bar Paul Kimmel The Fat Man
PAULK Pauly's Bar Paul Kimmel The Fat Man
ALFKI Alfreds Futterkiste Maria Anders Sales Representative
ANATR Trujillo Emparedados y helados Ana Trujillo Owner
ANTON Antonio Moreno Taquería Antonio Moreno Owner
AROUT Around the Horn Thomas Hardy Sales Representative
BERGS Berglunds snabbköp Christina Berglund Order Administrator
BLAUS Blauer See Delikatessen Hanna Moos Sales Representative
BLONP Blondesddsl père et fils Frédérique Citeaux Marketing Manager
BOLID Bólido Comidas preparadas Martín Sommer Owner
BONAP Bon app' Laurence Lebihan Owner
BOTTM Bottom-Dollar Markets Elizabeth Lincoln Accounting Manager
BSBEV B's Beverages Victoria Ashworth Sales Representative
CACTU Cactus Comidas para llevar Patricio Simpson Sales Agent
CENTC Centro comercial Moctezuma Francisco Chang Marketing Manager
CHOPS Chop-suey Chinese Yang Wang Owner
COMMI Comércio Mineiro Pedro Afonso Sales Associate
CONSH Consolidated Holdings Elizabeth Brown Sales Representative
DRACD Drachenblut Delikatessen Sven Ottlieb Order Administrator
DUMON Du monde entier Janine Labrune Owner
EASTC Eastern Connection Ann Devon Sales Agent




and my code is:



Dim filepath As String = Server.MapPath("testProc.csv")
Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
Dim regex As New Regex(pattern)
Dim sr As New StreamReader(File.OpenRead(filePath))
Dim line As String = sr.ReadLine()
Dim data As String() = regex.Split(line)
Dim conn As String = ConfigurationManager.ConnectionStrings("ConnMSSQL").ConnectionString
Dim connection As SqlConnection = New SqlConnection(conn)
connection.Open()

While line IsNot Nothing

' Splitting at comma and storing in a string array

Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)
command.CommandType = CommandType.StoredProcedure

command.Parameters.AddWithValue("@CustomerID", data(0))
command.Parameters.AddWithValue("@CompanyName", data(1))
command.Parameters.AddWithValue("@ContactName", data(2))
command.Parameters.AddWithValue("@ContactTitle", data(3))
line = sr.ReadLine()

End While


my procedure is:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[InsertCustomer]
(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30),

@ContactTitle nvarchar(30)

)

AS

INSERT INTO Customers
(

CustomerID,

CompanyName,

ContactName,

ContactTitle

)

VALUES

(

@CustomerID,

@CompanyName,

@ContactName,

@ContactTitle

)

 

[miscluce]

Oh I figured I needed to move this line inside the loop but it still does not work.

Dim data As String() = regex.Split(line)

 

[jmeckley]

1st run the stored proc from QA to make sure the proc is correct. if the proc works as expected. then move to the code.

I would break this into multiple objects.
1. load data from csv.
2. map line to a simple dto (for readability).
3. save dto to database.

then you can test each piece independently to find out what's broken.

since your stored proc is a simple insert. I would recommend using SqlBulkInsert object instead of multiple 1 line commands.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[miscluce]

I fixed this. I forgot the

command.ExecuteNonQuery()