在MS Visual Studio中的C#中在ASP.NET中注册页面

Question

我正在努力让我的注册页面在C#中的asp.net中工作。当我运行代码时，我不断地出现错误。我创建了一个带有点击事件的按钮。

protected void btn_Register_Click(object sender, EventArgs e)
{
try
{
SqlConnection Regconn = new SqlConnection(
// when logging in, use the following
//"Data Source=LAPTOP-MHFEI4S3;Initial Catalog=db_OnlineBookClub; User ID=sa; Password=p@55word;");
// with windows authentication use the following
"Data Source=LAPTOP-MHFEI4S3;Initial Catalog=db_OnlineBookClub;Integrated Security=SSPI;");
//opening database connection                
Regconn.Open();
//creating CRUD statement
SqlCommand cmdReg = Regconn.CreateCommand();
cmdReg.CommandText = @"INSERT INTO tbl_userInfo (name, surname, age, email, pword, securityQuestion, securityAnswer) 
VALUES('" + txt_Name.Text + "', '" + txt_Surname.Text + "', '" + 
txt_Age.Text + "', '" + txt_Email.Text + "', '" + txt_Password.Text + "', '" + 
txt_Q.Text + "', '" + txt_A.Text + "')";
cmdReg.ExecuteNonQuery();
//closing database connection
Regconn.Close();
Response.Redirect("Home.aspx");
}
catch (Exception err)
{
lbl_RegError.Text = err.Message;
}
}

显然，我插入的CRUD语句中有一个错误。

Answer 1

我建议您使用这种方法。当文本框为空时，它们不是空的，所以真的不需要编写代码来检查它们。

string strCon = "Data Source = LAPTOP - MHFEI4S3; Initial Catalog = db_OnlineBookClub; Integrated Security = SSPI; ";
string strSQL = "INSERT INTO People " +
"(name, surname, age, email, pword, securityQuestion, securityAnswer) " +
"VALUES (@tName, @surname, @age, @email, @pword, @Q, @A)";
using (SqlCommand cmdSQL = new SqlCommand(strSQL,new SqlConnection(strCon)))
{
cmdSQL.Parameters.Add("@tName", SqlDbType.NVarChar).Value = txt_Name.Text;
cmdSQL.Parameters.Add("@surname", SqlDbType.NVarChar).Value = txt_Surname.Text;

if (txtAge.Text == "") { txtAge.Text = "0"; }
cmdSQL.Parameters.Add("@Age", SqlDbType.Int).Value = txt_Age.Text;
cmdSQL.Parameters.Add("@email", SqlDbType.NVarChar).Value = txt_Email.Text;
cmdSQL.Parameters.Add("@pword", SqlDbType.NVarChar).Value = txt_Password.Text;
cmdSQL.Parameters.Add("@Q", SqlDbType.NVarChar).Value = txt_Q.Text;
cmdSQL.Parameters.Add("@A", SqlDbType.NVarChar).Value = txt_A.Text;
cmdSQL.Connection.Open();
cmdSQL.ExecuteNonQuery();

}

您在上面注意到：

Strong data types - no need for messy string concatenations
No sql injection issues.
Strong casting of types - automatic for you. 
Easy to read - easy to add MORE values, or remove them
Used ctrl-d in editor to repeat the first line - so I actually typed LESS code.
Using block cleans up - closes connection for you.
No need to create a connection object - sqlcommand has one
No need to create or set command text - sqlcommand has one.
And, sqlcommand also has a reader - so if needed, you don't create either.

Answer 2

请使用参数来防止SQL注入。如果值为null，则需要将参数的值设置为DBNull.Value。使用using语句，确保连接正确关闭。每次操作后还要关闭连接。

尝试以下操作：

添加以下使用语句：

using System.Data;
using System.Data.SqlClient;

注册：

private void Register(string name, string surname, string age, string email, string encryptedPwd, string securityQuestion, string encryptedSecurityAnswer, string connectionStr)
{
int rowsAffected = 0;
string sqlText = "INSERT INTO tbl_userInfo(name, surname, age, email, pword, securityQuestion, securityAnswer) VALUES(@name, @surname, @age, @email, @pword, @securityQuestion, @securityAnswer);";

try
{
using (SqlConnection con = new SqlConnection(connectionStr))
{
//open connection
con.Open();
using (SqlCommand cmd = new SqlCommand(sqlText, con))
{
//name
if (!String.IsNullOrEmpty(name))
{
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
}
else
{
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = DBNull.Value;
}
//surname
if (!String.IsNullOrEmpty(surname))
{
cmd.Parameters.Add("@surname", SqlDbType.NVarChar).Value = surname;
}
else
{
cmd.Parameters.Add("@surname", SqlDbType.NVarChar).Value = DBNull.Value;
}
//age
if (!String.IsNullOrEmpty(age))
{
cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = age;
}
else
{
cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = DBNull.Value;
}
//email
if (!String.IsNullOrEmpty(email))
{
cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
}
else
{
cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = DBNull.Value;
}
//pword
if (!String.IsNullOrEmpty(encryptedPwd))
{
cmd.Parameters.Add("@pword", SqlDbType.NVarChar).Value = encryptedPwd;
}
else
{
cmd.Parameters.Add("@pword", SqlDbType.NVarChar).Value = DBNull.Value;
}
//securityQuestion
if (!String.IsNullOrEmpty(securityQuestion))
{
cmd.Parameters.Add("@securityQuestion", SqlDbType.NVarChar).Value = securityQuestion;
}
else
{
cmd.Parameters.Add("@securityQuestion", SqlDbType.NVarChar).Value = DBNull.Value;
}
//securityAnswer
if (!String.IsNullOrEmpty(encryptedSecurityAnswer))
{
cmd.Parameters.Add("@surname", SqlDbType.NVarChar).Value = encryptedSecurityAnswer;
}
else
{
cmd.Parameters.Add("@securityAnswer", SqlDbType.NVarChar).Value = DBNull.Value;
}
//execute; returns the number of rows affected
rowsAffected = cmd.ExecuteNonQuery();
}
}
Response.Redirect("Home.aspx");
}
catch(SqlException ex)
{
lbl_RegError.Text = "Error: " + ex.Message;
//uncomment the following for debugging
//throw ex;
}
catch (Exception ex)
{
lbl_RegError.Text = "Error: " + ex.Message;
//uncomment the following for debugging
//throw ex;
}
}

有关连接字符串的详细信息，请参阅连接字符串。