Making a SQL Database login

[GAME]

Could anyone point me into the right direction on how to make a C# SQL Datalogin?

[mcriscolo]

Start here:

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx

[edgias]

using System.Data.SqlClient;

SqlConnection conn=new Sqlconnection("YourConnectionString");
conn.Open();
string qry="SELECT * FROM Tablename";
Sqlcommand cmd=new SqlCommand(qry,conn);
bool userFound=false;
SqlDatareader dr;
dr=cmd.ExecuteReader();
while(dr.Read())
{
if(user==user.Text&&pass=pass.Text)
{
userFound=true;
break;
}
}
if(userFound)
{
//Do Something e.g Response.Redirect("ToSomePage.aspx");
}
else
{

}

[Singlem]

Code above is very unsafe and has many possibilities to fail. Better to use try/catch/finally block for connection to the database and use a more "Where statement query"

using System.Data.SqlClient;

        private void btnCompile_Click(object sender, EventArgs e)
        {
            string qry = "SELECT Password FROM Tablename WHERE User=" + edtUserName.Text;
            using (SqlConnection conn = new SqlConnection("YourConnectionString"))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(qry, conn);
                    SqlDataReader reader;
                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            if (edtPassword.Text == reader["Passwrod"].ToString())
                            {
                                //what needs to happen if after this
                            }
                        }
                    }
                    else
                    {
                        MessageBox.Show("Username was not found", "Error",  MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error Opening Database", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                finally
                {
                    conn.Close();
                }
                
            }

}

[jugosoft]

Your code in vulnerabile with SQL injection. Always use parametarised queries:

public void checkUsername() {
            string qry = "SELECT Password FROM Tablename WHERE User=@username";
            using (SqlConnection conn = new SqlConnection("YourConnectionString"))
            {
                try
                {
                    conn.Open();

                    SqlCommand cmd = new SqlCommand(qry, conn);
                    cmd.Parameters.Add(new SqlParameter("username", userName.Text));
                    SqlDataReader reader;

                    reader = cmd.ExecuteReader();
 
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            if (edtPassword.Text == reader["Passwrod"].ToString())
                            {
                                //what needs to happen if after this
                            }
                        }
                    }
                    else
                    {
                        MessageBox.Show("Username was not found", "Error",  MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error Opening Database", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                finally
                {
                    conn.Close();
                }
 
            }
       }

[Singlem]

Thanks jugosoft, I always use stored procedures due to the injection fact, only been doing C# 3 months now...parametarised queries is better to use in all cases:)

[Mitja Bonca]

This is my code (I actually use it as well):

public static bool LogingInMethod(string userName, string password)
        {
            bool result = false;

            string sqlQuery = "SELECT UserName, Password FROM Users WHERE UserName ='" + userName + "'";
            using (SqlConnection sqlConn = new SqlConnection(p))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
                SqlDataReader reader;
                bool HasRows = false;
                try
                {
                    sqlConn.Open();
                    reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())

                            if (password == reader["Password"].ToString().Trim())
                            {
                                result = true;
                                break;
                            }
                        HasRows = true;
                    }
                    reader.Close();
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    sqlConn.Close();
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                    {
                        sqlConn.Close();
                    }
                    cmd = null;
                    reader = null;
                    GC.Collect();
                }

                if (result == true)
                {
                    result = true;
                }

                else if (HasRows == false)
                {
                    MessageBox.Show("Wrong userName.", "Eror", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    result = false;
                }
                else
                {
                    MessageBox.Show("Wrong password.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    result = false;
                }
            }
            return result;
        }

[GAME]

This is my code (I actually use it as well):

public static bool LogingInMethod(string userName, string password)
        {
            bool result = false;

            string sqlQuery = "SELECT UserName, Password FROM Users WHERE UserName ='" + userName + "'";
            using (SqlConnection sqlConn = new SqlConnection(p))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
                SqlDataReader reader;
                bool HasRows = false;
                try
                {
                    sqlConn.Open();
                    reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())

                            if (password == reader["Password"].ToString().Trim())
                            {
                                result = true;
                                break;
                            }
                        HasRows = true;
                    }
                    reader.Close();
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    sqlConn.Close();
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                    {
                        sqlConn.Close();
                    }
                    cmd = null;
                    reader = null;
                    GC.Collect();
                }

                if (result == true)
                {
                    result = true;
                }

                else if (HasRows == false)
                {
                    MessageBox.Show("Wrong userName.", "Eror", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    result = false;
                }
                else
                {
                    MessageBox.Show("Wrong password.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    result = false;
                }
            }
            return result;
        }

