c# - MySQL插入查询异常

Question

大家好，在 MySQL 插入期间遇到错误。

在阅读代码之前请注意...

如果我在 Visual studio 因错误而中断时从 toSend 获取字符串并手动将其作为查询输入，它可以正常工作并插入!

而且这似乎只是有时发生......尽管从专业角度来看它根本不应该发生......

代码

 command.CommandText = @"INSERT INTO offenders (dob, sex, first_name, date_created, hair, eyes, glasses, middle_name, last_name, address_line1, city, height, weight, OffDetail, risk_level, special_condition, offender_id, date_modified, designation, victim_relationship, weapon_used, force_used, nysid, scrape_state, state, zipcode, ethnicity_id, county_id, race_id, isgeocoded, created_by_user_id, computer_used, porn_involved, approved_for_web) VALUES (?dob, ?sex, ?first_name, ?date_created, ?hair, ?eyes, ?glasses, ?middle_name, ?last_name, ?address_line1, ?city, ?height, ?weight, ?OffDetail, ?risk_level, ?special_condition, ?offender_id, ?date_modified, ?designation, ?victim_relationship, ?weapon_used, ?force_used, ?nysid, ?scrape_state, ?state, ?zipcode, ?ethnicity_id, ?county_id, ?race_id, ?isgeocoded, ?created_by_user_id, ?computer_used, ?porn_involved, ?approved_for_web)";
                            command.Parameters.Add(new MySqlParameter("?dob", offender.GetParseableDoB() != null ? DateTime.ParseExact(offender.GetParseableDoB(), "MMM-dd-yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd") : ""));
                            command.Parameters.Add(new MySqlParameter("?sex", offender.sex.First()));
                            command.Parameters.Add(new MySqlParameter("?first_name", offender.firstName));
                            command.Parameters.Add(new MySqlParameter("?date_created", DateTime.Now.ToString("yyyy-MM-dd")));
                            command.Parameters.Add(new MySqlParameter("?hair", haircolor));
                            command.Parameters.Add(new MySqlParameter("?eyes", eyecolor));
                            command.Parameters.Add(new MySqlParameter("?glasses", glasses));
                            command.Parameters.Add(new MySqlParameter("?middle_name", offender.middleName));
                            command.Parameters.Add(new MySqlParameter("?last_name", offender.lastName));
                            command.Parameters.Add(new MySqlParameter("?address_line1", splitAddress[0]));
                            command.Parameters.Add(new MySqlParameter("?city", splitAddress[1]));
                            command.Parameters.Add(new MySqlParameter("?height", offender.height.Replace("\'", "ft").Replace("\"", "in")));
                            command.Parameters.Add(new MySqlParameter("?weight", offender.weight));
                            command.Parameters.Add(new MySqlParameter("?OffDetail", offender.currentPlacement));
                            command.Parameters.Add(new MySqlParameter("?risk_level", offender.riskLevel));
                            command.Parameters.Add(new MySqlParameter("?special_condition", offender.conditions));
                            command.Parameters.Add(new MySqlParameter("?offender_id", "NY" + offender.offenderId));
                            command.Parameters.Add(new MySqlParameter("?date_modified", DateTime.Now.ToString("yyyy-MM-dd")));
                            command.Parameters.Add(new MySqlParameter("?designation", offender.designation));
                            command.Parameters.Add(new MySqlParameter("?victim_relationship", offender.currentConviction.relationship));
                            command.Parameters.Add(new MySqlParameter("?weapon_used", offender.currentConviction.weaponsUsed));
                            command.Parameters.Add(new MySqlParameter("?force_used", offender.currentConviction.forceUsed));
                            command.Parameters.Add(new MySqlParameter("?nysid", offender.offenderId));
                            command.Parameters.Add(new MySqlParameter("?scrape_state", "36"));
                            command.Parameters.Add(new MySqlParameter("?state", offender.designation));
                            command.Parameters.Add(new MySqlParameter("?zipcode", splitAddress[2]));
                            command.Parameters.Add(new MySqlParameter("?ethnicity_id", ethnicityid));
                            command.Parameters.Add(new MySqlParameter("?county_id", 360000 + offender.countyid));
                            command.Parameters.Add(new MySqlParameter("?race_id", raceid));
                            command.Parameters.Add(new MySqlParameter("?isgeocoded", (offender.address[0].geoX != null && !offender.address[0].geoX.Equals("")) ? 1 : 0));
                            command.Parameters.Add(new MySqlParameter("?created_by_user_id", 5));
                            command.Parameters.Add(new MySqlParameter("?computer_used", offender.currentConviction.computerUsed.Contains("No") ? 0 : 1));
                            command.Parameters.Add(new MySqlParameter("?porn_involved", offender.currentConviction.pornInvolved.Contains("No") ? 0 : 1));
                            command.Parameters.Add(new MySqlParameter("?approved_for_web", 1));
                            command.ExecuteNonQuery();

