CODEWITHSUNDEEP
c#sql-serversql-server-2005
GregH
GregH

Reputation: 161

Best way to manage updating multiple fields in table row for SQL Server 2005 using C#

I have an application that reads and writes to table in SQL Server 2005. I know the basics of reading and writing to a table.

If there is no Id then I create a new entry. If there is an Id I update the existing entry.

What is the best way to write the SqlCommand string based upon if it already exists or is new?

I can't see writing two methods when it would then take updating two places when it came time for changes.

Any tips or tricks would be appreciated.

Upvotes: 1

Views: 494

Answers (3)

Surjit Samra
Surjit Samra

Reputation: 4662

Here is full sample of working code you need , all you need to do is check if record already exists then update it or just insert it.

create table MyEntity(id int,val varchar(20))
Go
create proc SaveMyEntity(@id int, @val varchar(20))
as
If Exists (Select ID from MyEntity where Id = @ID)
Update MyEntity
       Set val = @val
       Where ID = @ID
else
Insert into MyEntity values(@ID,@val)

-- here is proof of insertion/updation

Go 
SaveMyEntity 1,'a'
Go
SaveMyEntity 1,'b'
Go
SaveMyEntity 2,'a'
Go
select * from myEntity

Results will be

 id val
1   b
2   a

Then you can use this stored proc from your SqlCommand in C# (recommended)

Or your can just use this SQL statement from your C# SqlCommand (not recommended and tested)

If Exists (Select ID from MyEntity where Id = @ID)
Update MyEntity
       Set val = @val
       Where ID = @ID
else
Insert into MyEntity values(@ID,@val)

Upvotes: 3

Paul Hennessey
Paul Hennessey

Reputation: 203

It sounds like you might need a MERGE statement.

Sadly, a proper MERGE statement didn't arrive until SQL Server 2008. However, it is possible to mimic it in 2005. I found this very quickly, and there are plenty more examples out there.

Upvotes: 0

YvesR
YvesR

Reputation: 6222

I think you should work with a record set in ADO. There you can open a connection, set data and save then, the recordset should then decide if it has to insert or to update.

Something like this:

Set rs = CreateObject("ADODB.Recordset")
With rs
         Set .ActiveConnection = Application.GetNewConnection
         .CursorLocation = adUseClient
         .CursorType = adOpenStatic
         .LockType = adLockOptimistic
         .Open "select * from c_test where c_testid = '" & labelID.Caption & "'"

         If .RecordCount = 0 Then
            .AddNew
            .Fields("c_testid").Value = Application.BasicFunctions.GetIDFor("c_test")
            .Fields("accountid").Value = CurrentID
            .Fields("createuser").Value = Application.BasicFunctions.CurrentUserID
            .Fields("createdate").Value = Now
         End If

         .Fields("modifyuser").Value = Application.BasicFunctions.CurrentUserID
         .Fields("modifydate").Value = Now
         .Fields("field1").Value = edit1.Text
         .Fields("field2").Value = edit2.Text
         .Update

         .Close
End With
Set rs = Nothing

Taken from here: http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=76

Upvotes: -1

Related Questions