After 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:
C# Code Example
Press CTRL+C to copyMySql.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); }
Visual Basic Code Example
Press CTRL+C to copyDim conn As New MySqlConnection Dim cmd As New MySqlCommand Dim myData As MySqlDataReader Dim SQL As String Dim rawData() As Byte Dim FileSize As UInt32 Dim fs As FileStream 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 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
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.