防止重复发送电子邮件地址c# [英] Prevent Duplicate E-mail Address Submissions c#

问题描述

我在下面的c#代码中尝试添加其他功能，以防止使用asp.net表单将重复的电子邮件地址输入数据库.下面是代码，当前它正在将所有条目(重复或不重复)插入数据库.

I have the following c# code below that I am trying to include additional functionality to prevent duplicate e-mail addresses from being entered into the database using an asp.net form. Below is the code, currently it is inserting all entries, duplicate or not, into the database.

using System.Data.OleDb;
using System.Configuration;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        OleDbConnection con = new OleDbConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["northwind"].ToString();
        con.Open();
        string query = "SELECT COUNT(ID) FROM Table1 WHERE pEmail=' " + TextBox2.Text + "'";
    OleDbCommand cmd = new OleDbCommand(query, con);
    int count = (int)cmd.ExecuteScalar();

    if (count > 0)
        {
            Label1.Text = "email is already in use";
        }
        else {
        cmd.CommandText = "insert into[Table1](pName, pEmail)values(@nm,@em)";
        cmd.Parameters.AddWithValue("@nm", TextBox1.Text);
        cmd.Parameters.AddWithValue("@em", TextBox2.Text);
        cmd.Connection = con;
        int a = cmd.ExecuteNonQuery();
        if (a>0)
            {
                Label1.Text = "Inserted Sucessfully!";
            }
        }
    }
}

Asp.net表单代码

Asp.net form code

<form id="form1" runat="server">
<div style="height: 138px">

    Enter Name:<asp:TextBox ID="TextBox1" runat="server" style="margin-left: 12px"></asp:TextBox>

    <asp:RequiredFieldValidator 
id="reqName" 
ControlToValidate="TextBox1"
Style="color:Red"   
ErrorMessage="Please enter your name!"
runat="server"  />

    <br />
    Enter Email:&nbsp;&nbsp;&nbsp;
    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

    <asp:RegularExpressionValidator 
id="ValidEmail" 
ControlToValidate="TextBox2" 
Style="color:Red"
ValidationExpression="^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$"
ErrorMessage="Invalid Email Entry" 
runat="server" />

    <br />
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Submit" />
    <br />
    <asp:Label ID="Label1" runat="server"></asp:Label>

</div>
</form>

推荐答案

在执行 SELECT 语句时，需要使用

As you are a doing a SELECT statement, you need to the ExecuteScalar method on OleDbCommand to get the results. As per the MSDN documentation, ExecuteScalar returns:

结果集中第一行的第一列，或者为null如果结果集为空，则引用.

The first column of the first row in the result set, or a null reference if the result set is empty.

对于您而言，返回值将是查询中 COUNT(ID)的值.

In your case, the return will be the value of COUNT(ID) from your query.

ExecuteNonQuery用于修改数据并返回受查询影响的行数的操作.根据MSDN文档:

ExecuteNonQuery is for operations which modify data and will return the number of rows affected by the query. As per the MSDN documentation:

您可以使用ExecuteNonQuery来执行目录操作，例如例如，查询数据库的结构或创建数据库对象(例如表)，或更改数据库中的数据而无需通过执行UPDATE，INSERT或DELETE语句来使用DataSet.尽管ExecuteNonQuery不返回任何行，但是任何输出参数或映射到参数的返回值将填充数据.为了UPDATE，INSERT和DELETE语句，返回值为数字该命令影响的行数.对于所有其他类型的语句，返回值为-1.如果发生回滚，则返回值也为-1.

You can use the ExecuteNonQuery to perform catalog operations, for example, to query the structure of a database or to create database objects such as tables, or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements. Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

因此，请按以下方式更改您的代码:

So change you code as follows:

string query = "SELECT COUNT(ID) FROM Table1 WHERE pEmail='" + TextBox2.Text + "'"; // note: vulnerable to SQLInjection.
OleDbCommand cmd = new OleDbCommand(query, conn);
int count = (int)cmd.ExecuteScalar();

if (count > 0)
{
  // etc.

更新:我还建议使用交易，以确保在检查重复项和进行实际插入之间没有添加重复的电子邮件地址.

Update: I would also recommend using a Transaction on your operation to make sure that a duplicate email address is not added in between your checking for duplicates and you doing the actual insert.

更新2:基于切换到使用ExecuteScalar的更新代码，此行:

Update 2: Based on the updated code that switches to using ExecuteScalar, this line:

string query = "SELECT COUNT(ID) FROM Table1 WHERE pEmail=' " + TextBox2.Text + "'";

应该是:

string query = "SELECT COUNT(ID) FROM Table1 WHERE pEmail='" + TextBox2.Text + "'";

(删除了 pEmail ='和"之间的多余空格字符.

(Extra whitespace character between pEmail=' and " removed.

这篇关于防止重复发送电子邮件地址c#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！