How to check linq query result against Sql Server Database for data already exist or not?

Question

I am using active directory for getting all the departsment and filtering distinct departments using linq query, below is my code

private static DomainController GetDomainController(string domainpath)
{
    var domainContext = new DirectoryContext(DirectoryContextType.Domain, domainpath);
    var domain = Domain.GetDomain(domainContext);
    var controller = domain.FindDomainController();
    return controller;
}

private static MyMethod()
{
    var domainController = GetDomainController(ActiveDirectorySettings.DomainPath);

    // Lookup the information in AD
    var ldapEntry = new DirectoryEntry(string.Format("LDAP://{0}", domainController)) { AuthenticationType = AuthenticationTypes.Secure | AuthenticationTypes.FastBind };
    DirectorySearcher ds;

    ds = new DirectorySearcher(ldapEntry)
    {
        SearchScope = SearchScope.Subtree,
        Filter = "(&" + "(objectClass=user)" + "(department=" + departmentname + "*))"
    };

    ds.PropertiesToLoad.Add("department");
    if (ds.FindAll().Count >= 1)
    {
        //DataSet du = DataReader.CheckAdUserExist();
        var results = ds.FindAll();
        var uniqueSearchResults = results.Cast<SearchResult>().Select(x => GetProperty(x,"department")).Distinct();
        addUsersList.AddRange(uniqueSearchResults.Select(departmentName => new UsersAndDepartments
        {
            UserDepartment = departmentName
        }));
    }
}

I want to check the linq query result with the database whether department already exist or not, I am not sure how to do that?

Your question is a bit unclear, you want matching records for a department in AD and SQL Server DB? How do you retrieve data from your DB? — Flexabust Bergson
– Flexabust Bergson, Commented Jun 23, 2017 at 10:09
As I am getting department name from AD correctly now I want to check each department name whether its already there in database if it's already exist then do not add it in a list (addUsersList) — Naveen Kumar
– Naveen Kumar, Commented Jun 23, 2017 at 10:22
And how do you acces your DB? if you are using SqlConnection you can do a simple foreach to go through your uniqueSearchResults collection and then add an SqlCommand to do "Select * from table where department = "+departmentName+";". Then you use an SqlDataReader to read the results. Is that what you are looking for? — Flexabust Bergson
– Flexabust Bergson, Commented Jun 23, 2017 at 11:49
yes I am using SqlConnection but exactly where to write query it's using addUsersList.AddRange to populate the uniqueSearchResults, so should I write query inside it or outside. — Naveen Kumar
– Naveen Kumar, Commented Jun 23, 2017 at 12:47

Flexabust Bergson · Accepted Answer · 2017-06-23 12:59:56Z

If what you want is to create a simple database connection using SqlConnection you just need to query your DB using the department parameter you received from your AD request.

try{
    SqlConnection connection = new SqlConnection("YourConnectionstring");
    connection.Open();
    //Your connection string can be found through your Server DB
    //Now you go through your SearchResultCollection populated by SearchResult objects
    foreach(SearchResult res in uniqueSearchResult){
       SqlCommand cmd = new SqlCommand("Select * from yourTable where department=" +res.Properties["department"][0].ToString() + "", connection);
       SqlDataReader reader = cmd.ExecuteReader(); 
       //Here you verify if there are corresponding rows in your table 
       //with the request you have executed
       if(!reader.HasRows()){
           //If you have not found corresponding rows, then you add the department to your
           //list
           addUsersList.Add(res.Properties["department"][0].ToString());
       }
    }  
    connection.close();
}catch(Exception e){
    Console.WriteLine("Exception caught : \n\n" + e.ToString();
}

This should work. There are plenty of tutorials for this, but if you are making alot of requests I do not recommend using this connection method you will just lose too much time / organization, maybe try using a persistence Framework like Entity Framework :
https://www.codeproject.com/Articles/4416/Beginners-guide-to-accessing-SQL-Server-through-C

Hope this answers your question!

Naveen Kumar · Accepted Answer · 2017-06-29 05:17:39Z

Here is my solution, I have solved it myself

private static DomainController GetDomainController(string domainpath)
{
    var domainContext = new DirectoryContext(DirectoryContextType.Domain, domainpath);
    var domain = Domain.GetDomain(domainContext);
    var controller = domain.FindDomainController();
    return controller;
}

private static MyMethod()
{
    var domainController = GetDomainController(ActiveDirectorySettings.DomainPath);

    // Lookup the information in AD
    var ldapEntry = new DirectoryEntry(string.Format("LDAP://{0}", domainController)) { AuthenticationType = AuthenticationTypes.Secure | AuthenticationTypes.FastBind };
    DirectorySearcher ds;

    ds = new DirectorySearcher(ldapEntry)
    {
        SearchScope = SearchScope.Subtree,
        Filter = "(&" + "(objectClass=user)" + "(department=" + departmentname + "*))"
    };

    ds.PropertiesToLoad.Add("department");
    if (ds.FindAll().Count >= 1)
    {
        // getting list of all departments from the database
        var departmentsList = AllDepartments();

        // getting list of all departments from active directory
        var results = ds.FindAll();

        // filtering distinct departments from the result
        var uniqueSearchResults = results.Cast<SearchResult>().Select(x => GetProperty(x,"department")).Distinct();

        // here firstly i am getting the department list from the database and checking it for null, then using linq query i am comparing the result with ad department results
        if (departmentsList != null)
        {
            addUsersList.AddRange(from sResultSet in uniqueSearchResults
            where !departmentsList.Exists(u => u.UserDepartment == sResultSet)
            select new UsersAndDepartments
            {
                UserDepartment = sResultSet
            });
        }
        else
        {
            addUsersList.AddRange(uniqueSearchResults.Select(departmentName => new UsersAndDepartments
            {
                UserDepartment = departmentName
            }));
        }
    }
}

Collectives™ on Stack Overflow

How to check linq query result against Sql Server Database for data already exist or not?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related