public static List <Book> GetSubscribedItems(int uid)
{
using (SqlConnection ServerConnection = new SqlConnection(ConnectionString))
{
ServerConnection.Open();
//calling procedure from db
SqlCommand cmd = new SqlCommand();
List <Book> BooksList = new List <Book>();
DataTable users = new DataTable(); //stores IDs returned by db
try
{
cmd.CommandText = "UsersSubscription";
cmd.Connection = ServerConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//passing parameters to procedure
cmd.Parameters.Add(new SqlParameter("@uid", uid));
//passing output para
cmd.Parameters.Add(new SqlParameter("@flag", SqlDbType.Int));
cmd.Parameters["@flag"].Direction = ParameterDirection.Output;
SqlDataAdapter Data = new SqlDataAdapter(cmd);
Data.Fill(users); //execute procedure
}
catch (SqlException ex)
{
Console.Write("SQL Server Error" + ex);
}
finally
{
ServerConnection.Close();
}
int Flag = (int)cmd.Parameters["@flag"].Value;
if (Flag == 1) //if found
{
foreach (DataRow row in users.Rows)
{
Book getBook = BookCRUD.GetBook((int)row["ItemID"]);
if (getBook != null)
{
BooksList.Add(getBook);
}
}
return(BooksList);
}
else
{
return(null);
}
}
}
public static List <Book> AuthorSearch(string search)
{
using (SqlConnection ServerConnection = new SqlConnection(ConnectionString))
{
List <Book> books = new List <Book>();
ServerConnection.Open();
SqlCommand cmd = new SqlCommand();
try
{
//setting up command to call procedure
cmd.CommandText = "SearchByAuthor";
cmd.Connection = ServerConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//parameters
cmd.Parameters.Add(new SqlParameter("@authstr", search));
cmd.Parameters.Add(new SqlParameter("@flag", SqlDbType.Int));
cmd.Parameters["@flag"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int Flag = (int)cmd.Parameters["@flag"].Value;
if (Flag == 1)
{
DataTable itemIDs = new DataTable(); //stores IDs
SqlDataAdapter Data = new SqlDataAdapter(cmd);
Data.Fill(itemIDs); //get procedure result set
foreach (DataRow row in itemIDs.Rows)
{
books.Add(BookCRUD.GetBook((int)row["ItemID"]));
}
}
}
catch (SqlException ex)
{
Console.Write("SQL Server Error" + ex);
}
finally
{
ServerConnection.Close();
}
return(books);
}
}
public static List <KeyValuePair <Book, int> > GetOrderItems(int id)
{
using (SqlConnection ServerConnection = new SqlConnection(ConnectionString))
{
List <KeyValuePair <Book, int> > books = new List <KeyValuePair <Book, int> >();
ServerConnection.Open();
SqlCommand cmd = new SqlCommand();
try
{
//using procedure that returns book info for one book
cmd.CommandText = "GetOrderedItems";
cmd.Connection = ServerConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//parameters
cmd.Parameters.Add(new SqlParameter("@oid", id));
cmd.Parameters.Add(new SqlParameter("@flag", SqlDbType.Int));
cmd.Parameters["@flag"].Direction = ParameterDirection.Output;
DataTable itemIDs = new DataTable(); //stores IDs
SqlDataAdapter Data = new SqlDataAdapter(cmd);
Data.Fill(itemIDs); //get procedure result set
int Flag = (int)cmd.Parameters["@flag"].Value; //check if required order id found
if (Flag == 1)
{
foreach (DataRow row in itemIDs.Rows)
{
//add the book object along with its quantity to the returning list
Book OrderedBook = BookCRUD.GetBook((int)row["ItemID"]);
OrderedBook.Price = Convert.ToInt32(row["PriceSoldAt"]);
books.Add(new KeyValuePair <Book, int>(OrderedBook, (int)row["Quantity"]));
}
}
}
catch (SqlException ex)
{
Console.Write("SQL Server Error" + ex);
}
finally
{
ServerConnection.Close();
}
return(books);
}
}
public static List <Book> UserRecommendations(int id)
{
using (SqlConnection ServerConnection = new SqlConnection(ConnectionString))
{
List <Book> books = new List <Book>();
ServerConnection.Open();
SqlCommand cmd = new SqlCommand();
try
{
//setting up command to call procedure
cmd.CommandText = "RecommendBooks";
cmd.Connection = ServerConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//parameters
cmd.Parameters.Add(new SqlParameter("@uid", id));
cmd.Parameters.Add(new SqlParameter("@flag", SqlDbType.Int));
cmd.Parameters["@flag"].Direction = ParameterDirection.Output;
DataTable itemIDs = new DataTable(); //stores IDs
SqlDataAdapter Data = new SqlDataAdapter(cmd);
Data.Fill(itemIDs); //get procedure result set
int Flag = (int)cmd.Parameters["@flag"].Value; //check if required order id found
if (Flag == 1)
{
foreach (DataRow row in itemIDs.Rows)
{
books.Add(BookCRUD.GetBook((int)row["ID"]));
}
}
}
catch (SqlException ex)
{
Console.Write("SQL Server Error" + ex);
}
finally
{
ServerConnection.Close();
}
return(books);
}
}
public static List <Book> TitleSearch(string search)
{
using (SqlConnection ServerConnection = new SqlConnection(ConnectionString))
{
List <Book> books = new List <Book>();
ServerConnection.Open();
SqlCommand cmd = new SqlCommand();
try
{
//setting up command to call procedure
cmd.CommandText = "SearchBook";
cmd.Connection = ServerConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//parameters
cmd.Parameters.Add(new SqlParameter("@namestr", search));
DataTable itemIDs = new DataTable(); //stores IDs
SqlDataAdapter Data = new SqlDataAdapter(cmd);
Data.Fill(itemIDs); //get procedure result set
foreach (DataRow row in itemIDs.Rows)
{
books.Add(BookCRUD.GetBook((int)row["ItemID"]));
}
}
catch (SqlException ex)
{
Console.Write("SQL Server Error" + ex);
}
finally
{
ServerConnection.Close();
}
return(books);
}
}
