.Net 6: C# で PostgreSQL の Create

プロジェクトの作成

mkdir Create01
cd Create01
dotnet new console
dotnet add package Npgsql --version 7.0.0-preview.6

フォルダー構造

$ tree -L 1
.
├── Create01.csproj
├── Program.cs
└── obj

Program.cs

// -------------------------------------------------------------------
/*
	Program.cs

					Aug/09/2022

*/
// -------------------------------------------------------------------
using	System;
using	System.Data;
using	System.Text;
using	Npgsql;

// -------------------------------------------------------------------
class postgre_create
{
// -------------------------------------------------------------------
static async Task Main (string[] args)
{
	Console.WriteLine ("*** 開始 ***");

	string server="localhost";
	string str_db="city";
        string user="scott";
        string password="tiger123";

        string str_connect = "Host=" + server + ";Username="
                + user + ";Password="
                + password + ";Database=" + str_db + ";";

        await using var conn = new NpgsqlConnection(str_connect);
        await conn.OpenAsync();

	table_drop_proc (conn);

	table_create_proc (conn);

	DataTable dtable = table_prepare_proc ();

	dtable = data_prepare_proc (conn,dtable);

	Console.WriteLine ("*** 終了 ***");
}

// -------------------------------------------------------------------
/* [6]: */
static DataTable data_prepare_proc
	 (NpgsqlConnection connection,DataTable dtable)
{
	dtable = data_row_proc
		(connection,dtable,"t3461",@"広島",35867,"2009-4-21");

	dtable = data_row_proc
		(connection,dtable,"t3462",@"福山",52146,"2009-7-9");

	dtable = data_row_proc
		(connection,dtable,"t3463",@"東広島",29381,"2009-9-19");

	dtable = data_row_proc
		(connection,dtable,"t3464",@"呉",75836,"2009-7-29");

	dtable = data_row_proc
		(connection,dtable,"t3465",@"尾道",18924,"2009-11-2");

	dtable = data_row_proc
		(connection,dtable,"t3466",@"竹原",71456,"2009-10-7");

	dtable = data_row_proc
		(connection,dtable,"t3467",@"三次",92391,"2009-9-9");

	dtable = data_row_proc
		(connection,dtable,"t3468",@"大竹",75843,"2009-8-16");

	dtable = data_row_proc
		(connection,dtable,"t3469",@"府中",49123,"2009-10-24");

	return	dtable;
}

// -------------------------------------------------------------------
/* [6-4]: */
static DataTable data_row_proc
	(NpgsqlConnection connection,DataTable dtable,
	string id_a,string name_a,int population_a,string str_date)
{
	DataRow drow = dtable.NewRow ();
	drow["ID"] = id_a;
	drow["NAME"] = name_a;
	drow["POPULATION"] = population_a.ToString ();
	drow["DATE_MOD"] = str_date;

	dtable.Rows.Add (drow);

	insert_proc (connection,id_a,name_a,population_a,str_date);

	return	dtable;
}

// -------------------------------------------------------------------
static DataTable table_prepare_proc ()
{
	DataTable dtable = new DataTable ("cities");

	dtable.Columns.Add (new DataColumn ("id",typeof (string)));
	dtable.Columns.Add (new DataColumn ("name",typeof (string)));
	dtable.Columns.Add (new DataColumn ("population",typeof (int)));
	dtable.Columns.Add (new DataColumn ("date_mod",typeof (string)));

	return dtable;
}

// ----------------------------------------------------------------
public static void table_drop_proc (NpgsqlConnection connection)
{
	string sql_str_drop = "drop table cities";

	NpgsqlCommand command = new NpgsqlCommand (sql_str_drop,connection);
	try
		{
		command.ExecuteNonQuery ();
		}
	catch	(Exception exp)
		{
		Console.Error.WriteLine ("*** error *** table_drop_proc ***");
		Console.Error.WriteLine
		("*** error *** " + exp);
		Environment.Exit (1);
		}
}

// -------------------------------------------------------------------
public static void table_create_proc (NpgsqlConnection connection)
{

	string sql_str_create = "create TABLE cities ("
			+ "ID varchar(10) NOT NULL PRIMARY KEY,"
			+ "NAME text,"
			+ "POPULATION int,"
			+ "DATE_MOD text)";

	NpgsqlCommand command = new NpgsqlCommand (sql_str_create,connection);
	command.ExecuteNonQuery ();
}

// -------------------------------------------------------------------
public static void insert_proc
	(NpgsqlConnection connection,string id_a,string name,int population_a,string str_date)
{
	StringBuilder sb_sql = new StringBuilder
	("insert into cities (id, Name, Population, date_mod) values ('"
		+ id_a + "','"
		+ name + "',"
		+ population_a + ",'"
		+ str_date + "')");

	string str_sql = sb_sql.ToString ();


	NpgsqlCommand command = new NpgsqlCommand (str_sql,connection);
	int rowsAffected = command.ExecuteNonQuery ();

	if (rowsAffected < 1)
		{
		Console.WriteLine ("rowsAffected = " + rowsAffected);
		}
}

// -------------------------------------------------------------------
}

// -------------------------------------------------------------------

実行結果

$ dotnet run
*** 開始 ***
*** 終了 ***