public static List<Kitap> search(string kitapAdi, Yazar[] yazarlar, int[] yillar)
{
List<Kitap> Value = new List<Kitap>();
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
String CommandString = "select ID,Adi,BasimYili,YayineviID,KategoriID,SayfaSayisi,HasarDurumu,KiralamaDurumu,Ucret,Ozet,Kapak ";
CommandString += "from dbo.Kitap join dbo.KitapYazar on dbo.Kitap.ID = dbo.KitapYazar.KitapID where ";
CommandString += "(Adi like '%" + kitapAdi + "%') and (";
int counter = 0;
foreach (Yazar item in yazarlar)
{
if (item.ID != 0)
{
CommandString += "dbo.KitapYazar.YazarID=" + item.ID.ToString() + " or ";
counter++;
}
}
if (counter > 0)
{
CommandString = CommandString.Remove(CommandString.Length - 4);
CommandString += ") and (";
}
foreach (int item in yillar)
{
CommandString += "dbo.Kitap.BasimYili='" + item.ToString() + "' or ";
}
CommandString = CommandString.Remove(CommandString.Length - 4);
CommandString += ")";
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
SqlConnection AuthorCon = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
SqlCommand AuthorCmd = new SqlCommand("select YazarID from dbo.KitapYazar where KitapID = " + id.ToString(), AuthorCon);
AuthorCon.Open();
SqlDataReader AuthorReader = AuthorCmd.ExecuteReader();
List<Yazar> YazarList = new List<Yazar>();
while (AuthorReader.Read())
{
YazarList.Add(YazarIslemleri.getAuthor(AuthorReader.GetInt32(0)));
}
AuthorReader.Close();
AuthorCon.Close();
Yazar[] Yazarlar = null;
if (YazarList.Count > 0)
{
Yazarlar = new Yazar[YazarList.Count];
YazarList.CopyTo(Yazarlar);
}
Yayinevi Publisher = YayineviIslemleri.getPublisher(reader.GetInt32(3));
Kategori Category = KategoriIslemleri.getCategory(reader.GetInt32(4));
Kitap Current = new Kitap(id, reader.GetString(1), int.Parse(reader.GetString(2)), Yazarlar, Publisher, Category,
reader.GetInt32(5), reader.GetBoolean(7), reader.GetBoolean(6), reader.GetDecimal(8));
try
{
Current.Ozet = reader.GetString(9);
}
catch (SqlNullValueException snve)
{
Current.Ozet = "";
}
try
{
Current.Kapak = reader.GetString(10);
}
catch (SqlNullValueException snve)
{
Current.Kapak = "";
}
if (!Value.Contains(Current))
{
Value.Add(Current);
}
}
reader.Close();
con.Close();
return Value;
}
public static List<Kitap> mostRentedBook(DateTime First, DateTime Last)
{
List<Kitap> Value = new List<Kitap>();
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
String CommandString = "select KitapID,COUNT(KitapID) as KiralamaSayisi from dbo.Kiralama" +
" join dbo.KiralamaDetay on Kiralama.ID = KiralamaDetay.ID" +
" where KiralamaDetay.KiralamaTarihi between";
CommandString += " '" + convertToSqlDate(First.ToShortDateString()) + "' and";
CommandString += " '" + convertToSqlDate(Last.ToShortDateString()) + "' group by KitapID";
CommandString += " order by KiralamaSayisi desc";
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
int max = 0;
List<int> Kitaplar = new List<int>();
if (reader.Read())
{
max = reader.GetInt32(1);
Kitaplar.Add(reader.GetInt32(0));
}
while (reader.Read())
{
int currentCount = reader.GetInt32(1);
if (currentCount == max)
{
Kitaplar.Add(reader.GetInt32(0));
}
else if (currentCount < max)
{
break;
}
}
reader.Close();
con.Close();
if (Kitaplar.Count > 0)
{
CommandString = "select * from dbo.Kitap where ";
foreach (int item in Kitaplar)
{
CommandString += "ID=" + item.ToString() + " or ";
}
CommandString = CommandString.Remove(CommandString.Length - 4);
cmd = new SqlCommand(CommandString, con);
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
SqlConnection AuthorCon = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
SqlCommand AuthorCmd = new SqlCommand("select YazarID from dbo.KitapYazar where KitapID = " + id.ToString(), AuthorCon);
AuthorCon.Open();
SqlDataReader AuthorReader = AuthorCmd.ExecuteReader();
List<Yazar> YazarList = new List<Yazar>();
while (AuthorReader.Read())
{
YazarList.Add(YazarIslemleri.getAuthor(AuthorReader.GetInt32(0)));
}
AuthorReader.Close();
AuthorCon.Close();
Yazar[] Yazarlar = null;
if (YazarList.Count > 0)
{
Yazarlar = new Yazar[YazarList.Count];
YazarList.CopyTo(Yazarlar);
}
Yayinevi Publisher = YayineviIslemleri.getPublisher(reader.GetInt32(3));
Kategori Category = KategoriIslemleri.getCategory(reader.GetInt32(4));
Kitap Current = new Kitap(id, reader.GetString(1), int.Parse(reader.GetString(2)), Yazarlar, Publisher, Category,
reader.GetInt32(5), reader.GetBoolean(7), reader.GetBoolean(6), reader.GetDecimal(8));
try
{
Current.Ozet = reader.GetString(9);
}
catch (SqlNullValueException snve)
{
Current.Ozet = "";
}
try
{
Current.Kapak = reader.GetString(10);
}
catch (SqlNullValueException snve)
{
Current.Kapak = "";
}
if (!Value.Contains(Current))
{
Value.Add(Current);
}
}
reader.Close();
con.Close();
}
return Value;
}
public static List<Kitap> search(string kitapAdi)
{
List<Kitap> Value = new List<Kitap>();
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
String CommandString = "select * from dbo.Kitap where Adi like '%" + kitapAdi + "%'";
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
SqlConnection AuthorCon = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
SqlCommand AuthorCmd = new SqlCommand("select YazarID from dbo.KitapYazar where KitapID = " + id.ToString(), AuthorCon);
AuthorCon.Open();
SqlDataReader AuthorReader = AuthorCmd.ExecuteReader();
List<Yazar> YazarList = new List<Yazar>();
while (AuthorReader.Read())
{
YazarList.Add(YazarIslemleri.getAuthor(AuthorReader.GetInt32(0)));
}
AuthorReader.Close();
AuthorCon.Close();
Yazar[] Yazarlar = null;
if (YazarList.Count > 0)
{
Yazarlar = new Yazar[YazarList.Count];
YazarList.CopyTo(Yazarlar);
}
Yayinevi Publisher = YayineviIslemleri.getPublisher(reader.GetInt32(3));
Kategori Category = KategoriIslemleri.getCategory(reader.GetInt32(4));
Kitap Current = new Kitap(id, reader.GetString(1), int.Parse(reader.GetString(2)), Yazarlar, Publisher, Category,
reader.GetInt32(5), reader.GetBoolean(7), reader.GetBoolean(6), reader.GetDecimal(8));
try
{
Current.Ozet = reader.GetString(9);
}
catch (SqlNullValueException snve)
{
Current.Ozet = "";
}
try
{
Current.Kapak = reader.GetString(10);
}
catch (SqlNullValueException snve)
{
Current.Kapak = "";
}
Value.Add(Current);
}
reader.Close();
con.Close();
return Value;
}