利用FreeSQL和MiniExcel实现数据库数据的导入导出

数据的导出：

private void OnExport()
{
    try
    {
        FolderBrowserDialog dialog = new FolderBrowserDialog();
        if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            var path = dialog.SelectedPath;

            SaveToExcel<WordInfo>(path);
            SaveToExcel<PracticeInfo>(path);

            MessageBox.Show("导出成功");
        }
    }
    catch (Exception ex)
    {
        LogHelper.Error(ex);
        MessageBox.Show("操作异常：" + ex.Message);
    }
}

private void SaveToExcel<T>(string path) where T : class, IBaseInfo
{
    var items = DbHelper.SelectAll<T>();
    MiniExcel.SaveAs(Path.Combine(path, typeof(T).Name + ".xlsx"), items);
}

 

数据的导入：

private void OnImport()
{
    try
    {
        FolderBrowserDialog dialog = new FolderBrowserDialog();
        if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            var path = dialog.SelectedPath;

            LoadFromExcel<WordInfo>(path);
            LoadFromExcel<PracticeInfo>(path);
            OnRefresh();

            MessageBox.Show("导入成功");
        }
    }
    catch (Exception ex)
    {
        LogHelper.Error(ex);
        MessageBox.Show("操作异常：" + ex.Message);
    }
}

private void LoadFromExcel<T>(string path) where T : class, IBaseInfo, new()
{
    var fileName = Path.Combine(path, typeof(T).Name + ".xlsx");
    if (File.Exists(fileName))
    {
        var items = MiniExcel.Query<T>(fileName);
        DbHelper.AddDataInfos(items);
    }
}

 

代码中用到的DbHelper是我自己封装的一个类，对FreeSQL的接口做了简单的封装。

using FreeSql.DataAnnotations;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace CgdataBase
{
    public class DataHelper : IDisposable
    {
        private static DataHelper _instance;
        public static DataHelper Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new DataHelper();
                }

                return _instance;
            }
        }

        private IFreeSql freeSql;
        public static IFreeSql DB => Instance.freeSql;

        public void Initialize(FreeSql.DataType type, string connectionString)
        {
            freeSql = new FreeSql.FreeSqlBuilder()
                .UseConnectionString(type, connectionString)
                .UseLazyLoading(true)
                .UseMonitorCommand(cmd => Debug.WriteLine(cmd.CommandText))
                .UseAutoSyncStructure(true)
                .Build();

            //freeSql.UseJsonMap();
        }

        public int AddDataInfo<T>(T info) where T : class, IBaseInfo
        {
            return (int)freeSql.Insert<T>().AppendData(info).ExecuteIdentity();
        }

        public void AddDataInfos<T>(IEnumerable<T> items) where T : class, IBaseInfo
        {
            freeSql.Insert<T>().AppendData(items).ExecuteAffrows();
        }

        public void UpdateDataInfo<T>(T info) where T : class, IBaseInfo
        {
            freeSql.Update<T>()
                .SetSource(info)
                .ExecuteAffrows();
        }

        public void UpdateDataInfos<T>(IEnumerable<T> items) where T : class, IBaseInfo
        {
            freeSql.Update<T>()
                .SetSource(items)
                .ExecuteAffrows();
        }

        public void DeleteDataInfo<T>(T info) where T : class, IBaseInfo
        {
            freeSql.Delete<T>()
                .Where(s => s.ID == info.ID)
                .ExecuteAffrows();
        }

        public void DeleteDataInfos<T>(IEnumerable<T> items) where T : class, IBaseInfo
        {
            var ids = items.Select(s => s.ID).ToArray();
            freeSql.Delete<T>()
                .Where(s => ids.Contains(s.ID))
                .ExecuteAffrows();
        }

        public void DeleteDataInfos<T>(Expression<Func<T, bool>> exp) where T : class, IBaseInfo
        {
            freeSql.Delete<T>().Where(exp).ExecuteAffrows();
        }

        public List<T> SelectAll<T>() where T : class, IBaseInfo
        {
            return freeSql.Select<T>().ToList();
        }

        public List<T> SelectAll<T, TKey>(Expression<Func<T, TKey>> column, bool descending = false) where T : class, IBaseInfo
        {
            if (descending == false)
            {
                return freeSql.Select<T>().OrderBy(column).ToList();
            }
            else
            {
                return freeSql.Select<T>().OrderByDescending(column).ToList();
            }
        }

        public List<T> Select<T>(Expression<Func<T, bool>> exp) where T : class, IBaseInfo
        {
            return freeSql.Select<T>().Where(exp).ToList();
        }

        public List<T> Select<T, TKey>(Expression<Func<T, bool>> exp, Expression<Func<T, TKey>> column, bool descending = false) where T : class, IBaseInfo
        {
            if (descending == false)
            {
                return freeSql.Select<T>().Where(exp).OrderBy(column).ToList();
            }
            else
            {
                return freeSql.Select<T>().Where(exp).OrderByDescending(column).ToList();
            }
        }

        public T SelectByID<T>(int id) where T : class, IBaseInfo
        {
            return freeSql.Select<T>().Where(s => s.ID.Equals(id)).First();
        }

        public List<T> SelectBySql<T>(string sqlText) where T : class, IBaseInfo
        {
            return freeSql.Select<T>().WithSql(sqlText).ToList();
        }

        public void Dispose()
        {
            freeSql?.Dispose();
        }

        public bool Any<T>(Expression<Func<T, bool>> exp) where T : class, IBaseInfo
        {
            return freeSql.Select<T>().Where(exp).Any();
        }
    }

    public interface IBaseInfo
    {
        int ID { get; set; }
    }
}