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

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.
LVL 1
mnnoonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mnnoonAuthor Commented:

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
james-ct16Commented:
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
mnnoonAuthor Commented:
James, Do you have an example of that for a data adapter?
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

mnnoonAuthor Commented:
Or a link?
0
james-ct16Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mnnoonAuthor Commented:
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
james-ct16Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.