【SQL】C#.NETでSQL Server LocalDBを使う方法｜SSMS接続からSQL実行まで解説

sqllocaldb info

sqllocaldb info MSSQLLocalDB

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  user_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  prefecture VARCHAR(50),
  signup_date DATE NOT NULL,
  status VARCHAR(20) NOT NULL
);

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  order_date DATE NOT NULL,
  status VARCHAR(20) NOT NULL,
  coupon_code VARCHAR(30),
  note VARCHAR(200),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE order_items (
  order_item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO users 
(user_id, user_name, email, prefecture, signup_date, status) 
VALUES
(1, 'Aki',  'aki@example.com',  'Tokyo',    '2025-01-10', 'active'),
(2, 'Bora', 'bora@example.com', 'Osaka',    '2025-02-12', 'active'),
(3, 'Chin', 'chin@example.com', 'Tokyo',    '2025-02-20', 'inactive'),
(4, 'Dai',  'dai@example.com',  'Fukuoka',  '2025-03-05', 'active'),
(5, 'Ema',  'ema@example.com',  'Hokkaido', '2025-03-10', 'active');

INSERT INTO products 
(product_id, product_name, price, active) 
VALUES
(1, 'Laptop',     120000.00, 1),
(2, 'Headphones', 15000.00, 1),
(3, 'Mouse',       3000.00, 1),
(4, 'Novel',       1800.00, 1),
(5, 'Keyboard',    8000.00, 0);

INSERT INTO orders 
(order_id, user_id, order_date, status, coupon_code, note) 
VALUES
(1001, 1, '2025-04-01', 'paid',      'SPRING10', 'first order'),
(1002, 1, '2025-04-15', 'shipped',   NULL,       NULL),
(1003, 2, '2025-04-03', 'paid',      NULL,       'gift'),
(1004, 2, '2025-04-20', 'cancelled', NULL,       NULL),
(1005, 4, '2025-04-25', 'paid',      'VIP',      NULL);

INSERT INTO order_items 
(order_item_id, order_id, product_id, quantity, unit_price) 
VALUES
(1,  1001, 1, 1, 120000.00),
(2,  1001, 3, 2,   3000.00),
(3,  1002, 2, 1,  15000.00),
(4,  1002, 4, 3,   1800.00),
(5,  1003, 2, 2,  15000.00),
(6,  1003, 3, 1,   3000.00),
(7,  1004, 5, 1,   8000.00),
(8,  1005, 1, 1, 120000.00),
(9,  1005, 2, 1,  15000.00),
(10, 1005, 3, 1,   3000.00);

SELECT
    USER_ID,
    USER_NAME,
    Email,
    Prefecture,
    Status
FROM Users;

using Microsoft.Data.SqlClient;

namespace SQL_Server_Test
{
    public partial class Form1 : Form
    {
        // Server:LocalDBの接続先
        // Database:使用するデータベース
        // Integrated Security:Windows認証で接続
        // TrustServerCertificate:ローカル開発用に証明書を信頼
        private string connectionString =
                @"Server=(localdb)\MSSQLLocalDB;" +
                "Database=SampleDb;" +
                "Integrated Security=True;" +
                "TrustServerCertificate=True;";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ConnectTest();
        }

        private void ConnectTest()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))

                try
                {
                    connection.Open();
                    label2.Text = "LocalDBに接続しました。";
                }
                catch (Exception ex)
                {
                    label2.Text = "接続エラーが発生しました。";
                    Console.WriteLine(ex.Message);
                }
        }
    }
}

using Microsoft.Data.SqlClient;
using System.Data;

namespace SQL_Server_Test
{
    public partial class Form1 : Form
    {
        // Server:LocalDBの接続先
        // Database:使用するデータベース
        // Integrated Security:Windows認証で接続
        // TrustServerCertificate:ローカル開発用に証明書を信頼
        private string connectionString =
                @"Server=(localdb)\MSSQLLocalDB;" +
                "Database=SampleDb;" +
                "Integrated Security=True;" +
                "TrustServerCertificate=True;";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ConnectTest();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sql = @"SELECT user_id,user_name,email,prefecture,signup_date,status FROM dbo.users;";
            SelectTest(sql);
        }

        private void ConnectTest()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))

                try
                {
                    connection.Open();
                    label2.Text = "LocalDBに接続しました。";
                }
                catch (Exception ex)
                {
                    label2.Text = "接続エラーが発生しました。";
                    Console.WriteLine(ex.Message);
                }
        }

        private void SelectTest(string sql)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                using (SqlCommand command = new SqlCommand(sql, connection))
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataTable table = new DataTable();

                    adapter.Fill(table);

                    dataGridView1.DataSource = table;
                }

                label2.Text = "データを取得しました。";
            }
            catch (Exception ex)
            {
                label2.Text = "データ取得エラーが発生しました。";

                MessageBox.Show(
                    ex.Message,
                    "エラー",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Error
                );
            }
        }
    }
}