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