vb 事务sample

 1 Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click
 2         If MsgBox("Do you really want to save the table record(s)?", MsgBoxStyle.Information + MsgBoxStyle.YesNo, "CDSystem") = MsgBoxResult.No Then
 3             Exit Sub
 4         End If
 5         Dim trans As SqlTransaction = SqlConnect.BeginTransaction
 6         Try
 7             Dim sqlcmd As New SqlClient.SqlCommand
 8             Dim sqlcmd_delete As New SqlClient.SqlCommand
 9             '----------------------先刪除---------------------
10             With sqlcmd_delete
11                 .Connection = SqlConnect
12                 .Transaction = trans
13                 .CommandType = CommandType.Text
14                 .CommandText = "delete from cd_featureopns where  featcode='" & Me.txtFeatureCode.Text.ToString.Replace("'", "''") & "'"
15                 .Parameters.Clear()
16                 .ExecuteNonQuery()
17                 .Dispose()
18             End With
19             '-------------------------------------------------
20             If datatable.Rows.Count >= 0 Then
21                 '----------------------再重新插入-----------------
22                 For Each R As DataRow In datatable.Rows
23                     With sqlcmd
24                         .Connection = SqlConnect
25                         .Transaction = trans
26                         .CommandType = CommandType.StoredProcedure
27                         .CommandText = "Pro_CD_FeatureOpns_Save"
28                         With .Parameters
29                             .Clear()
30                             .Add("@opnno", SqlDbType.Int).Value = R.Item("opnno")
31                             .Add("@featcode", SqlDbType.VarChar, 12).Value = Me.txtFeatureCode.Text
32                             .Add("@dopncode", SqlDbType.VarChar, 12).Value = R.Item("dopncode")
33                             .Add("@machcode", SqlDbType.VarChar, 10).Value = R.Item("machcode")
34                             .Add("@stitinch", SqlDbType.Decimal, 18, 2).Value = R.Item("stitinch")
35                             .Add("@stitgaug", SqlDbType.Decimal, 18, 2).Value = R.Item("stitgaug")
36                             .Add("@needle", SqlDbType.Decimal, 18, 2).Value = R.Item("needle")
37                             .Add("@toploop", SqlDbType.Decimal, 18, 2).Value = R.Item("toploop")
38                             .Add("@undloop", SqlDbType.Decimal, 18, 2).Value = R.Item("undloop")
39                             .Add("@bobbin", SqlDbType.Decimal, 18, 2).Value = R.Item("bobbin")
40                             .Add("@user", SqlDbType.VarChar, 10).Value = g.gUserId
41                             .Add("@Tpye", SqlDbType.Int).Value = -1
42                             .Add("@error", SqlDbType.VarChar, 50).Value = "save fail,Contact the Administrator"
43                         End With
44                         .Parameters("@Tpye").Direction = ParameterDirection.Output
45                         .Parameters("@error").Direction = ParameterDirection.Output
46                         .ExecuteScalar()
47                         If .Parameters.Item("@Tpye").Value.ToString <> "0" Then
48                             trans.Rollback()
49                             MsgBox(.Parameters.Item("@error").Value.ToString)
50                             Exit Sub
51                         End If
52                         .Dispose()
53                     End With
54                 Next
55                 '-------------------------------------------------
56             End If
57 
58             trans.Commit()
59             getLastUpdate()
60             MsgBox("Save Completed", MsgBoxStyle.Information, CD_SYSTEM_NAME)
61         Catch ex As Exception
62             trans.Rollback()
63             MsgBox(ex.Message.Trim, MsgBoxStyle.Information, CD_SYSTEM_NAME)
64             Exit Sub
65         End Try
66     End Sub
67 
68 Private Sub getLastUpdate()
69         Try
70             Dim str As String = "select t1.ledtuser,t1.ledtdate from cd_featureopns t1 where t1.featcode='" + txtFeatureCode.Text.ToString().Replace("'", "''") + "'"
71             Dim tb As DataTable = gData.GetDataTable(str, SqlConnect)
72             txtUser.Text = tb.Rows(0).Item("ledtuser")
73             txtDate.Text = Format(tb.Rows(0).Item("ledtdate"), "yyyy-MM-dd HH:mm:ss")
74         Catch ex As Exception
75         End Try
76     End Sub

 1 set ANSI_NULLS ON
 2 set QUOTED_IDENTIFIER ON
 3 go
 4 
 5 
 6 
 7 
 8 
 9 
10 
11 
12 
13 ALTER    Proc [bogart].[Pro_CD_FeatureOpns_Save]
14 
15 @opnno        int,
16 @featcode    varchar(12),
17 @dopncode   varchar(12),
18 @machcode   varchar(10),
19 @stitinch   decimal(18,2),
20 @stitgaug    decimal(18,2),
21 @needle        decimal(18,2),
22 @toploop    decimal(18,2),
23 @undloop    decimal(18,2),
24 @bobbin        decimal(18,2),
25 @user        varchar(10),
26 @Tpye        int output,
27 @error        varchar(50) output
28 as 
29 
30 
31 begin
32 --table  CD_Component
33    
34 
35    begin
36       Insert into cd_featureopns(opnno,featcode,dopncode,machcode,stitinch,stitgaug,needle,toploop,undloop,bobbin,ledtuser,ledtdate)
37       Values(@opnno,@featcode,@dopncode,@machcode,@stitinch,@stitgaug,@needle,@toploop,@undloop,@bobbin,@user,getdate())
38         If @@Error<>0 
39              Begin
40                  set @Tpye=-1
41                  set @error='Insert CD_FeatureOpns Fail !'
42                  Return
43              End     
44    end 
45 
46 
47 set @Tpye=0
48 set @error='susseful'
49 
50 end

posted on 2013-04-17 10:59 vinsonLu 阅读(773) 评论(0) 编辑收藏举报