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(userid,Username,Email,Password,Security Question,SecurityAnswer,District(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("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() '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 Name,email,District , another table contains userid,with districts and tehsil id, another table contains userid districtid and tehsilid and uc id, another table contains userid,districtid,tehsilid,ucid,villagesid ,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 (username,Email,Password) 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 tehsil,another table for ucs,another 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.

How to save associated records

Answers (6)