C#

MERGE sometable
USING (select something FROM table) ON (value = @value)
WHEN NOT MATCHED THEN INSERT VALUES (...)

Pete O'Hanlon wrote:

I would also change your insert statement to include the SELECT COUNT as part of the where clause

try
    {
        string c = System.Environment.MachineName;
        string Domain = System.Net.Dns.GetHostName();
        ManagementObjectSearcher SerialNo = new ManagementObjectSearcher("root\\CIMV2", "SELECT * FROM_ 
        Win32_SystemEnclosure");
        foreach (ManagementObject querySN in SerialNo.Get())
        {
            string SN = (querySN["SerialNumber"].ToString());
            string SysIdent = c + SN + "@" + Domain;
    
            using(SqlConnection IdentSys = new SqlConnection(some connection;connection timeout=30"));
            using(SqlCommand selectname = new SqlCommand("SELECT COUNT(1) FROM dbo.SysIdent WHERE_              
                FullName = @SysIdent", IdentSys));
            selectname.Parameters.Add("@SysIdent", SqlDbType.NVarChar, 150);
            selectname.Parameters["@SysIdent"].Value = SysIdent;
            IdentSys.Open();
            int Count = (int)(selectname.ExecuteScalar());
            if (Count == 0)
            {
                using(SqlConnection InsertSys = new SqlConnection(some other connection_;connection
                    timeout=30"));
                using(SqlCommand newname = new SqlCommand("INSERT INTO dbo.SysIdent(FullName)_
                VALUES(@FullName)", InsertSys);
                newname.Parameters.AddWithValue("@FullName", SysIdent);
                InsertSys.Open();
                newname.ExecuteNonQuery();
                InsertSys.Close();
            }
            IdentSys.Close();
            using (SqlConnection selectID = new SqlConnection(another connection;connection timeout=30");
            SqlCommand GetID = new SqlCommand("SELECT * FROM dbo.SysIdent WHERE FullName = @SysIdent", selectID);
            GetID.Parameters.Add("@SysIdent", SqlDbType.NVarChar, 150);
            GetID.Parameters["@SysIdent"].Value = SysIdent;
            selectID.Open();
            SqlDataReader GetmyID = GetID.ExecuteReader();
            while (GetmyID.Read())
            {
                int myID = (int)(GetmyID["ID"]);
                Console.WriteLine(myID);
                Console.ReadLine();
                int mySysId = myID;
                if (!EventLog.SourceExists("mylog")) EventLog.CreateEventSource("mylog", "Application");
                EventLog.WriteEntry("mylog", "ID" + mySysId + " allocated");
            }
        }
    }
    catch (Exception f)
    {
        if (!EventLog.SourceExists("mylog")) EventLog.CreateEventSource("mylog", "Application");
        EventLog.WriteEntry("mylog", "Unable to allocate SysID" + f.ToString());
    }

((IDisposable)myObject).Dispose();

INSERT INTO dbo.SysIdent(FullName)
SELECT @FullName
WHERE NOT EXISTS (Select FullName FROM dbo.SysIdent WHERE FullName = @FullName)

CCodeNewbie wrote:

On more-or-less the same issue, given that the end product will be a windows
service, what would be the best way to ensure that the ID that is given on the
first run remains permanently static. i.e, it will use the same ID when it runs
timed_event_1 every 12 hours and the same ID for timed_event_2 which runs every
3 minutes?

public class TimedEventManager
{
  private BackgroundWorker bw = new BackgroundWorker();
  public void Initialise()
  {
    bw.DoWork += CheckForId();
    bw.RunWorkerAsync(null);
    // Set up the timers/threads as you need here.
  }

  private void CheckForId(object sender, DoWorkEventArgs e)
  {
    bw.DoWork -= CheckForId();
    bw.Dispose();
    // This is where I would call the necessary data access stuff and populate
    // the Id.
  }

  public int Id { get; set; }
  // Other necessary properties and methods here.
}

private TimedEventManager timedEvent;
protected override OnStart(string[] args)
{
  timedEvent = new TimedEventManager();
  // As
  timedEvent.Initialise();
}

private static int SID;
public static int mID