ASP.NET如何存取SQL Server数据库图片

SQL Server提供了一个特别的数据类型image它是一个包含binary数据的类型下边这个例子就向你展示了如何将文本或照片放入到数据库中的办法在这篇文章中我们要看到如何在SQL Server中存储和读取图片

建立一个表

在SQL SERVER中建立这样结构的一个表

列名类型目的IDInteger主键IDIMGTITLEVarchar()图片的标题IMGTYPEVarchar()图片类型 ASPNET要以辨认的类型IMGDATAImage用于存储二进制数据

存储图片到SQL SERVER数据库中

为了能存储到表中你首先要上传它们到你的WEB 服务器上你可以开发一个web form它用来将客户端中TextBox web control中的图片入到你的WEB服务器上来将你的 encType 属性设置为myltipart/formdata

Stream imgdatastream = FilePostedFileInputStream;

int imgdatalen = FilePostedFileContentLength;

string imgtype = FilePostedFileContentType;

string imgtitle = TextBoxText;

byte[] imgdata = new byte[imgdatalen];

int n = imgdatastreamRead(imgdataimgdatalen);

string connstr=((NameValueCollection)ContextGetConfig(appSettings))[connstr];

SqlConnection connection = new SqlConnection(connstr);

SqlCommand command = new SqlCommand

(INSERT INTO ImageStore(imgtitleimgtypeimgdata)

VALUES ( @imgtitle @imgtype@imgdata ) connection );

SqlParameter paramTitle = new SqlParameter

(@imgtitle SqlDbTypeVarChar );

paramTitleValue = imgtitle;

commandParametersAdd( paramTitle);

SqlParameter paramData = new SqlParameter( @imgdata SqlDbTypeImage );

paramDataValue = imgdata;

commandParametersAdd( paramData );

SqlParameter paramType = new SqlParameter( @imgtype SqlDbTypeVarChar );

paramTypeValue = imgtype;

commandParametersAdd( paramType );

connectionOpen();

int numRowsAffected = commandExecuteNonQuery();

connectionClose();

从数据库中恢复读取

现在让我们来从SQL Server中读取我们放入的数据吧!我们将要输出图片到你的浏览器上你也可以将它存放到你要的位置

private void Page_Load(object sender SystemEventArgs e)

{

string imgid =RequestQueryString[imgid];

string connstr=((NameValueCollection)

ContextGetConfig(appSettings))[connstr];

string sql=SELECT imgdata imgtype FROM ImageStore WHERE id = + imgid;

SqlConnection connection = new SqlConnection(connstr);

SqlCommand command = new SqlCommand(sql connection);

connectionOpen();

SqlDataReader dr = commandExecuteReader();

if(drRead())

{

ResponseContentType = dr[imgtype]ToString();

ResponseBinaryWrite( (byte[]) dr[imgdata] );

}

connectionClose();

}

要注意的是ResponseBinaryWrite 而不是ResponseWrite

下面给大家一个用于C# Winform的存入读取程序其中不同请大家自己比较!(为了方便起见我将数据库字段简化为二个imgtitle和imgdata

using System;

using SystemDrawing;

using SystemCollections;

using SystemComponentModel;

using SystemWindowsForms;

using SystemData;

using SystemIO;

using SystemDataSqlClient;

namespace WindowsApplication

