小弟初学c#和MySQL,现在遇到两个问题望各路大神解惑,一个是我明明开启连接了为什么还会提示connection must be valid and open?
还有一个问题是我的事务创建和启动语句应该放在哪里?如果放在我如下代码区域里,在catch异常时加入rollback会提示上下文不存在trans。
代码如下:
public void connectToMySqlService(DataGridView gridView, string sqlString,MysqlCmd mys**qlcmd)
{
try
{
//创建数据提供者
MySqlDataAdapter DataAdapter = new MySqlDataAdapter();
MySqlCommand cmd = new MySqlCommand();
conn.Open();
//启动事务
/* MySqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
*/
cmd.CommandText = sqlString;
switch (mysqlcmd)
{
case MysqlCmd.select:
{
DataAdapter.SelectCommand = cmd;
int n = DataAdapter.SelectCommand.ExecuteNonQuery();
MessageBox.Show("有条 " + n.ToString() + " 记录被查询!");
//填充数据集dataset,并为本次填充的数据起名“student_table”
DataAdapter.Fill(dataset, "student_table");
gridView.DataSource = dataset;
//在dataGridView1控件中显示名为student_table的填充数据
gridView.DataMember = "student_table";
break;
}
case MysqlCmd.insert:
{
DataAdapter.InsertCommand = cmd;
int n = DataAdapter.InsertCommand.ExecuteNonQuery();
MessageBox.Show("有条 " + n.ToString() + " 记录被插入!");
mysqlcmd = 0;
return;
}
case MysqlCmd.delete:
{
DataAdapter.DeleteCommand = cmd;
int n = DataAdapter.DeleteCommand.ExecuteNonQuery();
MessageBox.Show("有条 " + n.ToString() + " 记录被删除!");
mysqlcmd = 0;
return;
}
case MysqlCmd.update:
{
DataAdapter.UpdateCommand = cmd;
int n = DataAdapter.UpdateCommand.ExecuteNonQuery();
MessageBox.Show("有条 " + n.ToString() + " 记录被更新!");
mysqlcmd = 0;
return;
}
default:
break;
}
// trans.Commit();
}
catch (Exception ex)
{
// trans.Rollback();
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
dataset.Dispose();
MessageBox.Show("关闭连接");
}
}
}