从SQL数据库中提取Blob&当我拥有想要的图像时，将其写入文件 [英] Extract blobs from SQL database & writing it to a file when I have the image that I want

问题描述

超时已过期.超时时间已到操作完成或服务器没有响应.

我不确定100％是否正确地编写了所有代码.

adsblobfile表包含:

blob_image varbinary(MAX)
file_guid uniqueidentifier
file_size int


Web上的所有内容都使用一个定义为int的ID.但是，我的file_guid是一个唯一标识符.

有人可以告诉我我做错了吗?谢谢.

我尝试过的事情:

公共无效Getblob(字符串doc1，字符串country_client，字符串varPathToNewLocation)
{

试试
{
使用(SqlConnection varConnection = new SqlConnection(ConnectionString2))
{
SqlCommand getpic =新的SqlCommand(@"SELECT DISTINCT b.blob_image，y.title，y.mime_type，b.file_size" +
"FROM adsdocument y" +
"INNER JOIN adsindex x" +
打开(x.doc_guid = y.doc_guid)" +
"INNER JOIN adspage p" +
打开(x.doc_guid = p.doc_guid)" +
"INNER JOIN adsfileblob b" +
打开(p.file_guid = b.file_guid)" +
"LEFT OUTER JOIN agltransact t" +
打开(cast(t.voucher_no为varchar(50))= x.doc_index_2)AND(t.client = x.doc_index_1)" +
"WHERE x.doc_index_1 = @country" +
"AND y.title = @doc"，varConnection);

getpic.Parameters.Add("@ country"，SqlDbType.NVarChar，50).Value = country_client;
getpic.Parameters.Add("@ doc"，SqlDbType.NVarChar，50).Value = doc1;

FileStream fs; //将BLOB写入文件(* .bmp)
BinaryWriter bw; //将BLOB流传输到FileStream对象
int buffersize = 1000; //BLOB缓冲区的大小
byte []缓冲区=新的byte [buffersize];
byte [] outbyte =新的byte [buffersize]; //由GetBytes填充的BLOB byte []缓冲区
long numberofbytes = 0;
//长时间检索； //从GetBytes返回的字节
long startindex = 0; //BLOB输出中的起始位置
//string var_id ="; //在文件名中使用的ID


如果(varConnection.State！= ConnectionState.Open)
{
varConnection.Open();
}

SqlDataReader myReader = getpic.ExecuteReader(CommandBehavior.SingleRow);
myReader.Read();
//创建一个文件以保存输出

字符串filname = varPathToNewLocation.ToString()+"\\" + doc1.ToString();
fs = new FileStream(filname，FileMode.OpenOrCreate，FileAccess.Write，FileShare.Write);
bw =新的BinaryWriter(fs);

做
{
numberofbytes = myReader.GetBytes(0，startindex，buffer，0，buffersize);
如果(numberofbytes == 0)
{
休息；
}
bw.Write(buffer，0，(int)numberofbytes);
startindex + = numberofbytes;
} while(true);
bw.Flush();

Timeout expired. The timeout period elapsed to completion of the operation or the server is not responding.

I''m not 100% sure that I coded everything correctly.

The adsblobfile table consists of:

blob_image varbinary(MAX)
file_guid uniqueidentifier
file_size int


Everything on the Web uses an ID defined as an int; however, my file_guid is a uniqueidentifier.

Could someone please tell me what I am doing wrong? Thank you.

What I have tried:

public void Getblob(string doc1,string country_client,string varPathToNewLocation)
{

try
{
using (SqlConnection varConnection = new SqlConnection(ConnectionString2))
{
SqlCommand getpic = new SqlCommand(@"SELECT DISTINCT b.blob_image,y.title,y.mime_type,b.file_size " +
"FROM adsdocument y " +
"INNER JOIN adsindex x " +
"ON (x.doc_guid = y.doc_guid )" +
"INNER JOIN adspage p " +
"ON (x.doc_guid = p.doc_guid) " +
"INNER JOIN adsfileblob b " +
"ON (p.file_guid = b.file_guid) " +
"LEFT OUTER JOIN agltransact t " +
"ON (cast(t.voucher_no as varchar(50)) = x.doc_index_2 ) AND (t.client = x.doc_index_1 ) " +
"WHERE x.doc_index_1 = @country " +
"AND y.title = @doc", varConnection);

getpic.Parameters.Add("@country", SqlDbType.NVarChar,50).Value = country_client;
getpic.Parameters.Add("@doc", SqlDbType.NVarChar,50).Value = doc1;

FileStream fs; // Writes the BLOB to a file (*.bmp)
BinaryWriter bw; // Streams the BLOB to the FileStream object
int buffersize = 1000; // Size of BLOB buffer
byte[] buffer = new byte[buffersize];
byte[] outbyte = new byte[buffersize]; // The BLOB byte[] buffer to be filled by GetBytes
long numberofbytes = 0;
//long retval; // The bytes returned from GetBytes
long startindex = 0; // The starting position in the BLOB output
//string var_id = ""; // The ID to use in the file name


if (varConnection.State != ConnectionState.Open)
{
varConnection.Open();
}

SqlDataReader myReader = getpic.ExecuteReader(CommandBehavior.SingleRow);
myReader.Read();
// Create a file to hold the output

string filname = varPathToNewLocation.ToString() + "\\" + doc1.ToString();
fs = new FileStream(filname, FileMode.OpenOrCreate, FileAccess.Write, FileShare.Write);
bw = new BinaryWriter(fs);

do
{
numberofbytes = myReader.GetBytes(0, startindex, buffer, 0, buffersize);
if (numberofbytes == 0)
{
break;
}
bw.Write(buffer, 0, (int)numberofbytes);
startindex += numberofbytes;
} while (true);
bw.Flush();

推荐答案

无法运行您的查询，则可能会误以为SELECT DISTINCT b.blob_image在这里可能是相关的.取而代之的是，添加一个包含Blob数据的SHA或MD5的哈希列，并将其用作DISTINCT比较.比较128或256位值将比比较整个图像数据快得多！

用它返回ID值，并加入ID值以产生输出.

Without being able to run your query, I''d hazard that the SELECT DISTINCT b.blob_image is possibly relevant here. Instead of that, add a Hash column containing the SHA or MD5 of the blob data, and use that as the DISTINCT comparison. comparing 128 or 256 bit values is going to be significantly quicker than comparing whole image data!

Use that to return ID values, and JOIN those to produce your output.

这篇关于从SQL数据库中提取Blob&当我拥有想要的图像时，将其写入文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！