Saving any file in a database, just converting it to an array of bytes?

Question

Is converting a file to an array of bytes the best way to save ANY file format on a disk or binary column of a var database?

So, if someone wants to save a .gif or .doc / .docx or .pdf file, can I just convert it to bytearray UFT8 and save it in db as a stream of bytes?

Answer 1

Since it does not mention which database you have in mind, I assume SQL Server. The solutions below work both in 2005 and in 2008.

You need to create a table with VARBINARY(MAX) as one of the columns. In my example, I created a Raporty table, the RaportPlik column was VARBINARY(MAX) .

The way to put file in the database from drive :

 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(); } } 

This method should get file from the database and save it to drive :

 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); } } } 

This method should get file from the database and put it as a MemoryStream :

 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; } 

This method is to put a MemoryStream into the 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; } 

Happy coding :-)

Answer 2

While you can store files this way, it has significant tradeoffs:

• Most databases are not optimized for gigantic amounts of binary data, and query performance often deteriorates dramatically as the table is bloated even with indexes. (SQL Server 2008 with a column type of FILESTREAM is an exception to the rule.)
• Database backup / replication is very slow.
• It is much easier to process a damaged disk with 2 million images - just replace the disk with RAID - than a damaged DB table.
• If you accidentally delete a dozen images in the file system, your guys can replace them quite easily from the backup, and since the table index is tiny by comparison, it can be quickly restored. If you accidentally delete a dozen images in a table of gigantic databases, you have a long and painful expectation of restoring a database from a backup, paralyzing your entire system in the meantime.

These are just some of the shortcomings I can think of from my head. For tiny projects, it might be worth storing files this way, but if you are developing enterprise-level software, I would highly recommend against it.

Answer 3

It really depends on the database server.

For example, SQL Server 2008 supports FILESTREAM for this situation.

In addition, if you use a MemoryStream , it has a ToArray() method, which is converted to byte[] - this can be used to populate the varbinary field.

Answer 4

Yes, usually the best way to save a file in a database is to store an array of bytes in a BLOB column. You will probably need several columns for additional storage of file metadata, such as name, extension, etc.

It is not always a good idea to store files in a database - for example, the size of the database will grow rapidly if you store files in it. But it all depends on your use case.

Answer 5

Confirmation that I was able to use the response sent by MadBoy and edited Otiel on both MS SQL Server 2012 and 2014 in addition to the versions previously specified using varbinary (MAX) columns.

If you are wondering why you cannot "Filestream" (noted in a separate answer) as the data type in SQL Server table designer or why you cannot set the column data type to "Filestream" using T-SQL, this is because FILESTREAM is the persistence attribute of the varbinary data type (MAX). This is not a data type in itself.

See these articles on setting up and enabling FILESTREAM in the database: https://msdn.microsoft.com/en-us/library/cc645923(v=sql.120).aspx

http://www.kodyaz.com/t-sql/default-filestream-filegroup-is-not-available-in-database.aspx

After configuration, you can enable an additional varbinary (max) column with filters:

ALTER TABLE TableName

ADD ColumnName varbinary (max) FILESTREAM NULL

GO

Answer 6

I will describe how to store files in SQL Server and Oracle. This largely depends on how you receive the file, primarily on how you get its contents, and on the database that you use for the content in which you will store it, and on how you will receive it keep. These are 2 separate database examples with 2 separate ways to get the file I used.

SQL Server

Short answer: I used the base64 byte string, which I converted to byte[] and saved in the varbinary(max) field.

Long answer:

Suppose you download through a website, so you use <input id="myFileControl" type="file"/> or React DropZone. To get the file, you do something like var myFile = document.getElementById("myFileControl")[0]; or myFile = this.state.files[0]; ,

From there I will get the base64 string, using the code here: Convert input = file to a byte array (use the UploadFile2 function).

Then I will get this line, file name ( myFile.name ) and type ( myFile.type ) in a JSON object:

 var myJSONObj = { file: base64string, name: myFile.name, type: myFile.type, } 

