关于BLOB数据操作的问题？

[luoqing]

怎样把一个图像存入数据库中。
能否用Insert 或Update来实现?

[名剑传奇]

具体的代码我记不得啦但是实现的方法是:
1.数据库的栏位设置成images(二进制)
2.把图象的数据文件用文件操作控件读入放在一个不较大的变量里面
3.在insert 或是 update 语句实现插入或是更新.

[dotAge]

请参考vs.net中文联机文档：
ms-help://MS.VSCC/MS.MSDNVS.2052/cpguide/html/cpconobtainingblobvaluesfromdatabase.htm

内有详细示例。

[dotAge]

SORRY,以上只是读，以下文章有写的示例：

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q321900&

这是其中一段：

Dim cn As New SqlConnection(strCn)
Dim cmd As New SqlCommand("INSERT INTO BLOBTest (BLOBData) " & _
"VALUES (@BLOBData)", cn)
Dim strBLOBFilePath As String = _
"C:\Documents and Settings\All Users\Documents" & _
"\My Pictures\Sample Pictures\winter.jpg"
Dim fsBLOBFile As New FileStream(strBLOBFilePath, _
FileMode.Open, FileAccess.Read)
Dim bytBLOBData(fsBLOBFile.Length() - 1) As Byte
fsBLOBFile.Read(bytBLOBData, 0, bytBLOBData.Length)
fsBLOBFile.Close()
Dim prm As New SqlParameter("@BLOBData", SqlDbType.VarBinary, _
bytBLOBData.Length, ParameterDirection.Input, False, _
0, 0, Nothing, DataRowVersion.Current, bytBLOBData)
cmd.Parameters.Add(prm)
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()

[bigrongshu]

