-3
  1. Is it possible to send a text file of csv values to a parameter in a stored procedure?
    1. how do send the the text file from winforms to stored procedure?
    2. How do i create a parameter in stored procedure to accept that text file?

thanks

Phill Greggan
  • 2,234
  • 3
  • 20
  • 33
  • 1. Yes. 2. Depends on the stored procedure. 3. Depends on how you want to pass a text file. – Victor Zakharov Dec 07 '14 at 01:49
  • @GrantWinney the file contains 3 columns and 50 rows in csv format. what i want to do is pass the file directly into the stored procedure just like one would pass images so i dont have to create a seperate string hoping to extract text file's data from the stored procedure – Phill Greggan Dec 07 '14 at 01:52
  • @Neolisk Assume i want to send the file as a file not as a string, how would i do it? – Phill Greggan Dec 07 '14 at 01:53
  • You cannot send a file as a file. A byte array - maybe. Check this out - [Uploading and downloading files to/from a SQL Server database](http://www.codeproject.com/Articles/225446/Uploading-and-downloading-files-to-from-a-SQL-Serv). – Victor Zakharov Dec 07 '14 at 02:22
  • What do you mean with "send as a file"? – Alejandro Dec 07 '14 at 03:00

1 Answers1

3

You can pass the path to the file into the stored procedure as a simple "nvarchar(260)" parameter, though the Sql Server process will need to have read access to that file path (i.e., you either need to copy it to a share on the sql server itself from the WinForms app, or it needs to be on a network share that the Sql Server account has access to).

Once you have the path in the stored procedure, you can use a Bulk Insert process; see this thread on how to use Bulk Load from within a stored procedure: Bulk insert using stored procedure or this thread to load it into a temp table to work with: How to BULK INSERT a file into a *temporary* table where the filename is a variable?.

That being said, a better approach would be to convert the CSV file to Xml in the WinForms app, then pass Xml as a string to the stored procedure. Sql Server 2005 and later have good support for Xml parameters, enabling you to query them. (Personally I would take this latter approach). The Xml type is just one of the available parameter types, so you end up passing it like you would a string parameter. Working with Xml in the Stored Procedure will be much easier (and better supported) than the CSV will be.

Community
  • 1
  • 1
lheria
  • 581
  • 2
  • 4
  • An important observation to make is that any path passed will be interpreted *server side*. That is, they'll be relative to the box where the DB is running, **not** the calling application. Those paths will completely break when making calling from a remote machine (even though they may seem to work in a development environment where everything is installed together) – Alejandro Dec 07 '14 at 03:02