Login Form using C# and SQL

Question

HI,

I'm new to c#. I would like to know how I can create a login form for my application. The login form must use a SQL server 2005 express edition database (in which the username and password and roles are stored). The login form must validate the user using the database and allow them to access the program...

I would also like to know how I could implement the feature of 'roles' in my program. For example, if the user logged in is of 'admin' role he should be able to access all of the program's feature. But if the user logged in is of 'accounts' role he must only be able to access the payroll feature.

Here is the code I developed for the login page... but it doesn't work... Can anyone please help me?

SqlConnection UGIcon = new SqlConnection();
UGIcon.ConnectionString = "Data Source=HP-PC\\SQLEXPRESS;Initial Catalog=UGI;Integrated Security=True"
UGIcon.Open();

SqlCommand cmd = new SqlCommand("SELECT stUsername,stPassword FROM LoginDetails WHERE stUsername='" + textBoxUsername.Text + "' and stPassword='" + textBoxPassword.Text + "'", UGIcon);

SqlDataReader dr = cmd.ExecuteReader();

string userText = textBoxUsername.Text;
string passText = textBoxPassword.Text;

while (dr.Read())
{
    if ((dr["stUsername"].ToString() == userText) && (dr["stPassword"].ToString() == passText))
    {
        MessageBox.Show("OK");
    }
    else
    {
        MessageBox.Show("Error");
        SqlCommand cmd2 = new SqlCommand("select stRole FROM LoginDetails WHERE stUsername='" + textBoxUsername.Text + "'", UGIcon);

dr = cmd2.ExecuteReader();

while (dr.Read())
{
    if (dr["stRole"].ToString() == "Admin")
    {
        MessageBox.Show("Role = Admin");
    }
    else
    {
        MessageBox.Show("Role = Other");
    }

}

}

}

dr.Close();

UGIcon.Close();

Answer 1

Hi,

I have modified your code, and it is working as per your mentioned requirements.

I would like to suggest you that the String comparisions that you have made are not good by practice . So i have also changed them.

Another that I would like to suggest you that place your Connection String in the App.Config file

1	private bool CompareStrings(string string1, string string2)
2	{
3	return String.Compare(string1, string2, true, System.Globalization.CultureInfo.InvariantCulture) == 0 ? true : false;
4	}
5
6	private void button1_Click(object sender, System.EventArgs e)
7	{
8	try
9	{
10	SqlConnection UGIcon = new SqlConnection();
11	UGIcon.ConnectionString = "Server=.\\SQLEXPRESS; Database=Sample; User Id=sa; password=sa123";
12	UGIcon.Open();
13
14	SqlCommand cmd = new SqlCommand("SELECT ISNULL(stUsername, '') AS stUsername, ISNULL(stPassword,'') AS stPassword, ISNULL(stRole,'') AS stRole FROM LoginDetails WHERE stUsername='" + textBoxUsername.Text + "' and stPassword='" + textBoxPassword.Text + "'", UGIcon);
15
16	SqlDataReader dr = cmd.ExecuteReader();
17
18	string userText = textBoxUsername.Text;
19	string passText = textBoxPassword.Text;
20	string stRole = "admin";
21
22	while (dr.Read())
23	{
24	if (this.CompareStrings(dr["stUsername"].ToString(), userText) &&
25	this.CompareStrings(dr["stPassword"].ToString(), passText) &&
26	this.CompareStrings(dr["stRole"].ToString(), stRole))
27	{
28	MessageBox.Show("OK");
29	}
30	else
31	{
32	MessageBox.Show("Error");
33	}
34
35	}
36
37	dr.Close();
38
39	UGIcon.Close();
40
41	}
42	catch(Exception ex)
43	{
44	MessageBox.Show(ex.Message);
45	}
46	}

All you need to do is change the connection string as per your settings.
Regards,
ErSehmi

---

Like farmers we need to learn that we cannot sow & reap the same day

Answer 2

Hi!

I would like to say thanks to ersehmi for the wonderful code!

but, if ever i enter a wrong input for username and/or password,
the ELSE statement does NOT execute (it will NOT show a messagebox which will say "error").

im a c# newbie too, but i believe there are some "eof" stuff that will be involved here.

please help...
thanks...

