//从数据库中获取 二进制大对象 (BLOB) 值
public void GetBLOB()
{
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source= C:\Program Files\Microsoft Office\OFFICE11
\SAMPLES\" +@"Northwind.mdb";
OleDbCommand cmd = new OleDbCommand("SELECT 类别ID,图片 FROM 类别", conn);
FileStream fs; // Writes the BLOB to a file (*.bmp).
BinaryWriter bw; // Streams the BLOB to the FileStream object. int bufferSize = 100; // Size of the BLOB buffer.
byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to be filled by GetBytes.
long retval; // The bytes returned from GetBytes.
long startIndex = 0; // The starting position in the BLOB output. string category_id = ""; // The category id to use in the file name. conn.Open(); OleDbDataReader myReader = cmd.ExecuteReaderCommandBehavior.SequentialAccess); while (myReader.Read())
{
// Get the category id, which must occur before getting the logo.
category_id = myReader.GetInt32(0).ToString(); Console.WriteLine("类别ID = {0}", category_id); // Create a file to hold the output.
fs = new FileStream("category" + category_id + ".bmp", FileMode.OpenOrCreate, FileAccess.Write);
bw = new BinaryWriter(fs); // Reset the starting byte for the new BLOB.
startIndex = 0; // Read the bytes into outbyte[] and retain the number of bytes returned.
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize); // Continue reading and writing while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
bw.Write(outbyte);
bw.Flush(); // Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize;
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
} // Write the remaining buffer.
bw.Write(outbyte, 0, (int)retval - 1);
bw.Flush();
// Close the output file.
bw.Close();
fs.Close();
} // Close the reader and the connection.
myReader.Close();
conn.Close();
}这个函数能够正常运行，但执行之后，保存在硬盘上的BMP文件都是无效的，根本打不开。
这是什么原因呢？还有我要是在PictureBox中显示读取的位图该怎么做?
请按我给的方法来，使用OleDbDataReader。
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
byte[] b= (byte[])cmd.ExecuteScalar();
if (b.Length 〉 0)
{
MemoryStream stream = new MemoryStream(b, true);
stream.Write(b, 0, b.Length);
pictureBox1.Image = new Bitmap(stream);
stream.Close();
}
conn.Close();
按照http://community.csdn.net/Expert/topic/5312/5312107.xml?temp=.213833这个贴子的方法也不行，在Image.FromStream(ms);报错，提示‘参数无效’。
然后我又用
long len = dr.GetBytes(1,0,null,0,0);
byte[] image = new byte[len];
dr.GetBytes(1, 0, image, 0, (int)len);
MemoryStream ms = new MemoryStream(image);
Image image = Image.FromStream(ms);
还是不行。
我按照那个贴子中的方法插入了一行:
OleDbCommand addEmp = new OleDbCommand("INSERT INTO 类别 Values(9,'dsf','dfas',@Image)", nwindConn);
addEmp.Parameters.AddWithValue("@Image", photo);
之后可以打开Access查看在图片列中显示是"长二进制数据", 而且在Access中也无法显示。
中用了同样的方法，他也是先存后读，与http://community.csdn.net/Expert/topic/5312/5312107.xml?temp=.213833
中一模一样。而且存入的也是‘长二进制数据’。并不是OLE对象。
问题关键是在
OleDbCommand addEmp = new OleDbCommand("INSERT INTO 类别 Values(9,'dsf','dfas',@Image)", 这句。
OleDbCommand addEmp = new OleDbCommand("INSERT INTO 类别 Values(9,'dsf','dfas',@Image)", 这句。ole好像不能这么存...
OleDbCommand addEmp = new OleDbCommand("INSERT INTO 类别 Values(@类别ID,@类别名称,@说明,@图片)", nwindConn);addEmp.Parameters.Add("@类别ID", OleDbType.Integer).Value = 9;
addEmp.Parameters.Add("@类别名称", OleDbType.VarChar, 10).Value ="aaaa" ;
addEmp.Parameters.Add("@说明", OleDbType.VarChar, 30).Value = "bbbb";
addEmp.Parameters.Add("@图片", OleDbType.Binary, photo.Length).Value = photo;使用OleDB只能用OleDbType.Binary,没有SqlDbType.Image中的这种类型。
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;namespace WindowsApplication1
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
} private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
label1.Text = ofd.FileName;
this.pictureBox1.Image = Image.FromFile(label1.Text);
}
} OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\student.mdb");
DataSet ds = new DataSet();
private void button2_Click(object sender, EventArgs e)
{
FileStream fs = new FileStream(label1.Text, FileMode.Open, FileAccess.Read);
byte[] data = new byte[fs.Length];
fs.Read(data, 0, data.Length);
fs.Close(); OleDbCommand command = new OleDbCommand();
command.CommandText = "insert into pictureTab values(@id,@picture)";
command.Parameters.AddWithValue("@id", textBox1.Text);
command.Parameters.AddWithValue("@picture", data);
command.Connection = conn;
try
{
conn.Open();
int i = command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
command = null;
}
} private void button3_Click(object sender, EventArgs e)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "select picture from pictureTab where (id = @id)";
command.Parameters.AddWithValue("@id", textBox1.Text);
command.Connection = conn;
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
try
{
//ds = new DataSet();
conn.Open();
adapter.Fill(ds, "picture");
}
catch (Exception ex)
{ MessageBox.Show(ex.ToString()); }
finally
{
conn.Close();
command = null;
adapter = null;
} byte[] data = (byte[])ds.Tables["picture"].Rows[0]["picture"];
using (MemoryStream memStream = new MemoryStream(data))
{
this.pictureBox1.Image = Image.FromStream(memStream);
ds.Tables.Clear();
}
}
}
}
/*int id = int.Parse(textBox1.Text);
command = new OleDbCommand();
command.CommandText = "select picture from ?? where(id=@id)";
command.Parameters.AddWithValue("@id", id);
command.Connection = conn;
conn.Open();
OleDbDataReader reader = command.ExecuteReader();
reader.Read();
byte[] data = (byte[])reader["picture"];
MemoryStream mem = new MemoryStream(data);
pictureBox1.Image = Image.FromStream(mem);
conn.Close();*/