如何在更改之前添加一个功能来检查数据库中的电子邮件？ [英] How do I add a function to check the email against the database before being changed?

问题描述

我正在创建一个允许用户在网站上更改其电子邮件地址的功能要更改的电子邮件存储在** ASPNETDB **中。但是，要检查的电子邮件地址（MP_Email和BUP_Email）存储在另一个数据库 ProfileDB 中，该数据库在两个表中有两种类型的用户信息：

MainPlayer 和 BackupPlayer



第一个表是MainPlayer

，其中包含以下列：MP_ID， MP_FName，MP_LName和MP_Email

，第二个表是BackupPlayer

，其中包含以下列：BUP_ID，BUP_FName，BUP_LName和BUP_Email





但到目前为止，我的代码只执行更改电子邮件功能。如何在允许用户更改电子邮件之前首先检查两封电子邮件，如果电子邮件不符合，则不允许更改它。

下面是代码Behind br />

 部分 类 ChangeEmail 
 继承 System.Web.UI.Page 
 
 受保护的  Sub  SqlDataSource_Selecting（ ByVal  sender 作为 对象， ByVal  e  As  System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs） Handles  SqlDataSource.Selecting 
 ' 将UserID输入参数设置为当前登录用户的UserID  
 e.Command.Parameters（  @ UserID).Value = 我 .CurrentUserId 
 结束  Sub  
 
 受保护的  Sub  dvYourProfile_ItemUpdated（ ByVal  sender 作为 对象， ByVal  e  As  System.Web.UI.WebControls.DetailsViewUpdatedEventArgs）句柄 dvYourProfile.ItemUpdated 
 
 ' 更新用户的电子邮件地址 
 ' 首先，我们需要获取电子邮件文本框 
  Dim  txtEmail  As  TextBox =  CType （dvYourProfile .FindControl（  txtEmail），TextBox）
 
  Dim  myConnection  As  新 SqlConnection 
  Dim  myCommand  As  新 SqlCommand 
 
  Dim  usrInfo  As  MembershipUser = Membership.GetUser（）
 如果 字符串 .CompareOrdinal（usrInfo.Email，txtEmail.Text）<>  0  然后 
 ' < span class =code-comment>它们是不同的！ 
 usrInfo.Email = txtEmail.Text 
 Membership.UpdateUser（usrInfo）
 结束 如果 

下面是代码.aspx

< div style =margin-removedauto; margin-removedauto; width：549px; height：124px;; > 
< asp：DetailsView ID =dvYourProfilerunat =serverAutoGenerateRows =False
 DataKeyNames =UserIdDataSourceID =SqlDataSourceDefaultMode =Edit
 EnableTheming =真宽度=742pxHorizo​​ntalAlign =中心> 
< CommandRowStyle BorderColor =＃FFFFCCBorderStyle =None
 Horizo​​ntalAlign =CenterVerticalAlign =Middle/> 
< Fields> 
< asp：BoundField DataField =UserNameHeaderText =UserNameReadOnly =True
 SortExpression =UserName/> 
< asp：TemplateField HeaderText =EmailSortExpression =Email> 
< EditItemTemplate> 
< asp：TextBox ID =txtEmailrunat =serverColumns =35
 Text ='<％＃Eval（Email）％>'>< / ASP：文本框> 
< asp：RequiredFieldValidator ID =reqEmailrunat =server
 ControlToValidate =txtEmailDisplay =DynamicErrorMessage =*电子邮件字段不能为空！ 
 SetFocusOnError =True>< / asp：RequiredFieldValidator> 
