C# SqlDataAccess.getDataTable方法代码示例

        public static Annotation GetAnnotationDetail(Guid annotationId, SqlDataAccess sda)
        {
            Annotation returnValue = new Annotation();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                    A.AnnotationId
                                    ,A.MimeType
                                    ,A.FileName
                                    ,A.DocumentBody
                                FROM
                                    Annotation A
                                WHERE
                                    A.AnnotationId = '{0}'";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, annotationId));
                if (dt != null && dt.Rows.Count > 0)
                {
                    returnValue.AnnotationId = (Guid)dt.Rows[0]["AnnotationId"];
                    returnValue.MimeType = dt.Rows[0]["MimeType"] != DBNull.Value ? dt.Rows[0]["MimeType"].ToString() : string.Empty;
                    returnValue.FileName = dt.Rows[0]["FileName"] != DBNull.Value ? dt.Rows[0]["FileName"].ToString() : string.Empty;
                    returnValue.File = dt.Rows[0]["DocumentBody"] != DBNull.Value ? dt.Rows[0]["DocumentBody"].ToString() : string.Empty;
                }
            }
            catch (Exception)
            {

            }
            return returnValue;
        }

        public static MsCrmResultObject GetPointCodeInfo(Guid pointCodeId, SqlDataAccess sda)
        {
            MsCrmResultObject returnValue = new MsCrmResultObject();

            try
            {
                #region | SQL QUERY |

                string sqlQuery = @"SELECT
                                        pc.new_pointcodeId AS Id
                                        ,pc.new_name AS Name
                                        ,pc.new_portalid AS PortalId
                                        ,pc.new_portalidName AS PortalIdName
                                        ,'new_portal' AS PortalIdTypeName
                                        ,pc.new_group AS GroupCode
                                        ,pc.new_code AS Code
                                        ,pc.new_point AS Point
                                        ,pc.statuscode AS Status
                                    FROM
                                    new_pointcode AS pc (NOLOCK)
                                    WHERE
                                    pc.new_pointcodeId='{0}'";

                #endregion

                DataTable dt = sda.getDataTable(string.Format(sqlQuery, pointCodeId));

                if (dt.Rows.Count > 0)
                {
                    List<PointCode> pCode = dt.ToList<PointCode>();

                    returnValue.Success = true;
                    returnValue.ReturnObject = pCode[0];
                }
                else
                {
                    returnValue.Result = "Kayıt bulunamadı.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
            }

            return returnValue;
        }

        public static MsCrmResultObj<List<Town>> GetAllTowns(SqlDataAccess sda)
        {
            MsCrmResultObj<List<Town>> returnValue = new MsCrmResultObj<List<Town>>();

            try
            {
                #region | SQL QUERY |

                string sqlQuery = @"SELECT
                                        t.new_townId AS Id
                                        ,t.new_name AS Name
                                        ,t.new_towncode AS Code
                                        ,t.new_cityId AS CityId
                                        ,t.new_cityIdName AS CityIdName
                                        ,'new_city' AS CityIdTypeName
                                    FROM
                                    new_town AS t (NOLOCK)
                                        JOIN
                                            new_city AS c (NOLOCK)
                                                ON
                                                t.new_cityId=c.new_cityId";

                #endregion

                DataTable dt = sda.getDataTable(string.Format(sqlQuery));

                if (dt.Rows.Count > 0)
                {
                    List<Town> townList = dt.ToList<Town>();

                    returnValue.Success = true;
                    returnValue.ReturnObject = townList;
                }
                else
                {
                    returnValue.Result = "Kayıt bulunamadı.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
                returnValue.HasException = true;
            }

            return returnValue;
        }

        public static MsCrmResultObject GetArticleCategoryInfo(Guid categoryId, SqlDataAccess sda)
        {
            MsCrmResultObject returnValue = new MsCrmResultObject();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT DISTINCT
                                    E.new_articlecategoryId AS Id
                                    ,E.new_name Name
                                    ,E.new_portalId PortalId
                                    ,E.new_portalIdName PortalIdName
                                    ,E.new_imageurl AS ImageUrl
                                FROM
                                new_articlecategory AS E (NOLOCK)
                                WHERE
                                    E.new_articlecategoryId = '{0}'
                                    AND
                                    E.statuscode=1 --Active";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, categoryId));
                if (dt != null && dt.Rows.Count > 0)
                {
                    EntityReference er = new EntityReference()
                    {
                        Id = (Guid)dt.Rows[0]["Id"],
                        Name = dt.Rows[0]["Name"].ToString(),
                        LogicalName = dt.Rows[0]["ImageUrl"] != DBNull.Value ? dt.Rows[0]["ImageUrl"].ToString() : "no_image_available.png"
                    };

                    returnValue.Success = true;
                    returnValue.ReturnObject = er;
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "M024";
                }
            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static MsCrmResult GetPortalId(string url, SqlDataAccess sda)
        {
            MsCrmResult returnValue = new MsCrmResult();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                    u.new_portalId AS BrandId
                                FROM
                                    new_portalurl AS u (NOLOCK)
                                        JOIN
                                            new_portal AS b (NOLOCK)
                                                ON
                                                b.new_portalId=u.new_portalId
                                                AND
                                                b.statecode=0
                                                AND
                                                b.statuscode=1 --Active
                                WHERE
                                    u.new_name='{0}'
                                AND
                                    u.statecode=0";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, url));
                if (dt != null && dt.Rows.Count > 0)
                {
                    returnValue.CrmId = (Guid)dt.Rows[0]["BrandId"];
                    returnValue.Success = true;
                }
                else
                {
                    returnValue.Result = "M004"; //"Girmiş olduğunuz adres herhangi bir portala ait değil.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static MsCrmResult CheckIsUserYourFriend(Guid portalId, Guid portalUserId, Guid selectedUserId, SqlDataAccess sda)
        {
            MsCrmResult returnValue = new MsCrmResult();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                    f.new_friendshipId AS Id
                                FROM
                                    new_friendship AS f (NOLOCK)
                                WHERE
                                    f.new_portalId='{0}'
                                AND
                                    f.statecode=0
                                AND
                                (
                                    (f.new_partyoneId='{1}' AND f.new_partytwoId='{2}')
                                OR
                                    (f.new_partyoneId='{2}' AND f.new_partytwoId='{1}')
                                )";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, portalId, portalUserId, selectedUserId));

                if (dt != null && dt.Rows.Count > 0)
                {
                    returnValue.Success = true;
                    returnValue.CrmId = (Guid)dt.Rows[0]["Id"];
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "M039"; //"Kullanıcı arkadaşınız değildir!";
                }
            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static MsCrmResult LoginControl(Guid portalId, string userName, string password, SqlDataAccess sda)
        {
            MsCrmResult returnValue = new MsCrmResult();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                    U.new_userId UserId
                                FROM
                                    new_user U (NoLock)
                                WHERE
                                    U.new_name = '{0}'
                                    AND
                                    U.new_password = '{1}'
                                    AND
                                    U.statecode = 0
                                    AND
                                    U.statuscode = {2}";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, userName, password, (int)PortalUserStatus.Active));
                if (dt != null && dt.Rows.Count > 0)
                {
                    returnValue.CrmId = (Guid)dt.Rows[0]["UserId"];

                    MsCrmResultObject roleResult = PortalUserHelper.GetPortalUserRoles(portalId, returnValue.CrmId, sda);
                    returnValue.Success = roleResult.Success;
                    returnValue.Result = roleResult.Result;
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "M035"; //"Hatalı kullanıcı adı veya şifre!";
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return returnValue;
        }

        public static MsCrmResult GetPageContent(Guid portalId, PageNames pageName, SqlDataAccess sda)
        {
            MsCrmResult returnValue = new MsCrmResult();
            try
            {
                #region | SQL QUERY |

                string sqlQuery = @"SELECT
                                        pc.new_content AS Content
                                    FROM
                                        new_pagecontent AS pc (NOLOCK)
                                    WHERE
                                        pc.new_portalId='{0}'
                                    AND
                                        pc.new_page={1}
                                    AND
                                        pc.statecode=0";

                #endregion

                DataTable dt = sda.getDataTable(string.Format(sqlQuery, portalId, ((int)pageName).ToString()));

                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["Content"] != DBNull.Value)
                    {
                        returnValue.Success = true;
                        returnValue.Result = dt.Rows[0]["Content"].ToString();
                    }
                }
                else
                {
                    returnValue.Result = "M051"; //"Sayfa içeriği hazırlanmamıştır.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static MsCrmResultObj<List<City>> GetCities(SqlDataAccess sda)
        {
            MsCrmResultObj<List<City>> returnValue = new MsCrmResultObj<List<City>>();

            try
            {
                #region | SQL QUERY |

                string sqlQuery = @"SELECT
                                        c.new_cityId AS Id
                                        ,c.new_name AS Name
                                        ,c.new_citycode AS Code
                                    FROM
                                    new_city AS c (NOLOCK)
                                    WHERE
                                    c.new_countryId='F55A5B6D-B32E-E511-80C4-000D3A216510'";

                #endregion

                DataTable dt = sda.getDataTable(sqlQuery);

                if (dt.Rows.Count > 0)
                {
                    List<City> cityList = dt.ToList<City>();

                    returnValue.Success = true;
                    returnValue.ReturnObject = cityList;
                }
                else
                {
                    returnValue.Result = "Kayıt bulunamadı.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
            }

            return returnValue;
        }

        public static MsCrmResultObject GetQuestionLevels(Guid portalId, SqlDataAccess sda)
        {
            MsCrmResultObject returnValue = new MsCrmResultObject();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                        ql.new_questionlevelId AS Id
                                        ,ql.new_name AS Name
                                        ,ql.new_portalId AS PortalId
                                        ,ql.new_portalIdName AS PortalIdName
                                        ,ql.new_imageurl AS ImageUrl
                                    FROM
                                        new_questionlevel AS ql (NOLOCK)
                                    WHERE
                                        ql.new_portalId='{0}'
                                    AND
                                        ql.statecode=0
                                    AND
                                        ql.statuscode=1 --Active";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, portalId));

                if (dt != null && dt.Rows.Count > 0)
                {
                    List<QuestionLevel> returnList = new List<QuestionLevel>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        QuestionLevel _questionLevel = new QuestionLevel();
                        _questionLevel.Id = (Guid)dt.Rows[i]["Id"];
                        _questionLevel.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty;
                        _questionLevel.ImagePath = dt.Rows[i]["ImageUrl"] != DBNull.Value ? dt.Rows[i]["ImageUrl"].ToString() : "no_image_available.png";

                        if (dt.Rows[i]["PortalId"] != DBNull.Value)
                        {
                            EntityReference er = new EntityReference()
                            {
                                Id = (Guid)dt.Rows[i]["PortalId"],
                                Name = dt.Rows[i]["PortalIdName"].ToString()
                            };

                            _questionLevel.Portal = er;
                        }

                        returnList.Add(_questionLevel);
                    }

                    returnValue.Success = true;
                    returnValue.ReturnObject = returnList;
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "M027"; //"Herhangi bir soru seviyesi tanımı bulunamadı.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static MsCrmResultObject GetQuestionInfo(Guid questionId, SqlDataAccess sda)
        {
            MsCrmResultObject returnValue = new MsCrmResultObject();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                    q.new_questionId AS Id
                                    ,q.new_name AS Name
                                    ,q.new_portalId AS PortalId
                                    ,q.new_portalIdName AS PortalIdName
                                    ,q.new_questionlevelId AS LevelId
                                    ,q.new_questionlevelIdName AS LevelIdName
                                    --,q.new_portal_rubic_cube_definitionid AS DefinationId
                                    --,q.new_portal_rubic_cube_definitionidName AS DefinationIdName
                                    ,q.new_category AS QuestionCategoryValue
                                    ,NULL AS QuestionCategoryName
                                    ,q.new_timecount AS [Time]
                                    ,q.new_point AS [Point]
                                FROM
                                    new_question AS q (NOLOCK)
                                WHERE
                                    q.new_questionId='{0}'
                                AND
                                    q.statecode=0
                                AND
                                    q.statuscode=1 --Active";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, questionId));

                if (dt != null && dt.Rows.Count > 0)
                {
                    Question _question = new Question();
                    _question.Id = (Guid)dt.Rows[0]["Id"];
                    _question.Name = dt.Rows[0]["Name"] != DBNull.Value ? dt.Rows[0]["Name"].ToString() : string.Empty;

                    if (dt.Rows[0]["PortalId"] != DBNull.Value)
                    {
                        EntityReference er = new EntityReference()
                        {
                            Id = (Guid)dt.Rows[0]["PortalId"],
                            Name = dt.Rows[0]["PortalIdName"].ToString()
                        };

                        _question.Portal = er;
                    }

                    if (dt.Rows[0]["LevelId"] != DBNull.Value)
                    {
                        EntityReference er = new EntityReference()
                        {
                            Id = (Guid)dt.Rows[0]["LevelId"],
                            Name = dt.Rows[0]["LevelIdName"].ToString()
                        };

                        _question.QuestionLevel = er;
                    }

                    //if (dt.Rows[0]["DefinationId"] != DBNull.Value)
                    //{
                    //    EntityReference er = new EntityReference()
                    //    {
                    //        Id = (Guid)dt.Rows[0]["DefinationId"],
                    //        Name = dt.Rows[0]["DefinationIdName"].ToString()
                    //    };

                    //    _question.QuestionDefination = er;
                    //}

                    if (dt.Rows[0]["QuestionCategoryValue"] != DBNull.Value)
                    {
                        StringMap sm = new StringMap()
                        {
                            Value = (int)dt.Rows[0]["QuestionCategoryValue"],
                            Name = dt.Rows[0]["QuestionCategoryName"] != DBNull.Value ? dt.Rows[0]["QuestionCategoryName"].ToString() : string.Empty
                        };

                        _question.QuestionCategory = sm;
                    }

                    _question.Time = dt.Rows[0]["Time"] != DBNull.Value ? (int)dt.Rows[0]["Time"] : 0;
                    _question.Point = dt.Rows[0]["Point"] != DBNull.Value ? (int)dt.Rows[0]["Point"] : 0;

                    _question.QuestionChoices = (List<QuestionChoices>)GetQuestionChoices(_question.Id, sda).ReturnObject;

                    returnValue.Success = true;
                    returnValue.ReturnObject = _question;
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "M028"; //"Soruya ait bilgi bulunamadı";
                }
            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }
//.........这里部分代码省略.........

        public static MsCrmResultObject GetCubeStatusList(Guid portalId, SqlDataAccess sda)
        {
            MsCrmResultObject returnValue = new MsCrmResultObject();

            DateTime start = GeneralHelper.GetStartDateByScorePeriod(ScorePeriod.Monthly);
            DateTime end = GeneralHelper.GetEndDateByScorePeriod(ScorePeriod.Monthly);

            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                    *
                                INTO
                                    #temp
                                FROM
                                (
                                    SELECT
                                        *
                                        ,DENSE_RANK() OVER (ORDER BY B.TotalPoint DESC) AS Ranking
                                    FROM
                                    (
                                        SELECT
                                            A.UserId
                                            ,ISNULL(SUM(A.Point),0) AS TotalPoint
                                        FROM
                                        (
                                            SELECT DISTINCT
                                                u.new_userId AS UserId
                                                ,a.new_point AS Point
                                                ,a.new_scoreId AS AnswerId
                                            FROM
                                                new_user AS u (NOLOCK)
                                                    LEFT JOIN
                                                        new_score AS a (NOLOCK)
                                                            ON
                                                            u.new_userId=a.new_userId
                                                            --AND
                                                            --a.CreatedOn BETWEEN @start AND @end
                                                            AND
                                                            a.statecode=0
                                                    JOIN
                                                        new_new_user_new_role AS urd (NOLOCK)
                                                            ON
                                                            u.new_userId=urd.new_userid
                                                    JOIN
                                                        new_role AS rd (NOLOCK)
                                                            ON
                                                            urd.new_roleid =rd.new_roleId
                                                            AND
                                                            rd.statecode=0
                                                            AND
                                                            rd.statuscode=1 --Active
                                                            AND
                                                            rd.new_portalId='{0}'
                                            WHERE
                                                u.statecode=0
                                            AND
                                                u.statuscode=1 --Active
                                        ) AS A
                                        GROUP BY
                                            A.UserId
                                    ) AS B
                                ) AS C

                                SELECT
                                    t.*
                                    ,c.FullName AS UserIdName
                                FROM
                                    #temp AS t (NOLOCK)
                                        JOIN
                                            new_user AS u (NOLOCK)
                                                ON
                                                t.UserId=u.new_userId
                                        JOIN
                                            Contact AS c (NOLOCK)
                                                ON
                                                u.new_contactId =c.ContactId
                                ORDER BY
                                    t.Ranking ASC

                                DROP TABLE #temp";
                #endregion

                SqlParameter[] parameters = {
                                            new SqlParameter("@start",start)
                                            ,new SqlParameter("@end",end)
                                        };

                DataTable dt = sda.getDataTable(string.Format(query, portalId), parameters);

                if (dt != null && dt.Rows.Count > 0)
                {
                    List<UserCubeStatus> returnList = new List<UserCubeStatus>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        UserCubeStatus _cubeStatus = new UserCubeStatus();

                        if (dt.Rows[i]["UserId"] != DBNull.Value)
                        {
//.........这里部分代码省略.........

        public static MsCrmResultObject GetQuestionChoices(Guid questionId, SqlDataAccess sda)
        {
            MsCrmResultObject returnValue = new MsCrmResultObject();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                        c.new_questionchoiceId AS Id
                                        ,c.new_name AS Name
                                        ,c.new_iscorrect AS IsCorrect
                                        ,c.new_questionId AS QuestionId
                                        ,c.new_questionIdName AS QUestionIdName
                                    FROM
                                        new_questionchoice AS c (NOLOCK)
                                    WHERE
                                        c.new_questionId='{0}'
                                    AND
                                        c.statecode=0 ORDER BY NEWID()";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, questionId));

                if (dt != null && dt.Rows.Count > 0)
                {
                    List<QuestionChoices> returnList = new List<QuestionChoices>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        QuestionChoices _questionChoices = new QuestionChoices();
                        _questionChoices.Id = (Guid)dt.Rows[i]["Id"];
                        _questionChoices.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty;

                        if (dt.Rows[i]["QuestionId"] != DBNull.Value)
                        {
                            EntityReference er = new EntityReference()
                            {
                                Id = (Guid)dt.Rows[i]["QuestionId"],
                                Name = dt.Rows[i]["QuestionIdName"].ToString()
                            };

                            _questionChoices.Question = er;
                        }

                        _questionChoices.IsCorrect = dt.Rows[i]["IsCorrect"] != DBNull.Value ? (bool)dt.Rows[i]["IsCorrect"] : false;

                        returnList.Add(_questionChoices);
                    }

                    returnValue.Success = true;
                    returnValue.ReturnObject = returnList;
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "M030"; //"Soruya ait şıklar bulunamadı.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static MsCrmResultObject GetSurveyChoices(Guid surveyId, SqlDataAccess sda)
        {
            MsCrmResultObject returnValue = new MsCrmResultObject();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                        c.new_surveychoiceId AS Id
                                        ,c.new_name AS Name
                                        ,c.new_surveyId AS SurveyId
                                        ,c.new_surveyIdName AS SurveyIdName
                                    FROM
                                        new_surveychoice AS c (NOLOCK)
                                    WHERE
                                        c.new_surveyId='{0}'
                                    AND
                                        c.statecode=0";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, surveyId));

                if (dt != null && dt.Rows.Count > 0)
                {
                    List<SurveyChoices> returnList = new List<SurveyChoices>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        SurveyChoices surveyChoices = new SurveyChoices();
                        surveyChoices.Id = (Guid)dt.Rows[i]["Id"];
                        surveyChoices.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty;

                        if (dt.Rows[i]["SurveyId"] != DBNull.Value)
                        {
                            EntityReference er = new EntityReference()
                            {
                                Id = (Guid)dt.Rows[i]["SurveyId"],
                                Name = dt.Rows[i]["SurveyIdName"].ToString()
                            };

                            surveyChoices.Survey = er;
                        }

                        returnList.Add(surveyChoices);
                    }

                    returnValue.Success = true;
                    returnValue.ReturnObject = returnList;
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "Ankete ait şıklar bulunamadı.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static MsCrmResultObject SearchContact(Guid portalId, Guid userId, string key, SqlDataAccess sda)
        {
            MsCrmResultObject returnValue = new MsCrmResultObject();

            try
            {
                #region | SQL QUERY |
                string query = @"SELECT DISTINCT
                                        u.new_userId AS UserId
                                        ,u.new_name AS UserName
                                        ,u.new_imageurl AS ImageUrl
                                        ,c.FullName
                                        ,c.JobTitle
                                        ,CASE WHEN fr.new_friendshipId IS NOT NULL THEN 1 ELSE 0 END AS IsFriend
                                    FROM
                                    new_user AS u (NOLOCK)
                                        JOIN
                                            new_new_user_new_role AS ur (NOLOCK)
                                                ON
                                                ur.new_userid=u.new_userId
                                        JOIN
                                            new_role AS r (NOLOCK)
                                                ON
                                                ur.new_roleid=r.new_roleId
                                                AND
                                                r.statecode=0
                                                AND
                                                r.statuscode=1 --Active
                                                AND
                                                r.new_portalId='{0}'
                                        JOIN
                                            Contact AS c (NOLOCK)
                                                ON
                                                u.new_contactId=c.ContactId
                                        LEFT JOIN
                                            new_friendship AS fr (NOLOCK)
                                                ON
                                                '{2}' IN (fr.new_partyOneId,fr.new_partyTwoId)
                                                AND
                                                u.new_userId IN (fr.new_partyOneId,fr.new_partyTwoId)
                                                AND
                                                fr.statecode=0
                                    WHERE
                                        u.statecode=0
                                    AND
                                        u.new_userId!='{2}'
                                    AND
                                        u.statuscode=1 --Active
                                    AND
                                        c.FullName LIKE '%{1}%'";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, portalId, key, userId));

                if (dt != null && dt.Rows.Count > 0)
                {
                    List<UserFriends> lstUser = new List<UserFriends>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        UserFriends uf = new UserFriends()
                        {
                            UserId = (Guid)dt.Rows[i]["UserId"],
                            UserName = dt.Rows[i]["UserName"].ToString(),
                            FullName = dt.Rows[i]["FullName"].ToString(),
                            ImageUrl = dt.Rows[i]["ImageUrl"] != DBNull.Value ? dt.Rows[i]["ImageUrl"].ToString() : "nouserprofile.jpg",
                            JobTitle = dt.Rows[i]["JobTitle"] != DBNull.Value ? dt.Rows[i]["JobTitle"].ToString() : "---",
                            UserType = (int)dt.Rows[i]["IsFriend"]
                        };

                        lstUser.Add(uf);
                    }

                    returnValue.Success = true;
                    returnValue.ReturnObject = lstUser;
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "M057"; //"Herhangi bir kayıt bulunamadı!";
                }

            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }

            return returnValue;
        }