CODEWITHSUNDEEP
asp.netsqlado.net
Dmitry Makovetskiyd
Dmitry Makovetskiyd

Reputation: 7053

Question about inserting Users/Members into a database table!

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

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).

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!!!

Upvotes: 1

Views: 196

Answers (1)

marc_s
marc_s

Reputation: 754388

I would change two things mainly:

  1. 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)

  2. 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!).

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

Upvotes: 2

Related Questions