使用存储过程检查用户名是否存在 asp.net 并将值添加到注册.aspx

我在SQL Server中创建了一个存储过程来检查数据库中是否存在用户名：

CREATE PROCEDURE [dbo].[spCheckUsernameForAnswer]
     @username VARCHAR(30)
AS
BEGIN
    DECLARE @count INT
    SELECT @count = COUNT(username) 
    FROM Users 
    WHERE [username] = @username 
    IF (@count = 1)
    BEGIN
        SELECT 1 AS ReturnCode
    END
    ELSE
    BEGIN
        SELECT 0 AS Returncode
    END
END

这是我在Visual Studio Registration.aspx.cs中所做的。

我需要检查用户名是否存在，如果没有，则需要将所需的值插入数据库以注册新用户。

从下面的代码中，它一次又一次地使用相同的用户名注册新用户。

知道我在这里错过了什么吗？

protected void Button1_Click(object sender, EventArgs e)
{
    try
    {
        Guid newGUID = Guid.NewGuid();
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand("spCheckUsernameForAnswer", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter parausername = new SqlParameter("@username", TextBoxUN.Text);
        cmd.Parameters.Add(parausername);
        conn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            if (Convert.ToBoolean(rdr["ReturnCode"]))
            {
                Label1.Text = "Username found";
            }
            else
            {
                Label1.Text = "not found";
            }
        }
        conn.Close();
        SqlConnection conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
        conn1.Open();
        string insertQuery = "insert into [Users] (user_id, first_name, last_name, email, username, password) values (@user_id, @first_name, @last_name, @email, @username, @password)";
        SqlCommand com = new SqlCommand(insertQuery, conn1);
        com.Parameters.AddWithValue("@user_id", newGUID.ToString());
        com.Parameters.AddWithValue("@first_name", TextBoxFname.Text);
        com.Parameters.AddWithValue("@last_name", TextBoxLname.Text);
        com.Parameters.AddWithValue("@email", TextBoxEmail.Text);
        com.Parameters.AddWithValue("@username", TextBoxUN.Text);
        com.Parameters.AddWithValue("@password", TextBoxPass.Text);
        com.ExecuteNonQuery();
        Response.Write("Registration successful");
        conn1.Close();
    }
}
catch (Exception ex)
{
    Response.Write("Error:" + ex.ToString());
}

最好使用 cmd.ExecuteScalar，因为 sp 返回 1 或 0。

最好将insert into db part放在单独的方法中，例如RegisterUser方法。

