datagridview实现批量插入与更新

一、批量更新

1、创建表

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

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

7、批量插入效果如下：

点击批量插入后效果如下：

8、批量更新后效果如下：