< asp：RegularExpressionValidator ID =regExpEmailrunat =serverControlToValidate =txtEmailDisplay =DynamicErrorMessage =**请使用有效的电子邮件格式
 SetFocusOnError =真正的
 ValidationExpression =\ w +（[ -  +。'] \ w +）* @ \ w +（[ - 。] \ w +）* \。\ w +（[ - 。] \\ \\w +）*>< / ASP：的RegularExpressionValidator> 
< / EditItemTemplate> 
< ItemTemplate> 
< asp：Label ID =Label1runat =serverText ='<％＃Bind（Email）％>'>< / asp：Label> 
< / ItemTemplate> 
< / asp：TemplateField> 
< asp：TemplateField ShowHeader =False> 
< EditItemTemplate> 
< asp：LinkBut​​ton ID =LinkBut​​ton1runat =serverCausesValidation =True
 CommandName =UpdateText =UpdateOnClientClick =return confirm（'你确定要吗？更改您的电子邮件？'）>< / asp：LinkBut​​ton> 
 
< asp：LinkBut​​ton ID =lbCancelrunat =serverCausesValidation =False
 CommandName =CancelText =Cancelonclick =lbCancel_Click>< ; / ASP：LinkBut​​ton的> 
< / EditItemTemplate> 
< ItemTemplate> 
< asp：LinkBut​​ton ID =LinkBut​​ton1runat =serverCausesValidation =False
 CommandName =EditText =Edit>< / asp：LinkBut​​ton> 
< / ItemTemplate> 
< / asp：TemplateField> 
< / Fields> 
< AlternatingRowStyle BorderStyle =None/> 
< / asp：DetailsView> 

和SQLdatasource

< asp：SqlDataSource ID =SqlDataSourcerunat =server
 ConnectionString =<％$ ConnectionStrings：ConnectionStringAdmin％> 
 SelectCommand =SELECT aspnet_Membership.UserId，aspnet_Membership.Email，aspnet_Membership.LoweredEmail，aspnet_Users.UserName，aspnet_Users.LoweredUserName FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE（aspnet_Membership.UserId = @UserId） 
 UpdateCommand =UPDATE aspnet_Membership SET Email = @Email，LoweredEmail = @LoweredEmail FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE（aspnet_Membership.UserId = @UserId）> 
< SelectParameters>< asp：Parameter Name =UserIdType =Object/>< / SelectParameters> 
< updateparameters> 
< asp：Parameter Name =EmailType =string/> 
< asp：参数名称=LoweredEmailType =string/> 
< asp：参数名称=UserId/> 
< / updateparameters> 

解决方案

ConnectionStrings：ConnectionStringAdmin％>
SelectCommand =SELECT aspnet_Membership。 UserId，aspnet_Membership.Email，aspnet_Membership.LoweredEmail，aspnet_Users.UserName，aspnet_Users.LoweredUserName FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE（aspnet_Membership.UserId = @UserId）
UpdateCommand =UPDATE aspnet_Membership SET Email = @Email，LoweredEmail = @LoweredEmail FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE（aspnet_Membership.UserId = @UserId）>
< SelectParameters>< asp：Parameter Name = UserIdType =Object/>< / SelectParameters>
< updateparameters>
< asp：Parameter Name =EmailType =string/>
< ; asp：参数名称=LoweredEmailType =string/ >
< asp：参数名称=UserId/>
< / updateparameters>

我不确定SqlDataSource是否是执行此操作的最佳工具。如果我正确理解您的要求，您需要

- 检查电子邮件是否对指定用户有效

- 如果是，请将电子邮件更改为新的



据我所见，你有以下选项：

• 修改更新SqlDataSource的语句使用WHERE子句来确保电子邮件。类似
  
  

   更新 aspnet_Membership 
    SET  Email =  @ Email ，LoweredEmail =  @ LoweredEmail  
    FROM  aspnet_Membership  INNER   JOIN  aspnet_Users  ON  aspnet_Membership.UserId = aspnet_Users.UserId 
    WHERE （aspnet_Membership.UserId =  @ UserId ）
    AND  aspnet_Membership.MP_Email =  @ email  

  
  
  这将要求您检查是否有任何行更新。如果电子邮件错误，将更新0行
  
  

• 您可以在更新前使用单独的SqlCommand进行检查，这是我认为更好的选择。它会为您提供更多信息（电子邮件或用户ID无效）
• 使用存储过程，我认为这是将整个更改封装在过程中的最佳选择。这稍微提高了性能，因为需要更少的往返，但主要的好处是，如果需要，您可以在几个地方使用相同的功能，客户端可以专注于正确的错误处理。

I am creating a functionality that allows user to change their email addresses on the website The email to change is stored in the **ASPNETDB**. However, the email addresses (MP_Email and BUP_Email) to check against are stored in a different database ProfileDB which has two type of user info in two tables:
MainPlayer and BackupPlayer

The first table is MainPlayer
which has the following columns: MP_ID, MP_FName, MP_LName, and MP_Email
and the second table is BackupPlayer
which has the following columns:BUP_ID, BUP_FName, BUP_LName, and BUP_Email


But so far my code performs only the change email function. How to have it check the two emails first against the database before allowing user to change their email and if the email does not match the will not be allowed to change it.
Below is the code Behind

Partial Class ChangeEmail
    Inherits System.Web.UI.Page

    Protected Sub SqlDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource.Selecting
        'Set the UserID input parameter to the currently logged-in user's UserID
        e.Command.Parameters("@UserID").Value = Me.CurrentUserId
    End Sub

    Protected Sub dvYourProfile_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventArgs) Handles dvYourProfile.ItemUpdated

        'Update the user's Email address 
        'First, we need to get the Email textbox
        Dim txtEmail As TextBox = CType(dvYourProfile.FindControl("txtEmail"), TextBox)

        Dim myConnection As New SqlConnection
        Dim myCommand As New SqlCommand

        Dim usrInfo As MembershipUser = Membership.GetUser()
        If String.CompareOrdinal(usrInfo.Email, txtEmail.Text) <> 0 Then
            'They are different!
            usrInfo.Email = txtEmail.Text
            Membership.UpdateUser(usrInfo)
        End If

