.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Multiple sql-statements in one transaction

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :ASP.Net
Hi - I've two sql-statements - two inserts statements.The first one creates a person and the next creates an entry in another table with the person id as reference - everything works fine but I've been looking at my code and would like to know how I should wrap it up before firing the statements.I keep it all in one transaction - if the creating of the person fails or the other operation I need to rollbackMy question is:Should I use the same SqlCommand object to fire both statements? At this point I create a new instance for each statement - but isn't it possibly to use the same SqlCommand object, the same SqlConnection and wrap it into the same SqlTransaction?What is best practise in situations like this?

View Complete Post

More Related Resource Links

Multiple web methods in one transaction


Hi everyone

Sorry I am kinda new in web services, so it may sound a bit newbish question.

We have a web service that comunicates with the database and exposes web methods that enables the application to access the database. Each method does only one operation (Insert, Update or Delete). What I need to do is create a transaction that will be consisted of multiple calls to the database, meaning multiple calls of more then one web methods. Can I do that? (I read that if the web method does not throw exception - or ContextUtil.SetAbort() is not called - the transaction will be commited automatically when the web method finishes executing, which I don't want to happen because I want to call another web method in the same transaction, and then manually commit the transaction)

Thank you in advance.

Multiple update statements per row in Gridview Edit


I want to set up a gridview that will display data like this

WeekEndingDate Project Category Sun Mon Tues Wed Thu Fri Sat
8/14/2010      proj1   test     2   1   2    3   2   1   0
8/21/2010      proj1   test     0   2   2    2   2   1   0
8/28/2010      proj1   test     0   1  

Multiple statements in SSCE 3.5 SP2?

Hi all, I'm just wondering whether it's possible at all to execute multiple statements & multiple heterogeneous statements using the semicolon (";") separator.  SSCE balks at it right now if I issue via ADO.NET command text similar to the following (line separator is your typical \r\n): insert into Foo (id, name) values (1, 'foo1'); insert into Bar (id, name) values (1, 'bar1'); update Goo g set g.name = 'Goo1', g.fooId = 1, g.barId = 1 where id = 1; SQL Server Management Studio executes it just fine, which led me to believe that I could do it via ADO.NET. Can someone who knows please clarify?  What do I have to do in order to batch commands to ADO.NET? Thanks, Matthew

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon                              

Dropping and linking multiple items within the same editing scope transaction

Hi, I'm using IActivityTemplateFactory.Create to provide a custom experience when the user drops a toolbox item onto my Flowchart designer.  The implementation of IActivityTemplateFactory.Create is to use the model item tree to programmatically create multiple model items (related to activities), and link them together using the appropriate model item properties and connector view state.  All this works fine except for one thing. Say I'm dropping three activities as a result of IActivityTemplateFactory.Create.  I want the first activity to appear at the top, underneath should be the second activity and below that the third activity.  I want all three activity designers to appear alighned around their center point and there to be a straight, centered connection arrow between activity one and two and another straight, centered connection arrow between two and three.  Here's some ASCII art (we'll see how the formatting is retained - not very well it seems): |-----------------| | Activity 1 | | | |-----------------| | | | \/ |--------------| | Activity 2 | |--------------| | | | \/ |----------| |Activity 3| |----------|  It's important to note that the activity designers all not all the same size; both the height and width can vary. I've written code that looks at the various activity

Multiple databases backup/restore in a transaction, if possible, into only one file


Hi dear,

I have three databases named XDW, XOLTP, XOLTPSchema. All of these are for only one application. In the application, user should do backup/restore using GUI.


1. How I should do backup/restore in one transaction? i.e. all of three actions should be success elsewhere all of them should fail.

2. Is it possible to backup them into only one file e.g. X.bak?

3. Or, What is the standard solution in this condition?

Thanks in advance.

How To: Joining multiple DataTable using “LINQ to DataSet”

Dropping and linking multiple items within the same editing scope transaction - Part 2



In an earlier forum post, I asked about how to drop multiple items from the toolbox onto the design surface, have them arranged nicely, and do all this in a single editing transaction.  In that post, Kenny came up with a nice solution.

There's one more scenario that I didn't discuss in the earlier post, that I was hoping to be able to resolve on my own after getting the other post answered.  But, I I'm afraid I need some more help.

