public static int Insert(SQLiteConnection conn, UserBoss um) { if (um == null) { return(-2); } using (SQLiteCommand cmd = new SQLiteCommand(insertU, conn)) { cmd.Parameters.AddWithValue("@FirstName", um.FirstName); cmd.Parameters.AddWithValue("@LastName", um.LastName); cmd.Parameters.AddWithValue("@Email", um.Email); cmd.Parameters.AddWithValue("@Phone", um.Phone); cmd.Parameters.AddWithValue("@Street", um.Street); cmd.Parameters.AddWithValue("@City", um.City); cmd.Parameters.AddWithValue("@Zip", um.Zip); cmd.Parameters.AddWithValue("@Active", um.Active); try { cmd.ExecuteNonQuery(); } catch (Exception e) { Trace.WriteLine($"EXCEPTION: UserBossDbMapper.Insert: {e.Message}"); return(-1); } } long?lastId; using (SQLiteCommand cmd = new SQLiteCommand(@"select last_insert_rowid()", conn)) { lastId = (long?)cmd.ExecuteScalar(); } if (lastId == null) { return(-1); } using (SQLiteCommand cmd = new SQLiteCommand(insertUB, conn)) { cmd.Parameters.AddWithValue("@UserId", lastId); cmd.Parameters.AddWithValue("@Login", um.Login); cmd.Parameters.AddWithValue("@Password", um.Password); try { cmd.ExecuteNonQuery(); } catch (Exception e) { Trace.WriteLine($"EXCEPTION: UserBossDbMapper.Insert: {e.Message}"); return(-1); } } return(0); }
public static int Update(SQLiteConnection conn, UserBoss um) { if (um == null) { return(-2); } if (um.Id < 0) { return(-3); } using (SQLiteCommand cmd = new SQLiteCommand(updateU, conn)) { cmd.Parameters.AddWithValue("@Id", um.Id); cmd.Parameters.AddWithValue("@FirstName", um.FirstName); cmd.Parameters.AddWithValue("@LastName", um.LastName); cmd.Parameters.AddWithValue("@Email", um.Email); cmd.Parameters.AddWithValue("@Phone", um.Phone); cmd.Parameters.AddWithValue("@Street", um.Street); cmd.Parameters.AddWithValue("@City", um.City); cmd.Parameters.AddWithValue("@Zip", um.Zip); cmd.Parameters.AddWithValue("@Active", um.Active); try { cmd.ExecuteNonQuery(); } catch (Exception e) { Trace.WriteLine($"EXCEPTION: UserBossDbMapper.Update: {e.Message}"); return(-1); } } using (SQLiteCommand cmd = new SQLiteCommand(updateUB, conn)) { cmd.Parameters.AddWithValue("@Login", um.Login); cmd.Parameters.AddWithValue("@Password", um.Password); try { cmd.ExecuteNonQuery(); } catch (Exception e) { Trace.WriteLine($"EXCEPTION: UserBossDbMapper.Update: {e.Message}"); return(-1); } } return(0); }
public static UserBoss SelectById(SQLiteConnection conn, int id) { if (id < 0) { return(null); } using (SQLiteCommand cmd = new SQLiteCommand(selectById, conn)) { cmd.Parameters.AddWithValue("@Id", id); SQLiteDataReader reader = null; try { reader = cmd.ExecuteReader(); while (reader.Read()) { UserBoss um = new UserBoss() { Id = Convert.ToInt32(reader["rowid"].ToString()), FirstName = reader["FirstName"].ToString(), LastName = reader["LastName"].ToString(), Email = reader["Email"].ToString(), Phone = reader["Phone"].ToString(), Street = reader["Street"].ToString(), City = reader["City"].ToString(), Zip = reader["Zip"].ToString(), Active = Convert.ToBoolean(reader["Active"]), Login = reader["Login"].ToString(), Password = reader["Password"].ToString() }; reader.Close(); return(um); } } catch (Exception e) { reader?.Close(); Trace.WriteLine($"EXCEPTION: UserBossDbMapper.SelectById: {e.Message}"); } } return(null); }
public static List <UserBoss> SelectAll(SQLiteConnection conn) { List <UserBoss> result = new List <UserBoss>(); using (SQLiteCommand cmd = new SQLiteCommand(selectAll, conn)) { SQLiteDataReader reader = null; try { reader = cmd.ExecuteReader(); while (reader.Read()) { UserBoss um = new UserBoss() { Id = Convert.ToInt32(reader["rowid"].ToString()), FirstName = reader["FirstName"].ToString(), LastName = reader["LastName"].ToString(), Email = reader["Email"].ToString(), Phone = reader["Phone"].ToString(), Street = reader["Street"].ToString(), City = reader["City"].ToString(), Zip = reader["Zip"].ToString(), Active = Convert.ToBoolean(reader["Active"]), Login = reader["Login"].ToString(), Password = reader["Password"].ToString() }; result.Add(um); } } catch (Exception e) { Trace.WriteLine($"EXCEPTION: UserBossDbMapper.SelectAll: {e.Message}"); } finally { reader?.Close(); } } return(result); }
public static int Delete(SQLiteConnection conn, UserBoss ub) { if (ub == null) { return(-2); } if (ub.Id < 0) { return(-3); } using (SQLiteCommand cmd = new SQLiteCommand(deleteUB, conn)) { cmd.Parameters.AddWithValue("@Id", ub.Id); try { cmd.ExecuteNonQuery(); } catch (Exception e) { Trace.WriteLine($"EXCEPTION: UserBossDbMapper.Delete: {e.Message}"); return(-1); } } using (SQLiteCommand cmd = new SQLiteCommand(deleteU, conn)) { cmd.Parameters.AddWithValue("@Id", GetUserIdFromBossId(conn, ub.Id)); try { cmd.ExecuteNonQuery(); } catch (Exception e) { Trace.WriteLine($"EXCEPTION: UserBossDbMapper.Delete: {e.Message}"); return(-1); } } return(0); }