使用存储过程检查asp.net中是否存在用户名并将值添加到Registration.aspx(Check if username exist in asp.net using stored proce

使用存储过程检查asp.net中是否存在用户名并将值添加到Registration.aspx(Check if username exist in asp.net using stored procedure and adding values to Registration.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所做的。

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

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

知道我在这里缺少什么吗？

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()); }

I've created a stored procedure in SQL Server to check if username exists in the database:

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

Here is what I've done in Visual Studio Registration.aspx.cs.

I need to check if the username exists and if not to insert the required values into db to register a new user.

From the code below it keeps registering new users with the same username again and again.

Any idea what am I missing here?

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方法这样的单独方法中将insert into db part中。

但是主要的是你需要在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; } }

It's better use cmd.ExecuteScalar since the sp is returning either 1 or 0.

And it would be better to have the insert into db part in a separate method like RegisterUser method.

But the main thing is you need to call that method when it doesn't exist in db (in the else statement)

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; } }