Passing DataTable into a StoredProcedure

Thanks to Geetanshu Behera who informed me to try this.

-- Run this into one of your Database
-- 1. Create a table to populate via our StoredProcedure

CREATE TABLE Artist
(
[Artist#] int Primary Key Identity,
[Name] Varchar(50) Not Null
)

-- 2. Create a tabletype for Passing the DataTable
CREATE TYPE ArtistType AS TABLE (
[Name] varchar(100) NOT NULL
);


-- 3. Create Procedure that Populates a Table and
CREATE PROCEDURE [dbo].[AddShoppings](
@Artists dbo.ArtistType READONLY
)
AS
BEGIN
-- variables to use
DECLARE @name varchar(100);

-- cursor for artists parameter
DECLARE artistCursor CURSOR FOR SELECT [Name] FROM @Artists;
-- loop through artists
OPEN artistCursor;
FETCH NEXT FROM artistCursor INTO @name;
WHILE @@FETCH_STATUS = 0 BEGIN
-- insert the artist
INSERT INTO Artist ([Name]) VALUES (@name);
FETCH NEXT FROM artistCursor INTO @name;
END;
CLOSE artistCursor;
-- clean-up
DEALLOCATE artistCursor;
END;


-- 6. Modify the ConnectionString in the below code (VB.NET) to Point to your Database

Imports System.Collections
Imports System.Data.SqlClient
Imports System.Data

Public Module PassingDataTableIntostoredProcedure


Public Sub Main()
Dim connectionString As String = "Data Source=SUBHASH;Initial Catalog=A;Integrated Security=SSPI;"
InsertArticle(connectionString)
End Sub

Public Sub InsertArticle(ByVal connectionString As String)
Try
Dim CommandText As String = "AddShoppings"
Dim connection As New SqlConnection(connectionString)
Dim Command As New SqlCommand(CommandText, connection)
connection.Open()
Dim artist As New DataTable
Dim dcol As DataColumn
Dim drow As DataRow

' Create new DataColumn, set DataType, ColumnName
' and add to DataTable.
dcol = New DataColumn()
dcol.DataType = System.Type.GetType("System.String")
dcol.ColumnName = "Name"
dcol.Caption = "Name"
'dcol.AutoIncrement = True
'dcol.AutoIncrementSeed = 1
'dcol.AutoIncrement = True
'dcol.AutoIncrementStep = 1
dcol.ReadOnly = True
dcol.Unique = True

' Add the Column to the DataColumnCollection.
artist.Columns.Add(dcol)

'' Create second column.
'dcol = New DataColumn()
'dcol.DataType = System.Type.GetType("System.String")
'dcol.ColumnName = "Name"
'dcol.AutoIncrement = False
'dcol.Caption = "Nme"
'dcol.ReadOnly = False
'dcol.Unique = False

'' Add the column to the table.
'artist.Columns.Add(dcol)

' Create three new DataRow objects and add
' them to the DataTable
Dim i As Integer
For i = 0 To 2
drow = artist.NewRow()
' drow("Artist#") = i
drow("Name") = "NameValue - " + i.ToString()
artist.Rows.Add(drow)
Next i

'Adding New Data Rows
'Dim dr1, dr2, dr3 As DataRow
'dr1 = artist.NewRow
'dr1.Item("ArtistName") = "Aaron"
'dr2 = artist.NewRow
'dr2.Item("ArtistName") = "Vidya"
'dr3 = artist.NewRow
'dr3.Item("ArtistName") = "Cameroon"

Command.CommandType = CommandType.StoredProcedure
Command.Parameters.AddWithValue("@Artists", artist)
Command.ExecuteNonQuery()
Console.WriteLine("Passing DataTable into Stored Procedure completed")
Console.WriteLine("Test your Table Article for data")
Console.ReadLine()
connection.Close()

Catch ex As Exception

End Try
End Sub 'End Of InsertArticle Procedure

End Module 'End of PassingDataTableIntostoredProcedure Module

-- 7. Run the Code.
-- 8. Check if the Table Artist is populated