SQLite数据库存储文件, 通过二进制方式

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace DataBaseInsertFormBinData
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Demo demo1 = new Demo();
            demo1.DoMain();
        }
    }

    public  class Demo
    {
        public void DoMain()
        {
            //创建DB文件
            if (File.Exists("test.db3"))
            {
                File.Delete("test.db3");
            }
            else
            {
                SQLiteConnection.CreateFile("MyDatabase.sqlite");
            }

            string picturePath = @"D:\codeDemo\DataBaseInsertFormBinData\DataBaseInsertFormBinData\bin\Debug\1.jpg";
            byte[] photoPic = wordConvertByte(picturePath);

            using (var connection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"))
            using (var command = new SQLiteCommand("CREATE TABLE PHOTOS(ID INTEGER PRIMARY KEY AUTOINCREMENT, PHOTO BLOB)", connection))
            {
                connection.Open();
                command.ExecuteNonQuery();

                byte[] photo = photoPic;

                command.CommandText = "INSERT INTO PHOTOS (PHOTO) VALUES (@photo)";
                command.Parameters.Add("@photo", DbType.Binary).Value = photo;
                command.ExecuteNonQuery();

                command.CommandText = "SELECT PHOTO FROM PHOTOS WHERE ID = 1";
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        byte[] buffer = GetBytes(reader);
                        string picturePath2 = @"D:\codeDemo\DataBaseInsertFormBinData\DataBaseInsertFormBinData\bin\Debug\2.jpg";
                        this.getFile(buffer, picturePath2);
                        MessageBox.Show("文件已经生成!");
                    }

                }
            }
        }

        static byte[] GetBytes(SQLiteDataReader reader)
        {
            //一块的数据
            const int CHUNK_SIZE = 2 * 1024;
            byte[] buffer = new byte[CHUNK_SIZE];
            long bytesRead;
            long fieldOffset = 0;
            using (MemoryStream stream = new MemoryStream())
            {
                while ((bytesRead = reader.GetBytes(0, fieldOffset, buffer, 0, buffer.Length)) > 0)
                {
                    stream.Write(buffer, 0, (int)bytesRead);
                    fieldOffset += bytesRead;
                } 
                return stream.ToArray();
            }
        }

        /// <summary>
        /// word文件转换二进制数据(用于保存数据库)
        /// </summary>
        /// <param name="wordPath">word文件路径</param>
        /// <returns>二进制</returns>
        private byte[] wordConvertByte(string wordPath)
        {
            byte[] bytContent = null;
            System.IO.FileStream fs = null;
            System.IO.BinaryReader br = null;
            try
            {
                fs = new FileStream(wordPath, System.IO.FileMode.Open);
            }
            catch
            {
            }
            br = new BinaryReader((Stream)fs);
            bytContent = br.ReadBytes((Int32)fs.Length);


            return bytContent;
        }

        //将二进制数据转化为指定位置文件
        private void getFile(byte[] content, string filePath)
        {
            string fileName = filePath;
            if (System.IO.File.Exists(fileName))
            {
                System.IO.File.Delete(fileName);
            }
            //FileStream sw = new FileStream(@fileName, FileMode.Create);
            //StreamWriter fs = new StreamWriter(sw, Encoding.UTF8);
            //fs.Write(entity.Content);
            System.IO.FileStream fs = new System.IO.FileStream(fileName, System.IO.FileMode.Create);
            fs.Write(content, 0, content.Length);
            fs.Flush();
            fs.Close();
            fileName = System.IO.Path.Combine(Application.StartupPath, fileName);
        }
    }
}

 

转载于:https://www.cnblogs.com/leiliu-lucifer/p/5672475.html