Insert a client file into a column on a server database

I wish to wri开发者_C百科te a query that inserts a file that resides on the client (C# web server) into a column in the database server (SQL Server), something like INSERT … SELECT * FROM OPENROWSET(BULK…), but without having to save the file on the server machine first.

Is this even possible in SQL?

Although your context is unstated, I'm assuming that you're intending to run this from SSMS rather than from OSQL, a PowerShell script, or through some other means.

The file doesn't need to reside on the physical box running SQL Server, but SQL Server does need access to it. The typical approach, I believe, would be for an application server to copy the file to a shared repository and then pass it off to SQL Server through a UNC reference. The syntax to do so is relatively trivial and can be found in Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...).

If instead you're interested in providing a mechanism for the SQL Server to save a file from some type of stream operation where the client is directly transmitting a file and there is no shared repository, I'm not aware of a way to do that. Even if you use an SQL FILESTREAM object you still need an accessible NTFS location to stream from. See Saving and Retrieving File Using FileStream SQL Server 2008.

At some point, the server will have to have a hand on the file. That does not mean that the server has to keep the file, but the file has to get to the server in order to be read and inserted into the db. Typically, this is achieved with a form and a file-type input. On the server, you can use the uploaded file to create your query, then delete it.

That said, storing files in a database is a debatable practice. Depending on the type and size of files you're storing, your database can quickly balloon in size. For starters, this makes backups slower and more prone to failure, along with a laundry list of other potential pitfalls. Check out this question on SO: Storing Images in DB - Yea or Nay? As you can see from the answers, there are a number of considerations to be made, but a good rule of thumb is to not do this unless you have compelling reasons to do so.

In SQL Server BLOB Data in .NET: Tutorial, Mohammad Elsheimy explains how it can be done:

using(SqlConnection con = new SqlConnection(conStr) )
using(SqlCommand command = new SqlCommand("INSERT INTO MyFiles VALUES (@Filename, @Data)", con) )
{
    command.Parameters.AddWithValue("@Filename", Path.GetFileName(filename));
    command.Parameters.AddWithValue("@Data", File.ReadAllBytes(filename));
    connection.Open();
}

Basically, this way the file is read on the client and sent to the database server without a need for a temporary file on the server machine.