C# 自动批量导入图片到数据库中

// 引用dll
// System.Data.dll
// System.Data.SQLite.dllusing 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 = ;
byte[] image = null;
using (FileStream fs = File.OpenRead(path))
{
fileLength = (int)fs.Length;
image = new byte[fileLength];
fs.Read(image, , fileLength);
} Guest guest = new Guest();
string fileName = path.Substring(path.LastIndexOf("\\") + );
fileName = fileName.Substring(, fileName.LastIndexOf("."));
long id = ;
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, , 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[];
param[] = new SqlParameter("@GuestID", SqlDbType.BigInt);
param[].Value = Guest.GuestId;
param[] = new SqlParameter("@GuestImage", SqlDbType.Image);
param[].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[]; // 注意：这里赋值必须要用DbType.xxx，否则赋值不上
param[] = new SQLiteParameter("@GuestID", DbType.Int64);
param[].Value = Guest.GuestId;
param[] = new SQLiteParameter("@GuestImage", DbType.Binary);
param[].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 = ;
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 = "" + i.ToString();
photo.PHOTO = imgData;
photo.FILEEXT = "" + 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, ).Value = model.EMPLOYEENO;
command.Parameters.Add("@PHOTO_CONTENT",
SqlDbType.Image, model.PHOTO.Length).Value = model.PHOTO;
command.Parameters.Add("@RECORD_COUNTER",
SqlDbType.NVarChar, ).Value = model.FILEEXT; connection.Open();
int rows = command.ExecuteNonQuery(); if (rows > )
{
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);
}
}
}
}