string connstring = @"Data Source=localhost;Port=3307;Database=test;User ID=root;Password=pass;Charset=utf8";
DataSet dataSet=new DataSet();
MySqlDataAdapter da=new MySqlDataAdapter();
using (MySqlConnection conn=new MySqlConnection(connstring))
{
MySqlCommand selectCommand=new MySqlCommand("select * from dept order by deptno",conn);
da.SelectCommand = selectCommand;
da.Fill(dataSet, "dept");
foreach (var dataRow in dataSet.Tables[0].Select())
{
Console.WriteLine(dataRow["deptno"] + "-" + dataRow["deptname"]);
}
MySqlCommand insertCommand=new MySqlCommand("insert into dept(deptno,deptname) values(@deptno,@deptname)",conn);
MySqlParameter[] insertPara = { new MySqlParameter("@deptno", MySqlDbType.VarChar), new MySqlParameter("@deptname", MySqlDbType.VarChar) };
insertPara[0].SourceColumn = "deptno";
insertPara[1].SourceColumn = "deptname";
foreach (var p in insertPara)
{
insertCommand.Parameters.Add(p);
}
da.InsertCommand = insertCommand;
MySqlCommand delCommand = new MySqlCommand("delete from dept where deptno=@deptno", conn);
MySqlParameter[] delPara = { new MySqlParameter("@deptno", MySqlDbType.VarChar) };
delPara[0].SourceColumn = "deptno";
foreach (var p in delPara)
{
delCommand.Parameters.Add(p);
}
da.DeleteCommand = delCommand;
MySqlCommand updateCommand = new MySqlCommand("update dept set deptno=@deptno,deptname=@deptname where deptno=@deptno", conn);
MySqlParameter[] updatePara = {
// new MySqlParameter("@id", MySqlDbType.Int32),
new MySqlParameter("@deptno", MySqlDbType.VarChar), new MySqlParameter("@deptname", MySqlDbType.VarChar)
};
// updatePara[0].SourceColumn = "id";
updatePara[0].SourceColumn = "deptno";
updatePara[1].SourceColumn = "deptname";
foreach (var p in updatePara)
{
updateCommand.Parameters.Add(p);
}
da.UpdateCommand = updateCommand;
//add
DataRow row = dataSet.Tables[0].NewRow();
row["deptno"] = "1001";
row["deptname"] = "p1";
dataSet.Tables[0].Rows.Add(row);
//del
DataRow[] rowDel = dataSet.Tables[0].Select("deptno in(" + "'10'" + ")");
foreach (var dataRow in rowDel)
{
dataSet.Tables[0].Rows.Remove(dataRow);
}
//update
DataRow[] rowUpdate = dataSet.Tables[0].Select("deptno in(" + "'20'" + ")");
foreach (var dataRow in rowUpdate)
{
dataRow.BeginEdit();
dataRow["deptname"] = "MM";
dataRow.EndEdit();
}
da.Update(dataSet, "dept");
dataSet.AcceptChanges();
Console.WriteLine();
foreach (var dataRow in dataSet.Tables[0].Select())
{
Console.WriteLine(dataRow["deptno"] + "-" + dataRow["deptname"]);
}
}
Console.ReadLine();