C# 结构中的 MySQL 错误 [英] MySQL error in C# structure
本文介绍了C# 结构中的 MySQL 错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在程序上遇到了一些 MySQL 问题.我不专业.
I'm having some problem with MySQL on a program. I'm not professional with it.
错误信息:
"MySql.Data.MySqlClient.MySqlException: 已经有一个与此连接关联的打开的 DataReader，必须先关闭它.在 System.Void MySql.Data.MySqlClient.MySqlCommand.CheckState()在 MySqlDataReader MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(System.Data.CommandBehavior 行为)在 MySqlDataReader MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()"
"MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first. at System.Void MySql.Data.MySqlClient.MySqlCommand.CheckState() at MySqlDataReader MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(System.Data.CommandBehavior behavior) at MySqlDataReader MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()"
代码如下:
public static class MySQL
{
private static string strConnection;
static void dbConnection_StateChange(object sender, StateChangeEventArgs ev)
{
if (ev.CurrentState == ConnectionState.Broken)
{
System.Threading.Thread.Sleep(1000);
dbConnection.Close();
}
if (ev.CurrentState == ConnectionState.Closed)
{
System.Threading.Thread.Sleep(1000);
dbConnection = new MySqlConnection(strConnection);
dbConnection.StateChange += new System.Data.StateChangeEventHandler(dbConnection_StateChange);
System.Threading.Thread.Sleep(1000);
dbConnection.Open();
}
}
private static MySqlConnection dbConnection;
public static bool openConnection(string dbHost, int dbPort, string dbName, string dbUsername, string dbPassword)
{
try
{
strConnection = "server=" + dbHost + ";" + "database=" + dbName + ";" + "uid=" + dbUsername + ";" + "password=" + dbPassword;
dbConnection = new MySqlConnection(strConnection);
dbConnection.StateChange += new System.Data.StateChangeEventHandler(dbConnection_StateChange);
dbConnection.Open();
Console.WriteLine(" Connected to MySQL.");
return true;
}
catch (Exception ex)
{
Console.WriteLine(" Couldn't connect to MySQL! The error:");
Console.WriteLine(ex.Message);
return false;
}
}
public static void closeConnection()
{
try
{
dbConnection.Close();
}
catch
{
Console.WriteLine("tasdsdaa fodaa");
}
}
public static void checkConnection()
{
try
{
if (dbConnection.State != ConnectionState.Open)
{
closeConnection();
openConnection(Config.dbHost, Config.dbPort, Config.dbName, Config.dbUsername, Config.dbPassword);
}
}
catch {
Console.WriteLine("ta fodaa");
}
}
public static void runQuery(string Query)
{
checkConnection();
try { new MySqlCommand(Query, dbConnection).ExecuteScalar(); }
catch { }
}
public static int insertGetLast(string Query)
{
checkConnection();
return int.Parse((new MySqlCommand(Query + "; SELECT LAST_INSERT_ID();", dbConnection).ExecuteScalar()).ToString());
}
public static string runRead(string Query)
{
checkConnection();
try { return new MySqlCommand(Query + " LIMIT 1", dbConnection).ExecuteScalar().ToString(); }
catch
{
return "";
}
}
public static int runRead(string Query, object Tick)
{
checkConnection();
try { return Convert.ToInt32(new MySqlCommand(Query + " LIMIT 1", dbConnection).ExecuteScalar()); }
catch
{
return 0;
}
}
public static string[] runReadColumn(string Query, int maxResults)
{
checkConnection();
MySqlCommand Command = null;
MySqlDataReader Reader = null;
if (maxResults > 0)
Query += " LIMIT " + maxResults;
try
{
Command = dbConnection.CreateCommand();
Command.CommandText = Query;
Reader = Command.ExecuteReader();
if (Reader.HasRows)
{
ArrayList columnBuilder = new ArrayList();
while (Reader.Read())
{
try { columnBuilder.Add(Reader[0].ToString()); }
catch { columnBuilder.Add(""); }
}
return (string[])columnBuilder.ToArray(typeof(string));
}
else
{
return new string[0];
}
}
catch(MySqlException Ex)
{
if (Command != null)
{
Console.WriteLine("[MySQL] Error!\n\r" + Command.CommandText, Ex);
}
else
{
Console.WriteLine("[MySQL] Error!", Ex);
}
}
finally
{
// Close the reader/command if they are active.
if (Reader != null)
{
Reader.Close();
Reader.Dispose();
}
if (Command != null)
{
Command.Dispose();
}
}
return new string[0];
}
public static int[] runReadColumn(string Query, int maxResults, object Tick)
{
checkConnection();
MySqlCommand Command = null;
MySqlDataReader Reader = null;
if (maxResults > 0)
Query += " LIMIT " + maxResults;
try
{
Command = dbConnection.CreateCommand();
Command.CommandText = Query;
Reader = Command.ExecuteReader();
if (Reader.HasRows)
{
ArrayList columnBuilder = new ArrayList();
while (Reader.Read())
{
try { columnBuilder.Add(Reader.GetInt32(0)); }
catch { columnBuilder.Add(0); }
}
return (int[])columnBuilder.ToArray(typeof(int));
}
else
{
return new int[0];
}
}
catch (MySqlException Ex)
{
if (Command != null)
{
Console.WriteLine("[MySQL] Error!\n\r" + Command.CommandText, Ex);
}
else
{
Console.WriteLine("[MySQL] Error!", Ex);
}
}
finally
{
// Close the reader/command if they are active.
if (Reader != null)
{
Reader.Close();
Reader.Dispose();
}
if (Command != null)
{
Command.Dispose();
}
}
return new int[0];
}
public static string[] runReadRow(string Query)
{
checkConnection();
MySqlCommand Command = null;
MySqlDataReader Reader = null;
try
{
Command = dbConnection.CreateCommand();
Command.CommandText = Query + " LIMIT 1";
Reader = Command.ExecuteReader();
if (Reader.HasRows)
{
ArrayList rowBuilder = new ArrayList();
while (Reader.Read())
{
for (int i = 0; i < Reader.FieldCount; i++)
{
try { rowBuilder.Add(Reader[i].ToString()); }
catch { rowBuilder.Add(""); }
}
}
return (string[])rowBuilder.ToArray(typeof(string));
}
else
{
return new string[0];
}
}
catch (MySqlException Ex)
{
if (Command != null)
{
Console.WriteLine("[MySQL] Error!\n\r" + Command.CommandText, Ex);
}
else
{
Console.WriteLine("[MySQL] Error!", Ex);
}
}
finally
{
// Close the reader/command if they are active.
if (Reader != null)
{
Reader.Close();
Reader.Dispose();
}
if (Command != null)
{
Command.Dispose();
}
}
return new string[0];
}
public static int[] runReadRow(string Query, object Tick)
{
checkConnection();
MySqlCommand Command = null;
MySqlDataReader Reader = null;
try
{
Command = dbConnection.CreateCommand();
Command.CommandText = Query + " LIMIT 1";
Reader = Command.ExecuteReader();
if (Reader.HasRows)
{
ArrayList rowBuilder = new ArrayList();
while (Reader.Read())
{
for (int i = 0; i < Reader.FieldCount; i++)
{
try { rowBuilder.Add(Reader.GetInt32(i)); }
catch { rowBuilder.Add(0); }
}
}
return (int[])rowBuilder.ToArray(typeof(int));
}
else
{
return new int[0];
}
}
catch (MySqlException Ex)
{
if (Command != null)
{
Console.WriteLine("[MySQL] Error!\n\r" + Command.CommandText, Ex);
}
else
{
Console.WriteLine("[MySQL] Error!", Ex);
}
}
finally
{
// Close the reader/command if they are active.
if (Reader != null)
{
Reader.Close();
Reader.Dispose();
}
if (Command != null)
{
Command.Dispose();
}
}
return new int[0];
}
public static bool checkExists(string Query)
{
checkConnection();
try { return new MySqlCommand(Query + " LIMIT 1", dbConnection).ExecuteReader().HasRows; }
catch
{
return false;
}
}
public static List<List<string>> readArray(string Query)
{
MySqlCommand Command = null;
MySqlDataReader Reader = null;
try
{
// Create the command.
Command = MySQL.dbConnection.CreateCommand();
Command.CommandText = Query;
// Read the result.
Reader = Command.ExecuteReader();
// Store the incomming fields.
List<List<string>> fieldValues = new List<List<string>>();
// Read all the data.
while (Reader.Read())
{
// Create a new field values to hold the data.
List<string> Buffer = new List<string>();
// Add the field values.
for (int i = 0; i < Reader.FieldCount; i++)
{
Buffer.Add(Reader[i].ToString());
}
// Add it too our overall data.
fieldValues.Add(Buffer);
}
return fieldValues;
}
catch (MySqlException Ex)
{
if (Command != null)
{
Console.WriteLine("[MySQL] Error!\n\r" + Command.CommandText, Ex);
}
else
{
Console.WriteLine("[MySQL] Error!", Ex);
}
return null;
}
finally
{
// Close the reader/command if they are active.
if (Reader != null)
{
Reader.Close();
Reader.Dispose();
}
if (Command != null)
{
Command.Dispose();
}
}
}
public static string Stripslash(string Query)
{
try { return Query.Replace(@"\", "\\").Replace("'", @"\'"); }
catch { return ""; }
}
}
推荐答案
你需要在你的 Reader 对象上调用 Close 来释放下划线资源，就像这样:
You need to call Close on your Reader objects to release the underline resources, something like this:
try
{
Command = dbConnection.CreateCommand();
Command.CommandText = Query;
using (Reader = Command.ExecuteReader()) // 'using' forces a call to `Dispose`/`Close` at the end of the block
{
if (Reader.HasRows)
{
ArrayList columnBuilder = new ArrayList();
while (Reader.Read())
{
try { columnBuilder.Add(Reader.GetInt32(0)); }
catch { columnBuilder.Add(0); }
}
return (int[])columnBuilder.ToArray(typeof(int));
}
else
{
return new int[0];
}
}
}
这篇关于C# 结构中的 MySQL 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
