Update locks table.

Although I think my problem has mainly to do with my connection settings, I try my luck here anyway.

I have a table in which I want to update various records. The update statements take ages to complete. My test database is very small. The biggest table has 50 records.

When the update statement is 'running' I can access the other tables in SQL data manager but not the 'updating' one.
In SQL server I can see various process assigned to the asp_webuser. When I kill them I can access the table again. 

<code>

Tlist <handlingCue> lst = DataProvider.HandlingCueProvider.getByUserId(userId);

foreach( HandlingCue c in lst)
{
    c.isHandled = 1;
    dataprovider.HandlingCueProvider.Update(c);
}

</code>

I know I can also do this by a trigger or writing my own stored procedure. I want to keep the deployment as easy as possible.
And the loop above will rarely have 2 or more records. So I thought this approach was acceptable :)

Any ideas / hints what might be wrong ?

 Regards,
--W 

Two things, do you have EntityTransactionModule in your web.config?  Perhaps there are open transactions if it's set, but you don't use it.  But it definately shouldn't take a long time. 

Tlist <handlingCue> lst = DataProvider.HandlingCueProvider.getByUserId(userId);

foreach( HandlingCue c in lst)
{
    c.isHandled = 1;
}
//send the whole list instead of each instance, it will only do the entity validation once.
dataprovider.HandlingCueProvider.Update(lst);

Hi All,

I appear to be having a similar issue with updating a record in Sql Server Express 2005 64bit. The exact same website worked fine on my old environment (VS 2005, EntLib 2, SqlServer Exp 2005 32bit). However, on my new environment (VS 2008, EntLib3.1, SqlServer 2005 Exp 64bit) i'm having issues.

 After i try to perform an update on a record with the generated admin pages, i'm unable to access the table containing the row i tried to update via Select * queries in management studio. I can however access the table via the admin page, but if i again try to update, the table gets locked again. I'm not sure where to go from here.

 Any suggestions would be good.

Cheers,

Sam

I know this post is ancient but wanted to say that I recently added the .WEB dlls to my project and was using datasource objects but did not have the EntityTransactionModulein the web.config and it locked a table on update for minutes at a time. As soon as I added EntityTransactionModule to the web.config it fixed the issue immediately. I only say this for anyone running into the same issue can maybe find their solution.

Theres no need to use SP's with nettiers... I havent tryed LINQ yet, does it support databases other than SQL Server?

I used SPs all the time. If you have a query that doesn't fit within the basic table structure or that needs very special parameters, SPs are the way to do that. NETTIERS is built to where it incorporates those SPs as methods. I am not sure why you would say that.

I create my SQL sentences in the procedures.xml file and the wraper to call it... so there, no SP's in the database....

Just because the option of SP's is the most used doesnt mean you have to, or it's your only option....

I can see that. But if I have stored procedures that need a quick tweak, as long as it doesn't affect the output structure, I can make those tweaks without having to regenerate nettiers. That's why I like using SPs when I need more control over a query.

I dont like SP's, you do...

We better stay out of the "Procedures x Paramt. Queries" war. (;

Good thing we dont work together. lol

Default Linq does not support other databases other than SQL 2005. With .NET framework 3.5 and SP1 it also supports SQL server 2008.

However since Linq is a provider you can write your own implementations of your favorite SQL database... or save yourself a lot of time and download one of the many 'open source' onces available on the net. ( ex http://code.google.com/p/dblinq2007/ )

I certainly  agree that stored procedures can be very helpfull and in some cases even unavoidable. However performance tests show that Linq is doing a prety good job performance wise that i don't even bother to write my own stored procedures. Most of my applications are used internally with customers. Although they are critical most of the time performance is not an issue.

I switched to Linq since there was not that much activity anymore at that time. .Nettiers has nice features even onces that don't exits in Linq (ex. working with disconnected objects (unless you want to add a time stamp to each and every record) ). I noticed the new versions. When I find some time I might look into them.

--W

I love vbandrade's passion!  He is glad we don't work together though :)

Nettiers will always support both SP's and Non SP implementations!  That is what is nice, you can do it either way.  That way, we don't have to have the argument about who likes SP's vs Parameterized Sql :)  That way vbandrade and I can stay on speaking terms :)

I looked at LINQ alot when it came out, it is young.  We are really talking about DLINQ.....you can use LINQ with Nettiers objects.  DLINQ has a ways to go....when you run a DLINQ query, you have to maintain the Manager that retrieved the Data in order to update it.  You couldn't call a service layer to retrieve an ogject, destroy the service layer....create a new one, call save.  The save would fail.  In reality, this is V1 of DLINQ...hopefully they fix some of these short comings in later versions.....<rant>but MS always likes to make things that will help you create an APP in 5 minutes, vs the Enterprise Developer that has to isolate layers out and build an app to scale to many servers. </rant>

Search for DLINQ, I outlined some of the shortcoming here before.....at least I thought I did.

jeff

vbandrade:

I create my SQL sentences in the procedures.xml file and the wraper to call it... so there, no SP's in the database....

You have my curiosity now... Do you edit the procedures.xml after NetTiers is generated and then write your own methods to utilize them or how exactly do you do that?

Its a bit more complicated than that...

i have 2 procedures files... .netTiers generated and a custom one, so when i need to regen, i dont lose it all

Then i edited

StoredProcedureProvider.GetProcedureBodyFromEmbeddedResource

to receive one more parameter that indicates if that is a custom procedure or not, to load from another file.

And that was it. (;

Working like a charm.

Wow, that seems pretty complicated. You don't have an example to see somewhere do you?