Using table valued parameters to insert multiple rows in VB.NET

Dim Table1 As DataTable
'create a table named tmptblTable1 = New DataTable("tmptbl")
Dim Row1, Row2, Row3, Row4, Row5 As DataRow
'establishing connection. you need to provide password for SQL serverDim sConn As New SqlConnection()
Dim sSqlcommand As New SqlCommand()

Try'declare a column named Ptno Dim intPtno As DataColumn = New DataColumn("Ptno")
'setting the datatype for the columnintPtno.DataType = System.Type.GetType("System.Int32")
'adding the column to tableTable1.Columns.Add(intPtno)
Dim intUserId As DataColumn = New DataColumn("UserId")intUserId.DataType = System.Type.GetType(
"System.Int32")Table1.Columns.Add(intUserId)

Dim intDocType As DataColumn = New DataColumn("DocTypeId")intDocType.DataType = System.Type.GetType(
"System.Int32")Table1.Columns.Add(intDocType)

Dim strSessionId As DataColumn = New DataColumn("UserSessionId")strSessionId.DataType = System.Type.GetType(
"System.String")Table1.Columns.Add(strSessionId)


If Request.QueryString("Doc1") <> "0" Then'declaring a new rowRow1 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row1.Item("Ptno") = ptnoRow1.Item(
"UserId") = userIdRow1.Item(
"DocTypeId") = Doc1Row1.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row1)

End IfIf Request.QueryString("Doc2") <> "0" Then'declaring a new rowRow2 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row2.Item("Ptno") = ptnoRow2.Item(
"UserId") = userIdRow2.Item(
"DocTypeId") = Doc2Row2.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row2)

End IfIf Request.QueryString("Doc3") <> "0" Then'declaring a new rowRow3 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row3.Item("Ptno") = ptnoRow3.Item(
"UserId") = userIdRow3.Item(
"DocTypeId") = Doc3Row3.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row3)

End IfIf Request.QueryString("Doc4") <> "0" Then'declaring a new rowRow4 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row4.Item("Ptno") = ptnoRow4.Item(
"UserId") = userIdRow4.Item(
"DocTypeId") = Doc4Row4.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row4)

End IfIf Request.QueryString("Doc5") <> "0" Then'declaring a new rowRow5 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row5.Item("Ptno") = ptnoRow5.Item(
"UserId") = userIdRow5.Item(
"DocTypeId") = Doc5Row5.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row5)

End If

sConn.ConnectionString = ConfigurationManager.ConnectionStrings("StatusUpdatesConnectionString").ToString()sConn.Open()
sSqlcommand = sConn.CreateCommand()
sSqlcommand.CommandType = CommandType.StoredProcedure
sSqlcommand.CommandText =
"PROC_PV_ACCOUNT_DOCTYPE_INSERT"sSqlcommand.CommandTimeout = 3600
sSqlcommand.Connection = sConn
sSqlcommand.Parameters.Add("@DocTypeTbl", SqlDbType.Structured).Value = Table1sSqlcommand.ExecuteNonQuery()



Catch ex As Exception
Throw New Exception(ex.Message)
FinallysSqlcommand.Dispose()
If sConn.State = ConnectionState.Open ThensConn.Close()
sConn = NothingEnd IfEnd Try