C# clsDataLayer.fnExecuteDataset方法代码示例

        public bool fnCheckAgent()
        {
            try
            {
                dsUserInfo = new DataSet();
                SqlParameter spUserID = new SqlParameter();
                spUserID.ParameterName = "@Result";
                spUserID.SqlDbType = SqlDbType.VarChar;
                spUserID.Size = 20;
                spUserID.Direction = ParameterDirection.Output;

                _objDataLayer = new clsDataLayer();
                dsUserInfo = _objDataLayer.fnExecuteDataset("spCheckAgent", new SqlParameter("@EmailID", this.strEmailID),
                                                                               new SqlParameter("@Password", this.strPassword), spUserID);
                if (spUserID.Value.ToString() == "VALID USER")
                {
                    if (dsUserInfo.Tables[0].Rows.Count > 0)
                    {
                        HttpContext.Current.Session["RechargeUserID"] = dsUserInfo.Tables[0].Rows[0]["UserID"].ToString();
                        HttpContext.Current.Session["RechargeUserType"] = dsUserInfo.Tables[0].Rows[0]["UserType"].ToString();
                        HttpContext.Current.Session["RechargeEmailID"] = dsUserInfo.Tables[0].Rows[0]["EmailID"].ToString();
                        //HttpContext.Current.Session["TravelTotalComm"] = dsUserInfo.Tables[1].Rows[0]["TotalComm"].ToString();
                        //HttpContext.Current.Session["TravelCancelComm"] = dsUserInfo.Tables[1].Rows[0]["CancelTtlComm"].ToString();
                        HttpContext.Current.Session["Status"] = dsUserInfo.Tables[0].Rows[0]["Status"].ToString();

                        //if (dsUserInfo.Tables[1].Rows.Count > 0)
                        //{
                        //   HttpContext.Current.Session["APRBusCom"] = dsUserInfo.Tables[1].Rows[0][3].ToString();
                        //}

                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else if (spUserID.Value.ToString() == "INVALID USER")
                {
                    return false;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                //Logger.Log(Logger.LogType.Log_In_DB, ex, true);
                return false;
            }
            finally
            {
                _objDataLayer = null;
                dsUserInfo.Dispose();
            }
        }

        public DataSet fnGetData()
        {
            try
            {
                _objDataLayer = new clsDataLayer();
                SqlParameter[] _params = new SqlParameter[0];
                switch (this.ScreenInd)
                {
                    case Masters.Dmr:
                        return _objDataLayer.fnExecuteDataset("sp_dmr", new SqlParameter("@TableName", "Getdmr"),
                                                                                     new SqlParameter("@UserID", this.UserID),
                                                                                       new SqlParameter("@ToDate", this.To12),
                                                                                       new SqlParameter("@FromDate", this.From12));
                    #region distributors
                    case Masters.loginDetails:
                        return _objDataLayer.fnExecuteDataset("sp_LoginDetails", new SqlParameter("@TableName", "getlogindetails"),
                                                                                     new SqlParameter("@UserID", this.UserID),
                                                                                       new SqlParameter("@ToDate", this.To12),
                                                                                       new SqlParameter("@LoginTime", this.From12));
                    case Masters.GetAdminCommission:
                        return _objDataLayer.fnExecuteDataset("sp_AgentCommission", new SqlParameter("@TableName", "getAdminCommission"),
                                                                                     new SqlParameter("@OperatorsName", this.NetworkName));
                    case Masters.getdisCommission:
                        return _objDataLayer.fnExecuteDataset("sp_AgentCommission", new SqlParameter("@TableName", "getDisCommission"),
                                                                                    new SqlParameter("@NetworkName", this.NetworkName),
                                                                                    new SqlParameter("@Type", this.Type));
                    case Masters.GetDistributorsList:
                        return _objDataLayer.fnExecuteDataset("sp_GetdistributorsList", new SqlParameter("@TableName", this.Parameter),
                                                                                        new SqlParameter("@DistributorID", this.DistributorID));

                    case Masters.Distributorswisereports:
                        return _objDataLayer.fnExecuteDataset("SpDistributorReports", new SqlParameter("@UserID", this.ID),
                                                                                                              new SqlParameter("@From", this.From12),
                                                                                                                 new SqlParameter("@To", this.To12),
                                                                                                                  new SqlParameter("@Parameter", this.Parameter));

                    #endregion
                    case Masters.getimages:
                        return _objDataLayer.fnExecuteDataset("sp_getimages", _params);
                    case Masters.Getservices:
                        return _objDataLayer.fnExecuteDataset("sp_Temporaryservices", new SqlParameter("@TableName","GetServices"));
                    case Masters.GetOpeartorByMobileSeries:
                        return _objDataLayer.fnExecuteDataset("Sp_MobileNoSeries",new SqlParameter("@Prefix",this.Parameter));
                    case Masters.getticket:
                        return _objDataLayer.fnExecuteDataset("SP_GetDetails", new SqlParameter("@PGMBRefNo", this.BankReferenceNo));

                    case Masters.GetAgentAllReports:
                        return _objDataLayer.fnExecuteDataset("SpGetAgentAllReports", new SqlParameter("@From", this.From),
                                                                                  new SqlParameter("@To", this.To),
                                                                                  new SqlParameter("@UserID", this.UserID));

                    case Masters.getadds:
                        return _objDataLayer.fnExecuteDataset("sp_getadds", _params);
                    case Masters.getewalletusers:
                        return _objDataLayer.fnExecuteDataset("sp_GetEwalletUsers", _params);

                    case Masters.GetAdminReports:
                        return _objDataLayer.fnExecuteDataset("sp_GetAdminReports", new SqlParameter("@Tablename", this.Parameter),
                                                                                 new SqlParameter("@From", this.From),
                                                                                  new SqlParameter("@To", this.To),
                                                                                  new SqlParameter("@UserID", this.UserID));

                    case Masters.GetAgentOnly:
                        return _objDataLayer.fnExecuteDataset("sp_GetMobileDetailsOfAgentBySearch", new SqlParameter("@Tablename", this.Parameter),
                                                                                 new SqlParameter("@From", this.From),
                                                                                  new SqlParameter("@To", this.To),
                                                                                  new SqlParameter("@UserID", this.UserID));

                    case Masters.GetCommisionByNetwork:
                        return _objDataLayer.fnExecuteDataset("sp_AgentCommission", new SqlParameter("@TableName", "GetCommissionByNetwork"),
                                                                                     new SqlParameter("@OperatorsName",this.NetworkName),
                                                                                       new SqlParameter("@Type", this.Type));

                    case Masters.AgentCommission:
                        return _objDataLayer.fnExecuteDataset("sp_AgentCommission", new SqlParameter("@TableName", "GetCommission"),
                                                                                      new SqlParameter("@Type",this.Type),
                                                                                        new SqlParameter("@ID",this.DistributorID));

                    case Masters.GetRechargeAmount:
                        return _objDataLayer.fnExecuteDataset("sp_ListOfAmounts", new SqlParameter("@TableName", "GetRechargeAmount"),
                                                                                    new SqlParameter("@NetworkName", this.NetworkName));

                    case Masters.GetNetworkName:
                        return _objDataLayer.fnExecuteDataset("sp_ListOfAmounts", new SqlParameter("@TableName", "GetNetwork"),
                                                                                    new SqlParameter("@OperatorsType",this.OperatorType));
                    case Masters.ListOfAmounts:
                        return _objDataLayer.fnExecuteDataset("sp_ListOfAmounts", new SqlParameter("@TableName", "getListOfAmount"));

                    case Masters.getoperators:
                        return _objDataLayer.fnExecuteDataset("sp_Getoperators", _params);
                    case Masters.AgentReportBYIDDTH:
                        return _objDataLayer.fnExecuteDataset("SpGetMobileRechargeDetailsOfAgentsBYID", new SqlParameter("@From", this.From12),
                                                                                                                 new SqlParameter("@To", this.To12),
                                                                                                                  new SqlParameter("@UserID", this.ID),
                                                                                                                  new SqlParameter("@Parameter", this.Parameter));
                    case Masters.AgentReportBYIDDatacard:
                        return _objDataLayer.fnExecuteDataset("SpGetMobileRechargeDetailsOfAgentsBYID", new SqlParameter("@From", this.From12),
                                                                                                          new SqlParameter("@UserID", this.ID),
                                                                                                                 new SqlParameter("@To", this.To12),
                                                                                                                  new SqlParameter("@Parameter", this.Parameter));
//.........这里部分代码省略.........

 public DataSet GetOpeartorByMobileSeries(string Prefix)
 {
     try
     {
         _objDataLayer = new clsDataLayer();
         return _objDataLayer.fnExecuteDataset("Sp_MobileNoSeries",new SqlParameter("@Prefix",Prefix));
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

 public DataSet GetIgnoreList(int ProviderID)
 {
     try
     {
         _objDataLayer = new clsDataLayer();
         SqlParameter[] p = new SqlParameter[1];
         p[0] = new SqlParameter("@ProviderId", ProviderID);
         return _objDataLayer.fnExecuteDataset("SP_IgnoreOpr", p);
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

        public DataSet GetCitiesTours(string Consumerkey, string Consumersecretkey,int value)
        {
            try
            {
                _objDataLayer = new clsDataLayer();
                SqlParameter[] p = new SqlParameter[3];
                p[0] = new SqlParameter("@ConsumerKey", Consumerkey);
                p[1] = new SqlParameter("@ConsumerSecret", Consumersecretkey);
                p[2] = new SqlParameter("@Query", value);

                return _objDataLayer.fnExecuteDataset("SP_WebAPI_GetCities", p);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet GetAgentFlightSearch(FlightBAL objFlightBAL)
        {
            try
            {
                ObjDAL = new clsDataLayer();
                SqlParameter[] p = new SqlParameter[13];
                p[0] = new SqlParameter("@Source", objFlightBAL.Source);
                p[1] = new SqlParameter("@Destinations", objFlightBAL.Destinations);
                p[2] = new SqlParameter("@DateOfJourney", objFlightBAL.DateOfJourney);
                p[3] = new SqlParameter("@DateOfIssue", objFlightBAL.DateOfIssue);
                p[4] = new SqlParameter("@Name", objFlightBAL.Name);
                p[5] = new SqlParameter("@EmailId", objFlightBAL.EmailId);
                p[6] = new SqlParameter("@RefNo", objFlightBAL.RefNo);
                p[7] = new SqlParameter("@Operator", objFlightBAL.Operator);
                p[8] = new SqlParameter("@ContactNo", objFlightBAL.ContactNo);
                p[9] = new SqlParameter("@Status", objFlightBAL.Status);
                //p[10] = new SqlParameter("@PageSize", objFlightBAL.PageSize);
                p[10] = new SqlParameter("@TableName", objFlightBAL.TableName);
                p[11] = new SqlParameter("@FlightName", objFlightBAL.FlightName);
                p[12] = new SqlParameter("@userid", objFlightBAL.CreatedBy);
                return ObjDAL.fnExecuteDataset("SP_GetAgentReport", p);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

 public DataSet GetAgentFlights(FlightBAL objFlightBAL)
 {
     try
     {
         ObjDAL = new clsDataLayer();
         SqlParameter[] p = new SqlParameter[2];
         p[0] = new SqlParameter("@TableName", objFlightBAL.FlightName);
         p[1] = new SqlParameter("@userid", objFlightBAL.CreatedBy);
         return ObjDAL.fnExecuteDataset("SP_GetAgentReport", p);
     }
     catch (Exception ex)
     {
         throw new ArgumentException(ex.Message);
     }
 }

        public DataSet CarSearch(FlightBAL objFlightBAL)
        {
            try
            {
                ObjDAL = new clsDataLayer();
                SqlParameter[] p = new SqlParameter[6];
                p[0] = new SqlParameter("@ReferenceId ", objFlightBAL.RefNo);
                p[1] = new SqlParameter("@AgentName", objFlightBAL.Name);
                p[2] = new SqlParameter("@DateOfJourney", objFlightBAL.DateOfJourney);
                p[3] = new SqlParameter("@DateOfIssue", objFlightBAL.DateOfIssue);
                p[4] = new SqlParameter("@Query", objFlightBAL.TableName);
                p[5] = new SqlParameter("@AgentId", objFlightBAL.agentId);

                //p[10] = new SqlParameter("@PageSize", objFlightBAL.PageSize);

                //p[5] = new SqlParameter("@userid", objFlightBAL.CreatedBy);
                return ObjDAL.fnExecuteDataset("sp_CarProvisional", p);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

 public DataSet UpdateDomesticFlightBooking(FlightBAL objFlightBAL)
 {
     try
     {
         ObjDAL = new clsDataLayer();
         SqlParameter[] p = new SqlParameter[4];
         p[0] = new SqlParameter("@ReferenceNo", objFlightBAL.ReferenceNo);
         p[1] = new SqlParameter("@TransId", objFlightBAL.TransId);
         p[2] = new SqlParameter("@Status", objFlightBAL.Status);
         p[3] = new SqlParameter("@tableName", "UpdateDomFlightBooking");
         return ObjDAL.fnExecuteDataset("sp_DomesticFlightBooking", p);
     }
     catch (Exception ex)
     {
         throw new ArgumentException(ex.Message);
     }
 }

        //Get Airport Codes from Database
        public DataSet GetGuestReports(FlightBAL objFlightBAL)
        {
            try
            {

                ObjDAL = new clsDataLayer();

                SqlParameter[] p = new SqlParameter[4];

                p[0] = new SqlParameter("@DateOfJourney", objFlightBAL.DateOfJourney);

                p[1] = new SqlParameter("@RefNo", objFlightBAL.RefNo);

                p[2] = new SqlParameter("@TableName", objFlightBAL.TableName);

                p[3] = new SqlParameter("@DateOfIssue", objFlightBAL.DateOfIssue);

                return ObjDAL.fnExecuteDataset("sp_AllGuestReports", p);

            }

            catch (Exception ex)
            {

                throw ex;

            }
        }

        public DataSet fnGetData()
        {
            try
            {
                _objDataLayer = new clsDataLayer();
                SqlParameter[] _params = new SqlParameter[0];

                switch (this.ScreenInd)
                {

                    case blossom.GetCabDetails:
                        return _objDataLayer.fnExecuteDataset("SP_PassengerDetails", new SqlParameter("@Query", "SelectAll"),
                                                                               new SqlParameter("@ReferanceId", this.ReferanceId));

                    case blossom.SelectEmployee:
                        return _objDataLayer.fnExecuteDataset("SP_Employee", new SqlParameter("@Query", "Select"),
                                                                            new SqlParameter("@EmpId", this.EmpId));

                    case blossom.AllSelectRecord:
                        return _objDataLayer.fnExecuteDataset("SP_Employee", new SqlParameter("@Query", "AllSelect"));

                    case blossom.SelectHotelPolicy:
                        return _objDataLayer.fnExecuteDataset("SP_HotelPolicy", new SqlParameter("@Query", "Select"));

                    case blossom.SelectCab:
                        return _objDataLayer.fnExecuteDataset("SP_Cab", new SqlParameter("@Query", "Select"),
                                                                                new SqlParameter("@CabId", this.CarId));

                    case blossom.SelectAllCab:
                        return _objDataLayer.fnExecuteDataset("SP_Cab", new SqlParameter("@Query", "AllSelect"));

                    case blossom.SelectCityName:
                        return _objDataLayer.fnExecuteDataset("SP_City", new SqlParameter("@Query", "Select"));

                    case blossom.SelectAllCitys:
                        return _objDataLayer.fnExecuteDataset("SP_City", new SqlParameter("@Query", "SelectALL"));

                    case blossom.SelectCar:
                        return _objDataLayer.fnExecuteDataset("SP_Car", new SqlParameter("@Query", "Select"));

                    case blossom.SelectAllCars:
                        return _objDataLayer.fnExecuteDataset("SP_Car", new SqlParameter("@Query", "AllSelect"));

                    case blossom.SelectCarDetails:
                        return _objDataLayer.fnExecuteDataset("SP_CarDetailsForCity", new SqlParameter("@Query", "Select"),
                                                                         new SqlParameter("@CabId", this.CarId));

                    case blossom.SelectAllCarDetailsForCity:
                        return _objDataLayer.fnExecuteDataset("SP_CarDetailsForCity", new SqlParameter("@Query", "SelectAll"));

                    case blossom.SelectCarResult:
                        return _objDataLayer.fnExecuteDataset("SP_CarDetailsForCity", new SqlParameter("@Query", "CarResult"),
                                                                                      new SqlParameter("@CityId", this.CityId));

                    case blossom.SelectPsgDtls:
                        return _objDataLayer.fnExecuteDataset("SP_PassengerDetails", new SqlParameter("@Query", "SelectAll"));

                    case blossom.SelectGetMailDetails:
                        return _objDataLayer.fnExecuteDataset("SP_PassengerDetails", new SqlParameter("@Query", "GetMailDetails"),
                                                                                     new SqlParameter("@ReferanceId",this.ReferanceId));
                    case blossom.GetCarTicketDetais:
                        return _objDataLayer.fnExecuteDataset("sp_CarProvisional", new SqlParameter("@Query", "GetCarProvisonal"));

                    case blossom.GetAgentTicketDetaisl:
                        return _objDataLayer.fnExecuteDataset("sp_CarProvisional", new SqlParameter("@Query", "GetAgentBookings"));

                    case blossom.GetUserTicketDetaisl:
                        return _objDataLayer.fnExecuteDataset("sp_CarProvisional", new SqlParameter("@Query", "GetUserBookings"));
                    case blossom.GetGuestTicketDetail:
                        return _objDataLayer.fnExecuteDataset("sp_CarProvisional", new SqlParameter("@Query", "GetGuestBookings"));

                    case blossom.GetIndividualAgentTicketDetaisl:
                        return _objDataLayer.fnExecuteDataset("sp_CarProvisional", new SqlParameter("@Query", "Individual Agent Bookings"),
                                                                                      new SqlParameter("@Role", this.Role),
                                                                                      new SqlParameter("@AgentId", this.AgentId));

                    case blossom.GetCancellationdetails:
                        return _objDataLayer.fnExecuteDataset("sp_CarProvisional", new SqlParameter("@Query", "Cancellation"),
                                                                                      new SqlParameter("@ReferenceId", this.ReferanceId),
                                                                                      new SqlParameter("@EmailId", this.EmailId));

                    case blossom.CarDetails:
                        return _objDataLayer.fnExecuteDataset("SP_CarDetailsForCity", new SqlParameter("@Query", "CarDetails"),
                                                                                     new SqlParameter("@CarDetailsId", this.CarDetailsId));

                }
                return null;
            }

            catch (Exception ex)
            {
                //Logger.Log(Logger.LogType.Log_In_DB, ex, true);
                return null;
            }
        }

 public DataSet GetCommissionSlab(string role, string serviceName, string operatorName)
 {
     try
     {
         ObjDAL = new clsDataLayer();
         SqlParameter[] p = new SqlParameter[4];
         p[0] = new SqlParameter("@tablename", "Getdata");
         p[1] = new SqlParameter("@Role", role);
         p[2] = new SqlParameter("@ServiceName", serviceName);
         p[3] = new SqlParameter("@OperatorName", operatorName);
         return ObjDAL.fnExecuteDataset("SP_CommissionSlab", p);
     }
     catch (Exception ex)
     {
         throw new ArgumentException(ex.Message);
     }
 }

 //public DataSet GetCommisssions1(string strtablename)
 //{
 //    try
 //    {
 //        ObjDAL = new clsDataLayer();
 //        SqlParameter[] p = new SqlParameter[1];
 //        p[0] = ~ew SqlParameter("@tablename", strtablename);
 //        return ObjDAL.fnExecuteDataset("SP_CommissionSlab", p);
 //    }
 //    catch (Exception ex)
 //    {
 //        throw ex;
 //    }
 //}
 //bindu
 public DataSet GetAirlineNames(string type)
 {
     try
     {
         ObjDAL = new clsDataLayer();
         SqlParameter[] p = new SqlParameter[2];
         p[0] = new SqlParameter("@Flag", "GetAirlineNames");
         p[1] = new SqlParameter("@Type", type);
         return ObjDAL.fnExecuteDataset("sp_Agents", p);
     }
     catch (Exception ex)
     {
         throw new ArgumentException(ex.Message);
     }
 }

        public string CheckUser()
        {
            try
            {
                ObjDAL = new clsDataLayer();
                SqlParameter[] p = new SqlParameter[5];
                p[0] = new SqlParameter("@UserName", userName);
                p[1] = new SqlParameter("@Password", password);
                p[2] = new SqlParameter("@Result", SqlDbType.VarChar, 100);
                p[2].Direction = ParameterDirection.Output;
                p[3] = new SqlParameter("@Query", "CheckUser");

                p[4] = new SqlParameter("@Role", SqlDbType.VarChar, 100);
                p[4].Direction = ParameterDirection.Output;

                ObjDataset = ObjDAL.fnExecuteDataset("Sp_Users", p);
                if (ObjDataset != null)
                {
                    if (ObjDataset.Tables[0].Rows.Count > 0)
                    {
                        System.Web.HttpContext.Current.Session["UserID"] = ObjDataset.Tables[0].Rows[0]["ID"].ToString();
                        System.Web.HttpContext.Current.Session["UserName"] = ObjDataset.Tables[0].Rows[0]["UserName"].ToString();
                        System.Web.HttpContext.Current.Session["Name"] = ObjDataset.Tables[0].Rows[0]["Name"].ToString();
                        System.Web.HttpContext.Current.Session["Role"] = ObjDataset.Tables[0].Rows[0]["Role"].ToString();

                    }
                    else
                    {
                        System.Web.HttpContext.Current.Session["UserID"] = null;
                        System.Web.HttpContext.Current.Session["UserName"] = null;
                        System.Web.HttpContext.Current.Session["Name"] = null;
                    }
                }
                string role = p[4].Value.ToString();
                //if (role != null && role != "")
                //{
                //    System.Web.HttpContext.Current.Session["Role"] = role;
                //}
                //else
                //{
                //    System.Web.HttpContext.Current.Session["Role"] = null;
                //}
                return Convert.ToString(p[2].Value.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 public DataSet AllReports(string strservicename, int AgentID, DateTime fromdate, DateTime todate, string Agent, string RefNo)
 {
     try
     {
         ObjDAL = new clsDataLayer();
         SqlParameter[] p = new SqlParameter[6];
         p[0] = new SqlParameter("@AgentId", AgentID);
         p[1] = new SqlParameter("@tablename", strservicename);
         p[2] = new SqlParameter("@FromDate", fromdate);
         p[3] = new SqlParameter("@ToDate", todate);
         p[4] = new SqlParameter("@Agent", Agent);
         p[5] = new SqlParameter("@RefNo", RefNo);
         return ObjDAL.fnExecuteDataset("SP_ALLReports", p);
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }