コード例 #1
0
 public DataTable getPlanList()
 {
     result.Clear();
     sqlQuery = "Select trim(GoiTap.ma) + ' - ' + trim(GoiTap.ten) as List" +
                " From  GoiTap ";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 SqlDataAdapter da = new SqlDataAdapter(cmd);
                 // this will query your database and return the result to your datatable
                 da.Fill(result);
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
     return(result);
 }
コード例 #2
0
ファイル: Attendance.cs プロジェクト: hvtanh07/Gym-management
        public DataTable searchData(string keyword)
        {
            result.Clear();
            sqlQuery  = " select * from [Attendance]";
            sqlQuery += " WHERE ([ma] LIKE CONCAT('%','" + keyword + "','%'))";
            sqlQuery += " OR ([alias] LIKE CONCAT('%','" + keyword + "','%'))";

            conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        con.Open();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        // this will query your database and return the result to your datatable
                        da.Fill(result);
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            return(result);
        }
コード例 #3
0
        public int getDuration(string maGoi)
        {
            result.Clear();
            sqlQuery = "select thoiHan from GoiTap where ma = '" + maGoi + "'";
            int duration = 1;

            conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        con.Open();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        // this will query your database and return the result to your datatable
                        duration = (int)cmd.ExecuteScalar();
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            return(duration);
        }
コード例 #4
0
 public DataTable getIncomeofYear(DateTime fromDate, DateTime toDate)
 {
     result.Clear();
     sqlQuery = "SELECT YEAR(dateOfPay) as SalesYear, SUM(price) AS TotalSales" +
                " FROM MonthlyIncome" +
                " WHERE dateOfPay >= '" + fromDate + "' AND dateOfPay <= '" + toDate + "'" +
                " GROUP BY YEAR(dateOfPay)" +
                " ORDER BY YEAR(dateOfPay)";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 SqlDataAdapter da = new SqlDataAdapter(cmd);
                 // this will query your database and return the result to your datatable
                 da.Fill(result);
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
     return(result);
 }
コード例 #5
0
ファイル: hoiVien.cs プロジェクト: hvtanh07/Gym-management
 public DataTable getMemberpaymentinfo(String maHV)
 {
     sqlQuery = "Select Member.ten as HoiVien, MonthlyIncome.dateOfPay," +
                " trim(GoiTap.ma) + ' - ' + trim(GoiTap.ten) as Goi, MonthlyIncome.price" +
                " From MonthlyIncome inner join Member on Member.ma = MonthlyIncome.maKH inner join" +
                " GoiTap	on GoiTap.ma = MonthlyIncome.maGoiTap" +
                " Where Member.ma = '" + maHV + "'";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 SqlDataAdapter da = new SqlDataAdapter(cmd);
                 // this will query your database and return the result to your datatable
                 da.Fill(result);
                 con.Close();
                 con.Dispose();
             }
         }
     }
     catch (SqlException ex)
     {
     }
     return(result);
 }
コード例 #6
0
ファイル: hoiVien.cs プロジェクト: hvtanh07/Gym-management
        public DateTime getMemberExpireday(String maHV)
        {
            DateTime date = DateTime.Today;

            sqlQuery = "Select DATEADD(MONTH, SUM(PaymentPeriod), Member.ngayThamGia) AS expireDate" +
                       " From Member inner join MonthlyIncome on Member.ma = MonthlyIncome.maKH" +
                       " Where Member.ma = '" + maHV + "'" +
                       " Group by Member.ngayThamGia, Member.ma";
            conString.ConString constring = new conString.ConString();
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        con.Open();
                        date = DateTime.Parse(cmd.ExecuteScalar().ToString());
                        con.Dispose();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            return(date);
        }
コード例 #7
0
        public bool getRefication(string UserName, string password)
        {
            sqlQuery = "SELECT CASE WHEN EXISTS ( " +
                       "SELECT * FROM LogInData " +
                       "WHERE username = '******' and passW = '" + password + "') " +
                       "THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END";
            conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        //con.Open();
                        //SqlDataAdapter da = new SqlDataAdapter(cmd);
                        //// this will query your database and return the result to your datatable

                        //con.Close();

                        con.Open();
                        result = (bool)cmd.ExecuteScalar();
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                //con.Close();
            }
            return(result);
        }
コード例 #8
0
        public DataTable getData()
        {
            DataTable result = new DataTable();

            sqlQuery = "select * from [Log]";
            conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        con.Open();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        // this will query your database and return the result to your datatable
                        da.Fill(result);
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            return(result);
        }
コード例 #9
0
        public string getID(string UserName)
        {
            sqlQuery = "Select NHANVIEN.ID from NHANVIEN inner join LogInData on NHANVIEN.ID = LogInData.ma " +
                       "where LogInData.username = '******'";
            conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        //con.Open();
                        //SqlDataAdapter da = new SqlDataAdapter(cmd);
                        //// this will query your database and return the result to your datatable

                        //con.Close();

                        con.Open();
                        ID = cmd.ExecuteScalar().ToString();
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                //con.Close();
            }
            return(ID);
        }
