Solved

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

Posted on 2010-09-11
7
847 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 1

Author Comment

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

Accepted Solution

by:
james-ct16 earned 500 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 500 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

734 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