public void AddNewspaperToDatabase(Newspaper newspaper)
{
SqlCommand commandInsertNewspaper = new SqlCommand("INSERT INTO newspapers (title,number,year) VALUES (@title, @number, @year)", connection);
commandInsertNewspaper.Parameters.AddWithValue("title", newspaper.Title);
commandInsertNewspaper.Parameters.AddWithValue("number", newspaper.Number);
commandInsertNewspaper.Parameters.AddWithValue("year", newspaper.PublicationYear);
commandInsertNewspaper.ExecuteNonQuery();
string commandText = string.Format("SELECT id FROM newspapers WHERE newspapers.title = '{0}' AND newspapers.number = {1} AND newspapers.year = {2}", newspaper.Title, newspaper.Number, newspaper.PublicationYear);
SqlCommand commandSelectNewspaper = new SqlCommand(commandText, connection);
SqlDataReader readerNewspaper = commandSelectNewspaper.ExecuteReader();
readerNewspaper.Read();
int newspaperId = readerNewspaper.GetInt32(0);
readerNewspaper.Close();
foreach (var genre in newspaper.Genres)
{
SqlCommand commandInsertM2MNewspapersGenres = new SqlCommand("INSERT INTO m2m_newspapers_genres (newspaper_id,genre_id) VALUES (@newspaper_id, @genre_id)", connection);
commandInsertM2MNewspapersGenres.Parameters.AddWithValue("newspaper_id", newspaperId);
commandInsertM2MNewspapersGenres.Parameters.AddWithValue("genre_id", (int)genre);
commandInsertM2MNewspapersGenres.ExecuteNonQuery();
}
foreach (var author in newspaper.Authors)
{
if (GetAuthorID(author) == null)
{
SqlCommand commandInsertAuthor = new SqlCommand("INSERT INTO authors (name, year) VALUES (@name, @year)", connection);
commandInsertAuthor.Parameters.AddWithValue("name", author.Name);
commandInsertAuthor.Parameters.AddWithValue("year", author.Year);
commandInsertAuthor.ExecuteNonQuery();
}
if (GetAuthorID(author) != null)
{
SqlCommand commandInsertM2MNewspapersAuthors = new SqlCommand("INSERT INTO m2m_newspapers_authors (newspaper_id,author_id) VALUES (@newspaper_id, @author_id)", connection);
commandInsertM2MNewspapersAuthors.Parameters.AddWithValue("newspaper_id", newspaperId);
commandInsertM2MNewspapersAuthors.Parameters.AddWithValue("author_id", GetAuthorID(author));
commandInsertM2MNewspapersAuthors.ExecuteNonQuery();
}
}
}
private void Form_AddNewspaper(object sender, EventArgs e)
{
List <Author> authorsTemp = new List <Author>();
List <Genre> genre = new List <Genre>();
string title = _form.Title.Text;
int number = Convert.ToInt32(_form.Number.Text);
int year = Convert.ToInt32(_form.Year.Text);
for (int i = 0; i < _form.Genres.Controls.Count; i++)
{
CheckBox checkBox = _form.Genres.Controls[i] as CheckBox;
if (checkBox.Checked)
{
genre.Add((Genre)Enum.Parse(typeof(Genre), checkBox.Text));
}
}
Regex regex = new Regex(@"^authorTextBox\d$");
for (int i = 0; i < _form.Authors.Controls.Count; i++)
{
if (regex.IsMatch(_form.Authors.Controls[i].Name))
{
if (_form.Authors.Controls[i].Text != "")
{
string name = _form.Authors.Controls[i].Text;
int authorYear = Convert.ToInt32(_form.Authors.Controls[string.Format("{0}Year", _form.Authors.Controls[i].Name)].Text);
authorsTemp.Add(new Author(name, authorYear));
}
}
}
Author[] authors = new Author[authorsTemp.Count];
authorsTemp.CopyTo(authors);
Newspaper newspaper = new Newspaper(title, number, genre, year, authors);
_library.connection.Open();
_library.AddNewspaperToDatabase(newspaper);
_library.connection.Close();
MessageBox.Show("New newspaper add!", "OK!", MessageBoxButtons.OK, MessageBoxIcon.Information);
((Form)_form).Close();
}