コード例 #10
0
        public DataTable searchData(string keyword)
        {
            sqlQuery  = " select * from [TrangThietBi]";
            sqlQuery += " WHERE ([ten] LIKE CONCAT('%',@sKeyword,'%'))";
            sqlQuery += " OR ([ma] LIKE CONCAT('%',@sKeyword,'%'))";
            sqlQuery += " OR ([tinhTrang] LIKE CONCAT('%',@sKeyword,'%'))";
            sqlQuery += " OR ([ngayMua] LIKE CONCAT('%',@sKeyword,'%'))";
            sqlQuery += " OR ([giaThanh] LIKE CONCAT('%',@sKeyword,'%'))";
            sqlQuery += " OR ([baoHanh] LIKE CONCAT('%',@sKeyword,'%'))";
            sqlQuery += " OR ([ngayHetHanBaoHanh] LIKE CONCAT('%',@sKeyword,'%'))";
            sqlQuery += " OR ([hangSanXuat] LIKE CONCAT('%',@sKeyword,'%'))";

            conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        cmd.Parameters.AddWithValue("@sKeyword", keyword);
                        con.Open();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        // this will query your database and return the result to your datatable
                        da.Fill(result);
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            return(result);
        }
コード例 #11
0
ファイル: Attendance.cs プロジェクト: hvtanh07/Gym-management
        public DataTable searchDataintimespan(DateTime from, DateTime to)
        {
            result.Clear();
            sqlQuery  = " select * from [Attendance]";
            sqlQuery += " WHERE thoigian BETWEEN '" + from + "' AND '" + to + "';";

            conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        con.Open();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        // this will query your database and return the result to your datatable
                        da.Fill(result);
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            return(result);
        }
コード例 #12
0
ファイル: hoiVien.cs プロジェクト: hvtanh07/Gym-management
 public DataTable getMemberofMonth(DateTime fromDate, DateTime toDate)
 {
     sqlQuery = "SELECT bucketName AS thang," +
                " (SELECT COUNT(*) FROM Member WHERE ngayThamGia <= bucketLastDay)" +
                "- (SELECT COUNT(*) FROM Member WHERE ngayKetThuc<bucketFirstDay) as total" +
                " FROM monthBucket WHERE bucketLastDay >= '" + fromDate + "' AND bucketFirstDay <= '" + toDate + "'" +
                " ORDER BY bucketFirstDay";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 SqlDataAdapter da = new SqlDataAdapter(cmd);
                 // this will query your database and return the result to your datatable
                 da.Fill(result);
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
     return(result);
 }
コード例 #13
0
        public int getPermission(string UserName)
        {
            sqlQuery = "Select LogInData.permissionLevel from LogInData where LogInData.username =  '******'";

            conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        //con.Open();
                        //SqlDataAdapter da = new SqlDataAdapter(cmd);
                        //// this will query your database and return the result to your datatable

                        //con.Close();

                        con.Open();
                        permission = (int)cmd.ExecuteScalar();
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                //con.Close();
            }
            return(permission);
        }
コード例 #14
0
 public void Deleteinactive()
 {
     sqlQuery = "Delete from Member where ma in (Select ma from Member Where dateadd(yy,-2,getdate()) > ngayKetThuc)";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 SqlDataAdapter da = new SqlDataAdapter(cmd);
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
 }
コード例 #15
0
 public void Insert(string maNV, int level, string UserName, string pass)
 {
     sqlQuery = "INSERT INTO LogInData (maNV, permissionLevel, username, passW) VALUES ('" + maNV + "'," + level + ",'" + UserName + "','" + pass + "')";
     conString.ConString constring = new conString.ConString();
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 cmd.ExecuteNonQuery();
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
 }
コード例 #16
0
 public void deleteData(Class.trangThietBi data)
 {
     sqlQuery = "delete from TrangThietBi where ma ='" + data.ma + "'";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 cmd.ExecuteNonQuery();
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
 }
コード例 #17
0
ファイル: Attendance.cs プロジェクト: hvtanh07/Gym-management
 public void Insert(AttendanceClass attendanceClass)
 {
     sqlQuery = "insert into Attendance (ma,thoigian, alias) " +
                " values ('" + attendanceClass.ma + "','" + attendanceClass.attendanceTime + "', N'" + attendanceClass.alias + "')";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 cmd.ExecuteNonQuery();
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
 }
コード例 #18
0
 public void MakememberInactive()
 {
     sqlQuery = "Update Member set ngayKetThuc = getdate()" +
                "where ma in (select ma from Attendance group by ma having dateadd(yy,-1,getdate()) > max(thoigian))";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 SqlDataAdapter da = new SqlDataAdapter(cmd);
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
 }
