"Like" query example in SQL Query

USE [DBName]
GO
/****** Object:  StoredProcedure [dbo].[GetECONos]    Script Date: 07/31/2018 04:24:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec GetECONos '09907'

ALTER PROCEDURE [dbo].[GetECONos] 
    @ecoNo nvarchar(50) 
   
AS
BEGIN
IF OBJECT_ID(N'TEMPDB..#tmpProjects') IS NOT NULL
BEGIN
DROP TABLE #tmpProjects
END
SELECT CASE WHEN LEN(CONVERT(nvarchar(50), PROJECT_NO)) >=5 THEN
CONVERT(nvarchar(50),PROJECT_NO) 
ELSE
RIGHT('00000' + CONVERT(nvarchar(50), PROJECT_NO), 5) END
AS PROJECT_NO,
CASE WHEN LEN(CONVERT(nvarchar(50), INCREMENT_NO)) >=3 THEN
CONVERT(nvarchar(50),INCREMENT_NO) 
ELSE
RIGHT('000' + CONVERT(nvarchar(50), INCREMENT_NO), 3) END
AS INCREMENT_NO,
CONVERT(nvarchar(50),AMENDMENT_NO) AS AMENDMENT_NO,
PROJECT_NAME As Title, PROJECTID as ProjectId
INTO
#tmpProjects
FROM dbo.Project


SELECT (CONVERT(nvarchar(50),PROJECT_NO) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO)) AS ECONo,
Title,ProjectId FROM
#tmpProjects
where (CONVERT(nvarchar(50),PROJECT_NO) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO))
like '%'+@ecoNo+'%'
END

     

SQL Function for returning comma separate values

SQL Function for returning comma separate values

USE [DBName]
GO
/****** Object:  UserDefinedFunction [dbo].[BreakStringIntoTableRows]    Script Date: 07/31/2018 04:23:12 ******/
SET ANSI_NULLS ON


GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[BreakStringIntoTableRows] (@CommadelimitedString   varchar(1000))
RETURNS   @Result TABLE (Column1   VARCHAR(100))
AS
BEGIN
        DECLARE @IntLocation INT
        WHILE (CHARINDEX(',',    @CommadelimitedString, 0) > 0)
        BEGIN
              SET @IntLocation =   CHARINDEX(',',    @CommadelimitedString, 0)     
              INSERT INTO   @Result (Column1)
              --LTRIM and RTRIM to ensure blank spaces are   removed
              SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString,   0, @IntLocation))) 
              SET @CommadelimitedString = STUFF(@CommadelimitedString,   1, @IntLocation,   '')
        END
        INSERT INTO   @Result (Column1)
        SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed
        RETURN
END

Stored Procedure for iterating[For Each row] in temp table values in SQL

