/// <summary>
/// 解答候補の取得
/// </summary>
/// <param name="lvl"></param>
/// <param name="type"></param>
/// <returns></returns>
internal DataTable GetAnswers(string lvl, string type, string mode = null)
{
string whereLvl = "<= @lvl";
if ("一致".Equals(mode))
{
whereLvl = "= @lvl";
}
using (ISQLManager manager = SQLManager.GetInterface(YONTAKU_DATABASE_FILE))
{
string sql = $@"
-- ランダムな行番号を取得
WITH with_random_id(rowid) AS (
SELECT
abs(random() %(SELECT max(rowid) FROM answer))
UNION ALL
SELECT
abs(random() %(SELECT max(rowid) FROM answer))
FROM
answer
)
SELECT
*
FROM
answer JOIN (
-- 重複を避けて取得
SELECT DISTINCT
rowid
FROM
with_random_id
LIMIT
1000
)
ON answer.rowid == rowid
WHERE
lvl {whereLvl}
AND type = @type
LIMIT
100;
";
Dictionary <string, dynamic> parameters = new Dictionary <string, dynamic>
{
{ "lvl", lvl },
{ "type", type },
};
return(manager.Select(sql, parameters));
}
}
/// <summary>
/// 問題の取得
/// </summary>
/// <param name="lvl"></param>
/// <param name="type"></param>
/// <param name="mode">モード</param>
/// <returns></returns>
internal DataTable GetMondai(string lvl, string type, string mode = null)
{
// 最大100件をランダムに抽出
// 乱数次第で取得件数が変動するので要注意
using (ISQLManager manager = SQLManager.GetInterface(YONTAKU_DATABASE_FILE))
{
string whereLvl = "<= @lvl";
if ("一致".Equals(mode))
{
whereLvl = "= @lvl";
}
string sql = $@"
WITH with_random_id(rowid) AS (
SELECT
abs(random() %(SELECT max(rowid) FROM yontaku))
UNION ALL
SELECT
abs(random() %(SELECT max(rowid) FROM yontaku))
FROM
yontaku
)
SELECT
*
FROM
yontaku JOIN (
SELECT DISTINCT
rowid
FROM
with_random_id
LIMIT
1000
)
ON yontaku.rowid == rowid
WHERE
lvl {whereLvl}
AND type = @type
LIMIT
100;
";
Dictionary <string, dynamic> parameters = new Dictionary <string, dynamic>
{
{ "lvl", lvl },
{ "type", type },
};
return(manager.Select(sql, parameters));
}
}
protected void ButtonLogin_Click(object sender, EventArgs e)
{
bool isLogin = false;
try
{
//string path = Server.MapPath("./sqlite/");
//string dataSource = Path.Combine(path, "WebApp.db");
//using (SQLiteUtility util = new SQLiteUtility(dataSource))
//{
// util.Connect();
// string sql = "SELECT * FROM userinfo WHERE userid = @userid";
// string userid = TextBoxId.Text;
// Dictionary<string, dynamic> parameters = new Dictionary<string, dynamic>
// {
// { "userid", userid },
// };
// DataTable dataTable = util.Fill(sql, parameters);
// if (dataTable.Rows.Count > 0)
// {
// if (dataTable.Rows[0]["password"].ToString().Equals(TextBoxPw.Text))
// {
// LabelMessage.Text = "認証に成功しました。";
// SessionManager.UserInfo userInfo = new SessionManager.UserInfo(Session)
// {
// UserId = userid,
// };
// Server.Transfer("~/Menu.aspx", false);
// }
// else
// {
// LabelMessage.Text = "パスワードが違います。";
// }
// }
// else
// {
// LabelMessage.Text = "ユーザーが未登録です。";
// }
//}
using (ISQLManager manager = SQLManager.GetInterface("WebApp.db"))
{
string sql = "SELECT * FROM userinfo WHERE userid = @userid";
string userid = TextBoxId.Text;
Dictionary <string, dynamic> parameters = new Dictionary <string, dynamic>
{
{ "userid", userid },
};
DataTable dataTable = manager.Select(sql, parameters);
if (dataTable.Rows.Count > 0)
{
if (dataTable.Rows[0]["password"].ToString().Equals(TextBoxPw.Text))
{
LabelMessage.Text = "認証に成功しました。";
SessionManager.UserInfo userInfo = new SessionManager.UserInfo(Session)
{
UserId = userid,
};
isLogin = true;
}
else
{
LabelMessage.Text = "パスワードが違います。";
}
}
else
{
LabelMessage.Text = "ユーザーが未登録です。";
}
}
}
catch (Exception ex)
{
LabelMessage.Text = ex.Message;
Logger.GetInstance(GetType().Name).WriteException(MethodBase.GetCurrentMethod().Name, ex);
}
if (isLogin)
{
Server.Transfer("~/Menu.aspx", false);
}
}