shavrinwpf/ComputerShopWpf/Services/DatabaseService.cs

using System;
using System.Collections.Generic;
using System.Data;
using Npgsql;

namespace ComputerShopWpf.Services
{
    public class DatabaseService
    {
        private readonly string _connectionString;

        public DatabaseService()
            : this(ConnectionSettings.MainConnection)
        {
        }

        public DatabaseService(string connectionString)
        {
            _connectionString = string.IsNullOrWhiteSpace(connectionString)
                ? ConnectionSettings.MainConnection
                : connectionString;
        }

        public DataTable ExecuteSelect(string sql, Dictionary<string, object> parameters = null)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(_connectionString))
            using (NpgsqlCommand command = CreateCommand(sql, parameters, connection))
            using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
            {
                DataTable table = new DataTable();
                adapter.Fill(table);
                return table;
            }
        }

        public int ExecuteNonQuery(string sql, Dictionary<string, object> parameters = null)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(_connectionString))
            using (NpgsqlCommand command = CreateCommand(sql, parameters, connection))
            {
                connection.Open();
                return command.ExecuteNonQuery();
            }
        }

        public object ExecuteScalar(string sql, Dictionary<string, object> parameters = null)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(_connectionString))
            using (NpgsqlCommand command = CreateCommand(sql, parameters, connection))
            {
                connection.Open();
                return command.ExecuteScalar();
            }
        }

        private static NpgsqlCommand CreateCommand(string sql, Dictionary<string, object> parameters, NpgsqlConnection connection)
        {
            NpgsqlCommand command = new NpgsqlCommand(sql, connection);

            if (parameters != null)
            {
                foreach (KeyValuePair<string, object> item in parameters)
                {
                    string parameterName = item.Key.StartsWith("@", StringComparison.Ordinal)
                        ? item.Key
                        : "@" + item.Key;
                    command.Parameters.AddWithValue(parameterName, item.Value ?? DBNull.Value);
                }
            }

            return command;
        }
    }
}