The scenario is the same as in the earlier post with one additional complication.  One of the items I'm dropping as part of the drag/drop operation is a FlowSwitch node.  If I did nothing else, the solution presented in the earlier forum post would work.  However, I've an additional need to visually change the appearance of the FlowSwitch designer. Normally, one can change an activity's designer (even a built in activity) by using the metadata store.  However, the FlowSwitch is unique in that the Flowchart designer has hard-coded knowledge of the FlowSwitch designer, and if you replace the built in designer via the metadata store, there are numerous places where the Flowchart designer will generate exceptions and pop up error messages.  I also have the need t

'Add Service Reference' not working for multiple include statements


I'm using VS 2010 (.NET 4.0) and I cannot use 'Add Service Reference' to generate my .NET proxy client since looks like using 'Add Service Reference' (svcutil.exe) option doesn’t work if there are multiple include statements for the schemas in the wsdl. Using "Add Web Reference" (wsdl.exe) option does work to consume this kind of wsdl but having issues passing UsernameToken.

My understanding is it generates WCF client only if 'Add Service Reference' option is used and not 'Add Web Reference'.

The web service is from the 3rd party, so I cannot modify it and they expect UsernameToken in the SOAP header.

Here’s the url to get the package that co

INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.

MSSQL 2005 JDBC driver multiple select statements in stored procedure



we have a stored procedure with multiple select in it.



Select a,b,c from table1

Select f,g,h  from table2

Select j,k,l  from table3


Sometimes select no 2 will not return any values.

when this happens  we get only 2 result sets using CallableStatement getMoreResults method. 

it seems that if a select  doesn't return something it will  not have a corresponding resultset. 

On 2000 (driver + server) version we got an empty resultset for this situation. 

Is there  any  way to have the same behaivor with 2005 ?   We tryed with 1.2 and 2.0 jdbc driver.




Multiple statements are not allowed.


Hi I'm trying this  :



([Date].[Fiscal Year].&[2008],



[Measures].[Sales Amount Quota]);




= (ParallelPeriod([Date].[Fiscal].[Fiscal Year], 1,



[Measures].[Sales Amo

How to post multiple declare statements


I know how to do this with 1 declare statement, but for this there are multiple one's.  This example sql statement has 2, however my  actual one will have 29.

declare @Amount1 float, @Amount2 float
select top 1 @Amount1 = CAST(dbo.Designated_Amount.Amount AS Float)
  FROM dbo.Designated_Amount INNER JOIN
    dbo.SIMS ON dbo.Designated_Amount.Unit_Code = dbo.SIMS.Unit_Code
  Where (dbo.Designated_Amount.Unit_Code = '114') AND dbo.SIMS.Event_Type in ('IR','ME','MO','PE','PO','RU','SF','U1','U2','U3') AND (dbo.SIMS.EventEndTS is Null)AND (dbo.SIMS.Avail_Cap = 0))

select top 1 @Amount2 = CAST(dbo.Designated_Amount.Amount AS Float)
  FROM dbo.Designated_Amount INNER JOIN
    dbo.SIMS ON dbo.Designated_Amount.Unit_Code = dbo.SIMS3.Unit_Code
  Where (dbo.Designated_Amount.Unit_Code = '105') AND dbo.SIMS3.Event_Type in ('IR','ME','MO','PE','PO','RU','SF','U1','U2','U3') AND (dbo.SIMS3.EventEndTS is Null)AND (dbo.SIMS3.Avail_Cap = 0))
  ORDER BY dbo.SIMS3.EventStartTS DESC

;with cte1 as (SELECT TOP (1) CASE WHEN V300938F <= '.1' THEN coalesce(@Amount1,0) 
 ELSE 0 END AS Available

Multiple database support with Entity Framework

One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.

Binding the multiple drop down boxes in GridView using JQuery in ASP.NET

Here is the code for binding the multiple drop down boxes in GridView using JQuery in ASP.NET

Upload Multiple File using JQuery in asp.net

In this article I have explained how to upload multiple files using file upload control. I have used jQuery plugin for uploading multiple files.

I have also explained how to check for file type, file maximum size limit using jQuery & also using server side validation code.

Adding Multiple Columns and Rows in GridView without using a Database

This article describes on how to add multiple columns and rows in GridView without using a database. Basically the GridView will be populated with data based on the values entered in each TextBoxes on Button Click and retain the GridView data on post back.

GridView Multiple Filter AJAX Control

This AJAX control enables the user to filter data within any column inside a GridView. The user can add multiple filters and can delete existing ones too.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend