Created
November 26, 2019 07:21
-
-
Save liru-old/28065be12278ce09f14aea9730d16390 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System.Collections.Generic; | |
using System.Data.SqlClient; | |
namespace SunTripsLin | |
{ | |
class Database | |
{ | |
// [IMPORTANT] Connection string: used to connect to the DB, make sure you replace "SunTrips" with your database name!! | |
private readonly string connectionString = "Data Source=localhost;Initial Catalog=SunTrips;Integrated Security=True"; | |
/// <summary> | |
/// Gets a list of all users from the database | |
/// </summary> | |
/// <returns>A list of Users</returns> | |
public List<User> GetAllUsers() | |
{ | |
string sqlQuery = "SELECT * FROM [User]"; // Query to run against the DB | |
List<User> users = new List<User>(); // Start with an ampty list of users | |
using (SqlConnection myConnection = new SqlConnection(connectionString)) // Prepare connection to the db | |
{ | |
SqlCommand sqlCommand = new SqlCommand(sqlQuery, myConnection); // Prepare the query for the db | |
myConnection.Open(); // Open connection to the db | |
using (SqlDataReader dataReader = sqlCommand.ExecuteReader()) // Run query on db | |
{ | |
while (dataReader.Read()) // Read response from db (all rows) | |
{ | |
User user = new User(); // create new User object | |
user.Id = int.Parse(dataReader["Id"].ToString()); // Set user Id from db | |
user.Username = dataReader["Username"].ToString(); // Set user Username from db | |
user.Password = dataReader["Password"].ToString(); // Set user Password from db | |
users.Add(user); // Add last user to list of users | |
} | |
myConnection.Close(); // Close connection to the db | |
} | |
} | |
return users; // Return all users | |
} | |
/// <summary> | |
/// Returns the first user in the database with a matching Username | |
/// </summary> | |
/// <param name="username">Username to match in the db</param> | |
/// <returns>A User</returns> | |
public User GetUserByUsername(string username) | |
{ | |
string sqlQuery = "SELECT * FROM [User] WHERE [Username] LIKE @username"; // Query to run against the DB | |
User user = null; // Create a new user without any value | |
using (SqlConnection myConnection = new SqlConnection(connectionString)) // Prepare connection to the db | |
{ | |
SqlCommand sqlCommand = new SqlCommand(sqlQuery, myConnection); // Prepare the query for the db | |
sqlCommand.Parameters.AddWithValue("@username", username); // Add username to the query | |
myConnection.Open(); // Open connection to the db | |
using (SqlDataReader dataReader = sqlCommand.ExecuteReader()) // Run query on db | |
{ | |
if (dataReader.Read()) // Read response from db (first row) | |
{ | |
user = new User(); // create new User object | |
user.Id = int.Parse(dataReader["Id"].ToString()); // Set user Id from db | |
user.Username = dataReader["Username"].ToString(); // Set user Username from db | |
user.Password = dataReader["Password"].ToString(); // Set user Password from db | |
} | |
myConnection.Close(); // Close connection to the db | |
} | |
} | |
return user; // Return the user | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment