Read SQL / Update record

[gollnick]

Kind of new to SQL.
I have a process that is a detail/Master issue. I read (into a dataset) my detail record based on several keys. Then I have to read each master record..check some fields/update some fields and then update that master. Detail is not changed.
First.. How do I not lock the detail table during my select?

Second.. after I read my master.. do I need to close that connection BEFORE I attempt to update that record. Currently I set my sql command to get the record.. close the connection .. set my next sql command to UPDATE that record -- re-open connection and then do the executenonquery.
Seems like an awful waste of time but it works.

Is there a better way. We only get better if we ask questions from those who have done it before.

Thanks

gollnick

[jmcilhinney]

What you're describing is exactly what you should be doing. ADO.NET is designed to be used such that connections are opened only when needed and closed immediately after. If you have multiple SQL statements to execute together you would leave the connection open while they were all executed. If you're editing your data in code, which will be very fast, you should leave the connection open, otherwise close it in between. Selecting the child data won't lock the child table. ADO.NET uses optimistic concurrency, i.e. it allows multiple clients to access the same data and then only deals with conflicts if they occur, rather than locking tables and rows to prevent conflicts in the first place.

[gollnick]

Thanks .. I have been playing around with it the past several hours and found that if I close the datareader in between the selects and the updates I can cut a lot of processing out.

gollnick

[jmcilhinney]

Thanks .. I have been playing around with it the past several hours and found that if I close the datareader in between the selects and the updates I can cut a lot of processing out.

gollnick

I don't really know what that means because your first post doesn't mention a DataReader. When you talk in generalities then that's all we can do too. If you'd like us to comment on your code specifically, we'd need to see that specific code.

[gollnick]

Sorry... made a bad assumption that between a select and update I figured you would have to actually read the record.

gollnick