WPF用户登录界面设计-使用SQLite数据库进行存储

一、SQLite数据库介绍

SQLite是一款轻量级的关系型数据库，它小巧高效，无需服务器配置，仅需单一文件即可存储数据。SQLite跨平台支持，易于集成到各种应用程序中，并支持SQL语言进行数据操作。它保证了数据的完整性、一致性和持久性，通过文件级锁定实现并发访问的安全。SQLite广泛应用于移动、桌面和嵌入式系统，是管理本地数据的理想选择。其长期稳定性和开发团队的支持承诺，使其成为数字内容存储的可靠选择。

二、效果演示：

三、项目结构：

1.项目结构

2.数据库内容

四、例子代码

C#项目中安装System.Data.SQLite。这可以通过NuGet包管理器来完成。在Visual Studio中，你可以通过“管理NuGet包”来搜索并安装System.Data.SQLite。

1.前端Xaml文件

<Grid >
    <Grid.RowDefinitions>
        <RowDefinition Height="120"/>
        <RowDefinition Height="50"/>
        <RowDefinition Height="50"/>
        <RowDefinition Height="50"/>
        <RowDefinition Height="auto"/>
    </Grid.RowDefinitions>

    <Button Content="数据创建"   HorizontalAlignment="Left"  Click="DataAddition"  Height="30" Margin="30,0,0,0"/>

    <StackPanel Grid.Row="1" Orientation="Horizontal" HorizontalAlignment="Center" VerticalAlignment="Center">
        <TextBlock Text="用户名："  Margin="0,5,0,0"/>
        <TextBox x:Name="txtUsername" Width="200" Height="30" Margin="0,0,15,0"/>
    </StackPanel>


    <StackPanel Grid.Row="2" Orientation="Horizontal" HorizontalAlignment="Center" VerticalAlignment="Center">
        <TextBlock Text="密  码："  Margin="0,5,0,0"/>
        <PasswordBox x:Name="txtPassword" Width="200" Height="30" Margin="0,0,15,0"/>
        
    </StackPanel>
    <StackPanel Grid.Row="3" HorizontalAlignment="Center" VerticalAlignment="Center">
        <Button Content="登录"  Background="#0047AB" Foreground="White"  HorizontalAlignment="Right"  Click="LoginButton_Click" Width="200" Height="30" Margin="30,0,0,0"/>
    </StackPanel>
    
    <TextBlock Grid.Row="4" x:Name="myTextBlock" HorizontalAlignment="Center" VerticalAlignment="Center" FontSize="14"/>


</Grid>

2.后端Cs文件

    public partial class MainWindow : Window
    {
        string dbConnectionString = "Data Source=D:\\Quality managemen program\\UpperComputer\\列子程序\\tryWpfApp3-sq用户登录\\WpfApp1\\date\\mydatabase.db; Version=3;";
        
        public MainWindow()
        {
            InitializeComponent();
            using (var connection = new SQLiteConnection(dbConnectionString))
            {
                connection.Open();
                // 创建表（如果尚不存在）  
                CreateTable(connection);
            }
        }


        private void DataAddition(object sender, RoutedEventArgs e)
        {
            using (var connection = new SQLiteConnection(dbConnectionString))
            {
                connection.Open();
                for (int i = 0; i < 10; i++)
                {
                    // 插入数据  
                    InsertData(connection);
                }                   
            }
        }

        private void LoginButton_Click(object sender, RoutedEventArgs e)
        {
            string input_name = txtUsername.Text;
            string input_password = txtPassword.Password;

            using (var connection = new SQLiteConnection(dbConnectionString))
            {
                connection.Open();
                string querySql = "SELECT * FROM Users";

                using (var command = new SQLiteCommand(querySql, connection))
                using (var reader = command.ExecuteReader())
                {
                    int UserPermissions = 0;
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        string password = reader.GetString(2);
                        int permissions = reader.GetInt32(3);
                        Debug.WriteLine($"ID: {id}, Name: {name}, PassWord: {password}, Permissions: {permissions}");

                        if (name == input_name && password == input_password)
                        {
                            UserPermissions = permissions;
                        }
                    }

                    Debug.WriteLine($"当前权限为{UserPermissions}");
                    myTextBlock.Text = $"当前权限为{UserPermissions}";

                }
            }
        }



