Solved

Refresh mysql data adapter method after insert Visual Studio 2010/2008

Posted on 2010-09-11
7
835 Views
Last Modified: 2012-05-10
Searching for a technique on refreshing the return value on an insert...
ie two commands back to back like so, works great on local environment, no so great when deploying to a godaddy.com env.  If you can't find this, then I'm looking for the most efficient method... lightning fast.  Needs to be in .net, c# and preferable visual studio 2010 using mysql connector.

ex
insert into `TableName` (`column1`, `column2`) values (@aVarible, @bVariable);
Select last_insert_id();

Unfortunately this technique is not working when deploying, and I may have to just do two separate queries if this doesn't work.  I'm thinking creating a serializable transaction scope around the two separate methods above.  However I don't have the ability to test the efficieny of this technique versus pushing all relevent matches into a set of parameters.
0
Comment
Question by:mnnoon
  • 4
  • 3
7 Comments
 
LVL 1

Author Comment

by:mnnoon
Comment Utility

Here is a perfect example of what I'm talking about... Is there a more efficient method for refreshing.  I am locking the dataset, which may be considered bad if the table is not properly isolated, or the bandwidth is sufficiently high.  Is there a way of setting a timeout so that once this connection is open that it is immediately closed if it can't meet a critical time obligation, and maybe send an exception when it fails.
::::::::::::::::::::::::::::::::::::::::::::
ata.Connection.Open();
ata.Transaction = ata.Connection.BeginTransaction(System.Data.IsolationLevel.Serializable);

ata.InsertAccount(DateTime.Now);
int aid = Convert.ToInt32(ata.LastInsertId());  <-- note since there is full level locking it's only option is to get the last id which theorhetically must be from the insert just above.
ata.Transaction.Commit();
ata.Connection.Close();
0
 
LVL 10

Assisted Solution

by:james-ct16
james-ct16 earned 500 total points
Comment Utility
Howdy

In regards to your timeout comments you want to be looking at your connection string and thinking about the following parameters

Connection Timeout=5; how long to wait to get a connection
default command timeout=20; default timeout for each command

both of theses are in seconds and can be overridden in code on a case by case basis. When either timeout occurs you will have exceptions thrown where you can then take whatever action you see fit.

Regards

James


0
 
LVL 1

Author Comment

by:mnnoon
Comment Utility
James, Do you have an example of that for a data adapter?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:mnnoon
Comment Utility
Or a link?
0
 
LVL 10

Accepted Solution

by:
james-ct16 earned 500 total points
Comment Utility
Howdy

A link can be found here http://www.connectionstrings.com/mysql#p28 so all you will need to do is modify the connection string in your webconfig

James
0
 
LVL 1

Author Comment

by:mnnoon
Comment Utility
Thanks for your response.  
The only thing is it didn't really answer my overall question which is, "Is this method of inserting data into a table at least as efficient for Refreshing the ID during an insert, as any other technique, or is this simply too inefficient and why?
 
0
 
LVL 10

Assisted Solution

by:james-ct16
james-ct16 earned 500 total points
Comment Utility
Howdy

To address your original question, if it were me and I was particuarly concerned about the effeciency of the insert I would be doing it as a stored proc returning just your identity and retrieved through executescalar. I would also make sure that connection pooling is correctly setup so you dont have to keep opening and closing connections, which is where you will lose the most time.

James
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Hangfire / asp.net sample 3 27
ASP.Net Session State alternatives 3 26
Long URL in SMS 6 23
Problem to page 4 17
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now