KN_CAMP_SOFTWARE_TEST/Code/ClassLib/ZHYQ/Common/ClickHouseHelper.cs

using FangYar.Model.TBL;
using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Text;
using System.Threading;

namespace FangYar.Common
{

    public class ClickHouseHelper
    {

        ///// <summary>
        ///// Clickhouse数据库连接
        ///// </summary>
        //private static string connectionString = @"DSN=clickhouse.xfzn365.cn";
        /// <summary>
        /// Kafka服务器地址
        /// </summary>
        public static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["clickhouseConn"].ConnectionString;

        #region 执行Sql并返回数据集

        /// <summary>
        /// 执行Sql并返回数据集
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public static DataTable Execute(string sqlStr)//Execute(string sql,)
        {
            try
            {
                OdbcConnection myConnection = new OdbcConnection(connectionString);
                OdbcCommand myCommand = new OdbcCommand(sqlStr, myConnection);
                myConnection.Open();
                OdbcDataAdapter adapter = new OdbcDataAdapter();
                adapter.SelectCommand = myCommand;
                DataSet ds = new DataSet();

                adapter.Fill(ds);

                adapter.Dispose();
                myCommand.Dispose();
                myConnection.Close();
                return ds.Tables[0];

            }
            catch (Exception ex)
            {
                return new DataTable();
            }

        }
        #endregion



        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params OdbcParameter[] cmdParms)
        {
            using (OdbcConnection connection = new OdbcConnection(connectionString))
            {
                using (OdbcCommand cmd = new OdbcCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (Exception E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 替换sql语句中的参数占位为真实的参数值
        /// </summary>
        /// <param name="cmd">MySqlCommand对象</param>
        /// <param name="conn">数据库连接对象</param>
        /// <param name="trans">事务对象</param>
        /// <param name="cmdText">sql语句</param>
        /// <param name="cmdParms">参数值数组</param>
        private static void PrepareCommand(OdbcCommand cmd, OdbcConnection conn, OdbcTransaction trans, string cmdText, OdbcParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (OdbcParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }



        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (OdbcConnection connection = new OdbcConnection(connectionString))
            {
                using (OdbcCommand cmd = new OdbcCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (Exception E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }



    }

}