C#各种扩展名文件存入sql server数据库及读取到本地文件

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; }
        }
    }

实现效果如图：