C#實現SQL Server中存取圖片、文件
/*
在master中创建表test
CREATE TABLE test
(
id INT IDENTITY(1, 1) ,
files IMAGE
)
*/
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
namespace WindowsApplication11
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Button1_Click(object sender, EventArgs e)
{
// Data Source=.;Initial Catalog=ycmis1;Integrated Security=True
insertdataintodatabase(@"D:\123.txt", @"Data Source=.;Initial Catalog=master;Integrated Security=True");
}
//將資料寫進資料庫
//參數:
//filepath 檔路徑
//connectionstring 連接字串
public void insertdataintodatabase(string filepath, string connectionstring)
{
if (File.Exists(filepath) == false)
{
MessageBox.Show("無法讀取文件! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//創建檔物件以打開的形式讀取檔
System.IO.FileStream sfilestream = new System.IO.FileStream(filepath, System.IO.FileMode.Open);
//分配陣列大小
byte[] bfile = new byte[sfilestream.Length];
//將檔內容讀進陣列
sfilestream.Read(bfile, 0, (int)sfilestream.Length);
//關閉檔物件
sfilestream.Close();
//創建連接
SqlConnection conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand com = conn.CreateCommand();
com.CommandText = "insert into test values (@f )";
// com.CommandText = "update 表 set [圖片]=@f where id= '0001 ' "; //更新的
com.CommandType = CommandType.Text;
SqlParameter sp = new SqlParameter("@f ", SqlDbType.Image, bfile.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, bfile);
com.Parameters.Add(sp);
com.ExecuteNonQuery();
conn.Close();
}
//從資料庫中讀取資料
//參數:
//filepath 檔路徑
//connectionstring 連接字串
public void loaddatafromdatabase(string filepath, string connectionstring)
{
//判斷檔是否已存在.
if (File.Exists(filepath) != true)
{
//如存在則刪除
File.Delete(filepath);
}
//創建連接
SqlConnection conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand com = conn.CreateCommand();
com.CommandText = "select top 1 files from test ";
com.CommandType = CommandType.Text;
//用datareader讀取數據
SqlDataReader sr = com.ExecuteReader();
sr.Read();
//判斷是否有記錄
if (sr.HasRows == false)
{
sr.Close();
conn.Close();
return;
}
//分配陣列大小
byte[] bfile = new byte[Convert.ToInt32(sr.GetBytes(0, 0, null, 0, Int32.MaxValue))];
//將資料讀進陣列
sr.GetBytes(0, 0, bfile, 0, bfile.Length);
//關閉datareader
sr.Close();
//創建檔物件以創建檔的形式打開檔
System.IO.FileStream sfilestream = new System.IO.FileStream(filepath, System.IO.FileMode.Create);
//將陣列的內容寫進檔
sfilestream.Write(bfile, 0, bfile.Length);
//關閉文件
sfilestream.Close();
}
private void Button2_Click(object sender, EventArgs e)
{
loaddatafromdatabase(@"D:\1234.txt", @"Data Source=.;Initial Catalog=master;Integrated Security=True");
}
}
}
在master中创建表test
CREATE TABLE test
(
id INT IDENTITY(1, 1) ,
files IMAGE
)
*/
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
namespace WindowsApplication11
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Button1_Click(object sender, EventArgs e)
{
// Data Source=.;Initial Catalog=ycmis1;Integrated Security=True
insertdataintodatabase(@"D:\123.txt", @"Data Source=.;Initial Catalog=master;Integrated Security=True");
}
//將資料寫進資料庫
//參數:
//filepath 檔路徑
//connectionstring 連接字串
public void insertdataintodatabase(string filepath, string connectionstring)
{
if (File.Exists(filepath) == false)
{
MessageBox.Show("無法讀取文件! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//創建檔物件以打開的形式讀取檔
System.IO.FileStream sfilestream = new System.IO.FileStream(filepath, System.IO.FileMode.Open);
//分配陣列大小
byte[] bfile = new byte[sfilestream.Length];
//將檔內容讀進陣列
sfilestream.Read(bfile, 0, (int)sfilestream.Length);
//關閉檔物件
sfilestream.Close();
//創建連接
SqlConnection conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand com = conn.CreateCommand();
com.CommandText = "insert into test values (@f )";
// com.CommandText = "update 表 set [圖片]=@f where id= '0001 ' "; //更新的
com.CommandType = CommandType.Text;
SqlParameter sp = new SqlParameter("@f ", SqlDbType.Image, bfile.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, bfile);
com.Parameters.Add(sp);
com.ExecuteNonQuery();
conn.Close();
}
//從資料庫中讀取資料
//參數:
//filepath 檔路徑
//connectionstring 連接字串
public void loaddatafromdatabase(string filepath, string connectionstring)
{
//判斷檔是否已存在.
if (File.Exists(filepath) != true)
{
//如存在則刪除
File.Delete(filepath);
}
//創建連接
SqlConnection conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand com = conn.CreateCommand();
com.CommandText = "select top 1 files from test ";
com.CommandType = CommandType.Text;
//用datareader讀取數據
SqlDataReader sr = com.ExecuteReader();
sr.Read();
//判斷是否有記錄
if (sr.HasRows == false)
{
sr.Close();
conn.Close();
return;
}
//分配陣列大小
byte[] bfile = new byte[Convert.ToInt32(sr.GetBytes(0, 0, null, 0, Int32.MaxValue))];
//將資料讀進陣列
sr.GetBytes(0, 0, bfile, 0, bfile.Length);
//關閉datareader
sr.Close();
//創建檔物件以創建檔的形式打開檔
System.IO.FileStream sfilestream = new System.IO.FileStream(filepath, System.IO.FileMode.Create);
//將陣列的內容寫進檔
sfilestream.Write(bfile, 0, bfile.Length);
//關閉文件
sfilestream.Close();
}
private void Button2_Click(object sender, EventArgs e)
{
loaddatafromdatabase(@"D:\1234.txt", @"Data Source=.;Initial Catalog=master;Integrated Security=True");
}
}
}