UWP.如何将数据传递到数据库，然后填充网格

Question

我在UWP方面有点困难，想知道是否有人能帮忙？对C来说相当陌生♯在这里

基本上，我有一个添加员工的页面，我认为输入的数据在逻辑上应该发送到一个名为"的类；"人"；然后被添加到数据库中(但我知道什么哈哈哈！(。然后，数据库需要在主页面上填充一个网格。

因此，主要的两个问题是，我如何使类Person中的数据填充DB，进而填充主页面上的网格？如何将来自不同页面的人员添加到类中？

以下是我的尝试：

这就是我的个人课程：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace TestApp2
{
using static DB;
class Person 
{
public int PersonId { get; set; }
public int DepartmentId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Position { get; set; }
public string Address { get; set; }
public double PayratePH { get; set; }
public double Holiday { get; set; }
public string TaxCode { get; set; }
private List<String> Grab_Entries()
{
List<String> entries = new List<string>();
using (SqliteConnection db = new SqliteConnection("Filename=sqliteSample.db"))
{
db.Open();
SqliteCommand selectCommand = new SqliteCommand("SELECT First_Name from EmployeeTable", db);
SqliteDataReader query;
try
{
query = selectCommand.ExecuteReader();
}
catch (SqliteException)
{
//Handle error
return entries;
}
while (query.Read())
{
entries.Add(query.GetString(0));

}
db.Close();
}
return entries;

}

}
} 

这是我的数据库类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Windows.UI.Xaml;
namespace TestApp2
{
public static class DB
{
private static SuspendingEventHandler App_Suspending;
public static void database()
{
Application.Current.Suspending += new SuspendingEventHandler(App_Suspending);
using (SqliteConnection db = new SqliteConnection("Filename=sqliteSample.db"))
{
//Creation of the database table
db.Open();
String tableCommand1 = "CREATE TABLE IF NOT EXISTS EmployeeTable (Employee_ID INTEGER PRIMARY KEY AUTOINCREMENT, First_Name NVARCHAR(20) NULL, Last_Name NVARCHAR(40) NULL, Address NVARCHAR(50) NULL , Position NVARCHAR(20) NULL, Pay_Rate DOUBLE NULL, Tax_Code NVARCHAR(10) NULL, Sex NVARCHAR(20), NI NVACHAR(10), Emergency_Details NVARCHAR(100)";

SqliteCommand createTable = new SqliteCommand(tableCommand1, db);

try
{
createTable.ExecuteReader();
}
catch (SqliteException)
{
//Do nothing
}
}
}
}
} 

