从数据库读取SQL Varbinary Blob [英] Reading SQL Varbinary Blob from Database
问题描述
我正在将文件保存到sql blob到varbinary（max）列中，并且现在可以正常工作了（我相信）。
I am working on saving files to sql blob to a varbinary(max) column, and have got the save side of things working now (I believe).
鉴于我正在使用存储过程来检索数据库值，所以我不知道如何读取数据，我应该能够访问ds.Tables [0] .Rows [0] [ blobData];所以有必要像我在下面的示例中看到的那样有一个SQLCommand等：
What I can't figure out is how to read the data out, given that I'm retrieving my DB values using a stored procedure I should be able to access the column data like ds.Tables[0].Rows[0]["blobData"]; so is it necessary that I have an SQLCommand etc like I've seen in examples such as the one below:
private void OpenFile(string selectedValue)
{
String connStr = "...connStr";
fileName = ddlFiles.GetItemText(ddlFiles.SelectedItem);
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT BLOBData FROM BLOBTest WHERE testid = " + selectedValue;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
int size = 1024 * 1024;
byte[] buffer = new byte[size];
int readBytes = 0;
int index = 0;
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write, FileShare.None))
{
while ((readBytes = (int)dr.GetBytes(0, index, buffer, 0, size)) > 0)
{
fs.Write(buffer, 0, readBytes);
index += readBytes;
}
}
}
}
}
}
当我无需使用sqlcommand即可访问所需的列时，是否有一种更简单的方法？
Is there a simpler way to do this when I can access the column that I need without the sqlcommand?
希望我已经很清楚了在我的问题中，如果不是那么问，我会详细说明！
Hope I was clear enough in my question, if not then ask and I will elaborate!
更新：
现在的情况是这样-我具有存储过程返回的blobData列的值，可以将其传递到内存流中并调用'LoadDocument（memStream）;但是，这导致文本混乱，而不是显示我的实际文件。
The situation is now this- I have the value of the blobData column returned by my stored procedure, and can pass this into a memory stream and call 'LoadDocument(memStream); however this results in jibberish text instead of my actual file displaying.
我现在的问题是，有没有办法获取完整路径，包括存储在SQL Blob中的文件的文件扩展名？我目前正在为此考虑使用Filetable，希望能够获得完整路径。
My question now is is there a way to get the full path including file extension of a file stored in an SQL Blob? I am currently looking into using a Filetable for this in the hopes that I will be able to get the full path.
UPDATE 2：
我尝试创建一个临时文件并将其读取为无效（仍然乱码）
I tried creating a temp file and reading this to no avail (still gibberish)
string fileName = System.IO.Path.GetTempFileName().ToString().Replace(".tmp", fileExt);
using (MemoryStream myMemoryStream = new MemoryStream(blobData, 0, (int)blobData.Length, false, true))
{
using (FileStream myFileStream1 = File.Create(fileName))
{
myMemoryStream.WriteTo(myFileStream1);
myMemoryStream.Flush();
myMemoryStream.Close();
myFileStream1.Flush();
myFileStream1.Close();
FileInfo fi = new FileInfo(fileName);
Process prc = new Process();
prc.StartInfo.FileName = fi.FullName;
prc.Start();
}
}
干杯，
H
Cheers, H
推荐答案
您正在使它变得比原来更困难。使用MySQL只是因为它很方便-提供程序的工作原理几乎都相同。需要调整一些内容来处理非常大的数据项（比DB Provider还要多的服务器内容）。
You are making it more difficult than it needs to be. This is using MySQL just because it is handy - the providers all work pretty much the same. Some things will need to be tweaked to handle very large data items (more of a server thing than DB Provider).
string sql = "INSERT INTO BlobDemo (filename, fileType, fileData) VALUES (@name, @type, @data)";
byte[] imgBytes;
using (MySqlConnection dbCon = new MySqlConnection(MySQLConnStr))
using (MySqlCommand cmd = new MySqlCommand(sql, dbCon))
{
string ext = Path.GetExtension(filename);
dbCon.Open();
cmd.Parameters.Add("@name", MySqlDbType.String).Value = "ziggy";
cmd.Parameters.Add("@data", MySqlDbType.Blob).Value = File.ReadAllBytes(filename);
cmd.Parameters.Add("@tyoe", MySqlDbType.String).Value = ext;
int rows = cmd.ExecuteNonQuery();
}
文件数据直接馈送到DB Provider
The file data is fed directly to the DB Provider
是否可以获取完整路径，包括存储在SQL Blob中的文件的文件扩展名？
is there a way to get the full path including file extension of a file stored in an SQL Blob?
否。您的代码和上面的代码正在保存构成图像或任何文件的 bytes 。
No. Your code and the code above is saving the bytes which make up an image or any file.
这将读回数据，将其保存到文件中并启动关联的应用程序：
This will read the data back, save it to file and start the associated app:
string SQL = "SELECT itemName, itemData, itemtype FROM BlobDemo WHERE Id = @id";
string ext = "";
string tempFile = Path.Combine(@"C:\Temp\Blobs\",
Path.GetFileNameWithoutExtension(Path.GetTempFileName()));
using (MySqlConnection dbCon = new MySqlConnection(MySQLConnStr))
using (MySqlCommand cmd = new MySqlCommand(SQL, dbCon))
{
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = 14;
dbCon.Open();
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
ext = rdr.GetString(2);
File.WriteAllBytes(tempFile + ext, (byte[])rdr["itemData"]);
}
}
// OS run test
Process prc = new Process();
prc.StartInfo.FileName = tempFile + ext;
prc.Start();
}
- 读回的字节数已匹配
- 启动与图像关联的应用
- 图片框中显示的图像
在两种情况下， File .ReadAllBytes（）和 File.WriteAllBytes（）将为您完成大部分工作，无论文件类型如何。
In both cases, File.ReadAllBytes() and File.WriteAllBytes() will do most of the work for you, no matter the file type.
不需要一次从1k处提取数据。如果blob类似于您希望在应用程序中使用的图像：
There is no need to scoop out the data 1k at a time. If the blob was something like an image you wished to use in the app:
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
ext = rdr.GetString(2);
using (MemoryStream ms = new MemoryStream((byte[])rdr["imgData"]))
{
picBox.Image = Image.FromStream(ms);
}
}
}
可以输入blob字节到内存流，甚至不需要创建临时 Image ，除非您不需要显示它。
The blob bytes can be fed to the memstream, and even a temp Image need not be created unless you don't need to show it.
总的来说，Ceiling Cat使其恢复正常（图像为1.4 MB，已缩放；另一幅15.4 MB图像的测试也可行-两者都比我想存储在数据库中的大）。
In all, Ceiling Cat made it back just fine (image was 1.4 MB, zoomed; another test with a 15.4 MB image also worked - both are larger than I would care to store in a DB).:
根据使用方式，考虑将图像存档到文件系统并仅保存文件名-也许添加 Id 可以确保名称是唯一的，并有助于在视觉上将它们链接到记录。大量数据不仅会使数据库泛滥，而且在字节之间进行转换显然会涉及一些开销，这是可以避免的。
Depending on how this is used, consider archiving the images to somewhere on the file system and just saving the filename - perhaps with the Id added to assure the names are unique and help visually link them to the record. Not only will large blobs of data bloat the DB, but there is obviously some overhead involved in converting to and from bytes which can be avoided.
如果您希望/需要在关联应用程序完成后的某个时候删除它们（实际上不是组件）问题）），然后在特定目录中使用临时文件，这样您就可以在应用结束或启动时删除其中的所有内容（有条件地 1 ）：
If you want/need to delete these at some point after the associated app is done with them (not really a component of the question), then use a tempfile in a specific directory so you can delete everything in it (conditionally1) when the app ends, or at start up:
private string baseAppPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData),
"Company Name", "Product Name", "Temp Files");
为单个文件添加临时文件名和实际扩展名。或者，您可以维护 List< string>
Append a Temp Filename and the actual extension for individual files. Alternatively, you could maintain a List<string> trashCan to store the name of each file you create to be deleted later.
1 每当您删除它们时，都可以使用rashCan 存储创建的每个文件的名称。 ，执行允许文件仍可以在与扩展名相关的应用中打开。
1 Whenever you do delete them, do allow that files could still be open in the app associated with the extension.
这篇关于从数据库读取SQL Varbinary Blob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
