sql server表结构如下：
create table DataTable ( Id int identity(1,1) not null primary key, FileName nvarchar(100) not null, FilePath nvarchar(200) not null, Data varbinary(MAX) )
主要方法：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Drawing;
namespace DataAccess
{
public class PubFunction
{
/// <summary>
/// 把文件存入数据库
/// </summary>
/// <param name="filePaths">文件路径（含文件名）</param>
/// <returns>存入是否成功</returns>
public static bool StoreFiles(string[] filePaths)
{
try
{
for (int i = 0; i < filePaths.Length; i++)
{
string filePath = filePaths[i];
string fileName = filePath.Substring(filePath.LastIndexOf("\\") + 1);
using (SqlConnection connection = new SqlConnection(PubVariant.ConnectionString))
{
connection.Open();
FileStream pFileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
byte[] bytes = new byte[pFileStream.Length];
pFileStream.Read(bytes, 0, (int)pFileStream.Length);
string strSql = "insert into DataTable(FileName,FilePath,Data) values(@FileName,@FilePath,@Data)";
using (SqlCommand cmd = new SqlCommand(strSql, connection))
{
cmd.Parameters.Add("@FileName", SqlDbType.Text);
cmd.Parameters.Add("@FilePath", SqlDbType.Text);
cmd.Parameters.Add("@Data", SqlDbType.Binary);
cmd.Parameters["@FileName"].Value = fileName;
cmd.Parameters["@FilePath"].Value = filePath;
cmd.Parameters["@Data"].Value = bytes;
cmd.ExecuteNonQuery();
}
}
}
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
/// <summary>
/// 将数据库中数据写入文件
/// </summary>
/// <param name="fileName">用于查找数据的文件名</param>
/// <param name="destFilePath">目标文件路径（含文件名）</param>
/// <returns>写入是否成功</returns>
public static bool WriteFromDBtoFile(string fileName, string destFilePath)
{
FileStream pFileStream = null;
try
{
using (SqlConnection connection = new SqlConnection(PubVariant.ConnectionString))
{
connection.Open();
string strSql0 = "select Data from DataTable where FileName = '{0}'";
string strSql1 = String.Format(strSql0, fileName);
SqlCommand cmd = new SqlCommand(strSql1, connection);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
byte[] bytes = (byte[])dr[0];
pFileStream = new FileStream(destFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
pFileStream.Write(bytes, 0, bytes.Length);
}
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally
{
if (pFileStream != null)
{
pFileStream.Close();
}
}
}
public static DataTable GetDataFromSql(string strSql)
{
using (SqlConnection connection = new SqlConnection(PubVariant.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(strSql, connection))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
using (DataSet ds = new DataSet())
{
da.Fill(ds);
DataTable dt = ds.Tables[0];
return dt;
}
}
}
}
}
}
}
具体实现：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
namespace DataAccess
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void textBox1_MouseClick(object sender, MouseEventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = true;
ofd.InitialDirectory = "F:\\";
ofd.Filter = "All files(*.*)|*.*";
ofd.Title = "选择文件";
ofd.ShowDialog();
PubVariant.filePaths = ofd.FileNames;
listBox1.DataSource = PubVariant.filePaths;
}
private void btnSave_Click(object sender, EventArgs e)
{
if(PubFunction.StoreFiles(PubVariant.filePaths))
{
MessageBox.Show("Succeed!");
}
}
private void textBox2_MouseClick(object sender, MouseEventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "All files(*.*)|*.*";
sfd.Title = "保存文件";
sfd.InitialDirectory = "F:\\";
sfd.FileName = comboBox1.Text;
sfd.ShowDialog();
textBox2.Text = sfd.FileName;
PubVariant.saveFilePath = sfd.FileName;
}
private void button1_Click(object sender, EventArgs e)
{
string fileName = comboBox1.Text;
if(PubFunction.WriteFromDBtoFile(fileName,PubVariant.saveFilePath))
{
MessageBox.Show("Succeed!");
}
}
private void comboBox1_MouseClick(object sender, MouseEventArgs e)
{
string strSql = "select FileName from DataTable";
DataTable dt = PubFunction.GetDataFromSql(strSql);
for (int i = 0; i < dt.Rows.Count; i++)
{
comboBox1.Items.Add(dt.Rows[i][0].ToString());
}
}
}
}
全局变量：
public class PubVariant
{
public static string[] filePaths;
public static string saveFilePath;
public static string connectionString = "server=eagle;database=Test;user id = sa;password=123456";
public static string ConnectionString
{
get { return connectionString; }
set { connectionString = value; }
}
}
实现效果如图：
