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(); }