126 lines
4.2 KiB
C#
126 lines
4.2 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
using Microsoft.Data.Sqlite;
|
||
using System.Collections.ObjectModel;
|
||
namespace NotesDataAnalyst.NoteContent
|
||
{
|
||
|
||
|
||
public class DatabaseContext
|
||
{
|
||
private readonly string _connectionString;
|
||
|
||
public DatabaseContext(string dbPath)
|
||
{
|
||
_connectionString = $"Data Source={dbPath}";
|
||
InitializeDatabase();
|
||
}
|
||
|
||
private void InitializeDatabase()
|
||
{
|
||
using (var connection = new SqliteConnection(_connectionString))
|
||
{
|
||
connection.Open();
|
||
|
||
var command = connection.CreateCommand();
|
||
command.CommandText =
|
||
@"
|
||
CREATE TABLE IF NOT EXISTS Notes (
|
||
Id TEXT PRIMARY KEY,
|
||
Category TEXT,
|
||
Title TEXT,
|
||
Image TEXT,
|
||
Text TEXT,
|
||
DateCreated TEXT
|
||
);
|
||
";
|
||
command.ExecuteNonQuery();
|
||
}
|
||
}
|
||
|
||
public ObservableCollection<Note> GetAllNotes()
|
||
{
|
||
var notes = new ObservableCollection<Note>();
|
||
|
||
using (var connection = new SqliteConnection(_connectionString))
|
||
{
|
||
connection.Open();
|
||
|
||
var command = connection.CreateCommand();
|
||
command.CommandText = "SELECT * FROM Notes";
|
||
using (var reader = command.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
var note = new Note
|
||
{
|
||
Id = reader.GetString(0),
|
||
Category = reader.GetString(1),
|
||
Title = reader.GetString(2),
|
||
Image = reader.GetString(3),
|
||
Text = reader.GetString(4), // Загружаем текст из БД
|
||
DateCreated = DateTime.TryParse(reader.GetString(5), out var date) ? date : DateTime.UtcNow
|
||
};
|
||
|
||
notes.Add(note);
|
||
}
|
||
}
|
||
}
|
||
|
||
return notes;
|
||
}
|
||
|
||
public void SaveNote(Note note)
|
||
{
|
||
using (var connection = new SqliteConnection(_connectionString))
|
||
{
|
||
connection.Open();
|
||
|
||
var command = connection.CreateCommand();
|
||
command.CommandText =
|
||
@"
|
||
INSERT OR REPLACE INTO Notes (Id, Category, Title, Image, Text, DateCreated)
|
||
VALUES ($id, $category, $title, $image, $text, $dateCreated)
|
||
";
|
||
command.Parameters.AddWithValue("$id", note.Id);
|
||
command.Parameters.AddWithValue("$category", note.Category);
|
||
command.Parameters.AddWithValue("$title", note.Title);
|
||
command.Parameters.AddWithValue("$image", note.Image);
|
||
command.Parameters.AddWithValue("$text", note.Text);
|
||
command.Parameters.AddWithValue("$dateCreated", note.DateCreated.ToString("o")); // Сохраняем в формате ISO
|
||
command.ExecuteNonQuery();
|
||
|
||
}
|
||
}
|
||
|
||
public void DeleteNote(Note note)
|
||
{
|
||
if (string.IsNullOrEmpty(note.Id))
|
||
{
|
||
Console.WriteLine("Ошибка: У заметки отсутствует Id.");
|
||
return;
|
||
}
|
||
|
||
using (var connection = new SqliteConnection(_connectionString))
|
||
{
|
||
connection.Open();
|
||
|
||
var command = connection.CreateCommand();
|
||
command.CommandText =
|
||
@"
|
||
DELETE FROM Notes
|
||
WHERE Id = $id
|
||
";
|
||
command.Parameters.AddWithValue("$id", note.Id);
|
||
command.ExecuteNonQuery();
|
||
int result = command.ExecuteNonQuery();
|
||
|
||
Console.WriteLine(result > 0 ? "Заметка удалена из базы данных." : "Ошибка: Заметка не найдена в базе данных.");
|
||
}
|
||
}
|
||
}
|
||
|
||
}
|