public static bool Insert(Book b)
{
try
{
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("insert into book(bookNumber, title, authors, publisher) values(@bookNumber, @title, @authors, @publisher)");
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@bookNumber", b.BookNumber);
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)
{
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT Book ON;" +
"insert into book(bookNumber, title, authors, publisher)" +
"values (@bookNumber, @title, @authors, @publisher);" +
"SET IDENTITY_INSERT Book OFF");
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@bookNumber", b.BookNumber);
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);
}
}
public static CirculatedCopy GetCirculatedCopy(int borrowerNumber)
{
SqlConnection conn = DAO.getConnections();
string sql = "SELECT * FROM CirculatedCopy 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())
{
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(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 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);
}
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);
}
