Update database

[simon]

I have cashed dataSet

ds = Cache("userDataSet")
If ds Is Nothing Then
Cache("userDataSet") = createDataSet()
End If

function CreateDataSet
'code for creating dataSet
'SELECT name,surname FROM testUpdate WHERE id=1
end function

Then user change the values in dataSet
and in the end I would like to update this changes to the database.

Function UpdateDatabase
Dim adapterU As New SqlDataAdapter
Dim cmdUpdate As New SqlCommand
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("appStrConnection"))
myConn.Open()
cmdUpdate.Connection = myConn
cmdUpdate.CommandType = CommandType.StoredProcedure
Dim name As New SqlParameter("@name", SqlDbType.VarChar, 50)
name.SourceColumn = "name"
cmdUpdate.Parameters.Add(name)
Dim surname As New SqlParameter("@surname", SqlDbType.VarChar, 50)
surname.SourceColumn = "surname"
cmdUpdate.Parameters.Add(surname)

adapterU.UpdateCommand = cmdUpdate
adapterU.Update(ds, "testUpdate")
end function

Stored procedure is:

CREATE PROCEDURE pf_updateUser
@ime varchar(50),
@priimek varchar(50)
AS

UPDATE testUpdate SET
name=@ime,
surname=@priimek
WHERE id=1

This works. I would like to know only if this is the right procedure or is
there some better way for this.
If I have dataSet in session, should I put somehow also adapter in session?
And should I use sqlCommandBuilder somehow?¸


Thank you,
Simon

 

[Cor Ligthert]

Hi Simon,

If the updates go right, I cannot see what is wrong with the syntax you use,
exept one thing.

The cache belongs to the applications and therefore to all clients.

I think it is better to use the session in this situation when you are not
sure if there are more than one clients which can have a session open in the
same time.

(The cache is for datasets which are not updated however can give
information to all clients by instance a catalog)

I hope this helps?

Cor

 

[simon]

Thank you for your answer.
There can be more clients at the same time and each client can update the
record but only for his ID.

If I use session for each user, I can put all changes into the session
variables, so why use dataSet then?
Isn't that only ballast?

Thank you,
Simon

 

[Cor Ligthert]

Hi Simon,

That is why I wrote "if everything is working fine", you can use it to do
the updates from the user, however I never saw a nice sample doing that with
a webpage.

Cor

 

[Cor Ligthert]

Hi Simon,

I mean a binded dataset as in windows forms, for classic use you can of
course use it to update your databases.

Cor