88376

C# Login Screen - Find User in SQL Table and Update Field

I am currently writing a small application to keep track of monetary ins and outs, something just to improve my general C# skills. For my Login Screen currently I have the following Code

private void Login_Load(object sender, EventArgs e) { // TODO: This line of code loads data into the 'spendingInsAndOutsDataSet.Users' table. You can move, or remove it, as needed. this.usersTableAdapter.Fill(this.spendingInsAndOutsDataSet.Users); } private void button1_Click(object sender, EventArgs e) { string userNameText = userName.Text; string passwordText = password.Text; foreach (DataRow row in spendingInsAndOutsDataSet.Users) { if (row.ItemArray[4].Equals(userNameText) && row.ItemArray[5].Equals(passwordText)) { MessageBox.Show("Login Successful"); MainGUI newForm = new MainGUI(); this.Visible = false; newForm.Show(); break; } else { userName.Text = String.Empty; password.Text = String.Empty; MessageBox.Show("Login Failed"); break; } } }

What I am looking to do when the Login is Sucessful is to write the MachineName of the current PC to a field in Users table in my SQL Database. That way when I come to start creating records I can quickly find my UsersId (which is a foreign key in my Transactions table).

I know you can get the Active Machine Name using the System.Enviroments path but Im unsure exactly how to go about writing the update. I know how to do it using a SqlCommand but I am wondering if there is a simpler way using the DataRows I have used within the ForEach loop.

Thanks in advance, any questions let me know.

James

Answer1:

Assuming it is an Access database (If not then make the necessary changes):

Use an Adapter to fill a table with your results. Then compare the row columns with the information provided by the user. Don't forget to use parameters to avoid injections that may potentially ruin your database or expose your user's information to a hacker.

DataTable dt = new DataTable(); String sql = "SELECT * FROM users WHERE user = @user and password=@password" OleDbConnection connection = getAccessConnection(); OleDbDataAdapter da = new OleDbDataAdapter(sql, connection); da.SelectCommand.Parameters.Add("@user", OleDbType.VarChar).Value = userNameText; da.SelectCommand.Parameters.Add("@password", OleDbType.VarChar).Value = password.Text; try { connection.Open(); da.Fill(dt); connection.Close(); } catch(OleDbException ex) { connection.Close(); MessageBox.Show(ex.ToString()); } if(dt.Rows.Count == 1) return true; //username && password matches else if(dt.Rows.Count == 0) return false; // does not match

You could also use AddWithValue for your parameters.

da.SelectCommand.Parameters.AddWithValue("@user", userNameText);

getAccessConnection() is a predefined OleDbConnection function that has the connection to the database setup and creates a new instance of the connection for you (that I have created for myself).

public OleDbConnection getAccessConnection() { this.connection = new OleDbConnection(); this.connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Classified.SOURCE + ";Jet OLEDB:Database Password=" + Classified.PASS + ";"; return this.connection; }

It is preferred to create classes for all of these functions for developers who may join in on the project. Also read up on C# test driven development as well.

Also it looks like your loop will break even if that one record fails, only allowing it to go to it's first record.

Onto creating your own data set and filling it with queried tables is also useful. Here is a brief example:

DataSet ds = new DataSet(); ds.Tables.Add(dt, "userSearchedTable"); ds.Tables["userSearchedTable"].Rows[0][1].ToString();

Then you can declare a specific data table within the set when ever you need to.

Answer2:

In your foreach loop, set the MachineName of the current PC on relevant row then at the end of the method call:

this.usersTableAdapter.Update(this.spendingInsAndOutsDataSet.Users);

This will update the database with the machine name

However looking at your code there are a few additional comments to make I'd like to add to improve what you have:

You are loading the entire data table and then checking it for the username and password. Really you query for the user ID in the database, load that single row and check the password. If you have many users, your current implementation will create a lot of network traffic.

Instead of:

foreach (DataRow row in spendingInsAndOutsDataSet.Users)

Consider using something like:

foreach (SpendingInsAndOutsDataSet.UsersRow row in spendingInsAndOutsDataSet.Users)

i.e. the strongly typed version of the data row object. This means you can use:

row.Username.Equals(userNameText)

instead of

row.ItemArray[4].Equals(userNameText)

Also if you are anticipating that this will be used over a network, you should look to encrypt the passwords.

Recommend

  • SQL update statement to add a value
  • Linq - multiple rows as string
  • Access OSK(on screen keyboard) handle from desktop app without administrator rights
  • How to get top 10 and ORDER BY() from COUNT()
  • Flush google analytics events manually with Google Play Services
  • K-means in OpenCV's Python interface
  • Waiting for $.post to finish
  • date format change with DT and shiny
  • Many to Many in Linq using Dapper
  • How do I translate LR(1) Parse into a Abstract syntax tree?
  • Extending the Django 1.11 User Model
  • How to Model Ternary Relationship in CakePhp?
  • Are there any side effects from calling SQLAlchemy flush() within code?
  • Avoid registering duplicate broadcast receivers in Android
  • SQL: Getting the physical size of a subset of a table
  • How do I remove all but some records based on a threshold?
  • How do I get the list of bad records that didn't load in Bigquery?
  • How to handle elastic beanstalk deployment so it uploads only changed files
  • SQL Query - Table Joining Problems
  • Check all values in string[] for length?
  • Fail:(TESTMODE) Transactions of this market type cannot be processed on this system
  • bad substitution shell- trying to use variable as name of array
  • converting text file into xml using php?
  • Sequential (transactional) API calls in angular 4 with state management
  • MS Access - How to change the linked table path by amend the table
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • Android full screen on only one activity?
  • If I include Java 8 in my Android app does that affect which devices it will work on?
  • Why value captured by reference in lambda is broken? [duplicate]
  • Resize panoramic image to fixed size
  • Symfony2: How to get request parameter
  • Weird JavaScript statement, what does it mean?
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • Why joiner is not used after Sequence generator or Update statergy
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Is there any way to bind data to data.frame by some index?
  • Recursive/Hierarchical Query Using Postgres
  • How can i traverse a binary tree from right to left in java?
  • UserPrincipal.Current returns apppool on IIS