public static CirculatedCopy GetCirculatedCopy(Borrower borrower) { SqlConnection conn = DAO.getConnections(); string sql = "SELECT * FROM CirculatedCopy WHERE borrowerNumber = @borrowerNumber"; SqlCommand command = new SqlCommand(sql, conn); command.Parameters.Add(new SqlParameter("@borrowerNumber", borrower.BorrowerNumber)); conn.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CirculatedCopy s = new CirculatedCopy(); s.Id = Int32.Parse(reader["ID"].ToString()); s.CopyNumber = Int32.Parse(reader["copyNumber"].ToString()); s.BorrowerNumber = Int32.Parse(reader["borrowerNumber"].ToString()); s.BorrowedDate = Convert.ToDateTime(reader["borrowedDate"]); s.DueDate = Convert.ToDateTime(reader["dueDate"]); s.ReturnedDate = Convert.ToDateTime(reader["returnedDate"]); s.FineAmount = float.Parse(reader["fineAmount"].ToString()); conn.Close(); return(s); } return(null); }
public static bool Insert(Book b) { try { SqlConnection conn = DAO.getConnections(); SqlCommand cmd = new SqlCommand("insert into book( title, authors, publisher) values( @title, @authors, @publisher)"); cmd.Connection = conn; cmd.Parameters.AddWithValue("@title", b.Title); cmd.Parameters.AddWithValue("@authors", b.Authors); cmd.Parameters.AddWithValue("@publisher", b.Publisher); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); return(true); } catch (Exception) { return(false); } }
public static bool Insert(Borrower b) { try { SqlConnection conn = DAO.getConnections(); SqlCommand cmd = new SqlCommand("insert into Borrower(borrowerNumber, name, sex, address,telephone,email) " + "values(@borrowerNumber, @name, @sex, @address,@telephone,@email)"); cmd.Connection = conn; cmd.Parameters.AddWithValue("@borrowerNumber", b.BorrowerNumber); cmd.Parameters.AddWithValue("@name", b.Name); cmd.Parameters.AddWithValue("@sex", b.Sex); cmd.Parameters.AddWithValue("@address", b.Address); cmd.Parameters.AddWithValue("@telephone", b.Telephone); cmd.Parameters.AddWithValue("@email", b.Email); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); return(true); } catch (Exception) { SqlConnection conn = DAO.getConnections(); SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT Borrower ON;" + "insert into Borrower(borrowerNumber, name, sex, address,telephone,email) " + "values(@borrowerNumber, @name, @sex, @address,@telephone,@email)" + "SET IDENTITY_INSERT Borrower OFF"); cmd.Connection = conn; cmd.Parameters.AddWithValue("@borrowerNumber", b.BorrowerNumber); cmd.Parameters.AddWithValue("@name", b.Name); cmd.Parameters.AddWithValue("@sex", b.Sex); cmd.Parameters.AddWithValue("@address", b.Address); cmd.Parameters.AddWithValue("@telephone", b.Telephone); cmd.Parameters.AddWithValue("@email", b.Email); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); return(true); } }
public static Book GetBook(int bookNumber) { SqlConnection conn = DAO.getConnections(); string sql = "SELECT * FROM Book WHERE bookNumber = @bookNumber"; SqlCommand command = new SqlCommand(sql, conn); command.Parameters.Add(new SqlParameter("@bookNumber", bookNumber)); conn.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Book s = new Book(); s.BookNumber = Int32.Parse(reader["bookNumber"].ToString()); s.Title = reader["title"].ToString(); s.Authors = reader["authors"].ToString(); s.Publisher = reader["publisher"].ToString(); conn.Close(); return(s); } return(null); }
public static Reservation GetFirstReservation(int bookNumber) { SqlConnection conn = DAO.getConnections(); string sql = "SELECT TOP 1 * FROM Reservation WHERE status='R' AND bookNumber = @bookNumber"; SqlCommand command = new SqlCommand(sql, conn); command.Parameters.Add(new SqlParameter("@bookNumber", bookNumber)); conn.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Reservation s = new Reservation(); s.Id = Int32.Parse(reader["ID"].ToString()); s.BorrowerNumber = Int32.Parse(reader["borrowerNumber"].ToString()); s.BookNumber = Int32.Parse(reader["bookNumber"].ToString()); s.Date = Convert.ToDateTime(reader["date"]); s.Status = Char.Parse(reader["status"].ToString()); conn.Close(); return(s); } return(null); }
public static bool Insert(Copy c) { try { SqlConnection conn = DAO.getConnections(); SqlCommand cmd = new SqlCommand("insert into Copy(copyNumber,bookNumber,sequenceNumber,type,price)" + "values (@copyNumber,@bookNumber, @sequenceNumber, @type, @price)"); cmd.Connection = conn; cmd.Parameters.AddWithValue("@copyNumber", c.CopyNumber); cmd.Parameters.AddWithValue("@bookNumber", c.BookNumber); cmd.Parameters.AddWithValue("@sequenceNumber", c.SequenceNumber); cmd.Parameters.AddWithValue("@type", c.Type); cmd.Parameters.AddWithValue("@price", c.Price); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); return(true); } catch (Exception) { SqlConnection conn = DAO.getConnections(); SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT Copy ON;" + "insert into Copy(copyNumber,bookNumber,sequenceNumber,type,price)" + "values (@copyNumber,@bookNumber, @sequenceNumber, @type, @price)" + "SET IDENTITY_INSERT Copy OFF"); cmd.Connection = conn; cmd.Parameters.AddWithValue("@copyNumber", c.CopyNumber); cmd.Parameters.AddWithValue("@bookNumber", c.BookNumber); cmd.Parameters.AddWithValue("@sequenceNumber", c.SequenceNumber); cmd.Parameters.AddWithValue("@type", c.Type); cmd.Parameters.AddWithValue("@price", c.Price); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); return(true); } }
public static Copy GetCopyByBookNumber(int bookNumber) { SqlConnection conn = DAO.getConnections(); string sql = "SELECT * FROM Copy WHERE type='A' AND bookNumber = @bookNumber"; SqlCommand command = new SqlCommand(sql, conn); command.Parameters.Add(new SqlParameter("@bookNumber", bookNumber)); conn.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Copy s = new Copy( Convert.ToInt32(reader["bookNumber"].ToString()) , Convert.ToInt32(reader["copyNumber"].ToString()) , Convert.ToInt32(reader["sequenceNumber"].ToString()) , Char.Parse(reader["type"].ToString()) , float.Parse(reader["price"].ToString())); conn.Close(); return(s); } return(null); }
public static Borrower GetBorrower(int borrowerNumber) { SqlConnection conn = DAO.getConnections(); string sql = "SELECT * FROM Borrower WHERE borrowerNumber = @borrowerNumber"; SqlCommand command = new SqlCommand(sql, conn); command.Parameters.Add(new SqlParameter("@borrowerNumber", borrowerNumber)); conn.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Borrower s = new Borrower(); s.BorrowerNumber = Int32.Parse(reader["borrowerNumber"].ToString()); s.Name = reader["name"].ToString(); s.Sex = Char.Parse(reader["sex"].ToString()); s.Address = reader["address"].ToString(); s.Telephone = reader["telephone"].ToString(); s.Email = reader["email"].ToString(); conn.Close(); return(s); } return(null); }