はじめに
LocalDBのデータをコマンドラインから操作してみます。
実行環境
下記バージョンで動作確認しています。
- Windows 11
- .NET 8.0
$ dotnet --version
8.0.414
学習方針
LocalDBで開発する事で、SQL Serverにおいてもそのままできると思います。
$ mkdir dbtool
$ cd dbtool
$ dotnet new console
まずは実行してみます。
$ dotnet run
Hello World!
必要なパッケージをインストールします。
$ dotnet add package Microsoft.Data.SqlClient
以下のような使い方を想定しています。
$ dbtool
Usage: dbtool -options [file path]
options
-e export user from LocalDB
-i import user to LocalDB
-d delete user from LocalDB
-v display version
-h display help message
$ dbtool -v
LocalDB module: version 202510
$ dbtool -e users.txt
$ type users.txt
id email password updated
000000000000 aaa.bbb@gmail.com pAAAssword 2025/01/01 18:12:44
000000000001 ccc.ddd@gmail.com pCCCssword 2025/02/01 18:12:44
Program.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using Microsoft.Data.SqlClient;
namespace dbtool
{
class Program
{
static void Main(string[] args)
{
if (args.Length < 1 || args.Length > 2)
{
help();
return;
}
if (args.Length == 1)
{
switch (args[0])
{
case "-v": version(); break;
default: help(); break;
}
return;
}
switch (args[0])
{
case "-i": import(args[1]); break;
case "-e": export(args[1]); break;
case "-d": delete(args[1]); break;
default: help(); break;
}
return;
}
static void import(string param)
{
string sqlcmd;
string connectionString = @"Server=(localdb)\MSSQLLocalDB;Database=myop;User ID = sa; Password = Your$trongP@ssw0rd!;";
using (var cn = new SqlConnection(connectionString))
{
cn.Open();
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (StreamReader sr = new StreamReader(new FileStream(@param, FileMode.Open), Encoding.GetEncoding("shift_jis")))
{
while (sr.Peek() > 0)
{
String line = sr.ReadLine();
string[] values = line.Split('\t');
if (values[0] == "id")
{
continue;
}
else
{
sqlcmd = "MERGE users AS target USING(VALUES('" + values[0] + "','" + values[1] + "','" + values[2] + "','" + values[3] + "')) AS source(id, email, password, updated) ON target.id = source.id" +
" WHEN MATCHED THEN"+
" UPDATE SET email = source.email, password = source.password, updated = source.updated"+
" WHEN NOT MATCHED THEN" +
" INSERT(id, email, password, updated)"+
"VALUES(source.id, source.email, source.password, source.updated);";
}
using (SqlTransaction trans = cn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand(sqlcmd, cn);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
}
}
cn.Close();
}
return;
}
static void export(string param)
{
string connectionString = @"Server=(localdb)\MSSQLLocalDB;Database=myop;User ID = sa; Password = Your$trongP@ssw0rd!;";
using (var cn = new SqlConnection(connectionString))
{
cn.Open();
using (var cmd = new SqlCommand("SELECT * FROM users", cn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
string message = "id\temail\tpassword\tupdated\r\n";
while (reader.Read())
{
message += reader["id"].ToString() + "\t" + reader["email"].ToString() + "\t" + reader["password"].ToString() + "\t" + reader["updated"].ToString() + "\r\n";
}
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
File.WriteAllText(@param, message, Encoding.GetEncoding("shift_jis"));
}
}
cn.Close();
}
return;
}
static void delete(string param)
{
string connectionString = @"Server=(localdb)\MSSQLLocalDB;Database=myop;User ID = sa; Password = Your$trongP@ssw0rd!;";
using (var cn = new SqlConnection(connectionString))
{
cn.Open();
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (StreamReader sr = new StreamReader(new FileStream(@param, FileMode.Open), Encoding.GetEncoding("shift_jis")))
{
while (sr.Peek() > 0)
{
string sqlcmd = "DELETE FROM users WHERE id = " + sr.ReadLine();
using (SqlTransaction trans = cn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand(sqlcmd, cn);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
}
}
cn.Close();
}
return;
}
static void version()
{
Console.WriteLine("\nLocalDB module: version 202510\n");
return;
}
static void help()
{
Console.WriteLine("\nUsage: dbtool -options [file path]\n");
Console.WriteLine("options");
Console.WriteLine(" -e export user from LocalDB");
Console.WriteLine(" -i import user to LocalDB");
Console.WriteLine(" -d delete user from LocalDB");
Console.WriteLine(" -v display version");
Console.WriteLine(" -h display help message");
return;
}
}
}