Answer 3

Small and Compact Version of your code is  here

SqlConnection UGIcon = new SqlConnection();
UGIcon.ConnectionString = "Data Source=HP-PC\\SQLEXPRESS;Initial Catalog=UGI;Integrated Security=True"
UGIcon.Open();

string userText = textBoxUsername.Text;
string passText = textBoxPassword.Text;

SqlCommand cmd = new SqlCommand("SELECT stUsername,stPassword FROM LoginDetails WHERE stUsername='" + textBoxUsername.Text + "' and stPassword='" + textBoxPassword.Text + "'", UGIcon);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);

if ( dt.Rows.Count > 0)
{
   MessageBox.Show("Login Sucess!!");
   cmd = new SqlCommand("SELECT stRole from LoginDetails where stUsername=@stUsername",UGI);
   cmd.Parameters.AddWithValue("@stUsername",userText);
   string role = cmd.ExecuteScalar().ToString();
   MessageBox.Show(role);
   UGI.Close();
}
else 
{
   MessageBox.Show("Access Denied!!");
   UGI.Close();
}

Answer 4

Very helpful! Thanks ErSehmi!

Answer 5

  catch(Exception ex)  Hi,

I have modified your code, and it is working as per your mentioned requirements.

I would like to suggest you that the String comparisions that you have made are not good by practice . So i have also changed them.

Another that I would like to suggest you that place your Connection String in the App.Config file

1	private bool CompareStrings(string string1, string string2)
2	{
3	return String.Compare(string1, string2, true, System.Globalization.CultureInfo.InvariantCulture) == 0 ? true : false;
4	}
5
6	private void button1_Click(object sender, System.EventArgs e)
7	{
8	try
9	{
10	SqlConnection UGIcon = new SqlConnection();
11	UGIcon.ConnectionString = "Server=.\\SQLEXPRESS; Database=Sample; User Id=sa; password=sa123";
12	UGIcon.Open();
13
14	SqlCommand cmd = new SqlCommand("SELECT ISNULL(stUsername, '') AS stUsername, ISNULL(stPassword,'') AS stPassword, ISNULL(stRole,'') AS stRole FROM LoginDetails WHERE stUsername='" + textBoxUsername.Text + "' and stPassword='" + textBoxPassword.Text + "'", UGIcon);
15
16	SqlDataReader dr = cmd.ExecuteReader();
17
18	string userText = textBoxUsername.Text;
19	string passText = textBoxPassword.Text;
20	string stRole = "admin";
21
22	while (dr.Read())
23	{
24	if (this.CompareStrings(dr["stUsername"].ToString(), userText) &&
25	this.CompareStrings(dr["stPassword"].ToString(), passText) &&
26	this.CompareStrings(dr["stRole"].ToString(), stRole))
27	{
28	MessageBox.Show("OK");
29	}
30	else
31	{
32	MessageBox.Show("Error");
33	}
34
35	}
36
37	dr.Close();
38
39	UGIcon.Close();
40
41	}
42	catch(Exception ex)
43	{
44	MessageBox.Show(ex.Message);
45	}
46	}

All you need to do is change the connection string as per your settings.
Regards,
ErSehmi

---

Like farmers we need to learn that we cannot sow & reap the same day

what is this:

catch(Exception ex) 

Answer 6

What is this stUsername='" + textBoxUsername.Text + "'

Use parameters.

Answer 7

Hi ErSehmi,

I am a student and this code looks really good, but i am having trouble implementing it using a DAO and facade object with the form..can you offer any assistance please..?

Thanks

Answer 8

hi ErSehmi,

What if i have a table user and the password column data type is binary(16) how do i code that? and also using MD5Enc.MD5Encryption?

Answer 9

When you do codes like that, always prefer to pass params, or at least using string.Format, passing your params as you do, its easier to injection.

string.Format("select * from bla where bla1 = {0} and bla2 = {1}",param1,param2);

Answer 10

If the connection to the database fails - it will throw an error (exception) as it is in the Try {} Catch{} block it will be caught and a message box will show the exception.

"ex" will contain the actual message = ex.Message();

---

Digital Forensic Software Developer
CCS LABS Digital Forensic Software
Mark as Answer or Vote up if useful thank you!