USE [DBName]
GO
/****** Object:  StoredProcedure [dbo].[GetselectedEcoNoFromkeywords]    Script Date: 07/31/2018 04:21:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetselectedEcoNoFromkeywords]
@keywordsIds varchar(max)

as
Declare @Id varchar(100);
Declare @tempTb Table (id integer);

--Inserting ids values into temp table from dbo.BreakStringIntoTableRows(@val) function
-- this function (dbo.BreakStringIntoTableRows) is created for the splitting the ids values into table, which are comming as sp paramenter like '1023, 234,478'

Insert into @tempTb SELECT *  FROM  dbo.BreakStringIntoTableRows(@keywordsIds)
--select * from @tId

CREATE TABLE #ecoNo(
ECONo varchar(255),
Title varchar(max),
    ProjectId varchar(255),
   -- PROJECT_NO varchar(255),
   -- KEYWORDID varchar(255),
   -- cat_description varchar(255)
    );

While (Select Count(*) From @tempTb) >0
Begin
    Select @Id = Id From @tempTb

    -- Inserting and clubing values here in eco temp table
   
   
        INSERT INTO  #ecoNo SELECT
       
        CASE WHEN LEN(CONVERT(nvarchar(50), PROJECT_NO)) >=5 THEN
CASE WHEN LEN(CONVERT(nvarchar(50), INCREMENT_NO)) >=3 THEN
(CONVERT(nvarchar(50),PROJECT_NO) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO))
ELSE
(CONVERT(nvarchar(50),PROJECT_NO) +'-' +RIGHT('000' + CONVERT(nvarchar(50), INCREMENT_NO), 3)+CONVERT(nvarchar(50),AMENDMENT_NO))
END
        ELSE
CASE WHEN LEN(CONVERT(nvarchar(50), INCREMENT_NO)) >=3 THEN
(RIGHT('00000' + CONVERT(nvarchar(50), PROJECT_NO), 5) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO))
ELSE
(RIGHT('00000' + CONVERT(nvarchar(50), PROJECT_NO), 5) +'-' +RIGHT('000' + CONVERT(nvarchar(50), INCREMENT_NO), 3)+CONVERT(nvarchar(50),AMENDMENT_NO))
END
        END

        AS ECONo,PROJECT_NAME As Title, PROJECT.PROJECTID as ProjectId
FROM project, proj_keyword, cat_keyword
WHERE keywordid in(@id) and
PROJECT.PROJECTID = proj_keyword.projectid
AND proj_keyword.keywordid = cat_keyword.catid

    Delete @tempTb Where Id = @Id

End

select * from #ecoNo

-- exec[GetselectedEcoNoFromkeywords] '1025,1021'

Stored Procedure with cases and nested queries

Stored Procedure Sample

USE [DBName]
GO
/****** Object:  StoredProcedure [dbo].[GetTabsContentDetails]    Script Date: 07/31/2018 02:40:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Exec [GetTabsContentDetails] '4082' 

ALTER PROCEDURE [dbo].[GetTabsContentDetails] 
   @projectId nvarchar(50) 
   
AS 
  -- Titles, desc ------------
SELECT
CASE WHEN LEN(CONVERT(nvarchar(50), PROJECT_NO)) >=5 THEN
CASE WHEN LEN(CONVERT(nvarchar(50), INCREMENT_NO)) >=3 THEN
(CONVERT(nvarchar(50),PROJECT_NO) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO))
ELSE
(CONVERT(nvarchar(50),PROJECT_NO) +'-' +RIGHT('000' + CONVERT(nvarchar(50), INCREMENT_NO), 3)+CONVERT(nvarchar(50),AMENDMENT_NO))
END
        ELSE
CASE WHEN LEN(CONVERT(nvarchar(50), INCREMENT_NO)) >=3 THEN
(RIGHT('00000' + CONVERT(nvarchar(50), PROJECT_NO), 5) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO))
ELSE
(RIGHT('00000' + CONVERT(nvarchar(50), PROJECT_NO), 5) +'-' +RIGHT('000' + CONVERT(nvarchar(50), INCREMENT_NO), 3)+CONVERT(nvarchar(50),AMENDMENT_NO))
END
END
as ECONo,PROJECT_NAME As Title, DESCRIPTION as Description
FROM dbo.Project where PROJECTID= @projectId --7596
  -- End ---
 
  -- Dates Tab    ------------
-- SELECT planned_start_date, planned_end_date, actual_start_date, actual_end_date, engdate.CATDATE as Engineer_Date,
-- SIGNOFFREQUEST_DATE, (last_name+ ' ' +first_name) as Manager FROM project, employee, dbo.CAT_ENGINEER_DATES engdate
--  WHERE creatorid = empid AND project.engdateid = engdate.CATID AND projectid = @projectId
-- CALLING STORED PROCEDURE F0R ENG DATE
  EXEC GetEngDate @projectId
    --End Dates--
 
  -- option Tab  -------------
SELECT MTH.CAT_DESCRIPTION as Methodology, PR.CAT_DESCRIPTION as [Priority], CE.CAT_DESCRIPTION as [Copy Exact]
FROM PROJECT PRJ, dbo.CAT_METHODS MTH, dbo.Cat_priority PR, dbo.CAT_COPY_EXACT CE
WHERE MTH.CATID = PRJ.METHODID AND PR.CATID = PRJ.PRIORITYID AND CE.CATID=PRJ.INTELCOPYID AND PRJ.PROJECTID= @projectId

   
     --End option --
 
  -- Effectivities -----------
 
  SELECT cat_effectivity.cat_description as Effectivity, machine.machine_no as Machine, cat_machine_type.cat_description AS [Type],
  customer.companyname as Customer, customer.site as [Site], cat_country.cat_description AS Country, cat_ship_date.catdate as [Ship Date], cat_effectivity_reason.cat_description AS Reason FROM
  MAC_PROJ_EFFECTIVITY_ACT maceff, cat_effectivity, machine, cat_machine_type, customer, cat_country, cat_ship_date, cat_effectivity_reason WHERE
  maceff.macid = machine.macid AND maceff.effectivityid = cat_effectivity.catid AND machine.typeid = cat_machine_type.catid  AND machine.customerid = customer.customerid
  AND customer.countryid = cat_country.catid
  AND maceff.effreasonid = cat_effectivity_reason.catid
  AND machine.shipdateid = cat_ship_date.catid AND maceff.projectid=@projectId order by cat_ship_date.catdate desc
 
    --End Effectivities--
 
  -- Documents Tab------------
          SELECT doc_name as [Document Name], (employee.last_name+' '+ employee.first_name) AS [Owner],docaddtime as [Created Date],
          cat_doc_types.full_description as [Document Type], DOC_FILENAME as [Filename],LINEITEMS FROM proj_docs, employee, cat_doc_types
          WHERE proj_docs.employeeid = employee.empid AND proj_docs.doctype = cat_doc_types.catid AND proj_docs.projectid = @projectId
          order by doc_name desc
    --End Doc----
       
  -- SignOff Tab -------------
--SELECT (emp1.last_name+' , '+ emp1.first_name) AS Employee, eco_group.ecogroup_name As [Eco Group],   signatory As Signatory, signoff_date as [Signoff Date],
-- (emp2.last_name+' , '+ emp2.first_name) AS [Actual Signatory] FROM proj_signoff, eco_group, employee emp1, dbo.EMployee_2 emp2
-- WHERE proj_signoff.employeeid = emp1.empid AND proj_signoff.ecogroupid = eco_group.ecogroupid
-- AND emp2.EMPID=proj_signoff.ACTUAL_SIGNOFFID AND proj_signoff.projectid = @projectId order by emp1.last_name asc

-- SELECT (emp.last_name+' , '+ emp.first_name) AS Employee, eco_group.ecogroup_name As [Eco Group],   signatory As Signatory, signoff_date as [Signoff Date],
--   (emp.last_name+' , '+ emp.first_name) AS [Actual Signatory] FROM proj_signoff, eco_group, employee emp where  proj_signoff.ecogroupid = eco_group.ecogroupid
--And proj_signoff.employeeid = emp.empid AND proj_signoff.projectid = @projectId

SELECT (emp1.last_name+' , '+ ISNULL(emp1.FIRST_NAME,'')) AS Employee, eco_group.ecogroup_name As [Eco Group],   signatory As Signatory, signoff_date as [Signoff Date],
  (emp2.last_name+' , '+ ISNULL(emp2.FIRST_NAME,'')) AS [Actual Signatory] FROM proj_signoff, eco_group, employee emp1, dbo.EMployee_2 emp2
  WHERE proj_signoff.employeeid = emp1.empid AND proj_signoff.ecogroupid = eco_group.ecogroupid
  AND emp2.EMPID=proj_signoff.ACTUAL_SIGNOFFID AND proj_signoff.projectid = @projectId
      UNION ALL
SELECT (emp.last_name+' , '+ ISNULL(emp.FIRST_NAME,'')) AS Employee, eco_group.ecogroup_name As [Eco Group],   signatory As Signatory, signoff_date as [Signoff Date],
    (emp.last_name+' , '+ ISNULL(emp.FIRST_NAME,'')) AS [Actual Signatory] FROM proj_signoff, eco_group, employee emp where  proj_signoff.ecogroupid = eco_group.ecogroupid
And proj_signoff.employeeid = emp.empid AND proj_signoff.projectid = @projectId and SIGNOFF_DATE is null order by Employee asc

    --End Signoff --

   -- Costs Tab ---------------
         -- For Cost delta------
    SELECT CC.CAT_DESCRIPTION as Category , PC.ESTIMATED as Forecast,PC.ACTUAL as Actual FROM PROJ_COST_DELTA PC, dbo.CAT_COST_DELTA CC
where CC.CATID=PC.CATID and pc.PROJECTID=@projectId
         --End ----
         -- For Cost implementation------
    SELECT CC.CAT_DESCRIPTION as Category ,PC.ESTIMATED as Forecast,PC.ACTUAL as Actual FROM dbo.PROJ_COST_IMP PC,  dbo.CAT_COST_IMP CC
where CC.CATID=PC.CATID and pc.PROJECTID=@projectId
         --End ----
         --For Manhours----
    SELECT CM.CAT_DESCRIPTION as Category, PM.ESTIMATED as Forecast, PM.ACTUAL as Actual FROM PROJ_MANHOURS PM, dbo.CAT_MANHOURS CM
WHERE PM.CATID=CM.CATID AND PM.PROJECTID=@projectId
    --End cost-------
   
    -- Reason for Option table ----
         select CD.CAT_DESCRIPTION as Reason from proj_reason PR, dbo.CAT_PROJECT_REASON CD where PR.PROJREASID=CD.CATID AND projectid = @projectId
    --End--
   
   --Phase table----
  select distinct ph.CAT_DESCRIPTION as Phase, PHASE_TITLE as [Title], PLANNED_START_DATE as [Planned StartDate],
  ACTUAL_START_DATE as [Actual StartDate], PLANNED_END_DATE as [Planned EndDate],
  ACTUAL_END_DATE as [Actual EndDate] from dbo.Proj_phas p, dbo.CAT_PHASE ph
          where p.PHASEID=ph.CATID  AND PROJECTID = @projectId
   ---end-----------

Getting Client Context in SharePoint online and 2013 customization

Getting Client Context in SharePoint online and 2013 customization


 public class TestClientContext
    {
        string envName = Convert.ToString(ConfigurationManager.AppSettings["EnvName"]);
        string UserName = Convert.ToString(ConfigurationManager.AppSettings["UserName"]);
        string Password = Convert.ToString(ConfigurationManager.AppSettings["Password"]);

        public ClientContext GetClientContext(HttpContext httpContext)
        {

            var securePassword = new SecureString();
            //Convert string to secure string 
            foreach (char c in Password)
                securePassword.AppendChar(c);
                    securePassword.MakeReadOnly(); 

            string envSiteUrl = string.Empty;

            ClientContext clientContext = null;

            switch (envName)
            {
                case "DEV":
                    envSiteUrl = Convert.ToString(ConfigurationManager.AppSettings["SiteURL"]);
                    clientContext = new ClientContext(envSiteUrl);

                    clientContext.Credentials = new SharePointOnlineCredentials(UserName, securePassword);

                    break;               
                default:
                    var spContext = SharePointContextProvider.Current.GetSharePointContext(httpContext);
                    clientContext = spContext.CreateUserClientContextForSPHost();                 
                    break;
            }
            return clientContext;
        }

        public ClientContext GetClientContext()
        {
            HttpContext httpContext = HttpContext.Current;

            return GetClientContext(httpContext);
        }

        public string GetSPHostURL(HttpRequest request)
        {
            string spHostUrl = string.Empty;
            switch (envName)
            {
                case "DEV":
                    spHostUrl = string.Format("http://{0}", request.Url.Authority);
                    break;
                default:
                    spHostUrl = request.QueryString["SPHostUrl"].ToString();
                    break;
            }
            return spHostUrl;
        }

    }

Get all the user properties information from Active Directory, SharePoint online and 2013

Get all the user properties information from Active Directory, SharePoint online and 2013

Whole class is as:

Namespaces:
using System.DirectoryServices;

Implemeantaion:

 public static string GetCurrentLoggedInuser(ClientContext clientContext)
        {
            ADMethods objAdfn = new ADMethods();
            string userId = string.Empty;
            Web web = clientContext.Web;
            clientContext.Load(web);
            clientContext.Load(web.CurrentUser);
            clientContext.ExecuteQuery();
            User user = clientContext.Web.CurrentUser;
            ADMethods.ADAttributes userAttributes = objAdfn.GetEmployeeAttributes(user.Email, ADMethods.AdPrpoertyParameters.mail);
            userId = userAttributes.sAMAccountName;    // it will return as: "NSharmaX099020"
            return userId;

        }
=======================================================================

Class:
 public class ADMethods
        {
            string sLDAP_Path = System.Configuration.ConfigurationManager.AppSettings["LDAP_Path"];

            #region GetEmployeeAttributes
            /// <summary>
            /// Enum AD properties
            /// </summary>
            public enum AdPrpoertyParameters
            {
                employeeid,
                sAMAccountName,
                displayName,
                mail
            }
            /// <summary>
            ///
            /// </summary>
            public enum PropertyBagLDAPKeys
            {
                AmatLdapPath,
                TelLdapPath
            }

            private const string CONST_REGULARFULLTIME = "R";
            private const string CONST_CONTRACTUSER = "C";
            private const string CONST_SUPPLIERDIRECT = "T";
            private const string CONST_TRUSER = "TR";

            private const string CONST_AMAT = "AMAT";
            private const string CONST_TEL = "TEL";

            /// <summary>
            /// All active directory properties
            /// </summary>
            public class ADAttributes
            {
                public string employeeId { get; set; }
                public string employeeType { get; set; }
                public string mail { get; set; }
                public string sAMAccountName { get; set; }
                public string displayName { get; set; }
                public string givenName { get; set; }
                public string manager { get; set; }
                public string memberOf { get; set; }
                public string department { get; set; }
                public string departmentNumber { get; set; }
                public string division { get; set; }
                public string userAccountControl { get; set; }
                public string telephoneNumber { get; set; }
                public string postOfficeBox { get; set; }
                public string PhysicalDeliveryOfficeName { get; set; }
                public string objectClass { get; set; }
                public string uId { get; set; }
                public string domain { get; set; }
                public string Location { get; set; }
                public string Company { get; set; }
                public string FirstName { get; set; }
                public string LastName { get; set; }
                //    public string ManagerEmailAddress { get; set; }
                public string managerSamAcnt { get; set; }
                public string ManagerEmail { get; set; }
                public string ManagerLoginName { get; set; }
                public string ManagerDisplayName { get; set; }
                public string ManagerDomain { get; set; }
                public string co { get; set; }
                public string ManagerEmployeeID { get; set; }

            }

            /// <summary>
            /// To get  all Ad properties
            /// </summary>
            /// <param name="directoryEntry">Domain directory</param>
            /// <param name="Filtervalue">Ad filterValue</param>
            /// <param name="Input">Loginname\EmailId\EmpId</param>
            /// <returns></returns>
            private static ADAttributes GetDirectoryAttributes(SearchResult sresult, string Filtervalue, string Input, string DomainName)
            {


                if (sresult != null)
                {
                    ADAttributes objAd = new ADAttributes
                    {
                        employeeId = sresult.Properties["employeeid"].Count > 0 ? sresult.Properties["employeeid"][0].ToString() : string.Empty,
                        employeeType = sresult.Properties["employeeType"].Count > 0 ? sresult.Properties["employeeType"][0].ToString() : string.Empty,
                        sAMAccountName = sresult.Properties["sAMAccountName"].Count > 0 ? sresult.Properties["sAMAccountName"][0].ToString() : string.Empty,
                        department = sresult.Properties["department"].Count > 0 ? sresult.Properties["department"][0].ToString() : string.Empty,
                        departmentNumber = sresult.Properties["departmentNumber"].Count > 0 ? sresult.Properties["departmentNumber"][0].ToString() : string.Empty,
                        division = sresult.Properties["division"].Count > 0 ? sresult.Properties["division"][0].ToString() : string.Empty,
                        manager = sresult.Properties["manager"].Count > 0 ? sresult.Properties["manager"][0].ToString() : string.Empty,
                        managerSamAcnt = (sresult.Properties.Contains("manager") == true) ? Convert.ToString(sresult.Properties["manager"][0]).Split(',')[0].Split('=')[1] : string.Empty, // Splitting manger name
                        //manager = (sresult.Properties.Contains("manager") == true) ? Convert.ToString(sresult.Properties["manager"][0]).Split(',')[0].Split('=')[1] : string.Empty, // Splitting manger name
                        displayName = sresult.Properties["displayName"].Count > 0 ? sresult.Properties["displayName"][0].ToString() : string.Empty,
                        PhysicalDeliveryOfficeName = sresult.Properties["physicalDeliveryOfficeName"].Count > 0 ? sresult.Properties["physicalDeliveryOfficeName"][0].ToString() : string.Empty,
                        mail = sresult.Properties["mail"].Count > 0 ? sresult.Properties["mail"][0].ToString() : string.Empty,
                        memberOf = sresult.Properties["memberOf"].Count > 0 ? sresult.Properties["memberOf"][0].ToString() : string.Empty,
                        //memberOf = (sresult.Properties["memberOf"].Count > 0) ? Convert.ToString(sresult.Properties["memberOf"][0]).Split(',')[0].Split('=')[1] : string.Empty, //Splitting manger name
                        userAccountControl = sresult.Properties["userAccountControl"].Count > 0 ? sresult.Properties["userAccountControl"][0].ToString() : string.Empty,
                        givenName = sresult.Properties["givenName"].Count > 0 ? sresult.Properties["givenName"][0].ToString() : string.Empty,
                        objectClass = sresult.Properties["objectClass"].Count > 0 ? sresult.Properties["objectClass"][0].ToString() : string.Empty,
                        postOfficeBox = sresult.Properties["postOfficeBox"].Count > 0 ? sresult.Properties["postOfficeBox"][0].ToString() : string.Empty,
                        telephoneNumber = sresult.Properties["telephoneNumber"].Count > 0 ? sresult.Properties["telephoneNumber"][0].ToString() : string.Empty,
                        uId = sresult.Properties["uId"].Count > 0 ? sresult.Properties["uId"][0].ToString() : string.Empty,
                        Location = sresult.Properties["L"].Count > 0 ? sresult.Properties["L"][0].ToString() : string.Empty,
                        Company = sresult.Properties["Company"].Count > 0 ? sresult.Properties["Company"][0].ToString() : string.Empty,
                        FirstName = sresult.Properties["givenName"].Count > 0 ? sresult.Properties["givenName"][0].ToString() : string.Empty,
                        LastName = sresult.Properties["sn"].Count > 0 ? sresult.Properties["sn"][0].ToString() : string.Empty,
                        //   ManagerEmailAddress = sresult.Properties["ManagerEmailAddress"].Count > 0 ? sresult.Properties["ManagerEmailAddress"][0].ToString() : string.Empty,
                        co = sresult.Properties["co"].Count > 0 ? sresult.Properties["co"][0].ToString() : string.Empty,
                        domain = DomainName
                    };
                    return objAd;
                }
                else
                {
                    return null;
                }
            }
            /// <summary>
            /// To get Employee Attributes
            /// </summary>
            /// <param name="Input">Login Name/empid/mailI</param>
            /// <param name="PropertyParameter"></param>
            /// <returns></returns>
            public ADAttributes GetEmployeeAttributes(string Input, AdPrpoertyParameters PropertyParameter)
            {
                ADAttributes attributes = null;
                try
                {
                    string strLDAPPath = System.Configuration.ConfigurationManager.AppSettings["LDAP_Path"];

                    attributes = DirectoryEntryforAttributes(strLDAPPath, PropertyParameter.ToString(), Input, CONST_AMAT);
                    //if (attributes == null)
                    //{
                    //    attributes = DirectoryEntryforAttributes(strLDAPPath, PropertyParameter.ToString(), Input, CONST_TEL);
                    //}


                    ADAttributes mgrAttributes = null;
                    mgrAttributes = DirectoryEntryforAttributes(strLDAPPath, AdPrpoertyParameters.sAMAccountName.ToString(), attributes.managerSamAcnt, CONST_AMAT);
                    //if (mgrAttributes == null)
                    //{
                    //    mgrAttributes = DirectoryEntryforAttributes(strLDAPPath, AdPrpoertyParameters.sAMAccountName.ToString(), attributes.managerSamAcnt, CONST_TEL);
                    //}
                    if (mgrAttributes != null)
                    {
                        attributes.ManagerEmail = mgrAttributes.mail;
                        attributes.ManagerLoginName = mgrAttributes.sAMAccountName;
                        attributes.ManagerDisplayName = mgrAttributes.displayName;
                        attributes.ManagerDomain = mgrAttributes.domain;
                        attributes.ManagerEmployeeID = mgrAttributes.employeeId;

                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return attributes;
            }

            #endregion

            #region IsTemporaryUser
            /// <summary>
            /// This method used for Employee is Temporary user or not
            /// </summary>
            public bool IsTemporaryUser(string Input, AdPrpoertyParameters PropertyParameter)
            {
                string employeeType = string.Empty;
                employeeType = GetEmployeeType(Input, PropertyParameter);
                return employeeType == "T";
            }
            #endregion

            #region IsContractUser
            /// <summary>
            /// To check the Employee is Contract or not
            /// </summary>
            public bool IsContractUser(string Input, AdPrpoertyParameters PropertyParameter)
            {
                string employeeType = string.Empty;
                employeeType = GetEmployeeType(Input, PropertyParameter);
                return employeeType == CONST_CONTRACTUSER;

            }
            #endregion

            #region IsSuppilerDirect
            /// <summary>
            /// To Check the Employee is Supplier direct or not
            /// </summary>
            public bool IsSuppilerDirect(string Input, AdPrpoertyParameters PropertyParameter)
            {
                string employeeType = string.Empty;
                employeeType = GetEmployeeType(Input, PropertyParameter);
                return employeeType == CONST_SUPPLIERDIRECT;

            }
            #endregion

            #region IsRegularFullTime
            /// <summary>
            /// To Check the Employee isRegular FullTime or not
            /// </summary>
            /// <param name="Input">Login Name/empid/mailID</param>
            /// <param name="PropertyParameter"></param>
            /// <returns></returns>
            public bool IsRegularfullTime(string Input, AdPrpoertyParameters PropertyParameter)
            {
                string employeeType = string.Empty;
                employeeType = GetEmployeeType(Input, PropertyParameter);
                return employeeType == CONST_REGULARFULLTIME;
            }
            #endregion

            #region IsTRUser
            /// <summary>
            /// This method used for the current user is TR or not
            /// </summary>
            /// <param name="Input"></param>
            /// <param name="PropertyParameter"></param>
            /// <returns></returns>
            public bool IsTRUser(string Input, AdPrpoertyParameters PropertyParameter)
            {
                string displayName = string.Empty;
                bool isTRUser = false;
                ADAttributes objAdattributes;
                objAdattributes = GetEmployeeAttributes(Input, PropertyParameter);
                if (objAdattributes != null)
                {
                    displayName = objAdattributes.displayName;
                    if (displayName.IndexOf("--" + CONST_TRUSER) != -1)
                    {
                        isTRUser = true;
                    }
                }
                return isTRUser;

            }
            #endregion

            #region GetEmployeeType
            /// <summary>
            /// To get Employee type for used is contract or Tempray or TE used
            /// </summary>
            public string GetEmployeeType(string Input, AdPrpoertyParameters PropertyParameter)
            {
                string employeeType = string.Empty;
                ADAttributes objAdattributes;
                objAdattributes = GetEmployeeAttributes(Input, PropertyParameter);
                if (objAdattributes != null)
                {
                    employeeType = objAdattributes.employeeType;
                }

                return employeeType;
            }
            #endregion

            // ***Added by Swathi****//

            #region IsUserExist
            /// <summary>
            /// To Check this method user exist or not in active directory
            /// </summary>
            /// <param name="Input"></param>
            /// <param name="values"></param>
            /// <returns></returns>
            public bool IsUserExist(string Input, AdPrpoertyParameters PropertyParameter)
            {
                SearchResult sresult = null;
                sresult = DirectoryEntry(PropertyBagLDAPKeys.AmatLdapPath.ToString(), PropertyParameter.ToString(), Input);
                if (sresult == null)
                {
                    sresult = DirectoryEntry(PropertyBagLDAPKeys.TelLdapPath.ToString(), PropertyParameter.ToString(), Input);
                }
                return sresult != null;

            }
            #endregion

            #region AMATDirectoryEntry_ForGroup
            /// <summary>
            /// AMAT Directory Entry for Group
            /// </summary>
            /// <param name="Input"></param>
            /// <param name="values"></param>
            /// <returns></returns>
            //private static DirectorySearcher AMATDirectoryEntry_ForGroup(string GroupName)
            //{
            //    DirectorySearcher deSearch = new DirectorySearcher();
            //    DirectoryEntry de = new DirectoryEntry("LDAP://DC=amat,DC=com");
            //    deSearch.SearchRoot = de;
            //    deSearch.Filter = "(&(objectClass=group)(cn=" + GroupName + "))";

            //    return deSearch;
            //}
            //#endregion

            //#region TELTDirectoryEntry_ForGroup
            ///// <summary>
            ///// TEL Directory Entry for Group
            ///// </summary>
            ///// <param name="Input"></param>
            ///// <param name="values"></param>
            ///// <returns></returns>
            //private static DirectorySearcher TELTDirectoryEntry_ForGroup(string GroupName)
            //{
            //    DirectorySearcher deSearch = new DirectorySearcher();
            //    DirectoryEntry de = new DirectoryEntry("LDAP://DC=amat,DC=com");
            //    deSearch.SearchRoot = de;
            //    deSearch.Filter = "(&(objectClass=group)(cn=" + GroupName + "))";

            //    return deSearch;
            //}
            //#endregion

            #region GetUsersForGroup
            /// <summary>
            /// To get the people in the given AD Group from the two domans
            /// </summary>
            /// <param name="GroupName">AD group name</param>
            /// <returns>returs array list of all the users in the given AD goup</returns>
            public ArrayList GetUsersForGroup(string GroupName)
            {
                ArrayList alluserEmailIDs = new ArrayList();
                try
                {

                    alluserEmailIDs = AMATGetUsersForGroup(GroupName);

                    alluserEmailIDs = TELGetUsersForGroup(GroupName, alluserEmailIDs);
                }
                catch
                {


                }

                return alluserEmailIDs;
            }
            #endregion

            /// <summary>
            /// To get the people in the given AD Group from AMAT Domain
            /// </summary>
            /// <param name="GroupName">AD group name</param>
            /// <returns>returs array list of all the users in the given AD goup</returns>
            public ArrayList AMATGetUsersForGroup(string GroupName)
            {
                SearchResult results;
                SearchResultCollection resulll;
                ArrayList alEmailID = new ArrayList();

                DirectorySearcher deSearch = DirectoryEntry_ForGroup(PropertyBagLDAPKeys.AmatLdapPath.ToString(), GroupName);
                results = deSearch.FindOne();
                resulll = deSearch.FindAll();

                if (resulll != null)
                {
                    DirectoryEntry deGroup = new DirectoryEntry(results.Path);
                    System.DirectoryServices.PropertyCollection pcoll = deGroup.Properties;
                    int n = pcoll["member"].Count;

                    string strMail = null;
                    for (int l = 0; l < n; l++)
                    {
                        DirectoryEntry deUser = new DirectoryEntry("LDAP://amat.com/" + pcoll["member"][l].ToString());
                        strMail = GetProperty(deUser, "mail") + ",";
                        if (null != strMail && strMail.Trim() != "")
                        {
                            alEmailID.Add(strMail);
                        }
                        deUser.Close();
                    }
                    //de.Close();
                    deGroup.Close();
                }
                return alEmailID;
            }
            # endregion

            #region TELGetUsersForGroup
            /// <summary>
            /// To get the people in the given AD Group from TEL Domain
            /// </summary>
            /// <param name="GroupName">AD group name</param>
            /// <returns>returs array list of all the users in the given AD goup</returns>
            public ArrayList TELGetUsersForGroup(string GroupName, ArrayList alEmailID)
            {
                DirectorySearcher deSearch = DirectoryEntry_ForGroup(PropertyBagLDAPKeys.TelLdapPath.ToString(), GroupName);
                SearchResult results = deSearch.FindOne();
                SearchResultCollection resulll = deSearch.FindAll();

                if (resulll != null)
                {
                    DirectoryEntry deGroup = new DirectoryEntry(results.Path);
                    System.DirectoryServices.PropertyCollection pcoll = deGroup.Properties;
                    int n = pcoll["member"].Count;

                    string strMail = null;
                    for (int l = 0; l < n; l++)
                    {
                        DirectoryEntry deUser = new DirectoryEntry("LDAP://TEL.com/" + pcoll["member"][l].ToString());
                        strMail = GetProperty(deUser, "mail") + ",";
                        if (null != strMail && strMail.Trim() != "")
                        {
                            alEmailID.Add(strMail);
                        }
                        deUser.Close();
                    }
                    //de.Close();
                    deGroup.Close();
                }
                return alEmailID;
            }
            #endregion

            #region GetProperty
            /// <summary>
            /// To get the Member of propery for the user from AD
            /// </summary>
            /// <param name="oDE"></param>
            /// <param name="PropertyName"></param>
            /// <returns></returns>
            private static string GetProperty(DirectoryEntry oDE, string PropertyName)
            {
                if (oDE.Properties.Contains(PropertyName))
                {
                    return oDE.Properties[PropertyName][0].ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
            #endregion

            #region GetDomainName

            /// <summary>
            /// Directry entry for TEL Domain
            /// </summary>
            /// <param name="Input"> value on which the AD filter need to happen</param>
            /// <param name="PropertyParameter">enum value</param>
            /// <returns> returns Domain name</returns>
            public string GetDomainName(string Input, AdPrpoertyParameters PropertyParameter)
            {
                SearchResult sresult = null;
                SearchResult sresultTEL = null;

                sresult = DirectoryEntry(PropertyBagLDAPKeys.AmatLdapPath.ToString(), PropertyParameter.ToString(), Input);
                if (sresult == null)
                {
                    sresultTEL = DirectoryEntry(PropertyBagLDAPKeys.TelLdapPath.ToString(), PropertyParameter.ToString(), Input);
                }

                if (sresult != null)
                    return CONST_AMAT;
                else
                    return CONST_TEL;
            }

            #endregion

            #region GetuserMemberof_Groupinfo
            /// <summary>
            /// This method used for  Find out the given user belongs to which group in AD
            /// </summary>
            /// <param name="username">login name</param>
            /// <returns>return  to Group name</returns>
            public string GetuserMemberof_Groupinfo(string username, ArrayList lstGroups)
            {
                string memberofgroupnames = string.Empty;
                SearchResult sresult = null;
                //string[] strUserId = username.Split('\\');
                sresult = DirectoryEntry(PropertyBagLDAPKeys.AmatLdapPath.ToString(), "sAMAccountName", username);
                //if (sresult == null)
                //{
                //    sresult = DirectoryEntry(PropertyBagLDAPKeys.TelLdapPath.ToString(), "sAMAccountName", username);
                //}
                if (sresult != null && sresult.Properties["memberOf"].Count > 0)
                {
                    //adEntry = sresult.GetDirectoryEntry();
                    //memberofgroupname = sresult.Properties["memberof"][0].ToString();
                    //memberofgroupname = memberofgroupname.Substring(0, memberofgroupname.IndexOf(','));
                    //memberofgroupname = memberofgroupname.Substring(memberofgroupname.IndexOf('=') + 1);
                    if (sresult.Properties["memberOf"].Count > 0)
                    {
                        foreach (string prop in sresult.Properties["memberOf"])
                        {
                            foreach (string strGroupName in lstGroups)
                            {
                                if (prop.ToLower().Contains("cn=" + strGroupName.ToLower()))
                                {
                                    memberofgroupnames = memberofgroupnames + "," + strGroupName;
                                }
                            }
                        }
                    }
                    //strName = adEntry.Properties["givenName"].Value.ToString() + " " + adEntry.Properties["sn"].Value.ToString();
                }
                memberofgroupnames = memberofgroupnames.TrimStart(',');
                return memberofgroupnames;
            }

            #endregion

            #region UserBelongsToTheGroups
            /// <summary>
            /// This method used for  Find out the given user belongs to the given AD group
            /// </summary>
            /// <param name="username">login name</param>
            /// <param name="lstGroups">List of AD groups</param>
            /// <returns>return T/F</returns>
            public bool UserBelongsToTheGroups(string userName, ArrayList lstGroups)
            {
                SearchResult searcher = null;
                searcher = DirectoryEntry(PropertyBagLDAPKeys.AmatLdapPath.ToString(), "sAMAccountName", userName);
                if (searcher.Properties["memberOf"].Count > 0)
                {
                    foreach (string prop in searcher.Properties["memberOf"])
                    {
                        foreach (string strGroupName in lstGroups)
                        {
                            if (prop.ToLower().Contains("cn=" + strGroupName.ToLower()))
                            {
                                return true;
                            }
                        }
                    }
                }
                //else
                //{
                //    searcher = DirectoryEntry(PropertyBagLDAPKeys.TelLdapPath.ToString(), "sAMAccountName", userName);
                //    if (searcher.Properties["memberOf"].Count > 0)
                //    {
                //        foreach (string prop in searcher.Properties["memberOf"])
                //        {
                //            foreach (string strGroupName in lstGroups)
                //            {
                //                if (prop.ToLower().Contains("cn=" + strGroupName.ToLower()))
                //                {
                //                    return true;
                //                }
                //            }
                //        }
                //    }
                //}
                return false;
            }
            #endregion





            #region GetEmployeeAttributes
            /// <summary>
            /// To get Employee Attributes overloading method
            /// </summary>
            /// <param name="Input">Login Name/empid/mailI</param>
            /// <param name="PropertyParameter">type of input value(enum values) </param>
            /// <param name="currentuserlogin"></param>
            /// <returns>Returns the AD attributes for the give  user of ADAttributes class trpe</returns>
            public ADAttributes GetEmployeeAttributes(string Input, AdPrpoertyParameters PropertyParameter, string currentuserlogin)
            {
                ADAttributes attributes = null;
                string domainName = currentuserlogin.Split(new char[] { '\\' }, System.StringSplitOptions.RemoveEmptyEntries)[0];
                switch (domainName.ToUpper())
                {
                    case CONST_AMAT:
                        {
                            attributes = DirectoryEntryforAttributes(PropertyBagLDAPKeys.AmatLdapPath.ToString(), PropertyParameter.ToString(), Input, domainName.ToUpper());
                        }
                        break;
                    case CONST_TEL:
                        {
                            attributes = DirectoryEntryforAttributes(PropertyBagLDAPKeys.TelLdapPath.ToString(), PropertyParameter.ToString(), Input, domainName.ToUpper());
                        }
                        break;
                }
                return attributes;

            }
            #endregion

            #region DirectoryEntryforAttributes
            /// <summary>
            /// Method To get the user Attributes from the AD
            /// </summary>
            /// <param name="Input">Login Name/empid/mailI</param>
            /// <param name="Filtervalue">enum type for input value</param>
            /// <param name="PropertyKey">PropertyKey to get LDAp path from Property bag</param>
            /// <param name="DomainName">Domain</param>
            /// <returns></returns>
            public ADAttributes DirectoryEntryforAttributes(string PropertyKey,string Filtervalue, string Input, string DomainName)
            {
                SearchResult sresult;
                string query = string.Empty;
                string ldappath = sLDAP_Path;
                DirectoryEntry entry_Amat = new DirectoryEntry(ldappath);
                DirectorySearcher userSearch_Amat = new DirectorySearcher(entry_Amat);
                query = string.Format("(&(objectClass=user)(" + Filtervalue + "={0}))", Input);
                userSearch_Amat.Filter = query;
                //Remove this code if not required
                sresult = userSearch_Amat.FindOne();
                if (sresult == null)
                {
                    query = string.Format("(&(objectClass=contact)(" + Filtervalue + "={0}))", Input);
                    userSearch_Amat.Filter = query;
                    //samSearcher.PropertiesToLoad.Add(Filtervalue);
                    sresult = userSearch_Amat.FindOne();

                }
                ADAttributes objAd = GetDirectoryAttributes(sresult, Filtervalue, Input, DomainName);
                return objAd;

            }

            #endregion

            #region DirectoryEntry

            /// <summary>
            /// To get results form the AD
            /// </summary>
            /// <param name="Input">Login Name/empid/mailI</param>
            /// <param name="Filtervalue">enum type for input value</param>
            /// <param name="PropertyKey">PropertyKey to get LDAp path from Property bag</param>
            /// <returns></returns>
            private SearchResult DirectoryEntry(string PropertyKey, string Filtervalue, string Input)
            {
                SearchResult sresult;
                string query = string.Empty;
                string ldappath = sLDAP_Path;
                DirectoryEntry entry_Amat = new DirectoryEntry(ldappath);
                DirectorySearcher userSearch_Amat = new DirectorySearcher(entry_Amat);
                query = string.Format("(&(objectClass=user)(" + Filtervalue + "={0}))", Input);
                userSearch_Amat.Filter = query;
                //Remove this code if not required
                sresult = userSearch_Amat.FindOne();
                if (sresult == null)
                {
                    query = string.Format("(&(objectClass=contact)(" + Filtervalue + "={0}))", Input);
                    userSearch_Amat.Filter = query;
                    //Remove this code if not required
                    sresult = userSearch_Amat.FindOne();
                }

                return sresult;
            }
            #endregion

            #region DirectoryEntry_ForGroup
            /// <summary>
            /// To get people from the AD for the given group
            /// </summary>
            /// <param name="PropertyKey">PropertyKey to get LDAp path from Property bag</param>
            /// <param name="GroupName">AD group name</param>
            /// <returns></returns>
            private DirectorySearcher DirectoryEntry_ForGroup(string PropertyKey, string GroupName)
            {
                string ldappath = sLDAP_Path;
                DirectorySearcher deSearch = new DirectorySearcher();
                DirectoryEntry de = new DirectoryEntry(ldappath);
                deSearch.SearchRoot = de;
                deSearch.Filter = "(&(objectClass=group)(cn=" + GroupName + "))";

                return deSearch;
            }
            #endregion

            #region GetDomainName
            /// <summary>
            /// To get the domai name for the given input
            /// </summary>
            /// <param name="currentuserlogin">Login Name with domain</param>
            /// <returns></returns>
            public string GetDomainName(string currentuserlogin)
            {
                string domainName = currentuserlogin.Split(new char[] { '\\' }, System.StringSplitOptions.RemoveEmptyEntries)[0];
                return domainName.ToUpper();
            }
            #endregion


            public void GetGroupMembers(string strGroup)
            {
                System.Collections.Generic.List<string> groupMemebers = new System.Collections.Generic.List<string>();

                try
                {
                    DirectoryEntry ent = new DirectoryEntry("LDAP://DC=AMAT,DC=COM");// Change by your AD link

                    DirectorySearcher srch = new DirectorySearcher("(CN=" + strGroup + ")");

                    SearchResultCollection coll = srch.FindAll();

                    foreach (SearchResult rs in coll)
                    {
                        ResultPropertyCollection resultPropColl = rs.Properties;

                        foreach (Object memberColl in resultPropColl["member"])
                        {
                            DirectoryEntry gpMemberEntry = new DirectoryEntry("LDAP://" + memberColl);

                            System.DirectoryServices.PropertyCollection userProps = gpMemberEntry.Properties;


                            //getting user properties from AD

                            object obVal = userProps["displayName"].Value;
                            object obAcc = userProps["sAMAccountName"].Value;


                            //if (null != obVal)
                            //{
                            //    //groupMemebers.Add(obVal.ToString().ToLower());//display the display name
                            //    groupMemebers.Add(obAcc.ToString().ToLower());//Display the login
                            //}
                            //else groupMemebers.AddRange(GetGroupMembers(userProps["sAMAccountName"].Value.ToString()));

                        }
                    }
                }

                catch (Exception ex)
                {
                }

                // return groupMemebers;

            }




        }

Building Dynamic CAML Query with "AND" or "OR" operator and multiple parameter in SharePoint

Building Dynamic CAML Query with "AND" or "OR" operator and multiple parameter in SharePoint



 private string BuildingDynamicCAMLQuery(string[] parameters)
        {
            // string[] parameters = new string[] { "file1", "file2"};
            StringBuilder sb = new StringBuilder();

            if (parameters.Length == 0)
            {
                // for default query
                AppendingEQoperator(sb, "all");
            }

            // "OR" each parameter to the query
            for (int i = 0; i < parameters.Length; i++)
            {
                AppendingEQoperator(sb, parameters[i]);

                if (i > 0)
                {
                    sb.Insert(0, "<Or>");
                    sb.Append("</Or>");
                }
            }

            sb.Insert(0, "<View><Query><Where>");
            sb.Append("</Where></Query></View>");

            return sb.ToString();
        }

 
        private void AppendingEQoperator(StringBuilder sb, string paramValue)
        {
            // put your field's internal name in place of Category
            sb.Append("<Eq>");
            sb.Append("<FieldRef Name='FileLeafRef'/>");
            sb.AppendFormat("<Value Type='File'>" + paramValue + "</Value>");
            sb.Append("</Eq>");

        }