none
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();

    Thursday, June 26, 2008 2:00 AM

Answers

  • 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
    • Proposed as answer by ErSehmi Thursday, June 26, 2008 6:03 AM
    • Marked as answer by jack 321 Monday, June 30, 2008 6:21 AM
    Thursday, June 26, 2008 6:02 AM

All replies

  • 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
    • Proposed as answer by ErSehmi Thursday, June 26, 2008 6:03 AM
    • Marked as answer by jack 321 Monday, June 30, 2008 6:21 AM
    Thursday, June 26, 2008 6:02 AM
  • 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...
    Monday, December 14, 2009 2:37 AM
  • 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();
    }


    Monday, December 14, 2009 2:49 AM
  • Very helpful! Thanks ErSehmi!
    Friday, February 25, 2011 10:29 AM
  •   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) 

    Friday, April 15, 2011 10:20 AM
  • What is this stUsername='" + textBoxUsername.Text + "'

    Use parameters.

    Friday, April 15, 2011 11:33 AM
  • 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

    Saturday, March 24, 2012 4:26 AM
  • 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?

    Thursday, July 12, 2012 9:10 AM
  • 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);

    Thursday, July 12, 2012 1:49 PM
  • 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!

    Tuesday, July 17, 2012 1:38 PM
  • using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace loginform
    {
        public partial class Form1 : Form
        { 
            SqlConnection con=new SqlConnection("server=.;user id=sa;password=123;database=login");

            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                try
                {

                    con.Open();
                    string str = "select * from login where username='" + textBox1.Text +"' and password='" + textBox2.Text + "'";
                    SqlCommand cmd = new SqlCommand(str, con);
                    SqlDataReader dr = cmd.ExecuteReader();
                    string login = textBox1.Text;
                    string pwd = textBox2.Text;
                    while (dr.Read())
                    {
                        if ((dr["username"].ToString() == login) && (dr["password"].ToString() == pwd))
                        {
                            MessageBox.Show("login sucessfully");
                        }
                        else
                        {
                            MessageBox.Show("invalid username password");
                        }
                    }


                    dr.Close();
                    con.Close();
                 
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
               // finally
                {
                   
                }

                
            }
        }
    }
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace loginform
    {
        public partial class Form1 : Form
        { 
            SqlConnection con=new SqlConnection("server=.;user id=sa;password=123;database=login");

            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                try
                {

                    con.Open();
                    string str = "select * from login where username='" + textBox1.Text +"' and password='" + textBox2.Text + "'";
                    SqlCommand cmd = new SqlCommand(str, con);
                    SqlDataReader dr = cmd.ExecuteReader();
                    string login = textBox1.Text;
                    string pwd = textBox2.Text;
                    while (dr.Read())
                    {
                        if ((dr["username"].ToString() == login) && (dr["password"].ToString() == pwd))
                        {
                            MessageBox.Show("login sucessfully");
                        }
                        else
                        {
                            MessageBox.Show("invalid username password");
                        }
                    }


                    dr.Close();
                    con.Close();
                 
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
               // finally
                {
                   
                }

                
            }
        }
    }
                                                                                    
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace loginform
    {
        public partial class Form1 : Form
        { 
            SqlConnection con=new SqlConnection("server=.;user id=sa;password=123;database=login");

            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                try
                {

                    con.Open();
                    string str = "select * from login where username='" + textBox1.Text +"' and password='" + textBox2.Text + "'";
                    SqlCommand cmd = new SqlCommand(str, con);
                    SqlDataReader dr = cmd.ExecuteReader();
                    string login = textBox1.Text;
                    string pwd = textBox2.Text;
                    while (dr.Read())
                    {
                        if ((dr["username"].ToString() == login) && (dr["password"].ToString() == pwd))
                        {
                            MessageBox.Show("login sucessfully");
                        }
                        else
                        {
                            MessageBox.Show("invalid username password");
                        }
                    }


                    dr.Close();
                    con.Close();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
               // finally
                {

                }


            }
        }
    }
    login form with c# and sql serverusing System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace loginform
    {
        public partial class Form1 : Form
        { 
            SqlConnection con=new SqlConnection("server=.;user id=sa;password=123;database=login");

            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                try
                {

                    con.Open();
                    string str = "select * from login where username='" + textBox1.Text +"' and password='" + textBox2.Text + "'";
                    SqlCommand cmd = new SqlCommand(str, con);
                    SqlDataReader dr = cmd.ExecuteReader();
                    string login = textBox1.Text;
                    string pwd = textBox2.Text;
                    while (dr.Read())
                    {
                        if ((dr["username"].ToString() == login) && (dr["password"].ToString() == pwd))
                        {
                            MessageBox.Show("login sucessfully");
                        }
                        else
                        {
                            MessageBox.Show("invalid username password");
                        }
                    }


                    dr.Close();
                    con.Close();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
               // finally
                {

                }


            }
        }
    }

    Wednesday, June 19, 2013 8:19 PM