Bulk Insert with DataTable as parameter to Stored Procedure using Web Service in ASP.Net

Sandeep080
 
on Jan 14, 2019 12:41 AM
Sample_345778.zip
6169 Views

i want insert Set of record as Datable i.e called Bulkey Insert

how Can i do this 

i want to insert into database using stored procedure wth Input Parameter as type table through web service

Datatable will come from Web Page

Please Help Quickly

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jan 14, 2019 02:38 AM
on Jan 14, 2019 05:49 AM

Hi Sandeep080,

Using the below article i have created the example.

Insert (Save) DataTable to SQL Server Table using C# and VB.Net

Select Insert Edit Update Delete using Web Service in ASP.Net

Now please take its reference and correct your code.

Database

I have made use of the following table Customers with the schema as follows.

You can download the database table SQL by clicking the download link below.

Download SQL file

Procedure

CREATE TYPE [dbo].[CustomerType] AS TABLE(
    [Id] [int] NULL,
    [Name] [varchar](100) NULL,
    [Country] [varchar](50) NULL
)
GO

CREATE PROCEDURE [dbo].[Insert_Customers]
    @tblCustomers CustomerType READONLY
AS
BEGIN
    SET NOCOUNT ON;
 
    INSERT INTO Customers(CustomerId, Name, Country)
    SELECT Id, Name, Country FROM @tblCustomers
END

HTML

<asp:GridView ID="gvDataTable" runat="server" AutoGenerateColumns="false" EmptyDataText="No records has been added.">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<br />
<asp:Button Text="Bulk Insert" runat="server" OnClick="Insert" />
<br />
<br />
<asp:GridView ID="gvDataBase" runat="server" AutoGenerateColumns="false" EmptyDataText="No records has been added.">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

Web Service

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

/// <summary>
/// Summary description for Service
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
    public Service()
    {
    }

    [WebMethod]
    public DataTable Get()
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT CustomerId,Name,Country FROM Customers"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        dt.TableName = "Customers";
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }
    }

    [WebMethod]
    public void Insert(DataTable dt)
    {
        if (dt.Rows.Count > 0)
        {
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                string sql = "Insert_Customers";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@tblCustomers", dt);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }
    }
}

VB.Net

Imports System.Web.Services
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class Service
     Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function [Get]() As DataTable
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("SELECT CustomerId,Name,Country FROM Customers")
                Using sda As New SqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd
                    Using dt As New DataTable()
                        dt.TableName = "Customers"
                        sda.Fill(dt)
                        Return dt
                    End Using
                End Using
            End Using
        End Using
    End Function

    <WebMethod()> _
    Public Sub Insert(dt As DataTable)
        If dt.Rows.Count > 0 Then            
            Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using conn As SqlConnection = New SqlConnection(constr)
                Dim sql As String = "Insert_Customers"
                Using cmd As SqlCommand = New SqlCommand(sql, conn)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.AddWithValue("@tblCustomers", dt)                    
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    conn.Close()
                End Using
            End Using
        End If
    End Sub
End Class

Namespaces

C#

using System.Data;

VB.Net

Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindDataTableGrid();
    }
}

private void BindDataTableGrid()
{
    gvDataTable.DataSource = GetDataTable();
    gvDataTable.DataBind();
}

private DataTable GetDataTable()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                        new DataColumn("Name", typeof(string)),
                        new DataColumn("Country",typeof(string)) });
    dt.TableName = "Customers";
    dt.Rows.Add(1, "John Hammond", "United States");
    dt.Rows.Add(2, "Mudassar Khan", "India");
    dt.Rows.Add(3, "Suzanne Mathews", "France");
    dt.Rows.Add(4, "Robert Schidner", "Russia");
    return dt;
}

protected void Insert(object sender, EventArgs e)
{
    DataTable dt = GetDataTable();
    CRUD_Service.Service service = new CRUD_Service.Service();
    service.Insert(dt);
    gvDataBase.DataSource = service.Get();
    gvDataBase.DataBind();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindDataTableGrid()
    End If
End Sub

Private Sub BindDataTableGrid()
    gvDataTable.DataSource = GetDataTable()
    gvDataTable.DataBind()
End Sub

Private Function GetDataTable() As DataTable
    Dim dt As New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("Id", GetType(Integer)), _
                                           New DataColumn("Name", GetType(String)), _
                                           New DataColumn("Country", GetType(String))})
    dt.TableName = "Customers"
    dt.Rows.Add(1, "John Hammond", "United States")
    dt.Rows.Add(2, "Mudassar Khan", "India")
    dt.Rows.Add(3, "Suzanne Mathews", "France")
    dt.Rows.Add(4, "Robert Schidner", "Russia")
    Return dt
End Function

Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = GetDataTable()
    Dim service As CRUD_Service.Service = New CRUD_Service.Service()
    service.Insert(dt)
    gvDataBase.DataSource = service.Get()
    gvDataBase.DataBind()
End Sub

Screenshot

Web Page

Record After Insert