C# SQLite 用户管理：创建、更新、删除和获取用户数据

using System; using System.Collections.Generic; using System.Data; using System.Data.SQLite;

public class UsersDB { private string connectionString;

public UsersDB(string dbPath)
{
    connectionString = string.Format("Data Source={0};Version=3;", dbPath);
}

public void CreateUser(string userName, string password, string realName, string telephone, string phone)
{
    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
    {
        connection.Open();

        using (SQLiteCommand command = new SQLiteCommand(connection))
        {
            command.CommandText = "INSERT INTO Users (UserName, Password, RealName, Telephone, Phone, RegisterDate, LastLoginDate) VALUES (@UserName, @Password, @RealName, @Telephone, @Phone, @RegisterDate, @LastLoginDate)";
            command.Parameters.AddWithValue("@UserName", userName);
            command.Parameters.AddWithValue("@Password", password);
            command.Parameters.AddWithValue("@RealName", realName);
            command.Parameters.AddWithValue("@Telephone", telephone);
            command.Parameters.AddWithValue("@Phone", phone);
            command.Parameters.AddWithValue("@RegisterDate", DateTime.Now);
            command.Parameters.AddWithValue("@LastLoginDate", DateTime.Now);

            command.ExecuteNonQuery();
        }
    }
}

public void UpdateUser(int pid, string userName, string password, string realName, string telephone, string phone)
{
    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
    {
        connection.Open();

        using (SQLiteCommand command = new SQLiteCommand(connection))
        {
            command.CommandText = "UPDATE Users SET UserName = @UserName, Password = @Password, RealName = @RealName, Telephone = @Telephone, Phone = @Phone WHERE PID = @PID";
            command.Parameters.AddWithValue("@UserName", userName);
            command.Parameters.AddWithValue("@Password", password);
            command.Parameters.AddWithValue("@RealName", realName);
            command.Parameters.AddWithValue("@Telephone", telephone);
            command.Parameters.AddWithValue("@Phone", phone);
            command.Parameters.AddWithValue("@PID", pid);

            command.ExecuteNonQuery();
        }
    }
}

public void DeleteUser(int pid)
{
    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
    {
        connection.Open();

        using (SQLiteCommand command = new SQLiteCommand(connection))
        {
            command.CommandText = "DELETE FROM Users WHERE PID = @PID";
            command.Parameters.AddWithValue("@PID", pid);

            command.ExecuteNonQuery();
        }
    }
}

public List<User> GetUsers()
{
    List<User> users = new List<User>();

    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
    {
        connection.Open();

        using (SQLiteCommand command = new SQLiteCommand(connection))
        {
            command.CommandText = "SELECT * FROM Users";

            using (SQLiteDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    User user = new User();
                    user.PID = Convert.ToInt32(reader["PID"]);
                    user.UserName = reader["UserName"].ToString();
                    user.Password = reader["Password"].ToString();
                    user.RealName = reader["RealName"].ToString();
                    user.Telephone = reader["Telephone"].ToString();
                    user.Phone = reader["Phone"].ToString();
                    user.GroupID = Convert.ToInt32(reader["GroupID"]);
                    user.RegisterDate = Convert.ToDateTime(reader["RegisterDate"]);
                    user.LastLoginDate = Convert.ToDateTime(reader["LastLoginDate"]);

                    users.Add(user);
                }
            }
        }
    }

    return users;
}

public User GetUserByUserName(string userName)
{
    User user = null;

    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
    {
        connection.Open();

        using (SQLiteCommand command = new SQLiteCommand(connection))
        {
            command.CommandText = "SELECT * FROM Users WHERE UserName = @UserName";
            command.Parameters.AddWithValue("@UserName", userName);

            using (SQLiteDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    user = new User();
                    user.PID = Convert.ToInt32(reader["PID"]);
                    user.UserName = reader["UserName"].ToString();
                    user.Password = reader["Password"].ToString();
                    user.RealName = reader["RealName"].ToString();
                    user.Telephone = reader["Telephone"].ToString();
                    user.Phone = reader["Phone"].ToString();
                    user.GroupID = Convert.ToInt32(reader["GroupID"]);
                    user.RegisterDate = Convert.ToDateTime(reader["RegisterDate"]);
                    user.LastLoginDate = Convert.ToDateTime(reader["LastLoginDate"]);
                }
            }
        }
    }

    return user;
}

}

public class User { public int PID { get; set; } public string UserName { get; set; } public string Password { get; set; } public string RealName { get; set; } public string Telephone { get; set; } public string Phone { get; set; } public int GroupID { get; set; } public DateTime RegisterDate { get; set; } public DateTime LastLoginDate { get; set; } }