用SqlDataAdapter的UPdate来实现更新给你一段VB调用的实现代码,我是使用存储过程来实现更新的
Public Function UpdateProgram(ByRef refProgramDS As DataSet) As Boolean
Dim daSql As SqlDataAdapter
Dim sqlCn As SqlConnection
Dim parCurr As SqlParameter
Dim bResult As Boolean = True
Dim rowCurr As DataRow daSql = New SqlDataAdapter
sqlCn = New SqlConnection(SysConfig.ConnectionString()) 'process program main table
daSql.UpdateCommand = New SqlCommand
With daSql.UpdateCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramMainUpdate"
.Connection = sqlCn
.Parameters.Add("@PROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
.Parameters.Add("@PROGRAM_CN_NAME", SqlDbType.NVarChar, 255, "PROGRAM_CN_NAME")
.Parameters.Add("@PROGRAM_AUTHOR", SqlDbType.NVarChar, 255, "PROGRAM_AUTHOR")
.Parameters.Add("@PROGRAM_NATIONNALITY", SqlDbType.NVarChar, 255, "PROGRAM_NATIONNALITY")
.Parameters.Add("@PROGRAM_VALID", SqlDbType.NChar, 1, "PROGRAM_VALID")
.Parameters.Add("@PROGRAM_PRICE_LIMIT", SqlDbType.Decimal, 12, "PROGRAM_PRICE_LIMIT")
.Parameters.Add("@PROGRAM_FREE_TIME", SqlDbType.Int, 4, "PROGRAM_FREE_TIME")
.Parameters.Add("@TIME_TYPE", SqlDbType.NChar, 10, "TIME_TYPE")
.Parameters.Add("@PROGRAM_REMARK", SqlDbType.NVarChar, 255, "PROGRAM_REMARK")
parCurr = .Parameters.Add("@oldPROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
parCurr.SourceVersion = DataRowVersion.Original
End With
daSql.InsertCommand = New SqlCommand
With daSql.InsertCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramMainInsert"
.Connection = sqlCn
.Parameters.Add("@PROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
.Parameters.Add("@PROGRAM_CN_NAME", SqlDbType.NVarChar, 255, "PROGRAM_CN_NAME")
.Parameters.Add("@PROGRAM_AUTHOR", SqlDbType.NVarChar, 255, "PROGRAM_AUTHOR")
.Parameters.Add("@PROGRAM_NATIONNALITY", SqlDbType.NVarChar, 255, "PROGRAM_NATIONNALITY")
.Parameters.Add("@PROGRAM_VALID", SqlDbType.NChar, 1, "PROGRAM_VALID")
.Parameters.Add("@PROGRAM_PRICE_LIMIT", SqlDbType.Decimal, 12, "PROGRAM_PRICE_LIMIT")
.Parameters.Add("@PROGRAM_FREE_TIME", SqlDbType.Int, 4, "PROGRAM_FREE_TIME")
.Parameters.Add("@TIME_TYPE", SqlDbType.NChar, 10, "TIME_TYPE")
.Parameters.Add("@PROGRAM_REMARK", SqlDbType.NVarChar, 255, "PROGRAM_REMARK")
End With daSql.DeleteCommand = New SqlCommand
With daSql.DeleteCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramDelete"
.Connection = sqlCn
parCurr = .Parameters.Add("@oldPROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
parCurr.SourceVersion = DataRowVersion.Original
End With Try
daSql.Update(refProgramDS.Tables(0))
Catch
MsgBox("节目更新报错:" + Err.Description)
Return False
End Try
For Each rowCurr In refProgramDS.Tables(0).Rows
If (rowCurr.HasErrors) Then
bResult = False
Console.WriteLine(rowCurr.Item(0).ToString + " " + rowCurr.RowError)
End If
Next rowCurr 'process program price table
daSql.UpdateCommand = New SqlCommand
With daSql.UpdateCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramPriceUpdate"
.Connection = sqlCn
.Parameters.Add("@PROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
.Parameters.Add("@CLIENT_TYPE", SqlDbType.NChar, 10, "CLIENT_TYPE")
.Parameters.Add("@UNIT_TIME_TYPE", SqlDbType.NChar, 4, "UNIT_TIME_TYPE")
.Parameters.Add("@UNIT_PRICES", SqlDbType.Decimal, 12, "UNIT_PRICES")
.Parameters.Add("@LIMIT_PRICE", SqlDbType.Decimal, 12, "LIMIT_PRICE")
.Parameters.Add("@LIMIT_COUNT", SqlDbType.Decimal, 12, "LIMIT_COUNT")
.Parameters.Add("@PRICE_ON_DATE", SqlDbType.DateTime, 1, "PRICE_ON_DATE")
parCurr = .Parameters.Add("@oldPROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
parCurr.SourceVersion = DataRowVersion.Current
parCurr = .Parameters.Add("@oldCLIENT_TYPE", SqlDbType.NVarChar, 255, "CLIENT_TYPE")
parCurr.SourceVersion = DataRowVersion.Original
End With daSql.InsertCommand = New SqlCommand()
With daSql.InsertCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramPriceInsert"
.Connection = sqlCn
.Parameters.Add("@PROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
.Parameters.Add("@CLIENT_TYPE", SqlDbType.NChar, 10, "CLIENT_TYPE")
.Parameters.Add("@UNIT_TIME_TYPE", SqlDbType.NChar, 4, "UNIT_TIME_TYPE")
.Parameters.Add("@UNIT_PRICES", SqlDbType.Decimal, 12, "UNIT_PRICES")
.Parameters.Add("@LIMIT_PRICE", SqlDbType.Decimal, 12, "LIMIT_PRICE")
.Parameters.Add("@LIMIT_COUNT", SqlDbType.Decimal, 12, "LIMIT_COUNT")
.Parameters.Add("@PRICE_ON_DATE", SqlDbType.DateTime, 1, "PRICE_ON_DATE")
End With daSql.DeleteCommand = New SqlCommand()
With daSql.DeleteCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramPriceDelete"
.Connection = sqlCn
.Parameters.Add("@oldPROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
.Parameters.Add("@oldCLIENTTYPE", SqlDbType.NChar, 10, "CLIENT_TYPE")
End With
' daSql.DeleteCommand = Nothing
Try
daSql.Update(refProgramDS.Tables(1))
Catch
MsgBox("更新报错:" + Err.Description)
Return False
End Try
For Each rowCurr In refProgramDS.Tables(0).Rows
If (rowCurr.HasErrors) Then
bResult = False
Console.WriteLine(rowCurr.Item(0).ToString + " " + rowCurr.RowError)
End If
Next rowCurr Return bResult
End Function
Public Function UpdateProgram(ByRef refProgramDS As DataSet) As Boolean
Dim daSql As SqlDataAdapter
Dim sqlCn As SqlConnection
Dim parCurr As SqlParameter
Dim bResult As Boolean = True
Dim rowCurr As DataRow daSql = New SqlDataAdapter
sqlCn = New SqlConnection(SysConfig.ConnectionString()) 'process program main table
daSql.UpdateCommand = New SqlCommand
With daSql.UpdateCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramMainUpdate"
.Connection = sqlCn
.Parameters.Add("@PROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
.Parameters.Add("@PROGRAM_CN_NAME", SqlDbType.NVarChar, 255, "PROGRAM_CN_NAME")
.Parameters.Add("@PROGRAM_AUTHOR", SqlDbType.NVarChar, 255, "PROGRAM_AUTHOR")
.Parameters.Add("@PROGRAM_NATIONNALITY", SqlDbType.NVarChar, 255, "PROGRAM_NATIONNALITY")
.Parameters.Add("@PROGRAM_VALID", SqlDbType.NChar, 1, "PROGRAM_VALID")
.Parameters.Add("@PROGRAM_PRICE_LIMIT", SqlDbType.Decimal, 12, "PROGRAM_PRICE_LIMIT")
.Parameters.Add("@PROGRAM_FREE_TIME", SqlDbType.Int, 4, "PROGRAM_FREE_TIME")
.Parameters.Add("@TIME_TYPE", SqlDbType.NChar, 10, "TIME_TYPE")
.Parameters.Add("@PROGRAM_REMARK", SqlDbType.NVarChar, 255, "PROGRAM_REMARK")
parCurr = .Parameters.Add("@oldPROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
parCurr.SourceVersion = DataRowVersion.Original
End With
daSql.InsertCommand = New SqlCommand
With daSql.InsertCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramMainInsert"
.Connection = sqlCn
.Parameters.Add("@PROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
.Parameters.Add("@PROGRAM_CN_NAME", SqlDbType.NVarChar, 255, "PROGRAM_CN_NAME")
.Parameters.Add("@PROGRAM_AUTHOR", SqlDbType.NVarChar, 255, "PROGRAM_AUTHOR")
.Parameters.Add("@PROGRAM_NATIONNALITY", SqlDbType.NVarChar, 255, "PROGRAM_NATIONNALITY")
.Parameters.Add("@PROGRAM_VALID", SqlDbType.NChar, 1, "PROGRAM_VALID")
.Parameters.Add("@PROGRAM_PRICE_LIMIT", SqlDbType.Decimal, 12, "PROGRAM_PRICE_LIMIT")
.Parameters.Add("@PROGRAM_FREE_TIME", SqlDbType.Int, 4, "PROGRAM_FREE_TIME")
.Parameters.Add("@TIME_TYPE", SqlDbType.NChar, 10, "TIME_TYPE")
.Parameters.Add("@PROGRAM_REMARK", SqlDbType.NVarChar, 255, "PROGRAM_REMARK")
End With daSql.DeleteCommand = New SqlCommand
With daSql.DeleteCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramDelete"
.Connection = sqlCn
parCurr = .Parameters.Add("@oldPROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
parCurr.SourceVersion = DataRowVersion.Original
End With Try
daSql.Update(refProgramDS.Tables(0))
Catch
MsgBox("节目更新报错:" + Err.Description)
Return False
End Try
For Each rowCurr In refProgramDS.Tables(0).Rows
If (rowCurr.HasErrors) Then
bResult = False
Console.WriteLine(rowCurr.Item(0).ToString + " " + rowCurr.RowError)
End If
Next rowCurr 'process program price table
daSql.UpdateCommand = New SqlCommand
With daSql.UpdateCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramPriceUpdate"
.Connection = sqlCn
.Parameters.Add("@PROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
.Parameters.Add("@CLIENT_TYPE", SqlDbType.NChar, 10, "CLIENT_TYPE")
.Parameters.Add("@UNIT_TIME_TYPE", SqlDbType.NChar, 4, "UNIT_TIME_TYPE")
.Parameters.Add("@UNIT_PRICES", SqlDbType.Decimal, 12, "UNIT_PRICES")
.Parameters.Add("@LIMIT_PRICE", SqlDbType.Decimal, 12, "LIMIT_PRICE")
.Parameters.Add("@LIMIT_COUNT", SqlDbType.Decimal, 12, "LIMIT_COUNT")
.Parameters.Add("@PRICE_ON_DATE", SqlDbType.DateTime, 1, "PRICE_ON_DATE")
parCurr = .Parameters.Add("@oldPROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
parCurr.SourceVersion = DataRowVersion.Current
parCurr = .Parameters.Add("@oldCLIENT_TYPE", SqlDbType.NVarChar, 255, "CLIENT_TYPE")
parCurr.SourceVersion = DataRowVersion.Original
End With daSql.InsertCommand = New SqlCommand()
With daSql.InsertCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramPriceInsert"
.Connection = sqlCn
.Parameters.Add("@PROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
.Parameters.Add("@CLIENT_TYPE", SqlDbType.NChar, 10, "CLIENT_TYPE")
.Parameters.Add("@UNIT_TIME_TYPE", SqlDbType.NChar, 4, "UNIT_TIME_TYPE")
.Parameters.Add("@UNIT_PRICES", SqlDbType.Decimal, 12, "UNIT_PRICES")
.Parameters.Add("@LIMIT_PRICE", SqlDbType.Decimal, 12, "LIMIT_PRICE")
.Parameters.Add("@LIMIT_COUNT", SqlDbType.Decimal, 12, "LIMIT_COUNT")
.Parameters.Add("@PRICE_ON_DATE", SqlDbType.DateTime, 1, "PRICE_ON_DATE")
End With daSql.DeleteCommand = New SqlCommand()
With daSql.DeleteCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "ProgramPriceDelete"
.Connection = sqlCn
.Parameters.Add("@oldPROGRAM_NAME", SqlDbType.NVarChar, 255, "PROGRAM_NAME")
.Parameters.Add("@oldCLIENTTYPE", SqlDbType.NChar, 10, "CLIENT_TYPE")
End With
' daSql.DeleteCommand = Nothing
Try
daSql.Update(refProgramDS.Tables(1))
Catch
MsgBox("更新报错:" + Err.Description)
Return False
End Try
For Each rowCurr In refProgramDS.Tables(0).Rows
If (rowCurr.HasErrors) Then
bResult = False
Console.WriteLine(rowCurr.Item(0).ToString + " " + rowCurr.RowError)
End If
Next rowCurr Return bResult
End Function
public DataSet CreateCmdsAndUpdate(DataSet myDataSet,string myConnection,string mySelectQuery,string myTableName)
{
OleDbConnection myConn = new OleDbConnection(myConnection);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
myDataAdapter.SelectCommand = new OleDbCommand(mySelectQuery, myConn);
OleDbCommandBuilder custCB = new OleDbCommandBuilder(myDataAdapter); myConn.Open(); DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS); //code to modify data in dataset here //Insert new records from DataSet
DataRow[] myDataRowArray = custDS.Tables[0].Select(null, null, DataViewRowState.Added);
myDataAdapter.Update(myDataRowArray); myConn.Close(); return custDS;
}
如果你的DataSet是由一个.xsd生成的话,那就应该用存储过程(对每条新加入的行调用一次存储过程)。