CNAS/CNAS_DBSync/SelectTableType.cs

using CnasSynchronousCommon;
using CnasSynchronusDAL;
using CnasSynchrousModel;
using DBEngine;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CNAS_DBSync
{
    public class SelectTableType
    {
        private static string connectionStr = "";

        #region 自动模式调取方法
        public static DataTable MySqlsec(string strTableName)
        {
            MySQLDAL mysql = new MySQLDAL();
            DataTable tb = mysql.GetTableTypeAndLenth(strTableName);
            return tb;
        }
        public static DataTable Sqlserversec(string strTableName, SyncInstrumentItemInfo t)
        {

            DataTable dt = new DataTable();
            try
            {
                if (t.SyncInstrumentDSInfo.Host != "")
                    connectionStr = $"Data Source = {t.SyncInstrumentDSInfo.Host}; Initial Catalog = {t.SyncInstrumentDSInfo.ServerName}; User Id = {t.SyncInstrumentDSInfo.UserId}; Password = {t.SyncInstrumentDSInfo.UserPwd};";

                string sql = string.Format(@"SELECT 
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length AS MaxLength,
    c.is_nullable AS IsNullable,
    c.default_object_id AS DefaultObjectId,
	 ep.value AS remark
FROM 
    sys.columns c
JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
	LEFT JOIN 
    sys.extended_properties ep
	ON 
    c.object_id = ep.major_id
    AND c.column_id = ep.minor_id
    AND ep.name = 'MS_Description' -- 备注的属性名称
WHERE 
    c.object_id = OBJECT_ID('{0}'); ", strTableName); //查询字符串
                dt = GetDataTable(sql, new SqlParameter[] { });

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

        }
        /// <summary>
        /// 查询操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] sp)
        {
            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                conn.Open();
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn))
                {
                    sda.SelectCommand.Parameters.AddRange(sp);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
        public static DataTable PostgreSql(string strTableName)
        {

            DataTable dt = new DataTable();
            string strSql = string.Format(@"SELECT
                                                a.attname as ColumnName,
                                                format_type(a.atttypid, a.atttypmod) as DataType,
                                                a.attnotnull as 非空,
                                                col_description(a.attrelid, a.attnum) as remark
                                                FROM
                                                pg_class as c, pg_attribute as a
                                                where
                                                a.attrelid = c.oid
                                                and
                                                a.attnum > 0
                                                and
                                                c.relname = '{0}'; ", strTableName);
            try
            {
                dt = PostgreSQLHelper.ExecuteDataSet(strSql).Tables[0];
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
            }
            return dt;


        }
        public static DataTable DmSql(string strTableName)
        {

            DataTable dt = new DataTable();
            string strSql = string.Format(@"SELECT 
    c.COLUMN_NAME AS ColumnName,
    c.DATA_TYPE AS DataType,
    c.DATA_LENGTH AS 字段长度,
    c.NULLABLE AS 是否允许为空,
    com.COMMENTS AS remark
FROM 
    USER_TAB_COLUMNS c
LEFT JOIN 
    USER_COL_COMMENTS com
ON 
    c.TABLE_NAME = com.TABLE_NAME
    AND c.COLUMN_NAME = com.COLUMN_NAME
WHERE 
    c.TABLE_NAME = '{0}';", strTableName);
            try
            {
                dt = DamengHelper.ExecuteDataSet(strSql).Tables[0];
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
            }
            return dt;


        }
        public static DataTable OrcSql(string strTableName, SyncInstrumentItemInfo t) {
            connectionStr = $"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={t.SyncInstrumentDSInfo.Host})(PORT={t.SyncInstrumentDSInfo.Port}))" + $"(CONNECT_DATA=(SID={t.SyncInstrumentDSInfo.ServerName})));User Id={t.SyncInstrumentDSInfo.UserId};Password={t.SyncInstrumentDSInfo.UserPwd};";
            DataTable dt = new DataTable();
            string strSql = string.Format("select COLUMN_NAME AS ColumnName,NULLABLE AS IsNullable,DATA_TYPE AS DataType,DATA_LENGTH AS CharMaxLenth,DATA_LENGTH AS CharOcterLenth,DATA_PRECISION AS NumericPrecision,DATA_SCALE AS NumericScale from user_tab_columns where table_name='{0}'", strTableName.ToUpper());
            try
            {
                dt = GetDataTable(strSql, new OracleParameter[] { });
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
            }
            return dt;
        }
        /// <summary>
        /// 查询操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, params OracleParameter[] sp)
        {
            DataTable dt = new DataTable();
            try
            {
                using (OracleConnection conn = new OracleConnection(connectionStr))
                {
                    conn.Open();
                    //using (OracleDataAdapter sda = new OracleDataAdapter("select * from t_weight_info where WEIGH_DATE > '2019-10-02 14:20:12'", conn))
                    using (OracleDataAdapter sda = new OracleDataAdapter(sql, conn))
                    {
                        sda.SelectCommand.Parameters.AddRange(sp);
                        sda.Fill(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
            }
            return dt;
        }
        public static DataTable KingSql(string strTableName)
        {
            DataTable dt = new DataTable();
            string strSql = string.Format("       \r\n    SELECT \r\n    a.attname as ColumnName,\r\n    \r\n    col_description(a.attrelid, a.attnum) as remark,\r\n    b.data_type AS DATATYPE \r\n    \r\nFROM \r\n    pg_class as c,\r\n    pg_attribute as a,\r\n    information_schema.COLUMNS b\r\n \t\r\nWHERE \r\n    c.relname = '{0}' \r\n    and a.attrelid = c.oid \r\n    AND a.attname=b.column_name\r\n   \tAND b. table_name = '{0}'\r\n", strTableName);
            try
            {
                dt = KingbaseHelper.ExecuteDataSet(strSql).Tables[0];
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
            }
            return dt;
        }

        public static DataTable AccSql(InstrumentDataSourceInfo fileUrl,string sql)
        {
         var  connectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={fileUrl.Path};Jet OLEDB:Database Password={fileUrl.UserPwd};";
           DataTable dataTable = new DataTable();
        
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            {
                    try
                    {
                        connection.Open();
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                        {
                            adapter.Fill(dataTable);
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"查询执行错误: {ex.Message}");
                       
                    }
                    finally {
                        connection.Close();
                    }
                  
            }
        }
        
            return dataTable;
        }
        #endregion

        #region 手动模式调取方法
        public static DataTable MySqlsecSD(string strSql)
        {
            DataTable dt = new DataTable();
            try
            {
                dt = MySQLHelper.ExecuteDataSet(strSql).Tables[0];
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
            }
            return dt;
        
        }
        public static DataTable SqlserversecSD(string sql, SyncInstrumentItemInfo t)
        {

            DataTable dt = new DataTable();
            try
            {
                if (t.SyncInstrumentDSInfo.Host != "")
                    connectionStr = $"Data Source = {t.SyncInstrumentDSInfo.Host}; Initial Catalog = {t.SyncInstrumentDSInfo.ServerName}; User Id = {t.SyncInstrumentDSInfo.UserId}; Password = {t.SyncInstrumentDSInfo.UserPwd};";

               
                dt = GetDataTable(sql, new SqlParameter[] { });

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

        }
       
        public static DataTable PostgreSqlSD(string strSql)
        {

            DataTable dt = new DataTable();

            try
            {
                dt = PostgreSQLHelper.ExecuteDataSet(strSql).Tables[0];
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
            }
            return dt;


        }
        public static DataTable DmSqlSD(string strSql)
        {

            DataTable dt = new DataTable();
          
            try
            {
                dt = DamengHelper.ExecuteDataSet(strSql).Tables[0];
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
            }
            return dt;


        }
        public static DataTable OrcSqlSD(string strSql, SyncInstrumentItemInfo t)
        {
            connectionStr = $"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={t.SyncInstrumentDSInfo.Host})(PORT={t.SyncInstrumentDSInfo.Port}))" + $"(CONNECT_DATA=(SID={t.SyncInstrumentDSInfo.ServerName})));User Id={t.SyncInstrumentDSInfo.UserId};Password={t.SyncInstrumentDSInfo.UserPwd};";
            DataTable dt = new DataTable();
           // string strSql = string.Format("select COLUMN_NAME AS ColumnName,NULLABLE AS IsNullable,DATA_TYPE AS DataType,DATA_LENGTH AS CharMaxLenth,DATA_LENGTH AS CharOcterLenth,DATA_PRECISION AS NumericPrecision,DATA_SCALE AS NumericScale from user_tab_columns where table_name='{0}'", strTableName.ToUpper());
            try
            {
                dt = GetDataTable(strSql, new OracleParameter[] { });
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
            }
            return dt;
        }
       
        public static DataTable KingSqlSD(string Sql)
        {
            DataTable dt = new DataTable();
            //string strSql = string.Format(" SELECT  a.attname as ColumnName,col_description(a.attrelid, a.attnum) as remark, b.data_type AS DATATYPE  FROM    pg_class as c,  pg_attribute as a,   information_schema.COLUMNS b WHERE   c.relname = '{0}'  and a.attrelid = c.oid    AND a.attname=b.column_name   AND b. table_name LIKE '{0}'", strTableName);
            try
            {
                dt = KingbaseHelper.ExecuteDataSet(Sql).Tables[0];
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
            }
            return dt;
        }
        #endregion
    }
}