How to save associated records

Question

I want to save some related records I.e I have users into my system the users have the following fields (username,email,District,Tehsil,Uc,Villages)
The Districts have Tehsils,The Tehsils have ucs and the ucs have villages Now i am using th while loop and its slow the saving process very much any one guide me for batter approach and share any liked similar work with me please

Danish Habib · Answer

Thanks I am waiting for more useful guidelines..

Wim Sturkenboom · Answer

The only thing I currently notice is that you keep on opening and closing connections; that is a very expensive exercise. I suggest that you open the connection once in the beginning (before the the most outer loop) and close at the end (after the most outer loop). I'm not a VB person (C#) so still trying to figure a few things out. It might take a while though.

Danish Habib · Answer

Below is the code of my Save user information button. User table(useridUsernameEmailPasswordSecurity QuestionSecurityAnswerDistrict(from which he belongs to) The Location hierarchy is like that (districts populates Tehsils ->mean one district can have many tehsils and tehsil populate Ucs ->mean one tehsil may have many ucs in it  and Ucs populate villages (means->one uc may have 100 of villages ) so the story is like that one user may belong to just one district and 2 tehsil and 4 ucs and 20 villages in these ucs .so below is the code whcih i am using 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 = 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 'For Each item As ListItem In Me.CheckBoxList1.Items ' If item.Selected Then ' i += 1 ' CheckBoxList1.SelectedValue = item.Value ' If i = 1 Then ' listitems += item.Text ' Else ' listitems += ' + item.Text ' End If ' listitems = item.Value ' CheckBoxList1.SelectedValue = item.Value ' If i >= 1 Then ' If item.Selected Then ' Dim ccmd As New SqlCommand(insert into UserAndDistricts values (' & CheckBoxList1.SelectedValue & '' & 1 & ') cnnContact) ' ccmd.Parameters.AddWithValue(@DistrictID CheckBoxList1.SelectedValue) ' ccmd.Parameters.AddWithValue(@UserId litUserId.Text) ' ' status = (status(String.Format({0} item))) ' cnnContact.Open() ' ccmd.ExecuteNonQuery() ' cnnContact.Close() ' End If ' End If ' Else ' End If 'Next .Add(@DistrictId SqlDbType.TinyInt).Value = CheckBoxList1.SelectedValue '.Add(@DistrictId SqlDbType.TinyInt).Value = ddlDistricts.SelectedValue .Add(@IsAdmin SqlDbType.Bit).Value = chkAdmin.Checked '.Add(@ReportingPerson SqlDbType.TinyInt).Value = CInt(ddlReporintUser.SelectedValue) .Add(@IPAddress SqlDbType.VarChar).Value = Request.UserHostAddress .Add(@Result SqlDbType.Char 1).Direction = ParameterDirection.Output .Add(@ReturnId SqlDbType.BigInt).Direction = ParameterDirection.Output ' .Add(@CreateDate SqlDbType.SmallDateTime).Value = DateTime.Now 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(Ec) Or (CChar(cmdContact.Parameters(@Result).Value).Equals(Ec) 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(Ec) 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() 'ScriptManager.RegisterStartupScript(Page Page.GetType() Success Data has been saved True) 'Dim status As String = String.Empty 'Dim cmdsContact As New SqlCommand(GETID cnnContact) 'cmdsContact.CommandType = CommandType.StoredProcedure 'Dim rdr As SqlDataReader = Nothing 'rdr = cmdsContact.ExecuteReader() ' rdr.Close() 'listitems = ControlChars.CrLf 'Open this section when multiple districts came in If blnIsUpdate Then Dim cmd3 As New SqlCommand(Delete from UserAndDistricts 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 'open this when multiple districts came in 'This isthe Code whcih check all the selected districts checkboxes cheked and then save ''''''' 'mjhy ab in districts main sy tehsil check krni hain k wo b jo jo selected hain wo b save houn 'This is comment open this when multiple districts came in Dim i As Integer = 0 Do While (i If CheckBoxList1.Items(i).Selected Then Dim cmdDistrict As New SqlCommand(AssociateDistricts cnnContact) cmdDistrict.CommandType = CommandType.StoredProcedure Try With cmdDistrict.Parameters If blnIsUpdate Then .Add(@UserDistrictId SqlDbType.BigInt).Value = DBNull.Value ' .Add(@UserDistrictId SqlDbType.BigInt).Value = CInt(litDistrictUser.Text.Trim()) Else .Add(@UserDistrictId SqlDbType.BigInt).Value = DBNull.Value End If .Add(@DistrictId SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value) '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(@IsSelcted SqlDbType.Bit).Value = True .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() trnContact.Commit() cnnContact.Close() Dim j As Integer = 0 Dim k As Integer = 0 Dim L As Integer = 0 Do While (j If CheckBoxList2.Items(j).Selected Then Dim cmdsDistrict As New SqlCommand(AddTehsil cnnContact) cmdsDistrict.CommandType = CommandType.StoredProcedure Try With cmdsDistrict.Parameters If blnIsUpdate Then .Add(@UserDistrictId SqlDbType.BigInt).Value = DBNull.Value ' .Add(@UserDistrictId SqlDbType.BigInt).Value = CInt(litDistrictUser.Text.Trim()) Else .Add(@UserDistrictId SqlDbType.BigInt).Value = DBNull.Value End If .Add(@DistrictId SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value) .Add(@TehsilId SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value) 'GetListBoxSelStringInCommaTehsil(CheckBoxList2) 'CInt(CheckBoxList2.Items(j).Value) .Add(UserId SqlDbType.TinyInt).Value = litUserId.Text .Add(@IsSelcted SqlDbType.Bit).Value = True .Add(@DistrictName SqlDbType.NVarChar).Value = CheckBoxList1.Items(i).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() trnContact.Commit() cnnContact.Close() Catch ex As Exception End Try End If Do While (k If CheckBoxList3.Items(k).Selected Then Dim cmdsDistrictd As New SqlCommand(AddUcs cnnContact) cmdsDistrictd.CommandType = CommandType.StoredProcedure Try With cmdsDistrictd.Parameters If blnIsUpdate Then .Add(@UserDistrictId SqlDbType.BigInt).Value = DBNull.Value ' .Add(@UserDistrictId SqlDbType.BigInt).Value = CInt(litDistrictUser.Text.Trim()) Else .Add(@UserDistrictId SqlDbType.BigInt).Value = DBNull.Value End If .Add(@DistrictId SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value) If CheckBoxList2.Items(j).Selected = True Then .Add(@TehsilId SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value) Else .Add(@TehsilId SqlDbType.BigInt).Value = DBNull.Value End If ' .Add(@TehsilId SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value) 'GetListBoxSelStringInCommaTehsil(CheckBoxList2) 'CInt(CheckBoxList2.Items(j).Value) .Add(@UcId SqlDbType.BigInt).Value = CInt(CheckBoxList3.Items(k).Value) .Add(UserId SqlDbType.TinyInt).Value = litUserId.Text .Add(@IsSelcted SqlDbType.Bit).Value = True .Add(@DistrictName SqlDbType.NVarChar).Value = CheckBoxList1.Items(i).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 Do While (L If CheckBoxList4.Items(L).Selected Then Dim cmdsDistrict As New SqlCommand(AddVillages cnnContact) cmdsDistrict.CommandType = CommandType.StoredProcedure Try With cmdsDistrict.Parameters If blnIsUpdate Then .Add(@UserDistrictId SqlDbType.BigInt).Value = DBNull.Value ' .Add(@UserDistrictId SqlDbType.BigInt).Value = CInt(litDistrictUser.Text.Trim()) Else .Add(@UserDistrictId SqlDbType.BigInt).Value = DBNull.Value End If .Add(@DistrictId SqlDbType.BigInt).Value = CInt(CheckBoxList1.Items(i).Value) If CheckBoxList2.Items(j).Selected = True Then .Add(@TehsilId SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value) Else .Add(@TehsilId SqlDbType.BigInt).Value = DBNull.Value End If '.Add(@TehsilId SqlDbType.BigInt).Value = CInt(CheckBoxList2.Items(j).Value) ' GetListBoxSelStringInCommaTehsil(CheckBoxList2) 'CInt(CheckBoxList2.Items(j).Value) If CheckBoxList3.Items(k).Selected = True Then .Add(@UcId SqlDbType.BigInt).Value = CInt(CheckBoxList3.Items(k).Value) Else .Add(@UcId SqlDbType.BigInt).Value = DBNull.Value End If '.Add(@UcId SqlDbType.BigInt).Value = CInt(CheckBoxList3.Items(k).Value) .Add(@villageId SqlDbType.BigInt).Value = CInt(CheckBoxList4.Items(L).Value) .Add(UserId SqlDbType.TinyInt).Value = litUserId.Text .Add(@IsSelcted SqlDbType.Bit).Value = True .Add(@DistrictName SqlDbType.NVarChar).Value = CheckBoxList1.Items(i).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 k = (k + 1) Loop j = (j + 1) Loop Catch ex As Exception End Try End If i = (i + 1) Loop 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  locally it works fast but when hosted its too slow  I have made four tables for that one table contains user basic information NameemailDistrict  another table contains useridwith districts and tehsil id another table contains userid districtid and tehsilid and uc id another table contains useriddistrictidtehsiliducidvillagesid so what is the other way this way is not a good way whcih i am using below is the attached form of user: please respond Kind Regards Danish HAbib

Wim Sturkenboom · Answer

No that's not what I'm saying. Maybe you can explain why you're looping? Maybe show the relevant code. So we can try to understand what you're doing and why you're looping.

Danish Habib · Answer

Dear please Tell me how to store the values 1- You said that i store the user (usernameEmailPassword) in one table 2- then i store the UserId and district id in another table 3-then i save the userid and tehsil id in another table 4- then i store the userid and uc id in another table 5- then i store the userid and village id in another table u mean to say i have to create a seperate table for user basic information with districts another table for tehsilanother table for ucsanother table for villages Please reply??

Wim Sturkenboom · Answer

Is this what it is supposed to be? A user lives in a village that is in an uc that is in a tehsil that is in a district? If so Assuming you have tables for district tehsil uc and villages you basically only have to store the village with the user information; the other information (uc tehsil district) can be retrieved when you retrieve the user's record form the database.