|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
System.Transactions stored procedure returns NULL valueFirst off I am a newbie at using System.Transactions. I am trying to do what would seem to be a simple task. I am using a C#.Net in a Windows Form to extract information from a powerpoint document into a Sql Server 2005 Database. On the Database side: - I have two tables for TALKS and SLIDES - I also have two corresponding sp_save_talk and sp_save_slide that return the @@IDENTITY of the newly inserted row. On the WinForm side: - I have a strongly typed dataset for talks and slides which implement the StoredProcs as TableAdapter methods and return the identity. My Questions are: - Why is the return value of @@IDENTITY is coming back null, which doesn't help, because I need it to insert the slides? It works and I get a return value if I comment out the using TransactionScope line. - Am I using the right transaction type? - Does this even make sense? - I was going to just scrape the idea and use conn.begin transaction, but I cant find anywhere in the Strongy typed DataSet to use a transaction. Does one exist? I am trying to use the following code: using (System.Transactions.TransactionScope tx = new System.Transactions.TransactionScope()) { using (SqlConnection conn = new SqlConnection(MySettings.EndocannabinoidConnectionString)) { try { conn.Open(); int? talkId = 0; using (talksTableAdapter talkAdapter = new talksTableAdapter()) { talkAdapter.Connection = conn; talkId = (int?)talkAdapter.Save(0, Path.GetFileName(this.destination.Text), this.title.Text, (int?)this.topics.SelectedValue); } using (slidesTableAdapter slideAdapter = new slidesTableAdapter()) { slideAdapter.Connection = conn; foreach (Slide s in _pres.Slides) { // Insert Slides int? slideId = (int?)slideAdapter.Save(0, talkId, (int?)s.SlideIndex, Path.GetFileName(this.destination.Text) + "-slides/" + Path.GetFileName(s.ThumbnailImageFilename), Path.GetFileName(this.destination.Text) + "-slides/" + Path.GetFileName(s.ImageFilename), s.Title, s.Notes, s.Content, s.Keywords); } } tx.Complete(); } catch (Exception ex) { MessageBox.Show("Rolled Back because " + ex.Message + (ex.InnerException == null ? "" : ", " + ex.InnerException.Message)); } } } Hey Ben,
What is in the "Save" Method? BTW - you may find this blogpost useful - http://codebetter.com/blogs/sahil.malik/archive/2005/10/19/133309.aspx - Sahil Malik [MVP] ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx ---------------------------------------------------------------------------- Show quote "Ben Dewey" <Aspiring***@hotmail.com> wrote in message news:eV0gnNUPGHA.740@TK2MSFTNGP12.phx.gbl... > Hey everyone, > > First off I am a newbie at using System.Transactions. I am trying to do > what would seem to be a simple task. > > I am using a C#.Net in a Windows Form to extract information from a > powerpoint document into a Sql Server 2005 Database. > > On the Database side: > - I have two tables for TALKS and SLIDES > - I also have two corresponding sp_save_talk and sp_save_slide that return > the @@IDENTITY of the newly inserted row. > > On the WinForm side: > - I have a strongly typed dataset for talks and slides which implement > the StoredProcs as TableAdapter methods and return the identity. > > My Questions are: > - Why is the return value of @@IDENTITY is coming back null, which > doesn't help, because I need it to insert the slides? It works and I get > a return value if I comment out the using TransactionScope line. > - Am I using the right transaction type? > - Does this even make sense? > - I was going to just scrape the idea and use conn.begin transaction, but > I cant find anywhere in the Strongy typed DataSet to use a transaction. > Does one exist? > > > I am trying to use the following code: > using (System.Transactions.TransactionScope tx = new > System.Transactions.TransactionScope()) > { > using (SqlConnection conn = new > SqlConnection(MySettings.EndocannabinoidConnectionString)) > { > > try > { > conn.Open(); > > int? talkId = 0; > using (talksTableAdapter talkAdapter = new talksTableAdapter()) > { > talkAdapter.Connection = conn; > talkId = (int?)talkAdapter.Save(0, > Path.GetFileName(this.destination.Text), this.title.Text, > (int?)this.topics.SelectedValue); > } > > using (slidesTableAdapter slideAdapter = new slidesTableAdapter()) > { > slideAdapter.Connection = conn; > foreach (Slide s in _pres.Slides) > { > // Insert Slides > int? slideId = (int?)slideAdapter.Save(0, > talkId, > (int?)s.SlideIndex, > Path.GetFileName(this.destination.Text) + "-slides/" + > Path.GetFileName(s.ThumbnailImageFilename), > Path.GetFileName(this.destination.Text) + "-slides/" + > Path.GetFileName(s.ImageFilename), > s.Title, > s.Notes, > s.Content, > s.Keywords); > } > } > tx.Complete(); > } > catch (Exception ex) > { > MessageBox.Show("Rolled Back because " + ex.Message + (ex.InnerException > == null ? "" : ", " + ex.InnerException.Message)); > } > } > } > The Save Method is a method linked to the sp_save_talk Stored Proc.
its return value is a int? which should be the @@IDENTITY, but its coming back null. Did you add it? Or was it auto-added for you?
SM Show quote "Ben Dewey" <Aspiring***@hotmail.com> wrote in message news:O9WxVlUPGHA.2036@TK2MSFTNGP14.phx.gbl... > The Save Method is a method linked to the sp_save_talk Stored Proc. > > its return value is a int? which should be the @@IDENTITY, but its coming > back null. > It was added using the wizard for an existing Stored Proc attached below:
ALTER PROCEDURE dbo.save_talk ( @talk_id_in int, @talk_filename_in nvarchar(500), @talk_title_in nvarchar(500), @topic_id_in int ) AS IF @talk_title_in = '' BEGIN SET @talk_title_in = NULL END IF @talk_id_in = 0 OR @talk_id_in IS NULL BEGIN INSERT INTO talks (talk_filename, talk_title, topic_id) VALUES (@talk_filename_in, @talk_title_in, @topic_id_in) RETURN @@IDENTITY END ELSE BEGIN UPDATE talks SET talk_filename = @talk_filename_in, talk_title = @talk_title_in, topic_id = @topic_id_in WHERE talk_id = @talk_id_in RETURN @talk_id_in END You can't do anything if it is indeed being promoted to MSDTC, so "make
sure" means ---- If it is indeed promoting to MSDTC, and really you're doing transactions on a single database, then change your architecture so it doesn't promote. Did you see adding SqlTransaction support to a TableAdapter? SM Show quote "Ben Dewey" <Aspiring***@hotmail.com> wrote in message news:uoetrFVPGHA.3936@TK2MSFTNGP10.phx.gbl... > How do i? > > a) make sure your transaction isn't promoting to MSDTC. > First,
There is no reason why it would be promoting, I am using one Database and inserting into two tables, very basic, no... Is there anything fancy I need in my connection script, like Enlist=true, etc? Second, I saw your post, but my tableAdapters don't have a BeginTransaction Method I do have tableAdapter.Connection.BeginTransaction, tableAdapter.Connection.EnlistDistributedTransaction, and tableAdapter.Connection.EnlistTransaction. Should I use that? Does this mean that i am not suppose to use TransactionScope? Thanks for the help. Ben,
Your tableAdapter may be promoting the transaction..IIRC, it handles transactions on its own. Add an Enlist = false to your connection string. Secondly, you want to use scope_identity in place of @@identity; @@identity can get 'confused' sometimes. Andy >I am now using SCOPE_IDENTITY() Yes and it won't!!! (See my other reply).> Enlist=false; didn't work... My apologies I should have paid more attention to your messages instead of the half assed help I've offerred so far, so please put up with my slightly longer reply here. (Sorry just been hella busy lately). You need to manage your own connection lifetimes - that is the basic problem. Sys.Tx tries to automate that for you - even though I notice that you are indeed opening the connection yourself and keeping it open and passing it around, I can't be sure of wtf the tableadapter is doing under the scenes. My strong recommendation is not to mix tableadapters + tx-scope because you can only make that work in a very very specific and completely non-intuitive code model (see my upcoming code-magazine article later). For now I'd recommend going to your dataset - select the tabeladapter - and hit "Del" - and update the db with a SqlCommand instead. This can be done using plain vanilla ado.net concepts. DO NOTE: When using DataAdapter + TxScope - manage your connection lifetime yourself - don't let the dataadapter do that for you. And frankly when you have gone to that depth - why not just use a SqlCommand, SqlCOnnection and SqlTransaction? :) ..... If you *must use* TableAdapter, ..... Try and implement the BeginTransaction method as I described on my blog ..... If you *must use* TxScope, ..... Manage connection lifetimes yourself. Don't let the Tableadapter or the DataAdapter do them for u. BTW, I'm dissing only TableAdapters, TxScope is a great feature but TableAdapters in my eyes suck eggs. Hope this helps. :) - Sahil Malik [MVP] ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx ---------------------------------------------------------------------------- Show quote "Ben Dewey" <Aspiring***@hotmail.com> wrote in message news:%23jgQc$WPGHA.2796@TK2MSFTNGP10.phx.gbl... >I am now using SCOPE_IDENTITY() > > Enlist=false; didn't work... > Enlist=false signifies that the current connection will not enlist within
the same transaction. That may or may not have any effect on the promotion as it doesn't have any affect on the transaction itself. But you don't want the new connection to not enlist within the same tx - you do want everything to be in one transaction, so I wouldn't recommend adding Enlist = False. Also the reason specified for scope_identity vs. @@identity is incorrect. Though I agree that you should in most situations prefer to use scope_identity, but in certain borderline cases you may have to use @@identity. - Sahil Malik [MVP] ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx ---------------------------------------------------------------------------- Show quote "Andy" <ajj3***@alum.rit.edu> wrote in message news:1141244588.669427.273140@u72g2000cwu.googlegroups.com... > Ben, > > Your tableAdapter may be promoting the transaction..IIRC, it handles > transactions on its own. Add an Enlist = false to your connection > string. > > Secondly, you want to use scope_identity in place of @@identity; > @@identity can get 'confused' sometimes. > > Andy > I suggested Enlist = false based on <a
href="http://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx">this</a> blog. Its how to work around having transactions promoted. The reason for the scope_identity was to rule out triggers that may be munging up @@identity. I haven't found a reason that compelled me to use @@identity over scope_identity. What would be the case where you'd prefer it (just for my own curiosity)?\ Andy Well while it is true that enlist=false will prevent the transaction from
being promoted - avoiding promotion at the cost of not enlisting in the transaction is probably not what you want. The basic point is, If you have a distributed transaction then there is no price difference to pay when using escalation, but if your local transactions start getting escalated, then you need to rethink your architecture. - Sahil Malik [MVP] ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx __________________________________________________________ Show quote "Andy" <ajj3***@alum.rit.edu> wrote in message news:1141330773.481270.48560@u72g2000cwu.googlegroups.com... >I suggested Enlist = false based on <a > href="http://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx">this</a> > blog. Its how to work around having transactions promoted. > > The reason for the scope_identity was to rule out triggers that may be > munging up @@identity. > > I haven't found a reason that compelled me to use @@identity over > scope_identity. What would be the case where you'd prefer it (just for > my own curiosity)?\ > > Andy > It still could be promoting, though the chances are low - just make sure by
looking under Component Services in the control panel. Secondly - yes my post talks about "Adding" a BeginTransaction method - yes it doesn't have that to begin with and that sucks. Frankly my view is, dump TableAdapter - thats half your problem. Nothing wrong with using TxScope - I have an article written up for code-magazine which explains all of this stuff in detail, which was unfortunately pushed to the next upcoming issue. For now I'd recommend a) wrap sqlcommands not tableadapters in a txscope b) manage your own connections rather than letting connection pooling do them for you - this is as simple as opening the connection *outside* the tx.scope. - Sahil Malik [MVP] ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx ---------------------------------------------------------------------------- Show quote "Ben Dewey" <Aspiring***@hotmail.com> wrote in message news:%23cjjnoWPGHA.1696@TK2MSFTNGP14.phx.gbl... > First, > There is no reason why it would be promoting, I am using one Database and > inserting into two tables, very basic, no... > Is there anything fancy I need in my connection script, like Enlist=true, > etc? > > Second, > I saw your post, but my tableAdapters don't have a BeginTransaction > Method > > I do have tableAdapter.Connection.BeginTransaction, > tableAdapter.Connection.EnlistDistributedTransaction, and > tableAdapter.Connection.EnlistTransaction. Should I use that? > > Does this mean that i am not suppose to use TransactionScope? > > Thanks for the help. > > > Well I got it working,
I looked deeper into the Dataset.Designer.cs and sure enough they were using ExecuteScaler to run the stored proc. So I changed my stored proc from RETURN @@IDENTITY to SELECT @@IDENTITY and everything worked. Now I am successfully using: using (TransactionScope) using (conn) { conn.open(); using (stronglyTypedDataAdapter1) { stronglyTypedDataAdapter1.connection = conn; id1 = stronglyTypedDataAdapter1.Save(val1, val2); // uses stored proc query } using(stronglyTypedDataAdapter2) { stronglyTypedDataAdapter2.connection = conn; id2 = stronglyTypedDataAdapter2.Save(id1, val1, val2); } transactionScope.Complete(); } Ben,
Glad to hear you got it working. Just out of curiosity..you didn't happen to be an intern for a web development company just outside of Philadelphia a few years ago, did you? andy  |
|||||||||||||||||||||||
Other interesting topics