Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2010-09-11
7
Medium Priority
?
858 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
ID: 33655399

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 2000 total points
ID: 33655477
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
ID: 33659082
James, Do you have an example of that for a data adapter?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 1

Author Comment

by:mnnoon
ID: 33659173
Or a link?
0
 
LVL 10

Accepted Solution

by:
james-ct16 earned 2000 total points
ID: 33659193
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
ID: 33659286
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 2000 total points
ID: 33659361
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
An ASP.NET Web Form User Control is not newly introduced in ASP.NET. In fact, it was an old technology yet still playing a role to generate web content, especially when we want to use it to have a better and easy way to control part of the web conte…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

579 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