Below is the code for .aspx

<div style="margin-removedauto; margin-removedauto; width: 549px; height: 124px;"; >
        <asp:DetailsView ID="dvYourProfile" runat="server" AutoGenerateRows="False" 
            DataKeyNames="UserId" DataSourceID="SqlDataSource" DefaultMode="Edit" 
            EnableTheming="True" Width="742px" HorizontalAlign="Center">
            <CommandRowStyle BorderColor="#FFFFCC" BorderStyle="None" 
                HorizontalAlign="Center" VerticalAlign="Middle" />
            <Fields>
           <asp:BoundField DataField="UserName" HeaderText="UserName" ReadOnly="True" 
                    SortExpression="UserName" />
                <asp:TemplateField HeaderText="Email" SortExpression="Email">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEmail" runat="server" Columns="35" 
                            Text='<%# Eval("Email") %>'></asp:TextBox>
                        <asp:RequiredFieldValidator ID="reqEmail" runat="server" 
                            ControlToValidate="txtEmail" Display="Dynamic" ErrorMessage="* The E-mail field cannot be empty!" 
                            SetFocusOnError="True"></asp:RequiredFieldValidator>
                        <asp:RegularExpressionValidator ID="regExpEmail" runat="server" ControlToValidate="txtEmail" Display="Dynamic" ErrorMessage="** Please use a valid E-mail format" 
                            SetFocusOnError="True" 
                            ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("Email") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField ShowHeader="False">
                    <EditItemTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" 
                            CommandName="Update" Text="Update" OnClientClick="return confirm('Are you sure you want to change your E-mail?')"></asp:LinkButton>
                         
                        <asp:LinkButton ID="lbCancel" runat="server" CausesValidation="False" 
                            CommandName="Cancel" Text="Cancel" onclick="lbCancel_Click"></asp:LinkButton>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" 
                            CommandName="Edit" Text="Edit"></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>
            </Fields>
            <AlternatingRowStyle BorderStyle="None" />
        </asp:DetailsView>

And the SQLdatasource

<asp:SqlDataSource ID="SqlDataSource" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionStringAdmin %>" 
        SelectCommand ="SELECT aspnet_Membership.UserId, aspnet_Membership.Email, aspnet_Membership.LoweredEmail, aspnet_Users.UserName, aspnet_Users.LoweredUserName FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE (aspnet_Membership.UserId = @UserId)" 
        UpdateCommand ="UPDATE aspnet_Membership SET Email = @Email, LoweredEmail = @LoweredEmail FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE (aspnet_Membership.UserId = @UserId)">
<SelectParameters><asp:Parameter Name="UserId" Type="Object" /></SelectParameters>
            <updateparameters>
               <asp:Parameter Name="Email" Type="string"/>
               <asp:Parameter Name="LoweredEmail" Type="string"/>
               <asp:Parameter Name="UserId" />
            </updateparameters>

解决方案

ConnectionStrings:ConnectionStringAdmin %>" SelectCommand ="SELECT aspnet_Membership.UserId, aspnet_Membership.Email, aspnet_Membership.LoweredEmail, aspnet_Users.UserName, aspnet_Users.LoweredUserName FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE (aspnet_Membership.UserId = @UserId)" UpdateCommand ="UPDATE aspnet_Membership SET Email = @Email, LoweredEmail = @LoweredEmail FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE (aspnet_Membership.UserId = @UserId)"> <SelectParameters><asp:Parameter Name="UserId" Type="Object" /></SelectParameters> <updateparameters> <asp:Parameter Name="Email" Type="string"/> <asp:Parameter Name="LoweredEmail" Type="string"/> <asp:Parameter Name="UserId" /> </updateparameters>

I'm not sure if SqlDataSource is the best tool for doing this. If I understand your requirement correctly, you need to
- check if the email is valid for the specified user
- if it is, change the email to something new

As far as I can see you have options like:

• Modify the UPDATE statement of the SqlDataSource to use a WHERE clause to ensure the email. Something like
  

  UPDATE aspnet_Membership 
  SET Email = @Email, LoweredEmail = @LoweredEmail 
  FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId 
  WHERE (aspnet_Membership.UserId = @UserId) 
  AND aspnet_Membership.MP_Email = @email

  
  This would require you to check if any rows are updated or not. In case of wrong email, 0 rows would be updated
  

• You could use a separate SqlCommand to do the check before the update, this would be a better option in my opinion since it would give you more information (is the email or user id invalid)
• Use a stored procedure, I'd say this would be the best option to encapsulate the whole change in a procedure. This slightly enhances the performance because less roundtrips are needed but the main benefit is that you can use the same functionality in several places if needed and the client side can just concentrate on proper error handling.

这篇关于如何在更改之前添加一个功能来检查数据库中的电子邮件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！