// Klasa zawierająca metody obsługujące operacje na bazie danych
public List <List <string> > LoadData(string table, List <string> columnsName)
{
//Nawiązanie połączenia z bazą danych
var auth = new Authentication();
auth.getConnection();
using (SQLiteConnection con = new SQLiteConnection(auth.connectionString))
{
List <List <string> > RecordsList = new List <List <string> >();
List <string> ValueList = new List <string>();
con.Open();
SQLiteCommand cmd = new SQLiteCommand();
int Count = columnsName.Count();
string query = @"SELECT " + columnsName[0];
if (Count > 1)
{
for (int i = 1; i < Count; i++)
{
query += "," + columnsName[i];
}
}
query += " FROM " + table;
cmd.CommandText = query;
cmd.Connection = con;
SQLiteDataReader read = cmd.ExecuteReader();
while (read.Read())
{
int VariableCount = read.FieldCount;
for (int i = 0; i < VariableCount; i++)
{
Type t = typeof(System.Int64);
Type d = typeof(System.DateTime);
if (read.GetFieldType(i).Equals(t))
{
ValueList.Add(read.GetFieldValue <long>(i).ToString());
}
else if (read.GetFieldType(i).Equals(d))
{
ValueList.Add(read.GetFieldValue <DateTime>(i).ToString());
}
else
{
ValueList.Add(read.GetFieldValue <string>(i));
}
}
RecordsList.Add(ValueList);
ValueList = new List <string>();
}
return(RecordsList);
}
}
/// <summary>
/// 日付範囲による検索
/// </summary>
/// <param name="daoAttendanceList">検索結果格納用変数</param>
/// <param name="fromDate">検索開始範囲</param>
/// <param name="toDate">検索終了範囲</param>
/// <param name="statusCode">ステータスコード</param>
/// <returns>エラーメッセージ 正常終了の場合空文字</returns>
public static string SelectFromTo(ref List <DaoAttendance> daoAttendanceList, DateTime fromDate, DateTime toDate, int statusCode)
{
string errMes = "";
try
{
using (SQLiteConnection con = new SQLiteConnection("Data Source=" + DB_FILE_NAME))
{
con.Open();
using (SQLiteCommand cmd = con.CreateCommand())
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT");
sql.AppendLine(" *");
sql.AppendLine("FROM " + TABLE_NAME);
sql.AppendLine("WHERE 1=1");
sql.AppendLine(" AND REPLACE([record_date],'/','-') >= @startDate");
sql.AppendLine(" AND REPLACE([record_date],'/','-') <= @endDate");
//MEMO:データの投入形式がyyyy/MM/ddとなっているため、REPLACEしないとWHERE文にマッチしない
cmd.Parameters.Add(new SQLiteParameter("startDate", fromDate));
cmd.Parameters.Add(new SQLiteParameter("endDate", toDate));
//ステータス指定時のみ追加
if (statusCode == 1 || statusCode == 2)
{
sql.AppendLine(" AND [to_work] = @status");
cmd.Parameters.Add(new SQLiteParameter("status", statusCode));
}
cmd.CommandText = sql.ToString();
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DaoAttendance attendance = new DaoAttendance();
attendance.recordDate = reader.GetFieldValue <string>(reader.GetOrdinal("record_date"));
attendance.toWork = reader.GetFieldValue <long> (reader.GetOrdinal("to_work"));
daoAttendanceList.Add(attendance);
}
}
}
}
catch (Exception e)
{
errMes = e.ToString();
}
return(errMes);
}
// SWprawdzanie poprawnosci logowania dla lekarza
public int ChecKDoctor(string username, string password)
{
//Nawiązanie połączenia z bazą danych
var auth = new Authentication();
auth.getConnection();
using (SQLiteConnection con = new SQLiteConnection(auth.connectionString))
{
long NumerID = 0;
con.Open();
SQLiteCommand cmd = new SQLiteCommand();
string query = @"SELECT * FROM Lekarz WHERE Login='******' and Haslo='" + password + "'";
int count = 0;
cmd.CommandText = query;
cmd.Connection = con;
SQLiteDataReader read = cmd.ExecuteReader();
while (read.Read())
{
count++;
NumerID = read.GetFieldValue <Int64>(0);
}
if (count == 1)
{
return((int)NumerID);
}
else
{
return(0);
}
}
}
public List <Row> Read(string sTable, int iMinId, int iMaxId)
{
Row NewRow;
List <Row> ltReturn = new List <Row>();
if (Open())
{
using (SQLiteCommand Command = new SQLiteCommand("SELECT id, data FROM " + sTable + " WHERE id>=" + iMinId.ToString() + " AND id <=" + iMaxId.ToString(), _DatabaseConnection))
{
using (SQLiteDataReader Reader = Command.ExecuteReader())
{
while (Reader.Read())
{
NewRow = new Row
{
iId = Reader.GetFieldValue <int>(0),
abData = GetBytes(Reader, 1)
};
ltReturn.Add(NewRow);
}
}
}
}
return(ltReturn);
}
static public string Select(ref List <DaoTodo> daoTodos)
{
string errMes = "";
try
{
//DB接続の作成
using (SQLiteConnection con = new SQLiteConnection("Data Source=" + DB_FILE_NAME))
{
//DB接続開始
con.Open();
//コマンドの生成
using (SQLiteCommand cmd = con.CreateCommand())
{
//SQL文の生成
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT * FROM " + table_name);
//コマンドセット
cmd.CommandText = sql.ToString();
//コマンド実行
SQLiteDataReader reader = cmd.ExecuteReader();
//データ取得
while (reader.Read())
{
//データ取得処理
DaoTodo daoTodo = new DaoTodo();
daoTodo.title = reader.GetFieldValue <string>(reader.GetOrdinal("title"));
daoTodo.description = reader.GetFieldValue <string>(reader.GetOrdinal("description"));
daoTodo.category = reader.GetFieldValue <string>(reader.GetOrdinal("category"));
daoTodo.is_done = reader.GetFieldValue <long> (reader.GetOrdinal("is_done"));
//long型でキャストすることで一旦解決。メモリ節約のため値の持ち方は検討。
daoTodos.Add(daoTodo);
}
}
}
}
catch (Exception e)
{
errMes = e.ToString();
}
return(errMes);
}
/// <summary>
/// 記録日付の新しいデータを上限付きで取得
/// </summary>
/// <param name="daoAttendanceList">結果受け取りリスト</param>
/// <param name="limit">LIMIT句で指定する行数</param>
/// <returns>エラーメッセージ 正常終了の場合空文字</returns>
public static string SelectAll(ref List <DaoAttendance> daoAttendanceList, int limit)
{
string errMes = "";
try
{
using (SQLiteConnection con = new SQLiteConnection("Data Source=" + DB_FILE_NAME))
{
con.Open();
using (SQLiteCommand cmd = con.CreateCommand())
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT");
sql.AppendLine(" *");
sql.AppendLine("FROM " + TABLE_NAME);
sql.AppendLine("ORDER BY");
sql.AppendLine(" DATE(REPLACE(record_date,'/','-')) DESC,");
sql.AppendLine(" TIME(REPLACE(record_date,'/','-')) DESC");
//MEMO:データの投入形式がyyyy/MM/ddとなっているため、REPLACEしないとORDER BY文にマッチしない
sql.AppendLine("LIMIT " + limit);
cmd.CommandText = sql.ToString();
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DaoAttendance attendance = new DaoAttendance();
attendance.recordDate = reader.GetFieldValue <string>(reader.GetOrdinal("record_date"));
attendance.toWork = reader.GetFieldValue <long> (reader.GetOrdinal("to_work"));
daoAttendanceList.Add(attendance);
}
}
}
}
catch (Exception e)
{
errMes = e.ToString();
}
return(errMes);
}
public User Get(string name = null, long id = 0)
{
User user = null;
ExecuteCommandInConnection((command) =>
{
command.CommandText = name != null ? $"select * from Users where Name = '{name}'" : $"select * from Users where Id = '{id}'";
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
user = new User()
{
Id = reader.GetFieldValue <long>(0),
Name = reader.GetFieldValue <string>(1),
LastTimeLoginId = reader.GetValue(3) is DBNull ? 0 : (long)reader.GetValue(3)
};
}
reader.Close();
});
return(user);
}
public PackageSourceData(SQLiteDataReader reader)
{
this.Name = reader.GetString(0);
this.FeedUrl = reader.GetString(1);
if (!reader.IsDBNull(2))
{
this.UserName = reader.GetString(2);
}
var blob = !reader.IsDBNull(3) ? reader.GetFieldValue <byte[]>(3) : null;
if (blob != null)
{
this.Password = AH.CreateSecureString(InedoLib.UTF8Encoding.GetString(ProtectedData.Unprotect(blob, null, RompConfig.UserMode ? DataProtectionScope.CurrentUser : DataProtectionScope.LocalMachine)));
}
}
private void MainForm_Load(object sender, EventArgs e)
{
//TODO - If database not found, create new and import defaults from defaults.xml
//if defaults.xml is not found create blank defaults table
//check if sqlite database exists
if (!System.IO.File.Exists(dbFilename))
{
MessageBox.Show("Database not found. A blank database has been created but does not contain the 'defaults' spellbook.");
SQLiteConnection.CreateFile(dbFilename);
}
//establish connection
dbConn = new SQLiteConnection("Data Source=" + dbFilename + "; Version=3;");
dbConn.Open();
command = new SQLiteCommand(dbConn);
//create spellbooks table if not exists
command.CommandText = "CREATE TABLE IF NOT EXISTS 'spellbooks' (name varchar(30) NOT NULL UNIQUE)";
command.ExecuteNonQuery();
command.CommandText = "INSERT OR IGNORE INTO spellbooks (name) VALUES ('defaults')";
command.ExecuteNonQuery();
//create defaults table if not exists
command.CommandText = "CREATE TABLE IF NOT EXISTS 'defaults' " + dbColNamesSQLVerbose;
command.ExecuteNonQuery();
//populate comboBox1 contents
spellbooks = new List <string>();
command.CommandText = "SELECT name FROM spellbooks;";
try
{
SQLiteDataReader rdr = command.ExecuteReader();
while (rdr.Read())
{
spellbooks.Add(rdr.GetFieldValue <string>(0));
}
sbBookNamesCoBx.DataSource = spellbooks;
}
catch (SQLiteException ex)
{
MessageBox.Show("Error: The database table \"spellbooks\" could not be accessed.\n\nDetails:\n" + ex.ToString());
}
}
public List <string> GetTableNames()
{
List <string> ltReturn = new List <string>();
if ((_DatabaseConnection != null) && !string.IsNullOrEmpty(_sPath) && (File.Exists(_sPath)))
{
using (SQLiteCommand Command = new SQLiteCommand("SELECT name FROM sqlite_master WHERE type='table';", _DatabaseConnection))
{
if (Open())
{
using (SQLiteDataReader Reader = Command.ExecuteReader())
{
while (Reader.Read())
{
ltReturn.Add(Reader.GetFieldValue <string>(0));
}
}
Close();
}
}
}
return(ltReturn);
}
public List<UserModel> GetUsers(string name)
{
var users = new List<UserModel>();
try
{
string sql = name.ToLower().Equals(DbConstants.ALL_USERS_KEY.ToLower()) ? DbConstants.GET_ALL_USERS : DbConstants.GET_USER_BY_NAME;
using (SQLiteConnection con = new SQLiteConnection(DbConstants.CONNECTION_STRING))
{
con.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
cmd.Prepare();
cmd.Parameters.Clear();
if (!name.ToLower().Equals(DbConstants.ALL_USERS_KEY.ToLower())) cmd.Parameters.AddWithValue("@name", name);
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
if (!rdr.HasRows) return null;
while (rdr.Read())
{
users.Add(new UserModel
{
Uid = rdr.GetInt32(1),
Name = rdr.GetString(2),
Face = rdr.GetFieldValue<byte[]>(3)
});
}
}
}
con.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("Poop, " + ex.Message);
}
return users;
}
public T Get <T>(int i)
{
return(cursor.GetFieldValue <T>(i));
}
public static T GetField <T>(this SQLiteDataReader reader, string label)
{
// Inefficient, but right now I don't care.
return(reader.GetFieldValue <T>(reader.GetOrdinal(label)));
}
