Insert DataTable in to SQL Server Table using C# and VB.Net

Step 1 : Create User-Defined TableType.

CREATE TYPE [dbo].[Employee] AS TABLE(

      [Id] [int] NULL,

      [Name] [varchar](100) NULL,

      [Country] [varchar](50) NULL

)

GO

when user define type is created it will display in Object Explorer
Programability > Types > User-Defined TableTypes

or

User-Defined TableType is also created from Object Explorer
Right click on folder Programability > Types > User-Defined TableTypes
and create new  type

Step 2 : Create Stored Procedure which accept DataTable as  parameter

CREATE PROCEDURE [dbo].[Insert_Employee]

      @tblEmployee CustomerType READONLY

AS

BEGIN

      SET NOCOUNT ON;

     

      INSERT INTO Customers(CustomerId, Name, Country)

      SELECT Id, Name, Country FROM @tblEmployee

END

Step 3 : Call Stored Procedure From C# and VB

C# code

SqlCommand cmd = new SqlCommand("Insert_Employee")

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

cmd.Parameters.AddWithValue("@tblEmployee", DatatableObject);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

VB code

cmd.CommandType = CommandType.StoredProcedure

cmd.Connection = con

cmd.Parameters.AddWithValue("@tblEmployee", DatatableObject)

con.Open()

cmd.ExecuteNonQuery()

con.Close()

How to call WebMethod from JQuery

Script

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js" type="text/javascript"></script>

<script type = "text/javascript">

function ShowCurrentTime() {

    $.ajax({

        type: "POST",

        url: "CS.aspx/GetCurrentTime",

        data: '{name: "' + $("#<%=txtUserName.ClientID%>")[0].value + '" }',

        contentType: "application/json; charset=utf-8",

        dataType: "json",

        success: OnSuccess,

        failure: function(response) {

            alert(response.d);

        }

    });

}

function OnSuccess(response) {

    alert(response.d);

}

</script>

WebMothod

in C# or VB

C#

[System.Web.Services.WebMethod]

public static string GetCurrentTime(string name)

{

    return "Hello " + name + Environment.NewLine + "The Current Time is: "

        + DateTime.Now.ToString();

}

VB

<System.Web.Services.WebMethod()> _

Public Shared Function GetCurrentTime(ByVal name As String) As String

   Return "Hello " & name & Environment.NewLine & "The Current Time is: " & _

            DateTime.Now.ToString()

End Function