How to allow database table updates from multiple programs

Since ADO.Net uses a 'disconnected' model where the data available to a single program is just a copy of what i开发者_JS百科s in the database, what is the normal way to handle multiple programs needing to update the same table in a database? The problem I see is that a program can't update unless his copy of the data is the most up-to-date. Are we supposed to read before updating and hope that the update occurs before someone else changes the data?

I would guess the most common way is through Optimistic Concurrency Control. This type of currency control works well in systems where contention for the same data is unlikely.

Optimistic Concurrency in ADO.NET (MSDN)

I normally implement Optimistic Concurrency Control by using a timestamp column in SQL Server. The timestamp column is updated with a new value each time a row is updated. When you want to update a row, you first check its timestamp to see if it matches your local copy. If it has changed, you generally tell the user that a concurrency violation has occurred and provide some type of resolution, such as allowing the user to decide which version to keep (local version or database version).

The preferred design pattern is to make use of the ExecuteNonQuery method to effect an SQL UPDATE; and to make make use of parameterized SQL whenever possible.

    Dim cnn As SqlConnection
    Dim cmd As SqlCommand
    Dim sql As String

    sql = "UPDATE Table_A SET SomeField=SomeValue,SomeOtherField=SomeOtherValue WHERE SomeKeyField=SomeKeyValue"

    cnn = New SqlConnection(connectionString)

    cnn.Open()
    cmd = New SqlCommand(Sql, cnn)
    cmd.ExecuteNonQuery()
    cmd.Dispose()
    cnn.Close()

In this way, there is generally no need to read in the record first. The intended row or rows are identified via the WHERE clause.

See this for examples with SQL parameters: http://dotnetfacts.blogspot.com/2009/01/adonet-command-parameters.html

BTW, if you are new to ADO.NET but know ADO, this MS article may be of some help: http://msdn.microsoft.com/en-us/library/ms973217.aspx