datagridview实现批量插入与更新

一、批量更新

1、创建表

USE [test]GO/****** Object:  Table [dbo].[test]    Script Date: 07/20/2017 00:43:53 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[test_proc](    @tt [testtable] READONLY)ASINSERT 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[update_twotable]@tt [testtable] READONLYASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;    UPDATE aSET    a.mycode=b.testcode,a.myname=b.testname,a.mydes=b.testdesFROM   test a,(select ID,testcode,testname,testdesfrom @tt) bWHERE  a.id = b.idENDGO

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 SubEnd Class

7、批量插入效果如下：

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

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