// 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))); }