SQLBULKCOPY 批量插入多条记录从 GRIDVIEW 到 ASP.NET C#,VB 中的 SQL 数据库

最新推荐文章于 2024-04-26 19:52:36 发布

一个普普通通的搬运工

最新推荐文章于 2024-04-26 19:52:36 发布

阅读量382

点赞数

文章标签： asp.net 数据库 c#

本文链接：https://blog.csdn.net/m0_50971283/article/details/130360987

版权

SQLBULKCOPY 批量插入多条记录从 GRIDVIEW 到 ASP.NET C#,VB 中的 SQL 数据库

简介：在本文中，将解释如何使用 C# 和 VB 语言在 Asp.Net 中使用 SqlBulkCopy 将从 gridview 中选择的批量数据一次保存到 sql server 数据库表中

描述：在处理 asp.net 项目时，我需要将多个选定的记录从 gridview 插入数据库。一种方法是循环遍历 gridview 记录并将选中的记录一条一条地保存到数据库中。但是当涉及到数千条记录时，它会降低性能。
SqlBulkCopy 能够使用来自其他来源的数据高效地批量加载 SQL Server 表。SqlBulkCopy 类可用于仅将数据写入 SQL Server 表。但是，数据源不限于SQL Server；可以使用任何数据源，只要数据可以加载到 DataTable实例或使用IDataReader实例读取即可。

实列：创建一个演示页面以将数据批量插入到 sql 表中。

出于演示目的，从 gridview 中的“tbEmployees”表填充了员工数据。现在假设想要入围一些员工并将这些入围的员工插入另一个表中，立即说“tbShortlistedEmployees”。所以将选定的员工数据加载到数据表中，并使用 SqBulkCopy 将这些记录批量插入到目标 sql 表中。
所以首先创建“tbEmployees”表并使用以下脚本将虚拟数据插入其中

GO
CREATE TABLE tbEmployees
(
EmpId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EmpCode VARCHAR(10),
EmpName VARCHAR(100)
)
GO
INSERT INTO tbEmployees (EmpCode, EmpName)
VALUES
(‘EMP0001’, ‘Rahul’),
(‘EMP0002’, ‘Sonam’),
(‘EMP0003’, ‘Sahil’),
(‘EMP0004’, ‘Raunak’),
(‘EMP0005’, ‘Shikhir’),
(‘EMP0006’, ‘Anjan’),
(‘EMP0007’, ‘Rajesh’),
(‘EMP0008’, ‘Supreet’),
(‘EMP0009’, ‘Simran’);

现在创建另一个表 tbShortlistedEmployees 来存储入围的员工

CREATE TABLE tbShortlistedEmployees
(
EmpId INT,
EmpCode VARCHAR(10),
EmpName VARCHAR(100)
)
现在创建一个存储过程来获取和绑定 GridView 中的员工记录

CREATE TABLE tbShortlistedEmployees
(
EmpId INT,
EmpCode VARCHAR(10),
EmpName VARCHAR(100)
)

创建另一个存储过程以绑定另一个 GridView 中的入围员工记录

CREATE PROCEDURE spGetEmployees
AS
BEGIN
SELECT * FROM tbEmployees
END

数据库工作结束。现在在 web.config 文件中创建一个连接字符串：

  <connectionStrings>
    <add name="conStr" connectionString="Data Source=LALIT-PC;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </connectionStrings>

