C# DAL.executeDataSet方法代码示例

 public DataSet selectAllMemberTypes()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT MemberTypeNo, Description FROM MEMBER_TYPE WHERE isArchived=0 and hasLoan=1";
     DataSet ds = dal.executeDataSet(sql);
     return ds;
 }

 public DataSet SelectAllCapitalContribution()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT MinCapitalContributionId AS 'Minimum Capital Contribution ID', l.MemberTypeNo AS 'Member Type Id', Description AS 'Member Type', MinimumContribution AS 'Share Capital', Duration, l.Status, l.isArchived FROM MINIMUM_CAPITAL_CONTRIBUTION l INNER JOIN MEMBER_TYPE m ON l.MemberTypeNo = m.MemberTypeNo";
     DataSet ds = dal.executeDataSet(sql);
     return ds;
 }

 public DataSet selectAccountTypes()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT AccountTypeId, SavingsTypeName FROM SAVINGS_ACCOUNT_TYPE WHERE AccountTypeId NOT IN(SELECT AccountTypeId FROM MAXIMUM_WITHDRAWAL) AND isMaximumWithdrawal = 1 AND isArchived = 0";
     DataSet ds = dal.executeDataSet(sql);
     return ds;
 }

 public DataSet selectMembers()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select m.AccountNo as 'Account No.', concat(m.FirstName,' ',m.MiddleName,' ',m.LastName) as 'Name', SUM(h.ShareCapital) AS 'Balance' from Member m inner join MEMBER_TYPE t on m.MemberTypeNo = t.MemberTypeNo full join MEMBER_SHARECAPITAL h on m.AccountNo = h.accountNo where m.Status = 1 and t.hasShareCapital = 1 group by m.AccountNo, m.FirstName, m.LastName, m.MiddleName";
     DataSet ds = dal.executeDataSet(sql);
     return ds;
 }

 public DataSet selectMaximumWithdrawalAll()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT m.AccountTypeId AS 'Account Type Id', SavingsTypeName AS 'Account Type', MaximumWithdrawal AS 'Maximum Withdrawal', m.Status, isArchived, m.DateCreated AS 'Date Created', m.DateModified AS 'Last Modified' FROM MAXIMUM_WITHDRAWAL m INNER JOIN SAVINGS_ACCOUNT_TYPE s ON m.AccountTypeId = s.AccountTypeId";
     DataSet ds = dal.executeDataSet(sql);
     return ds;
 }

 public DataSet selectMember()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT AccountNo AS 'Account No', CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS 'Member Name', Description AS 'Member Type' FROM MEMBER m INNER JOIN MEMBER_TYPE mt ON m.MemberTypeNo = mt.MemberTypeNo WHERE m.Status=1";
     DataSet ds = dal.executeDataSet(sql);
     return ds;
 }

 public void setData()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS 'Name', CONCAT(AddressNo, ' ', AddressStreet, ', ', AddressBarangay, ', ', AddressCity, ', ', AddressProvince, ', ', AddressZipCode) AS 'Address', BirthDate, TelephoneNo, Gender FROM MEMBER WHERE AccountNo = @AccountNo";
     Dictionary<String, Object> parameters = new Dictionary<string, object>();
     parameters.Add("@AccountNo", this.AccountNo);
     DataSet ds = dal.executeDataSet(sql, parameters);
 }

 public DataSet selectActiveMember()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select Member.AccountNo as 'Account No.', concat(Member.FirstName,' ',Member.MiddleName,' ',Member.LastName) as 'Account Name', Member_Type.Description AS 'Member Type' from Member, Member_Type where Member.MemberTypeNo=Member_Type.MemberTypeNo and AccountNo not in (Select AccountNo from Termination)";
     String sqlct = "Select COUNT(Member.AccountNo) from Member, Member_Type where Member.MemberTypeNo=Member_Type.MemberTypeNo and AccountNo not in (Select AccountNo from Termination)";
     DataSet ds = dal.executeDataSet(sql);
     checkEmpty = Convert.ToInt32(dal.executeScalar(sqlct));
     return ds;
 }

 public DataSet searchMemberByName(String Name)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select m.AccountNo as 'Account No.', concat(m.FirstName,' ',m.MiddleName,' ',m.LastName) as 'Name', SUM(h.ShareCapital) AS 'Balance' from Member m inner join MEMBER_TYPE t on m.MemberTypeNo = t.MemberTypeNo full join MEMBER_SHARECAPITAL h on m.AccountNo = h.accountNo  where m.Status = 1 and hasShareCapital = 1 and concat(m.FirstName,' ',m.MiddleName,' ',m.LastName) like(@MemberName) group by m.AccountNo, m.FirstName, m.LastName, m.MiddleName";
     Dictionary<String, Object> parameters = new Dictionary<string, object>();
     Name = "%" + Name + "%";
     parameters.Add("@MemberName", Name);
     DataSet ds = dal.executeDataSet(sql, parameters);
     return ds;
 }

 public DataSet searchMemberName(String MemberName)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT AccountNo AS 'Account No', CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS 'Member Name', Description AS 'Member Type' FROM MEMBER m INNER JOIN MEMBER_TYPE mt ON m.MemberTypeNo = mt.MemberTypeNo WHERE m.Status=1 and LastName LIKE(@searchName) OR FirstName LIKE(@searchName) OR MiddleName LIKE(@searchName)";
     Dictionary<String, Object> parameters = new Dictionary<string, object>();
     MemberName = "%" + MemberName + "%";
     parameters.Add("@searchName", MemberName);
     DataSet ds = dal.executeDataSet(sql, parameters);
     return ds;
 }

 public DataSet searchTimeDepositCertificateNo(String searchName)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT m.AccountNo AS 'Account No', CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS 'Member Name', CertificateNo AS 'Certificate No', DepositAmount AS 'Current Balance', isHold FROM MEMBER m INNER JOIN TIME_DEPOSIT td ON m.AccountNo = td.AccountNo WHERE td.Status = 1 AND CertificateNo LIKE(@searchName)";
     searchName = "%" + searchName + "%";
     Dictionary<String, Object> parameters = new Dictionary<string, object>();
     parameters.Add("@searchName", searchName);
     DataSet ds = dal.executeDataSet(sql, parameters);
     return ds;
 }

 public DataSet searchMemberByName(String Name)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select Member.AccountNo as 'Account No.', concat(Member.FirstName,' ',Member.MiddleName,' ',Member.LastName) as 'Account Name', Member_Type.Description AS 'Member Type' from Member, Member_Type where Member.MemberTypeNo=Member_Type.MemberTypeNo and AccountNo not in (Select AccountNo from Termination) and concat(Member.FirstName,' ',Member.MiddleName,' ',Member.LastName) like(@MemberName)";
     Dictionary<String, Object> parameters = new Dictionary<string, object>();
     Name = "%" + Name + "%";
     parameters.Add("@MemberName", Name);
     DataSet ds = dal.executeDataSet(sql, parameters);
     return ds;
 }

 public DataSet searchMember(String searchName)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT DISTINCT sa.SavingsAccountNo AS 'Account Holder', SavingsTypeName AS 'Savings Type', sa.AccountTypeId, Balance FROM SAVINGS_ACCOUNT sa INNER JOIN MEMBER_SAVINGS_ACCOUNT msa ON sa.SavingsAccountNo = msa.SavingsAccountNo INNER JOIN SAVINGS_ACCOUNT_TYPE sat ON sa.AccountTypeId = sat.AccountTypeId WHERE sa.SavingsAccountNo LIKE(@searchName)";
     searchName = "%" + searchName + "%";
     Dictionary<String, Object> parameters = new Dictionary<string, object>();
     parameters.Add("@searchName", searchName);
     DataSet ds = dal.executeDataSet(sql, parameters);
     return ds;
 }

 public DataSet selectFees()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
      String sql = "SELECT FeeId AS 'Fee No', Description, Amount, Status, isArchived FROM fee WHERE isArchived = 0";
      DataSet ds = dal.executeDataSet(sql);
      return ds;
 }

 public DataSet selectMemberTypes()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
      String sql = "SELECT MemberTypeNo AS 'Type No', Description AS 'Type Name' FROM Member_Type WHERE isArchived = 0";
      DataSet ds = dal.executeDataSet(sql);
      return ds;
 }