C# 如何插入 BLOB 数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13208349/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert BLOB datatype
提问by Jobi
I'm using the following code to insert into a blob field:
我正在使用以下代码插入 blob 字段：
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":
插入没问题，但在使用“在查看器中打开值”时没有显示图像：
回答by dash
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:
当您使用字符串连接时，二进制数据没有正确传递到您的插入 - 您将得到rawData.ToString()它可能只是打印出 TypeName（因此与> 3000 字节的文件大小相比，您的二进制数据长度为 13 个字节）；试试这个：
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.
我在这里为你介绍了几个概念；首先，如果您要一次加载所有二进制数据，只需使用静态方法File.ReadAllBytes- 它的代码要少得多。
Secondly, there is no need to use the fully qualified namespace each time - use the usingdirective
其次，不需要每次都使用完全限定的命名空间——使用using指令
Thirdly, (slightly confusingly) there is a also a using statementin C#. This ensures that any object that implements IDisposableis properly cleaned up after itself. In the case of the connection, it will explicitly call Close and Dispose if you command succeeds or fails.
第三，（有点令人困惑）在 C# 中还有一个using 语句。这确保了任何实现IDisposable 的对象在其自身之后被正确清理。在连接的情况下，如果您的命令成功或失败，它将显式调用 Close 和 Dispose。
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).
最后，我已经参数化了您的查询。参数有用的原因有很多；它们有助于防止SQL 注入，并且在这种情况下，它们还应确保您的数据类型得到正确处理。您可以阅读有关SqlParameter 的更多信息（例如，MySqlParameter 是特定于数据库的实现，但使用相同的原理）。
Tested as working with MySQL 5.5.15, MySQL Connector 5.2.7 running under .Net 4
经测试可与 MySQL 5.5.15、MySQL Connector 5.2.7 一起在 .Net 4 下运行
回答by Johan
How about this:
这个怎么样：
It works fine for me.
Exepte I found out that it is the wrong one for me.
这对我来说可以。
Exepte 我发现这对我来说是错误的。
<?php
// csv invoeren naar pos.
$CSV = "uitvoer.csv";
// The CSV file has only 2 colums; The "Reference" and the image name (in my case the barcode with "thumb_" in front of it.
$username = "Username";
$password = "Passwwoorrdd";
$database = "POS";
$counter = 0;
// Create connection
$conn = new mysqli("localhost", $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully <br>";
//$mysqli->select_db();
ini_set('max_execution_time', 1000); //300 seconds = 5 minutes
if (($handle = fopen($CSV, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1050, ",")) !== FALSE) {
// this loops through each line of your csv, putting the values into array elements
$counter++;
$IDEE = $data[0];
// It seems that after opening the image the $data is mixed up.
$imag = "photos/".$data[1]; // where "photos/' the folder is where this php file gets executed (mostly in /var/www/ of /var/www/html/)
$fh = fopen($imag, "r");
$data = addslashes(fread($fh, filesize($imag)));
fclose($fh);
echo " Ref: ".$IDEE." ----".$counter."----<br>";
// If there will be a time-out. You could erase the part what is already done minus 1.
$sql = "UPDATE PRODUCTS SET IMAGE='".$data."' WHERE CODE=$IDEE";
// The table PRODUCTS with IMAGE. There are more data in that table. But i need only to update the IMAGE. The rest is already inserted.
// 带有 IMAGE 的表 PRODUCTS。该表中有更多数据。但我只需要更新图像。其余的已经插入。
if ($conn->query($sql) === TRUE) {
echo "Tabel <b>products</b> updated successfully<br>";
} else {
echo "<br>Error updating tabel <b>Products</b>: " . $conn->error;
Exit();
}
}
fclose($handle);
}
?>