{

/// ＜summary＞

/// Form 的摘要说明

/// ＜/summary＞

public class Form : SystemWindowsFormsForm

{

private SystemWindowsFormsButton button;

/// ＜summary＞

/// 必需的设计器变量

/// ＜/summary＞

private SystemComponentModelContainer components = null;

private string ConnectionString = Integrated Security=SSPI;Initial Catalog=;DataSource=localhost;;

private SqlConnection conn = null;

private SqlCommand cmd = null;

private SystemWindowsFormsButton button;

private SystemWindowsFormsPictureBox pic;

private SystemWindowsFormsOpenFileDialog openFileDialog;

private string sql = null;

private SystemWindowsFormsLabel label;

private string nowId=null;

public Form()

{

//

// Windows 窗体设计器支持所必需的

//

InitializeComponent();

conn = new SqlConnection(ConnectionString);

//

// TODO: 在 InitializeComponent 调用后添加任何构造函数代码

//

}

/// ＜summary＞

/// 清理所有正在使用的资源

/// ＜/summary＞

protected override void Dispose( bool disposing )

{

if (connState == ConnectionStateOpen)

connClose();

if( disposing )

{

if (components != null)

{

componentsDispose();

}

}

baseDispose( disposing );

}

#region Windows Form Designer generated code

/// ＜summary＞

/// 设计器支持所需的方法 不要使用代码编辑器修改

/// 此方法的内容

/// ＜/summary＞

private void InitializeComponent()

{

thisbutton = new SystemWindowsFormsButton();

thispic = new SystemWindowsFormsPictureBox();

thisbutton = new SystemWindowsFormsButton();

thisopenFileDialog = new SystemWindowsFormsOpenFileDialog();

thislabel = new SystemWindowsFormsLabel();

thisSuspendLayout();

//

// button

//

thisbuttonLocation = new SystemDrawingPoint( );

thisbuttonName = button;

thisbuttonSize = new SystemDrawingSize( );

thisbuttonTabIndex = ;

thisbuttonText = 加入新的图片;

thisbuttonClick += new SystemEventHandler(thisbutton_Click);

//

// pic

//

thispicLocation = new SystemDrawingPoint( );

thispicName = pic;

thispicSize = new SystemDrawingSize( );

thispicTabIndex = ;

thispicTabStop = false;

//

// button

//

thisbuttonLocation = new SystemDrawingPoint( );

thisbuttonName = button;

thisbuttonSize = new SystemDrawingSize( );

thisbuttonTabIndex = ;

thisbuttonText = 从数据库中恢复图像;

thisbuttonClick += new SystemEventHandler(thisbutton_Click);

//

// openFileDialog

//

thisopenFileDialogFilter = \图像文件(*jpg*bmp*gif)|*jpg|*bmp|*gif\;

//

// label

//

thislabelLocation = new SystemDrawingPoint( );

thislabelName = label;

thislabelSize = new SystemDrawingSize( );

thislabelTabIndex = ;

//

// Form

//

thisAutoScaleBaseSize = new SystemDrawingSize( );

thisClientSize = new SystemDrawingSize( );

thisControlsAddRange(new SystemWindowsFormsControl[] {

thislabel

thisbutton

thispic

thisbutton});

thisName = Form;

thisText = Form;

thisLoad += new SystemEventHandler(thisForm_Load);

thisResumeLayout(false);

}

#endregion

/// ＜summary＞

/// 应用程序的主入口点

/// ＜/summary＞

[STAThread]

static void Main()

{

ApplicationRun(new Form());

}

private void button_Click(object sender SystemEventArgs e)

{

openFileDialogShowDialog ();

if (openFileDialogFileNameTrim()!=)

{

FileInfo fi = new FileInfo(openFileDialogFileName);

string imgtitle=openFileDialogFileName;

int imgdatalen=(int)fiLength;

byte[] imgdata = new byte[imgdatalen];

Stream imgdatastream=fiOpenRead();

int n=imgdatastreamRead(imgdataimgdatalen);

if( connState == ConnectionStateOpen)

connClose();

ConnectionString =Integrated Security=SSPI; + Initial Catalog=mydb; +Data Source=localhost;;

connConnectionString = ConnectionString;

try

{

string mySelectQuery = INSERT INTO ImageStore(imgtitleimgdata) VALUES (@imgtitle @imgdata );

//string mySelectQuery=UPDATE ImageStore set imgtitle=@imgtitleimgdata=@imgdata ;

SqlCommand myCommand = new SqlCommand(mySelectQuery conn);

SqlParameter paramTitle = new SqlParameter(@imgtitle SqlDbTypeVarChar );

paramTitleValue = imgtitle;

myCommandParametersAdd( paramTitle);

SqlParameter paramData = new SqlParameter( @imgdata SqlDbTypeImage );

paramDataValue = imgdata;

myCommandParametersAdd( paramData );

connOpen();

int numRowsAffected = myCommandExecuteNonQuery();

connClose();

}

catch(Exception err)

{

MessageBoxShow(您输入名称可能在数据库中已存在或输入为空请检查!+errToString() );

}

finally

{}

}

}

private void Form_Load(object sender SystemEventArgs e)

{

}

private void button_Click(object sender SystemEventArgs e)

{

//打开数据库连接

if( connState == ConnectionStateOpen)

connClose();

ConnectionString =Integrated Security=SSPI; + Initial Catalog=mydb; +Data Source=localhost;;

connConnectionString = ConnectionString;

// 创建数据适配器

string sql=SELECT * FROM ImageStore ;

SqlCommand command = new SqlCommand(sql conn);

try

{connOpen();}

catch(Exception newerr)

{

MessageBoxShow( 不能打开数据联接!) ;

}

finally

{}

SqlDataReader dr = commandExecuteReader();

if(drRead())

{

FileInfo fi = new FileInfo(temp);

FileStream myStream=fiOpen(FileModeCreate);

byte[] mydata=((byte[])dr[imgdata]);

//labelText=您现在看到的是+ dr[imgtitle]ToString();

foreach(byte a in mydata)

{

myStreamWriteByte(a);

}

myStreamClose();

Image myImage=ImageFromFile(temp) ;

picImage=myImage;

picRefresh();

drClose ();

}

else

{

MessageBoxShow(没有成功读入数据!) ;

}

connClose();

}

}

}