Which database to consider for transactional functions

Question

0.00/5 (No votes)

See more:

I am designing an accounting business software. At first thought I planned to use MySql as backend. But, after a few days of work, I found that its really tough to deal with MySql for transactions.

MySql connector for Ado.net is not implemented well for this purpose (as I can see, personal experience). They do not even have a function to set autocommit on or off. Which is the biggest drawback I see. You got to start the transaction in the query itself to turn off autocommit for transactions instead of handling transactions from frontend.

It is becoming an overhead for me.

I used MySql as it is open source and I had good experience with it.

Now, please let me know if I am wrong in my concerns about MySql or I should switch to other RDBMS.

What I have tried:

Tried googling for a good post but havent found one.

Posted 5-Oct-16 3:32am

Member 11040029

Updated 5-Oct-16 3:52am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Foothill · Accepted Answer · 2016-10-05T03:52:00

Solution 1

I'm a little biased but I would suggest looking into SQL Server.

Serveral editions from free to all the bells and whistles
Full support for transactions
Full .Net library support
2016 version performance is on par with Oracle
T-SQL is easier to work with pure opinion

Example of using transactions with SQL Server and .Net

C#

SqlConnection conn = new SqlConnection(_connectionString);
try
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction(); // BEGIN TRANSACTION
SqlCommand = new SqlCommand() { Connectin = conn, CommandType = SqlCommandType.StoredProcedure };
// assign command parameters and execute the statement....
trans.Commit(); // COMMIT TRANSACTION
}
catch(SqlException)
{
 trans.Rollback(); //ROLLBACK TRANSACTION
}

Posted 5-Oct-16 3:52am

Foothill

Comments

Member 11040029 5-Oct-16 9:56am

yeah sql server would be a good choice Foothill. I am still interested to know what lacks for mysql in .net..if you got any idea?

Foothill 5-Oct-16 10:03am

Direct support from Microsoft. All other database support is either through generic ODBC calls or their own custom database library (e.g. Oracle.ManagedDataAccess to name one).

Member 11040029 5-Oct-16 10:27am

ur right foothill..but i need more satisfactory answers on this post..if u can help me with that a little bit more

Foothill 5-Oct-16 10:30am

I can try. What specific information are you looking for?

Member 11040029 5-Oct-16 10:44am

actually lots of work has already been done in mysql..I mean tables, procedures etc are created and referenced. Any good way to export all these to sql server..or any good approach for mysql transactions in .net?

Foothill 5-Oct-16 10:53am

I don't know of anything pre-built to clone a MySql database to SQL Server.
If you want attempt transactions in MySQL, give this a read: http://www.codeproject.com/Articles/6721/Transactions-locking-and-table-releasing-with-MySQ
It looks complicated but doable.