如何插入BLOB数据类型 [英] How to insert BLOB datatype

问题描述

我正在使用以下代码将其插入到blob字段中:

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":

推荐答案

在使用字符串连接时，二进制数据未正确传递到插入中-您将得到rawData.ToString()，它可能仅打印出TypeName (因此您的二进制数据的长度为13个字节，而文件大小大于3000个字节)；请尝试以下方法:

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

第三，(有点令人困惑)在C#中还有一个使用语句.这样可以确保实现 IDisposable 的任何对象在其自身之后均被正确清理.对于连接，如果命令成功或失败，它将显式调用Close and Dispose.

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.

最后，我已将您的查询参数化.参数之所以有用，有很多原因.它们有助于防止 SQL注入，并且在这种情况下，它们还应确保您的数据类型为处理正确.您可以阅读有关 SqlParameter 的更多信息(例如，MySqlParameter是特定于数据库的实现，但使用相同的原理).

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).

经测试可在.Net 4下运行的MySQL Connector 5.2.7与MySQL 5.5.15兼容

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

这篇关于如何插入BLOB数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！