/// <summary> /// добавляет запись о справочнике в базу данных /// </summary> /// <param name="author"></param> /// <param name="year"></param> /// <param name="name"></param> /// <returns></returns> public string add_book(string author, string year, string name) { try { class_book[] book = new class_book[512]; string query = "select * From Book where Author ='" + author + "'" + " and Name= '" + name + "'"; SqlCommand com = Program.data_module._conn.CreateCommand(); com.CommandText = query; SqlDataReader rd = com.ExecuteReader(); int i = 0; while (rd.Read()) { i++; book[i] = new class_book(); book[i].result = "OK"; book[i].author = rd.GetString(1); book[i].name = rd.GetString(3); book[i].year = rd.GetInt32(2).ToString(); if ((book[i].author == author) && (book[i].year == year) && (book[i].name == name)) { rd.Close(); rd.Dispose(); com.Dispose(); } } rd.Close(); rd.Dispose(); com.Dispose(); } catch (Exception) { return null; } return SQL_Exec(string.Format("insert into Book (Author, Year,Name ) values ('{0}',{1},'{2}')", ((author.Length == 0) ? "NULL" : author), ((year.Length == 0) ? "NULL" : year), ((name.Length == 0) ? "NULL" : name))); }
/// <summary> /// возвращает справочник по указанному в параметрах идентификатору (коду) /// </summary> /// <param name="id"></param> /// <returns></returns> public class_book get_book(int id) { class_book book = new class_book(); string query = "select * from Book where IDBook="; query += id.ToString(); try { SqlCommand com = Program.data_module._conn.CreateCommand(); com.CommandText = query; SqlDataReader rd = com.ExecuteReader(); if (rd.Read()) { book.result = "OK"; book.book_id = id.ToString(); book.author = rd.GetString(1); if (rd.IsDBNull(2)) { book.year = ""; } else { book.year = rd.GetInt32(2).ToString(); } if (rd.IsDBNull(3)) { book.name = ""; } else { book.name = rd.GetString(3); } } rd.Close(); rd.Dispose(); com.Dispose(); } catch (Exception ex) { book.result = "ERROR_" + ex.Data + " " + ex.Message; } return book; }
/// <summary> /// Возвращает самый максимальный год выпуска кулинарной книги /// </summary> public class_book[] get_book_year() { class_book[] book = new class_book[512]; string query = "select MAX(B.Year) as Year from Book B join FoodInBook FIB on FIB.IDBook = B.IDBook where FIB.ID_food ='"; for (int t = 1; t < this._food_list1.Count(); t++) if (this._food_list1[t] != null) { string guery1 = this._food_list1[t].food_id + "'"; string query2 = query + guery1; try { SqlCommand com = Program.data_module._conn.CreateCommand(); com.CommandText = query2; SqlDataReader rd = com.ExecuteReader(); int i = 0; while (rd.Read()) { i = i + 1; book[i] = new class_book(); book[i].result = "OK"; if (rd.IsDBNull(0)) { book[i].year = ""; } else { book[i].year = rd.GetInt32(0).ToString(); } } rd.Close(); rd.Dispose(); com.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message + " " + ex.Data); return null; } } return book; }
/// <summary> /// Возвращает id книги /// </summary> public class_book[] get_id_book_list() { class_book[] book = new class_book[512]; string query = "select * from FoodinBook where ID_food='"; for (int t = 1; t < this._food_list1.Count(); t++) if (this._food_list1[t] != null) { string guery1 = this._food_list1[t].food_id + "'"; string query2 = query + guery1; try { SqlCommand com = Program.data_module._conn.CreateCommand(); com.CommandText = query2; SqlDataReader rd = com.ExecuteReader(); int i = 0; while (rd.Read()) { i = i + 1; book[i] = new class_book(); book[i].result = "OK"; book[i].book_id = rd.GetInt32(2).ToString(); } rd.Close(); rd.Dispose(); com.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message + " " + ex.Data); return null; } } return book; }
/// <summary> /// Возвращает название и год выпуска кулинарной книги /// </summary> public class_book[] get_book_list4() { class_book[] book = new class_book[512]; string query = "select * from Book where IDBook='"; if (this._id_book[5] != null) { string guery1 = this._id_book[5].book_id + "'"; string query2 = query + guery1; try { SqlCommand com = Program.data_module._conn.CreateCommand(); com.CommandText = query2; SqlDataReader rd = com.ExecuteReader(); int i = 0; while (rd.Read()) { i = i + 1; book[i] = new class_book(); book[i].result = "OK"; book[i].author = rd.GetString(1).ToString(); book[i].year = rd.GetInt32(2).ToString(); book[i].name = rd.GetString(3); } rd.Close(); rd.Dispose(); com.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message + " " + ex.Data); return null; } } return book; }