ADO.NET 图片读取和保存

操作 SQLite 数据库进行图片存储

// 引用dll
  // System.Data.dll
  // System.Data.SQLite.dll

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.IO;
using System.Windows;

namespace WpfAppSychronize
{
    public partial class MainWindow : Window
    {
        string fileDirectory = AppDomain.CurrentDomain.BaseDirectory + "GuestImages\\";
        public MainWindow()
        {
            InitializeComponent();

            if (!Directory.Exists(fileDirectory))
            {
                Directory.CreateDirectory(fileDirectory);
            }
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            QueryImagesFromFile();
        }

        /// <summary>
        /// 读取图片文件列表
        /// </summary>
        /// <returns></returns>
        public List<Guest> QueryImagesFromFile()
        {
            string diretory = fileDirectory;
            string[] filePaths = Directory.GetFiles(diretory);
            List<Guest> guests = new List<Guest>();

            foreach (var path in filePaths)
            {
                int fileLength = 0;
                byte[] image = null;
                using (FileStream fs = File.OpenRead(path))
                {
                    fileLength = (int)fs.Length;
                    image = new byte[fileLength];
                    fs.Read(image, 0, fileLength);
                }

                Guest guest = new Guest();
                string fileName = path.Substring(path.LastIndexOf("\\") + 1);
                fileName = fileName.Substring(0, fileName.LastIndexOf("."));
                long id = 0;
                long.TryParse(fileName, out id);
                guest.GuestId = id;
                guest.GuestImage = image;
                guests.Add(guest);
            }
            return guests;
        }

        /// <summary>
        /// 从SQL Server数据库中读取图片字段信息
        /// </summary>
        /// <returns></returns>
        public List<Guest> QueryImagesFromSQLDB()
        {
            string sql = @"SELECT [GuestID],[GuestImage] FROM [GuestInfo]";
            List<Guest> guests = new List<Guest>();

            using (SqlDataReader dr = DbHelperSQL.ExecuteReader(sql))
            {
                while (dr.Read())
                {
                    Guest guest = new Guest();
                    guest.GuestId = Convert.ToInt64(dr["GuestID"]);
                    guest.GuestImage = dr["GuestImage"] != DBNull.Value ? (byte[])dr["GuestImage"] : null;
                    guests.Add(guest);
                }
            }
            return guests;
        }

        /// <summary>
        /// 从SQLite数据库中读取图片字段信息
        /// </summary>
        /// <returns></returns>
        public List<Guest> QueryImagesFromSQLiteDB()
        {
            DbHelperSQLite.connectionString = "Data Source=MyDatabase.sqlite;Version=3;";

            string sql = @"SELECT [GuestID],[GuestImage] FROM [GuestInfo]";
            List<Guest> guests = new List<Guest>();

            using (SQLiteDataReader dr = DbHelperSQLite.ExecuteReader(sql))
            {
                while (dr.Read())
                {
                    Guest guest = new Guest();
                    guest.GuestId = Convert.ToInt64(dr["GuestID"]);
                    guest.GuestImage = dr["GuestImage"] != DBNull.Value ? (byte[])dr["GuestImage"] : null;
                    guests.Add(guest);
                }
            }
            return guests;
        }

        /// <summary>
        /// 把图片字节流保存为文件
        /// </summary>
        /// <param name="guests"></param>
        public void SaveImagesToFile(List<Guest> guests)
        {
            foreach (var Guest in guests)
            {
                byte[] MyData = Guest.GuestImage;
                string imagePath = fileDirectory + Guest.GuestId.ToString() + @".jpg";

                using (FileStream fs = new FileStream(imagePath, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    fs.Write(MyData, 0, MyData.Length);
                    fs.Close();
                }
            }
        }

        /// <summary>
        /// 把图片字节流保存到SQL Server数据库
        /// </summary>
        /// <param name="guests"></param>
        public void SaveImagesToSQLDB(List<Guest> guests)
        {
            foreach (var Guest in guests)
            {
                string sql = @"INSERT INTO [GuestInfo] ([GuestID],[GuestImage]) VALUES(@GuestID, @GuestImage)";

                SqlParameter[] param = new SqlParameter[2];
                param[0] = new SqlParameter("@GuestID", SqlDbType.BigInt);
                param[0].Value = Guest.GuestId;
                param[1] = new SqlParameter("@GuestImage", SqlDbType.Image);
                param[1].Value = Guest.GuestImage != null ? Guest.GuestImage : new byte[] { };

                DbHelperSQL.ExecuteSql(sql, param);
            }
        }

        /// <summary>
        /// 把图片字节流保存到SQLite数据库
        /// </summary>
        /// <param name="guests"></param>
        public void SaveImagesToSQLiteDB(List<Guest> guests)
        {
            DbHelperSQLite.connectionString = "Data Source=MyDatabase.sqlite;Version=3;";

            foreach (var Guest in guests)
            {
                string sql = @"INSERT INTO [GuestInfo]([GuestID],[GuestImage]) VALUES(@GuestID, @GuestImage)";

                SQLiteParameter[] param = new SQLiteParameter[2];

                // 注意：这里赋值必须要用DbType.xxx，否则赋值不上
                param[0] = new SQLiteParameter("@GuestID", DbType.Int64);
                param[0].Value = Guest.GuestId;
                param[1] = new SQLiteParameter("@GuestImage", DbType.Binary);
                param[1].Value = Guest.GuestImage != null ? Guest.GuestImage : new byte[] { };

                DbHelperSQLite.ExecuteSql(sql, param);
            }
        }
    }

