【C#】SQL Server接続とか

まぁ、これはWindowsアプリに限らんけどもー
ちゃんと既定クラスなりinterfaceなり用意して設計すれば、SQL Serverだけじゃなくても使えますねー

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Hoge
{
    public static SqlServerClient
    {
        private static SqlConnection GetConnection(string? initialCatalog)
        {
            return new SqlConnection(GetConnectionString("hogeServer", "hogeUser", "hogePassword", true, true, initialCatalog);
        }

        // 接続文字列生成-わざわざStringBuilderなんて使いません！
        private static string GetConnectionString(string serverName, string userId, string password, bool encrypt, bool trustServerCertificate, string? initialCatalog)
        {
            var connectionString = string.Empty;

            connectionString += string.Format("Data Source = {0};", serverName);
            connectionString += string.Format("UserId = {0};", userId);
            connectionString += string.Format("Pasword = {0};", password);
            if (encrypt)
                connectionString += "Encrypt = True;";
            if (trustServerCertificate)
                connectionString += "TrustServerCertificate = True;";
            if (initialCatalog != null)
                connectionString += string.Format("Initial Catalog = {0};", initialCatalog);

            return connectionString;
        }

        // カタログ名一覧取得
        public static string[] GetCatalogNames()
        {
            using (var connection = GetConnection(null))
            {
                connection.Open();

                var adapter = new SqlDataAdapter();
                adapter.SelectCommand = connection.CreateCommand();
                adapter.SelectCommand.CommandText = @"SELECT name FROM sysdatabases;";

                var dataTable = new System.Data.DataTable();
                adapter.Fill(dataTable);

                return dataTable.Rows.Cast<DataRow>().Select(x => x.ItemArray[0].ToString())
                    .ToArray();
            }
        }

        // テーブル名一覧取得
        public static string[] GetTableNames(string catalog)
        {
            using (var connection = GetConnection(catalog))
            {
                var adapter = new SqlDataAdapter();
                adapter.SelectCommand = connection.CreateCommand();
                adapter.SelectCommand.CommandText = @"SELECT * FROM Sys.Tables;";

                var dataTable = new System.Data.DataTable();
                try
                {
                    connection.Open();
                    adapter.Fill(dataTable);
                }
                catch { }

                return dataTable.Rows.Cast<DataRow>().Select(x => x.ItemArray[0].ToString()).ToArray();
            }
        }

        // SELECT文実行
        public static DataTable ExecuteSelectQuery(string catalog, string query)
        {
            using (var connection = GetConnection(catalog))
            {
                var adapter = new SqlDataAdapter();
                adapter.SelectCommand = connection.CreateCommand();
                adapter.SelectCommand.CommandText = query;

                var dataTable = new System.Data.DataTable();
                try
                {
                    connection.Open();
                    adapter.Fill(dataTable);
                }
                catch { }

                return dataTable;
            }
        }

        // SQL文実行
        public static void ExecuteQuery(string catalog, string query)
        {
            using (var connection = GetConnection(catalog))
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
        }
    }
}