错误

     A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>PFML_NY_MySQLUpdater.vshost.exe</AppDomain><Exception><ExceptionType>MySql.Data.MySqlClient.MySqlException, MySql.Data, Version=6.9.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d</ExceptionType><Message>Fatal error encountered during command execution.</Message><StackTrace>   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at PFML_NY_MySQLUpdater.MySqlUtils.InsertOffenderAddresses(Offender offender)
   at PFML_NY_MySQLUpdater.Program.Main(String[] args)
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. ---&amp;gt; MySql.Data.MySqlClient.MySqlException: Fatal error encountered attempting to read the resultset. ---&amp;gt; MySql.Data.MySqlClient.MySqlException: Reading from the stream has failed. ---&amp;gt; System.IO.IOException: Unable to read data from the transport connection: A non-blocking socket operation could not be completed immediately. ---&amp;gt; System.Net.Sockets.SocketException: A non-blocking socket operation could not be completed immediately
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   --- End of inner exception stack trace ---
   at MySql.Data.Common.MyNetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32&amp;amp; affectedRow, Int64&amp;amp; insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32&amp;amp; affectedRows, Int64&amp;amp; insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at PFML_NY_MySQLUpdater.MySqlUtils.InsertOffenderAddresses(Offender offender)
   at PFML_NY_MySQLUpdater.Program.Main(String[] args)
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</ExceptionString><InnerException><ExceptionType>MySql.Data.MySqlClient.MySqlException, MySql.Data, Version=6.9.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d</ExceptionType><Message>Fatal error encountered attempting to read the resultset.</Message><StackTrace>   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)</StackTrace><ExceptionString>MySql.Data.MySqlClient.MySqlException: Fatal error encountered attempting to read the resultset. ---&amp;gt; MySql.Data.MySqlClient.MySqlException: Reading from the stream has failed. ---&amp;gt; System.IO.IOException: Unable to read data from the transport connection: A non-blocking socket operation could not be completed immediately. ---&amp;gt; System.Net.Sockets.SocketException: A non-blocking socket operation could not be completed immediately
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   --- End of inner exception stack trace ---
   at MySql.Data.Common.MyNetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32&amp;amp; affectedRow, Int64&amp;amp; insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32&amp;amp; affectedRows, Int64&amp;amp; insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)</ExceptionString><InnerException><ExceptionType>MySql.Data.MySqlClient.MySqlException, MySql.Data, Version=6.9.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d</ExceptionType><Message>Reading from the stream has failed.</Message><StackTrace>   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32&amp;amp; affectedRow, Int64&amp;amp; insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32&amp;amp; affectedRows, Int64&amp;amp; insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()</StackTrace><ExceptionString>MySql.Data.MySqlClient.MySqlException: Reading from the stream has failed. ---&amp;gt; System.IO.IOException: Unable to read data from the transport connection: A non-blocking socket operation could not be completed immediately. ---&amp;gt; System.Net.Sockets.SocketException: A non-blocking socket operation could not be completed immediately
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   --- End of inner exception stack trace ---
   at MySql.Data.Common.MyNetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32&amp;amp; affectedRow, Int64&amp;amp; insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32&amp;amp; affectedRows, Int64&amp;amp; insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()</ExceptionString><InnerException><ExceptionType>System.IO.IOException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Unable to read data from the transport connection: A non-blocking socket operation could not be completed immediately.</Message><StackTrace>   at MySql.Data.Common.MyNetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()</StackTrace><ExceptionString>System.IO.IOException: Unable to read data from the transport connection: A non-blocking socket operation could not be completed immediately. ---&amp;gt; System.Net.Sockets.SocketException: A non-blocking socket operation could not be completed immediately
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   --- End of inner exception stack trace ---
   at MySql.Data.Common.MyNetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()</ExceptionString><InnerException><ExceptionType>System.Net.Sockets.SocketException, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>A non-blocking socket operation could not be completed immediately</Message><StackTrace>   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)</StackTrace><ExceptionString>System.Net.Sockets.SocketException: A non-blocking socket operation could not be completed immediately
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)</ExceptionString><NativeErrorCode>2733</NativeErrorCode></InnerException></InnerException></InnerException></InnerException></Exception></TraceRecord>

Answer 1

您是否尝试过以下数据？

Offer offender = new Offender {
  firstName  = "Samuel",
  middleName = null,
  lastName   = "D'Souza",
}

目前，代码存在严重安全问题，您应该使用参数，否则组织外部的人员可能会导致整个数据库被盗或修改。如果您有这样的代码，则需要提出此问题并审查和替换每条 SQL 语句。

要了解如何在查询中使用参数，请使用此 link .

我已经发布了一个片段供您开始使用

// Define the MySQL command to get the product having the ID = 100...
MySqlCommand command = new MySqlCommand();
command.CommandText =@"
  INSERT INTO offenders (dob, sex, first_name, date_created, hair, eyes, ...) 
  VALUES (?DOB, ?Sex, ?FirstName, ?DateCreated, ?Hair, ?Eyes, ...)";
command.Parameters.Add(new MySqlParameter("?DOB", offender.dob));   
command.Parameters.Add(new MySqlParameter("?Sex", offender.sex));  
... 
command.ExecuteNonQuery();