當前位置:首頁 » 操作系統 » 資料庫保存二進制

資料庫保存二進制

發布時間: 2023-09-02 15:03:25

① 怎麼把圖片文件在sql server資料庫中保存為二進制記錄(註:不是用圖片路徑),最好有源代碼。

首先在SQL Server中建立一個圖片存儲的數庫表,ImageData Column為圖象二進制數據儲存欄位,ImageContentType Column為圖象文件類型記錄欄位,
ImageDescription Column為儲蓄圖象文件說明欄位,ImageSize Column為儲存圖象文件長度欄位,結構如下:
CREATE TABLE [dbo].[ImageStore] (
[ImageID] [int] IDENTITY (1, 1) NOT NULL ,
[ImageData] [image] NULL ,
[ImageContentType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ImageDescription] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[ImageSize] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 向資料庫中存入圖片:using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;namespace UpLoadFile
{
/// <summary>
/// Summary description for UpLoadImage.
/// </summary>
public class UpLoadImage : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button btnUpload;
protected System.Web.UI.WebControls.Label txtMessage;
protected System.Web.UI.WebControls.TextBox txtDescription;
protected System.Web.UI.HtmlControls.HtmlTable Table1;
protected System.Web.UI.HtmlControls.HtmlInputFile UP_FILE;//HtmlControl、WebControls控制項對象
protected Int32 FileLength = 0;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
if(!Page.IsPostBack)
{
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnUpload.Click += new System.EventHandler(this.btnUpload_Click);
this.Load += new System.EventHandler(this.Page_Load); }
#endregion private void btnUpload_Click(object sender, System.EventArgs e)
{
HttpPostedFile UpFile = this.UP_FILE.PostedFile; //HttpPostedFile對象,用於讀取圖象文件屬性
FileLength = UpFile.ContentLength; //記錄文件長度
try
{
if (FileLength == 0)
{ //文件長度為零時
txtMessage.Text = "<b>請你選擇你要上傳的文件</b>";
}
else
{
Byte[] FileByteArray = new Byte[FileLength]; //圖象文件臨時儲存Byte數組
Stream StreamObject = UpFile.InputStream; //建立數據流對像
//讀取圖象文件數據,FileByteArray為數據儲存體,0為數據指針位置、FileLnegth為數據長度
StreamObject.Read(FileByteArray,0,FileLength);
//建立SQL Server鏈接
SqlConnection Con = new SqlConnection("uid=sa;pwd= ;initial catalog=EE;data source=127.0.0.1;Connect Timeout=90");
String SqlCmd = "INSERT INTO ImageStore (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.VarChar,50).Value = UpFile.ContentType; //記錄文件類型
//把其它單表數據記錄上傳
CmdObj.Parameters.Add("@ImageDescription", SqlDbType.VarChar,200).Value = txtDescription.Text;
//記錄文件長度,讀取時使用
CmdObj.Parameters.Add("@ImageSize", SqlDbType.BigInt,8).Value = UpFile.ContentLength;
Con.Open();
CmdObj.ExecuteNonQuery();
Con.Close();
txtMessage.Text = "<p><b>OK!你已經成功上傳你的圖片</b>";//提示上傳成功
}
}
catch (Exception ex)
{
txtMessage.Text = ex.Message.ToString();
}

} }
}
將資料庫中的圖片數據讀出來顯示:using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;namespace UpLoadFile
{
/// <summary>
/// Summary description for ReadImage.
/// </summary>
public class ReadImage : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
if(!Page.IsPostBack)
{
string id = Request.QueryString["ImgID"]; //得到圖片的ID if (id != ""&& id != null && id != string.Empty)
{
ShowImage( id);
}
}
} #region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load); }
#endregion public void ShowImage(string id)
{
int ImgID = Convert.ToInt32(id); //ImgID為圖片ID
//建立資料庫鏈接
SqlConnection Con = new SqlConnection("uid=sa;pwd= ;initial catalog=EE;data source=127.0.0.1;Connect Timeout=90");
String SqlCmd = "SELECT * FROM ImageStore WHERE ImageID = @ImageID";
SqlCommand CmdObj = new SqlCommand(SqlCmd, Con);
CmdObj.Parameters.Add("@ImageID", SqlDbType.Int).Value = ImgID;
try
{
Con.Open();
SqlDataReader SqlReader = CmdObj.ExecuteReader();
SqlReader.Read();
Response.ContentType = (string)SqlReader["ImageContentType"];//設定輸出文件類型
//輸出圖象文件二進制數制
Response.OutputStream.Write((byte[])SqlReader["ImageData"], 0, (int)SqlReader["ImageSize"]);
Response.End();
Con.Close();
}
catch
{
Response.Write("<script>alert('該圖片不存在');</script>");
return;
} }
}
}

② 文件轉二進制流保存到資料庫中是不是比較節省資料庫空間

以二進制方式保存主要是考慮資料庫欄位存儲大小的問題,及方便性,不見得節資料庫空間。

③ 怎麼在mysql中存儲二進制文件

php腳本為例。PHP一般只支持小於2M的文件,假如要存取大於2M的文件,那就要進系統方面的設置了。具體操作如下:


首先創建測試表testtable

CREATETABLEtesttable(idINT(5)NOTNULLAUTO_INCREMENTPRIMARYKEY,filenameCHAR(255),dataLONGBLOB);