コード例 #19
0
 public void insertData(Class.trangThietBi data)
 {
     sqlQuery = "insert into TrangThietBi (ten, ma, tinhTrang, ngayMua, giaThanh, baoHanh, ngayHetHanBaoHanh, hangSanXuat) values (N'" +
                data.ten + "','" + data.ma + "',N'" + data.tinhTrang + "','" +
                data.ngayMua + "'," + data.giaThanh + "," + data.baoHanh + ",'" + data.ngayHetHanBaoHanh + "',N'" + data.hangSanXuat + "') ";
     conString.ConString constring = new conString.ConString();
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 cmd.ExecuteNonQuery();
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
 }
コード例 #20
0
 public void updateData(Class.trangThietBi data)
 {
     sqlQuery = "update TrangThietBi set ten = N'" + data.ten + "', tinhTrang = N'" +
                data.tinhTrang + "', ngayMua= '" + data.ngayMua + "', giaThanh=" + data.giaThanh + ", baoHanh=" + data.baoHanh +
                ", ngayHetHanBaoHanh= '" + data.ngayHetHanBaoHanh + "', hangSanXuat= '" + data.hangSanXuat + "' where ma ='" + data.ma + "'";
     conString.ConString constring = new conString.ConString();
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 cmd.ExecuteNonQuery();
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
 }
コード例 #21
0
ファイル: hoiVien.cs プロジェクト: hvtanh07/Gym-management
        public string GetIDfromName(string ten)
        {
            sqlQuery = "select ma from Member where ten = N'" + ten + "'";
            string ma = "";

            conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        con.Open();
                        ma = cmd.ExecuteScalar().ToString();
                        con.Dispose();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            return(ma);
        }
コード例 #22
0
        public void Insert(string task)
        {
            valiballecommon valiballecommon = valiballecommon.GetStorage();

            sqlQuery = "insert into Log (username, thoigian, task) values ('" +
                       valiballecommon.UserName + "',N'" + DateTime.Now + "', N'" + task + "')";
            conString.ConString constring = new conString.ConString();
            try
            {
                using (var con = new SqlConnection(constring.initString()))
                {
                    using (var cmd = new SqlCommand(sqlQuery, con))
                    {
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
        }
コード例 #23
0
ファイル: hoiVien.cs プロジェクト: hvtanh07/Gym-management
 public DataTable AddPayment(string maKH, string maGoiTap, DateTime dateOfPay, int PaymentPeriod, int price)
 {
     sqlQuery = "insert into MonthlyIncome (maKH, maGoiTap, dateOfPay, PaymentPeriod, price) " +
                " values ('" + maKH + "','" + maGoiTap + "','" + dateOfPay + "'," + PaymentPeriod + "," + price + ")";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 cmd.ExecuteNonQuery();
                 con.Close();
                 con.Dispose();
             }
         }
     }
     catch (SqlException ex)
     {
     }
     return(result);
 }
コード例 #24
0
ファイル: hoiVien.cs プロジェクト: hvtanh07/Gym-management
 public DataTable DeletePayment(string maKH, DateTime dateOfPay)
 {
     sqlQuery = "Delete from MonthlyIncome where maKH = '" + maKH + "' AND " +
                "year(dateOfPay) = '" + dateOfPay.Year + "' and month(dateOfPay) = '" + dateOfPay.Month + "' and day(dateOfPay) = '" + dateOfPay.Day + "'";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 cmd.ExecuteNonQuery();
                 con.Close();
                 con.Dispose();
             }
         }
     }
     catch (SqlException ex)
     {
     }
     return(result);
 }
コード例 #25
0
ファイル: feedBackDB.cs プロジェクト: hvtanh07/Gym-management
 public bool Insert(feedBack feedback)
 {
     sqlQuery = "insert into Feedback (maKH,thoigian,feedback)" +
                " values('" + feedback.maHV + "', '" + feedback.date + "', N'" + feedback.feedback + "')";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 cmd.ExecuteNonQuery();
                 con.Close();
                 return(true);
             }
         }
     }
     catch (SqlException ex)
     {
         return(false);
     }
 }
コード例 #26
0
ファイル: hoiVien.cs プロジェクト: hvtanh07/Gym-management
 public DataTable getMemberListCode()
 {
     sqlQuery = "Select Trim(Member.ma) as list" +
                " From  Member ";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 SqlDataAdapter da = new SqlDataAdapter(cmd);
                 // this will query your database and return the result to your datatable
                 da.Fill(result);
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
     return(result);
 }
コード例 #27
0
ファイル: hoiVien.cs プロジェクト: hvtanh07/Gym-management
 public DataTable getMemberDetailInfo(string ma)
 {
     sqlQuery = "Select ma, ten from Member " +
                " where ma = '" + ma + "' ";
     conString.ConString constring = new conString.ConString();    //this will hide the database info ... sort of
     try
     {
         using (var con = new SqlConnection(constring.initString()))
         {
             using (var cmd = new SqlCommand(sqlQuery, con))
             {
                 con.Open();
                 SqlDataAdapter da = new SqlDataAdapter(cmd);
                 // this will query your database and return the result to your datatable
                 da.Fill(result);
                 con.Close();
             }
         }
     }
     catch (SqlException ex)
     {
     }
     return(result);
 }