Database

Re: very urgent pls help me about concurrency in ADO.Net

18-Jan-07 1:15

ed^nrgc18-Jan-07 2:14

Re: very urgent pls help me about concurrency in ADO.Net

18-Jan-07 2:14

Why do you need a transaction for a simple select?

What about adding (nolock)?

Rob Graham18-Jan-07 3:48

Rob Graham

18-Jan-07 3:48

The choice as to where to maintain the transactions depends on your usage, but a good guideline is never mix the two. If yo choose to put transactions in your SPs, then DON'T put them in the application code as well.
IMO, keepeng the transaction management in the application code is more flexible, and allows use of distributed transactions (should they ever be needed) more gracefully. It also allows transaction scope to span multiple SP's if needed.
Note that transactions are rarely (if ever) needed for Select statements, but should generally be used for data modification statements (Insert, Update).

Re: very urgent pls help me about concurrency in ADO.Net

Mike Dimmick19-Jan-07 6:34

Mike Dimmick

19-Jan-07 6:34

The point of a transaction is to give consistency across multiple operations. If you need that consistency within a stored procedure, use BEGIN TRANSACTION/COMMIT TRANSACTION/ROLLBACK TRANSACTION in the stored procedure text. If you need that consistency across multiple calls from the client application, use the SqlTransaction object.

I personally favour consolidating as many basic operations as possible, that are directly related, into a single stored procedure or query batch, in order to avoid being 'chatty' with the database (and incurring a lot of network latency). Therefore I very rarely use SqlTransaction objects.

Stability. What an interesting concept. -- Chris Maunder

PavanPareta18-Jan-07 0:40

PavanPareta

18-Jan-07 0:40

can some body let me know what is the main diffrence between SQL function and Store Procedure.

Cool | :cool:

Pavan Pareta

Re: hi

ed^nrgc18-Jan-07 2:14

Copy data from datatable to SqlServer table

18-Jan-07 2:14

1. out params

Re: hi

M.H.1.2.318-Jan-07 10:28

M.H.1.2.3

18-Jan-07 10:28

1.stored procedures allow default values for parameters user defined functions do not.
2.stored procedures allow you to use temp tables user defined functions only allow table variables.
3.you can use table valued user defined functions for normal table functionality such as joins, select *, etc. which can be useful sometimes.
4.both have cached execution plans.

In general I would suggest to use procedures when you can and function only when a procedure won't do the job, just because it seems that functions could allow you to kill your performance when used in where clauses on queries with alot of rows, or even if used in select list on queries with alot of rows. Also functions calling functions makes sense to an application developer but doing something like that in your databse functions may be tempting, but imho is a bad idea.

Re: hi

PavanPareta18-Jan-07 17:35

PavanPareta

18-Jan-07 17:35

Thanks M.H.1.2.3,
thanks for Solution...
Rose | [Rose]

Pavan Pareta

Member 368588918-Jan-07 0:05

Member 3685889

18-Jan-07 0:05

Hi,

Can anyone help me with the code to copy data from datatable to SqlServer table. Please dont use the SqlBulkCopy technique as i am working in .net 1.1 which does not support that. Give me the code to copy the rows in datatable one by one to database.

Thanks in advance...

Usha

Re: Copy data from datatable to SqlServer table

ed^nrgc18-Jan-07 2:17

ANY operator in SQL Server

18-Jan-07 2:17

Use DTS.

Uma Kameswari17-Jan-07 23:23

Uma Kameswari

17-Jan-07 23:23

Hi,
Again I am back with ANY operator Smile | :)

can anyone give me an example of how to use any operator in sql server?

Regards,
Uma

Re: ANY operator in SQL Server

Pete O'Hanlon18-Jan-07 0:22

Pete O'Hanlon

18-Jan-07 0:22

SELECT Col1, Col2, Col3
FROM TABLE1 WHERE
Col4 >= ANY
( SELECT Col1 FROM TABLE2 WHERE DateAdded >= '21-Oct-2006')

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

handling database records

rahulpasupuleti17-Jan-07 22:16

rahulpasupuleti

17-Jan-07 22:16

Hi there.i have two button controls on my UI of a web application.i name them as 'previous' and 'next'.when i click on 'previous',i should be able to go to the previous record and when i click the 'next' button,i should be able to go to the next record in an sql server database.i also have two buttons for adding and editing records in a dataset.can i get help in writing code in c# preferably.

thanks.

Re: handling database records

Vinay Dornala17-Jan-07 22:45

Re: handling database records

