mes-client/UAS_Tools_HY/PublicMethods/ConnectDB.cs

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Security.Cryptography;
using System.Threading;
using System.Threading.Tasks;

namespace UAS_Tools_HY.PublicMethods
{
    internal class ConnectDB
    {
        //private static readonly string ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.81.208)(PORT=11687))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=N_MES_HY;Password=select!#%*(;";
        private static readonly string ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.18.6.220)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=N_MES_HY;Password=select!#%*(;";
        public static bool TestConnection()
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(ConnectionString))
                {
                    connection.Open();
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"数据库连接测试失败: {ex.Message}");
            }
        }

        public static DataTable ExecuteSelect(string sqlQuery, Dictionary<string, object> parameters = null)
        {
            DataTable dataTable = new DataTable();
            try
            {
                using (OracleConnection connection = new OracleConnection(ConnectionString))
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(sqlQuery, connection))
                    {
                        if (parameters != null && parameters.Count > 0)
                        {
                            foreach (var param in parameters)
                            {
                                command.Parameters.Add(new OracleParameter(param.Key, param.Value ?? DBNull.Value));
                            }
                        }
                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            adapter.Fill(dataTable);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return dataTable;
        }

        public static int ExecuteInsert(string sqlQuery, Dictionary<string, object> parameters = null)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(ConnectionString))
                {
                    connection.Open();
                    using (OracleCommand command = new OracleCommand(sqlQuery, connection))
                    {
                        if (parameters != null && parameters.Count > 0)
                        {
                            foreach (var param in parameters)
                            {
                                command.Parameters.Add(new OracleParameter(param.Key, param.Value ?? DBNull.Value));
                            }
                        }
                        return command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static int ExecuteUpdate(string sqlQuery, Dictionary<string, object> parameters = null)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(ConnectionString))
                {
                    connection.Open();
                    using (OracleCommand command = new OracleCommand(sqlQuery, connection))
                    {
                        if (parameters != null && parameters.Count > 0)
                        {
                            foreach (var param in parameters)
                            {
                                command.Parameters.Add(new OracleParameter(param.Key, param.Value ?? DBNull.Value));
                            }
                        }
                        return command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static int ExecuteDelete(string sqlQuery, Dictionary<string, object> parameters = null)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(ConnectionString))
                {
                    connection.Open();
                    using (OracleCommand command = new OracleCommand(sqlQuery, connection))
                    {
                        if (parameters != null && parameters.Count > 0)
                        {
                            foreach (var param in parameters)
                            {
                                command.Parameters.Add(new OracleParameter(param.Key, param.Value ?? DBNull.Value));
                            }
                        }
                        return command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static async Task<DataTable> ExecuteSelectCancellableSimpleAsync(string sqlQuery,Dictionary<string, object> parameters = null,CancellationToken cancellationToken = default)
        {
            DataTable dataTable = new DataTable();
            try
            {
                using (OracleConnection connection = new OracleConnection(ConnectionString))
                {
                    await connection.OpenAsync(cancellationToken);

                    using (OracleCommand command = new OracleCommand(sqlQuery, connection))
                    {
                        if (parameters != null && parameters.Count > 0)
                        {
                            foreach (var param in parameters)
                            {
                                command.Parameters.Add(new OracleParameter(param.Key, param.Value ?? DBNull.Value));
                            }
                        }

                        command.CommandTimeout = 0;

                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            await Task.Run(() =>
                            {
                                adapter.Fill(dataTable);

                                cancellationToken.ThrowIfCancellationRequested();
                            }, cancellationToken);
                        }
                    }
                }
            }
            catch (OperationCanceledException)
            {
                throw new OperationCanceledException("数据库查询已取消");
            }
            catch (Exception ex)
            {
                throw new Exception($"数据库查询失败: {ex.Message}");
            }

            return dataTable;
        }
    }
}