Hi All am Trying to Save Multiple files to the SQL Server database

Question

Hi All am Trying to Save Multiple files to the SQL Server database

AppDev 21

So here is what I am trying to and what I have working already
To be clear here is what I want to do.
I would like to save files to a sql database that have have been selected using using the window dialog box.
I have it working where I can save to file system but When I try added source codes for saving to database,
its a no do and I am not sure.

-THanks

The longer version below

So I have found a few examples of people saving flies to a database and to a file system.
I am allowing the user to select multiple files using the mvc file dialog thingy... I am not sure what it is called
but it open up a windows file selector. THe examples have it saving to a folder on the server and I have that working just fine,
It saves a file path and all of that stuff.

I want to also save the files to the database.

But for some reason the httpPostedFileBase thing will not allow me to use it with IEnumerable<httpPostedFileBase> when saving to the database.
So let me be clear IEnumerable<httpPostedFileBase> works fine when saving to the folder as I am able to do a do while loop to get each file and file name and save it to the assigned path.
the IEnumerable<httpPostedFileBase> will not work for the data stream for the database save.

Let me be clear I don't understand IEnumerable that concept is very hard to grasp because the the explanation from microsoft is not written for a lay person to under stand.. it very technical sadly and I am not a technical person. But I guess it allows you to count via some sort of interface so you can loop through.

But any way my problem is with the posted file.inputstream I guess when its IEnumerable<HttpPostedFileBase> postedFile there is no inputstream???

Maybe some one can help me out see these two screen shots please:

When I declare the postedFile as IEnumerable<HttpPostedFileBase> postedFile I get the errors

When I remove the IEnumerable part I do have errors

