vb 事务sample

Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click
        If MsgBox("Do you really want to save the table record(s)?", MsgBoxStyle.Information + MsgBoxStyle.YesNo, "CDSystem") = MsgBoxResult.No Then
            Exit Sub
        End If
        Dim trans As SqlTransaction = SqlConnect.BeginTransaction
        Try
            Dim sqlcmd As New SqlClient.SqlCommand
            Dim sqlcmd_delete As New SqlClient.SqlCommand
            '----------------------先刪除---------------------
            With sqlcmd_delete
                .Connection = SqlConnect
                .Transaction = trans
                .CommandType = CommandType.Text
                .CommandText = "delete from cd_featureopns where  featcode='" & Me.txtFeatureCode.Text.ToString.Replace("'", "''") & "'"
                .Parameters.Clear()
                .ExecuteNonQuery()
                .Dispose()
            End With
            '-------------------------------------------------
            If datatable.Rows.Count >= 0 Then
                '----------------------再重新插入-----------------
                For Each R As DataRow In datatable.Rows
                    With sqlcmd
                        .Connection = SqlConnect
                        .Transaction = trans
                        .CommandType = CommandType.StoredProcedure
                        .CommandText = "Pro_CD_FeatureOpns_Save"
                        With .Parameters
                            .Clear()
                            .Add("@opnno", SqlDbType.Int).Value = R.Item("opnno")
                            .Add("@featcode", SqlDbType.VarChar, 12).Value = Me.txtFeatureCode.Text
                            .Add("@dopncode", SqlDbType.VarChar, 12).Value = R.Item("dopncode")
                            .Add("@machcode", SqlDbType.VarChar, 10).Value = R.Item("machcode")
                            .Add("@stitinch", SqlDbType.Decimal, 18, 2).Value = R.Item("stitinch")
                            .Add("@stitgaug", SqlDbType.Decimal, 18, 2).Value = R.Item("stitgaug")
                            .Add("@needle", SqlDbType.Decimal, 18, 2).Value = R.Item("needle")
                            .Add("@toploop", SqlDbType.Decimal, 18, 2).Value = R.Item("toploop")
                            .Add("@undloop", SqlDbType.Decimal, 18, 2).Value = R.Item("undloop")
                            .Add("@bobbin", SqlDbType.Decimal, 18, 2).Value = R.Item("bobbin")
                            .Add("@user", SqlDbType.VarChar, 10).Value = g.gUserId
                            .Add("@Tpye", SqlDbType.Int).Value = -1
                            .Add("@error", SqlDbType.VarChar, 50).Value = "save fail,Contact the Administrator"
                        End With
                        .Parameters("@Tpye").Direction = ParameterDirection.Output
                        .Parameters("@error").Direction = ParameterDirection.Output
                        .ExecuteScalar()
                        If .Parameters.Item("@Tpye").Value.ToString <> "0" Then
                            trans.Rollback()
                            MsgBox(.Parameters.Item("@error").Value.ToString)
                            Exit Sub
                        End If
                        .Dispose()
                    End With
                Next
                '-------------------------------------------------
            End If

            trans.Commit()
            getLastUpdate()
            MsgBox("Save Completed", MsgBoxStyle.Information, CD_SYSTEM_NAME)
        Catch ex As Exception
            trans.Rollback()
            MsgBox(ex.Message.Trim, MsgBoxStyle.Information, CD_SYSTEM_NAME)
            Exit Sub
        End Try
    End Sub

Private Sub getLastUpdate()
        Try
            Dim str As String = "select t1.ledtuser,t1.ledtdate from cd_featureopns t1 where t1.featcode='" + txtFeatureCode.Text.ToString().Replace("'", "''") + "'"
            Dim tb As DataTable = gData.GetDataTable(str, SqlConnect)
            txtUser.Text = tb.Rows(0).Item("ledtuser")
            txtDate.Text = Format(tb.Rows(0).Item("ledtdate"), "yyyy-MM-dd HH:mm:ss")
        Catch ex As Exception
        End Try
    End Sub

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go









ALTER    Proc [bogart].[Pro_CD_FeatureOpns_Save]

@opnno        int,
@featcode    varchar(12),
@dopncode   varchar(12),
@machcode   varchar(10),
@stitinch   decimal(18,2),
@stitgaug    decimal(18,2),
@needle        decimal(18,2),
@toploop    decimal(18,2),
@undloop    decimal(18,2),
@bobbin        decimal(18,2),
@user        varchar(10),
@Tpye        int output,
@error        varchar(50) output
as 


begin
--table  CD_Component
   

   begin
      Insert into cd_featureopns(opnno,featcode,dopncode,machcode,stitinch,stitgaug,needle,toploop,undloop,bobbin,ledtuser,ledtdate)
      Values(@opnno,@featcode,@dopncode,@machcode,@stitinch,@stitgaug,@needle,@toploop,@undloop,@bobbin,@user,getdate())
        If @@Error<>0 
             Begin
                 set @Tpye=-1
                 set @error='Insert CD_FeatureOpns Fail !'
                 Return
             End     
   end 


set @Tpye=0
set @error='susseful'

end