Would you happen to know how to set up a public sql database, so anyuser would be able to access it?

[Mitja Bonca]

That has to be a remote db. So everyone can access it. Some info.

[GAME]

I still don't get what to do.

[Singlem]

The connection is handled in here

#
using (SqlConnection conn = new SqlConnection("YourConnectionString"))

So you just need to set that up

public string YourConnectionString = "SERVER = localhost; DATABASE = DatabaseName; User ID = root; PASSWORD = password;";

[GAME]

How would I set up an online database?

[Singlem]

Google I found has many answers regarding this in the world

http://msdn.microsoft.com/en-us/library/dd857537(v=vs.85).aspx

Don't be afraid to use google

[shajis001]

http://en.csharp-online.net/Working_...s_using_OLE_DB

refer this u will get the basdic idea and then start...

[shajis001]

using System;
using System.Data;
using System.Data.OleDb;

namespace csbook.ch15 {

   class Example15_2 {

      static void Main(string[] args) {
         string conString =
              @"Provider=Microsoft.JET.OLEDB.4.0;"
            + @"data source=c:\data\Northwind.mdb";


         // create an open the connection          
         OleDbConnection conn = new OleDbConnection(conString);
         conn.Open();

         // create the DataSet
         DataSet ds = new DataSet();

         // create the adapter and fill the DataSet
         OleDbDataAdapter adapter = 
            new OleDbDataAdapter("Select * from Customers", conn);
         adapter.Fill(ds);

         // close the connection
         conn.Close();

         DataTable dt = ds.Tables[0];
         foreach (DataRow dr in dt.Rows) {
            Console.WriteLine(dr["CompanyName"].ToString());
            }
         }
      }
   }

The listing first creates a connection string that provides information to the OleDbConnection class. Specifically, the connection string contains the provider for the database engine that we want to connect to and the data source, which in this case is a Microsoft Access file. If the file were password protected, we would also specify a User ID and Password in the connection string. After creating the connection string, the program creates an OleDbConnection object, passing the connection string as the argument.

Next, the listing opens the database connection by calling the connection’s Open method. It also creates an empty DataSet that will later be filled from the database.

The OleDbAdapter class encapsulates the command to fill the DataSet. We create the adapter by passing a SQL query string and the database connection to its constructor. This query string will select all fields of all rows from the Customers table. The adapter’s Fill method will execute the query through the connection and load the results into the DataSet that we pass to it. The results include the data from the query as well as the metadata, defining its structure. When the call is complete, the DataSet will contain a table with all the Customer records from the database. At that point, we can close the database connection so that it can be used by some other application.

The tables within the DataSet are represented by the DataTable class. Each DataTable has a collection of DataRow objects storing the rows from the query. The foreach loop iterates through the DataRows, displaying the CompanyName field. The DataRow object uses the string "CompanyName" as an index to find the field of that name and retrieve its value. The value is returned as an object type. Calling the virtual ToString method will result in displaying the string value of the field

[GAME]

Does anyone have a method to insert a username, password and a email?

[Mitja Bonca]

What do you mean? A method in Win form to insert userName, password and an email to sql dataBase?
YOu have to pass the data from textBoxes to sql command parameters, end execute the procedure.
Try this code:

private void InsertingData()
        {
            string connString = @"server=x;uid=y;pwd=z;database=xyz"; //this is an example, you need your own
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                string query = String.Format(@"INSERT INTO Users VALUES (@id, @name, @password, @email)");
                using (SqlCommand cmd = new SqlCommand(query, sqlConn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1; //FIND YOUR NEW ID IF YOU HAVE THIS COLUMN IN DATABASE
                    cmd.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = textBox1.Text;
                    cmd.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = textBox2.Text;
                    cmd.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = textBox3.Text;
                    cmd.Connection.Open();
                    try { cmd.ExecuteNonQuery(); }
                    catch (Exception ex)
                    { 
                        MessageBox.Show(ex.Message); 
                    }
                    finally { cmd.Connection.Close(); }
                }
            }
        }

[GAME]

It worked. THANK YOU!!!