Solved

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

Posted on 2010-09-11
7
839 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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