public void AddNewMagazineToDatabase(Magazine magazine)
{
SqlCommand commandInsertMagazine = new SqlCommand("INSERT INTO magazines (title,number,year) VALUES (@title, @number, @year)", connection);
commandInsertMagazine.Parameters.AddWithValue("title", magazine.Title);
commandInsertMagazine.Parameters.AddWithValue("number", magazine.IssueNumber);
commandInsertMagazine.Parameters.AddWithValue("year", magazine.PublicationYear);
commandInsertMagazine.ExecuteNonQuery();
string commandText = string.Format("SELECT id FROM magazines WHERE magazines.title = '{0}' AND magazines.number = {1} AND magazines.year = {2}", magazine.Title, magazine.IssueNumber, magazine.PublicationYear);
SqlCommand commandSelectMagazine = new SqlCommand(commandText, connection);
SqlDataReader readerMagazine = commandSelectMagazine.ExecuteReader();
readerMagazine.Read();
int magazineId = readerMagazine.GetInt32(0);
readerMagazine.Close();
foreach (var genre in magazine.Genres)
{
SqlCommand commandInsertM2MMagazinesGenres = new SqlCommand("INSERT INTO m2m_magazines_genres (magazine_id,genre_id) VALUES (@magazine_id, @genre_id)", connection);
commandInsertM2MMagazinesGenres.Parameters.AddWithValue("magazine_id", magazineId);
commandInsertM2MMagazinesGenres.Parameters.AddWithValue("genre_id", (int)genre);
commandInsertM2MMagazinesGenres.ExecuteNonQuery();
}
foreach (var author in magazine.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 commandInsertM2MMagazinesAuthors = new SqlCommand("INSERT INTO m2m_magazines_authors (magazine_id,author_id) VALUES (@magazine_id, @author_id)", connection);
commandInsertM2MMagazinesAuthors.Parameters.AddWithValue("magazine_id", magazineId);
commandInsertM2MMagazinesAuthors.Parameters.AddWithValue("author_id", GetAuthorID(author));
commandInsertM2MMagazinesAuthors.ExecuteNonQuery();
}
}
}
private void Form_AddBook(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);
Magazine magazine = new Magazine(title, number, genre, year, authors);
_library.connection.Open();
_library.AddNewMagazineToDatabase(magazine);
_library.connection.Close();
MessageBox.Show("New magazine add!", "OK!", MessageBoxButtons.OK, MessageBoxIcon.Information);
((Form)_form).Close();
}