C從SQL數(shù)據(jù)庫中讀取和存入

1、C#從SQL 數(shù)據(jù)庫中讀取和存入圖片本實(shí)例主要介紹如何將圖片存入數(shù)據(jù)庫。將圖片存入數(shù)據(jù)庫,首先要在數(shù)據(jù)庫中建立一張表,將存儲(chǔ)圖片的字段類型設(shè)為Image類型,用FileStream類、BinaryReader把圖片讀成字節(jié)的形式,賦給一個(gè)字節(jié)數(shù)組,然后用ADO.SqlCommand對(duì)象的ExecuteNonQuery()方法來把數(shù)據(jù)保存到數(shù)據(jù)庫中。主要代碼如下： private void button1_Click(object sender, EventArgs e) openFileDialog1.Filter = *jpg|*.JPG|*.GIF|*.GIF|*.BMP|*.BMP; i

2、f(openFileDialog1.ShowDialog()=DialogResult.OK) string fullpath =openFileDialog1.FileName;/文件路徑 FileStream fs = new FileStream(fullpath, FileMode.Open); byte imagebytes =new bytefs.Length; BinaryReader br = new BinaryReader(fs); imagebytes = br.ReadBytes(Convert.ToInt32(fs.Length); /打開數(shù)據(jù)庫 SqlConnect

3、ion con = new SqlConnection(server=(local);uid=sa;pwd=;database=db_05); con.Open(); SqlCommand com = new SqlCommand(insert into tb_08 values(ImageList),con); com.Parameters.Add(ImageList, SqlDbType.Image); com.ParametersImageList.Value = imagebytes; com.ExecuteNonQuery(); con.Close(); 本實(shí)例主要介紹如何從數(shù)據(jù)庫中

4、把圖片讀出來。實(shí)現(xiàn)本實(shí)例主要是利用SqlDataReader從數(shù)據(jù)庫中把Image字段值讀出來，賦給一個(gè)byte字節(jié)數(shù)組，然后使用MemoryStream類與Bitmap把圖片讀取出來。主要代碼如下： private void button1_Click(object sender, EventArgs e) byte imagebytes = null; /打開數(shù)據(jù)庫 SqlConnection con = new SqlConnection(server=(local);uid=sa;pwd=;database=db_05); con.Open(); SqlCommand com = ne

5、w SqlCommand(select top 1* from tb_09, con); SqlDataReader dr = com.ExecuteReader(); while (dr.Read() imagebytes = (byte)dr.GetValue(1); dr.Close(); com.Clone(); con.Close(); MemoryStream ms = new MemoryStream(imagebytes); Bitmap bmpt = new Bitmap(ms); pictureBox1.Image = bmpt; 本實(shí)例主要介紹如何只允許輸入指定圖片格式。

6、用OpenFileDialog控件打開圖片文件，只要將OpenFileDialog控件的Filter屬性指定為特定的圖片格式即可。例如，打開.bmp文件的圖片，主要代碼如下:this.openFileDialog1.Filter = bmp文件(*.bmp)|*.bmp；在用pictureBox控件輸入圖片時(shí)，要想統(tǒng)一圖片大小，只需把控件的SizeMode屬性值設(shè)為StretchImage即可，StretchImage值表示圖像的大小將調(diào)整為控件的大小。這樣，圖片的大小就統(tǒng)一了。ASP.NET下上傳圖片到數(shù)據(jù)庫，并且讀出圖片 首先在SQL Server中建立一個(gè)圖片存儲(chǔ)的數(shù)庫表，ImageDa

7、ta Column為圖象二進(jìn)制數(shù)據(jù)儲(chǔ)存字段，ImageContentType Column為圖象文件類型記錄字段，ImageDescription Column為儲(chǔ)蓄圖象文件說明字段,ImageSize Column為儲(chǔ)存圖象文件長度字段，結(jié)構(gòu)如下：CREATE TABLE dbo.ImageStore ( ImageID int IDENTITY (1, 1) NOT NULL , ImageData image NULL , ImageContentType varchar (50) COLLATE Chinese_PRC_CI_AS NULL , ImageDescription var

8、char (200) COLLATE Chinese_PRC_CI_AS NULL , ImageSize int NULL ) ON PRIMARY TEXTIMAGE_ON PRIMARY*/ /UpLoadImage.aspx程序內(nèi)容如下：上傳圖片 上傳圖片(選擇你要上傳的圖片) 文件說明(添加上傳圖片說明，如：作者、出處) /UpLoadImage.cs程序內(nèi)容如下：using System;using System.Web;using System.IO;using System.Data;using System.Data.SqlClient;using System.Web.UI

9、;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls; namespace UploadImage public class UploadImage : Page protected HtmlInputFile UP_FILE; /HtmlControl、WebControls控件對(duì)象protected TextBox txtDescription;protected Label txtMessage;protected Int32 FileLength = 0; /記錄文件長度變量 protected void B

10、utton_Submit(System.Object sender, System.EventArgs e) HttpPostedFile UpFile = UP_FILE.PostedFile; /HttpPostedFile對(duì)象，用于讀取圖象文件屬性FileLength = UpFile.ContentLength; /記錄文件長度 try if (FileLength = 0) /文件長度為零時(shí) txtMessage.Text = 請(qǐng)你選擇你要上傳的文件; else Byte FileByteArray = new ByteFileLength; /圖象文件臨時(shí)儲(chǔ)存Byte數(shù)組 Stre

11、am StreamObject = UpFile.InputStream; /建立數(shù)據(jù)流對(duì)像 /讀取圖象文件數(shù)據(jù)，F(xiàn)ileByteArray為數(shù)據(jù)儲(chǔ)存體，0為數(shù)據(jù)指針位置、FileLnegth為數(shù)據(jù)長度 StreamObject.Read(FileByteArray,0,FileLength); /建立SQL Server鏈接 SqlConnection Con = new SqlConnection(Data Source=Localhost;Initial Catalog=testdb;User ID=sa;Pwd=;); String SqlCmd = INSERT INTO Image

12、Store (ImageData, ImageContentType, ImageDescription, ImageSize) valueS (Image, ContentType, ImageDescription, ImageSize); SqlCommand CmdObj = new SqlCommand(SqlCmd, Con); CmdObj.Parameters.Add(Image,SqlDbType.Binary, FileLength).value = FileByteArray; CmdObj.Parameters.Add(ContentType, SqlDbType.Va

13、rChar,50).value = UpFile.ContentType; /記錄文件類型 /把其它單表數(shù)據(jù)記錄上傳 CmdObj.Parameters.Add(ImageDescription, SqlDbType.VarChar,200).value = txtDescription.Text; /記錄文件長度，讀取時(shí)使用 CmdObj.Parameters.Add(ImageSize, SqlDbType.BigInt,8).value = UpFile.ContentLength; Con.Open(); CmdObj.ExecuteNonQuery(); Con.Close(); t

14、xtMessage.Text = OK!你已經(jīng)成功上傳你的圖片;/提示上傳成功 catch (Exception ex) txtMessage.Text = ex.Message.ToString();/好了，圖片已經(jīng)上傳到數(shù)據(jù)庫，現(xiàn)在還要干什么呢？當(dāng)然是在數(shù)據(jù)庫中讀取及顯示在Web頁中啦,請(qǐng)看以下程序:/ReadImage.aspx程序內(nèi)容如下：/ /ReadImage.cs程序內(nèi)容如下：using System;using System.Data;using System.Data.SqlClient;using System.Web.UI;using System.Web.UI.WebC

15、ontrols;using System.Web.UI.HtmlControls;namespace ReadImage public class MainDisplay : System.Web.UI.Page public void Page_Load(System.Object sender, System.EventArgs e) int ImgID = Convert.ToInt32(Request.QueryStringImgID); /ImgID為圖片ID /建立數(shù)據(jù)庫鏈接 SqlConnection Con = new SqlConnection(Data Source=KIN

16、G;Initial Catalog=testdb;User ID=sa;Pwd=;); String SqlCmd = SELECT * FROM ImageStore WHERE ImageID = ImageID; SqlCommand CmdObj = new SqlCommand(SqlCmd, Con); CmdObj.Parameters.Add(ImageID, SqlDbType.Int).value = ImgID; Con.Open(); SqlDataReader SqlReader = CmdObj.ExecuteReader(); SqlReader.Read();

17、Response.ContentType = (string)SqlReaderImageContentType;/設(shè)定輸出文件類型 /輸出圖象文件二進(jìn)制數(shù)制 Response.OutputStream.Write(byte)SqlReaderImageData, 0, (int)SqlReaderImageSize); Response.End(); Con.Close(); /很簡(jiǎn)單吧_/最后，我們當(dāng)然要把它在Web頁面顯示出來啦/ShowImage.hml這個(gè)是從數(shù)據(jù)庫讀取出來的圖象：0000000000000000000000000000000000000000000000000000

18、0000000000000000000000000000000000000000000000000000000000將圖片插入數(shù)據(jù)庫并使用讀取出來的正確方 作者：佚名 文章來源：不詳 點(diǎn)擊數(shù)：68 更新時(shí)間：2006-11-7 書寫本文是因?yàn)榻裉煲姷紺SDN的首頁上一篇存在明顯失誤的名為“在Asp.Net中從sqlserver檢索(retrieve)圖片”的文章。不說其錯(cuò)誤是因?yàn)橛闷浞椒ù_實(shí)能從數(shù)據(jù)庫中讀取出圖片并顯示在瀏覽器，說其失誤是因?yàn)榇a的意圖不能被完全的實(shí)現(xiàn)，作者也似乎對(duì)http協(xié)議以及瀏覽器在處理http數(shù)據(jù)的流程一知半解。 1、如何出錯(cuò) 以下是這片文章提到的方法： Public

