USE [test]
GO
/****** Object: Table [dbo].[test] Script Date: 07/20/2017 00:43:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[mycode] [nvarchar](50) NULL,
[myname] [nvarchar](50) NULL,
[mydes] [nvarchar](50) NULL
) ON [PRIMARY]
GO
2、创建用户定义表类型
USE [test]
GO
/****** Object: UserDefinedTableType [dbo].[testtable] Script Date: 07/20/2017 00:44:15 ******/
CREATE TYPE [dbo].[testtable] AS TABLE(
[ID] [int] NULL,
[testcode] [nvarchar](50) NULL,
[testname] [nvarchar](50) NULL,
[testdes] [nvarchar](50) NULL
)
GO
3、创建批量插入存储过程
USE [test]
GO
/****** Object: StoredProcedure [dbo].[test_proc] Script Date: 07/20/2017 00:44:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[test_proc]
(
@tt [testtable] READONLY
)
AS
INSERT INTO [dbo].[test] ([mycode],[myname],[mydes])
SELECT oc.[testcode],oc.[testname],oc.[testdes] FROM @tt AS oc;
GO
4、创建批量更新存储过程：
USE [test]
GO
/****** Object: StoredProcedure [dbo].[update_twotable] Script Date: 07/20/2017 00:45:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[update_twotable]
@tt [testtable] READONLY
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE a
SET a.mycode=b.testcode,a.myname=b.testname,a.mydes=b.testdes
FROM test a,
(select ID,testcode,testname,testdes
from @tt) b
WHERE a.id = b.id
END
GO
5、在VISUAL STUDIO创建工程及FORM
datagridview实现批量插入与更新
6、FORM代码如下：
Public Class Form1
#Region "定义变量"
Dim cnstring As String = "Server =(local); Integrated Security=True; Initial Catalog=test;"
Dim cn As New SqlClient.SqlConnection(cnstring)
Dim cmd As SqlClient.SqlCommand = cn.CreateCommand()
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet
Dim dt As New DataTable
#End Region
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim s As String = "select * from [test]"
cmd.CommandText = s
da.SelectCommand = cmd
da.Fill(ds)
dt = ds.Tables(0)
DataGridView1.DataSource = dt
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Dim sqlbulkcopy As New SqlClient.SqlBulkCopy(cnstring, SqlClient.SqlBulkCopyOptions.KeepIdentity)
sqlbulkcopy.DestinationTableName = "test"
'数据库中的表名
sqlbulkcopy.ColumnMappings.Add("mycode", "mycode")
sqlbulkcopy.ColumnMappings.Add("myname", "myname")
sqlbulkcopy.ColumnMappings.Add("mydes", "mydes")
sqlbulkcopy.WriteToServer(DataGridView1.DataSource)
MsgBox("成功！")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
DataGridView1.BeginEdit(True)
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim t As New DataTable
t = DataGridView1.DataSource
Dim cmd2 As New SqlClient.SqlCommand
Try
With cmd2
.Connection = cn
.CommandText = "update_twotable"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@tt", SqlDbType.Structured).Value = t
If cn.State <> ConnectionState.Open Then
cn.Open()
End If
End With
cmd2.ExecuteNonQuery()
MsgBox("OK!")
Catch ex As Exception
Finally
cn.Close()
End Try
End Sub
End Class
