I need to save/load files to/from MySQL database. Estimated file size of about 500 Mb. I have not much experience using MySQL and never worked with such a big files in database context. My first move was to read "MySQL Connector/Net Developer Guide", but examples there was not very suitable for practical use. I was looking for questions and answers here, but all the question about blobs I found was about saving pictures in db. Pictures are not very big files, much lesser than 500Mb. Here the examples, from "MySQL Connector/Net Developer Guide", I'm talking about:
Writing BLOB to datavase example
Reading BLOB from database example
This is the code, I wrote, based on these examples (to write blob in database):
public static class TABLES
{
public struct Column
{
public string Name { get; }
public string Table { get; }
public string Alias
{
get { return Table + Name; }
}
public string AS
{
get { return string.Format ( "{0} AS {1}", Name, Alias ); }
}
public string TableDotName
{
get { return string.Format ( "{0}.{1}", Table, Name ); }
}
public string TableDotAS
{
get { return string.Format ( "{0}.{1}", Table, AS ); }
}
public override string ToString ( )
{
return Name;
}
public Column ( string parTitle, string parTable )
{
Name = parTitle;
Table = parTable;
}
}
public class FILES
{
public const string TABLE = "Files";
public static readonly Column ID = new Column( "id", TABLE );
public static readonly Column TITLE = new Column( "Title", TABLE );
public static readonly Column FILE = new Column( "File", TABLE );
public static readonly Column [] COLUMNS = { ID, TITLE, FILE };
}
}
public bool AddFileParameter(MySqlCommand parCommand, string parFilePath, string parParameterName, bool parIsRequired )
{
bool retResult = false;
if(parCommand == null )
{
throw new NullReferenceException ( "MySqlCommand is null" );
}
if (System.IO.File.Exists( parFilePath ) )
{
using ( System.IO.FileStream fs = new System.IO.FileStream ( parFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read ) )
{
uint FileSize = (uint)fs.Length;
byte [ ] rawData = new byte[FileSize];
fs.Read ( rawData, 0, ( int ) FileSize );
fs.Close ( );
parCommand.Parameters.AddWithValue ( parParameterName, rawData );
retResult = true;
}
}
else if (parIsRequired)
{
throw new System.IO.FileNotFoundException ( "File not found", parFilePath );
}
return retResult;
}
public int InsertFile ( string parTitle, string parFilePath )
{
int retResult = 0;
string queryParTitle = "@Title";
string queryParFile = "@File";
string commandText = string.Format(@"INSERT IGNORE INTO {0} ({1}, {2}) VALUES({3}, {4});",
new object[] {
TABLES.FILES.TABLE, //0
TABLES.FILES.TITLE.Name, //1
TABLES.FILES.FILE.Name, //2
queryParTitle, //3
queryParFile, //4
} );
try
{
using ( var myConnection = DBOpenConnection ( ) )
{
try
{
using ( MySqlCommand myCommand = new MySqlCommand ( commandText, myConnection ) )
{
myCommand.Prepare ( );
myCommand.Parameters.AddWithValue ( queryParTitle, parTitle );
AddFileParameter ( myCommand, parFilePath, queryParFile, false );
retResult = DBExecuteNonQuery ( myCommand );
}
}
finally
{
DBCloseConnection ( myConnection );
}
}
return retResult;
}
catch ( DBConnectException e )
{
throw e;
}
catch ( DBDisconnectException e )
{
throw e;
}
catch ( DBExecuteQueryException e )
{
throw e;
}
catch ( DBReadValueException e )
{
throw e;
}
catch ( Exception e )
{
throw ( new DBException ( "Unhadled database error", e ) );
}
}
It works, but the problem is that the file is loaded entirely into RAM. And I can't figured out how to upload file piece by piece.
The second problem is getting file from database. In the example above, we must explicitly specify the size of the BLOB. It's a shame. How should I know its size? And why database don't know it? MySQL Connector has method "IsDBNull", but I can't find nothing like "GetSize". And again the BLOB is loaded entirely into RAM.
So, my question: How to work with big BLOBs properly? How to download and upload it piece by piece? And how to get files from BLOB without knowing their size.