and xhr.send(JSON.stringify(myJSONObj); file to the MVC server side using XMLHttpRequest, specifying Content-Type application/json : xhr.send(JSON.stringify(myJSONObj); you need to create a ViewModel to associate it with:

 public class MyModel { public string file { get; set; } public string title { get; set; } public string type { get; set; } } 

and specify [FromBody]MyModel myModelObj as the passed parameter:

 [System.Web.Http.HttpPost] // required to spell it out like this if using ApiController, or it will default to System.Mvc.Http.HttpPost public virtual ActionResult Post([FromBody]MyModel myModelObj) 

You can then add this to this function and save it using the Entity Framework:

 MY_ATTACHMENT_TABLE_MODEL tblAtchm = new MY_ATTACHMENT_TABLE_MODEL(); tblAtchm.Name = myModelObj.name; tblAtchm.Type = myModelObj.type; tblAtchm.File = System.Convert.FromBase64String(myModelObj.file); EntityFrameworkContextName ef = new EntityFrameworkContextName(); ef.MY_ATTACHMENT_TABLE_MODEL.Add(tblAtchm); ef.SaveChanges(); 

tblAtchm.File = System.Convert.FromBase64String(myModelObj.file); being an operational line.

You will need a model to represent the database table:

 public class MY_ATTACHMENT_TABLE_MODEL { [Key] public byte[] File { get; set; } // notice this change public string Name { get; set; } public string Type { get; set; } } 

This will save the data in the varbinary(max) field as byte[] . Name and Type were nvarchar(250) and nvarchar(10) , respectively. You can include the size by adding it to your table as an int & MY_ATTACHMENT_TABLE_MODEL as public int Size { get; set;} public int Size { get; set;} public int Size { get; set;} public int Size { get; set;} and add to the line tblAtchm.Size = System.Convert.FromBase64String(myModelObj.file).Length; above.

oracle

Short answer: convert it to byte[] , assign it to OracleParameter , add it to OracleCommand and update the table BLOB field using the link to the parameter value ParameterName :BlobParameter

Long answer: when I did this for Oracle, I used OpenFileDialog and extracted and sent the byte / file information as follows:

 byte[] array; OracleParameter param = new OracleParameter(); Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog(); dlg.Filter = "Image Files (*.jpg, *.jpeg, *.jpe)|*.jpg;*.jpeg;*.jpe|Document Files (*.doc, *.docx, *.pdf)|*.doc;*.docx;*.pdf" if (dlg.ShowDialog().Value == true) { string fileName = dlg.FileName; using (FileStream fs = File.OpenRead(fileName) { array = new byte[fs.Length]; using (BinaryReader binReader = new BinaryReader(fs)) { array = binReader.ReadBytes((int)fs.Length); } // Create an OracleParameter to transmit the Blob param.OracleDbType = OracleDbType.Blob; param.ParameterName = "BlobParameter"; param.Value = array; // <-- file bytes are here } fileName = fileName.Split('\\')[fileName.Split('\\').Length-1]; // gets last segment of the whole path to just get the name string fileType = fileName.Split('.')[1]; if (fileType == "doc" || fileType == "docx" || fileType == "pdf") fileType = "application\\" + fileType; else fileType = "image\\" + fileType; // SQL string containing reference to BlobParameter named above string sql = String.Format("INSERT INTO YOUR_TABLE (FILE_NAME, FILE_TYPE, FILE_SIZE, FILE_CONTENTS, LAST_MODIFIED) VALUES ('{0}','{1}',{2},:BlobParamerter, SYSDATE)", fileName, fileType, array.Length); // Do Oracle Update RunCommand(sql, param); } 

And inside the Oracle update done with ADO:

 public void RunCommand(string sql, OracleParameter param) { OracleConnection oraConn = null; OracleCommand oraCmd = null; try { string connString = GetConnString(); oraConn = OracleConnection(connString); using (oraConn) { if (OraConnection.State == ConnectionState.Open) OraConnection.Close(); OraConnection.Open(); oraCmd = new OracleCommand(strSQL, oraConnection); // Add your OracleParameter if (param != null) OraCommand.Parameters.Add(param); // Execute the command OraCommand.ExecuteNonQuery(); } } catch (OracleException err) { // handle exception } finally { OraConnction.Close(); } } private string GetConnString() { string host = System.Configuration.ConfigurationManager.AppSettings["host"].ToString(); string port = System.Configuration.ConfigurationManager.AppSettings["port"].ToString(); string serviceName = System.Configuration.ConfigurationManager.AppSettings["svcName"].ToString(); string schemaName = System.Configuration.ConfigurationManager.AppSettings["schemaName"].ToString(); string pword = System.Configuration.ConfigurationManager.AppSettings["pword"].ToString(); // hopefully encrypted if (String.IsNullOrEmpty(host) || String.IsNullOrEmpty(port) || String.IsNullOrEmpty(serviceName) || String.IsNullOrEmpty(schemaName) || String.IsNullOrEmpty(pword)) { return "Missing Param"; } else { pword = decodePassword(pword); // decrypt here return String.Format( "Data Source=(DESCRIPTION =(ADDRESS = ( PROTOCOL = TCP)(HOST = {2})(PORT = {3}))(CONNECT_DATA =(SID = {4})));User Id={0};Password{1};", user, pword, host, port, serviceName ); } } 

And the data type for the FILE_CONTENTS column was BLOB , FILE_SIZE was NUMBER(10,0) , LAST_MODIFIED was DATE , and the rest were NVARCHAR2(250) .

Answer 7

What database are you using? usually you don’t save the files in the database, but I think sql 2008 supports it ...

The file is binary data, so UTF 8 doesn't matter here.

UTF 8 matters when you try to convert a string to an array of bytes ... not a file to an array of bytes.

Answer 8

What does a locale mean? i.e. using (var varConnection = Locale.sqlConnectOneTime (Locale.sqlDataConnectionDetails)) using (var varConnection = Locale.sqlConnectOneTime (Locale.sqlDataConnectionDetails))