但最主要的是，当 db 中不存在该方法时，您需要调用该方法(在 else 语句中(

protected void Button1_Click(object sender, EventArgs e)
{
    try
    {         
        using(var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString))
        {
            using(var cmd = new SqlCommand("spCheckUsernameForAnswer", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@username", TextBoxUN.Text));
                conn.Open();
                var returnCode = Convert.ToInt32(cmd.ExecuteScalar());
                if(returnCode == 1)
                {
                    Label1.Text = "Username found";
                }
                else
                {
                    Label1.Text = "not found";
                    Register();
                }
            }
        }               
    }
    catch (Exception ex)
    {
        Response.Write("Error:" + ex.ToString());
    }
}
private void RegisterUser()
{
    try 
    {
        var newGUID = Guid.NewGuid();
        using(var conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString))
        {
            conn1.Open();
            string insertQuery = "insert into [Users] (user_id, first_name, last_name, email, username, password) values (@user_id, @first_name, @last_name, @email, @username, @password)";
            using(var com = new SqlCommand(insertQuery, conn1))
            {
                com.Parameters.AddWithValue("@user_id", newGUID.ToString());
                com.Parameters.AddWithValue("@first_name", TextBoxFname.Text);
                com.Parameters.AddWithValue("@last_name", TextBoxLname.Text);
                com.Parameters.AddWithValue("@email", TextBoxEmail.Text);
                com.Parameters.AddWithValue("@username", TextBoxUN.Text);
                com.Parameters.AddWithValue("@password", TextBoxPass.Text);
                com.ExecuteNonQuery();
            }
        }
        Response.Write("Registration successful");
    }
    catch (Exception exc)
    {
        //log the exception;
    }
}

试试这个不太确定为什么你使用阅读器，如果我是，你将处理存储过程中的所有内容

protected void Button1_Click(object sender, EventArgs e)
{
  try
  {
    Guid newGUID = Guid.NewGuid();
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
    SqlCommand cmd = new SqlCommand("spCheckUsernameForAnswer", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter parausername = new SqlParameter("@username", TextBoxUN.Text);
    cmd.Parameters.Add(parausername);
    conn.Open();
    var userexsist = (bool)cmd.ExecuteScalar();
    if (userexsist)
    {
        Label1.Text = "Username found";
        conn.close();
    }
    else
    {
        Label1.Text = "not found";

        string insertQuery = "insert into [Users] (user_id, first_name, last_name, email, username, password) values (@user_id, @first_name, @last_name, @email, @username, @password)";
        SqlCommand com = new SqlCommand(insertQuery, conn);
        com.Parameters.AddWithValue("@user_id", newGUID.ToString());
        com.Parameters.AddWithValue("@first_name", TextBoxFname.Text);
        com.Parameters.AddWithValue("@last_name", TextBoxLname.Text);
        com.Parameters.AddWithValue("@email", TextBoxEmail.Text);
        com.Parameters.AddWithValue("@username", TextBoxUN.Text);
        com.Parameters.AddWithValue("@password", TextBoxPass.Text);
        com.ExecuteNonQuery();
        Response.Write("Registration successful");
        conn.Close();
    }        
  }
  catch (Exception ex)
  {
    Response.Write("Error:" + ex.ToString());
  }
}

您也可以在存储过程中管理它。找到解决方案：

USE [akhil_db]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sp_Insert_AddUser] 
(  
@fname varchar(200),
@lname varchar(200),
@userName varchar(200),
@userEmail varchar(200),  
@userPasword varchar(200),  
@userType varchar(200),
 @msg VARCHAR(100) OUT )  
AS  
BEGIN  
SET NOCOUNT ON;  
Declare @UserCount int;
SELECT @UserCount = COUNT(*) FROM user_master WHERE [user_name] = @userName or [user_email]=@userEmail;
IF(@UserCount > 0)
begin
 Set  @msg = 'User already exists';
end
ELSE
begin
Insert into user_master(
        user_fname,
        user_lname,
      [user_name]
      ,[user_email]
      ,[user_pasword]
     ,user_type
      )
       values(
        @fname,
        @lname,
       @userName
      ,@userEmail
      ,@userPasword
     ,@userType
      )
      SET @msg ='Registered Successfully'
END 
END 

Code behind c#

SqlCommand cmd = new SqlCommand("Sp_Insert_AddUser", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@fname", txtfname.Text);
            cmd.Parameters.Add("@lname", txtlname.Text);
            cmd.Parameters.AddWithValue("@userName", txtUserName.Text);
            cmd.Parameters.AddWithValue("@userEmail", txtUserEmail.Text);
            cmd.Parameters.AddWithValue("@userPasword", txtPass.Text);
            cmd.Parameters.AddWithValue("@userType", ddlUserType.SelectedValue);
            cmd.Parameters.Add("@msg", SqlDbType.Char, 500);
            cmd.Parameters["@msg"].Direction = ParameterDirection.Output;
            con.Open();
            cmd.ExecuteNonQuery();
            string message = (string)cmd.Parameters["@msg"].Value;
            lblMessage.Visible = true;
            lblMessage.Text = message;
            con.Close();

我正在使用辅助类与很快我会发送替代解决方案

SqlHelper sho = new SqlHelper();
public bool alreadyexist()
        {
string[] str = { "@catname", "@proname" };       
string[] obj = { comboproductname.Text, comboitemname.Text };
SqlDataReader sdrr = sho.GetReaderByCmd("sp_item_alreadyex", str, obj);
if (sdrr.Read())
{
    sdrr.Close();
    sho.CloseConnection();
    return true;
}
else
{
    sdrr.Close();
    sho.CloseConnection();
    return false;
}

存储过程：

Create procedure [dbo].[sp_item_alreadyex]
    @catname nvarchar(50),
    @proname nvarchar(50)
as
begin
    select * 
    from Item 
    where Item_Name = @proname and Category = @catname
end

相关内容

最新更新

热门标签：