База данных Access. Первичный ключ id. Задумка: по нажатию кнопки введенные значения должны передаться в БД. Но из неизвестной мне причине, данное выполнение не происходит.
string x = textBox3.Text;
string y = textBox4.Text;
OleDbCommand command = new OleDbCommand();
OleDbTransaction transaction = null;
command.Connection = myConnection;
transaction = myConnection.BeginTransaction();
command.Connection = myConnection;
command.Transaction = transaction;
command.CommandText =
"INSERT INTO translater_ (eng , rus) VALUES (" + x + ", " + y + ")";
command.ExecuteNonQuery();
transaction.Commit();
myConnection.Close();
Если создать вот такой класс Слово
public class Word
{
public int Id { get; set; }
public string Eng { get; set; }
public string Rus { get; set; }
public override string ToString()
{
return $"{Id}: {Eng}-{Rus}";
}
}
То для него получился вот такой класс Хранилища
public class Repository
{
//ctor
public Repository()
{
}
private OleDbConnection GetConnection()
{
OleDbConnectionStringBuilder oleStringBuilder = new OleDbConnectionStringBuilder();
oleStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
var currentDir = Path.GetDirectoryName(this.GetType().Assembly.Location);
oleStringBuilder.DataSource = Path.Combine(currentDir, "wordsDb.accdb");
OleDbConnection connection = new OleDbConnection(oleStringBuilder.ConnectionString);
return connection;
}
/// <summary>
/// Получение полного списка всех слов
/// </summary>
/// <returns></returns>
public List<Word> GetWords()
{
var result = new List<Word>();
using (OleDbConnection connection = GetConnection())
using (OleDbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Words";
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
var word = new Word
{
Id = reader.GetFieldValue<int>(0),
Eng = reader.GetFieldValue<string>(1),
Rus = reader.GetFieldValue<string>(2)
};
result.Add(word);
}
}
return result;
}
/// <summary>
/// Поиск слова по его Id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Word GetWordById(int id)
{
Word result = null;
using (OleDbConnection connection = GetConnection())
using (OleDbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Words WHERE Id=@id";
command.Parameters.AddWithValue("id", id);
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
result = new Word
{
Id = reader.GetFieldValue<int>(0),
Eng = reader.GetFieldValue<string>(1),
Rus = reader.GetFieldValue<string>(2)
};
}
}
return result;
}
/// <summary>
/// Поиск слова по анг. значению
/// </summary>
/// <param name="eng"></param>
/// <returns></returns>
public Word GetWordByEng(string eng)
{
if (String.IsNullOrEmpty(eng)) throw new ArgumentNullException(nameof(eng));
Word result = null;
using (OleDbConnection connection = GetConnection())
using (OleDbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Words WHERE english=@eng";
command.Parameters.AddWithValue("eng", eng);
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
result = new Word
{
Id = reader.GetFieldValue<int>(0),
Eng = reader.GetFieldValue<string>(1),
Rus = reader.GetFieldValue<string>(2)
};
}
}
return result;
}
/// <summary>
/// Добавление нового слова
/// </summary>
/// <param name="word"></param>
/// <returns></returns>
public int AddWord(Word word)
{
if (word == null) throw new ArgumentNullException(nameof(word));
if (String.IsNullOrWhiteSpace(word.Eng)) throw new ArgumentException("Английское значение обязательное");
if (String.IsNullOrWhiteSpace(word.Rus)) throw new ArgumentException("Русское значение обязательное");
int result = 0;
using (OleDbConnection connection = GetConnection())
using (OleDbCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO Words (english, russian) VALUES (@eng, @rus)";
command.Parameters.AddWithValue("eng", word.Eng);
command.Parameters.AddWithValue("rus", word.Rus);
connection.Open();
result = command.ExecuteNonQuery();
}
return result;
}
/// <summary>
/// Обновление слова
/// </summary>
/// <param name="word"></param>
/// <returns></returns>
public int UpdateWord(Word word)
{
if (word == null) throw new ArgumentNullException(nameof(word));
if (word.Id == 0) throw new ArgumentException(nameof(word));
int result = 0;
using (OleDbConnection connection = GetConnection())
using (OleDbCommand command = connection.CreateCommand())
{
command.CommandText = $"UPDATE Words SET english = '{word.Eng}', russian = '{word.Rus}' WHERE Id = {word.Id}";
connection.Open();
result = command.ExecuteNonQuery();
}
return result;
}
/// <summary>
/// Удаление слова
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int DeleteWord(int id)
{
int result = 0;
using (OleDbConnection connection = GetConnection())
using (OleDbCommand command = connection.CreateCommand())
{
command.CommandText = "DELETE FROM Words WHERE Id=@id";
command.Parameters.AddWithValue("id", id);
connection.Open();
result = command.ExecuteNonQuery();
}
return result;
}
}
Как таким классом пользоваться можете посмотреть через тесты
[TestClass()]
public class RepositoryTests
{
[TestMethod()]
public void GetAllWords()
{
Repository repo = new Repository();
var res = repo.GetWords();
Assert.IsTrue(res.Count > 0);
}
[TestMethod]
public void GetWordByEng()
{
Repository repo = new Repository();
string eng = "apple";
Word word = repo.GetWordByEng(eng);
Assert.IsNotNull(word);
Assert.AreEqual(eng, word.Eng);
}
[TestMethod]
public void GetWordById()
{
Repository repo = new Repository();
string eng = "apple";
int id = 1;
Word word = repo.GetWordById(id);
Assert.IsNotNull(word);
Assert.AreEqual(eng, word.Eng);
}
[TestMethod]
[Ignore]
public void InsertWord()
{
var word = new Word { Eng = "test", Rus = "тест" };
Repository repo = new Repository();
int res = repo.AddWord(word);
Assert.IsTrue(res > 0);
}
[TestMethod]
[Ignore]
public void UpdateWord()
{
var rus = "тестОбновлен";
Repository repo = new Repository();
var word = repo.GetWordByEng("test");
word.Eng = word.Eng + 1;
word.Rus = rus;
int res = repo.UpdateWord(word);
var updatedWord = repo.GetWordById(word.Id);
Assert.IsTrue(res > 0);
Assert.AreEqual(rus, updatedWord.Rus);
}
[TestMethod]
[Ignore]
public void DeleteWord()
{
Repository repo = new Repository();
var word = repo.GetWordByEng("test1");
int res = repo.DeleteWord(word.Id);
Assert.IsTrue(res > 0);
}
}
Виртуальный выделенный сервер (VDS) становится отличным выбором
Какая разница между этими двумя класами? Что в первом универсальный тип данных - что во второмБолее того, насколько я знаю, компилятор тип...
Всем привет :) Как можно добавить InitialDirectory для FileDialog в файл конфигурации XML?