private void InitializeCollection()
{
mCollection = new ModelCollection();
mCollection.Read();
tnCollectionItem = new TreeNode("Collection");
InitializeType();
}
public static void NpgsqlDBImportCollection(ModelCollection mCollection)
{
string conn_str = "Server=localhost;Port=5432;User ID=xiao;Database=note;Password=xiao;Enlist=true";
using (NpgsqlConnection conn = new NpgsqlConnection(conn_str))
{
//PostgreSQLへ接続
conn.Open();
List <ImportEventItem> orderList = new List <ImportEventItem>();
int evid = GetLastId("event", 10000000, conn);
int chid = GetLastId("event_item", 11000000, conn);
int collectionid = GetLastId("collection", 30000000, conn);
int collectionchildid = GetLastId("collection_item", 31000000, conn);
foreach (var item in mCollection.Items)
{
string cmd_str = string.Format("insert into collection (id, name, category, status, memo, name2, name3, level, input_date, release_date) values " +
"('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}');",
++collectionid, QuoConv(item.Name), QuoConv(item.Category), QuoConv(item.Status), QuoConv(item.Memo),
QuoConv(item.Name2), QuoConv(item.Name3), item.Level, QuoConv(item.InputDate.ToString("yyyy-MM-dd")), QuoConv(item.ReleaseDate.ToString("yyyy-MM-dd")));
NpgsqlCommand cmd = new NpgsqlCommand(cmd_str, conn);
var rtn1 = cmd.ExecuteNonQuery();
foreach (var child in item.Character)
{
cmd_str = string.Format("insert into collection_item (id, name, type, index, memo, name2, name3, parent_id, input_date, update_date) values " +
"('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}');",
++collectionchildid, QuoConv(child.Name), "Character", child.Index, QuoConv(child.Memo),
QuoConv(child.Name2), QuoConv(child.Name3), collectionid, QuoConv(child.Date.ToString("yyyy-MM-dd")), QuoConv(child.Date.ToString("yyyy-MM-dd")));
cmd = new NpgsqlCommand(cmd_str, conn);
rtn1 = cmd.ExecuteNonQuery();
}
foreach (var child in item.Paragraph)
{
cmd_str = string.Format("insert into collection_item (id, name, type, index, memo, name2, name3, parent_id, input_date, update_date) values " +
"('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}');",
++collectionchildid, QuoConv(child.Name), "Paragraph", child.Index, QuoConv(child.Memo),
"", "", collectionid, QuoConv(child.Date.ToString("yyyy-MM-dd")), QuoConv(child.Date.ToString("yyyy-MM-dd")));
cmd = new NpgsqlCommand(cmd_str, conn);
rtn1 = cmd.ExecuteNonQuery();
}
foreach (var child in item.Setting)
{
cmd_str = string.Format("insert into collection_item (id, name, type, index, memo, name2, name3, parent_id, input_date, update_date) values " +
"('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}');",
++collectionchildid, QuoConv(child.Name), "Setting", child.Index, QuoConv(child.Memo),
"", "", collectionid, QuoConv(child.Date.ToString("yyyy-MM-dd")), QuoConv(child.Date.ToString("yyyy-MM-dd")));
cmd = new NpgsqlCommand(cmd_str, conn);
rtn1 = cmd.ExecuteNonQuery();
}
foreach (var child in item.Tags)
{
cmd_str = string.Format("insert into collection_parent_bean_tags (collection_parent_bean_id, tag) values " +
"('{0}', '{1}');",
collectionid, QuoConv(child));
cmd = new NpgsqlCommand(cmd_str, conn);
rtn1 = cmd.ExecuteNonQuery();
}
cmd_str = string.Format("select Count(*) from event where name = '{0}' or name = '{1}' or name = '{2}' ", QuoConv(item.Name), QuoConv(item.Name2), QuoConv(item.Name3));
cmd = new NpgsqlCommand(cmd_str, conn);
int rtnCount = 0;
using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
rtnCount = dr.GetInt32(0);
}
}
}
if (rtnCount > 0)
{
continue;
}
cmd_str = string.Format("insert into event (id, name, category, status, sorted) values ('{0}', '{1}', '{2}', '{3}', '{4}');",
++evid, QuoConv(item.Name), QuoConv(item.Category), "Finished", "Collection");
cmd = new NpgsqlCommand(cmd_str, conn);
rtn1 = cmd.ExecuteNonQuery();
cmd_str = string.Format("insert into event_item (id, date, memo, time, parent_id) values ('{0}', '{1}', '{2}', '{3}', '{4}');",
++chid, QuoConv(item.InputDate.ToString("yyyy-MM-dd")), "", 0.1, evid);
cmd = new NpgsqlCommand(cmd_str, conn);
rtn1 = cmd.ExecuteNonQuery();
Console.WriteLine("Insert success! {0}", item.Name);
}
Console.WriteLine("Insert Complete!");
conn.Close();
}
}