最后是我的主页。评论部分是我测试我是否可以填充xaml网格(我正在使用社区开发网格，如果这有帮助的话？https://learn.microsoft.com/en-us/windows/communitytoolkit/controls/datagrid_guidance/datagrid_basics)

using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.WindowsRuntime;
using Windows.Foundation;
using Windows.Foundation.Collections;
using Windows.UI.Popups;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.UI.Xaml.Controls.Primitives;
using Windows.UI.Xaml.Data;
using Windows.UI.Xaml.Input;
using Windows.UI.Xaml.Media;
using Windows.UI.Xaml.Navigation;

// The Blank Page item template is documented at https://go.microsoft.com/fwlink/?LinkId=402352&clcid=0x409
namespace TestApp2
{

public class Department
{
public int DepartmentId { get; set; }
public string DepartmentName { get; set; }
}
//public class Person
//{
//    public int PersonId { get; set; }
//    public int DepartmentId { get; set; }
//    public string FirstName { get; set; }
//    public string LastName { get; set; }
//    public string Position { get; set; }
//    public string Address { get; set; }
//    public double PayratePH { get; set; }
//    public double Holiday { get; set; }
//    public string TaxCode { get; set; }

//}
/// <summary>
/// An empty page that can be used on its own or navigated to within a Frame.
/// </summary>
public sealed partial class MainPage : Page
{
public List<Department> Departments { get; set; }
List<Person> persons;
public MainPage()
{
this.InitializeComponent();
persons = new List<Person>();
//    Departments = new List<Department>
//{
//    new Department {DepartmentId = 1, DepartmentName = "R&D"},
//    new Department {DepartmentId = 2, DepartmentName = "Finance"},
//    new Department {DepartmentId = 3, DepartmentName = "IT"}
//};

//    persons = new List<Person>
//{
//    new Person
//    {
//        PersonId = 1, DepartmentId = 3, FirstName = "Ronald", LastName = "Rumple",
//        Position = "Network Administrator", Address = "14 Malkie Avenue",  Holiday = 20,
//        TaxCode = "LC455"
//    },
//    new Person
//    {
//        PersonId = 2, DepartmentId = 1, FirstName = "Brett", LastName = "Banner",
//        Position = "Software Developer", Address = "4/L Long Terrace", PayratePH = 12.95, Holiday = 12.5,
//        TaxCode = "LC455"
//    },
//    new Person
//    {
//        PersonId = 3, DepartmentId = 2, FirstName = "Alice", LastName = "Anderson",
//        Position = "Accountant", Address = "56 Hemming Way", PayratePH = 10,  Holiday = 19.9,
//        TaxCode = "LC455"
//    }
//};
}
private async void searchEmployee_Click(object sender, RoutedEventArgs e)
{
await new MessageDialog("Test").ShowAsync();
}
private void rota_Click(object sender, RoutedEventArgs e)
{
this.Frame.Navigate(typeof(Rota));
}
private void emailEmployee_Click(object sender, RoutedEventArgs e)
{
this.Frame.Navigate(typeof(email));
}
private void addEmployee_Click(object sender, RoutedEventArgs e)
{
this.Frame.Navigate(typeof(AddEmployee));
}
}
}

Answer 1

如何使类Person的数据填充DB，进而填充主页上的网格

您创建的tableCommand1字符串不是不完整的，您需要这样修改它：String tableCommand1 = "CREATE TABLE IF NOT EXISTS EmployeeTable (Employee_ID INTEGER PRIMARY KEY AUTOINCREMENT, First_Name NVARCHAR(20) NULL, Last_Name NVARCHAR(40) NULL, Address NVARCHAR(50) NULL, Position NVARCHAR(20) NULL, Pay_Rate DOUBLE NULL, Tax_Code NVARCHAR(10) NULL, Sex NVARCHAR(20), NI NVACHAR(10), Emergency_Details NVARCHAR(100))";

创建数据库的完整代码是：

public async static void database()
{
await ApplicationData.Current.LocalFolder.CreateFileAsync("sqliteSample.db", CreationCollisionOption.OpenIfExists);
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}"))
{
//Creation of the database table
db.Open();
String tableCommand1 = "CREATE TABLE IF NOT EXISTS EmployeeTable (Employee_ID INTEGER PRIMARY KEY AUTOINCREMENT, First_Name NVARCHAR(20) NULL, Last_Name NVARCHAR(40) NULL, Address NVARCHAR(50) NULL, Position NVARCHAR(20) NULL, Pay_Rate DOUBLE NULL, Tax_Code NVARCHAR(10) NULL, Sex NVARCHAR(20), NI NVACHAR(10), Emergency_Details NVARCHAR(100))";
SqliteCommand createTable = new SqliteCommand(tableCommand1, db);
try
{
createTable.ExecuteReader();
}
catch (SqliteException ee){}
}
}

当你想把Person的数据插入数据库时，我以first_name为例：

private void InsertData(object sender, RoutedEventArgs e)
{
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT INTO EmployeeTable VALUES (NULL, @First_Name);";
insertCommand.Parameters.AddWithValue("@First_Name", "Hello");
insertCommand.ExecuteReader();
db.Close();
}
}

有关如何使用sqlite的更多详细信息，您可以参考本文档。

此外，当你想向网格显示新数据时，建议使用ObservableCollection类而不是List，当你插入或删除该类中的数据时，它会自动更新UI。

ObservableCollection<Person> persons;
public MainPage()
{
this.InitializeComponent();
persons = new ObservableCollection<Person>();
......
}

如何将来自不同页面的人员添加到类中

您可以声明一个公共静态属性来表示MainPage实例，然后您可以直接调用一个公共方法来添加一个新的Person类。您可以将NavigationCacheMode设置为Enabled，在这种情况下，当您返回MainPage时，数据将被缓存。例如：

主页.cs:

public MainPage()
{
this.InitializeComponent();
......
this.NavigationCacheMode = NavigationCacheMode.Enabled;
Current = this;
}
public static MainPage Current;
public void addMethod(Person p) 
{
persons.Add(p);
}

SecondPage.cs:

private void AddNewData(object sender, RoutedEventArgs e)
{
//First add data to database like mainpage does
MainPage.Current.addMethod(person);
}