• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Active Directory Periodic Update using Sql or application settings

hi,

with a help of tgerbert i came thru this code below,i was trying to do periodic update ,in a way of get the last successful runtime and pass it to the search filter.

I was thinking to get the last successful store procedure run and pass that string but am not sure how that works.

Any help please


static void Main(string[] args)
        {

          
            DirectorySearcher searcher = new DirectorySearcher();
            //Sql connection
            SqlConnection sqlConnection1 = new SqlConnection(Your Connection string);
            sqlConnection1.Open();
            
            DateTime previousHour = DateTime.Now.AddHours(-1);
            string ldapDateString = String.Format("{0:yyyyMMddHH}0000.0Z", previousHour.ToUniversalTime());
            string filter = String.Format("(&(objectClass=user)(modifyTimeStamp>={0}))", ldapDateString);

           
            searcher.Filter = filter;
           //Default maxpage size is 1000 in AD config
           //so we max the page size to 3000
            searcher.PageSize = 3000;

            string EmailID = string.Empty;
            string phoneNo = string.Empty;
            string AccountName = String.Empty;
            string EmployeeNo = string.Empty;

            //I will collect AD Properties values here
            foreach (SearchResult result in searcher.FindAll())
            {
                var Phone = result.GetDirectoryEntry().Properties["telephonenumber"].Value;
                if (Phone != null)
                {
                    phoneNo = Phone.ToString();
                    
                }
                
              
                var Emply = result.GetDirectoryEntry().Properties["otherpager"].Value;
                if (Emply != null)
                {
                    EmployeeNo = Emply.ToString();

                }
                else
                {
                     
                    EmployeeNo =null ;
                }
              
               
                //SQl Insert
                try
                {

                   
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("InsertADUser", sqlConnection1);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;


                    if (!string.IsNullOrEmpty(EmployeeNo))
                    {
                        cmd.Parameters.Add("@EmployeeNo", SqlDbType.Char);
                        cmd.Parameters["@EmployeeNo"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@EmployeeNo"].Value = EmployeeNo;
                    }
                    else
                    {
                        cmd.Parameters.Add("@EmployeeNo", SqlDbType.Char);
                        cmd.Parameters["@EmployeeNo"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@EmployeeNo"].Value = DBNull.Value;
                    }

                   

                    if (!string.IsNullOrEmpty(phoneNo))
                    {
                        cmd.Parameters.Add("@phoneNo", SqlDbType.Char);
                        cmd.Parameters["@phoneNo"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@phoneNo"].Value = phoneNo;
                    }
                    else
                    {
                        cmd.Parameters.Add("@phoneNo", SqlDbType.Char);
                        cmd.Parameters["@phoneNo"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@phoneNo"].Value = DBNull.Value;
                    }
                   
                    cmd.ExecuteNonQuery();

                   
                }
                catch (System.Exception se)
                {
                    Console.WriteLine(se);
                }

            } 
    
            searcher.Dispose();
            entry.Close();
            sqlConnection1.Close();

        }

Open in new window



0
Sha1395
Asked:
Sha1395
  • 4
  • 2
1 Solution
 
Todd GerbertIT ConsultantCommented:
Right-click on your project, choose Properties, go to the settings tab, create a settings file, add a setting named "LastRun", make the type System.DateTime, the scope "User", make the default value "1/1/2000".

Use Properties.Settings.Default.LastRun to get the DateTime stored in the settings file to use in your LDAP query. After successfully inserting records into the SQL database set Properties.Settings.Default.LastRun = DateTime.Now and call Properties.Settings.Default.Save() to update the DateTime stored in the settings file.

This settings file is per-user, so the LastRun would be unique for each user running the program, but since you're scheduling it with the task manager you know it's always going to be the same user running it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.DirectoryServices;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
	class Program
	{
		static void Main(string[] args)
        {

          
            DirectorySearcher searcher = new DirectorySearcher();
            //Sql connection
            SqlConnection sqlConnection1 = new SqlConnection("Your Connection string");
            sqlConnection1.Open();
            
            //DateTime previousHour = DateTime.Now.AddHours(-1);
            string ldapDateString = String.Format("{0:yyyyMMddHH}0000.0Z", Properties.Settings.Default.LastRun.ToUniversalTime());
            string filter = String.Format("(&(objectClass=user)(modifyTimeStamp>={0}))", ldapDateString);

           
            searcher.Filter = filter;
           //Default maxpage size is 1000 in AD config
           //so we max the page size to 3000
            searcher.PageSize = 3000;

            string EmailID = string.Empty;
            string phoneNo = string.Empty;
            string AccountName = String.Empty;
            string EmployeeNo = string.Empty;

            //I will collect AD Properties values here
            foreach (SearchResult result in searcher.FindAll())
            {
                var Phone = result.GetDirectoryEntry().Properties["telephonenumber"].Value;
                if (Phone != null)
                {
                    phoneNo = Phone.ToString();
                    
                }
                
              
                var Emply = result.GetDirectoryEntry().Properties["otherpager"].Value;
                if (Emply != null)
                {
                    EmployeeNo = Emply.ToString();

                }
                else
                {
                     
                    EmployeeNo =null ;
                }
              
               
                //SQl Insert
                try
                {

                   
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("InsertADUser", sqlConnection1);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;


                    if (!string.IsNullOrEmpty(EmployeeNo))
                    {
                        cmd.Parameters.Add("@EmployeeNo", SqlDbType.Char);
                        cmd.Parameters["@EmployeeNo"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@EmployeeNo"].Value = EmployeeNo;
                    }
                    else
                    {
                        cmd.Parameters.Add("@EmployeeNo", SqlDbType.Char);
                        cmd.Parameters["@EmployeeNo"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@EmployeeNo"].Value = DBNull.Value;
                    }

                   

                    if (!string.IsNullOrEmpty(phoneNo))
                    {
                        cmd.Parameters.Add("@phoneNo", SqlDbType.Char);
                        cmd.Parameters["@phoneNo"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@phoneNo"].Value = phoneNo;
                    }
                    else
                    {
                        cmd.Parameters.Add("@phoneNo", SqlDbType.Char);
                        cmd.Parameters["@phoneNo"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@phoneNo"].Value = DBNull.Value;
                    }
                   
                    cmd.ExecuteNonQuery();

                   // Everything completed successfully, update the last run time
					Properties.Settings.Default.LastRun = DateTime.Now;
					Properties.Settings.Default.Save();
                }
                catch (System.Exception se)
                {
                    Console.WriteLine(se);
                }

            } 
    
            searcher.Dispose();
            entry.Close();
            sqlConnection1.Close();

        }
	}
}

Open in new window

Untitled.png
0
 
Sha1395Author Commented:
Wow! that's cool but when i tested the ldapstring return this value ""19991231130000.0Z" this is not correct right.

if i use your old ldapstring (less than one hour) that retruns (20110330040000.0Z) this.

Look like somewhere i messed up something.
0
 
Sha1395Author Commented:
Ooops sorry tgerbert i missed some code that's the reason it gives a weird value.Now i got this value after first run

"20110330050000.0Z"

Thaannnnnnnnnnnnnnnnnnkssssssssssssssssssssssssssssss a looooooooooooooooooooooot.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Sha1395Author Commented:
The best solution
0
 
Todd GerbertIT ConsultantCommented:
No problem, glad to help - I learn a lot myself in the process of answering questions, so I'm getting something out of it too. ;)
0
 
Sha1395Author Commented:
Believe me i was thinking to sort out this problem for three days and your answers solved less than 3 sec.

Its really really mind blowing and every time i learn from you guys to approach the problem in different dimension.

Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now