public static int Main(string[] args)
{
// DBConのデバッグログをコンソールに出す
DBCon.UseConsoleLog = true;
// SCSLibのOpeLogを使ってデバッグログを出力する場合は、
// DBCon.UseDebugLog = true;
// DBと接続する。
using (DBCon db = new DBCon(DBCon.Type.PostgreSQL, // データベースの種類
"localhost", // データベースサーバ
"mydb", // データベース名
"myuser", // 接続ユーザ名
"mypass")){ // 接続パスワード
// SQL文を実行
db.Execute("CREATE TABLE IF NOT EXISTS サンプルテーブル (ID INTEGER, 名前 VARCHAR(32), 電話番号 VARCHAR(16))");
// INSERT文の実行例
int n;
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO サンプルテーブル");
sql.Append(" (ID,名前,電話番号)");
sql.Append(" VALUES (1,'テスト一郎','03-4567-8901')");
n = db.Execute(sql); // StringBuilderを直接渡すことができる。
Console.WriteLine("{0}件登録しました", n);
// usingを使っているので明示的にクローズする必要は無いが、念のため
db.Close();
}
return(0);
}
protected void signUp_Click(object sender, EventArgs e)
{
if ((newPass.Text.Length != 0) && (newId.Text.Length != 0))
{
string sql2 = string.Format("SELECT Username FROM users WHERE Username = '******'", newId.Text);
var con = new DBCon();
var reader = con.Execute(sql2);
RegexUtilities util = new RegexUtilities();
bool check = util.IsValidEmail(newEmailid.Text);
if (!reader.HasRows && check)
{
con.Close();
string sql = string.Format("INSERT INTO users ([Username], [Password], [email]) VALUES ('{0}', '{1}', '{2}')", newId.Text, newPass.Text, newEmailid.Text);
con.Execute(sql);
checking.Text = "Account Created Successfully!";
con.Close();
}
else if (reader.HasRows)
{
checking.Text = "Username Already Exists!";
con.Close();
}
else if (!check)
{
checking.Text = "Invalid Email ID!";
con.Close();
}
con.Close();
}
else
{
checking.Text = "Invalid Username or Password!";
}
}
protected void submit_Click(object sender, EventArgs e)
{
DateTime t = System.DateTime.Now;
string time = t.ToString();
int i = 0;
if(reminderName.Text != "")
{
int id = Convert.ToInt32(Request.QueryString["id"]);
string id1 = id.ToString();
var con = new DBCon();
string sql = string.Format("UPDATE Reminders SET name = '{0}' WHERE ID = {1}", reminderName.Text, id);
var reader = con.Execute(sql);
con.Close();
var con2 = new DBCon();
string sql2 = string.Format("UPDATE Reminders SET reminder = '{0}' WHERE ID = {1}", reminderBody.Text, id);
var reader2 = con2.Execute(sql2);
con2.Close();
Response.Redirect("success.aspx");
}
else
errorLabelreminder.Text = "*Enter Title!";
}
protected void submit_Click(object sender, EventArgs e)
{
DateTime t = System.DateTime.Now;
string time = t.ToString();
if (noteName.Text != "")
{
int id = Convert.ToInt32(Request.QueryString["id"]);
var con = new DBCon();
string sql = string.Format("UPDATE Notes SET noteTitle = '{0}' WHERE ID = {1}", noteName.Text, id);
var reader = con.Execute(sql);
con.Close();
var con2 = new DBCon();
string sql2 = string.Format("UPDATE Notes SET note2 = '{0}' WHERE ID = {1}", noteBody.Text, id);
var reader2 = con2.Execute(sql2);
con2.Close();
var con3 = new DBCon();
string sql3 = string.Format("UPDATE Notes SET noteTime = '{0}' WHERE ID = {1}", time, id);
var reader3 = con3.Execute(sql3);
con3.Close();
errorLabelNotes.Text = sql2;
Response.Redirect("Success.aspx");
}
else
{
errorLabelNotes.Text = "*Enter Title!";
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["Username"] == null)
{
Response.Redirect("redirectionpage.aspx"); // Help From http://stackoverflow.com/questions/12219246/submit-show-results-delay-3-seconds-and-redirect
}
;
mainPageUsername.Text = Session["Username"].ToString();
string sql = string.Format("SELECT * FROM Alarms WHERE userID={0}", Session["ID"]);
var db = new DBCon();
var reader = db.Execute(sql);
var sb = new StringBuilder();
while (reader.Read())
{
sb.AppendLine("<tr>");
var time = Convert.ToDateTime(reader["time"].ToString());
sb.AppendLine(string.Format("<td class=\"time\">{0}</td>", time.ToLongTimeString()));
sb.AppendLine("</tr>");
}
lbl_table.Text = sb.ToString();
db.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
var con = new DBCon();
string sql = string.Format("SELECT ID, Username, Password FROM users WHERE Username = '******' AND Password = '******'", loginId.Text, loginPass.Text);
var reader = con.Execute(sql);
while (reader.Read())
{
Session["ID"] = reader["ID"];
Session["Password"] = reader["Password"];
}
if (reader.HasRows)
{
checking.Text = "Success!";
Session["Username"] = loginId.Text;
con.Close();
con.Close();
Response.Redirect("mainpage.aspx");
}
else
{
checking.Text = "Invalid Username or Password!";
con.Close();
con.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
var con = new DBCon();
string sql = string.Format("SELECT ID, Username, Password FROM users WHERE Username = '******' AND Password = '******'", loginId.Text, loginPass.Text);
var reader = con.Execute(sql);
while (reader.Read())
{
Session["ID"] = reader["ID"];
Session["Password"] = reader["Password"];
}
if (reader.HasRows)
{
checking.Text = "Success!";
Session["Username"] = loginId.Text;
con.Close();
con.Close();
Response.Redirect("mainpage.aspx");
}
else
{
checking.Text = "Invalid Username or Password!";
con.Close();
con.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Session["Username"] == null)
{
Response.Redirect("redirectionpage.aspx"); // Help From http://stackoverflow.com/questions/12219246/submit-show-results-delay-3-seconds-and-redirect
};
mainPageUsername.Text = Session["Username"].ToString();
int id = Convert.ToInt32(Request.QueryString["id"]);
var db = new DBCon();
var reader = db.Execute(string.Format("SELECT * FROM Notes WHERE ID={0}", id));
while (reader.Read())
{
noteName.Text = reader["noteTitle"].ToString();
nn = reader["noteTitle"].ToString();
ID = id;
noteBody.Text = reader["note2"].ToString();
DateTime.Text = reader["noteTime"].ToString();
}
db.Close();
}
}
protected void submit_Click(object sender, EventArgs e)
{
DateTime t = System.DateTime.Now;
string time = t.ToString();
int i = 0;
if (reminderName.Text != "")
{
int id = Convert.ToInt32(Request.QueryString["id"]);
string id1 = id.ToString();
var con = new DBCon();
string sql = string.Format("UPDATE Reminders SET name = '{0}' WHERE ID = {1}", reminderName.Text, id);
var reader = con.Execute(sql);
con.Close();
var con2 = new DBCon();
string sql2 = string.Format("UPDATE Reminders SET reminder = '{0}' WHERE ID = {1}", reminderBody.Text, id);
var reader2 = con2.Execute(sql2);
con2.Close();
Response.Redirect("success.aspx");
}
else
{
errorLabelreminder.Text = "*Enter Title!";
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Session["Username"] == null)
{
Response.Redirect("redirectionpage.aspx"); // Help From http://stackoverflow.com/questions/12219246/submit-show-results-delay-3-seconds-and-redirect
}
;
mainPageUsername.Text = Session["Username"].ToString();
int id = Convert.ToInt32(Request.QueryString["id"]);
var db = new DBCon();
var reader = db.Execute(string.Format("SELECT * FROM Notes WHERE ID={0}", id));
while (reader.Read())
{
noteName.Text = reader["noteTitle"].ToString();
nn = reader["noteTitle"].ToString();
ID = id;
noteBody.Text = reader["note2"].ToString();
DateTime.Text = reader["noteTime"].ToString();
}
db.Close();
}
}
protected void reminderSubmitButton_Click(object sender, EventArgs e)
{
int i = 0;
if (newReminderTime.Text != "")
{
DateTime t1 = DateTime.Now; //http://forums.asp.net/t/1025431.aspx?How+to+compare+Time+in+C+
DateTime t2 = Convert.ToDateTime(string.Format("{0} {1}", newReminderDate.Text.ToString(), newReminderTime.Text.ToString())); //("{ 0:0000}", value)
i = DateTime.Compare(t1, t2);
}
if (newReminderName.Text != "" && i < 0 && newReminderTime.Text != "")
{
var con = new DBCon();
string sql = string.Format("INSERT INTO Reminders ( [name], [reminder],[time],[userID] ) VALUES ( '{0}', '{1}', '{2} {3}',{4})", newReminderName.Text, newReminder.Text, newReminderDate.Text, newReminderTime.Text, Session["ID"]);
var reader = con.Execute(sql);
con.Close();
Response.Redirect("reminderSuccess.aspx");
}
else if (newReminderName.Text == "")
{
errorLabelReminder.Text = "*Enter Name!";
}
else if (i >= 0)
{
errorLabelReminder.Text = "*Enter correct Time!";
}
else
{
errorLabelReminder.Text = "*Enter correct Time!";
}
}
protected void newUsernameSubmit_Click(object sender, EventArgs e)
{
string sql2 = string.Format("UPDATE users SET Username = '******' WHERE ID = {1}", newUserName.Text, Session["ID"]);
var db2 = new DBCon();
var reader2 = db2.Execute(sql2);
db2.Close();
Session.RemoveAll();
Response.Redirect("Default.aspx");
}
protected void delete_Click(object sender, EventArgs e)
{
int id = Convert.ToInt32(Request.QueryString["id"]);
var con = new DBCon();
string sql = string.Format("DELETE FROM Reminders WHERE ID={0}", id);
var reader = con.Execute(sql);
con.Close();
Response.Redirect("success.aspx");
}
protected void newUsernameSubmit_Click(object sender, EventArgs e)
{
string sql2 = string.Format("UPDATE users SET Username = '******' WHERE ID = {1}", newUserName.Text, Session["ID"]);
var db2 = new DBCon();
var reader2 = db2.Execute(sql2);
db2.Close();
Session.RemoveAll();
Response.Redirect("Default.aspx");
}
protected void alarmSubmitButton_Click(object sender, EventArgs e)
{
if (newAlarmTime.Text != "")
{
var con = new DBCon();
string sql = string.Format("INSERT INTO Alarms ( [time],[userID] ) VALUES ('{0}', {1})", newAlarmTime.Text, Session["ID"]);
var reader = con.Execute(sql);
con.Close();
Response.Redirect("alarmSuccess.aspx");
}
}
protected void signUp_Click(object sender, EventArgs e)
{
if ((newPass.Text.Length != 0) && (newId.Text.Length != 0))
{
string sql2 = string.Format("SELECT Username FROM users WHERE Username = '******'", newId.Text);
var con = new DBCon();
var reader = con.Execute(sql2);
RegexUtilities util = new RegexUtilities();
bool check= util.IsValidEmail(newEmailid.Text);
if (!reader.HasRows && check)
{
con.Close();
string sql = string.Format("INSERT INTO users ([Username], [Password], [email]) VALUES ('{0}', '{1}', '{2}')", newId.Text, newPass.Text,newEmailid.Text);
con.Execute(sql);
checking.Text = "Account Created Successfully!";
con.Close();
}
else if(reader.HasRows)
{
checking.Text = "Username Already Exists!";
con.Close();
}
else if (!check)
{
checking.Text = "Invalid Email ID!";
con.Close();
}
con.Close();
}
else
{
checking.Text = "Invalid Username or Password!";
}
}
protected void deleteAccButton_Click(object sender, EventArgs e)
{
if (password.Text == Session["Password"].ToString())
{
var con = new DBCon();
string sql = string.Format("DELETE FROM Notes WHERE userID={0}", Session["ID"]);
var reader = con.Execute(sql);
con.Close();
string sql2 = string.Format("DELETE FROM Reminders WHERE userID={0}", Session["ID"]);
reader = con.Execute(sql2);
con.Close();
string sql3 = string.Format("DELETE FROM Alarms WHERE userID={0}", Session["ID"]);
reader = con.Execute(sql3);
con.Close();
string sql4 = string.Format("DELETE FROM users WHERE ID={0}", Session["ID"]);
reader = con.Execute(sql4);
con.Close();
Response.Redirect("Default.aspx");
}
else
{
errorDeleteAcc.Text = "Invalid Password!";
}
}
protected void noteSubmitButton_Click(object sender, EventArgs e)
{
DateTime t = DateTime.Now;
string time = t.ToString();
if (newNoteName.Text != "")
{
var con = new DBCon();
string sql = string.Format("INSERT INTO Notes ([noteTitle], [note2], [noteTime], [userID]) VALUES ('{0}', '{1}', '{2}',{3})", newNoteName.Text, newNote.Text, time, Session["ID"]);
var reader = con.Execute(sql);
con.Close();
Response.Redirect("noteSuccess.aspx");
}
else
{
errorLabelNotes.Text = "*Enter Title!";
}
}
public OperationResult DeleteInfoByIDNo(int Id)
{
var result = new OperationResult();
try
{
var mssql = new DBCon();
var parameters = new List <SqlParameter> {
new SqlParameter("@IDNo", Id)
};
result = mssql.Execute(parameters, "prc_EntityDelete");
}
catch (SqlException ex)
{
result.Success = false;
result.Add(ex.Message);
}
return(result);
}
protected void noteSubmitButton_Click(object sender, EventArgs e)
{
DateTime t = DateTime.Now;
string time = t.ToString();
if (newNoteName.Text != "")
{
var con = new DBCon();
string sql = string.Format("INSERT INTO Notes ([noteTitle], [note2], [noteTime], [userID]) VALUES ('{0}', '{1}', '{2}',{3})", newNoteName.Text, newNote.Text,time, Session["ID"]);
var reader = con.Execute(sql);
con.Close();
Response.Redirect("noteSuccess.aspx");
}
else
errorLabelNotes.Text = "*Enter Title!";
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["Username"] == null)
{
Response.Redirect("redirectionpage.aspx"); // Help From http://stackoverflow.com/questions/12219246/submit-show-results-delay-3-seconds-and-redirect
};
mainPageUsername.Text = Session["Username"].ToString();
string sql = string.Format("SELECT * FROM Reminders WHERE userID={0}", Session["ID"]);
var db = new DBCon();
var reader = db.Execute(sql);
var sb = new StringBuilder();
while (reader.Read())
{
sb.AppendLine("<tr>");
sb.AppendLine(string.Format("<td><a href='viewreminder.aspx?id={0}'>{1}</a></td>", reader["ID"], reader["name"]));
sb.AppendLine("</tr>");
}
lbl_table.Text = sb.ToString();
db.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["Username"] == null)
{
Response.Redirect("redirectionpage.aspx"); // Help From http://stackoverflow.com/questions/12219246/submit-show-results-delay-3-seconds-and-redirect
};
mainPageUsername.Text = Session["Username"].ToString();
string sql = string.Format("SELECT * FROM Alarms WHERE userID={0}", Session["ID"]);
var db = new DBCon();
var reader = db.Execute(sql);
var sb = new StringBuilder();
while (reader.Read())
{
sb.AppendLine("<tr>");
var time = Convert.ToDateTime(reader["time"].ToString());
sb.AppendLine(string.Format("<td class=\"time\">{0}</td>", time.ToLongTimeString()));
sb.AppendLine("</tr>");
}
lbl_table.Text = sb.ToString();
db.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["Username"] == null)
{
Response.Redirect("redirectionpage.aspx"); // Help From http://stackoverflow.com/questions/12219246/submit-show-results-delay-3-seconds-and-redirect
}
;
mainPageUsername.Text = Session["Username"].ToString();
string sql = string.Format("SELECT * FROM Reminders WHERE userID={0}", Session["ID"]);
var db = new DBCon();
var reader = db.Execute(sql);
var sb = new StringBuilder();
while (reader.Read())
{
sb.AppendLine("<tr>");
sb.AppendLine(string.Format("<td><a href='viewreminder.aspx?id={0}'>{1}</a></td>", reader["ID"], reader["name"]));
sb.AppendLine("</tr>");
}
lbl_table.Text = sb.ToString();
db.Close();
}
protected void reminderSubmitButton_Click(object sender, EventArgs e)
{
int i = 0;
if (newReminderTime.Text != "")
{
DateTime t1 = DateTime.Now; //http://forums.asp.net/t/1025431.aspx?How+to+compare+Time+in+C+
DateTime t2 = Convert.ToDateTime(string.Format("{0} {1}", newReminderDate.Text.ToString(), newReminderTime.Text.ToString())); //("{ 0:0000}", value)
i = DateTime.Compare(t1, t2);
}
if (newReminderName.Text != ""&& i < 0 && newReminderTime.Text != "")
{
var con = new DBCon();
string sql = string.Format("INSERT INTO Reminders ( [name], [reminder],[time],[userID] ) VALUES ( '{0}', '{1}', '{2} {3}',{4})", newReminderName.Text, newReminder.Text, newReminderDate.Text, newReminderTime.Text, Session["ID"]);
var reader = con.Execute(sql);
con.Close();
Response.Redirect("reminderSuccess.aspx");
}
else if (newReminderName.Text == "")
{
errorLabelReminder.Text = "*Enter Name!";
}
else if (i >= 0)
{
errorLabelReminder.Text = "*Enter correct Time!";
}
else
errorLabelReminder.Text = "*Enter correct Time!";
}
protected void submit_Click(object sender, EventArgs e)
{
DateTime t = System.DateTime.Now;
string time = t.ToString();
if (noteName.Text != "")
{
int id = Convert.ToInt32(Request.QueryString["id"]);
var con = new DBCon();
string sql = string.Format("UPDATE Notes SET noteTitle = '{0}' WHERE ID = {1}", noteName.Text, id);
var reader = con.Execute(sql);
con.Close();
var con2 = new DBCon();
string sql2 = string.Format("UPDATE Notes SET note2 = '{0}' WHERE ID = {1}", noteBody.Text, id);
var reader2= con2.Execute(sql2);
con2.Close();
var con3 = new DBCon();
string sql3 = string.Format("UPDATE Notes SET noteTime = '{0}' WHERE ID = {1}", time, id);
var reader3 = con3.Execute(sql3);
con3.Close();
errorLabelNotes.Text = sql2;
Response.Redirect("Success.aspx");
}
else
errorLabelNotes.Text = "*Enter Title!";
}
protected void delete_Click(object sender, EventArgs e)
{
int id = Convert.ToInt32(Request.QueryString["id"]);
var con = new DBCon();
string sql = string.Format("DELETE FROM Notes WHERE ID={0}", id );
var reader = con.Execute(sql);
con.Close();
Response.Redirect("success.aspx");
}
protected void deleteAccButton_Click(object sender, EventArgs e)
{
if (password.Text == Session["Password"].ToString())
{
var con = new DBCon();
string sql = string.Format("DELETE FROM Notes WHERE userID={0}", Session["ID"]);
var reader = con.Execute(sql);
con.Close();
string sql2 = string.Format("DELETE FROM Reminders WHERE userID={0}", Session["ID"]);
reader = con.Execute(sql2);
con.Close();
string sql3 = string.Format("DELETE FROM Alarms WHERE userID={0}", Session["ID"]);
reader = con.Execute(sql3);
con.Close();
string sql4 = string.Format("DELETE FROM users WHERE ID={0}", Session["ID"]);
reader = con.Execute(sql4);
con.Close();
Response.Redirect("Default.aspx");
}
else
errorDeleteAcc.Text = "Invalid Password!";
}
protected void alarmSubmitButton_Click(object sender, EventArgs e)
{
if (newAlarmTime.Text != "")
{
var con = new DBCon();
string sql = string.Format("INSERT INTO Alarms ( [time],[userID] ) VALUES ('{0}', {1})", newAlarmTime.Text, Session["ID"]);
var reader = con.Execute(sql);
con.Close();
Response.Redirect("alarmSuccess.aspx");
}
}
