How can I store a file in SQL database?

Question

I'm getting files from Angular with this code:

 var httpPostedFile = HttpContext.Current.Request.Files["file"];

This is my directory:

 string folderExists = (HttpContext.Current.Server.MapPath("~/abc"));

How can I save my files into that folder?

for (int i = 0; i < HttpContext.Current.Request.Files.Count; i++)
{
    var FileName = httpPostedFile.FileName;
    int fileSize = httpPostedFile.ContentLength;
    byte[] fileByteArray = new byte[fileSize];
    httpPostedFile.InputStream.Read(fileByteArray, 0, fileSize);

    string fileLocation = Path.Combine(HttpContext.Current.Server.MapPath("~/App_Data/uploads"), FileName);

    if (!Directory.Exists(HttpContext.Current.Server.MapPath("~/App_Data/uploads")))
        Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/App_Data/uploads"));

    httpPostedFile.SaveAs(fileLocation);
}

I'm saving the files into the folder, but I want to store those files in SQL database.

My file structure:

 public class FileTable
    {
        [Key]
        public int Id { get; set; }
        public byte[] FileData { get; set; }
        public string FileName { get; set; }
    }

How can I store my file in the database?

check this might help : https://stackoverflow.com/questions/25125127/asp-net-mvc-4-c-sharp-httppostedfilebase-how-do-i-store-file/25125284#25125284 — Ehsan Sajjad, Jan 03 '19 at 17:34

score 0 · Answer 1 · edited Jan 22 '19 at 20:32

You can store the file using a stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spStoreBinaryFiles]
    @FILE_PATH VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @FILE_LENGTH BIGINT
    DECLARE @FILE_DATA VARBINARY(MAX)
    DECLARE @FILE_NAME VARCHAR(100)
    DECLARE @DOCUMENT_NAME VARCHAR(100) 
    DECLARE @DOCUMENT_NATURE VARCHAR(5)  

    DECLARE @VAL1 VARCHAR(100)
    DECLARE @VAL2 VARCHAR(100)

    DECLARE curDOCUMENTS CURSOR FOR 
         SELECT *  
         FROM dbo.SPLIT ( ';', @FILE_PATH)

    OPEN curDOCUMENTS

    FETCH NEXT FROM curDOCUMENTS INTO @VAL1, @VAL2

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF OBJECT_ID('#ORStable') IS NULL 
        BEGIN
            CREATE TABLE #ORStable _
            (Length BIGINT, vDocument VARBINARY(MAX))

            DECLARE @SQL_QUERY NVARCHAR(1000)

            SET @SQL_QUERY = 'INSERT INTO #ORStable
                                SELECT len(bulkcolumn), *
                                FROM OPENROWSET(BULK '''+@VAL2+''', _
                SINGLE_BLOB) AS BinaryData'

            EXEC SP_executesql @SQL_QUERY
        END

        EXEC dbo.spGetDocumentNature @VAL2, @DOCUMENT_NATURE OUTPUT
        EXEC dbo.spGetDocumentName @VAL2, @DOCUMENT_NAME OUTPUT

        SELECT TOP 1 @FILE_LENGTH = Length, @FILE_DATA = vDocument 
        FROM #ORStable

       INSERT INTO dbo.tblBinaryFiles ([File], [Path], [Ext], [Size],[Binary])
       VALUES (@DOCUMENT_NAME, @VAL2, @DOCUMENT_NATURE, @FILE_LENGTH, @FILE_DATA)

       DROP TABLE dbo.#ORStable

       FETCH NEXT FROM curDOCUMENTS INTO @VAL1, @VAL2
   END

   CLOSE curDOCUMENTS
   DEALLOCATE curDOCUMENTS 
END

Now you can call this stored procedure like this:

       DECLARE @SQL_QUERY     NVARCHAR(1000)

        SET @SQL_QUERY= '
        INSERT INTO #ORStable
        SELECT len(bulkcolumn), *
        FROM OPENROWSET(BULK '''+@VAL2+''', _
                SINGLE_BLOB) AS BinaryData'
        exec SP_executesql @SQL_QUERY

Here variable VAL2 contains the single file path information.

Example

VAL2 = "\\192.168.1.1\myFiles\yourfile.pdf"

VAL2 = "C:\yourFilesFolder\yourfile.pdf"

Note: If you use any network path or live path, please confirm your folder have read and write permission

Why would the OP not use the technology already in use in his program and why would there be a file when the OP wants to have it in the database? — nvoigt, Jan 04 '19 at 08:45

score 0 · Answer 2 · answered Jan 04 '19 at 22:26

If you're using Entity Framework Core and you pushed a migration to the table, then you can just create a new FileTable class and attach the byte array to it if it's already in the database. You would just add it to your FileData property and then save it in the database. If you haven't created the database yet, then just make sure that the column for FileData is of type varbinary(max).

an example would be:

public void AttachFile(byte[] byteArray, int objectID)
        {
            FileTable currentFileTable = new FileTable();
            currentFileTable = _yourcontext.yourtable.Where(e => e.ID == objectID).FirstOrDefault();
            currentFileTable.FileData = byteArray;
            _yourcontext.yourtable.Update(currentFileTable);
            _yourcontext.SaveChanges();
}

This would work if you already had an object in the database and knew what the ID was. If it is a new object then you would just do:

public void AttachFile(byte[] byteArray)
            {
                FileTable currentFileTable = new FileTable();                
                currentFileTable.FileData = byteArray;
                //Populate your other properties
                _yourcontext.yourtable.Add(currentFileTable);
                _yourcontext.SaveChanges();
    }

How can I store a file in SQL database?

2 Answers2