public static List <Employee> ListRecords()
{
List <Employee> listEmp = new List <Employee>();
string sqlSelect = "Select * From Employees ";
SqlConnection sqlConn = new SqlConnection();
sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlSelect, sqlConn);
SqlDataReader sqlReader = sqlcmd.ExecuteReader();
while (sqlReader.Read())
{
Employee emp = new Employee();
emp.EmployeeId = Convert.ToInt32(sqlReader["EmployeeId"]);
emp.FirstName = sqlReader["FirstName"].ToString();
emp.LastName = sqlReader["LastName"].ToString();
emp.JobTitle = sqlReader["JobTitle"].ToString();
listEmp.Add(emp);
}
sqlConn.Close();
return(listEmp);
}
/*********************************************Software************************************************/
public static bool saveSoftware(Software software)
{
Category cat = new Category();
bool success = false;
try
{
string sqlInsert = "Insert INTO Softwares (SoftwareId, SoftwareName, CategorieId) " +
"SELECT @SoftwareId, @SoftwareName, c.CategorieId FROM Categories c, Softwares " +
"WHERE Softwares.CategorieId = c.CategorieId ";
// MessageBox.Show(sqlInsert);//Test
SqlConnection sqlconn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlInsert, sqlconn);
sqlcmd.Parameters.AddWithValue("@SoftwareId", software.SoftwareId);
sqlcmd.Parameters.AddWithValue("@SoftwareName", software.SoftwareName);
sqlcmd.Parameters.AddWithValue("@CategorieId", cat.CategorieId);
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
success = true;
}
catch (Exception ex)
{
throw ex;
}
return(success);
}
public static List <User> ListUserRecord()
{
List <User> listUser = new List <User>();
string sqlSelect = "Select * From Users ";
SqlConnection sqlConn = new SqlConnection();
sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlSelect, sqlConn);
SqlDataReader sqlReader = sqlcmd.ExecuteReader();
while (sqlReader.Read())
{
User user = new User();
user.UserId = Convert.ToInt32(sqlReader["UserId"].ToString());
user.Password = sqlReader["Password"].ToString();
listUser.Add(user);
}
sqlConn.Close();
return(listUser);
}
public static List <Book> listBook(int AuthorId)
{
List <Book> list = new List <Book>();
string sqlSelect = "Select ab.Isbn, Title, LimitPrice, YearPublished, QOH From AuthorBook ab, Authors a, Books b " +
"Where ab.AuthorId= a.AuthorId " +
"AND ab.Isbn = b.Isbn " +
"AND a.AuthorId = " + AuthorId;
SqlConnection sqlConn = new SqlConnection();
sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlSelect, sqlConn);
SqlDataReader sqlReader = sqlcmd.ExecuteReader();
while (sqlReader.Read())
{
Book book = new Book();
book.Isbn = Convert.ToInt32(sqlReader["Isbn"].ToString());
book.Title = sqlReader["Title"].ToString();
book.LimitPrice = Convert.ToInt32(sqlReader["LimitPrice"].ToString());
book.YearPublished = Convert.ToInt32(sqlReader["YearPublished"].ToString());
book.QOH = Convert.ToInt32(sqlReader["QOH"].ToString());
list.Add(book);
}
sqlConn.Close();
return(list);
}
/*********************************************Book**********************************************************/
public static bool saveBook(Book book)
{
Category cat = new Category(); Publisher pub = new Publisher();
bool success = false;
try
{
string sqlInsert = "Insert INTO Books (Isbn, Title, LimitPrice, YearPublished, QOH, CategorieId, PublisherId) " +
"SELECT @Isbn, @Title, @LimitPrice, @YearPublished, @QOH, c.CategorieId, p.PublisherId FROM Categories c, Publishers p, Books " +
"WHERE Books.CategorieId = c.CategorieId AND Books.PublisherId = p.PublisherId";
// MessageBox.Show(sqlInsert);//Test
SqlConnection sqlconn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlInsert, sqlconn);
sqlcmd.Parameters.AddWithValue("@Isbn", book.Isbn);
sqlcmd.Parameters.AddWithValue("@Title", book.Title);
sqlcmd.Parameters.AddWithValue("@LimitPrice", book.LimitPrice);
sqlcmd.Parameters.AddWithValue("@YearPublished", book.YearPublished);
sqlcmd.Parameters.AddWithValue("@QOH", book.QOH);
sqlcmd.Parameters.AddWithValue("@CategorieId", cat.CategorieId);
sqlcmd.Parameters.AddWithValue("@PublisherId", pub.PublisherId);
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
success = true;
}
catch (Exception ex)
{
throw ex;
}
return(success);
}
public static void DeleteSoftware(Software software)
{
string sqlDelete = "Delete From Softwares WHERE SoftwareId=@SoftwareId";
SqlConnection sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlDelete, sqlConn);
sqlcmd.Parameters.AddWithValue("@SoftwareId", software.SoftwareId);
sqlcmd.Parameters.AddWithValue("@SoftwareName", software.SoftwareName);
sqlcmd.ExecuteNonQuery();
sqlConn.Close();
}
public static void DeleteUser(User user)
{
string sqlDelete = "Delete From Users Where UserId=@UserId";
SqlConnection sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlDelete, sqlConn);
sqlcmd.Parameters.AddWithValue("@UserId", user.UserId);
sqlcmd.Parameters.AddWithValue("@Password", user.Password);
sqlcmd.ExecuteNonQuery();
sqlConn.Close();
}
public static void UpdateUser(User user)
{
string sqlUpdate = "Update Users " +
"SET Password=@Password WHERE UserId=@UserId";
SqlConnection sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlUpdate, sqlConn);
sqlcmd.Parameters.AddWithValue("@UserId", user.UserId);
sqlcmd.Parameters.AddWithValue("@Password", user.Password);
sqlcmd.ExecuteNonQuery();
sqlConn.Close();
}
public static void DeleteEmployee(Employee emp)
{
string sqlDelete = "Delete From Employees Where EmployeeId=@EmployeeId";
SqlConnection sqlConn = UtilityDB.ConnectDB();//to connect to the database
//Customize the sqlCommand object
SqlCommand sqlcmd = new SqlCommand(sqlDelete, sqlConn);
sqlcmd.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId);
sqlcmd.Parameters.AddWithValue("@FirstName", emp.FirstName);
sqlcmd.Parameters.AddWithValue("@LastName", emp.LastName);
sqlcmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle);
sqlcmd.ExecuteNonQuery();
sqlConn.Close();
}
public static Software Searchsoftware(int softwareID)
{
Software soft = new Software();
string sqlSelect = "Select * From Softwares " + "Where SoftwareId= " + softwareID;
SqlConnection sqlconn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlSelect, sqlconn);
SqlDataReader sqlReader = sqlcmd.ExecuteReader();
if (sqlReader.Read())
{
soft.SoftwareId = Convert.ToInt32(sqlReader["SoftwareId"].ToString());
soft.SoftwareName = sqlReader["SoftwareName"].ToString();
}
return(soft);
}
//Code for User Authentication***************************************
public static Boolean Authentication(int UserId, string Password)
{
// User user = new User();
string sqlSelect = "Select UserId, Password from Users " +
"Where UserId = " + UserId +
"AND Password = '******'";
SqlConnection sqlconn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlSelect, sqlconn);
SqlDataReader sqlReader = sqlcmd.ExecuteReader();
if (sqlReader.Read())
{
return(true);
}
return(false);
}
public static void DeleteBook(Book book)
{
string sqlDelete = "Delete From Books Where Isbn=@Isbn";
SqlConnection sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlDelete, sqlConn);
sqlcmd.Parameters.AddWithValue("@Isbn", book.Isbn);
sqlcmd.Parameters.AddWithValue("@Title", book.Title);
sqlcmd.Parameters.AddWithValue("@LimitPrice", book.LimitPrice);
sqlcmd.Parameters.AddWithValue("@YearPublished", book.YearPublished);
sqlcmd.Parameters.AddWithValue("@QOH", book.QOH);
sqlcmd.ExecuteNonQuery();
sqlConn.Close();
}
public static void UpdateSoftware(Software soft)
{
string sqlUpdate = "Update Softwares " +
"SET SoftwareName=@SoftwareName Where SoftwareId=@SoftwareId";
MessageBox.Show(sqlUpdate);
SqlConnection sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlUpdate, sqlConn);
sqlcmd.Parameters.AddWithValue("@SoftwareId", soft.SoftwareId);
sqlcmd.Parameters.AddWithValue("@SoftwareName", soft.SoftwareName);
sqlcmd.ExecuteNonQuery();
sqlConn.Close();
}
public static Book SearchBook(int Isbn)
{
Book book = new Book();
string sqlSelect = "Select * From Books " + "Where Isbn= " + Isbn;
SqlConnection sqlconn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlSelect, sqlconn);
SqlDataReader sqlReader = sqlcmd.ExecuteReader();
if (sqlReader.Read())
{
book.Isbn = Convert.ToInt32(sqlReader["Isbn"].ToString());
book.Title = sqlReader["Title"].ToString();
book.LimitPrice = Convert.ToInt32(sqlReader["LimitPrice"].ToString());
book.YearPublished = Convert.ToInt32(sqlReader["YearPublished"].ToString());
book.QOH = Convert.ToInt32(sqlReader["QOH"].ToString());
}
return(book);
}
public static void UpdateBook(Book book)
{
string sqlUpdate = "Update Books " +
"SET Title=@Title, LimitPrice=@LimitPrice, YearPublished=@YearPublished, QOH=@QOH Where Isbn=@Isbn ";
MessageBox.Show(sqlUpdate);
SqlConnection sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlUpdate, sqlConn);
sqlcmd.Parameters.AddWithValue("@Isbn", book.Isbn);
sqlcmd.Parameters.AddWithValue("@Title", book.Title);
sqlcmd.Parameters.AddWithValue("@LimitPrice", book.LimitPrice);
sqlcmd.Parameters.AddWithValue("@YearPublished", book.YearPublished);
sqlcmd.Parameters.AddWithValue("@QOH", book.QOH);
sqlcmd.ExecuteNonQuery();
sqlConn.Close();
}
public static User searchUSerRecord(int UserId)
{
string sqlSelect = "Select * From Users " + "Where UserId= " + UserId;
SqlConnection sqlconn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlSelect, sqlconn);
SqlDataReader sqlReader = sqlcmd.ExecuteReader();
User user = new User();
if (sqlReader.Read())
{
user.UserId = Convert.ToInt32(sqlReader["UserId"].ToString());
user.Password = sqlReader["Password"].ToString();
}
else
{
user = null;
}
sqlconn.Close();
return(user);
}
public static List <Software> listsoftware()
{
List <Software> list = new List <Software>();
string sqlSelect = "Select SoftwareId, SoftwareName From Softwares";
SqlConnection sqlConn = new SqlConnection();
sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlSelect, sqlConn);
SqlDataReader sqlReader = sqlcmd.ExecuteReader();
while (sqlReader.Read())
{
Software soft = new Software();
soft.SoftwareId = Convert.ToInt32(sqlReader["SoftwareId"].ToString());
soft.SoftwareName = sqlReader["SoftwareName"].ToString();
list.Add(soft);
}
sqlConn.Close();
return(list);
}
public static Employee searchRecord(int empId)
{
string sqlSelect = "Select * From Employees " + "Where EmployeeId= " + empId;
SqlConnection sqlconn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlSelect, sqlconn);
SqlDataReader sqlReader = sqlcmd.ExecuteReader();
Employee emp = new Employee();
if (sqlReader.Read())
{
emp.EmployeeId = Convert.ToInt32(sqlReader["EmployeeId"].ToString());
emp.FirstName = sqlReader["FirstName"].ToString();
emp.LastName = sqlReader["LastName"].ToString();
emp.JobTitle = sqlReader["JobTitle"].ToString();
}
else
{
emp = null;
}
sqlconn.Close();
return(emp);
}
public static bool SaveEmployee(Employee emp)
{
bool success = false;
try
{
string sqlInsert = "Insert into Employees " + "Values(@EmployeeId, @FirstName, @LastName, @JobTitle)";
SqlConnection sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlInsert, sqlConn);
sqlcmd.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId);
sqlcmd.Parameters.AddWithValue("@FirstName", emp.FirstName);
sqlcmd.Parameters.AddWithValue("@LastName", emp.LastName);
sqlcmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle);
sqlcmd.ExecuteNonQuery();
sqlConn.Close();
success = true;
}
catch (SqlException ex)
{
throw ex;
}
return(success);
}
/*******************************************AuthorBook************************************************/
/**********************************Add Data into AuthorBooks******************************************/
public static bool saveauthBook(AuthorBook AuthBook)
{
bool success = false;
try
{
string sqlInsert = "Insert INTO AuthorBook " + "Values(@AuthorId, @Isbn, @Date)";
SqlConnection sqlconn = UtilityDB.ConnectDB();
SqlCommand sqlCmd = new SqlCommand(sqlInsert, sqlconn);
sqlCmd.Parameters.AddWithValue("@AuthorId", AuthBook.AuthorId);
sqlCmd.Parameters.AddWithValue("@Isbn", AuthBook.Isbn);
sqlCmd.Parameters.AddWithValue("@Date", AuthBook.Date);
sqlCmd.ExecuteReader();
sqlconn.Close();
success = true;
}
catch (SqlException ex)
{
throw ex;
}
return(success);
}
public static bool AddUser(User user)
{
bool success = false;
try
{
string sqlInsert = "Insert into Users " + "Values(@UserId, @Password)";
SqlConnection sqlConn = UtilityDB.ConnectDB();
SqlCommand sqlcmd = new SqlCommand(sqlInsert, sqlConn);
sqlcmd.Parameters.AddWithValue("@UserId", user.UserId);
sqlcmd.Parameters.AddWithValue("@Password", user.Password);
sqlcmd.ExecuteNonQuery();
sqlConn.Close();
success = true;
}
catch (SqlException ex)
{
throw ex;
}
return(success);
}
