有人问到图片如何存储到数据库中,以前也做过,但年代已有点久远,选择使用的软件都升级了,有些东西已废弃。所以花了点时间用VS2008+SQL Server 2005做了一个测试程序,希望能对人有所帮助。与人玫瑰,手有余香。涉及到存储过程,ADO.NET操纵数据库,SQL Server 2005中的image(或varbinary(max))字段使用。
在SQL Server 2005中建立数据库、表、存储过程,SQL如下:
create database test
go
use test
create table tblImage(
[id] int identity(1,1) primary key,
picture varbinary(max))--或picture image
go
--插入图片到数据库表中的varbinary(max)类型(或image)的存储过程,需要文件名及表名参数(有默认值,可不提供此参数)
create proc spInsertImage(@fileName nvarchar(100), @tableName nvarchar(20)='tblImage') as
begin
set nocount on
declare @sql nvarchar(max)
set @sql='insert into ' + @tableName +
' select * from OPENROWSET (bulk ''' + @fileName +''', SINGLE_BLOB) as myTable'
exec(@sql)
end
在VS2008(C#)中新建项目,名为test,WinForm命名为frmImage,先在窗体上添加1个PictureBox,3个Button,1个OpenFileDialog;cs文件中代码如下:
using System;
using System.IO;//自己引入
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;//自己引入
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace test
{
public partial class frmImage : Form
{
public frmImage()
{
InitializeComponent();
}
DataSet ds = new DataSet();
SqlDataAdapter da;
//连接串,express版,集成认证方式
string cnnString = @"integrated security=true;database=test;server=.\sqlexpress";
string fileName="";
//项目名为test,WinForm命名为frmImage
//先在窗体上添加1个PictureBox,3个Button,1个OpenFileDialog
private void frmImage_Load(object sender, EventArgs e)
{
pictureBox1.SizeMode = PictureBoxSizeMode.Zoom;
}
//显示表中的第index+1行的第2列(binary(max)类型)到pictureBox1
private void showImage(int index)
{
byte[] bytes = (byte[])ds.Tables[0].Rows[index][1];
MemoryStream memStream = new MemoryStream(bytes);
try
{
Bitmap myImage = new Bitmap(memStream);
pictureBox1.Image = myImage;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
pictureBox1.Image = null;
}
}
//打开文件,获得一张图片的文件名
private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.InitialDirectory = "D:\\";
openFileDialog1.Filter = "*.jpg|*.*";
openFileDialog1.FilterIndex = 0;
openFileDialog1.FileName="";
openFileDialog1.Title = "打开文件";
openFileDialog1.ShowDialog();
fileName=openFileDialog1.FileName;
}
//插入图片到数据表tblImage中
private void button2_Click(object sender, EventArgs e)
{
SqlConnection cnn = new SqlConnection(cnnString);
cnn.Open();
SqlCommand cmd = new SqlCommand("spInsertImage", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@fileName", fileName);
//@tableName使用默认参数值,下面这句可不用
//cmd.Parameters.AddWithValue("@tableName", "tblImage");
try
{
//调用存储过程spInsertImage
cmd.ExecuteNonQuery();
MessageBox.Show("成功插入一张图片到数据库中!");
}
catch
{
MessageBox.Show("出错了,请确认是否正确选择了图片文件!");
}
finally
{
cnn.Close();
}
}
//随机显示一张图片
private void button3_Click(object sender, EventArgs e)
{
SqlConnection cnn = new SqlConnection(cnnString);
cnn.Open();
da = new SqlDataAdapter("select * from tblImage", cnn);
da.Fill(ds, "testImage");
Random ran = new Random();
int index = ran.Next(ds.Tables[0].Rows.Count);
showImage(index);
}
}
}