CNAS/CnasSynchronusDAL/DAL/FoxProDAL.cs

using CnasSynchronousCommon;
using CnasSynchrousModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace CnasSynchronusDAL
{
    public static class FoxProDAL
    {
        /// <summary>
        /// 获取所有表名和表结构
        /// </summary>
        /// <param name="strPath"></param>
        /// <returns></returns>
        public static Dictionary<string, DataTable> ReadFoxProTables(FoxProOpenParams t)
        {
            Dictionary<string, DataTable> dictTables = new Dictionary<string, DataTable>();
            try
            {
                //连接字符串
                string connstring = string.Format("provider=VFPOLEDB.1;Source Type=DBF;data source={0};user id=admin;password={1}", t.StrPath,t.StrPwd);
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    DataTable TablesName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字

                    string sql = "";
                    OleDbDataAdapter ada = null;
                    foreach (DataRow dr in TablesName.Rows)
                    {
                        string strTableName = dr[2].ToString();

                        sql = string.Format("SELECT * FROM [{0}] where 1=0", strTableName); //查询字符串
                        ada = new OleDbDataAdapter(sql, connstring);
                        DataSet set = new DataSet();
                        ada.Fill(set);

                        DataTable dt = set.Tables[0];
                        if (!string.IsNullOrWhiteSpace(t.specialMethod.Method))
                        {
                            switch (t.specialMethod.Method.ToLower())
                            {
                                case "comcat":  //合并某几个列，指定为某特定名称的列
                                    dt = FoxProSpecialDtMethod.FoxProSpecialDtMethodForComcat(dt, t.specialMethod.Value);
                                    break;
                            }
                        }

                        dictTables.Add(strTableName.ToUpper(), dt);
                    }
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            return dictTables;
        }

        internal static DataTable ReadFoxProTableStruct(FoxProOpenParams t, ConditionParams u)
        {
            DataTable dt = new DataTable();
            try
            {
                //连接字符串
                string connstring = string.Format("provider=VFPOLEDB.1;Source Type=DBF;data source={0};user id=admin;password={1}", t.StrPath, t.StrPwd);
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    string sql = "";
                    OleDbDataAdapter ada = null;
                    sql = string.Format("SELECT * FROM [{0}] where 1=0", u.TableName); //查询字符串
                    ada = new OleDbDataAdapter(sql, connstring);
                    DataSet set = new DataSet();
                    ada.Fill(set);

                    dt= set.Tables[0];
                    if (!string.IsNullOrWhiteSpace(t.specialMethod.Method))
                    {
                        switch (t.specialMethod.Method.ToLower())
                        {
                            case "comcat":  //合并某几个列，指定为某特定名称的列
                                dt=FoxProSpecialDtMethod.FoxProSpecialDtMethodForComcat(dt, t.specialMethod.Value);
                                break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            return dt;
        }

        public static DataTable ReadFoxProTablesByDate(FoxProOpenParams t, ConditionParams u)
        {
            DataTable dt = new DataTable();
            try
            {
                //连接字符串
                string connstring = string.Format("provider=VFPOLEDB.1;Source Type=DBF;data source={0};user id=admin;password={1}", t.StrPath, t.StrPwd);
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();

                    DataTable TablesName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
                    string sql = "";
                    OleDbDataAdapter ada = null;
                    if (!string.IsNullOrWhiteSpace(t.autosql.FoxProViewName)&&!string.IsNullOrWhiteSpace(t.autosql.FoxProViewSql))
                        sql = t.autosql.FoxProViewSql;
                    else
                        sql = string.Format("select * from [{0}]", u.TableName); //查询字符串
                    ada = new OleDbDataAdapter(sql, connstring);
                    DataSet set = new DataSet();
                    ada.Fill(set);

                    dt = set.Tables[0];
                    if (!string.IsNullOrWhiteSpace(t.specialMethod.Method))
                    {
                        switch (t.specialMethod.Method.ToLower())
                        {
                            case "comcat":  //合并某几个列，指定为某特定名称的列
                                dt = FoxProSpecialDtMethod.FoxProSpecialDtMethodForComcat(set.Tables[0], t.specialMethod.Value);
                                break;
                        }
                    }
                    DataTable dt_Middle = dt.Clone();
                    DateTime dtTime = DateTime.Now;
                    foreach (DataRow dr in dt.Rows)
                    {                    
                        if (DateTime.TryParse(dr[u.DateColumn].ToString(), out dtTime))
                        {
                            if (dtTime > Convert.ToDateTime(u.DateValue))
                            {
                                DataRow drNew = dt_Middle.NewRow();
                                drNew.ItemArray = dr.ItemArray;
                                dt_Middle.Rows.Add(drNew);
                            }
                        }
                        else if (IfSepcialDateFormat(dr[u.DateColumn].ToString(),t.timeFormat,ref dtTime))
                        {
                            if (dtTime > Convert.ToDateTime(u.DateValue))
                            {
                                DataRow drNew = dt_Middle.NewRow();
                                drNew.ItemArray = dr.ItemArray;
                                dt_Middle.Rows.Add(drNew);
                            }
                        }
                    }

                    dt = DateAndTimeTypeOpera(dt_Middle);
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            return dt;
        }

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

        public static DataTable ReadFoxProTablesByTableName(string strPath, string strUser, string strPwd, string strViewName,string strViewSql,string strTableName)
        {
            DataSet set = new DataSet();
            try
            {
                //连接字符串
                string connstring = string.Format("provider=VFPOLEDB.1;Source Type=DBF;data source={0};user id=admin;password={1}", strPath, strPwd);
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    DataTable TablesName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字

                    string sql = "";
                    OleDbDataAdapter ada = null;
                    if (strViewName == strTableName && !string.IsNullOrWhiteSpace(strViewName))
                        sql = strViewSql;
                    else
                        sql = string.Format("SELECT * FROM [{0}]", strTableName); //查询字符串
                    ada = new OleDbDataAdapter(sql, connstring);

                    ada.Fill(set);
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            return set.Tables[0];
        }


        /// <summary>
        /// 获取数据时，单独处理某些（Date和Time）类型数据，并把数据类型转换为字符串类型
        /// </summary>
        private static DataTable DateAndTimeTypeOpera(DataTable dt)
        {
            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() != "")
                        {
                            string strTime = Convert.ToDateTime(dr[dc.ColumnName]).ToString("HH:mm:ss");
                            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
                    {
                        drNewRow[dc.ColumnName] = dr[dc.ColumnName];
                    }
                }
                dtNewFormat.Rows.Add(drNewRow);
            }

            //返回数据
            return dtNewFormat;
        }

    }
}