C# Utility.GetList方法代码示例

    public void RadAjaxManager1_AjaxRequest(object sender, AjaxRequestEventArgs e)
    {
        Utility U = new Utility();
        string sql = "SELECT a.FullName as CustomerName,a.UpiCode, a.Phone, b.FullName,s.PositionName FROM [Customer] as a LEFT JOIN [CustomerSupervisor] as b on a.Id=b.CustomerId left join SupervisorPosition as s on b.PositionId=s.Id where a.Phone in (" + e.Argument + ")";
        string sqlSale = "Select l.FullName as CustomerName,l.UpiCode,l.Phone,l.FullName as FullName,r.RoleName as PositionName FROM [Salesmen] as l LEFT JOIN [Role] as r on l.RoleId=r.Id where l.Phone in (" + e.Argument + ")";
        DataTable dtcustomer = U.GetList(sql);
        DataTable dtSale = U.GetList(sqlSale);
        DataTable dtAll = dtcustomer.Copy();
        dtAll.Merge(dtSale);
        SchedulePhoneNumbers.DataSource = dtAll;
        SchedulePhoneNumbers.Rebind();
        SchedulePhoneNumbers.Visible = true;

        SchedulePhoneNumbers.PageSize += 10;
        SchedulePhoneNumbers.Rebind();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            int ID =int.Parse(Request.QueryString["SID"].ToString());
            var Sdetail = SRepo.GetSchedulePromotionById(ID);
            if (Sdetail != null)
            {
                lblUPI.Text = Sdetail.UpiCode;
                lbltitle.Text = Sdetail.Title;
                lblSMSContent.Text = Sdetail.SMSContent;
                ltrWebContent.Text = Sdetail.WebContent;
                lblStartDate.Text = string.Format("{0:d}", Sdetail.StartDate);
                lblEndDate.Text = string.Format("{0:d}",Sdetail.EndDate);
                lblAdmin.Text = SRepo.GetAdministratorName(Sdetail.AdministratorId);

                string phoneList = SRepo.GetSchedulePromotionById(ID).PhoneNumbers;
                phoneList = string.Format("'{0}'", phoneList);
                phoneList = phoneList.Replace(",", "','");
                string sql = "SELECT a.FullName as CustomerName,a.UpiCode, a.Phone, b.FullName,s.PositionName FROM [Customer] as a LEFT JOIN [CustomerSupervisor] as b on a.Id=b.CustomerId left join [SupervisorPosition] as s on b.PositionId=s.Id where a.Phone in (" + phoneList + ")";
                Utility utility = new Utility();
                SchedulePhoneNumbers.DataSource = utility.GetList(sql);

                Utility.SetCurrentMenu("mPromotion");
            }
       }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            Utility.SetCurrentMenu("mAdministrator");
            int Id =Convert.ToInt32(Request.QueryString["ID"]);
            string phoneList = ScheduleRepo.GetSchedulePromotionById(Id).PhoneNumbers;

            string strPhoneList = string.Empty;
            if(!string.IsNullOrEmpty(phoneList))
            {
                string[] listPhone = phoneList.Split(',');
                foreach (var s in listPhone)
                {
                    strPhoneList += string.Format("'{0}',", s);
                }
                strPhoneList = strPhoneList.TrimEnd(',');
            }

            string sql = "SELECT a.FullName as CustomerName,a.UpiCode, a.Phone, b.FullName,s.PositionName " +
                         "FROM [Customer] as a LEFT JOIN [CustomerSupervisor] as b on a.Id=b.CustomerId left join SupervisorPosition as s on " +
                         "b.PositionId=s.Id where a.Phone in (" + strPhoneList + ")";
            Utility utility = new Utility();
            SchedulePhoneNumbers.DataSource = utility.GetList(sql);
        }
    }

    private DataTable GetCustomerLog(int supervisorId)
    {
        var sql =
            "select c.*, t.TypeName as CustomerTypeName, ch.ChannelName as ChannelName,d.DistrictName as DistrictName, " +
            "l.LocalName as LocalName,s.FullName as SupervisorName,p.PositionName,s.Phone as supervisorPhone, cusLog.IsApprove ";
        sql += "from Customer c left join CustomerType t on c.CustomerTypeId=t.Id";
        sql += " left join Channel ch on c.ChannelId=ch.Id left join District d on c.DistrictId=d.Id";
        sql +=
            " left join Local l on c.LocalId=l.Id left join CustomerSupervisor s on c.Id=s.CustomerId left join SupervisorPosition p on s.PositionId=p.Id ";

        sql += supervisorId != -1
                   ? string.Format("join CustomerLog cusLog on cusLog.CustomerId=c.Id where c.Id='{0}'", supervisorId)
                   : string.Format("join CustomerLog cusLog on cusLog.CustomerId=c.Id");

        sql += " order by c.CreateDate desc";

        Utility U = new Utility();
        return U.GetList(sql);
    }

 public DataTable GetPromotionByIdList(string idList)
 {
     string sql = "select Id, Title from SchedulePromotion where id in (" + idList + ") group by Id, Title order by Id";
     Utility U = new Utility();
     return U.GetList(sql);
 }

    public string GetLocalList(int GroupId, int RegionId, int AreaId, int LocalId)
    {
        string sql1 = string.Empty;
        sql1 += "SELECT l.Id from Groups g left join region r on g.Id=r.GroupId left join area a on r.Id=a.RegionId left join local l on r.Id=l.AreaId where l.Id>0";
        if (GroupId > 0)
            sql1 += " and g.Id=" + GroupId;
        if (RegionId > 0)
            sql1 += " and r.Id=" + RegionId;
        if (AreaId > 0)
            sql1 += " and a.Id=" + AreaId;
        if (LocalId > 0)
            sql1 += " and l.Id=" + LocalId;

        Utility U = new Utility();
        DataTable dt = U.GetList(sql1);
        string result = string.Empty;
        foreach (DataRow r in dt.Rows)
        {
            result += r["Id"].ToString() + ",";
        }
        if (result == "")
            return result;
        return result.Substring(0, result.Length - 1);
    }

    public string SalesRegionList(string strGroupIdList, int salemenId)
    {
        if (!string.IsNullOrEmpty(strGroupIdList))
        {
            var u = new Utility();
            string SqlRegion = "select Id from Region where GroupId in (" + strGroupIdList + ") group by Id";
            var dt = u.GetList(SqlRegion);
            string result = string.Empty;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (!string.IsNullOrEmpty(dt.Rows[i][0].ToString()))
                    result += dt.Rows[i][0] + ",";
            }
            string sqlRegionId = GetRegionBySalemenId(salemenId);
            if (sqlRegionId != "")
                result += sqlRegionId;

            return result == "" ? result : result.Substring(0, result.Length - 1);
        }
        return string.Empty;
    }

 public string GetGroupBySalemenId(int salemenId)
 {
     var u = new Utility();
     string sqlGroup = "select GroupId from salesgroup where SalesmenId = " + salemenId + " group by GroupId";
     var dt = u.GetList(sqlGroup);
     string result = string.Empty;
     for (int i = 0; i < dt.Rows.Count; i++)
     {
         result += dt.Rows[i][0] + ",";
     }
     if (result == "")
         return result;
     else
         return result.Substring(0, result.Length - 1);
 }

    private string GetLocalBySalemenId(int salemenId)
    {
        var u = new Utility();
        string SqlRegion = "select LocalId from SalesLocal where salesmenId = " + salemenId + " group by LocalId";
        var dt = u.GetList(SqlRegion);
        string result = string.Empty;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            result += dt.Rows[i][0] + ",";
        }

        return result;
    }

    public List<int> GetLocalIdsStringByAreaId(int areaId)
    {
        var u = new Utility();
        var sql = string.Format("select Id from Local where AreaId in (select Id from Area where RegionId = {0})", areaId);
        var dt = u.GetList(sql);

        return GetListByDatatableResult(dt);
    }

    protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
    {
        if ((e.Item is GridEditableItem) && (e.Item.IsInEditMode))
        {
            GridEditableItem edititem = (GridEditableItem)e.Item;

            int roleId = 0;

            var vwSalesmen = edititem.DataItem as vwSalemen;
            if (vwSalesmen != null)
            {
                roleId = vwSalesmen.RoleId.HasValue ? vwSalesmen.RoleId.Value : 0;
            }

            Hashtable values = new Hashtable();
            edititem.ExtractValues(values);

            var RolesList = from r in DbContext.Roles select new { roleId = r.Id, RoleName = r.RoleName };
            if (RolesList.Count() > 0)
            {
                RadComboBox ddlRoles = ((RadComboBox)edititem.FindControl("ddlRoles"));
                ddlRoles.DataSource = RolesList.ToList();
                ddlRoles.DataTextField = "RoleName";
                ddlRoles.DataValueField = "roleId";
                ddlRoles.DataBind();

                RadComboBoxItem item = new RadComboBoxItem("Select a Role", "0");
                ddlRoles.Items.Insert(0, item);

                ddlRoles.SelectedValue = roleId.ToString();
            }

            if (UtilitiesHelpers.Instance.IsRepRole(roleId))
            {
                // Get group
                var group = groupRepo.GetAll();
                if (group != null && group.Count > 0)
                {
                    var ddlGroupAddNew = ((RadComboBox)edititem.FindControl("ddlGroupAddNew"));
                    if (ddlGroupAddNew != null)
                    {
                        var newGroup = new Group { Id = 0, GroupName = "Select a group" };
                        group.Insert(0, newGroup);
                        ddlGroupAddNew.DataSource = group;
                        ddlGroupAddNew.DataTextField = "GroupName";
                        ddlGroupAddNew.DataValueField = "Id";
                        ddlGroupAddNew.DataBind();

                        //
                        if (vwSalesmen != null)
                        {
                            var salesmenId = vwSalesmen.Id;

                            var sql = string.Format("Select distinct GroupId  FROM [SalesGroup] where SalesmenId={0}",
                                                    salesmenId);
                            var utils = new Utility();
                            var groupDt = utils.GetList(sql);
                            if (groupDt != null && groupDt.Rows.Count == 1)
                            {
                                var groupId = groupDt.Rows[0]["GroupId"].ToString();
                                ddlGroupAddNew.SelectedValue = groupId;

                                var region = regionRepo.GetRegionByGroupId(int.Parse(groupId));
                                var ddlRegionAddNew = ((RadComboBox)edititem.FindControl("ddlRegionAddNew"));
                                if (region != null && ddlRegionAddNew != null)
                                {
                                    ddlRegionAddNew.DataSource = region;
                                    ddlRegionAddNew.DataTextField = "RegionName";
                                    ddlRegionAddNew.DataValueField = "Id";
                                    ddlRegionAddNew.DataBind();

                                    sql = string.Format("Select distinct RegionId  FROM [Salesregion] where SalesmenId={0}",
                                                    salesmenId);
                                    var regionDt = utils.GetList(sql);
                                    if (regionDt != null && regionDt.Rows.Count > 0)
                                    {
                                        var regionId = regionDt.Rows[0]["RegionId"].ToString();
                                        ddlRegionAddNew.SelectedValue = regionId;

                                        var area = areaRepo.GetAreaByRegionId(int.Parse(regionId));
                                        var ddlAreaAddNew = ((RadComboBox)edititem.FindControl("ddlAreaAddNew"));
                                        if (area != null && ddlAreaAddNew != null)
                                        {
                                            ddlAreaAddNew.DataSource = area;
                                            ddlAreaAddNew.DataTextField = "AreaName";
                                            ddlAreaAddNew.DataValueField = "Id";
                                            ddlAreaAddNew.DataBind();

                                            sql = string.Format("Select distinct AreaId  FROM [SalesArea] where SalesmenId={0}",
                                                            salesmenId);
                                            var areaDt = utils.GetList(sql);
                                            if (areaDt != null && areaDt.Rows.Count > 0)
                                            {
                                                var areaId = areaDt.Rows[0]["AreaId"].ToString();
                                                ddlAreaAddNew.SelectedValue = areaId;

                                                var local = localRepo.GetLocalByAreaId(int.Parse(areaId));
                                                var ddlLocalAddNew = ((RadComboBox)edititem.FindControl("ddlLocalAddNew"));
                                                if (local != null && ddlLocalAddNew != null)
                                                {
//.........这里部分代码省略.........

    public void GetFilterData()
    {
        Utility U = new Utility();
        DataTable dt = new DataTable();
        int GroupId = int.Parse(ddlGroup.SelectedValue);
        int RegionId = (ddlRegion.Items.Count > 0) ? int.Parse(ddlRegion.SelectedValue) : 0;
        int AreaId = (ddlArea.Items.Count > 0) ? int.Parse(ddlArea.SelectedValue) : 0;
        int LocalId = (ddlLocal.Items.Count > 0) ? int.Parse(ddlLocal.SelectedValue) : 0;

        string sql = string.Empty;

        var groupJoinString = string.Empty;
        var groupWhereString = string.Empty;
        if (GroupId > 0)
        {
            groupJoinString += " left join salesgroup sg on s.Id=sg.salesmenId ";
            groupWhereString = " and sg.GroupId=" + GroupId + " ";
        }

        var regionJoinString = string.Empty;
        var regionWhereString = string.Empty;
        if (RegionId > 0)
        {
            regionJoinString += " left join salesregion sr on s.Id=sr.SalesmenId ";
            regionWhereString = " and sr.RegionId=" + RegionId + " ";
        }

        var areaJoinString = string.Empty;
        var areaWhereString = string.Empty;
        if (AreaId > 0)
        {
            areaJoinString += " left join salesArea sa on s.Id=sa.salesmenId ";
            areaWhereString = " and sa.AreaId=" + AreaId + " ";
        }

        var localJoinString = string.Empty;
        var localWhereString = string.Empty;
        if (LocalId > 0)
        {
            localJoinString += " left join salesLocal sl on s.Id=sl.SalesmenId ";
            localWhereString = " and sl.LocalId=" + LocalId + " ";
        }

        sql = "select s.*,r.RoleName from salesmen s " + groupJoinString + regionJoinString + areaJoinString +
              localJoinString + " left join Role r on s.RoleId=r.Id where s.Id > 0 " + groupWhereString +
              regionWhereString + areaWhereString + localWhereString;

        if (txtFullName.Text.Trim() != "")
            sql += " and s.FullName like '%" + txtFullName.Text.Trim() + "%'";
        if (txtPhoneNumber.Text.Trim() != "")
            sql += " and s.Phone like '%" + txtPhoneNumber.Text.Trim() + "%'";


        if (txtRoleName.Text.Trim() != "")
            sql += " and r.RoleName like '%" + txtRoleName.Text.Trim() + "%'";



        dt = U.GetList(sql);
        RadGrid1.DataSource = null;
        RadGrid1.DataSource = dt;
        RadGrid1.Rebind();
    }