Code Crafters–Day1&Day2
According to the plan, we will complete the registration and login part in the first two days
Code Display
using System.Data;
using Erinn;
#pragma warning disable CS8600
#pragma warning disable CS8603
namespace Fzu;
public static partial class FzuMySql
{
public static class Accounts
{
public static async Task Insert(string email, string username, string userpassword)
{
const string query = "INSERT INTO accounts (email, username, userpassword) VALUES (@email, @username, @userpassword)";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@userpassword", userpassword);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Info(e.Message);
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task UpdateUsernameByEmail(string email, string username)
{
const string query = "UPDATE accounts SET username = @username WHERE email = @email";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@email", email);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Info(e.Message);
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task UpdateUserpasswordByEmail(string email, string userpassword)
{
const string query = "UPDATE accounts SET userpassword = @userpassword WHERE email = @email";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@userpassword", userpassword);
cmd.Parameters.AddWithValue("@email", email);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Info(e.Message);
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task DeleteByEmail(string email)
{
const string query = "DELETE FROM accounts WHERE email = @email";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@email", email);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Info(e.Message);
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task<bool> CheckEmail(string email)
{
const string query = "SELECT email FROM accounts WHERE email = @email";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@email", email);
await cmd.ExecuteNonQueryAsync();
await using var reader = cmd.ExecuteReader();
return reader.HasRows;
}
catch (Exception e)
{
Log.Info(e.Message);
return true;
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task<bool> CheckUsername(string username)
{
const string query = "SELECT username FROM accounts WHERE username = @username";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@username", username);
await cmd.ExecuteNonQueryAsync();
await using var reader = cmd.ExecuteReader();
return reader.HasRows;
}
catch (Exception e)
{
Log.Info(e.Message);
return true;
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task<string> GetUserpasswordByEmail(string email)
{
const string query = "SELECT userpassword FROM accounts WHERE email = @email";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@email", email);
await cmd.ExecuteNonQueryAsync();
string password = null;
await using var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
await reader.ReadAsync();
password = reader.GetString("userpassword");
}
return password;
}
catch (Exception e)
{
Log.Info(e.Message);
return null;
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task<string> GetUserpasswordByUsername(string username)
{
const string query = "SELECT userpassword FROM accounts WHERE username = @username";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@username", username);
await cmd.ExecuteNonQueryAsync();
string password = null;
await using var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
await reader.ReadAsync();
password = reader.GetString("userpassword");
}
return password;
}
catch (Exception e)
{
Log.Info(e.Message);
return null;
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task<string> GetUsernameByEmail(string email)
{
const string query = "SELECT username FROM accounts WHERE email = @email";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@email", email);
await cmd.ExecuteNonQueryAsync();
string password = null;
await using var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
await reader.ReadAsync();
password = reader.GetString("username");
}
return password;
}
catch (Exception e)
{
Log.Info(e.Message);
return null;
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task<string> GetEmailByUsername(string username)
{
const string query = "SELECT email FROM accounts WHERE username = @username";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@username", username);
await cmd.ExecuteNonQueryAsync();
string password = null;
await using var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
await reader.ReadAsync();
password = reader.GetString("email");
}
return password;
}
catch (Exception e)
{
Log.Info(e.Message);
return null;
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task<Account> GetAccountByEmail(string email)
{
const string query = "SELECT * FROM accounts WHERE email = @email";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@email", email);
await cmd.ExecuteNonQueryAsync();
Account account = default;
await using var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
await reader.ReadAsync();
account = new Account(reader.GetInt32("id"), email, reader.GetString("username"), reader.GetString("userpassword"));
}
return account;
}
catch (Exception e)
{
Log.Info(e.Message);
return default;
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task<Account> GetAccountByUsername(string username)
{
const string query = "SELECT * FROM accounts WHERE username = @username";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
cmd.Parameters.AddWithValue("@username", username);
await cmd.ExecuteNonQueryAsync();
Account account = default;
await using var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
await reader.ReadAsync();
account = new Account(reader.GetInt32("id"), reader.GetString("email"), username, reader.GetString("userpassword"));
}
return account;
}
catch (Exception e)
{
Log.Info(e.Message);
return default;
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public static async Task Truncate()
{
const string query = "TRUNCATE TABLE accounts";
var connection = await MySqlService.Pop();
try
{
await using var cmd = MySqlService.SendQuery(query, connection);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Info(e.Message);
}
finally
{
await connection.CloseAsync();
MySqlService.Push(connection);
}
}
public struct Account
{
public int id;
public string email;
public string username;
public string userpassword;
public Account(int id, string email, string username, string userpassword)
{
this.id = id;
this.email = email;
this.username = username;
this.userpassword = userpassword;
}
}
}
}
Code Analysis
1.Namespace and Using Statements:
The code is part of the Fzu namespace.
It imports necessary namespaces such as System.Data and Erinn for database-related functionality.
2.FzuMySql Class:
This is a static partial class containing a nested class named Accounts.
It appears to be designed to interact specifically with a MySQL database.
3.Accounts Class:
Contains static methods for various account-related operations.
4.Database Operations:
Insert Method:
Inserts a new record into the “accounts” table with the provided email, username, and userpassword.
Update Methods:
UpdateUsernameByEmail and UpdateUserpasswordByEmail update the username and userpassword, respectively, based on the provided email.
Delete Method:
Deletes a record from the “accounts” table based on the provided email.
Check Methods:
CheckEmail and CheckUsername check whether an email or username already exists in the “accounts” table.
Get Methods:
Retrieve information from the database based on either email or username.
GetUserpasswordByEmail and GetUserpasswordByUsername retrieve the userpassword.
GetUsernameByEmail and GetEmailByUsername retrieve the username and email, respectively.
GetAccountByEmail and GetAccountByUsername retrieve the complete account information as a struct.
Truncate Method:
Empties the “accounts” table by truncating it.
5.Account Struct:
Defines a struct named Account to hold account information with fields for id, email, username, and userpassword.
6.Exception Handling:
The methods include exception handling to log any exceptions that might occur during database operations.
7.Async Programming:
All methods are asynchronous (async and await are used) to avoid blocking the main thread during database operations.
8.Connection Handling:
The methods use the MySqlService.Pop and MySqlService.Push methods to manage database connections, ensuring proper opening and closing of connections.
9.Parameterized Queries:
Parameterized queries are used to prevent SQL injection attacks. Parameters are added using cmd.Parameters.AddWithValue.
10.Logging:
Exceptions are logged using Log.Info(e.Message).
11.Warnings:
There are #pragma warning disable directives for specific compiler warnings (CS8600 and CS8603), suggesting that the code may not be fully handling nullable types.
Summary and picture display
This project is a user registration and login system implemented in C# language, mainly involving interaction with MySQL database. Here is a summary of the project:
Project Overview:
The project uses C# language, adopts asynchronous programming model, and stores user account information through MySql database.
All operations are static methods defined in the Accounts class under the FzuMySql namespace.
With asynchronous programming, you can avoid blocking the main thread during database operations.
Database interaction:
Provides database operations such as insert, update, delete, check, get, and empty for managing user accounts.
All database operations use parameterized queries to help prevent SQL injection attacks.
Exception handling:
All database operations include an exception handling mechanism to catch exceptions and log the exception information. This helps identify and resolve problems in a timely manner.
Connection Management:
The MySqlService.Pop and MySqlService.Push methods are used to manage the database connection, ensuring that the connection is opened and closed correctly.
Structured data:
By defining the Account structure, user account information is encapsulated in a single structure, making it easier to obtain complete account information.
Parameterized query:
All SQL queries are parameterized, which helps improve the security of the system and prevent malicious SQL injection attacks.
Log records:
Log exception information using log.info (e.Message) to help developers debug and analyze problems when they occur.
Warning handling:
Handling of specific compiler warnings via the #pragma warning disable directive, although not specified, may involve handling of nullable types.
Advantages and disadvantages:
Advantages: Good practices in asynchronous programming, exception handling, parameterized queries, connection management, etc.
Deficiencies: There may be inadequacies in the handling of nullable types that require further inspection and optimization.
In summary, this project demonstrates the implementation of a basic user account management system, combining asynchronous programming, database interaction, exception handling and other aspects of programming techniques, laying the foundation for the development of a robust and secure system. In further development, we can consider strengthening the processing of nullable types and expanding the functions according to actual needs.
Project burnout diagram(Day1&Day2)
49
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
