首先在SQL SERVER数据库新建一个存储过程，名称为：sup_入库提交，代码如下：
CREATE PROCEDURE usp_入库操作
@部品番号 NVARCHAr(30),
@入库数量 decimal(18,0),
@库位号 NVARCHAr(20)
AS
BEGIN
SET NOCOUNT ON;
IF @入库数量<=0
BEGIN
RAISERROR('入库数量必须大于0',16,1);
RETURN;
END
BEGIN TRY
BEGIN TRANSACTION;
IF EXISTS(SELECt 1 FROM 结存 WHERe 部品番号=@部品番号 AND 库位号=@库位号)
BEGIN
UPDATE 结存 SET 结存数量=结存数量+@入库数量 WHERe 部品番号=@部品番号 AND 库位号=@库位号;
END
ELSE
BEGIN
INSERT INTO 结存(部品番号,结存数量,库位号,预用数量) VALUES(@部品番号,@入库数量,@库位号,0);
END
INSERT INTO 历史入库(部品番号,入库数量,库位号,入库日期) VALUES(@部品番号,@入库数量,@库位号,GETDATE());
COMMIT TRANSACTION
SELECT 0 AS 结果代码,'成功' AS 结果消息
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT -1 AS 结果代码,ERROR_MESSAGE() AS 结果消息
END CATCH
END
然后在窗体界面的提交按钮写如下代码：
imports System.Data.SqlClient
Public Class Form1
Private connString As String = "Server=CW.shscth.com.cn;Database=SCTH;Integrated Security=True;"
Private da As SqlDataAdapter
Private dt As DataTable
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Mybase.Load
InitializeDataGrisView()
End Sub
Private Sub InitializeDataGrisView()
DataGridView1.Columns.Clear()
DataGridView1.Columns.Add("部品番号", "部品番号")
DataGridView1.Columns.Add("数量", "数量")
DataGridView1.Columns.Add("库位号", "库位号")
DataGridView1.Columns("部品番号").Width = 150
DataGridView1.Columns("数量").Width = 100
DataGridView1.Columns("库位号").Width = 120
DataGridView1.Columns("数量").ValueType = GetType(Decimal)
End Sub
Private Sub Button提交入库_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button提交入库.Click
If Not ValidateData() Then
Return
End If
BulkUpdateDatabase()
End Sub
Private Function BulkUpdateDatabase() As Boolean
Dim conn As SqlConnection = Nothing
Dim transaction As SqlTransaction = Nothing
Try
conn = New SqlConnection(connString)
conn.Open()
transaction = conn.BeginTransaction()
'执行批量更新
Using cmd As New SqlCommand("usp_入库操作", conn, transaction)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@部品番号", SqlDbType.NVarChar, 30)
cmd.Parameters.Add("@入库数量", SqlDbType.Decimal)
cmd.Parameters.Add("@库位号", SqlDbType.NVarChar, 20)
For Each row As DataGridViewRow In DataGridView1.Rows
If row.IsNewRow Then Continue For
'设置参数值
cmd.Parameters("@部品番号").Value = row.Cells("部品番号").Value.ToString().Trim()
cmd.Parameters("@入库数量").Value = CDec(row.Cells("数量").Value)
cmd.Parameters("@库位号").Value = row.Cells("库位号").Value.ToString().Trim()
'执行存储过程
cmd.ExecuteNonQuery()
Next
End Using
'提交事务
transaction.Commit()
MessageBox.Show("提交成功！")
DataGridView1.Rows.Clear()
Return True
Catch ex As Exception
If transaction IsNot Nothing Then
Try
transaction.Rollback()
Catch rollbackEx As Exception
'记录回滚错误
End Try
End If
MessageBox.Show("提交失败:" & ex.Message)
Return False
Finally
If conn IsNot Nothing AndAlso conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
End Function
End Class
