private static void ExecutePostgresDDL() { StringBuilder sb = new StringBuilder(); sb.AppendLine("CREATE TABLE USER_MST( "); sb.AppendLine(" user_id SERIAL PRIMARY KEY"); sb.AppendLine(" , emp_id INTEGER UNIQUE"); sb.AppendLine(" , user_name TEXT"); sb.AppendLine(" , kengen INTEGER"); sb.AppendLine(" , mail_id TEXT"); sb.AppendLine(" , password TEXT"); sb.AppendLine(" , mail_address TEXT"); sb.AppendLine(" , sanka_kahi INTEGER"); sb.AppendLine(" , delete_flg INTEGER"); sb.AppendLine(" , version INTEGER"); sb.AppendLine(" , cre_date TEXT"); sb.AppendLine("); "); sb.AppendLine(""); sb.AppendLine("CREATE TABLE WORD_DIC( "); sb.AppendLine(" word_id SERIAL PRIMARY KEY"); sb.AppendLine(" , ronri_name1 TEXT"); sb.AppendLine(" , ronri_name2 TEXT"); sb.AppendLine(" , butsuri_name TEXT"); sb.AppendLine(" , data_type text"); sb.AppendLine(" , user_id INTEGER"); sb.AppendLine(" , version INTEGER"); sb.AppendLine(" , cre_date TEXT"); sb.AppendLine(" , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)"); sb.AppendLine("); "); sb.AppendLine(""); sb.AppendLine("CREATE TABLE WORD_SHINSEI( "); sb.AppendLine(" shinsei_id SERIAL PRIMARY KEY"); sb.AppendLine(" , ronri_name1 TEXT"); sb.AppendLine(" , ronri_name2 TEXT"); sb.AppendLine(" , butsuri_name TEXT"); sb.AppendLine(" , word_id INTEGER"); sb.AppendLine(" , status INTEGER"); sb.AppendLine(" , user_id INTEGER"); sb.AppendLine(" , version INTEGER"); sb.AppendLine(" , cre_date TEXT"); sb.AppendLine(" , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)"); sb.AppendLine("); "); sb.AppendLine("CREATE TABLE or_map( "); sb.AppendLine(" or_id SERIAL PRIMARY KEY"); sb.AppendLine(" , data_type text"); sb.AppendLine(" , db_data_type text"); sb.AppendLine(" , project_name text"); sb.AppendLine(" , yuko_flg INTEGER"); sb.AppendLine(" , delete_flg INTEGER"); sb.AppendLine(" , version INTEGER"); sb.AppendLine(" , cre_date text"); sb.AppendLine(");"); sb.AppendLine("insert "); sb.AppendLine("into USER_MST( "); sb.AppendLine(" user_id"); sb.AppendLine(" , emp_id"); sb.AppendLine(" , user_name"); sb.AppendLine(" , kengen"); sb.AppendLine(" , mail_id"); sb.AppendLine(" , password"); sb.AppendLine(" , mail_address"); sb.AppendLine(" , sanka_kahi"); sb.AppendLine(" , delete_flg"); sb.AppendLine(" , version"); sb.AppendLine(") "); sb.AppendLine("values ( "); sb.AppendLine(" 1"); sb.AppendLine(" , 999"); sb.AppendLine(" , 'Admin'"); sb.AppendLine(" , 0"); sb.AppendLine(" , '999'"); sb.AppendLine(" , '*****@*****.**'"); sb.AppendLine(" , '*****@*****.**'"); sb.AppendLine(" , 0"); sb.AppendLine(" , 0"); sb.AppendLine(" , 0"); sb.AppendLine("); "); sb.AppendLine("insert into public.or_map(or_id,data_type,db_data_type,project_name,yuko_flg,delete_flg,version,cre_date) values (1,'String','VARCHAR',null,0,0,0,null);"); sb.AppendLine("insert into public.or_map(or_id,data_type,db_data_type,project_name,yuko_flg,delete_flg,version,cre_date) values (2,'Integer','INTEGER',null,0,0,0,null);"); sb.AppendLine("insert into public.or_map(or_id,data_type,db_data_type,project_name,yuko_flg,delete_flg,version,cre_date) values (3,'Date','DATE',null,0,0,0,null);"); sb.AppendLine("insert into public.or_map(or_id,data_type,db_data_type,project_name,yuko_flg,delete_flg,version,cre_date) values (4,'Timestamp','TIMESTAMP',null,0,0,0,null);"); string postgresDdlText = sb.ToString(); CommonFunction common = new CommonFunction(); string dbConnectionString = common.getDbConnectionString(); using (NpgsqlConnection cn = new NpgsqlConnection(dbConnectionString)) { cn.Open(); NpgsqlCommand cmd = cn.CreateCommand(); cmd.CommandText = postgresDdlText; cmd.ExecuteNonQuery(); cn.Close(); } }
private void sqliteTestConnectBtn_Click(object sender, EventArgs e) { StringBuilder sb = new StringBuilder(); sb.Append("Data Source=" + this.sqliteDbFilePath.Text); sb.Append(";foreign keys=true;"); CommonFunction common = new CommonFunction(); string dbConnectionString = common.getDbConnectionString(); string dbProviderName = common.getDbProviderName(); try { using (SQLiteConnection cn = new SQLiteConnection(sb.ToString())) { cn.Open(); SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT * FROM WORD_DIC"; cmd.ExecuteReader(); common.setSqliteDbContextPath(sb.ToString()); MyRepository rep = new MyRepository(); UserMst fromUser = rep.FindUserMstByUserId(999); MessageBox.Show("DB接続に成功しました!!"); this.sqliteConnectableDbPath.Text = sb.ToString(); this.sqliteSaveBtn.Visible = true; this.sqliteDbFilePath.Enabled = false; } } catch (Exception ex) { MessageBox.Show("DB接続失敗"); common.resetDbContextPath(dbConnectionString, dbProviderName); } }