22.2.5.10. Handling BLOB Data With Connector/Net

The first step is using MySQL with BLOB data is to configure the server. Let's start by creating a table to be accessed. In my file tables, I usually have four columns: an AUTO_INCREMENT column of appropriate size (UNSIGNED SMALLINT) to serve as a primary key to identify the file, a VARCHAR column that stores the file name, an UNSIGNED MEDIUMINT column that stores the size of the file, and a MEDIUMBLOB column that stores the file itself. For this example, I will use the following table definition:

CREATE TABLE file(file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,file_name VARCHAR(64) NOT NULL,file_size MEDIUMINT UNSIGNED NOT NULL,file MEDIUMBLOB NOT NULL);

After creating a table, you might need to modify the max_allowed_packet system variable. This variable determines how large of a packet (that is, a single row) can be sent to the MySQL server. By default, the server only accepts a maximum size of 1MB from the client application. If you intend to exceed 1MB in your file transfers, increase this number.

The max_allowed_packet option can be modified using the MySQL Workbench Server Administration screen. Adjust the Maximum permitted option in the Data / Memory size section of the Networking tab to an appropriate setting. After adjusting the value, click the Apply button and restart the server using the Startup / Shutdown screen of MySQL Workbench. You can also adjust this value directly in the my.cnf file (add a line that reads max_allowed_packet=xxM), or use the SET max_allowed_packet=xxM; syntax from within MySQL.

Try to be conservative when setting max_allowed_packet, as transfers of BLOB data can take some time to complete. Try to set a value that will be adequate for your intended use and increase the value if necessary.

To write a file to a database, we need to convert the file to a byte array, then use the byte array as a parameter to an INSERT query.

The following code opens a file using a FileStream object, reads it into a byte array, and inserts it into the file table:

Visual Basic Example

Dim conn As New MySqlConnectionDim cmd As New MySqlCommandDim SQL As StringDim FileSize As UInt32Dim rawData() As ByteDim fs As FileStreamconn.ConnectionString = "server=127.0.0.1;" _    & "uid=root;" _    & "pwd=12345;" _    & "database=test"Try    fs = New FileStream("c:\image.png", FileMode.Open, FileAccess.Read)    FileSize = fs.Length    rawData = New Byte(FileSize) {}    fs.Read(rawData, 0, FileSize)    fs.Close()    conn.Open()    SQL = "INSERT INTO file VALUES(NULL, @FileName, @FileSize, @File)"    cmd.Connection = conn    cmd.CommandText = SQL    cmd.Parameters.AddWithValue("@FileName", strFileName)    cmd.Parameters.AddWithValue("@FileSize", FileSize)    cmd.Parameters.AddWithValue("@File", rawData)    cmd.ExecuteNonQuery()    MessageBox.Show("File Inserted into database successfully!", _    "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)    conn.Close()Catch ex As Exception    MessageBox.Show("There was an error: " & ex.Message, "Error", _        MessageBoxButtons.OK, MessageBoxIcon.Error)End Try

C# Example

MySql.Data.MySqlClient.MySqlConnection conn;MySql.Data.MySqlClient.MySqlCommand cmd;conn = new MySql.Data.MySqlClient.MySqlConnection();cmd = new MySql.Data.MySqlClient.MySqlCommand();string SQL;UInt32 FileSize;byte[] rawData;FileStream fs;conn.ConnectionString = "server=127.0.0.1;uid=root;" +    "pwd=12345;database=test;";try{    fs = new FileStream(@"c:\image.png", FileMode.Open, FileAccess.Read);    FileSize = fs.Length;    rawData = new byte[FileSize];    fs.Read(rawData, 0, FileSize);    fs.Close();    conn.Open();    SQL = "INSERT INTO file VALUES(NULL, @FileName, @FileSize, @File)";    cmd.Connection = conn;    cmd.CommandText = SQL;    cmd.Parameters.AddWithValue("@FileName", strFileName);    cmd.Parameters.AddWithValue("@FileSize", FileSize);    cmd.Parameters.AddWithValue("@File", rawData);    cmd.ExecuteNonQuery();    MessageBox.Show("File Inserted into database successfully!",        "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);    conn.Close();}catch (MySql.Data.MySqlClient.MySqlException ex){    MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,        "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);}

The Read method of the FileStream object is used to load the file into a byte array which is sized according to the Length property of the FileStream object.

After assigning the byte array as a parameter of the MySqlCommand object, the ExecuteNonQuery method is called and the BLOB is inserted into the file table.

Once a file is loaded into the file table, we can use the MySqlDataReader class to retrieve it.

The following code retrieves a row from the file table, then loads the data into a FileStream object to be written to disk:

Visual Basic Example

Dim conn As New MySqlConnectionDim cmd As New MySqlCommandDim myData As MySqlDataReaderDim SQL As StringDim rawData() As ByteDim FileSize As UInt32Dim fs As FileStreamconn.ConnectionString = "server=127.0.0.1;" _    & "uid=root;" _    & "pwd=12345;" _    & "database=test"SQL = "SELECT file_name, file_size, file FROM file"Try    conn.Open()    cmd.Connection = conn    cmd.CommandText = SQL    myData = cmd.ExecuteReader    If Not myData.HasRows Then Throw New Exception("There are no BLOBs to save")    myData.Read()    FileSize = myData.GetUInt32(myData.GetOrdinal("file_size"))    rawData = New Byte(FileSize) {}    myData.GetBytes(myData.GetOrdinal("file"), 0, rawData, 0, FileSize)    fs = New FileStream("C:\newfile.png", FileMode.OpenOrCreate, FileAccess.Write)    fs.Write(rawData, 0, FileSize)    fs.Close()    MessageBox.Show("File successfully written to disk!", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)    myData.Close()    conn.Close()Catch ex As Exception    MessageBox.Show("There was an error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)End Try

C# Example

MySql.Data.MySqlClient.MySqlConnection conn;MySql.Data.MySqlClient.MySqlCommand cmd;MySql.Data.MySqlClient.MySqlDataReader myData;conn = new MySql.Data.MySqlClient.MySqlConnection();cmd = new MySql.Data.MySqlClient.MySqlCommand();string SQL;UInt32 FileSize;byte[] rawData;FileStream fs;conn.ConnectionString = "server=127.0.0.1;uid=root;" +    "pwd=12345;database=test;";SQL = "SELECT file_name, file_size, file FROM file";try{    conn.Open();    cmd.Connection = conn;    cmd.CommandText = SQL;    myData = cmd.ExecuteReader();    if (! myData.HasRows)        throw new Exception("There are no BLOBs to save");    myData.Read();    FileSize = myData.GetUInt32(myData.GetOrdinal("file_size"));    rawData = new byte[FileSize];    myData.GetBytes(myData.GetOrdinal("file"), 0, rawData, 0, (int)FileSize);    fs = new FileStream(@"C:\newfile.png", FileMode.OpenOrCreate, FileAccess.Write);    fs.Write(rawData, 0, (int)FileSize);    fs.Close();    MessageBox.Show("File successfully written to disk!",        "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);    myData.Close();    conn.Close();}catch (MySql.Data.MySqlClient.MySqlException ex){    MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,        "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);}

After connecting, the contents of the file table are loaded into a MySqlDataReader object. The GetBytes method of the MySqlDataReader is used to load the BLOB into a byte array, which is then written to disk using a FileStream object.

The GetOrdinal method of the MySqlDataReader can be used to determine the integer index of a named column. Use of the GetOrdinal method prevents errors if the column order of the SELECT query is changed.