17-Jan-07 22:45

hi
use BindingContextConcept and try with this code.Ihope will help for u r requirement and give replay

write in nextbutton_click event

this.BindingContext("nameofDataSet","NameofTable").position= this.bindingContext("NameofDataset","nameoftable").position+1;

this is for prevbutton_click event

this.BindingContext("nameofDataSet","NameofTable").position= this.bindingContext("NameofDataset","nameoftable").position-1;

if the record cursor reaches first record then u can disabl prev and same for next

Have a nice day

rahulpasupuleti18-Jan-07 0:29

rahulpasupuleti

18-Jan-07 0:29

thank u very much.i had also mentioned about adding and editing records.i tried with controlname.databindings.add.but thats not working since i am implementing in a webapplication and that method only seems to work for windows forms.can i get any help in this?

thanks in advance.

about Transactions

Vinay Dornala17-Jan-07 18:15

17-Jan-07 18:15

Hi All,

Can we have nested Transcations means one transaction within other ;

what is the difference between rollback Transaction and rollback work in sql

Pls give clarification

WINNING IS NOT OUR DREAM, IT'S A HABIT,

HAVE A NICE DAY

Re: about Transactions

Pete O'Hanlon18-Jan-07 0:29

Pete O'Hanlon

18-Jan-07 0:29

You can nest transactions and roll them back using ROLLBACK TRANSACTION. ROLLBACK WORK rolls the transaction back to the top (and sets the transaction count back to 0) regardless of which level of nested transaction you are in.

begin transaction TopTransaction
update table1 where tblid = @id
save transaction innerTransaction
update table2 where tblid = @id
rollback transaction innerTransaction
commit transaction

In this example, the changes to table1 are saved and table2 changes are rolled back.

begin transaction TopTransaction
update table1 where tblid = @id
save transaction innerTransaction
update table2 where tblid = @id
rollback work

In this example, both transactions are rolled back because the rollback work command has been issued.

Re: about Transactions

Vinay Dornala18-Jan-07 0:55

SQL2005 Licensing (Boring)

18-Jan-07 0:55

thanks for u r reply ,

But my Question is

begin Transaction1

one or more sql stmt
begin transaction2
one or more sql stmt
end transaction2

end Transaction1

pls confirm once for me

Re: about Transactions

Pete O'Hanlon18-Jan-07 1:14

Pete O'Hanlon

18-Jan-07 1:14

You can nest transactions. Why not have a try and see what the results of various commit/rollback scenarios are?

thedom217-Jan-07 14:15

thedom2

17-Jan-07 14:15

Hi

Can anyone tell me if i purchase SQL2005 standard with 5 cals, whether it will allow more than 5 users connected as my numbers are up and down and i dont want to purchase too many cals for startup of project.
I know with sql2000 its ok to go over.

Thanks
Dom
D'Oh! | :doh:

SQL Distinct

nlindley717-Jan-07 9:59

nlindley7

17-Jan-07 9:59

So I have a database with multiple records with the same agtMast field. What I need to do is select unique agtMast records, but i need the whole record. I know this will select the unique fields...

SELECT DISTINCT agtMast
FROM table_name;

...but it only selects the agtMast field and I need the entire record.

Any help would be greatly appreciated.

Nathan Lindley

Drew McGhie17-Jan-07 11:31

Drew McGhie

17-Jan-07 11:31

Well, say there are 3 rows
agtMast ID Name Other
One 22 James Other1
One 22 James Other2
Two 22 Bond Whee!

There are 2 distinct values for the agtMast column, but 2 possible sets of values are different. Do you want one row for each agtMast value, and are there specifics as to which one should be selected, or do you want the distinct sets of values (So if One/22/James/Other1 appeared 10 times, you would want it to appear only once, but have One/22/James/Other2 appear as well after the query)

I need some more information as to how you're clarifying the "correct" row.

nlindley718-Jan-07 3:20

nlindley7

18-Jan-07 3:20

Thanks for the reply Drew.

What I am looking for is just one of each of the agtMast (which is the agent's code) records. What the situation is is before on the old system, they listed each agent every time they changed an address (instead of updating, they just added new rows). What I need to do is just get one of the records (1 for each agtMast code, but no more than 1), for the main contact information and if the address is not the current one, it doesn't matter. I would just put the DISTINCT records into a holding table, but i'm not able to grab just a single record for each DISTINCT agtMast.

Thanks again for the reply!

Nathan Lindley

ed^nrgc18-Jan-07 2:19

18-Jan-07 2:19

Group by agtMast