    public class Guest
    {
        public long GuestId { get; set; }
        public byte[] GuestImage { get; set; }
    }

    public abstract class DbHelperSQL
    {
        //数据库连接字符串
        public static string connectionString = "";

        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
    }

    public class DbHelperSQLite
    {
        //数据库连接字符串
        public static string connectionString = "";

        /// <summary>
        /// 执行查询语句，返回SQLiteDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SQLiteDataReader</returns>
        public static SQLiteDataReader ExecuteReader(string strSQL)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
            try
            {
                connection.Open();
                SQLiteDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SQLite.SQLiteException e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SQLiteParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}

批量保存图片到数据库：

using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;

namespace DataImport
{
    public partial class Form1 : Form
    {
        public List<string> _photoFilePathList = new List<string>();
        string _connStr = "Data Source=192.168.10.109;Initial Catalog=HT_ACCESS;User ID=sa;Password=123456";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            GetFiles(textBox1.Text);

            int i = 0;
            foreach (var file in _photoFilePathList)
            {
                string err = "";
                try
                {
                    FileStream fs = new FileStream(file, System.IO.FileMode.Open, FileAccess.Read);
                    BinaryReader binaryReader = new BinaryReader(fs);

                    byte[] imgData = binaryReader.ReadBytes((int)fs.Length);

                    VIEW_M_PHOTO photo = new VIEW_M_PHOTO();
                    photo.EMPLOYEENO = "90000" + i.ToString();
                    photo.PHOTO = imgData;
                    photo.FILEEXT = "10000" + i.ToString();

                    //保存照片
                    AddPhoto(photo);

                    i++;
                }
                catch (Exception ex)
                {
                    err = ex.Message.ToString();
                    continue;
                }
            }
        }
        
        public bool AddPhoto(VIEW_M_PHOTO model)
        {
            using (SqlConnection connection = new SqlConnection(_connStr))
            {
                SqlCommand command = new SqlCommand(@"INSERT INTO [HT_ACCESS].[dbo].[HT_PHOTO] (EMP_NO,PHOTO_CONTENT,RECORD_COUNTER)
Values(@EMP_NO, @PHOTO_CONTENT, @RECORD_COUNTER)", connection);

                command.Parameters.Add("@EMP_NO",
                   SqlDbType.NVarChar, 20).Value = model.EMPLOYEENO;
                command.Parameters.Add("@PHOTO_CONTENT",
                    SqlDbType.Image, model.PHOTO.Length).Value = model.PHOTO;
                command.Parameters.Add("@RECORD_COUNTER",
                    SqlDbType.NVarChar, 30).Value = model.FILEEXT;

                connection.Open();
                int rows = command.ExecuteNonQuery();

                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }        
        private void btnOpenDirectory_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择文件路径";
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                string foldPath = dialog.SelectedPath;
                textBox1.Text = foldPath;
            }
        }

        public void GetFiles(string str)
        {
            DirectoryInfo parentFolder = new DirectoryInfo(str);

            //删除子文件夹
            DirectoryInfo[] childFolders = parentFolder.GetDirectories();
            foreach (DirectoryInfo dir in childFolders)
            {
                try
                {
                    string dirName = dir.Name;
                    //if (dirName.Contains("obj") || dirName.Contains("bin"))
                    //{
                    //    Directory.Delete(dir.FullName, true);
                    //}
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            //删除当前文件夹内文件
            FileInfo[] files = parentFolder.GetFiles();
            foreach (FileInfo file in files)
            {
                //string fileName = file.FullName.Substring((file.FullName.LastIndexOf("\\") + 1), file.FullName.Length - file.FullName.LastIndexOf("\\") - 1);
                string fileName = file.Name;
                try
                {
                    //if (fileName.Contains("cs"))
                    //{
                    //    //File.Delete(file.FullName);
                    //    string path = file.FullName;
                    //    //Path.ChangeExtension(path, "txt");
                    //    File.Move(path, Path.ChangeExtension(path, "txt"));
                    //}

                    _photoFilePathList.Add(file.FullName);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            //递归搜索子文件夹内文件
            foreach (DirectoryInfo childFolder in parentFolder.GetDirectories())
            {
                GetFiles(childFolder.FullName);
            }
        }
    }
}