I have Users into my web application and Each user have the Following Information ;
Users(Name,ID,Email,Password,District,Tehsil,Ucs,Villages)
The Rules are
The Districts ,Tehsil,UCs,Villages are cascade in nature means that on district selection the Tehsils populates ,ON Tehsil Selection the UCS Populates, and ON UCS selection The Villages populates,
1-Each user can have one or many districts
2-Each user can have no or many Tehsils
3-Each user can have no or many ucs
4-Each user can have no or many villages
I have done this in that way
Having table of users where i store (userid(P),Name,EMail,Password,DistrictID(F))
and my second table is USERANDDISTRICTS
USERANDDISTRICTS(Userid(F),RecordID(P),DistrictID(F),TEHSILID(F),UCID(F),VILLAGEID(F))
and I am saving records on nested loops mean first the district loop gets selected record if it has two selected values then it first insert the first one and then loop the nested other Tehsil,UCs,Village and then come to the second values it makes my code to run very slow below is the code which i am using.
Also on edit i am deleting all the previous records and insert the new selected records
How to edit so i do not need to remove the previous records
"SAVE CODE IS BELOW"
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())))
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("@Password", SqlDbType.VarChar).Value = base64Encode(txtPassword.Text.Trim())
.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 < CheckBoxList1.Items.Count)
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 < CheckBoxList2.Items.Count)
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 < CheckBoxList3.Items.Count)
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 < CheckBoxList4.Items.Count)
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
End Sub