关于将用户/成员插入数据库表的问题！

My registration form has got a CreateUserWizard. I used its event that is fired after the user is created.

我的注册表有一个CreateUserWizard。我使用了在创建用户后触发的事件。

Then I obtain the users identity and key. In the last line, I send the unique key to a function inside a class that should insert the key into the Users table (the field is a primary key and is unique).

然后我获取用户身份和密钥。在最后一行中,我将唯一键发送到类中的一个函数,该类应该将键插入Users表(该字段是主键并且是唯一的)。

public partial class Registration : System.Web.UI.Page
{
    protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
    {
        MembershipUser CurrentUser = Membership.GetUser(User.Identity.Name);
        int i =(int) CurrentUser.ProviderUserKey;
        RegisterAdo.InsertUsers(i);
    }
}

Below, I execute the query with the value that I passed and insert the user into a database

下面,我使用我传递的值执行查询并将用户插入数据库

class RegisterAdo
{
    public static void InsertUsers(int UsersIDentity)
    {
        string myConnectionString = WebConfigurationManager.ConnectionStrings["YourGuruDB"].ConnectionString;
        SqlConnection sqlConnect = new SqlConnection(myConnectionString);
        SqlCommand sqlCommand = new SqlCommand(RegisterAdo.insertCommand(UsersIDentity), sqlConnect);
        try
        {
            sqlConnect.Open();
            sqlCommand.ExecuteNonQuery();
        }
        catch (Exception x)
        {

        }
        finally
        {
            sqlConnect.Close();
        }
    }

public static String insertCommand(int UsersIdentityToinsert)
{
    string insertCommand="INSERT INTO Users(";
    insertCommand += "UserID)";
    insertCommand += "VALUES('";
    insertCommand += UsersIdentityToinsert+"')";

    return insertCommand;
}

My question is whether it is the best way to insert UserID into a table, and whether I do it right at all. I need the UserID to be unique, and the whole command executed with no fail...(just after the user was created and the whole UserCreateUser finished validating the user!!!

我的问题是它是否是将UserID插入表中的最佳方式,以及我是否正确执行。我需要UserID是唯一的,并且整个命令执行时没有失败......(就在创建用户并且整个UserCreateUser完成验证用户之后!

1 个解决方案

#1

I would change two things mainly:

我会改变两件事:

don't concatenate together your SQL statement - this opens doors to SQL injection attacks. Use parametrized queries instead - they are both safer, and they perform better (since only a single copy of the query's execution plan needs to be created and cached and will be reused over and over again)

不要将SQL语句连接在一起 - 这为SQL注入攻击打开了大门。使用参数化查询 - 它们更安全,并且性能更好(因为只需要创建和缓存查询执行计划的单个副本,并且将一遍又一遍地重复使用)
put your SqlConnection and SqlCommand objects into using blocks so that they'll be automatically freed / disposed when the using blocks ends (and you can save yourself the finally block of the try...catch construct, too!).

将您的SqlConnection和SqlCommand对象放入使用块中,以便在使用块结束时自动释放/处置它们(并且您也可以自己保存try ... catch构造的finally块!)。

So my code would look like this

所以我的代码看起来像这样

    public static void InsertUsers(int UsersIDentity)
    {
        string myConnectionString = WebConfigurationManager.ConnectionStrings["YourGuruDB"].ConnectionString;

        string insertStmt =
           "INSERT INTO dbo.Users(UserID) VALUES(@UserID)";

        using(SqlConnection _con = new SqlConnection(myConnectionString))
        using(SqlCommand _cmd = new SqlCommand(insertStmt, sqlConnect))
        {
           _cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = UsersIDentity;

          try
          {
              _con.Open();
              _cmd.ExecuteNonQuery();
              _con.Close();
          }
          catch (Exception x)
          {
               // do something if error occurs
          }
      }

#1