How to prevent duplicate entries in SQL Database using Stored procedures in C#.net

I'm trying to make a simple user registration form with the following 4 fields,

• userid ( which is unique)
• username ( which is unique)
• password
• email

Now i'm able to prevent us开发者_开发技巧er from entering duplicate user entries in the Sql Database. But want to fire an event where the message can be successfully displayed to the user that the account 'already exists' when user provides the same userid/username or 'account creation successful' when the userid/username does not pre-exist.

Please provide some C# code help (asp.net) to solve this issue. Thanks.

Can't you just use a stored procedure that will test and insert the user and also return an output parameter with the result?

CREATE PROC addUser @UserID decimal, @Username varchar()..., @Result varchar(50) output
as
if exists(Select UserId from Users  where username = @Username)
begin
 set @Result = 'Already there'
 return
end

insert Users ....
set @Result= 'Success'

You can handle the check before the insertion.

To do this, you can use an event such as an OnItemInserting event with a DetailsView, use the values the user entered to check if the username exists and cancel the insert if it does. You can use the OnItemInserted event to confirm the new account afterward.

You should definitely do additional input checks and check values, etc but the below is just pseudo code to get you in the right direction.

Take a look at these examples OnItemInserting http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.detailsview.iteminserting.aspx

OnItemInserted http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.detailsview.iteminserted.aspx

Example (pseudo code)

<asp:DetailsView ID="NewAccount" runat="server"
    DataSourceID="Account"
    AutoGenerateRows="false"
    OnItemInserted="NewAccount_ItemInserted"
    OnItemInserting="NewAccount_ItemInserting">
    <Fields>
      <asp:BoundField DataField="UserName" HeaderText="UserName" />
      <asp:TemplateField HeaderText="Password">
          <InsertItemTemplate>
            <%-- Put your password boxes here --%>
          </InsertItemTemplate>
      </asp:TemplateField>
      <asp:BoundField DataField="Email" HeaderText="Email" />
    </Fields>
  </asp:DetailsView>

The code behind

void NewAccount_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
  {
    // Account successfully inserted
    PanelAccountSuccessful.Visible = true; // Show the successful message

  }

  void NewAccount_ItemInserting(object sender, DetailsViewInsertEventArgs e)
  {
    // Check for account that exists
    SqlConnection ....
    SqlDataSource ....

    //use e.Values["key"] to compare
    // select an account that matches e.Values["UserName"] and/or e.Values["Email"]
    SqlDataReader reader ....

    while (reader.read())
    {
        // If you returned results then the account exists
        PanelAccountExists.Visible = true; // Show the error message that the account exists
        e.Cancel = true; // This cancels the insert
    }

    // Otherwise this will fall through and do the insert
    // Check that the passwords match and any other input sanitation you need
    if (Password1.Text.Trim() != Password2.Text.Trim())
        e.Cancel = true; // Cancel if passwords don't match
  }