/*//*/
        // 创建表
        static void CreateTable(SQLiteConnection connection)
        {
            string createTableSql = "CREATE TABLE IF NOT EXISTS Users (" +
                                    "Id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                    "Name TEXT NOT NULL, " +
                                    "PassWord TEXT, " +
                                    "Permissions INT)";

            using (var command = new SQLiteCommand(createTableSql, connection))
            {
                command.ExecuteNonQuery();
            }
        }

        // 插入数据 
        static void InsertData(SQLiteConnection connection)
        {
            string insertSql = "INSERT INTO Users (Name, PassWord, Permissions) VALUES (@name, @password, @permissions)";

            using (var command = new SQLiteCommand(insertSql, connection))
            {
                command.Parameters.AddWithValue("@name", "John Doe");
                command.Parameters.AddWithValue("@password", "123456");
                command.Parameters.AddWithValue("@permissions", 1);


                command.ExecuteNonQuery();

                // 可以插入多条数据，只需重复上述过程即可  
            }
        }
       

        // 查询数据  
        static void ReadData(SQLiteConnection connection)
        {
            string querySql = "SELECT * FROM Users";

            using (var command = new SQLiteCommand(querySql, connection))
            using (var reader = command.ExecuteReader())
            {
                int UserPermissions = 0;
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    string password = reader.GetString(2);
                    int permissions = reader.GetInt32(3);
                    Debug.WriteLine($"ID: {id}, Name: {name}, PassWord: {password}, Permissions: {permissions}");

                    if (name == "AA" && password == "999999")
                    {
                        UserPermissions = permissions;
                    }
                }

                Debug.WriteLine($"当前权限为{UserPermissions}");
               
            }
        }

        // 更新数据  
        static void UpdateData(SQLiteConnection connection)
        {
            string updateSql = "UPDATE Users SET PassWord = @newPassWord WHERE Id = @id";

            using (var command = new SQLiteCommand(updateSql, connection))
            {
                command.Parameters.AddWithValue("@newPassWord", "8888");
                command.Parameters.AddWithValue("@id", 3);

                command.ExecuteNonQuery();
            }
        }

       


    }

五、C#控制台数据库读写方法例子

using System;
using System.Data.SQLite; // 引入SQLite命名空间  

class Program
{
    static void Main(string[] args)
    {
        // 数据库连接字符串  
        string dbConnectionString = "Data Source=D:\\Quality managemen program\\UpperComputer\\C#K\\读写SQlite数据库\\ConsoleApp1\\data\\mydatabase.db; Version=3;";
      


        // 创建并连接到SQLite数据库  
        using (var connection = new SQLiteConnection(dbConnectionString))
        {
            connection.Open();

            // 创建表（如果尚不存在）  
            CreateTable(connection);

            for (int i = 0; i < 10; i++)
            {
                // 插入数据  
                InsertData(connection);
            }


            // 查询数据  
            ReadData(connection);

             更新数据  
            //UpdateData(connection);

             再次查询数据以查看更新  
            //ReadData(connection);
        }

        Console.WriteLine("Press any key to exit...");
        Console.ReadKey();
    }

    // 创建表
    static void CreateTable(SQLiteConnection connection)
    {
        string createTableSql = "CREATE TABLE IF NOT EXISTS Users (" +
                                "Id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                "Name TEXT NOT NULL, " +
                                "PassWord TEXT, " +
                                "Permissions INT)";

        using (var command = new SQLiteCommand(createTableSql, connection))
        {
            command.ExecuteNonQuery();
        }
    }

    // 插入数据 
    static void InsertData(SQLiteConnection connection)
    {
        string insertSql = "INSERT INTO Users (Name, PassWord, Permissions) VALUES (@name, @password, @permissions)";

        using (var command = new SQLiteCommand(insertSql, connection))
        {
            command.Parameters.AddWithValue("@name", "John Doe");
            command.Parameters.AddWithValue("@password", "123456");
            command.Parameters.AddWithValue("@permissions", 1);


            command.ExecuteNonQuery();

            // 可以插入多条数据，只需重复上述过程即可  
        }
    }


    // 查询数据  
    static void ReadData(SQLiteConnection connection)
    {
        string querySql = "SELECT * FROM Users";

        using (var command = new SQLiteCommand(querySql, connection))
        using (var reader = command.ExecuteReader())
        {
            int UserPermissions = 0;
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string name = reader.GetString(1);
                string password = reader.GetString(2);
                int permissions = reader.GetInt32(3);
                Console.WriteLine($"ID: {id}, Name: {name}, PassWord: {password}, Permissions: {permissions}");

               if (name =="AA" && password=="999999")
                {
                    UserPermissions = permissions;
                }
            }
            
            Console.WriteLine($"当前权限为{UserPermissions}");
        }
    }

    // 更新数据  
    static void UpdateData(SQLiteConnection connection)
    {
        string updateSql = "UPDATE Users SET PassWord = @newPassWord WHERE Id = @id";

        using (var command = new SQLiteCommand(updateSql, connection))
        {
            command.Parameters.AddWithValue("@newPassWord", "8888");
            command.Parameters.AddWithValue("@id", 3);  

            command.ExecuteNonQuery();
        }
    }
}