给你一段代码:www.codeproject.com上的 (太长,分几次)
-----------------------------------------------------
The Database Tables
CREATE TABLE [dbo].[Albums] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[desc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Photos] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[desc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[album_id] [int] NOT NULL ,
[photo] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The Stored Procedures
CREATE PROC sp_GetPhotoAlbums AS
SELECT Albums.[id] AS AlbumID, Albums.[name] AS Album,
Albums.[desc] AS Album_Desc, Photos.[id] AS PhotoID,
Photos.[name] AS Photo, Photos.photo, Photos.[desc] AS Photo_Desc
FROM Albums INNER JOIN Photos ON Albums.[id] = Photos.album_id
ORDER BY Albums.[id]
GO

CREATE PROCEDURE sp_InsertPhoto
@name AS VARCHAR(50),
@image AS IMAGE,
@album AS INT
AS

INSERT INTO Photos ([name], photo, album_id)
VALUES (@name, @image, @album)

RETURN @@identity
GO

CREATE PROCEDURE sp_NewAlbum
@name AS VARCHAR(20)
AS

INSERT INTO Albums ([name])
VALUES (@name)

RETURN @@identity
GO

[bigrongshu]

Storing the images
After selecting the Add Photo context menu item a multi select FileOpenDialog allows images to be selected for addition. For each file a System.IO.FileStream is created and used to read into a byte array. The byte array is then passed to a method which uses it as an input parameter for a stored procedure to add it to an image field of the database table.

The last step is to add the image to the treeview. A helper class call TreeItem is used for this purpose. This class stores the description and database index id of the image. This class is also used for albums so an enum defines the type of object it is. After a new instance of this class is created it is assigned to the Tag member of the TreeNode. The node is then added to the selected album node.

private void OnAddPhoto(object sender, System.EventArgs e)
{
// Show the file open dialog
if( DialogResult.OK == FileOpenDlg.ShowDialog() )
{
// Retrieve the treeitem for the selected parent node
TreeItem item = (TreeItem)treeAlbum.SelectedNode.Tag;

// We allow multiple selections so loop through each one
foreach( string file in FileOpenDlg.FileNames )
{
// Create a new stream to load this photo into
System.IO.FileStream stream = new System.IO.FileStream(file,
System.IO.FileMode.Open,
System.IO.FileAccess.Read);
// Create a buffer to hold the stream bytes
byte[] buffer = new byte[stream.Length];
// Read the bytes from this stream
stream.Read(buffer, 0, (int)stream.Length);
// Now we can close the stream
stream.Close();

// Extract out the name of the file an use it for the name
// of the photo
string strName = System.IO.Path.GetFileNameWithoutExtension(file);

// Insert the image into the database and add it to the tree
InsertImage(ref buffer, strName, item.Id);
buffer = null;
}
// Select the last child node under this album
treeAlbum.SelectedNode = treeAlbum.SelectedNode.LastNode;
}
}

private void InsertImage(ref byte[] buffer, string strName, int nAlbum)
{
try
{
// Create a stored procedure command
SqlCommand cmd = new SqlCommand("sp_InsertPhoto", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;

// Add the return value parameter
SqlParameter param = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;

// Add the name paramter and set the value
cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = strName;
// Add the image paramter and set the value
cmd.Parameters.Add("@image", SqlDbType.Image).Value = buffer;
// Add the album paramter and set the value
cmd.Parameters.Add("@album", SqlDbType.Int).Value = nAlbum;

// Execute the insert
cmd.ExecuteNonQuery();

// Return value will be the index of the newly added photo
int nID = (int)cmd.Parameters["RETURN_VALUE"].Value;

// Create a new node
TreeNode node = new TreeNode(strName);
// Create new treeitem to store the info about this photo
node.Tag = new TreeItem(ItemType.Photo, nID, "Enter description");

// Get the index of the album we are adding to
// and insert the new photo node
nID = treeAlbum.SelectedNode.Index;
treeAlbum.Nodes[nID].Nodes.Add(node);
}
catch(Exception e)
{
MessageBox.Show(e.Message);
}
}

[bigrongshu]

Display the image
The AfterSelect event captures the selection of an image or album. If an images is selected the Tag member of the selected node is retrieved and cast into a TreeItem. The Id member of this class is then passed to a stored procedure to retrieve the desired image. The return of the ExecuteScalar method is cast into a byte array which is then read into a System.IO.MemoryStream. This stream object is then used to create a Bitmap which is then used assigned to the picturebox for display.

private void OnAfterSelect(object sender,
System.Windows.Forms.TreeViewEventArgs e)
{
try
{
// Disable edit controls if we were in edit mode
if( true == btnUpdate.Visible )
{
btnUpdate.Visible = false;
txtDesc.Visible = false;
lblDesc.Visible = true;
}

// Retrieve the item info for this node
TreeItem item = (TreeItem)e.Node.Tag;

// If the selected item is an album...
if( ItemType.Album == item.Type )
{
lblDesc.Text = item.Desc; // Set the description
pictureBox.Image = null; // Clear the image
return;
}

// ...otherwise it is a photo

// Create a command to select the selected photo
string strCmd = String.Format("SELECT photo FROM Photos WHERE id = {0}",
item.Id);
SqlCommand cmd = new SqlCommand(strCmd, sqlConn);

// Set the description
lblDesc.Text = item.Desc;

// Get bytes return from stored proc
byte[] b = (byte[])cmd.ExecuteScalar();
if(b.Length > 0)
{
// Open a stream for the image and write the bytes into it
System.IO.MemoryStream stream = new System.IO.MemoryStream(b, true);
stream.Write(b, 0, b.Length);

// Create a bitmap from the stream
Bitmap bmp = new Bitmap(stream);

// Check for scaling and assign the bitmap to the Picturebox
if( bmp.Width > 500 && bmp.Height > 300)
{
Bitmap bmp1 = new Bitmap(bmp, new Size(500,300));
pictureBox.Image = bmp1;
}
else
pictureBox.Image = bmp;

// Close the stream
stream.Close();
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}