熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

ASP.NET如何存取SQL Server數據庫圖片

2013-11-13 12:18:33  來源: .NET編程 

  SQL Server提供了一個特別的數據類型image它是一個包含binary數據的類型下邊這個例子就向你展示了如何將文本或照片放入到數據庫中的辦法在這篇文章中我們要看到如何在SQL Server中存儲和讀取圖片

  建立一個表

  在SQL SERVER中建立這樣結構的一個表

 列名  類型  目的  ID  Integer  主鍵ID  IMGTITLE  Varchar()  圖片的標題  IMGTYPE  Varchar()  圖片類型 ASPNET要以辨認的類型  IMGDATA  Image  用於存儲二進制數據  

  存儲圖片到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=;Data Source=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();
}
}
}


From:http://tw.wingwit.com/Article/program/net/201311/15629.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.