Mono handles System.Data.SqlCommands differently with .NET?

Question

I'm having problems with the application we port to Mono.

(For reference, the .NET runtime is 4.0, and the mono version is 2.6.7.)

EDIT: this problem persists on Mono 2.10.2

As part of running the application, it reads data into memory. For this part, I just use the built-in SQL commands, but for some reason I see inconsistent behavior in Linux / Mono (when it all works in Windows / .NET).

I have a query that works fine in some scenarios, but not in others.

This specific example does not work:

var cmd = new SqlCommand("SELECT ID, Name, VATTerritoryID, NativeCurrencyID FROM PricingZones", conn); var reader = cmd.ExecuteReader(); var objectToLoad = new SomeObjectType(); while (reader.Read()) { objectToLoad.Property1 = reader.GetInt32(row.GetOrdinal("ID")); objectToLoad.Property2 = reader.GetString(row.GetOrdinal("Name")); objectToLoad.Property3 = reader.GetInt32(row.GetOrdinal("VATTerritoryID")); objectToLoad.Property3 = reader.GetInt32(row.GetOrdinal("NativeCurrencyID")); } 

EDIT: for comparison, here that works :

 var cmd = new SqlCommand("SELECT VATTerritoryID, ProductDescriptionID, VATBandID FROM VATTerritoryBandExceptions", conn); var reader = cmd.ExecuteReader(); var someOtherObjectToLoad = new SomeOtherObjectType(); while (reader.Read()) { someOtherObjectToLoad.Property1 = reader.GetInt32(row.GetOrdinal("VATTerritoryID")); someOtherObjectToLoad.Property2 = reader.GetString(row.GetOrdinal("ProductDescriptionID")); someOtherObjectToLoad.Property3 = reader.GetInt32(row.GetOrdinal("VATBandID")); } 

I had suspicions that there are differences:

• The case (since I know this is different from windows / linux), but everything that fits in lowercase did not solve the problem.
• Column names (maybe Mono cares more about reserved words?), But it seems that instead of Name with the name [Name] or 'Name' there were no others

The error in the first case was:

 [IndexOutOfRangeException: Array index is out of range.] at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i) 

Assuming the returned result set does not have "column1".

(EDIT: this section has been updated a bit for clarity)

Oddly enough, if I do this:

 var cmd = new SqlCommand("SELECT ID, Name, VATTerritoryID, NativeCurrencyID FROM PricingZones", conn); var reader = cmd.ExecuteReader(); var objectToLoad = new SomeObjectType(); while (reader.Read()) { Console.WriteLine("First row, first column is " + row.GetValue(0)); Console.WriteLine("First row, second column is " + row.GetValue(1)); Console.WriteLine("First row, third column is " + row.GetValue(2)); Console.WriteLine("First row, fourth column is " + row.GetValue(3)); } 

Output:

 First row, first column is 0 First row, second column is New Array index is out of range. 

I suppose that in this case something strange happens with the Mono framework, but I cannot find the corresponding error report, and I cannot determine why this happens only in some cases, but not in others! Has anyone else had a similar experience?

EDIT: I changed some of the statements to match what was in the rejection request if there is a problem with reserved words or similar. Note that the query that I issue in the second case does indeed query four columns and apparently only returns two very strange (0 | New).

Answer 1

Ok, I was able to reproduce your problem. You have a thread problem. This was my only idea what could be causing this problem, and I was able to reproduce it.

To fix this, you need to do the following:

• Make sure every reader has a reader.Close () request after parsing the data.

• To make thread safe calls, use the following code:

   Object executeLock = new Object(); private IDataReader ExecuteThreadSafe(IDbCommand sqlCommand) { lock (executeLock) { return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); } } 

It seems that mono has a different implementation of SQL objects than .NET. It is true that I could not play it in Windows!

Answer 2

I would start by trying to figure out exactly where the difference lies.

First, you have the following code:

 objectToLoad.Property1 = reader.GetInt32(row.GetOrdinal("column1")); objectToLoad.Property2 = reader.GetString(row.GetOrdinal("column2")); objectToLoad.Property3 = reader.GetString(row.GetOrdinal("column ")); //Is the space on the end intended? 

I believe you said that the first two lines work, and the third line explodes. First we need to find out where it explodes. I would try:

 int test = row.GetOrdinal("column "); 

Does test match a valid column index? If not, then your problem. Make sure the exact column name, try different shells, etc. If the index is valid, try:

 object foo = reader[test]; Console.WriteLine(foo.GetType().Name); 

to find out what the data type of this column is. Perhaps the problem is casting.

If this fails, I will try to load the reader into the DataSet so that you can examine the exact schema more closely.

If you find a difference in behavior between Mono and the .NET Framework, the Mono team usually really wants to fix them. I highly recommend writing this as an error.

Hope this helps!

Answer 3

Mono is not .NET, and there are many differences, especially with earlier versions. The root methodology for connecting to SQL uses the TDS (table data stream) implementation in C #, which for earlier versions of Mono (and TDS as a result) can cause many problems. Almost all of the core classes for SQL and data have differences that can potentially throw exceptions. It might be worth a try Mono 2.10+, because the Mono team is constantly improving the whole project.

Answer 4

Try accessing your reader this way:

 reader["column1isastring"].ToString(); (Int32)reader["column2isInt32"]; 

Also, as an extra note, make sure that you use the use directive for disposable objects. I'm not sure Mono implements this, but it's worth it. What the using directive does is clear disposable objects as soon as you use them. It is very convenient and makes for clean code. Quick example:

 using (MySqlCommand command = new MySqlCommand("SELECT column1, column2, column FROM tablename", conn)) { try { conn.Open(); using (MySqlDataReader reader = command.ExecuteReader()) { reader.Read(); var someString = reader["column1isastring"].ToString(); var whatever = (Int32)reader["column2isInt32"]; } //reader closes and disposes here } catch (Exception ex) { //log this exception } finally { conn.Close(); } } //conn gets closed and disposed of here 

In addition, if you get user input that goes directly to your commands, make sure you use the MySqlParameter class to, for example, drop malicious parameters from the list.

Answer 5

I am using Mono 4.0 and I have a similar problem. My research shows that the most likely cause of this problem is the incorrect implementation of the connection pool in Mono. At least if I turn off the pool, the problem goes away.

To disable the connection pool, you need to add the following line to the connection string: Pooling=false;

After that, the creation of the connection object should look like this:

 var conn = new SqlConnection("Server=localhost; Database=somedb; user=user1; password=qwerty; Pooling=false;");