Database.cs

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PatientBillingSystem
{
    internal class Database
    {
        // SQL DATABASE
        private static string connectionString = """
            Server=LAPTOP-ASTRODAV;
            Database=PatientBillingSystem;
            Integrated Security=True;
            Encrypt=True;
            TrustServerCertificate=True;
            """;
        public static SqlConnection SqlConnection = new SqlConnection(connectionString);


        // METHODS
        public static void StartSqlConnection()
        {
            if (SqlConnection == null) SqlConnection = new SqlConnection(connectionString);
            SqlConnection.Open();
        }

        public static void StopSqlConnection()
        {
            if(SqlConnection != null) SqlConnection.Close();
        }

        public static bool IsSqlConnected()
        {
            if (SqlConnection.State == System.Data.ConnectionState.Open) return true;
            else
            {
                MessageBox.Show("The system is not connected to database", "SQL Connection Failed");
                return false;
            }
        }


        // SQL QUERY
        public static List<string[]> Query(string query)
        {
            try
            {
                SqlConnection.Open();
                if (!IsSqlConnected())
                {
                    Debug.WriteLine("Database not connected. return NULL??");
                    return null;
                }

                SqlCommand command = new SqlCommand(query, SqlConnection);

                if (query.Trim().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
                {
                    SqlDataReader reader = command.ExecuteReader();
                    List<string[]> rows = new List<string[]>();

                    while (reader.Read())
                    {
                        string[] row = new string[reader.FieldCount];
                        for (int i = 0; i < reader.FieldCount; i++)
                            row[i] = reader.GetValue(i).ToString();
                        rows.Add(row);
                    }

                    if (rows.Count == 0)
                    {
                        string[] row = new string[reader.FieldCount];
                        for (int i = 0; i < reader.FieldCount; i++)
                            row[i] = string.Empty;
                        rows.Add(row);
                    }
                    reader.Close();

                    SqlConnection.Close();
                    return rows;
                }
                else
                {
                    command.ExecuteNonQuery();
                    SqlConnection.Close();
                    return null;
                }
            }
            catch (Exception e)
            {
                SqlConnection.Close();
                throw e;
            }
        }

        // Project developed by Dave Laurence Tolentino | BSIT-3B
        // FINAL TASK PERFORMANCE FOR
        // EVENT-DRIVEN PROGRAMMING | ADVANCED DATABASE | APPLICATION DEVELOPMENT
        // Final revision submitted at <insert date>
    }
}