yanh
/
CNAS


			
							using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Data;
using CnasSynchronousCommon;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using Newtonsoft.Json.Linq;
using Dm;

namespace CnasSynchronusDAL
{
    public static class MdbDAL
    {
        /// <summary>
        /// 获取所有表名和表结构
        /// </summary>
        /// <param name="strPath"></param>
        /// <returns></returns>
        public static Dictionary<string, DataTable> ReadAccessTables(string strPath, string strPwd,string strAccessVersion)
        {
            Dictionary<string, DataTable> dictTables = new Dictionary<string, DataTable>();
            OleDbConnection conn = null;
            try
            {
                //连接字符串
                string connstring = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password={1}", strPath, strPwd);
                if(!string.IsNullOrWhiteSpace(strAccessVersion)&&strAccessVersion != "0")
                     connstring = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password={1}", strPath, strPwd);
                using (conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    DataTable TablesName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字

                    foreach (DataRow dr in TablesName.Rows)
                    {
                        string strTableName = dr[2].ToString();
                        string sql = string.Format("SELECT * FROM [{0}] where 1=0", strTableName); //查询字符串
                        using (OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring))
                        {
                            DataSet set = new DataSet();
                            ada.Fill(set);
                            dictTables.Add(strTableName.ToUpper(), set.Tables[0]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            finally
            {
                conn?.Close();
                conn?.Dispose();
            }
            return dictTables;
        }

        internal static DataTable ReadAccessTableStruct(string strPath, string strPwd, string strAccessVersion, string strViewName, string strViewSQL,string strTableName)
        {
            DataTable dt = new DataTable();
            OleDbConnection conn = null;
            OleDbDataAdapter ada = null;
            try
            {
                //连接字符串
                string connstring = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password={1}", strPath, strPwd);
                if (!string.IsNullOrWhiteSpace(strAccessVersion) && strAccessVersion != "0")
                    connstring = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password={1}", strPath, strPwd);
                using (conn = new OleDbConnection(connstring))
                {
                    conn.Open();

                    //DataTable TablesName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
                    string sql = "";
                    if (strViewName == strTableName && !string.IsNullOrWhiteSpace(strViewName))
                    {
                        if(strViewSQL.ToUpper().Contains("WHERE"))
                            sql = strViewSQL + " and 1=0";
                        else
                            sql = strViewSQL + " where 1=0";
                    }
                    else
                        sql = string.Format("select * from [{0}] where 1=0", strTableName); //查询字符串

                    using (ada = new OleDbDataAdapter(sql, connstring))
                    {
                        DataSet set = new DataSet();
                        ada.Fill(set);
                        dt = set.Tables[0];
                    }
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            finally
            {
                ada?.Dispose();
                conn?.Close();
                conn?.Dispose();
            }
            return dt;
        }

        public static DataTable ReadAccessTablesByDate(string strPath, string strPwd, string strTableName, string strDateColumn, string strDate, string strAccessVersion,string strSpecialDateFormat,string strViewName, string strViewSQL)
        {
            DataTable dt = new DataTable();
            OleDbConnection conn = null;
            OleDbCommand command = null;
            OleDbDataReader reader = null;
            try
            {
                AppLog.Info("开始执行");
                //连接字符串
                string connstring = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password={1}", strPath, strPwd);
                if (!string.IsNullOrWhiteSpace(strAccessVersion) && strAccessVersion != "0")
                     connstring = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password={1}", strPath, strPwd);
                //执行sql语句
                string sql = "";
                if (strViewName == strTableName && !string.IsNullOrWhiteSpace(strViewName))
                {
                    sql = strViewSQL;
                }
                else
                {
                    sql = $"select * from [{strTableName}]" + "\r\n" +
                          $"where {strDateColumn} is not null and ? < {strDateColumn}";
                }
                
                using (conn = new OleDbConnection(connstring))
                {
                    conn.Open();

                    //获取数据库表结构
                    DataTable dtStruct = new DataTable();
                    dtStruct = ReadAccessTableStruct(strPath, strPwd, strAccessVersion, strViewName, strViewSQL, strTableName);

                    //获取数据，并插入到表中
                    using (command = new OleDbCommand(sql, conn))
                    {
                        if (strViewName != strTableName || string.IsNullOrWhiteSpace(strViewName))
                        {
                            string datetime = string.IsNullOrWhiteSpace(strSpecialDateFormat) ? strDate : Convert.ToDateTime(strDate).ToString(strSpecialDateFormat);
                            command.Parameters.AddWithValue("?", datetime);
                        }

                        using (reader = command.ExecuteReader())
                        {
                            try
                            {
                                while (reader.Read())
                                {
                                    DataRow row = dtStruct.NewRow();
                                    for (int i = 0; i < dtStruct.Columns.Count; i++)
                                    {
                                        row[i] = reader.GetValue(i);
                                    }
                                    dtStruct.Rows.Add(row);
                                }
                            }
                            catch (Exception ex)
                            {
                                //发生异常，写入日志
                                AppLog.Error(ex.Message);
                            }
                        }
                    }

                    AppLog.Info("ReadAccessTablesByDate执行的参数：" + strDate+","+ sql);
                    AppLog.Info("ReadAccessTablesByDate读取到的数据行数：" + dtStruct.Rows.Count);
                                 
                    /*将数据库中某些特殊类型的日期数据转换一下格式，便于后续处理*/
                    dt = DateAndTimeTypeOpera(dtStruct, strDateColumn,strSpecialDateFormat);

                    AppLog.Info("结束执行");
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            finally
            {
                reader?.Close();
                command?.Dispose();
                conn?.Close();
                conn?.Dispose();
            }
            return dt;
        }

        private static bool IfSepcialDateFormat(string strValue,string strSpecialDateFormat, ref DateTime dtTime)
        {
            bool bIfTrue = true;
            try
            {
                dtTime = DateTime.ParseExact(strValue, strSpecialDateFormat, new System.Globalization.CultureInfo("zh-CN", true));
            }
            catch (Exception e)
            {
                bIfTrue = false;
                AppLog.Error(e.Message);
            }
            return bIfTrue;
        }

        public static DataTable ReadAccessTablesByTableName(string strPath, string strUser, string strPwd, string strTableName,string strAccessVersion)
        {
            DataSet set = new DataSet();
            OleDbConnection conn = null;
            OleDbDataAdapter ada = null;
            try
            {
                //连接字符串
                string connstring = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password={1}", strPath, strPwd);
                if (!string.IsNullOrWhiteSpace(strAccessVersion) && strAccessVersion != "0")
                    connstring = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password={1}", strPath, strPwd);
                using (conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    string sql = string.Format("SELECT * FROM [{0}]", strTableName); //查询字符串
                    using (ada = new OleDbDataAdapter(sql, connstring))
                    {
                        ada.Fill(set);
                    }
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            finally
            {
                ada?.Dispose();
                conn?.Close();
                conn?.Dispose();
            }
            return set.Tables[0];
        }


        /// <summary>
        /// 获取数据时，单独处理某些（Date和Time）类型数据，并把数据类型转换为字符串类型
        /// </summary>
        private static DataTable DateAndTimeTypeOpera(DataTable dt,string strDateColumn,string strSpecialDateFormat)
        {
            DataTable dtNewFormat = new DataTable();
            List<string> lstChangeColumn = new List<string>();
            //添加列
            foreach (DataColumn dc in dt.Columns)
            {
                string strDateType = dc.DataType.ToString();
                switch (strDateType.ToUpper())
                {
                    case "SYSTEM.DATETIME":
                        dtNewFormat.Columns.Add(dc.ColumnName, typeof(string));  //使用字符串来存储该字段，而不是采用它的数据库格式（C#无法区分Date, Time，DateTime，前两种格式会自动补充数据，导致数据的不准确）
                        lstChangeColumn.Add(dc.ColumnName);
                        break;
                    default:
                        dtNewFormat.Columns.Add(dc.ColumnName, dc.DataType);
                        break;
                }
            }

            //添加数据行
            foreach (DataRow dr in dt.Rows)
            {
                DataRow drNewRow = dtNewFormat.NewRow();
                foreach (DataColumn dc in dtNewFormat.Columns)
                {
                    if (lstChangeColumn.Contains(dc.ColumnName))
                    {
                        if (dr[dc.ColumnName] != null && dr[dc.ColumnName].ToString() != "")
                        {
                            DateTime dateTime = Convert.ToDateTime(dr[dc.ColumnName]);
                            string strTime = dateTime.ToString("HH:mm:ss");
                            if (dateTime <= Convert.ToDateTime("1900-01-02"))
                            {
                                drNewRow[dc.ColumnName] = strTime;
                            }
                            else
                            {
                                if (strTime == "00:00:00")
                                    drNewRow[dc.ColumnName] = Convert.ToDateTime(dr[dc.ColumnName]).ToString("yyyy-MM-dd");
                                else
                                    drNewRow[dc.ColumnName] = Convert.ToDateTime(dr[dc.ColumnName]).ToString("yyyy-MM-dd HH:mm:ss");
                            }
                        }
                    }
                    else
                    {
                        if (dc.ColumnName.ToLower() == strDateColumn.ToLower())
                        {
                            DateTime dt1 = DateTime.Now;
                            if (DateTime.TryParse(dr[dc.ColumnName].ToString(), out dt1))
                            {
                                drNewRow[dc.ColumnName] = dr[dc.ColumnName].ToString();
                            }
                            else
                            {
                                DateTime dtTime = DateTime.ParseExact(dr[dc.ColumnName].ToString(), strSpecialDateFormat, new System.Globalization.CultureInfo("zh-CN", true));
                                if (dtTime.ToString("HH:mm:ss") == "00:00:00")
                                    drNewRow[dc.ColumnName] = dtTime.ToString("yyyy-MM-dd");
                                else
                                    drNewRow[dc.ColumnName] = dtTime.ToString("yyyy-MM-dd HH:mm:ss");
                            }
                        }
                        else
                        {
                            drNewRow[dc.ColumnName] = dr[dc.ColumnName];
                        }
                    }
                }
                dtNewFormat.Rows.Add(drNewRow);
            }

            //返回数据
            return dtNewFormat;
        }

    }
}