using System.Data;
using Microsoft.Data.SqlClient;
using MES.Service.util;

namespace MES.Service.DB;

/// <summary>
/// 用于SQL Server数据库访问的帮助类
/// </summary>
public class SqlServerSQLHelper
{
    private readonly string _connectionString;

    private readonly string ConnectionString =
        AppsettingsUtility.Settings.DataBaseConn;

    public SqlServerSQLHelper()
    {
        _connectionString = ConnectionString;
    }

    public SqlServerSQLHelper(string connectionString)
    {
        _connectionString = connectionString;
    }

    public DataTable ExecuteQuery(string query)
    {
        using (var connection = new SqlConnection(_connectionString))
        using (var command = new SqlCommand(query, connection))
        {
            connection.Open();
            var dataTable = new DataTable();
            using (var reader = command.ExecuteReader())
            {
                dataTable.Load(reader);
            }
            return dataTable;
        }
    }

    // 执行非查询操作 对数据库进行 增、删、改 操作(1） 
    public int ExecuteNonQuery(string query)
    {
        using (var connection = new SqlConnection(_connectionString))
        using (var command = new SqlCommand(query, connection))
        {
            connection.Open();
            return command.ExecuteNonQuery();
        }
    }

    public int ExecuteNonQueryWithTransaction(SqlConnection connection,
        SqlTransaction transaction, string query)
    {
        using (var command = connection.CreateCommand())
        {
            command.Transaction = transaction;
            command.CommandText = query;
            return command.ExecuteNonQuery();
        }
    }

    public int ExecuteTransaction(string query1)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            var transaction = connection.BeginTransaction();
            int totalRowsAffected = 0;

            try
            {
                totalRowsAffected = ExecuteNonQueryWithTransaction(
                    connection, transaction, query1);
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception(ex.Message);
            }
            return totalRowsAffected;
        }
    }

    public object ExecuteScalar(string query)
    {
        using (var connection = new SqlConnection(_connectionString))
        using (var command = new SqlCommand(query, connection))
        {
            connection.Open();
            return command.ExecuteScalar();
        }
    }

    public void ExecuteStoredProcedure(string procedureName,
        SqlParameter[] parameters)
    {
        using (var connection = new SqlConnection(_connectionString))
        using (var command = new SqlCommand(procedureName, connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddRange(parameters);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }

    public DataTable ExecuteDataTable(string sql)
    {
        return ExecuteDataTable(sql, CommandType.Text, null);
    }

    public DataTable ExecuteDataTable(string sql, CommandType commandType)
    {
        return ExecuteDataTable(sql, commandType, null);
    }

    public DataTable ExecuteDataTable(string sql, CommandType commandType,
        SqlParameter[] parameters)
    {
        var data = new DataTable();
        using (var connection = new SqlConnection(_connectionString))
        using (var command = new SqlCommand(sql, connection))
        {
            command.CommandType = commandType;
            if (parameters != null)
                command.Parameters.AddRange(parameters);

            using (var adapter = new SqlDataAdapter(command))
            {
                adapter.Fill(data);
            }
        }
        return data;
    }
}