/// <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;
}
