How to store the file in the database ?

Question

How to store the file which is uploaded by the user to the database ? I want to store the file in the database how can we do that ? In the back-end I am using sql with c#.net application.

Answer 1

One solution that will keep your db size down is to store the location of the file on the server. IE a file path.

However you will have to make a manager if you ever want to move files about.

Answer 2

You can use a blob field type. When you read the file in from a StreamReader convert it to a byte array and then insert that into the blob field.

The reverse procedure when you want to read it, get the blob as a byte array, read it into a streamreader and write that to the response buffer.

Answer 3

Assuming SQL 2005 or later, I would use a VarBinary(MAX) field. Pass the uploaded file as a byte[] to the insert statement. According to Microsoft, since SQL 2005, storing large chunks of data (files and images) in the DB no longer decreases performance to a great extent.

eg:

public void SaveFileToDB(string description, byte[] file)
{
    using (SqlConnection con = new SqlConnection(conStr)
    {
        con.Open();
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.Parameters.Add("@Description", SqlDbType.VarChar, description);
            cmd.Parameters.Add("@File", SqlDbType.VarBinary, file);
            cmd.CommandText = "UploadedFileUpdate";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
        }
    }
}

If you have SQL 2008, see Ron Klein's suggestion.

Answer 4

Upload/Import Download/Export a File to/from Oracle DB

Although it's implemented for an Oracle database I think you can get the idea reading the post.

Answer 5

MS SQL Server 2008 (and above, I guess) offers FileStream data type. Just Google for it (or Bing it, whatever :-)), I think you'll find what you need.

Answer 6

This solution works for SQL SERVER 2005/2008.

You have to create table with VARBINARY(MAX) as one of the columns. In my case I've created Table Raporty with column RaportPlik being VARBINARY(MAX) column.

Below there are couple of support functions you can modify for your needs:

    public static void databaseFilePut(string varFilePath) {
        byte[] file;
        using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read)) {
            using (var reader = new BinaryReader(stream)) {
                file = reader.ReadBytes((int) stream.Length);

            }

        }
        using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
        using (var sqlWrite = new SqlCommand("INSERT INTO Raporty (RaportPlik) Values(@File)", varConnection)) {
            sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;
            sqlWrite.ExecuteNonQuery();
        }
    }
    public static void databaseFileRead(string varID, string varPathToNewLocation) {
        using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
        using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
            sqlQuery.Parameters.AddWithValue("@varID", varID);
            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                if (sqlQueryResult != null) {
                    sqlQueryResult.Read();
                    var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                    sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                    using (var fs = new FileStream(varPathToNewLocation, FileMode.Create, FileAccess.Write)) fs.Write(blob, 0, blob.Length);
                }
        }
    }
    public static MemoryStream databaseFileRead(string varID) {
        MemoryStream memoryStream = new MemoryStream();
        using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
        using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
            sqlQuery.Parameters.AddWithValue("@varID", varID);
            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                if (sqlQueryResult != null) {
                    sqlQueryResult.Read();
                    var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                    sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                    //using (var fs = new MemoryStream(memoryStream, FileMode.Create, FileAccess.Write)) {
                    memoryStream.Write(blob, 0, blob.Length);
                    //}
                }
        }
        return memoryStream;
    }

First method is to put file into database from drive, second method is to get file and save it on drive, and 3rd method is to get file from database and put it as MemoryStream so you can some other stuff with it then just writing it to drive.

This 4th method is to put MemoryStream into database:

public static int databaseFilePut(MemoryStream fileToPut) {
        int varID = 0;
        byte[] file = fileToPut.ToArray();
        const string preparedCommand = @"
                    INSERT INTO [dbo].[Raporty]
                               ([RaportPlik])
                         VALUES
                               (@File)
                        SELECT [RaportID] FROM [dbo].[Raporty]
            WHERE [RaportID] = SCOPE_IDENTITY()
                    ";
        using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
        using (var sqlWrite = new SqlCommand(preparedCommand, varConnection)) {
            sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;

            using (var sqlWriteQuery = sqlWrite.ExecuteReader())
                while (sqlWriteQuery != null && sqlWriteQuery.Read()) {
                    varID = sqlWriteQuery["RaportID"] is int ? (int) sqlWriteQuery["RaportID"] : 0;
                }
        }
        return varID;
    }