public List <titlesDTO> selectedTitle()
{
string query = string.Empty;
query += "SELECT DAUSACH.MaDauSach, DAUSACH.TenDauSach, ";
query += "COUNT(*) TONGSACH, SUM(CASE WHEN SACH.DaMuon = 1 THEN 1 ELSE 0 END) SOLUOTMUON ";
query += "FROM SACH inner join DAUSACH on SACH.MaDauSach = DAUSACH.MaDauSach ";
query += "GROUP BY DAUSACH.MaDauSach, DAUSACH.TenDauSach";
List <titlesDTO> lsTitles = new List <titlesDTO>();
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
try
{
con.Open();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
int temp = 0;
while (reader.Read())
{
titlesDTO titles = new titlesDTO();
titles.MaDauSach = reader["MaDauSach"].ToString();
titles.TenDauSach = reader["TenDauSach"].ToString();
titles.TongSoLuongTrongKho = int.Parse(reader["TONGSACH"].ToString());
titles.SoLuotMuon = int.Parse(reader["SOLUOTMUON"].ToString());
temp = titles.TongSoLuongTrongKho - titles.SoLuotMuon;
if (temp > 0)
{
titles.SoLuongConLai = temp;
}
else
{
titles.SoLuongConLai = 0;
}
lsTitles.Add(titles);
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
}
return(lsTitles);
}
private void btnMod_Click(object sender, EventArgs e)
{
authBUS = new authBUS();
titlesDTO titleDTO = new titlesDTO();
// to get MaDauSach
int rowIndex = dgvTitlesManage.CurrentCell.RowIndex;
titleDTO.MaDauSach = dgvTitlesManage.Rows[rowIndex].Cells[0].Value.ToString();
titleDTO.TenDauSach = dgvTitlesManage.Rows[rowIndex].Cells[1].Value.ToString();
//to get auth Code from DAUSACH_TACGIA
//string authReferenceTitle = authBUS.selectedAuthReferenceToTitle(titlesDTO);
frmModTitles frmMod = new frmModTitles();
DialogResult warning = new DialogResult();
warning = MessageBox.Show("Bạn có chắc chắn muốn sửa đầu sách này?", "Cảnh báo!", MessageBoxButtons.YesNo);
if (warning == DialogResult.Yes)
{
frmMod.tbTitlesCode.Text = titleDTO.MaDauSach;
frmMod.tbTitlesName.Text = titleDTO.TenDauSach;
// MessageBox.Show(authReferenceTitle);
// frmMod.lsbAuth.SelectedValue = authReferenceTitle;
frmMod.tbTitlesCode.ReadOnly = true;
frmMod.ShowDialog();
}
else
{
return;
}
LoadDataInto_DataGridViewOfTitle();
}
public List <titlesDTO> selectedTitle()
{
string query = string.Empty;
query += "SELECT MaDauSach, TenDauSach";
query += " FROM DAUSACH";
List <titlesDTO> lsTitles = new List <titlesDTO>();
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
try
{
con.Open();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
titlesDTO titles = new titlesDTO();
titles.MaDauSach = reader["MaDauSach"].ToString();
titles.TenDauSach = reader["TenDauSach"].ToString();
lsTitles.Add(titles);
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
}
return(lsTitles);
}
// to delete a titles
private void btnDel_Click(object sender, EventArgs e)
{
DialogResult warning = new DialogResult();
warning = MessageBox.Show("Bạn có chắc chắn muốn xóa đầu sách này?", "Cảnh báo!", MessageBoxButtons.YesNo);
if (warning == DialogResult.Yes)
{
int rowIndex = dgvTitlesManage.CurrentCell.RowIndex;
titlesDTO = new titlesDTO();
titlesDTO.MaDauSach = dgvTitlesManage.Rows[rowIndex].Cells[0].Value.ToString();
titlesBUS.del(titlesDTO);
}
else
{
return;
}
LoadDataInto_DataGridViewOfTitle();
}
// to select Auth code from DAUSACH_TACGIA TABLE
public string selectedAuthReferenceToTitle(titlesDTO titles)
{
string query = string.Empty;
query += "SELECT MaTacGia ";
query += "FROM DAUSACH_TACGIA WHERE MaDauSach = @mds";
authDTO auth = new authDTO();
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
try
{
con.Open();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
cmd.Parameters.AddWithValue("@mds", titles.MaDauSach);
auth.MaTacGia = reader["MaTacGia"].ToString();
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
}
return(auth.MaTacGia);
}
private void btnAdd_Click(object sender, EventArgs e)
{
//Map data from gui
titlesDTO titlesDTO = new titlesDTO();
titlesDTO.MaDauSach = tbTitlesCode.Text;
titlesDTO.TenDauSach = tbTitlesName.Text;
titlesDTO.MaTacGia = lsbAuth.SelectedValue.ToString();
titlesDTO.TheLoai = lsbType.SelectedValue.ToString();
//Add into DB
bool result = titlesBUS.add(titlesDTO);
if (result == true)
{
MessageBox.Show("Thêm đầu sách thành công");
}
else
{
MessageBox.Show("Thêm đầu sách thất bại");
}
}
public bool mod(titlesDTO titles)
{
bool isMod = titlesDAL.mod(titles);
return(isMod);
}
// to call delete function titles
public bool del(titlesDTO titles)
{
bool isDel = titlesDAL.del(titles);
return(isDel);
}
// to call add function titles
public bool add(titlesDTO titles)
{
bool isAdd = titlesDAL.add(titles);
return(isAdd);
}
public string selectedAuthReferenceToTitle(titlesDTO titlesDTO)
{
return(authDAL.selectedAuthReferenceToTitle(titlesDTO));
}
private void loadDataIntoForm()
{
titlesDTO = new titlesDTO();
}
public bool add(titlesDTO titles)
{
//add into DauSach table
string queryAddTitles = string.Empty;
queryAddTitles += "INSERT INTO DAUSACH (MaDauSach, TenDauSach) ";
queryAddTitles += "VALUES (@MaDauSach, @TenDauSach)";
//add author into DauSach_TacGia table
string queryAddAuth = string.Empty;
queryAddAuth += "INSERT INTO DAUSACH_TACGIA (MaDauSach, MaTacGia) ";
queryAddAuth += "VALUES (@MaDauSach, @MaTacGia)";
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;")) //Init connection to host
{
// to add titles and name of titles into table DAUSACH
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = queryAddTitles;
cmd.Parameters.AddWithValue("@MaDauSach", titles.MaDauSach);
cmd.Parameters.AddWithValue("@TenDauSach", titles.TenDauSach);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;"))
{
using (SqlCommand cmd2 = new SqlCommand())
{
cmd2.Connection = con;
cmd2.CommandType = System.Data.CommandType.Text;
cmd2.CommandText = queryAddAuth;
cmd2.Parameters.AddWithValue("@MaDauSach", titles.MaDauSach);
cmd2.Parameters.AddWithValue("@MaTacGia", titles.MaTacGia);
try
{
con.Open();
cmd2.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
// to modify
public bool mod(titlesDTO titles)
{
//modify titles from DauSach table
string queryModTitles = string.Empty;
queryModTitles += "UPDATE DAUSACH SET ";
queryModTitles += "TenDauSach = @tds WHERE MaDauSach = @mds";
//delete author from DauSach_TacGia table
string queryModAuth = string.Empty;
queryModAuth += "UPDATE DAUSACH_TACGIA SET ";
queryModAuth += "MaTacGia = @mtg WHERE MaDauSach = @mds ";
//delte titles from DauSach_TheLoai table
string queryModType = string.Empty;
queryModType += "UPDATE DAUSACH_THELOAI SET ";
queryModType += "MaTheLoai = @mtl WHERE MaDauSach = @mds ";
// to modify titles from table DAUSACH_TACGIA
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = queryModAuth;
cmd.Parameters.AddWithValue("@mds", titles.MaDauSach);
cmd.Parameters.AddWithValue("@mtg", titles.MaTacGia);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
// to modify titles from table DauSach_TheLoai table
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;"))
{
using (SqlCommand cmd2 = new SqlCommand())
{
cmd2.Connection = con;
cmd2.CommandType = System.Data.CommandType.Text;
cmd2.CommandText = queryModType;
cmd2.Parameters.AddWithValue("@mds", titles.MaDauSach);
cmd2.Parameters.AddWithValue("@mtl", titles.TheLoai);
try
{
con.Open();
cmd2.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
// to modify titles and name of titles from table DAUSACH
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;")) //Init connection to host
{
using (SqlCommand cmd3 = new SqlCommand())
{
cmd3.Connection = con;
cmd3.CommandType = System.Data.CommandType.Text;
cmd3.CommandText = queryModTitles;
cmd3.Parameters.AddWithValue("@mds", titles.MaDauSach);
cmd3.Parameters.AddWithValue("@tds", titles.TenDauSach);
try
{
con.Open();
cmd3.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
// to delete titles from DAUSACH table and referencing table
public bool del(titlesDTO titles)
{
string queryDelTitles = string.Empty;
queryDelTitles += "DELETE FROM DAUSACH ";
queryDelTitles += "WHERE MaDauSach = @mds";
//delete author from DauSach_TacGia table
string queryDelAuth = string.Empty;
queryDelAuth += "DELETE FROM DAUSACH_TACGIA ";
queryDelAuth += "WHERE MaDauSach = @mds ";
//delte titles from DauSach_TheLoai table
string queryDelType = string.Empty;
queryDelType += "DELETE FROM DAUSACH_THELOAI ";
queryDelType += "WHERE MaDauSach = @mds ";
//delete titles from SACH table
string queryDelBook = string.Empty;
queryDelBook += "DELETE FROM SACH ";
queryDelBook += "WHERE MaDauSach = @mds ";
// to delete titles from table SACH
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = queryDelBook;
cmd.Parameters.AddWithValue("@mds", titles.MaDauSach);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
// to delete titles from table DAUSACH_TACGIA
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = queryDelAuth;
cmd.Parameters.AddWithValue("@mds", titles.MaDauSach);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
// to delete titles from table DauSach_TheLoai table
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;"))
{
using (SqlCommand cmd2 = new SqlCommand())
{
cmd2.Connection = con;
cmd2.CommandType = System.Data.CommandType.Text;
cmd2.CommandText = queryDelType;
cmd2.Parameters.AddWithValue("@mds", titles.MaDauSach);
try
{
con.Open();
cmd2.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
// to delete titles and name of titles from table DAUSACH
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;")) //Init connection to host
{
using (SqlCommand cmd3 = new SqlCommand())
{
cmd3.Connection = con;
cmd3.CommandType = System.Data.CommandType.Text;
cmd3.CommandText = queryDelTitles;
cmd3.Parameters.AddWithValue("@mds", titles.MaDauSach);
try
{
con.Open();
cmd3.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
