Relational insertion into the tables

Question

I have related data (relational database) cascade in nature and i want to save that data the basic structure of the data is like that 
I have district(on first level) , these districts populates Tehsils(second level) , These tehsils populates UCs(third Level) and These Ucs populates villages this has been done now i want to save a record against a user the business rules are like that 
One user can have one or more districts, Many or no Tehsil, Many or no UCs, Many or no villages, the probibility of insertion is that the user can have More villages but uc and tehsil and district count does not go beyond the 20 but villages can have 280 for one user .


I have created four tables 
District(districtId(P),Name)
TEhsil(TehsilID(P),Name,DistrictID(F))
UCS(UCID(P),Name,TEHSILID(F))
Villages(VIllageID(P),Name,UCID(F))
P ---->primary key
F----->Foreign key 

Now i Know that the district can have one to twenty rows so on save function i am simple using the loop and same for tehsil but for ucs and villages i am using table value parameter still it takes 1-2 minutes to save 80 villages ?why it is so and secondly let us an example if I am saving the Villages (for every village ID I pass that ID to a function which fetch the UCID of that Villlage and save(as forign key) may b this method is taking much time or what else .
and mean while I am using four function each one creating its own connection and then close 


below is the code sample ...

Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click

        If Not Page.IsValid Then Return

        Dim blnIsUpdate As Boolean = False

        Dim hasedBytes As Byte()

        Dim strPassword As String = String.Empty

        Dim md5Hasher As New MD5CryptoServiceProvider()

        If Not litUserId.Text.Trim().Length.Equals(0) Then blnIsUpdate = True

        Dim cnnContact As New SqlConnection(AppSettings("DbSqlPortal"))

        Dim cmdContact As New SqlCommand("spAdminSaveUsers", cnnContact)

        Dim IDs As Integer = Nothing

        Dim trnContact As SqlTransaction = Nothing

        cmdContact.CommandType = CommandType.StoredProcedure

        hasedBytes = md5Hasher.ComputeHash(New UTF8Encoding().GetBytes((txtPassword.Text.Trim() & txtEmail.Text.Trim())))

        GetDistrictID()

        'If GetDistrictId() > 0 Then

        '    DisplayMessage(Me.Master.MessageBox, "A User with same District and Same user role  already exists.", "Record Exists", MessageBoxTypes.Warning)

        'Else

        Try

            With cmdContact.Parameters

                If blnIsUpdate Then

                    .Add("@UserId", SqlDbType.BigInt).Value = CInt(litUserId.Text.Trim())

                Else

                    .Add("@UserId", SqlDbType.BigInt).Value = DBNull.Value

                End If

                .Add("@FullName", SqlDbType.NVarChar).Value = txtFullName.Text.Trim()

                .Add("@Email", SqlDbType.NVarChar).Value = txtEmail.Text.Trim()

                .Add("@Password", SqlDbType.VarChar).Value = base64Encode(txtPassword.Text)

                'txtPassword.Text.Trim()

                'Convert.ToBase64String(hasedBytes)

                .Add("@SecurityQuestion", SqlDbType.VarChar).Value = ddlSecurityQuestions.SelectedItem.Text

                .Add("@SecurityAnswer", SqlDbType.VarChar).Value = txtAnswer.Text.Trim()

                .Add("@UserTypes", SqlDbType.TinyInt).Value = ddlUserTypes.SelectedValue

                .Add("@IsActive", SqlDbType.Bit).Value = chkActive.Checked

                .Add("@DistrictId", SqlDbType.TinyInt).Value = CheckBoxList1.SelectedValue

                .Add("@IsAdmin", SqlDbType.Bit).Value = chkAdmin.Checked

                .Add("@IPAddress", SqlDbType.VarChar).Value = Request.UserHostAddress

                .Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output

                .Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirection.Output

                If ChkIsReportAble.Checked = False Then

                    .Add("@IsReportable", SqlDbType.Bit).Value = False

                Else

                    .Add("@IsReportable", SqlDbType.Bit).Value = ChkIsReportAble.Checked

                End If

                If ChkIsReportAble.Checked Then

                    .Add("@ReporintPerson", SqlDbType.TinyInt).Value = CInt(ddlReporintUser.SelectedValue)

                Else

                    .Add("@ReporintPerson", SqlDbType.TinyInt).Value = DBNull.Value

                End If

            End With

            cnnContact.Open()

            trnContact = cnnContact.BeginTransaction()

            'GetDistrictId()

            'If GetDistrictId() > 0 Then

            '    DisplayMessage(Me.Master.MessageBox, "A User with same District and Same user role  already exists.", "Record Exists", MessageBoxTypes.Warning)

            'Else

            cmdContact.Transaction = trnContact

            cmdContact.ExecuteNonQuery()

            If ddlUserTypes.SelectedValue = "6" Then

                If CChar(cmdContact.Parameters("@Result").Value).Equals("E"c) Or (CChar(cmdContact.Parameters("@Result").Value).Equals("E"c) Or GetDistrictID()) > 0 Then

                    '   Msg.Text = "The Record has been Successfully Submitted !"

                    DisplayMessage(Me.Master.MessageBox, "A User with same District and role  already exists.", "Record Exists", MessageBoxTypes.Warning)

                    'MessageBoxShow(Page, "This Record Exists Try with some other record!")

                    litUserId.Text = String.Empty

                    Return

                End If

            End If

            If CChar(cmdContact.Parameters("@Result").Value).Equals("E"c) Then

                '   Msg.Text = "The Record has been Successfully Submitted !"

                DisplayMessage(Me.Master.MessageBox, "A User with same Email already exists.", "Record Exists", MessageBoxTypes.Warning)

                'MessageBoxShow(Page, "This Record Exists Try with some other record!")

                litUserId.Text = String.Empty

                Return

            End If

            trnContact.Commit()

            litUserId.Text = (cmdContact.Parameters("@ReturnId").Value)

            cnnContact.Close()

            ' SearchUsers()

            If blnIsUpdate Then

                '  spAdminDeleletUserAssociationNews()

                'Dim cmdsDistrict As New SqlCommand("spAdminDeleletUserAssociation", cnnContact)

                Dim cmdsDistrict As New SqlCommand("spAdminDeleletUserAssociationNews", cnnContact)

                cmdsDistrict.CommandType = CommandType.StoredProcedure

                Try

                    With cmdsDistrict.Parameters

                        .Add("@UserId", SqlDbType.TinyInt).Value = litUserId.Text

                        .Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output

                    End With

                    cnnContact.Open()

                    trnContact = cnnContact.BeginTransaction()

                    cmdsDistrict.Transaction = trnContact

                    'GetDistrictId()

                    cmdsDistrict.ExecuteNonQuery()

                    trnContact.Commit()

                    cnnContact.Close()

                Catch ex As Exception

                End Try

                'Dim cmd3 As New SqlCommand("Delete from UserAndDistricts Where UserId=@Userid,Delete from UserDistricts Where UserID=@Userid", cnnContact)

                'cmd3.CommandType = CommandType.Text

                'cmd3.Parameters.AddWithValue("@UserId", litUserId.Text)

                'cnnContact.Open()

                'cmd3.ExecuteNonQuery()

                'cnnContact.Close()

                'Dim cmd2 As New SqlCommand("shiftDistrictsAndSUsers", cnnContact)

                'cmd2.CommandType = CommandType.StoredProcedure

                'cmd2.Parameters.AddWithValue("@UserId", litUserId.Text)

                'cnnContact.Open()

                'cmd2.ExecuteNonQuery()

                'cnnContact.Close()

            End If

            'GetListBoxSelStringInComma(CheckBoxList1)

            Bulkinsert()

            'Dim i As Integer = 0

            'Do While (i < CheckBoxList1.Items.Count)

            '    If CheckBoxList1.Items(i).Selected Then

            '        Dim cmdDistrict As New SqlCommand("UserDistrictsss", cnnContact)

            '        cmdDistrict.CommandType = CommandType.StoredProcedure

            '        With cmdDistrict.Parameters

            '            .Add("@DistrictId", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)

            '            ' .Add("@DistrictId", SqlDbType.NVarChar).Value = GetListBoxSelStringInComma(CheckBoxList1).ToString()

            '            'If CheckBoxList2.Items(i).Selected = False Then

            '            '    .Add("@TehsilId", SqlDbType.BigInt).Value = 0

            '            'End If

            '            ' .Add("@TehsilId", SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(i).Value)

            '            .Add("UserId", SqlDbType.TinyInt).Value = litUserId.Text

            '            .Add("@DistrictName", SqlDbType.NVarChar).Value = CheckBoxList1.Items(i).Text

            '            .Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output

            '            .Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirection.Output

            '        End With

            '        cnnContact.Open()

            '        trnContact = cnnContact.BeginTransaction()

            '        cmdDistrict.Transaction = trnContact

            '        cmdDistrict.ExecuteNonQuery()

            '        If CChar(cmdContact.Parameters("@Result").Value).Equals("E"c) Then

            '            '   Msg.Text = "The Record has been Successfully Submitted !"

            '            DisplayMessage(Me.Master.MessageBox, "A User with same District already exists.", "Record Exists", MessageBoxTypes.Warning)

            '            'MessageBoxShow(Page, "This Record Exists Try with some other record!")

            '            litUserId.Text = String.Empty

            '            Return

            '        End If

            '        trnContact.Commit()

            '        cnnContact.Close()

            '    End If

            '    i = (i + 1)

            'Loop

            Dim j As Integer = 0

            Do While (j < CheckBoxList2.Items.Count)

                If CheckBoxList2.Items(j).Selected Then

                    Dim cmdsDistrict As New SqlCommand("UserTehsilss", cnnContact)

                    cmdsDistrict.CommandType = CommandType.StoredProcedure

                    With cmdsDistrict.Parameters

                        .Add("@TehsilId", SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value)

                        hdnThil = CInt(CheckBoxList2.Items(j).Value)

                        'GetDistrictIds(CheckBoxList2.Items(j).Value)

                        ' .Add("@DistrictID", SqlDbType.Int).Value = GetDistrictIds(CheckBoxList2.Items(j).Value)

                        .Add("@DistrictID", SqlDbType.Int).Value = 1

                        'GetListBoxSelStringInCommaTehsil(CheckBoxList2)

                        'CInt(CheckBoxList2.Items(j).Value)

                        .Add("UserId", SqlDbType.TinyInt).Value = litUserId.Text

                        .Add("@tehsilName", SqlDbType.NVarChar).Value = CheckBoxList2.Items(j).Text

                        .Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output

                        .Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirection.Output

                        '.Add("@DistrictID", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)

                    End With

                    cnnContact.Open()

                    trnContact = cnnContact.BeginTransaction()

                    cmdsDistrict.Transaction = trnContact

                    'GetDistrictId()

                    cmdsDistrict.ExecuteNonQuery()

                    If CChar(cmdContact.Parameters("@Result").Value).Equals("E"c) Then

                        '   Msg.Text = "The Record has been Successfully Submitted !"

                        DisplayMessage(Me.Master.MessageBox, "A User with same Tehsil already exists.", "Record Exists", MessageBoxTypes.Warning)

                        'MessageBoxShow(Page, "This Record Exists Try with some other record!")

                        litUserId.Text = String.Empty

                        Return

                    End If

                    trnContact.Commit()

                    cnnContact.Close()

                End If

                j = (j + 1)

            Loop

            Dim k As Integer = 0

            Do While (k < CheckBoxList3.Items.Count)

                If CheckBoxList3.Items(k).Selected Then

                    Dim cmdsDistrictd As New SqlCommand("UserUCss", cnnContact)

                    cmdsDistrictd.CommandType = CommandType.StoredProcedure

                    Try

                        With cmdsDistrictd.Parameters

                            .Add("@UcId", SqlDbType.BigInt).Value = CInt(CheckBoxList3.Items(k).Value)

                            hdnUC = CInt(CheckBoxList3.Items(k).Value)

                            '  .Add("@TehsilId", SqlDbType.BigInt).Value = GetTehsilIDIds(CheckBoxList3.Items(k).Value)

                            .Add("@TehsilId", SqlDbType.BigInt).Value = 13

                            .Add("@UserId", SqlDbType.BigInt).Value = litUserId.Text

                            .Add("@UCName", SqlDbType.NVarChar).Value = CheckBoxList3.Items(k).Text

                            .Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output

                            .Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirection.Output

                            '.Add("@DistrictID", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)

                        End With

                        cnnContact.Open()

                        trnContact = cnnContact.BeginTransaction()

                        cmdsDistrictd.Transaction = trnContact

                        cmdsDistrictd.ExecuteNonQuery()

                        trnContact.Commit()

                        cnnContact.Close()

                    Catch ex As Exception

                    End Try

                End If

                k = k + 1

            Loop

            BulkinsertVillages()

            'Dim L As Integer = 0

            'Do While (L < CheckBoxList4.Items.Count)

            '    If CheckBoxList4.Items(L).Selected Then

            '        Dim cmdsDistrict As New SqlCommand("UserVillagess", cnnContact)

            '        cmdsDistrict.CommandType = CommandType.StoredProcedure

            '        Try

            '            With cmdsDistrict.Parameters

            '                .Add("@villageId", SqlDbType.BigInt).Value = CInt(CheckBoxList4.Items(L).Value)

            '                hdnVillage = CInt(CheckBoxList4.Items(L).Value)

            '                .Add("@UcUD", SqlDbType.BigInt).Value = GetUCIDIds(CheckBoxList4.Items(L).Value)

            '                .Add("UserId", SqlDbType.TinyInt).Value = litUserId.Text

            '                .Add("@VillageName", SqlDbType.NVarChar).Value = CheckBoxList4.Items(L).Text

            '                .Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output

            '                .Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirection.Output

            '                '.Add("@DistrictID", SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value)

            '            End With

            '            cnnContact.Open()

            '            trnContact = cnnContact.BeginTransaction()

            '            cmdsDistrict.Transaction = trnContact

            '            cmdsDistrict.ExecuteNonQuery()

            '            trnContact.Commit()

            '            cnnContact.Close()

            '        Catch ex As Exception

            '        End Try

            '    End If

            '    L = (L + 1)

            'Loop

            '    Catch ex As Exception

            'End Try

            cnnContact.Close()

            Dim strMessage As String = "A new User has successfully been created and saved."

            If blnIsUpdate Then strMessage = "The selected User has successfully been updated and saved."

            'lblMesssages.InnerText = "Welcome"

            DisplayMessage(Me.Master.MessageBox, strMessage, "User Saved Successfully", MessageBoxTypes.Success)

            SendMAilMessage()

            MVSearch.ActiveViewIndex = 0

            SearchUsers()

            'DisplayInitialPage()

        Catch ex As Exception

            If trnContact IsNot Nothing Then trnContact.Rollback()

            DisplayMessage(Me.Master.MessageBox, "An unexpected error occurred while saving the Record. Please retry.", "Record Not Saved", MessageBoxTypes.Error)

        Finally

            DisposeDataObjects(, , cmdContact, cnnContact)

        End Try

        ' End If

    End Sub