將文件存入表中使用如下PHP代碼

<?php
mysql_connect("localhost","root","password");//連接資料庫
mysql_select_db("database");//選定資料庫
$filename=""//這里填入二進制文件名
$data=addslashes(fread(fopen($filename,"r"),filesize($filename)));//打開文件並規范化數據存入變數$data中
$result=mysql_query("INSERTINTOtesttable(filename,data)VALUES('$filename','$data')");//數據插入到資料庫test表中
mysql_close();
?>


從表中取迴文件,使用如下PHP代碼

<?php
if($id){
mysql_connect("localhost","root","password");
mysql_select_db("database");
$filename=""//這里填入二進制文件名
$query="=$filename";
$result=mysql_query($query);
$data=mysql_result($result,0,"data");
?>

④ 如何將txt,doc等文件以二進制形式保存在資料庫中

private int WriteToDB(string strName, string strType, ref byte[] Buffer)
{
int nFileID = 0;

// Create connection
OleDbConnection dbConn = new OleDbConnection(GetConnectionString());

// Create Adapter
OleDbDataAdapter dbAdapt = new OleDbDataAdapter("SELECT * FROM tblFile", dbConn);

// We need this to get an ID back from the database
dbAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey;

// Create and initialize CommandBuilder
OleDbCommandBuilder dbCB = new OleDbCommandBuilder(dbAdapt);

// Open Connection
dbConn.Open();

// New DataSet
DataSet dbSet = new DataSet();

// Populate DataSet with data
dbAdapt.Fill(dbSet, "tblFile");

// Get reference to our table
DataTable dbTable = dbSet.Tables["tblFile"];

// Create new row
DataRow dbRow = dbTable.NewRow();

// Store data in the row
dbRow["FileName"] = strName;
dbRow["FileSize"] = Buffer.Length;
dbRow["ContentType"] = strType;
dbRow["FileData"] = Buffer;

// Add row back to table
dbTable.Rows.Add(dbRow);

// Update data source
dbAdapt.Update(dbSet, "tblFile");

// Get newFileID
if( !dbRow.IsNull("FileID") )
nFileID = (int)dbRow["FileID"];

// Close connection
dbConn.Close();

// Return FileID
return nFileID;
}
寫入庫。
private void ShowTheFile(int FileID)
{
// Define SQL select statement
string SQL = "SELECT FileSize, FileData, ContentType FROM tblFile WHERE FileID = "
+ FileID.ToString();

// Create Connection object
OleDbConnection dbConn = new OleDbConnection(GetConnectionString());

// Create Command Object
OleDbCommand dbComm = new OleDbCommand(SQL, dbConn);

// Open Connection
dbConn.Open();

// Execute command and receive DataReader
OleDbDataReader dbRead = dbComm.ExecuteReader();

// Read row
dbRead.Read();

// Clear Response buffer
Response.Clear();

// Set ContentType to the ContentType of our file
Response.ContentType = (string)dbRead["ContentType"];

// Write data out of database into Output Stream
Response.OutputStream.Write((byte[])dbRead["FileData"], 0, (int)dbRead["FileSize"]);

// Close database connection
dbConn.Close();

// End the page
Response.End();
}
讀出

⑤ 資料庫中存儲二進制文件的查詢效率問題

你所謂的二進制數據文件針對的應該是大對象,一般而言,不會使用到這種存儲方式。不過說查詢效率,在對大對象進行查詢時,像ORACLE,就是給數據文件增加了一個頭,用以查詢時的定位。而對於大數據的文本文件,比如CLOB,它提供了其他的方式讓你來進行數據查詢。而你所說的存在其他的欄位,那麼,如果你存儲的是文本文件而非音頻視頻的話,建議你採用CLOB而非BLOB……另外,就查詢效率而言,沒有什麼固定的方式是最好的,只有針對你的應用,採用最合適的資料庫架構才是最優的。在查詢效率這點上,建議你看看資料庫的簇集,索引,分區/多文件組(oracle是分區,sql server就是多文件組了),這些如果使用恰當的話,可以提高查詢效率……

⑥ 以二進制形式保存文件到資料庫 有什麼優點缺點呢請指教 資料庫是mysql,文件類型是doc和txt

優點,不用單獨管理文件了唄,文件數據都在資料庫里呢。用戶想訪問文件的話,你就可以做一些許可權檢查什麼的,通過才給它取數據。

缺點,資料庫稍微有些壓力唄~~~~資料庫文件會變大~~~

熱點內容
解壓的兩字詞 發布:2025-03-01 03:34:52 瀏覽:534
java框架開源 發布:2025-03-01 03:33:27 瀏覽:47
紆解壓力 發布:2025-03-01 03:20:27 瀏覽:542
可編程式控制制系統工控機維修 發布:2025-03-01 03:11:05 瀏覽:432
php與網頁設計 發布:2025-03-01 03:08:20 瀏覽:428
兒童電腦編程培訓 發布:2025-03-01 03:08:17 瀏覽:673
得到緩存的 發布:2025-03-01 03:08:13 瀏覽:932
計算機中存儲的內容 發布:2025-03-01 03:04:30 瀏覽:725
為什麼蘋果連接appleid伺服器超時 發布:2025-03-01 03:04:29 瀏覽:587
怎麼填寫伺服器埠和ip 發布:2025-03-01 03:00:36 瀏覽:225