ASP.NET如何存取SQLServer數據庫圖片
SQL Server提供了一個特別的數據類型：image，它是一個包含binary數據的類型。下邊這個例子就向你展示了如何將文本或照片放入到數據庫中的辦法。在這篇文章中我們要看到如何在SQL Server中存儲和讀取圖片。
1、建立一個表：
在SQL SERVER中建立這樣結構的一個表：
| 列名 | 類型 | 目的 |
| ID | Integer | 主鍵ID |
| IMGTITLE | Varchar(50) | 圖片的標題 |
| IMGTYPE | Varchar(50) | 圖片類型. ASP.NET要以辨認的類型 |
| IMGDATA | Image | 用于存儲二進制數據 |
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 byte[imgdatalen]; 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.QueryString["imgid"]; 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 = dr["imgtype"].ToString(); Response.BinaryWrite( (byte[]) dr["imgdata"] ); } 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 的摘要說明。 /// ＜/summary＞ public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.Button button1; /// ＜summary＞ /// 必需的設計器變量。 /// ＜/summary＞ private 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＞ /// 清理所有正在使用的資源。 /// ＜/summary＞ protected 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＞ /// 設計器支持所需的方法 - 不要使用代碼編輯器修改 /// 此方法的內容。 /// ＜/summary＞ private 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＞ /// 應用程序的主入口點。 /// ＜/summary＞ [STAThread] static 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 byte[imgdatalen]; 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; try { string 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() ); } finally {} } } private void Form1_Load(object sender, System.EventArgs e) { } private void button2_Click(object sender, System.EventArgs e) { //打開數據庫連接 if( conn.State == ConnectionState.Open) conn.Close(); ConnectionString ="Integrated Security=SSPI;" + "Initial Catalog=mydb;" +"Data Source=localhost;"; conn.ConnectionString = ConnectionString; // 創建數據適配器 string sql="SELECT * FROM ImageStore" ; SqlCommand command = new SqlCommand(sql, conn); try {conn.Open();} catch(Exception newerr) { MessageBox.Show(" 不能打開數據聯接!") ; } finally {} SqlDataReader dr = command.ExecuteReader(); if(dr.Read()) { FileInfo fi = new FileInfo("temp"); FileStream myStream=fi.Open(FileMode.Create); byte[] mydata=((byte[])dr["imgdata"]); //label2.Text="您現在看到的是："+ dr["imgtitle"].ToString(); foreach(byte a in mydata) { myStream.WriteByte(a); } myStream.Close(); Image myImage=Image.FromFile("temp") ; pic1.Image=myImage; pic1.Refresh(); dr.Close (); } else { MessageBox.Show("沒有成功讀入數據!") ; } conn.Close(); } } } |