.Net 6: C# で MariaDB の Create

プロジェクトの作成

mkdir Create01
cd Create01
dotnet new console
dotnet add package MySql.Data --version 8.0.30

フォルダー構造

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

Program.cs

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

					Apr/08/2022

*/
// -------------------------------------------------------------------
using	System;
using	System.Collections.Generic;
using	MySql.Data.MySqlClient;

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

	Dictionary <string,Object> dict_aa = data_prepare_proc ();

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

	string str_connect = "Server=" + server + 
		";User Id=" + user + ";Password=" + password +
		";Database=" + str_db + ";";


	MySqlConnection connection = new MySqlConnection (str_connect);
	connection.Open ();

	mysql_manipulate.table_drop_proc (connection);

	mysql_manipulate.table_create_proc (connection);

	mysql_manipulate.dict_to_db_proc (dict_aa,connection);

	connection.Close ();

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


// -------------------------------------------------------------------
static Dictionary <string,Object> data_prepare_proc ()
{
	Dictionary <string,Object> dict_aa
			= new Dictionary <string,Object> ();

	dict_aa = dict_append_proc (dict_aa,"t3321","岡山",497125,"2009-8-4");
	dict_aa = dict_append_proc (dict_aa,"t3322","倉敷",219687,"2009-9-2");
	dict_aa = dict_append_proc (dict_aa,"t3323","津山",871392,"2009-10-8");
	dict_aa = dict_append_proc (dict_aa,"t3324","玉野",923187,"2009-5-9");

	dict_aa = dict_append_proc (dict_aa,"t3325","笠岡",651978,"2009-1-5");
	dict_aa = dict_append_proc (dict_aa,"t3326","井原",395647,"2009-5-22");
	dict_aa = dict_append_proc (dict_aa,"t3327","総社",412786,"2009-7-17");
	dict_aa = dict_append_proc (dict_aa,"t3328","高梁",174835,"2009-3-4");
	dict_aa = dict_append_proc (dict_aa,"t3329","新見",781324,"2009-10-12");

	return	dict_aa;
}

// -------------------------------------------------------------------
public static Dictionary <string,Object> dict_append_proc
	(Dictionary <string,Object> dict_aa,
	String id_in,String name_in,int population_in,String date_mod)
{
	Dictionary <string,string> unit_aa
			= new Dictionary <string,string> ();

	unit_aa.Add ("name",name_in);
	unit_aa.Add ("population",population_in.ToString ());
	unit_aa.Add ("date_mod",date_mod);

	dict_aa.Add (id_in,unit_aa);

	return	dict_aa;
}

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

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

mysql_manipulate.cs

// -------------------------------------------------------------------
/*
	mysql_manipulate.cs

					Aug/09/2022


*/
// -------------------------------------------------------------------
using	System;
using	System.Collections.Generic;
using	System.Data;
using	System.Text;
using	MySql.Data.MySqlClient;

// -------------------------------------------------------------------
public class mysql_manipulate
{
// -------------------------------------------------------------------
/* [4]: */
public static DataTable mysql_data_fetch_proc (MySqlConnection connection)
{
	string command = "SELECT id,name,population,date_mod FROM cities order by ID";

	MySqlDataAdapter da_adapter = new MySqlDataAdapter (command,connection);

	DataTable dtable = new DataTable ();

	da_adapter.Fill (dtable);

	return	dtable;
}

/* ------------------------------------------------------------------- */
/* [6]: */
public static void mysql_update_proc
	(MySqlConnection connection,string id_a,int population_a)
{
	DateTime dateNow = DateTime.Now;

	string str_date = dateNow.ToString("yyyy-MM-dd HH:mm:ss");
	Console.WriteLine (str_date);

	StringBuilder sb_sql = new StringBuilder
			("UPDATE cities SET population = "
			+ population_a + @" , date_mod = '" + str_date +
			@"' WHERE id = '" + id_a + "'");

	string str_sql = sb_sql.ToString ();

	Console.WriteLine (str_sql);

	MySqlCommand command = new MySqlCommand (str_sql,connection);
	int rowsAffected = command.ExecuteNonQuery ();
	Console.WriteLine ("rowsAffected = " + rowsAffected);
}

/* ------------------------------------------------------------------- */
public static void mysql_delete_proc
	(MySqlConnection connection,string id_a)
{
	StringBuilder sb_sql = new StringBuilder
			("DELETE from cities WHERE ID = '" + id_a + "'");

	string str_sql = sb_sql.ToString ();

	Console.WriteLine (str_sql);

	MySqlCommand command = new MySqlCommand (str_sql,connection);
	int rowsAffected = command.ExecuteNonQuery ();
	Console.WriteLine ("rowsAffected = " + rowsAffected);
}

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

	MySqlCommand command = new MySqlCommand (sql_str_drop,connection);
	command.ExecuteNonQuery ();
}

/* ------------------------------------------------------------------- */
public static void table_create_proc (MySqlConnection connection)
{
	string sql_str_create = "create TABLE cities ("
			+ "id varchar(10) NOT NULL PRIMARY KEY,"
			+ "name text,"
			+ "population int,"
			+ "date_mod text)";

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

// -------------------------------------------------------------------
public static void dict_to_db_proc
	(Dictionary <string,Object> dict_aa,MySqlConnection connection)
{
	foreach (KeyValuePair<string, Object> kv in dict_aa)
		{
		Dictionary <string,string> unit_aa
				= (Dictionary <string,string>)kv.Value;

		int population = int.Parse (unit_aa["population"]);
		
		mysql_insert_proc
			(connection,kv.Key,unit_aa["name"],
			population,unit_aa["date_mod"]);
		}
}

// -------------------------------------------------------------------
public static void mysql_insert_proc
	(MySqlConnection 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 ();

//	Console.WriteLine (str_sql);

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

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

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

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

実行結果

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