存储图片到SQL-SERVER数据库中.doc

如何存储图片到SQL SERVER数据库中SQL Server提供了一个特别的数据类型：image，它是一个包含binary数据的类型。下边这个例子就向你展示了如何将文本或照片放入到数据库中的办法。在这篇文章中我们要看到如何在SQL Server中存储和读取图片。1、建立一个表：在SQL SERVER中建立这样结构的一个表：列名 类型目的 ID Integer 主键ID IMGTITLE Varchar(50) 图片的标题 IMGTYPE Varchar(50)图片类型. ASP.NET要以辨认的类型IMGDATAImage用于存储二进制数据 2、存储图片到SQL SERVER数据库中为了能存储到表中，你首先要上传它们到你的WEB 服务器上，你可以开发一个web form，它用来将客户端中TextBox web control中的图片入到你的WEB服务器上来。将你的 encType 属性设置为：myltipart/formdata.Stream imgdatastream = File1.PostedFile.InputStream;int imgdatalen = File1.PostedFile.ContentLength;string imgtype = File1.PostedFile.ContentType;string imgtitle = TextBox1.Text;byte imgdata = new byteimgdatalen;int n = imgdatastream.Read(imgdata,0,imgdatalen);string connstr=(NameValueCollection)Context.GetConfig(appSettings)connstr;SqlConnection connection = new SqlConnection(connstr);SqlCommand command = new SqlCommand(INSERT INTO ImageStore(imgtitle,imgtype,imgdata)VALUES ( imgtitle, imgtype,imgdata ), connection );SqlParameter paramTitle = new SqlParameter(imgtitle, SqlDbType.VarChar,50 );paramTitle.Value = imgtitle;command.Parameters.Add( paramTitle);SqlParameter paramData = new SqlParameter( imgdata, SqlDbType.Image );paramData.Value = imgdata;command.Parameters.Add( paramData );SqlParameter paramType = new SqlParameter( imgtype, SqlDbType.VarChar,50 );paramType.Value = imgtype;command.Parameters.Add( paramType );connection.Open();int numRowsAffected = command.ExecuteNonQuery();connection.Close();3、从数据库中恢复读取现在让我们来从SQL Server中读取我们放入的数据吧!我们将要输出图片到你的浏览器上，你也可以将它存放到你要的位置。private void Page_Load(object sender, System.EventArgs e)string imgid =Request.QueryStringimgid;string connstr=(NameValueCollection)Context.GetConfig(appSettings)connstr;string sql=SELECT imgdata, imgtype FROM ImageStore WHERE id = + imgid;SqlConnection connection = new SqlConnection(connstr);SqlCommand command = new SqlCommand(sql, connection);connection.Open();SqlDataReader dr = command.ExecuteReader();if(dr.Read()Response.ContentType = drimgtype.ToString();Response.BinaryWrite( (byte) drimgdata );connection.Close();要注意的是Response.BinaryWrite 而不是Response.Write.下面给大家一个用于C# Winform的存入、读取程序。其中不同请大家自己比较!(为了方便起见，我将数据库字段简化为二个：imgtitle和imgdata。using System;using System.Drawing;using System.Collections;using System.ComponentModel;using System.Windows.Forms;using System.Data;using System.IO;using System.Data.SqlClient;namespace WindowsApplication21/ summary/ Form1 的摘要说明。/ /summarypublic class Form1 : System.Windows.Forms.Formprivate System.Windows.Forms.Button button1;/ summary/ 必需的设计器变量。/ /summaryprivate System.ComponentModel.Container components = null;private string ConnectionString = Integrated Security=SSPI;Initial Catalog=;DataSource=localhost;private SqlConnection conn = null; private SqlCommand cmd = null;private System.Windows.Forms.Button button2;private System.Windows.Forms.PictureBox pic1;private System.Windows.Forms.OpenFileDialog openFileDialog1;private string sql = null;private System.Windows.Forms.Label label2;private string nowId=null;public Form1()/ Windows 窗体设计器支持所必需的/InitializeComponent();conn = new SqlConnection(ConnectionString); / TODO: 在 InitializeComponent 调用后添加任何构造函数代码/ summary/ 清理所有正在使用的资源。/ /summaryprotected override void Dispose( bool disposing )if (conn.State = ConnectionState.Open)conn.Close();if( disposing )if (components != null) components.Dispose();base.Dispose( disposing );#region Windows Form Designer generated code/ summary/ 设计器支持所需的方法 - 不要使用代码编辑器修改/ 此方法的内容。/ /summaryprivate void InitializeComponent()this.button1 = new System.Windows.Forms.Button();this.pic1 = new System.Windows.Forms.PictureBox();this.button2 = new System.Windows.Forms.Button();this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();this.label2 = new System.Windows.Forms.Label();this.SuspendLayout();/ / button1/ this.button1.Location = new System.Drawing.Point(0, 40);this.button1.Name = button1;this.button1.Size = new System.Drawing.Size(264, 48);this.button1.TabIndex = 0;this.button1.Text = 加入新的图片;this.button1.Click += new System.EventHandler(this.button1_Click);/ / pic1/ this.pic1.Location = new System.Drawing.Point(280, 8);this.pic1.Name = pic1;this.pic1.Size = new System.Drawing.Size(344, 264);this.pic1.TabIndex = 3;this.pic1.TabStop = false;/ / button2/ this.button2.Location = new System.Drawing.Point(0, 104);this.button2.Name = button2;this.button2.Size = new System.Drawing.Size(264, 40);this.button2.TabIndex = 4;this.button2.Text = 从数据库中恢复图像;this.button2.Click += new System.EventHandler(this.button2_Click);/ / openFileDialog1/ this.openFileDialog1.Filter = 图像文件(*.jpg,*.bmp,*.gif)|*.jpg|*.bmp|*.gif;/ / label2/ this.label2.Location = new System.Drawing.Point(0, 152);this.label2.Name = label2;this.label2.Size = new System.Drawing.Size(264, 48);this.label2.TabIndex = 5;/ / Form1/ this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);this.ClientSize = new System.Drawing.Size(632, 273);this.Controls.AddRange(new System.Windows.Forms.Control this.label2,this.button2,this.pic1,this.button1);this.Name = Form1;this.Text = Form1;this.Load += new System.EventHandler(this.Form1_Load);this.ResumeLayout(false);#endregion/ summary/ 应用程序的主入口点。/ /summarySTAThreadstatic void Main() Application.Run(new Form1();private void button1_Click(object sender, System.EventArgs e) openFileDialog1.ShowDialog ();if (openFileDialog1.FileName.Trim()!=)FileInfo fi = new FileInfo(openFileDialog1.FileName);string imgtitle=openFileDialog1.FileName; int imgdatalen=(int)fi.Length; byte imgdata = new byteimgdatalen;Stream imgdatastream=fi.OpenRead(); int n=imgdatastream.Read(imgdata,0,imgdatalen);if( conn.State = ConnectionState.Open)conn.Close(); ConnectionString =Integrated Security=SSPI; + Initial Catalog=mydb; +Data Source=localhost; conn.ConnectionString = ConnectionString;trystring mySelectQuery = INSERT INTO ImageStore(imgtitle,imgdata) VALUES (imgtitle, imgdata );/string mySelectQuery=UPDATE ImageStore set imgtitle=imgtitle,imgdata=imgdata ;SqlCommand myCommand = new SqlCommand(mySelectQuery, conn);SqlParameter paramTitle = new SqlParameter(imgtitle, SqlDbType.VarChar,50 );paramTitle.Value = imgtitle;myCommand.Parameters.Add( paramTitle);SqlParameter paramData = new SqlParameter( imgdata, SqlDbType.Image );paramData.Value = imgdata;myCommand.Parameters.Add( paramData );conn.Open(); int numRowsAffected = myCommand.ExecuteNonQuery();conn.Close();catch(Exception err)MessageBox.Show(您输入名称可能在数据库中已存在或输入为空，请检查!+err.ToString() );finallyprivate void Form1_Load(object sender, System.EventArgs e)private void button2_Click(object sender, System.EventArgs e)/打开数据库连接if( conn.State = Connect