HTML Source

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <fieldset style="width:500px;">
                <legend>Bulk insert multiple records to sql table</legend>
                <table>
                    <tr>
                        <td style="vertical-align: top">List of Employees
            <asp:GridView ID="grdEmployeeDetails" runat="server" AutoGenerateColumns="false" CellPadding="4" CellSpacing="4">
                <AlternatingRowStyle BackColor="White" />
                <HeaderStyle BackColor="#507C7D1" Font-Bold="True" ForeColor="White" HorizontalAlign="Left"></HeaderStyle>
                <RowStyle BackColor="#EFF3FB" />
                <Columns>
                    <asp:TemplateField>
                        <ItemTemplate>
                            <asp:CheckBox ID="cbSelect" runat="server" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="EmpId" HeaderText="EmpId" ItemStyle-Width="30" />
                    <asp:BoundField DataField="EmpName" HeaderText="Name" ItemStyle-Width="90" />
                    <asp:BoundField DataField="EmpCode" HeaderText="Code" ItemStyle-Width="60" />
                </Columns>
            </asp:GridView>
                        </td>
                        <td></td>
                        <td style="vertical-align: top">Shortlisted Employees
                        <asp:GridView ID="grdShortlistedEmployees" runat="server" AutoGenerateColumns="false" CellPadding="4" CellSpacing="4">
                            <AlternatingRowStyle BackColor="White" />
                            <HeaderStyle BackColor="#507C7D1" Font-Bold="True" ForeColor="White" HorizontalAlign="Left"></HeaderStyle>
                            <RowStyle BackColor="#EFF3FB" />
                            <Columns>
                                <asp:BoundField DataField="EmpId" HeaderText="EmpId" ItemStyle-Width="30" />
                                <asp:BoundField DataField="EmpName" HeaderText="Name" ItemStyle-Width="90" />
                                <asp:BoundField DataField="EmpCode" HeaderText="Code" ItemStyle-Width="60" />
                            </Columns>
                        </asp:GridView>
                        </td>
                    </tr>
                </table>
                <br />
                <asp:Button ID="btnBulkInsert" Text="Bulk Insert" OnClick="btnBulkInsert_Click" runat="server" />
                <asp:Label ID="lblMsg" runat="server"></asp:Label>
            </fieldset>
        </div>
    </form>
</body>
</html>

Asp.Net C# Code to bulk insert data into sql server table using SqlBulkCopy

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web.UI.WebControls;

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindEmployees();
        }
    }
    protected void BindEmployees()
    {
        string conString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("spGetEmployees", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                grdEmployeeDetails.DataSource = dt;
                grdEmployeeDetails.DataBind();
            }
            else
            {
                grdEmployeeDetails.DataSource = null;
                grdEmployeeDetails.DataBind();
            }
        }
    }

    protected void btnBulkInsert_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Id", typeof(Int32));
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Code", typeof(string));
        try
        {

            foreach (GridViewRow row in grdEmployeeDetails.Rows)
            {
                if (((CheckBox)row.FindControl("cbSelect")).Checked)
                {
                    Int32 empid = Convert.ToInt32(row.Cells[1].Text);
                    string name = row.Cells[2].Text;
                    string code = row.Cells[3].Text;
                    dt.Rows.Add(empid, name, code);
                }
            }
            //Check if datatable has any row
            if (dt.Rows.Count > 0)
            {
                string conString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(conString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name in which records will be inserted in bulk
                        sqlBulkCopy.DestinationTableName = "dbo.tbShortlistedEmployees";

                        //Map the DataTable columns with that of the database table. Optional if database table column and datatable columns names are same
                        sqlBulkCopy.ColumnMappings.Add("Id", "EmpId");
                        sqlBulkCopy.ColumnMappings.Add("Name", "EmpName");
                        sqlBulkCopy.ColumnMappings.Add("Code", "EmpCode");
                        con.Open();
                        sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                        lblMsg.Text = dt.Rows.Count + " records inserted successfully";
                        lblMsg.ForeColor = Color.Green;
                        BindShortlistedEmployees();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            lblMsg.Text = "Error: " + ex.Message.ToString();
            lblMsg.ForeColor = Color.Red;
        }
    }

    protected void BindShortlistedEmployees()
    {
        string conString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("spGetShortlistedEmployees", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                grdShortlistedEmployees.DataSource = dt;
                grdShortlistedEmployees.DataBind();
            }
            else
            {
                grdShortlistedEmployees.DataSource = null;
                grdShortlistedEmployees.DataBind();
            }
        }
    }

