Prevent duplicate username insert wcf help

hey im using vs 2012 and sql 2012 .net framework 4.5

i have searched loads now and found different examples and tried different samples and have found nothing that works. my db has a primary key which is the idx i also want the username to be unique which i cannot do

im using windows forms connecting to a wcf service

sample code im using in the wcf to add to the db is:#

public DataSet SelectUserDetails()
{

SqlConnection con = new SqlConnection("blah blah");

con.Open();

SqlCommand cmd = new SqlCommand("Select * from RegistrationTable", con);

SqlDataAdapter sda = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

sda.Fill(ds);

cmd.ExecuteNonQuery();

con.Close();

return ds;

}



public string InsertUserDetails(UserDetails userInfo)
{

string Message;

SqlConnection con = new SqlConnection("blah blah");

con.Open();

SqlCommand cmd = new SqlCommand("insert into RegistrationTable(Username,Password) values(@UserName,@Password)", con);

cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);

cmd.Parameters.AddWithValue("@Password", userInfo.Password);

int result = cmd.ExecuteNonQuery();

if (result == 1)
{

Message = userInfo.UserName + " Details inserted successfully";

}

else
{

Message = userInfo.UserName + " Details not inserted successfully";

}

con.Close();

return Message;

}
}

}
my windows form code is as follows#

private void button1_Click(object sender, EventArgs e)
{
ServiceReference1.UserDetails objuserdetail = new ServiceReference1.UserDetails();

objuserdetail.UserName = textBox1.Text;

objuserdetail.Password = textBox2.Text;

obj.InsertUserDetails(objuserdetail);

}






any help will be amazing, like i said been searching for ages, and am lost as to what to do

regards

Dean

1. Put a "Unique" constraint on the username column in the database table. Then add try/catch for SQLException. In catch, prompt user that username is not unique and allow them to try again.

2. Check before updating by querying the table in the database to see if the username is already there. If username already exists in the database, prompt user that username is not unique and allow them to try again.

you can use both approaches in tandem.

#1 is the safer approach, as it checks at the database before allowing the update (no multi-threading contention issues).

hey thanks for response i now have my idx as unique and my username set to unique too. i tested it and it threw an exception,
only thing is now i get this error and cant work it out

Error 1 'WcfService1.Service1.InsertUserDetails(WcfService1.UserDetails)': not all code paths return a value

my code is



public string InsertUserDetails(UserDetails userInfo)
{
string connection = @"blah blah blah";
SqlConnection con = new SqlConnection(connection);

try
{
con.Open();
if (con.State.Equals(System.Data.ConnectionState.Open))
{
try
{
SqlCommand cmd = new SqlCommand("insert into Query(UserName,Password,Country,Email) values(@UserName,@Password,@Country,@Email)", con);

cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);

cmd.Parameters.AddWithValue("@Password", userInfo.Password);

cmd.Parameters.AddWithValue("@Country", userInfo.Country);

cmd.Parameters.AddWithValue("@Email", userInfo.Email);
int result = cmd.ExecuteNonQuery();

if (result == 1)
{ //next statement
con.Close(); }

else
{ //next statement
con.Close(); }



}

catch (SqlException exception)
{
if (exception.Number == 2601) //2627)
{
throw new FaultException("Sorry Username Is Taken.", new FaultCode("Insert Fault"));
}
else
{ }
}
catch (Exception)
{
throw new FaultException("There was a problem connecting to the database.", new FaultCode("ConnectionFault"));
}
}

}
finally
{ }
}
}
}



any advice will be great ty

Dean

You have InsertUserDetails declared as returning a string, but are not returning anything. Define it as follows:

public void InsertUserDetails(UserDetails userInfo)

ah thank you much appreciated will try this when i get hme from work and let use know how it goes

Regards

Dean