19、Sub Page_Load(sender As Object, e As EventArgs) Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings(ConnectionString) Dim myCommand As New SqlCommand(Select * from Person, myConnection) Try myConnection.Open() Dim myDataReader as SqlDataReader myDataReader = myCommand.ExecuteRead

20、er(CommandBehavior.CloseConnection) Do While (myDataReader.Read() Response.ContentType = myDataReader.Item(PersonImageType) Response.BinaryWrite(myDataReader.Item(PersonImage) Loop myConnection.Close() Response.Write(Person info successfully retrieved!) Catch SQLexc As SqlException Response.Write(Re

21、ad Failed : & SQLexc.ToString() End Try End Sub 顯然，編程者是想將Person表中所有的記錄中的PersonImage字段所存儲(chǔ)的圖片一次性地輸出到瀏覽器中，并且在輸出成功地情況下在已輸出的圖片的下方打印出“Person info successfully retrieved!”信息。然而事實(shí)上上述代碼僅僅能正確地輸出第一條記錄中的圖片。對(duì)于瀏覽器來說，一個(gè)http請(qǐng)求獲取一個(gè)文件（html或者圖片），所以以上代碼的輸出將被作為一個(gè)文件（類型依據(jù)Response.ContentType = myDataReader.Item(PersonImag

22、eType)定）被瀏覽器處理。如果http相應(yīng)的類型是image/jpeg之類的圖片，則瀏覽器使用相應(yīng)的圖片解析功能對(duì)這一個(gè)圖片文件進(jìn)行解析。因此，上述代碼的顯示結(jié)果只能是第一條記錄PersonImage字段的圖片。后面的記錄輸出的圖片數(shù)據(jù)將成為第一張圖片的多余數(shù)據(jù)（此點(diǎn)具有普遍性，但并非絕對(duì)，依圖片的格式而定），從而后面的“Person info successfully retrieved！”的信息也自然無法本顯示出來，因?yàn)檫@些信息已經(jīng)是圖片文件里面的編碼了。 2、正確的做法 A、將圖片輸入到數(shù)據(jù)庫中，以下是一個(gè)將圖片輸入到數(shù)據(jù)庫的代碼片斷：（完整的DEMO程序見附錄一） FileStre

23、am fs=File.OpenRead(filePath.Text); byte content=new bytefs.Length; fs.Read(content, 0,content.Length); fs.Close(); SqlConnection conn=new SqlConnection(Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseImage;Data Source=(local); conn.Open(); SqlCommand comm=conn.CreateCom

24、mand(); comm.CommandText=insert into Images(Image, contentType) values(image, contentType); comm.CommandType=CommandType.Text; comm.Parameters.Add(image, SqlDbType.Image).Value=content; comm.Parameters.Add(contentType, SqlDbType.NVarChar).Value= GetContentType(new FileInfo(filePath.Text).Extension.R

25、emove(0,1); if(comm.ExecuteNonQuery()=1) MessageBox.Show(Successfully insert image into database!); else MessageBox.Show(Failed to insert image into database); conn.Close(); B、將數(shù)據(jù)庫中的圖片讀出來的代碼片斷：（完整DEMO程序見附錄二） try SqlConnection conn=new SqlConnection(Integrated Security=SSPI;Persist Security Info=Fals

26、e;Initial Catalog=DatabaseImage;Data Source=(local); conn.Open(); SqlCommand comm=conn.CreateCommand(); comm.CommandText=select * from Images where id=id; comm.CommandType=CommandType.Text; comm.Parameters.Add(id, SqlDbType.BigInt).Value=int.Parse(Requestid); SqlDataReader reader=comm.ExecuteReader(

27、); while(reader.Read() Response.ContentType=readercontentType.ToString(); Response.BinaryWrite(byte)readerImage); Response.End(); conn.Close(); catch Response.End(); 這段代碼可置于Page_Load事件中，數(shù)據(jù)圖片要注意的兩點(diǎn)是： 一、 設(shè)置正確的ContentType（http中的content-type），圖片的content-type格式一般為image/*，如jpeg為image/jpeg，bmp為image/bmp等等。

28、 二、 僅僅輸出一張圖片二進(jìn)制流， 中Page_Load事件先于頁面輸出被觸發(fā)，因此圖片的輸出可以在此事件中進(jìn)行，直接操作Reponse對(duì)象，避免輸出與圖片無關(guān)的而外信息（額外的第二張圖片或者文字）。圖片的二進(jìn)制流輸出后及時(shí)使用Response.End()方法結(jié)束http響應(yīng)，避免頁面中的額外信息被的引擎默認(rèn)輸出到客戶端。 希望此文能夠起到拋磚引玉的作用！_ 附錄一： MainForm.cs using System; using System.Drawing; using System.Collections; using System.ComponentModel; using Syste

29、m.Data; using System.Data.SqlClient; using System.IO; using System.Windows.Forms; namespace InsertImageToDatabase public class MainForm : System.Windows.Forms.Form private System.Windows.Forms.OpenFileDialog openFileDlg; private System.Windows.Forms.TextBox filePath; private System.Windows.Forms.But

30、ton browseButton; private System.Windows.Forms.Button insertButton; / / Required designer variable. / private System.ComponentModel.Container components = null; public MainForm() / / Required for Windows Form Designer support / InitializeComponent(); / / TODO: Add any constructor code after Initiali

31、zeComponent call / / / Clean up any resources being used. / protected override void Dispose( bool disposing ) if( disposing ) if (components != null) components.Dispose(); base.Dispose( disposing ); #region Windows Form Designer generated code / / Required method for Designer support - do not modify

32、 / the contents of this method with the code editor. / private void InitializeComponent() this.openFileDlg = new System.Windows.Forms.OpenFileDialog(); this.filePath = new System.Windows.Forms.TextBox(); this.browseButton = new System.Windows.Forms.Button(); this.insertButton = new System.Windows.Fo

33、rms.Button(); this.SuspendLayout(); / / openFileDlg / this.openFileDlg.DefaultExt = *.jpg;*.gif;*.bmp;*.png; this.openFileDlg.Filter = Image Files|*.jpg;*.gif;*.bmp;*.png|All Files|*.*; / / filePath / this.filePath.Location = new System.Drawing.Point(16, 16); this.filePath.Name = filePath; this.file

34、Path.ReadOnly = true; this.filePath.Size = new System.Drawing.Size(168, 20); this.filePath.TabIndex = 0; this.filePath.Text = ; / / browseButton / this.browseButton.Location = new System.Drawing.Point(200, 16); this.browseButton.Name = browseButton; this.browseButton.TabIndex = 1; this.browseButton.

35、Text = &Browse; this.browseButton.Click += new System.EventHandler(this.browseButton_Click); / / insertButton / this.insertButton.Enabled = false; this.insertButton.Location = new System.Drawing.Point(200, 56); this.insertButton.Name = insertButton; this.insertButton.TabIndex = 2; this.insertButton.

36、Text = &Insert; this.insertButton.Click += new System.EventHandler(this.insertButton_Click); / / MainForm / this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(292, 273); this.Controls.Add(this.insertButton); this.Controls.Add(this.browseButton); this.C

37、ontrols.Add(this.filePath); this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle; this.MaximizeBox = false; this.Name = MainForm; this.Text = Insert Image to Database; this.ResumeLayout(false); #endregion / / The main entry point for the application. / STAThread static void Main()

38、 Application.Run(new MainForm(); private void browseButton_Click(object sender, System.EventArgs e) if(openFileDlg.ShowDialog()=DialogResult.OK) filePath.Text=openFileDlg.FileName; insertButton.Enabled=true; private void insertButton_Click(object sender, System.EventArgs e) FileStream fs=File.OpenRe

39、ad(filePath.Text); byte content=new bytefs.Length; fs.Read(content, 0,content.Length); fs.Close(); SqlConnection conn=new SqlConnection(Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseImage;Data Source=(local); conn.Open(); SqlCommand comm=conn.CreateCommand(); comm.Comm

40、andText=insert into Images(Image, contentType) values(image, contentType); comm.CommandType=CommandType.Text; SqlParameter param=comm.Parameters.Add(image, SqlDbType.Image); param.Value=content; comm.Parameters.Add(contentType, SqlDbType.NVarChar).Value= GetContentType(new FileInfo(filePath.Text).Extension.Remove(0,1); if(comm.ExecuteNon