当前位置: 动力学知识库 > 问答 > 编程问答 >

c# - MySQL Reader closing immediately

问题描述:

I have a method that is recursively populating a list. First it selects the Parent items, then it selects any children it may have using foreign keys. It selects the Parent items fine, but when it starts picking the children, that's when the Reader closes for no reason.

I have followed it through in the debugger, and there is no reason that I can see for it to be closing. The search parameters are valid, the search returns items (I checked it manually), and the Reader is active. Yet for some reason, as soon as it opens, it closes, without any errors.

  • The connection string is valid
  • The Reader is valid
  • The search parameters are valid
  • The command returns results (I tested this one specifically - WHERE 1 = 1 AND SE.ParentSE = 4;"; has over 10 results)

I don't know what's going on?

try

{

using (MySqlConnection connection = new MySqlConnection(GetConnectionString()))

{

connection.Open();

using (MySqlCommand command = connection.CreateCommand())

{

command.CommandText = @"SELECT

SE.SEId,

SE.Name,

SE.Status,

SE.Description,

SE.ParentME AS ParentMEId,

SE.ParentSE AS ParentSEId,

SE.Comments AS Comments,

SE.Removed AS Removed,

SE.SupplierId AS SupplierId,

TC.TerminalCount AS TerminalCount,

SE2.Name AS ParentSE,

ME.EquipmentNumber AS ParentME,

D.DSId AS DataSheet,

D.DSType AS DataSheetType,

C.Code AS EquipmentType,

C.Description AS TypeDescription,

C.CodeType AS TypeDiscipline,

SE.EquipmentType AS EquipTypeId,

SE3.SEId AS SE3Id,

SE4.SEId AS SE4Id,

SE5.SEId AS SE5Id,

SE6.SEId AS SE6Id,

SE2.ParentME AS ME2Id,

SE3.ParentME AS ME3Id,

SE4.ParentME AS ME4Id,

SE5.ParentME AS ME5Id,

SE6.ParentME AS ME6Id,

PAC.UDCId AS AreaCodeId,

PAC.Code AS AreaCode,

PAC.Description AS AreaCodeDescription

FROM SubEquipment SE

LEFT JOIN DataSheet D ON SE.SEId = D.SEId

LEFT JOIN (SELECT SEId, COUNT(*) as TerminalCount FROM Terminal GROUP BY SEId) TC ON SE.SEId = TC.SEId

LEFT JOIN SubEquipment SE2 ON SE.ParentSE = SE2.SEId

LEFT JOIN MajorEquipment ME ON SE.ParentME = ME.MEId

LEFT JOIN UserDefinedCode C ON C.UDCId = SE.EquipmentType

LEFT JOIN SubEquipment SE3 ON SE2.ParentSE = SE3.SEId

LEFT JOIN SubEquipment SE4 ON SE3.ParentSE = SE4.SEId

LEFT JOIN SubEquipment SE5 ON SE4.ParentSE = SE5.SEId

LEFT JOIN SubEquipment SE6 ON SE5.ParentSE = SE6.SEId

INNER JOIN MajorParentage MP ON SE.SEId = MP.SEId

INNER JOIN MajorEquipment MEU ON MP.MEId = MEU.MEId

INNER JOIN UserDefinedCode PAC ON MEU.PACId = PAC.UDCId

LEFT JOIN ComponentStatus CS1 ON CS1.StatusName = SE.Status

WHERE 1 = 1"

// " AND SE.ParentSE = @SEParentId"

// @SEParentId = 4

+ parentSearch

+ ";";

command.Parameters.AddWithValue("@PACId", areaCodeId);

command.Parameters.AddWithValue("@AreaCode", areaCode.Cipher());

command.Parameters.AddWithValue("@ETId", equipmentTypeId);

command.Parameters.AddWithValue("@EquipmentType", equipmentType.Cipher());

command.Parameters.AddWithValue("@EquipmentNumber", equipmentNumber.Cipher());

command.Parameters.AddWithValue("@Description", description.Cipher());

command.Parameters.AddWithValue("@Comments", comments.Cipher());

command.Parameters.AddWithValue("@TypeDiscipline", typeDiscipline);

command.Parameters.AddWithValue("@MEParentId", parentMEId);

command.Parameters.AddWithValue("@MEParent", parentMENumber.Cipher());

command.Parameters.AddWithValue("@SEParentId", parentSEId);

command.Parameters.AddWithValue("@SEParent", parentSENumber.Cipher());

command.Parameters.AddWithValue("@SEId", excludeSEId);

command.Parameters.AddWithValue("@Status", status.Cipher());

using (MySqlDataReader reader = command.ExecuteReader())

{

// Read all the results and populate a model with them.

while (reader.Read())

{

// Do stuff...

}

// Finished reading the results

if (!reader.IsClosed)

reader.Close();

}

}

// Finished with the connection

if (connection.State == System.Data.ConnectionState.Open)

connection.Close();

}

}

catch (MySqlException e)

{

ShowMySQLError(e, 4417);

}

网友答案:

Found it.

This method was being used in multiple places throughout the program; but for some reason, it was only this call that was closing the Reader unexpectedly. On closer inspection - the properties of the reader showed that it had no rows: HasRows = false. Meaning that there was something wrong with the query.

I looked again, and found that the issue was an overlooked parameter (which I trimmed for the sake of the question - I didn't think it was necessary). There were a few default boolean parameters, one was set to true, causing the query to return an empty result.

Moral of the story: Understand what your code is doing!

分享给朋友:
您可能感兴趣的文章:
随机阅读: