public sealed partialclass MainPage : Page
{
privateList<Persons> personsTableList;
privateList<Orders> ordersTableList;
private SQLiteAsyncConnectionasyncDataBase;
public MainPage()
{
this.InitializeComponent();
ConnData();
InitData();
}
private void ConnData()
{
var dbPath=Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path,"db.sqlite");
asyncDataBase = new SQLite.SQLiteAsyncConnection(dbPath);
}
private async void InitData()
{
var result= await asyncDataBase.CreateTablesAsync<Persons,Orders>();
List<Persons> persons = newList<Persons>()
{
newPersons(){FirstName="Adams",LastName="John",Address="OxfordStreet",City="London"},
newPersons(){FirstName="Bush",LastName="George",Address="FifthAvenue",City="New York"},
newPersons(){FirstName="Carter",LastName="Thomas",Address="ChanganStreet",City="Beijing"}
};
List<Orders> orders = newList<Orders>()
{
newOrders(){OrderNo="77895",ID_P=3},
newOrders(){OrderNo="44678",ID_P=3},
newOrders(){OrderNo="22456",ID_P=1},
newOrders(){OrderNo="24562",ID_P=1},
newOrders(){OrderNo="34764",ID_P=65}
};
awaitasyncDataBase.InsertAllAsync(persons);
awaitasyncDataBase.InsertAllAsync(orders);
varpersonsTable =asyncDataBase.Table<Persons>();
personsTableList = await personsTable.ToListAsync();
this.lv1.ItemsSource = personsTableList;
varordersTable =asyncDataBase.Table<Orders>();
ordersTableList = await ordersTable.ToListAsync();
this.lv2.ItemsSource = ordersTableList;
}
private async void InsertDataBase_Click(objectsender, RoutedEventArgs e)
{
awaitasyncDataBase.InsertAsync(new Persons() { FirstName = "Aaron",LastName = "Xue", Address = "MinHang", City = "Shanghai" });
varpersionTable =asyncDataBase.Table<Persons>();
this.lv3.ItemsSource = await persionTable.ToListAsync();
}
private async void ModifyDataBase_Click(objectsender, RoutedEventArgs e)
{
stringcity = "Shanghai";
int i =await asyncDataBase.ExecuteAsync("UPDATE Persons SET City=? WHEREFirstName='Bush'", city);
varpersonsTable =asyncDataBase.Table<Persons>();
this.lv3.ItemsSource = await personsTable.ToListAsync();
}
private async void QueryDataBase_Click(objectsender, RoutedEventArgs e)
{
List<Result> re = awaitasyncDataBase.QueryAsync<Result>("SELECTPersons.FirstName,Persons.LastName,Orders.OrderNo FROM Persons LEFTJOIN Orders ON Persons.ID_P=Orders.ID_P ORDER BYPersons.FirstName");
this.lv4.ItemsSource = re;
}
}
public classPersons
{
[SQLite.AutoIncrement, SQLite.PrimaryKey]
public int ID_P { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Address { get; set; }
public string City { get; set; }
}
public classOrders
{
[SQLite.AutoIncrement, SQLite.PrimaryKey]
public int ID_O { get; set; }
public string OrderNo { get; set; }
public int ID_P { get; set; }
}
public classResult
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string OrderNo { get; set; }
}
<GridBackground="{StaticResourceApplicationPageBackgroundThemeBrush}">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*" />
<ColumnDefinition Width="*" />
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition Height="*" />
<RowDefinition Height="100" />
<RowDefinition Height="400" />
</Grid.RowDefinitions>
<ListView Name="lv1" Grid.Row="0"Grid.Column="0">
<ListView.ItemTemplate>
<DataTemplate>
<StackPanelOrientation="Horizontal">
<StackPanel.Resources>
<StyleTargetType="TextBlock">
<Setter Property="Width"Value="150" />
<Setter Property="TextAlignment"Value="Left" />
</Style>
</StackPanel.Resources>
<TextBlock Width="50" Text="{Binding ID_P}"/>
<TextBlock Text="{Binding FirstName}"/>
<TextBlock Text="{Binding LastName}"/>
<TextBlock Text="{Binding Address}"/>
<TextBlock Text="{Binding City}"/>
</StackPanel>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
<ListView Name="lv2" Grid.Row="0"Grid.Column="1">
<ListView.ItemTemplate>
<DataTemplate>
<StackPanelOrientation="Horizontal">
<StackPanel.Resources>
<StyleTargetType="TextBlock">
<Setter Property="Width"Value="150" />
<Setter Property="TextAlignment"Value="Left" />
</Style>
</StackPanel.Resources>
<TextBlock Width="50" Text="{Binding ID_O}"/>
<TextBlock Text="{Binding OrderNo}"/>
<TextBlock Text="{Binding ID_P}"/>
</StackPanel>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
<StackPanel Grid.Row="1"Grid.Column="0" Grid.ColumnSpan="2" Orientation="Horizontal"HorizontalAlignment="Left">
<Button Content="Add" Click="InsertDataBase_Click"/>
<Button Content="Update"Click="ModifyDataBase_Click" />
<Button Content="Query" Click="QueryDataBase_Click"/>
</StackPanel>
<ListView Grid.Row="2"Grid.Column="0" Name="lv3">
<ListView.ItemTemplate>
<DataTemplate>
<StackPanelOrientation="Horizontal">
<StackPanel.Resources>
<StyleTargetType="TextBlock">
<Setter Property="Width"Value="150" />
<Setter Property="TextAlignment"Value="Left" />
</Style>
</StackPanel.Resources>
<TextBlock Width="50" Text="{Binding ID_P}"/>
<TextBlock Text="{Binding FirstName}"/>
<TextBlock Text="{Binding LastName}"/>
<TextBlock Text="{Binding Address}"/>
<TextBlock Text="{Binding City}"/>
</StackPanel>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
<ListView Grid.Row="2"Grid.Column="1" Name="lv4">
<ListView.ItemTemplate>
<DataTemplate>
<StackPanelOrientation="Horizontal">
<StackPanel.Resources>
<StyleTargetType="TextBlock">
<Setter Property="Width"Value="150" />
<Setter Property="TextAlignment"Value="Left" />
</Style>
</StackPanel.Resources>
<TextBlock Text="{Binding FirstName}"/>
<TextBlock Text="{Binding LastName}"/>
<TextBlock Text="{Binding OrderNo}"/>
</StackPanel>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
</Grid>
