Blob 的相關用法用很多， 如以下的例子 是用 java 存取 mysql 為例
http://caterpillar.onlyfun.net/Gossip/HibernateGossip/BlobClob.html
在這裡，將使用 C# 將圖片儲存在 sqlite 上
這裡參考了 這一篇文章 http://sqlite.phxsoftware.com/forums/p/324/1329.aspx#1329
我的範例大致描述一下
此範例有使用 System.Data.SQLite; library , 如何使用 見
C sharp or .Net 使用sqlite 設定
有四個物件在視窗上
名稱 函式 說明
---------------------------------------------------------
CreateDB CreateDB_Click 建立資料庫
SaveToDB Save_Click 將圖片儲存在資料庫
LoadImageFromDB Load_Click 將圖片重資料庫讀出
imageExample null 顯示圖片的物件
顯示畫面如下
若 資料庫 及圖片以儲存 按下 LoadImageFromDB 就會重資料庫讀取檔案並顯示
CODE 如下
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SQLite;
using System.IO;
using System.Data;
namespace SQLiteBlobTest
{
/// Interaction logic for Window1.xaml
public partial class Window1 : Window
{
/// Set Database Root
string DataBaseRoot = Directory.GetCurrentDirectory() + "\\TestBlobDB.db";
public Window1()
{
InitializeComponent();
}
/**
* @name CreateDataBase
*
* Create Database "TestBlobDB.db",
*
*
* Must import System.Data.SQLite or it not work
* @param DataBaseRoot [in] the data base root
*
*/
public static void CreateDataBase(string DataBaseRoot)
{
/// Creat Database
SQLiteConnection.CreateFile(DataBaseRoot);
/// Open Database and set password
SQLiteConnection cnn = new SQLiteConnection("Data Source=" + DataBaseRoot);
/// Open connect
cnn.Open();
/// Close connect
cnn.Close();
}
/**
* @name CreateBlobTable
*
* Create table whcih hava two column
* Name , type is nvarchar(40) , to record the name
* Image , type is BLOB , to record the image with byte[]
*
* Must import System.Data.SQLite or not work
* @param DataBaseRoot [in] the data base root
*/
public void CreateBlobTable(string DataBaseRoot)
{
/// Open Database
SQLiteConnection cnn = new SQLiteConnection("Data Source=" + DataBaseRoot );
/// Open connect
cnn.Open();
/// Define SQLite Command object
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cnn;
/// Set commend to create table
cmd.CommandText = "CREATE TABLE [BlobTable] (Name nvarchar(40), Image BLOB)";
/// Execute the command, iF have exist show error message
cmd.ExecuteNonQuery();
/// Close connect
cnn.Close();
}
/**
* @name CheckDataBaseExist
*
* Check database exist or not
*
* @param DataBaseRoot [in] the data base root
* @return True if exist, otherwise false
*
*/
public Boolean CheckDataBaseExist(string DataBaseRoot)
{
/// Set return value
Boolean ExistOrNot;
/// Check file is exits
if (File.Exists(DataBaseRoot))
{
ExistOrNot = true;
}
else
{
ExistOrNot = false;
}
return ExistOrNot;
}
/**
* @name CreateDB_Click
*
* When click this button , it will create database and table
*
*
*/
private void CreateDB_Click(object sender, RoutedEventArgs e)
{
/// Set Database Root
string DataBaseRoot = this.DataBaseRoot;
/// Flag id database exist
bool FlagIsDBExist = CheckDataBaseExist(DataBaseRoot);
if (FlagIsDBExist)
{
MessageBox.Show("The Database is already exist");
}
else
{
/// Create database and table "BlobTable"
CreateDataBase(DataBaseRoot);
CreateBlobTable(DataBaseRoot);
}
}
/**
* @name Save_Click
*
* When click this button , it will save the image from cumputer to the database
*
*
*/
private void Save_Click(object sender, RoutedEventArgs e)
{
/// Set Database Root
string DataBaseRoot = this.DataBaseRoot;
/// Open Database
SQLiteConnection cnn = new SQLiteConnection("Data Source=" + DataBaseRoot);
/// Open connect
cnn.Open();
/// Define SQLite Command object
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cnn;
/// Open FIleStream to transform image to byte array "MyData"
/// @"C:\20090908002chiachunchuang.jpg" is your image root
FileStream fs = new FileStream(@"C:\20090908002chiachunchuang.jpg", FileMode.OpenOrCreate, FileAccess.Read);
byte[] MyData = new byte[fs.Length];
fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));
fs.Close();
/// Set commend to save the date to database
cmd.CommandText = "INSERT INTO BlobTable VALUES( 'Test', @blobdata)";
/// When using blob type using the commend to let db save blob type
cmd.Parameters.AddWithValue("@blobdata", MyData);
/// Execute the command
cmd.ExecuteNonQuery();
/// Close connect
cnn.Close();
}
/**
* @name Load_Click
*
* When click this button , it get the image from database and show on windows
*
*
*/
private void Load_Click(object sender, RoutedEventArgs e)
{
/// Declare the byte[] to record the image imformation
byte[] MyData=null;
/// Set Database Root
string DataBaseRoot = this.DataBaseRoot;
/// Open Database
SQLiteConnection cnn = new SQLiteConnection("Data Source=" + DataBaseRoot);
/// Open connect
cnn.Open();
/// Define SQLite Command object
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cnn;
/// Set commend to get value
cmd.CommandText = "Select * from BlobTable";
/// CommandBehavior -> using System.Data;
/// Read all information
using (SQLiteDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
/// Get the value from db
MyData = (Byte[])dr.GetValue(1);
}
}
cnn.Close();
/// Put the image byte[] to MemoryStream
MemoryStream ms = new MemoryStream(MyData);
/// Declare BitmapImage Bi
BitmapImage Bi = new BitmapImage();
Bi.BeginInit();
/// Set Bi source from MemoryStream
Bi.StreamSource = ms;
Bi.EndInit();
/// Set the imageExample's source and it will show on windows
imageExample.Source = Bi;
}
}
}
沒有留言:
張貼留言