我有以下代码,我基本上是从mysql提取数据并在SQL Server中查询。
SQL Server部分工作正常,但是我无法解决如何保持mysql连接打开以进行更新的问题。当它在mysql的foreach循环中执行读取器时,它会出现错误:
An unhandled exception of type 'System.InvalidOperationException' occurred in MySql.Data.dll
Additional information: Connection must be valid and open.
代码:
DB db = new DB();
String sConfig_hostname =
String sConfig_dbname =
String sConfig_dbusername =
String sConfig_dbpassword =
string MyConString = "SERVER=" + sConfig_hostname + ";" +
"DATABASE=" + sConfig_dbname + ";" +
"UID=" + sConfig_dbusername + ";" +
"PASSWORD=" + sConfig_dbpassword + ";Allow Zero Datetime=true;";
MySqlConnection connection = new MySqlConnection(MyConString);
string sQuery="Select * from inbox where Transferred = 0";
MySqlDataAdapter myDA = new MySqlDataAdapter(sQuery, connection);
MySqlCommandBuilder cmb=new MySqlCommandBuilder(myDA);
DataTable MyDT = new DataTable();
myDA.Fill(MyDT);
foreach (DataRow row in MyDT.Rows)
{
String SQL = String.Format("Insert into Inbox (Message, Received, Sender) VALUES ('{0}', '{1}', '{2}')", GeneralFunctions.SQLescape(row["TextDecoded"].ToString()), row["ReceivingDateTime"].ToString(), row["SenderNumber"].ToString());
String mySQL = "Update inbox set Transferred = 1 where ID = " + row["ID"].ToString();
db.Update(SQL);
MySqlCommand SQLup = new MySqlCommand(mySQL);
MySqlDataReader reader = SQLup.ExecuteReader();
}
失败的原因是因为您不想在这里执行读取器,而只想执行该语句,并且因为没有使用连接初始化命令:
MySqlConnection connection = new MySqlConnection(MyConString);
connection.Open();
...
MySqlCommand SQLup = new MySqlCommand(mySQL, connection);
SQLup.ExecuteNonQuery();
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句