C# Project help

Akuma

Golden Oldie
Golden Oldie
Dec 6, 2006
628
15
125
hey im using visual studio 2012, .net framework 4.5 and have been searching online everythwere and cannot find the solution to my problem

i am creating a login system, using windows forms as client and connecting to wcf service.

my db has IDX which is unique primary key, i also need my username to be unique, this is where i am struggling, i cannot find code or write code to check if username exists already in db in sql 2012

any info would be great ty

VirUs
 

Far

tsniffer
Staff member
Developer
May 19, 2003
20,172
30
2,767
540
This is taken from my php login system, but as Haz said the principle is always the same

//Check for duplicate login ID
if($Username != '') {
$qry = "SELECT * FROM user_account WHERE username='$Username'";
$result = mysql_query($qry);
if($result) {
if(mysql_num_rows($result) > 0) {
$errmsg_arr[] = 'Login ID already in use';
$errflag = true;
}
@mysql_free_result($result);
}
else {
die("Query failed");
}
}

If you're struggling with that (i understand its not formatted correctly because of the forum)

Put the username the user entered in to a variable $username
Run an SQL statement to return all results with that login name
If the results returned is greater than 0 (IE it already exists) tell the user it exists
If no results returned, the user is free to use that name
 
Last edited:

Akuma

Golden Oldie
Golden Oldie
Dec 6, 2006
628
15
125
hey thanks for replies, been trying that but for somereason it doesnt seem to read the query first and keeps inserting this is my code

string Message;
SqlConnection con = new SqlConnection("blah blah);
con.Open();

string username = userInfo.UserName;
string password = userInfo.Password;
string country = userInfo.Country;
string email = userInfo.Email;
string sqlquery = ("if not exists(select * from RegistrationTable where username =@UserName)");

sqlquery = "INSERT INTO RegistrationTable (UserName, Password, Country, Email) VALUES ('" + userInfo.UserName + "','" + userInfo.Password + "','" + userInfo.Country + "','" + userInfo.Email + "')";
SqlCommand command = new SqlCommand(sqlquery, con);

command.Parameters.AddWithValue("@UserName", userInfo.UserName);
command.Parameters.AddWithValue("@Password", userInfo.Password);
command.Parameters.AddWithValue("@Country", userInfo.Country);
command.Parameters.AddWithValue("@Email", userInfo.Email);
int result = command.ExecuteNonQuery();

if (result == 1)
{
Message = userInfo.UserName + " Details inserted successfully";
}
else
{
Message = userInfo.UserName + " Details not inserted successfully";
}
con.Close();
return Message;
 

Far

tsniffer
Staff member
Developer
May 19, 2003
20,172
30
2,767
540
if thats exactly how your code reads it's likely because the INSERT statement will fire regardless of the outcome of the @username existing.

i would advise to count the rows.

string sqlquery = SELECT COUNT(*) from RegistrationTable WHERE username = @username;
if(sqlquery > 0){
console.writeline("username already exists");
}
else{
INSERT INTO TABLE HERE
}

This won't work because the text in red is wrong, but i hope it gives you the idea. Unfortunately i dont have visual C# installed on this machine to test it.

Take a look here, it shows you how to correctly count the rows.

http://www.rhyous.com/2009/09/21/how-do-i-get-the-number-of-rows-returned-from-a-sql-query-in-c/

IF NOT EXISTS may work (like you've used) however you'll still need it inside an IF statement so it skips if it does exist.
 
Last edited:

Coly

LOMCN Developer
Developer
Mar 31, 2005
1,399
33
195
Germany
Do it direct in the Database with stored procedure, it is faster and safe...
For MS-SQL 2012 stored procedure you can find many information by Google
 

Akuma

Golden Oldie
Golden Oldie
Dec 6, 2006
628
15
125
cheers guys will give these a ry and let use know how i get on if i get sorted ill post code so people in future can see it to solve their problem

VBirUs
 

02goswej

Golden Oldie
Golden Oldie
Jul 12, 2004
690
0
123
United Kingdom
Take a look at Entity Framework, it will allow you to communicate with your database as if it were an instantiated object using LINQ.

In simple terms you create an EDMX file which is generally an automatically generated mirror of your database but in classes. You can then do the following (may not be syntactically correct) to check if a username exists.

Code:
string strUserId; //The user ID you're checking exists.

using (YourDatabaseContext _context = new YourDatabaseContext())
{[INDENT]bool bAccountExists = _context.Account.Count(x => x.UserID.Equals(strUserId)) != 0[/INDENT]
}

Alternatively you can simply make the field unique in your database and check for an error in the transaction after querying the database.

John
 
Last edited: