I have Associated Records like having four table the first one is stand alone and the later three depends on each other like the second table having a column as forign key of first table, and the third one having the forign key (primary key of second table ), and the fourth one having the foreign key of third table ,
I am using table value parameter but it makes process too slow to save 200 records in bulk i am showing one sample method i am using four sepearte for each insertion.
;;;;
Private Function BulkinsertVillages()
Dim dt As New DataTable()
Dim cnnCdf As SqlConnection = Nothing
dt.Columns.AddRange(New DataColumn(3) {New DataColumn("VillageId", GetType(Integer)), New DataColumn("VillageName", GetType(String)), New DataColumn("UcID", GetType(Integer)), New DataColumn("UserID", GetType(Integer))})
Dim i As Integer = 0
Do While (i < CheckBoxList4.Items.Count)
If CheckBoxList4.Items(i).Selected Then
Dim id As Integer = Integer.Parse(CheckBoxList4.Items(i).Value)
hdnVillage = id
Dim name As String = CheckBoxList4.Items(i).Text
Dim country As String = litUserId.Text
Dim Ucid As Integer = GetUCIDIds(CheckBoxList4.Items(i).Value)
dt.Rows.Add(id, name, Ucid, country)
End If
i = (i + 1)
Loop
If dt.Rows.Count > 0 Then
cnnCdf = New SqlConnection(AppSettings("DbSqlPortal"))
Using cmd As New SqlCommand("Insert_CustomersSecond")
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = cnnCdf
cmd.Parameters.AddWithValue("@tblCustomersSecond", dt)
cnnCdf.Open()
cmd.ExecuteNonQuery()
cnnCdf.Close()
End Using
End If
End Function
" Dim Ucid As Integer = GetUCIDIds(CheckBoxList4.Items(i).Value)" may be i have to find that record using this method it may takes time