SqlCommand cmd = new SqlCommand(); //khoi tạo command
public DataTable GetPT()
{
DataTable dt = new DataTable();
//gán đói tượng connectiom
cmd.Connection = conn.SQL_CONN;
//lấy tên thủ tục
cmd.CommandText = "Loatnhacungcap";
//proc lấy danh sách dữ liệu
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.OpenConn(); //mơ kết nối
IDataReader reader = cmd.ExecuteReader(); //khai báo một reader
dt.Load(reader);
conn.CloseConn(); //đóng kết nối
}
catch (Exception ex)
{
ex.Message.ToString();
cmd.Dispose();
conn.CloseConn();
}
return(dt);
}
public string dem(string id)
{
DataTable dt = new DataTable();
cmd.CommandText = "select COUNT(*) from MuonSach where maDG='" + id + "' and han < GETDATE() and trangthai = 'dang muon'";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
con.OpenConn();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
string x = dt.Rows[0][0].ToString();
return(x);
}
public bool AddData(string name, string pw) //thêm tài khoản
{
cmd.CommandText = "Insert into Account values ('" + name + "','" + pw + "')";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
string mex = ex.Message;
cmd.Dispose();
con.CloseConn();
}
return(false);
}
public DataTable BarcodeSticker_SCD(string strlenh)
{
var dt = new DataTable();
cmd.CommandText = " SELECT SCD,Barcode,SoLuongCanIn ,SoLuongSKU ,SoLuongSheet_Goi,COUNT(SoLuongSheet_Goi)AS TongSoLuong,SoLuongPcs_Sheet ,NhanVien FROM dbo.tbBarcodeSticker where SCD = '"+ strlenh + "' GROUP BY SCD,Barcode,SoLuongCanIn,SoLuongSKU,SoLuongSheet_Goi,SoLuongPcs_Sheet,NhanVien ORDER BY Barcode asc ";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable LoadData_KhuonBe()
{
var dt = new DataTable();
cmd.CommandText = "select * from tbDanhSachKhuonBe order by KhachHang asc";
cmd.CommandType = CommandType.Text;
cmd.Notification = null;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable LoadData()
{
var dt = new DataTable();
cmd.CommandText = " SELECT * FROM dbo.tbMyDungDuLieu";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable LoadDataNull()
{
var dt = new DataTable();
cmd.CommandText = " Select * from dbo.tbQuanLyTienTe where ketchuyen is null";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable LoadData()
{
var dt = new DataTable();
cmd.CommandText = "select * from dbo.tbKhachHang";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable LoadAllData()
{
var dt = new DataTable();
cmd.CommandText = "select * from tbNhanVien";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception ex)
{
var mex = ex.Message;
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public string checkmk(string ma) //xoa du lieu
{
cmd.CommandText = "select matkhau from thuthu Where matt = '" + ma + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0].ToString());
}
}
catch (Exception ex)
{
dt = null;
string mex = ex.Message;
}
return(dt.Rows.ToString());
}
public DataTable GetData_IDTemDanAD(string strlenh)
{
var dt = new DataTable();
cmd.CommandText = "SELECT STT ,IDTemDanAD ,SO ,Size ,OrderDate,QTY = CASE WHEN QTY = '500' THEN '500 + 5' ELSE qty END FROM dbo.tbTemDanAd where IDTemDanAd = '" + strlenh + "' ";
//SELECT STT ,IDTemDanAD ,SO ,Size ,OrderDate,QTY = CASE WHEN QTY = '500' THEN '500 + 5' ELSE qty END FROM dbo.tbTemDanAd
cmd.CommandType = CommandType.Text;
cmd.Notification = null;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable LoadData()
{
var dt = new DataTable();
cmd.CommandText = "select * from tbDonSanXuat, tbLanhLieu where tbDonSanXuat.SCD = tbLanhLieu.IDLanhLieu";
cmd.CommandType = CommandType.Text;
cmd.Notification = null;
cmd.Connection = con.Connection;
try
{
con.OpenConn(); var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
//var de = new SqlDependency(cmd);
//de.OnChange += de_OnChange;
//dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public string taoma()
{
DataTable dt = new DataTable();
cmd.CommandText = "select COUNT(*) from DocGia ";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
con.OpenConn();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
string x = "S" + dt.Rows[0][0].ToString();
return(x);
}
private void btnLoad_Click(object sender, EventArgs e)
{
con.OpenConn();
SqlConnection sqlcon = con.Connection;
SqlDataAdapter adapter = new SqlDataAdapter("sp_Getdata", sqlcon);
adapter.SelectCommand.Parameters.AddWithValue("@FormDate", Convert.ToDateTime(dtFrom.Value));
adapter.SelectCommand.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(dtTo.Value));
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable da = new DataTable();
adapter.Fill(da);
dtgvDS.DataSource = da;
con.CloseConn();
}
private void frmDS_NhanVien_Load(object sender, EventArgs e)
{
con.OpenConn();
SqlConnection sqlcon = con.Connection;
SqlDataAdapter adapter = new SqlDataAdapter("DS_NhanVien", sqlcon);
//adapter.SelectCommand.Parameters.AddWithValue();
//adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable da = new DataTable();
adapter.Fill(da);
DS_NhanVien ds = new DS_NhanVien();
ds.SetDataSource(da);
crystalReportViewer5.ReportSource = ds;
con.CloseConn();
}
public DataTable PhanQuyen()
{
var dt = new DataTable();
cmd.CommandText = "SELECT * FROM dbo.tbNhanVien , dbo.tbPhanQuyen WHERE tbNhanVien.MaNhanVien = tbPhanQuyen.MaNhanVien";
cmd.CommandType = CommandType.Text;
cmd.Notification = null;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
private void frm_TK_HoaDon_Load(object sender, EventArgs e)
{
con.OpenConn();
SqlConnection sqlcon = con.Connection;
SqlDataAdapter adapter = new SqlDataAdapter("SP_TK_HoaDon", sqlcon);
adapter.SelectCommand.Parameters.AddWithValue("@maHD", _maHD);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable da = new DataTable();
adapter.Fill(da);
ReportDocument rdm = new ReportDocument();
rdm.Load(@"crystalreport\TK_HoaDon.rpt");
rdm.SetDataSource(da);
cry_TK_HoaDon.ReportSource = rdm;
con.CloseConn();
}
public DataTable LoadData_BuHao()
{
var dt = new DataTable();
cmd.CommandText = " SELECT MauSac ,BuHaoOffsetAvery FROM dbo.tbBuHao";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable GetData_NgayXuongDon_SO(string strlenh1, string strlenh2)
{
var dt = new DataTable();
cmd.CommandText = " SELECT * FROM tbthongtingopdonAd WHERE NgayXuongDon = '" + strlenh1 + "' AND SO like '" + strlenh2 + "' ";
cmd.CommandType = CommandType.Text;
cmd.Notification = null;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable checkLog(string user, string pass)
{
ConnectToSQL con = new ConnectToSQL();
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
cmd.CommandText = "select * from NhanVien where MaNV = '" + user + "' and MatKhau = '" + pass + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception ex)
{
string mex = ex.Message;
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable GetData() //lấy dữ liệu
{
DataTable dt = new DataTable();
cmd.CommandText = "select maDG, ht from DocGia";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception ex)
{
string mex = ex.Message;
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
private void btnXacNhan_Click(object sender, EventArgs e)
{
if (txtMa.Text == "")
{
errorProvider1.SetError(txtMa, "Bạn chưa nhập tài khoản.");//thông báo lỗi erroprovider
return;
}
else
if (txtMKC.Text == "")
{
errorProvider1.SetError(txtMKC, "Bạn chưa nhập mật khẩu cũ.");//thông báo lỗi erroprovider
return;
}
else
if (txtLMKM.Text == "")
{
errorProvider1.SetError(txtMKM, "Bạn chưa nhập Mật khẩu mới.");//thông báo lỗi erroprovider
return;
}
else
if (txtLMKM.Text == "")
{
errorProvider1.SetError(txtLMKM, "Bạn chưa nhập lại mật khẩu mới.");//thông báo lỗi erroprovider
return;
}
else
{
try
{
cmd.CommandText = "select COUNT(*) from tb_Account where TaiKhoan=@tk And MatKhau=@mk";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
con.OpenConn();
cmd.Parameters.Add(new SqlParameter("@tk", txtMa.Text));
cmd.Parameters.Add(new SqlParameter("@mk", txtMKC.Text));
int x = (int)cmd.ExecuteScalar();
if (x == 1)
{
if (txtLMKM.Text.Trim() == txtMKM.Text.Trim())
{
try
{
cmd.CommandText = "Update tb_Account set MatKhau='" + txtLMKM.Text.Trim() + "' Where TaiKhoan = '" + txtMa.Text.Trim() + "'";//chuỗi kết nối đến bảng acount
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
con.OpenConn();
cmd.ExecuteNonQuery();
MessageBox.Show("Đổi mật khẩu thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Hide();
}
catch
{
MessageBox.Show("Đổi mk thất bại", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Nhập lại mật khẩu không đúng", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
try
{
cmd.CommandText = "select COUNT(*) from tb_NhanVien where MaNV=@Manv And MatKhau=@mknv";//chuỗi kết nối đến NV acount
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
con.OpenConn();
cmd.Parameters.Add(new SqlParameter("@Manv", txtMa.Text));
cmd.Parameters.Add(new SqlParameter("@mknv", txtMKC.Text));
int x1 = (int)cmd.ExecuteScalar();
if (x1 == 1)
{
if (txtLMKM.Text.Trim() == txtMKM.Text.Trim())
{
try
{
cmd.CommandText = "Update tb_NhanVien set MatKhau='" + txtLMKM.Text + "' Where MaNV = '" + txtMa.Text + "'";//chuỗi kết nối đến bảng acount
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
con.OpenConn();
cmd.ExecuteNonQuery();
MessageBox.Show("Đổi mật khẩu thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Hide();
}
catch
{
MessageBox.Show("Đổi mk thất bại", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Nhập lại mật khẩu không đúng", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception)
{
}
}
}
catch (Exception)
{
MessageBox.Show("Kết nối thất bại");
}
finally
{
con.CloseConn();
}
txtMa.Clear();
txtMKC.Clear();
txtMKM.Clear();
txtLMKM.Clear();
}
}
//public delegate void NewHome();
//public event NewHome OnNewHome;
public DataTable alldata()
{
var dt = new DataTable();
cmd.CommandText = "SELECT scd FROM dbo.tbDonSanXuat";
cmd.CommandType = CommandType.Text;
cmd.Notification = null;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
//var de = new SqlDependency(cmd);
//de.OnChange += de_OnChange;
//dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable GetAllData()
{
var dt = new DataTable();
cmd.CommandText = "select * from tbDonSanXuat_Avery";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
var sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception)
{
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
public DataTable GetData() //lấy dữ liệu
{
DataTable dt = new DataTable();
cmd.CommandText = "select mas,anh,tens,theloai,tenTG,namxb,nhaxb from Sach";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
try
{
con.OpenConn();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception ex)
{
string mex = ex.Message;
cmd.Dispose();
con.CloseConn();
}
return(dt);
}
private void btnXoa_Click(object sender, EventArgs e)
{
if (txtTen.Text == "")
{
errorProvider1.SetError(txtTen, "Bạn chưa nhập tài khỏan");//thông báo lỗi erroprovider
return;
}
else if (txtTen.Text.Trim() == "admin")
{
MessageBox.Show("Bạn không thể xóa tài khoản admin", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtTen.Clear();
}
else
{
try
{
cmd.CommandText = "select COUNT(*) from tb_Account where TaiKhoan=@taiKhoan";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
con.OpenConn();
cmd.Parameters.Add(new SqlParameter("@taiKhoan", txtTen.Text.Trim()));
int x = (int)cmd.ExecuteScalar();
if (x == 1)
{
try
{
DialogResult hoi;
hoi = MessageBox.Show("Bạn có muốn Xóa không", "Thông báo!", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (hoi == DialogResult.Yes)
{
try
{
cmd.CommandText = "Delete tb_Account Where TaiKhoan = '" + txtTen.Text.Trim() + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = con.Connection;
con.OpenConn();
cmd.ExecuteNonQuery();
MessageBox.Show("Xóa thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Hide();
}
catch
{
MessageBox.Show("Xóa thất bại", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
return;
}
catch (Exception)
{
MessageBox.Show("Kết nối thất bại");
}
txtTen.Clear();
}
else
{
lbShow.Text = "Tài khoản không tồn tại";
}
}
catch (Exception)
{
MessageBox.Show("Kết nối thất bại");
}
finally
{
con.CloseConn();
}
}
}
