Updating a Record in a SQL DB from a C# Console

Why not modify your stored procedure to do the checking?

-- STORED PROCEDURE
 
alter procedure dbo.usp_InsertToWMIDATA
		@ID int,
		@WorkstationName varchar(15),
		@UserName varchar(15),
		@Domain varchar(50),
		@WkstnStatus varchar(10) = NULL,
		@MacAddress varchar(17)
AS
BEGIN
	IF EXISTS (SELECT COUNT(1) FROM Main WHERE ID = @ID)
		UPDATE Main
		SET WorkstationName = @WorkstationName,
		    UserName = @UserName,
		    Domain = @Domain,
		    WkstnStatus = @WkstnStatus,
		    MacAddress = @MacAddress
		WHERE ID = @ID
	ELSE
		INSERT INTO Main (WorkstationName, UserName, Domain, WkstnStatus, MacAddress)
		VALUES (@WorkstationName, @UserName, @Domain, @WkstnStatus, @MacAddress)
END
 
 
// C#
 
foreach (ManagementObject csObjQueried in csObjSearcher.Get())
{
    try
    {
        sqlCmd2.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = "ID" ;
        sqlCmd2.Parameters.AddWithValue("@WorkstationName", csObjQueried["Name"]);
        sqlCmd2.Parameters.AddWithValue("@UserName", csObjQueried["UserName"]);
        sqlCmd2.Parameters.AddWithValue("@Domain", csObjQueried["Domain"]);
        sqlCmd2.Parameters.AddWithValue("@WkstnStatus", csObjQueried["Status"]);
        
        sqlCmd2.CommandType = CommandType.StoredProcedure;
        sqlCmd2.ExecuteNonQuery("dbo.usp_InsertToWMIDATA");
    }
    catch (Exception ex)
    {
        sw.WriteLine("No Win32_ComputerSystem WMI Data returned for " + client.Wkstn + ".\nError is " +
                ex.Message);
        sw.Flush();
    }
}

Select allOpen in new window

After updating the code, I get this error "Failed to convert parameter value from a String to a Int32". That must have something to do with passing the ID to @ID as a parameter.
So, I shouldn't pass anything in really.......correct? and if that is correct, how do I adjust the code so that the SP also doesn't expect a Parameter.

Thinking about it again. ID should be = to something shouldn't it? So, if I pass in something to @ID, I think a call or some Select statement should be run to find the ID on the specific workstation before an update could be made.

I took your ID column to be an auto-increment field that would just add 1 every time a record is inserted. Is this correct? If not, what is the ID column for?

Correct, the ID Column is an Identity / PK Column. I understand how the Inserts are working, but, how is the actual C# Code going to hunt down the specific workstation client's Identity ID in order to know what record to update?

Other than ID, which fields are unique to a workstation? Workstation name I presume?

Correct, just the Workstation Name.

My apologies. I was having a rough time figuring out how to set rules to move emails in my email client. I think EE's mail was a victim of this. Back to the issue...


I attached some modifications. Since workstation name is unique, we can identify a record in the database. The id column is auto-generated so we don't need to pass in anything for that.

-- STORED PROCEDURE
 
alter procedure dbo.usp_InsertToWMIDATA
                @WorkstationName varchar(15),
                @UserName varchar(15),
                @Domain varchar(50),
                @WkstnStatus varchar(10) = NULL,
                @MacAddress varchar(17)
AS
BEGIN
        IF EXISTS (SELECT COUNT(1) FROM Main WHERE WorkstationName = @WorkstationName)
                UPDATE Main
                SET UserName = @UserName,
                    Domain = @Domain,
                    WkstnStatus = @WkstnStatus,
                    MacAddress = @MacAddress
                WHERE  WorkstationName = @WorkstationName
        ELSE
                INSERT INTO Main (WorkstationName, UserName, Domain, WkstnStatus, MacAddress)
                VALUES (@WorkstationName, @UserName, @Domain, @WkstnStatus, @MacAddress)
END
 
 
// C#
 
foreach (ManagementObject csObjQueried in csObjSearcher.Get())
{
    try
    {
        sqlCmd2.Parameters.AddWithValue("@WorkstationName", csObjQueried["Name"]);
        sqlCmd2.Parameters.AddWithValue("@UserName", csObjQueried["UserName"]);
        sqlCmd2.Parameters.AddWithValue("@Domain", csObjQueried["Domain"]);
        sqlCmd2.Parameters.AddWithValue("@WkstnStatus", csObjQueried["Status"]);
        
        sqlCmd2.CommandType = CommandType.StoredProcedure;
        sqlCmd2.ExecuteNonQuery("dbo.usp_InsertToWMIDATA");
    }
    catch (Exception ex)
    {
        sw.WriteLine("No Win32_ComputerSystem WMI Data returned for " + client.Wkstn + ".\nError is " +
                ex.Message);
        sw.Flush();
    }
}

Select allOpen in new window

kaufmed. I tested it and this allowed Updates to work perfectly. I'll accept your solution but I really need you to answer this other question first with respect to the Workstation being unique. I have not added the "WorkstationName" column in my table to the Table Designer/Indexes...Keys/Column Property as a "Unique" column. Should I add that column as a Unique ID? I would imagine adding it would make sense but there may also be a reason not too. Thanks!

Adding a Unique constraint to your database column ensures that if you try to add a record with a particular Workstation Name, and that name already exists in a record, then you will get an error (which you can catch/handle) when the new record is attempted to be added. There's nothing saying you do or do not have to do it this way, but I would imagine having it might be more beneficial than not. Of course, there will be a little overhead in checking the table for uniqueness when you add records, but I would perceive it as negligible compared to database integrity.

I hope that made sense :)