PDF (US Ltr)
- 6.2Mb
PDF (A4)
- 6.2Mb
HTML Download (TGZ)
- 2.8Mb
HTML Download (Zip)
- 2.9Mb
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 MySqlConnection
Dim cmd As New MySqlCommand
Dim SQL As String
Dim FileSize As UInt32
Dim rawData() As Byte
Dim fs As FileStream
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 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.