// Get one book
public BookDetail GetBook(int book_id, out string errormsg)
{
// Skapa SqlConnection
SqlConnection dbConnection = new SqlConnection();
// Koppling mot SQL Server
dbConnection.ConnectionString = @"Data Source=DESKTOP-RBA35NK;Initial Catalog=lab3;Integrated Security=True";
// sqlstring och hämta alla böcker från databasen
String sqlstring = "SELECT * FROM Tbl_Book WHERE B_Id = @id;";
SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);
dbCommand.Parameters.Add("id", SqlDbType.Int).Value = book_id;
SqlDataAdapter adapter = new SqlDataAdapter(dbCommand);
DataSet ds = new DataSet();
try
{
dbConnection.Open();
adapter.Fill(ds, "book");
int count = 0;
int i = 0;
count = ds.Tables["book"].Rows.Count;
if (count > 0)
{
BookDetail Book = new BookDetail();
Book.Id = Convert.ToInt32(ds.Tables["book"].Rows[i]["B_Id"]);
Book.Title = ds.Tables["book"].Rows[i]["B_Title"].ToString();
Book.Description = ds.Tables["book"].Rows[i]["B_Description"].ToString();
Book.Price = Convert.ToDecimal(ds.Tables["book"].Rows[i]["B_Price"]);
Book.Usr_Id = Convert.ToInt32(ds.Tables["book"].Rows[i]["Usr_Id"]);
errormsg = "";
return(Book);
}
else
{
errormsg = "Ingen bok hittades.";
return(null);
}
}
catch (Exception e)
{
errormsg = e.Message;
return(null);
}
finally
{
dbConnection.Close();
}
}
public List <BookDetail> FilterBooks(out string errormsg, int id)
{
// Skapa SqlConnection
SqlConnection dbConnection = new SqlConnection();
// Koppling mot SQL Server
dbConnection.ConnectionString = @"Data Source=DESKTOP-RBA35NK;Initial Catalog=lab3;Integrated Security=True";
// sqlstring och lägg till en user i databasen
String sqlstring = "SELECT * FROM Tbl_Book WHERE B_Id = @id";
SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);
dbCommand.Parameters.Add("id", SqlDbType.Int).Value = id;
SqlDataReader reader = null;
List <BookDetail> Booklist = new List <BookDetail>();
try
{
// Open the connection
dbConnection.Open();
// 1. Get an instance of the SqlDataReader
reader = dbCommand.ExecuteReader();
// 2. read necessary columns of each record
while (reader.Read())
{
BookDetail Book = new BookDetail();
Book.Id = Convert.ToInt32(reader["B_Id"]);
Book.Title = reader["B_Title"].ToString();
Book.Description = reader["B_Description"].ToString();
Book.Price = Convert.ToDecimal(reader["B_Price"]);
Book.Usr_Id = Convert.ToInt32(reader["Usr_Id"]);
Booklist.Add(Book);
}
reader.Close();
errormsg = "";
return(Booklist);
}
catch (Exception e)
{
errormsg = e.Message;
return(Booklist);
}
finally
{
dbConnection.Close();
}
}
// Update information about a book
public int EditBook(BookDetail bd, out string errormsg)
{
// Skapa SqlConnection
SqlConnection dbConnection = new SqlConnection();
// Koppling mot SQL Server
dbConnection.ConnectionString = @"Data Source=DESKTOP-RBA35NK;Initial Catalog=lab3;Integrated Security=True";
// sqlstring och lägg till en user i databasen
String sqlstring = "UPDATE Tbl_Book SET B_Title = @title, B_Description = @description, B_Price = @price WHERE B_Id = @id;";
SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);
dbCommand.Parameters.Add("id", SqlDbType.Int).Value = bd.Id;
dbCommand.Parameters.Add("title", SqlDbType.NVarChar, 50).Value = bd.Title;
dbCommand.Parameters.Add("description", SqlDbType.NVarChar).Value = bd.Description;
dbCommand.Parameters.Add("price", SqlDbType.Decimal).Value = bd.Price;
try
{
dbConnection.Open();
int i = 0;
i = dbCommand.ExecuteNonQuery();
if (i == 1)
{
errormsg = "";
}
else
{
errormsg = "No book updated.";
}
return(i);
}
catch (Exception e)
{
errormsg = e.Message;
return(0);
}
finally
{
dbConnection.Close();
}
}