[大数据]C# 从数据库读取数据 导出到CSV

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSVtoDataBase
{
    public class SqlSugarHelper
    {
        public static string ConnectionString = string.Empty; //必填, 数据库连接字符串
        public static SqlSugarClient db
        {
            get => new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = ConnectionString,
                DbType = DbType.Sqlite,         //必填, 数据库类型
                IsAutoCloseConnection = true,       //默认false, 时候知道关闭数据库连接, 设置为true无需使用using或者Close操作
                //InitKeyType = InitKeyType.Attribute    //默认SystemTable, codefist需要使用Attribute
                InitKeyType = InitKeyType.SystemTable
            });
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSVtoDataBase
{
    public class SqlSugarService
    {
        /// <summary>
        /// 设置连接字符串
        /// </summary>
        /// <param name="ConnectionStr"></param>
        public static void SetConnectionStr(string ConnectionStr)
        {
            SqlSugarHelper.ConnectionString = ConnectionStr;
        }
    }
}

using Entitys;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSVtoDataBase
{
    public interface IResponsitory
    {
        List<ActualData> GetActualDatas();

        DataTable GetDataTableActualDatas();
        int InsertActualData(List<ActualData> actualDatas);

        DataTable GetDataTableSysAdmin();
        int InsertSysAdmin(List<Sysadmins> sysadmins);

    }
}

using Entitys;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSVtoDataBase
{
    public class SugarResponsitory: IResponsitory
    {
        private SqlSugarClient _db = SqlSugarHelper.db;
        public SqlSugarClient Db { get => _db; set => _db = value; }

        public List<ActualData> GetActualDatas()
        {
            return _db.Queryable<ActualData>().OrderBy(x => x.Id,OrderByType.Desc).Take(50).ToList();
        }

        public DataTable GetDataTableActualDatas()
        {
            return _db.Queryable<ActualData>()
                .OrderBy(x => x.Id, OrderByType.Desc)
                .Take(50)
                .Select(x => new 
                { 
                    InsertTime = x.InsertTime,
                    VarName = x.VarName,
                    Remark = x.Remark
                }).ToDataTable();
        }

        public int InsertActualData(List<ActualData> actualDatas)
        {
            int count = _db.Insertable(actualDatas).ExecuteCommand();
            return count;
        }

        public DataTable GetDataTableSysAdmin()
        {
            return _db.Queryable<Sysadmins>()
                .Select(x => new
                {
                    UserManage = x.UserManage,
                    LoginName = x.LoginName,
                    Report = x.Report,
                    LoginPwd = x.LoginPwd,
                    SysLog = x.SysLog,
                    SysSet = x.SysSet,
                    Trend = x.Trend,
                    HandCtrl = x.HandCtrl,
                    AutoCtrl = x.AutoCtrl,
                }).ToDataTable();
        }

        public int InsertSysAdmin(List<Sysadmins> sysadmins)
        {
            return _db.Insertable(sysadmins).ExecuteCommand();
        }
    }
}

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSVtoDataBase
{
    public class CSVHelper
    {
        /// <summary>
        /// 写入CSV
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="dt">要写入的datatable</param>
        public void WriteCSV(string fileName, DataTable dt)
        {
            FileStream fs;
            StreamWriter sw;
            string data = null;

            //判断文件是否存在,存在就不再次写入列名
            if (!File.Exists(fileName))
            {
                fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
                sw = new StreamWriter(fs, Encoding.UTF8);

                //写出列名称
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    data += dt.Columns[i].ColumnName.ToString();
                    if (i < dt.Columns.Count - 1)
                    {
                        data += ",";//中间用，隔开
                    }
                }
                sw.WriteLine(data);
            }
            else
            {
                fs = new FileStream(fileName, FileMode.Append, FileAccess.Write);
                sw = new StreamWriter(fs, Encoding.UTF8);
            }

            //写出各行数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                data = null;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    data += dt.Rows[i][j].ToString();
                    if (j < dt.Columns.Count - 1)
                    {
                        data += ",";//中间用，隔开
                    }
                }
                sw.WriteLine(data);
            }
            sw.Close();
            fs.Close();
        }



        /// <summary>
        /// 读取CSV文件
        /// </summary>
        /// <param name="fileName">文件路径</param>
        public DataTable ReadCSV(string fileName)
        {
            DataTable dt = new DataTable();
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            StreamReader sr = new StreamReader(fs, Encoding.UTF8);

            //记录每次读取的一行记录
            string strLine = null;
            //记录每行记录中的各字段内容
            string[] arrayLine = null;
            //分隔符
            string[] separators = { "," };
            //判断，若是第一次，建立表头
            bool isFirst = true;

            //列的个数
            int dtColumns = 0;

            //逐行读取CSV文件
            while ((strLine = sr.ReadLine()) != null)
            {
                strLine = strLine.Trim();//去除头尾空格
                arrayLine = strLine.Split(separators, StringSplitOptions.RemoveEmptyEntries);//分隔字符串，返回数组

                if (isFirst)  //建立表头
                {
                    dtColumns = arrayLine.Length;//列的个数
                    for (int i = 0; i < dtColumns; i++)
                    {
                        dt.Columns.Add(arrayLine[i]);//每一列名称
                    }
                    isFirst = false;
                }
                else   //表内容
                {
                    DataRow dataRow = dt.NewRow();//新建一行
                    for (int j = 0; j < dtColumns; j++)
                    {
                        dataRow[j] = arrayLine[j];
                    }
                    dt.Rows.Add(dataRow);//添加一行
                }
            }
            sr.Close();
            fs.Close();

            return dt;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace CSVtoDataBase
{
    public class ModelHelper<T> where T : new()  // 此处一定要加上new()
    {

        public  IList<T> DataTableToModel(DataTable dt)
        {

            IList<T> list = new List<T>();// 定义集合
            Type type = typeof(T); // 获得此模型的类型
            string tempName = "";
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
                foreach (PropertyInfo pro in propertys)
                {
                    tempName = pro.Name;
                    if (dt.Columns.Contains(tempName))
                    {
                        if (!pro.CanWrite) continue;
                        object value = dr[tempName];
                        if (value != DBNull.Value)
                            pro.SetValue(t, value, null);
                    }
                }
                list.Add(t);
            }
            return list;
        }
    }
}

using Entitys;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSVtoDataBase
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string path = Directory.GetCurrentDirectory();
            string str1 = @"\DataBase\sb1.csv";
            string str2 = @"\DataBase\Monitor.db";
            
            SqlSugarService.SetConnectionStr("Data Source=" + path + str2);

            SugarResponsitory sugar = new SugarResponsitory();
            CSVHelper cSVHelper = new CSVHelper();
            TabletoList tabletoList = new TabletoList();
            ModelHelper<Sysadmins> modelHelper = new ModelHelper<Sysadmins>();

            DataTable sys = sugar.GetDataTableSysAdmin();
            
            //写入csv
            cSVHelper.WriteCSV(path+str1, sys);

            DataTable sys1 = cSVHelper.ReadCSV(path + str1);

            //List<Sysadmins> writeSys = tabletoList.TableToListModel<Sysadmins>(sys1);
            //List<Sysadmins> writeSys = tabletoList.DataTableToList<Sysadmins>(sys1);
            //sugar.InsertSysAdmin(writeSys);

            //List<ActualData> act2 = tabletoList.TableToListModel<ActualData>(act);
            //foreach (ActualData temp in act2)
            //{
            //    Console.WriteLine($"{temp.VarName}+{temp.Id}");

            //}

            Console.ReadKey();
        }
    }
}

阅读世界，共赴山海

423全民读书节，邀你共读