数据库中的双重数据

Question

    [WebMethod]
    public string Login(string Username, string Password)
    {
        String result;
        SqlConnection con = new SqlConnection(@"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersNicole WongDesktopInari Tracking SystemInari Tracking SystemApp_DataDatabase1.mdf;Integrated Security=True;User Instance=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT Username, Password from UserData where Username = @Username AND Password = @Password", con);
        cmd.Parameters.AddWithValue("@UserName", Username);
        cmd.Parameters.AddWithValue("@Password", Password);
        cmd.ExecuteNonQuery();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        // Create an instance of DataSet.
        DataSet ds = new DataSet();
        da.Fill(ds);
        if (ds.Tables[0].Rows.Count> 0)
        {
            DateTime dt = DateTime.Now;
            SqlCommand cmd1 = new SqlCommand("INSERT INTO ActivityLog (CreateOn, CreateBy) VALUES (@CreateOn,@CreateBy)", con);
            cmd1.Parameters.AddWithValue("@CreateOn", dt);
            cmd1.Parameters.AddWithValue("@CreateBy", Username);
            cmd1.ExecuteNonQuery();
            SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
            // Create an instance of DataSet.
            DataSet ds1 = new DataSet();
            da1.Fill(ds);
            con.Close();
            result = "Successful";
            return result;
        }
        else
        {
            result = "Fail";
            return result;
        }

这是我简单的Web方法，将用户登录时间存储到数据库中。

问题是将数据保存两次到数据库中。例如，我登录系统，然后返回成功，但是我检查了数据库，其中有两个相同的记录与相同的数据保存。我使用断点运行，但是没有任何重复，该代码按线运行良好。

有什么想法吗？预先感谢

Answer 1

public string Login(string Username, string Password)
{
    String result;
    SqlConnection con = new SqlConnection(@"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersNicole WongDesktopInari Tracking SystemInari Tracking SystemApp_DataDatabase1.mdf;Integrated Security=True;User Instance=True");
    con.Open();
    SqlCommand cmd = new SqlCommand("SELECT Username, Password from UserData where Username = @Username AND Password = @Password", con);
    cmd.Parameters.AddWithValue("@UserName", Username);
    cmd.Parameters.AddWithValue("@Password", Password);
    //This us pretty much useless on a select, SELECT is a query, not a NonQuery
    //cmd.ExecuteNonQuery(); 
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    // Create an instance of DataSet.
    DataSet ds = new DataSet();
    da.Fill(ds);
    if (ds.Tables[0].Rows.Count> 0)
    {
        DateTime dt = DateTime.Now;
        SqlCommand cmd1 = new SqlCommand("INSERT INTO ActivityLog (CreateOn, CreateBy) VALUES (@CreateOn,@CreateBy)", con);
        cmd1.Parameters.AddWithValue("@CreateOn", dt);
        cmd1.Parameters.AddWithValue("@CreateBy", Username);
        cmd1.ExecuteNonQuery();
        //Don't use the DataAdapter and try to fill a dataset from an insert, all this insert will return is @@ROWCOUNT
        //SqlDataAdapter da1 = new SqlDataAdapter(cmd1); 
        // Create an instance of DataSet.
        //DataSet ds1 = new DataSet();
        //da1.Fill(ds); 
        con.Close();
        result = "Successful";
        return result;
    }
    else
    {
        result = "Fail";
        return result;
    }

您同时执行了您的选择和插入两次，而选择并不重要，但是随着插入功能。从选择中删除.ExecuteNonQuery()，然后从插入物中删除SqlDataAdapter。