Here Is the source codes:
In the controller: calling the class

 [HttpPost]  
        [HandleError]  
        public ActionResult MultiFileUpload(IEnumerable<HttpPostedFileBase> FileData)  
        {  
  
            var EmailResponder = new ApplicationClasses.EmailResponderClass();  
            string FileListString = null;  
            string EmailFileListString = null;  
  
            int? FileCounter = 0; //(FileData.Count()+1); //Get the Number of Files  
  
  
            VM_LHR_ApplicationClass vm_LHR_ApplicationClass = new VM_LHR_ApplicationClass();  
            vm_LHR_ApplicationClass.CONTACT_FIRST_NAME = GV_FNAME.ToUpper();  
            vm_LHR_ApplicationClass.CONTACT_LAST_NAME = GV_LName.ToUpper();  
            vm_LHR_ApplicationClass.CONTACT_EMAIL = GV_Email.ToUpper();  
  
  
            try  
            {  
  
  
  
  
                //MakeUploadDate No Spaces:  
                string UploadDate = DateTime.Today.ToString("MM/dd/yy");  
                UploadDate = UploadDate.Replace("/", string.Empty);  
  
                IList<string> FileList = new List<string>();  
  
                foreach (var file in FileData)  
                {  
                    FileCounter = FileCounter + 1; //Increment   
  
                    //MakeUniqueTicker See: https://stackoverflow.com/questions/9278909/net-short-unique-identifier  
                    var ticks = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day).Ticks.ToString();  
                      
                    var ans = DateTime.Now.Millisecond; //Add Unique Filename Prefix  
                    //var UniqueId = ans.ToString("x").Remove(4,4);  
                    //var FilesToSave = Path.GetExtension(file.FileName).ToList();  
  
                    string FileExt = Path.GetExtension(file.FileName).ToString();   
  
                    //May Need   
                    string FileNamePrefix = UploadDate + "__" + LHRController.GV_LName + LHRController.GV_FNAME + "__" + Path.GetFileName(file.FileName).ToString(); //Guid.NewGuid().ToString().Remove(5, 10);// ticks.Remove(5, 11);  
                     
  
                    //string filepath = Guid.NewGuid() + System.IO.Path.GetExtension(file.FileName);  
                    string filepath = ans +"_"+ FileNamePrefix; //+ System.IO.Path.GetExtension(file.FileName);  
                    file.SaveAs(Path.Combine(Server.MapPath("~/Documents/LEAD/DOCS_IN"), filepath.ToUpper()));  
  
                    //TODO: Add Doc List Code here in for loop  
  
                    FileList.Add(Path.GetFileName(file.FileName).ToUpper());  
     
                    TempData["FileUploadList"] = FileList; //List Of Files To Upload  
  
                   
                    FileListString = FileListString + " Uploaded File [#" +FileCounter+"] " + Path.GetFileName(file.FileName).ToString().ToUpper();  
                    EmailFileListString = EmailFileListString + " Uploaded File [#" + FileCounter + "] " + Path.GetFileName(file.FileName).ToString().ToUpper() +"<br/>";  
  
                    //TODO: There is where code to save to DB would Do  
                    DIS_TOOLS_CLASS DISTOOLS = new DIS_TOOLS_CLASS();  
                    DISTOOLS.DB_ENTRY_SAVE(FileData, filepath, FileExt, "LHR", "LHR TEST");  
                }

In the class Source Codes

using DIS.Models;  
using System;  
using System.Collections.Generic;  
using System.IO;  
using System.Linq;  
using System.Web;  
  
namespace DIS.ApplicationClasses  
{  
    public class DIS_TOOLS_CLASS  
    {  
  
  
        public void DB_ENTRY_SAVE(HttpPostedFileBase postedFile, string SaveFileName, string SaveFileExt, string SourceID, string SourceDISC)  
        {  
            byte[] bytes;  
            using (BinaryReader br = new BinaryReader(postedFile.InputStream))  
            {  
                bytes = br.ReadBytes(postedFile.ContentLength);  
            }  
            DSDBEntities DB_Entities = new DSDBEntities();  
            DB_Entities.DSDB_STORE_TBL.Add(new DSDB_STORE_TBL  
            {  
                DOC_FILE_NAME = Path.GetFileName(postedFile.FileName),  
                DOC_CONTENT_TYPE = postedFile.ContentType,  
                DOC_DATA = bytes  
            });  
  
  
        }  
  
  
    }  
}

3 answers

Your answer

Answer 1

QiYou-MSFT 4,326 Microsoft External Staff

Hi @AppDev ,
First of all, from your needs, I don't think it's a good choice to use a database to store files. The preferred database storage space may not be suitable for storing a large number of files. Of course, if you think this is necessary, you can use the C# method to convert the file to a Byte array and store it in a database (of course, if the file is too large, it will take up a lot of space).

public byte[] UploadFile(string fileUrl)  
{  
　　FileStream FILES = new FileStream(fileUrl, FileMode.Open, FileAccess.Read);  
　　try  
　　{  
　　　　byte[] buffur = new byte[FILES.Length];  
　　　　FILES.Read(buffur, 0, (int)FILES.Length);  
  
　　　　return buffur;  
　　}  
　　catch (Exception ex)  
　　{  
　　　　//MessageBoxHelper.ShowPrompt(ex.Message);  
　　　　return null;  
　　}  
　　finally  
　　{  
　　　　if (FILES != null)  
　　　　{  
　　　　　　FILES.Close();  
　　　　}  
　　}  
}

At the same time, I think it is better to use a database to store the file storage address.
As for the problem with IEnumerable, it's an enumerator. You can use List <T>to try it out.

Best Regards
Qi You

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

AppDev 21 Reputation points

2022-12-20T16:21:35.987+00:00

Let me ask this another way... is there a way from me to extract the current file out in a foreach loop to be sent to the class?

I am going to need to have to store this in the database as I have it saving to a folder but for security reasons saving to a database is what has to happen.

So here is the important source codes... how can I extract the the single file out to be sent to the class?
QiYou-MSFT 4,326 Reputation points Microsoft External Staff

2022-12-22T05:41:10.04+00:00

AgaveJoe's answer I think is great. If you want to store the file in the database, you still need to convert the file to a byte array. As for your idea of uploading files to the class via a foreach array, you use Razor Page encoding. I think it's better to convert the file into a byte array directly through the basic method and then through Sqlcommand.
sblb 1,231 Reputation points

2022-12-22T09:57:55.097+00:00

Another solution will be save the file by using File Stream in SQL Server. This is A good solution proposed by Microsoft.
QiYou-MSFT 4,326 Reputation points Microsoft External Staff

2022-12-22T10:22:09.837+00:00

Hi @AppDev ,
I almost forgot about this thing, and it also helps you with your question. Thanks for sblb-6958 for the suggestion.
This is the relevant documentation：
https://learn.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server?view=sql-server-ver16
App Dev 1 Reputation point

2023-01-09T16:56:17.707+00:00

Thank you I will have a look at this information you provided.

Answer 2

AgaveJoe 30,126

how can I extract the the single file out to be sent to the class?

Write the HttpPostedFileBase.InputStream to a MemoryStream then convert the memory stream to a byte array. Save the bytes and file name in a table.

[HttpPost]  
public ActionResult UploadFile(IEnumerable<HttpPostedFileBase> files)  
{  
    foreach(HttpPostedFileBase file in files)  
    {  
        if (file.ContentLength > 0)  
        {  
            string fileName = Path.GetFileName(file.FileName);  
            byte[] bytes;  
            using (var memoryStream = new MemoryStream())  
            {  
                file.InputStream.CopyTo(memoryStream);  
                bytes = memoryStream.ToArray();  
            }  
            //Save the bytes and fileName in the database.  
        }  
    }  
  
    return RedirectToAction("Index");  
}

AppDev 21 Reputation points

2022-12-20T21:53:47.507+00:00

For some reason it will not let me post the source codes some silly 1600 character limit, So I will have to post a screen shot
The hightlighted area is where it not storying the bytes, This is what I am getting Stored.

My source codes with your Source Codes

The Data is coming in:

Something is Going wrong here:
AppDev 21 Reputation points

2022-12-20T22:12:50.76+00:00

Also I found this Error I trying moving the using end bracket thinking maybe something was getting cleared out.

AgaveJoe 30,126

Simply move the bytes declaration outside the using block to change the variable scope.

             byte[] bytes;  
             using (var memoryStream = new MemoryStream())  
             {  
                 file.InputStream.CopyTo(memoryStream);  
                 bytes = memoryStream.ToArray();  
             }

Or don't implement a using block if it is too confusing.

[HttpPost]  
public ActionResult UploadFile(IEnumerable<HttpPostedFileBase> files)  
{  
    foreach(HttpPostedFileBase file in files)  
    {  
        if (file.ContentLength > 0)  
        {  
            string fileName = Path.GetFileName(file.FileName);  
                          
            var memoryStream = new MemoryStream();  
            file.InputStream.CopyTo(memoryStream);  
            byte[] bytes = memoryStream.ToArray();  
  
            //Save the bytes and fileName in the database.  
  
            memoryStream.Dispose();  
        }  
    }  
    return RedirectToAction("Index");  
}

AppDev 21 Reputation points

2022-12-20T23:24:02.827+00:00

Thank you all for the help but I am still Getting (

(System.Web.HttpPostedFileWrapper)file).InputStream.ReadTimeout = '((System.Web.HttpPostedFileWrapper)file).InputStream.ReadTimeout' threw an exception of type 'System.InvalidOperationException'

I did find this: https://learn.microsoft.com/en-us/dotnet/api/system.web.httppostedfile.inputstream?view=netframework-4.8
Do I need to read this array some how ???

I have removed the usings and every thing as instructed ... still something goes haywire ... is this hopeless ? why would any thing time out.
In my sample application it work like this

But when trying to save more then one file as sperate row item it just goes wrong. This is why I hate programming so much
AppDev 21 Reputation points

2022-12-20T23:24:54.307+00:00

And again to everyone thank you so much for all the help. I am mind blown that something seeming so routine would be such a horror show.

Whats so odd the file type is being saved to the database just not the data... how in the world can part of the file save by not the binary information...
its wacky!!

From the database which is sql server The Field Doc Data is where the Binary file should be stored Hope that helps

Answer 3

Karen Payne MVP 35,586 Volunteer Moderator

I have an article that should help on Microsoft Technet C# Insert binary files into SQL-Server table which is not going to match your code style but should be helpful as you can try the code out here at the following repository.

Share via

Hi All am Trying to Save Multiple files to the SQL Server database

3 answers

Your answer