c# - How to insert BLOB datatype

Question

I'm using the following code to insert into a blob field:

---

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

string SQL;
int FileSize;
byte[] rawData;
FileStream fs;

conn.ConnectionString = "server=192.168.1.104;uid=root;" +
        "pwd=root;database=cady234;";

fs = new FileStream(@"d:Untitled.gif", FileMode.Open, FileAccess.Read);
FileSize = (int)fs.Length;

rawData = new byte[FileSize];
fs.Read(rawData, 0, FileSize);
fs.Close();

conn.Open();

string strFileName = "test name";
SQL = "INSERT INTO file (file_name, file_size, file) VALUES ('" + strFileName + "', "+FileSize+", '"+rawData+"')";

cmd.Connection = conn;
cmd.CommandText = SQL;

cmd.ExecuteNonQuery();
conn.Close();

---

The insertion is ok but the image is not getting displayed while using "Open value in viewer":

Answer 1

The binary data isn't being properly passed to your insert as you are using string concatenation - you'll get rawData.ToString() which probably just prints out the TypeName (hence your binary data being 13 bytes in length compared to the filesize of > 3000 bytes); try this instead:

byte[] rawData = File.ReadAllBytes(@"d:Untitled.gif");
FileInfo info = new FileInfo(@"d:Untitled.gif");

int fileSize = Convert.ToInt32(info.Length);

using(MySqlConnection connection = new MySqlConnection("server=192.168.1.104;uid=root;pwd=root;database=cady234;"))
{
    using(MySqlCommand command = new MySqlCommand())
    {
        command.Connection = connection;
        command.CommandText = "INSERT INTO file (file_name, file_size, file) VALUES (?fileName, ?fileSize, ?rawData);";
        MySqlParameter fileNameParameter = new MySqlParameter("?fileName", MySqlDbType.VarChar, 256);
        MySqlParameter fileSizeParameter = new MySqlParameter("?fileSize", MySqlDbType.Int32, 11);
        MySqlParameter fileContentParameter = new MySqlParameter("?rawData", MySqlDbType.Blob, rawData.Length);

        fileNameParameter.Value = "test name";
        fileSizeParameter.Value = fileSize;
        fileContentParameter.Value = rawData;

        command.Parameters.Add(fileNameParameter);
        command.Parameters.Add(fileSizeParameter);
        command.Parameters.Add(fileContentParameter);

        connection.Open();

        command.ExecuteNonQuery();

    }
}

I've introduced several concepts for you here; firstly, if you are going to load all of the binary data at once, simply use the static method File.ReadAllBytes - it's a lot less code.

Secondly, there is no need to use the fully qualified namespace each time - use the using directive

Thirdly, (slightly confusingly) there is a also a using statement in C#. This ensures that any object that implements IDisposable is properly cleaned up after itself. In the case of the connection, it will explicitly call Close and Dispose if you command succeeds or fails.

Finally, I've parameterized your query. Parameters are useful for many reasons; they help protect against SQL Injection, and, in this instance, they should also ensure that your data types are handled correctly. You can read more about SqlParameter (which, like, MySqlParameter is a database specific implementation but uses the same principles).

Tested as working with MySQL 5.5.15, MySQL Connector 5.2.7 running under .Net 4