Asp.Net VB Code to bulk insert data into sql server table using SqlBulkCopy

Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Data
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindEmployees()
        End If
    End Sub


    Protected Sub BindEmployees()
        Dim conString As String = ConfigurationManager.ConnectionStrings("conStr").ConnectionString
        Using con As New SqlConnection(conString)
            Dim dt As New DataTable()
            Dim cmd As New SqlCommand("spGetEmployees", con)
            cmd.CommandType = CommandType.StoredProcedure
            Dim adp As New SqlDataAdapter(cmd)
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                grdEmployeeDetails.DataSource = dt
                grdEmployeeDetails.DataBind()
            Else
                grdEmployeeDetails.DataSource = Nothing
                grdEmployeeDetails.DataBind()
            End If
        End Using
    End Sub

    Protected Sub btnBulkInsert_Click(sender As Object, e As EventArgs)
        Dim dt As New DataTable()
        dt.Columns.Add("Id", GetType(Int32))
        dt.Columns.Add("Name", GetType(String))
        dt.Columns.Add("Code", GetType(String))
        Try

            For Each row As GridViewRow In grdEmployeeDetails.Rows
                If DirectCast(row.FindControl("cbSelect"), CheckBox).Checked Then
                    Dim empid As Int32 = Convert.ToInt32(row.Cells(1).Text)
                    Dim name As String = row.Cells(2).Text
                    Dim code As String = row.Cells(3).Text
                    dt.Rows.Add(empid, name, code)
                End If
            Next
            'Check if datatable has any row
            If dt.Rows.Count > 0 Then
                Dim conString As String = ConfigurationManager.ConnectionStrings("conStr").ConnectionString
                Using con As New SqlConnection(conString)
                    Using sqlBulkCopy As New SqlBulkCopy(con)
                        'Set the database table name in which records will be inserted in bulk
                        sqlBulkCopy.DestinationTableName = "dbo.tbShortlistedEmployees"

                        'Map the DataTable columns with that of the database table. Optional if database table column and datatable columns names are same
                        sqlBulkCopy.ColumnMappings.Add("Id", "EmpId")
                        sqlBulkCopy.ColumnMappings.Add("Name", "EmpName")
                        sqlBulkCopy.ColumnMappings.Add("Code", "EmpCode")
                        con.Open()
                        sqlBulkCopy.WriteToServer(dt)
                        con.Close()
                        lblMsg.Text = dt.Rows.Count & " records inserted successfully"
                        lblMsg.ForeColor = Color.Green
                        BindShortlistedEmployees()
                    End Using
                End Using
            End If
        Catch ex As Exception
            lblMsg.Text = "Error: " & ex.Message.ToString()
            lblMsg.ForeColor = Color.Red
        End Try
    End Sub

    Protected Sub BindShortlistedEmployees()
        Dim conString As String = ConfigurationManager.ConnectionStrings("conStr").ConnectionString
        Using con As New SqlConnection(conString)
            Dim dt As New DataTable()
            Dim cmd As New SqlCommand("spGetShortlistedEmployees", con)
            cmd.CommandType = CommandType.StoredProcedure
            Dim adp As New SqlDataAdapter(cmd)
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                grdShortlistedEmployees.DataSource = dt
                grdShortlistedEmployees.DataBind()
            Else
                grdShortlistedEmployees.DataSource = Nothing
                grdShortlistedEmployees.DataBind()
            End If
        End Using
    End Sub

一个普普通通的搬运工

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQLBULKCOPY 批量插入多条记录从 GRIDVIEW 到 ASP.NET C#,VB 中的 SQL 数据库

现在假设想要入围一些员工并将这些入围的员工插入另一个表中，立即说“tbShortlistedEmployees”。所以将选定的员工数据加载到数据表中，并使用 SqBulkCopy 将这些记录批量插入到目标 sql 表中。描述：在处理 asp.net 项目时，我需要将多个选定的记录从 gridview 插入数据库。简介：在本文中，将解释如何使用 C# 和 VB 语言在 Asp.Net 中使用 SqlBulkCopy 将从 gridview 中选择的批量数据一次保存到 sql server 数据库表中。
复制链接

扫一扫