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



Sha1395Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.