datatable更新到mysql_SQL通过Datatable更新数据库表内容

//要注意的一点是在Select语句中要选择的列中必须包含主键的列,此外不支持多表连接查询

DataTable dt = new DataTable();

using (SqlConnection sqlConn = new SqlConnection("********"))

{

//先取出从数据库中取出Datatable

SqlCommand sqlcmd = new SqlCommand("select * from ExecuteReservation", sqlConn);

sqlConn.Open();

SqlDataReader sqlReader = null;

sqlReader = sqlcmd.ExecuteReader();

dt.Load(sqlReader);

//修改一条记录

dt.Rows[0]["VersionInfo"] = "5.0";

//以下三行代码就是把上面更改的数据更新到数据库中

SqlDataAdapter sda = new SqlDataAdapter(sqlcmd);

SqlCommandBuilder sqlcmdB = new SqlCommandBuilder(sda);

sda.Update(dt);

}

//以上如果不使用SQL语句的话，也可以使用存储过程，修改成以下代码即可

SqlCommand sqlcmd = new SqlCommand("对应的存储过程名", sqlConn);

sqlcmd.CommandType = CommandType.StoredProcedure;

//查询出来的DataTable是连接表的集合，更新的时候可以使用UpdateCommand来完成，代码如下

//存储过程如下

ALTER PROCEDURE [dbo].[SelectExe]

@versioninfo nvarchar(50),

@ID int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

update dbo.ExecuteReservation set versioninfo=@versioninfo where ID=@ID

END

————————————————————————————————————————————————————————————————————

SqlCommand newCmd = new SqlCommand("SelectExe", sqlConn);

newCmd.CommandType = CommandType.StoredProcedure;

newCmd.Parameters.Add(new SqlParameter("@VersionInfo", SqlDbType.VarChar));

newCmd.Parameters[0].SourceColumn = "VersionInfo";

newCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));

newCmd.Parameters[1].SourceColumn = "ID";

//修改记录

dt.Rows[0]["VersionInfo"] = "45";

dt.Rows[1]["VersionInfo"] = "56";

SqlDataAdapter sda = new SqlDataAdapter( );

sda.UpdateCommand = newCmd;

//随便新建一个Datatable，将数据更新到指定的表中会需要设置InsertCommand，否则会报“Update requires a valid InsertCommand when passed DataRow collection with new rows.”

sda.InsertCommand = newcmd;

sda.Update(dt);