Projekt-Anfragen: Tel: 07022/9319004 info@CodeDocu.de Software Entwicklung in C# WPF Asp.Net Core Vba Excel Word SQL-Server EF Linq, UWP Net
#

SQLite: komplette SQL Datenbank in Universal Windows App
 
Im Folgenden der komplette Code zum Beispiel, wie man unter einer neuen Windows App UWP eine Datenbank zur Laufzeit erstellt, eine Tabelle erstellt und anschließend die Tabelle mit Daten füllt.
Danach werden die Daten der Tabelle Reihe für Reihe durchlaufen und in eine Listbox gefüllt.
 

 
Das Beispiel zeigt den C# Code  und Xaml Code zum Video-Tutorial:

 
Mainpage.xaml.cs

using System;
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.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;
using Windows.Storage; //Files, Folders
using SQLitePCL; //SQLite
 
 
 
namespace sample_SQL_Folders
{
 
public sealed partial class MainPage : Page
{
public MainPage()
{
this.InitializeComponent();
}
 
public SQLiteConnection dbConnection = new SQLiteConnection("Folders.db");
 
#region Buttons
//==========================< Buttons >==========================
private void btnCreateDB_Click(object sender, RoutedEventArgs e)
{
//------------< btnCreateDB_Click() >------------
SQLiteConnection dbConnection = new SQLiteConnection("Folders.db");
string sSQL = @"CREATE TABLE IF NOT EXISTS Folders
(IDFolder Integer Primary Key Autoincrement NOT NULL
, Foldername VARCHAR(200)
, Path VARCHAR(255));";
ISQLiteStatement cnStatement = dbConnection.Prepare(sSQL);
cnStatement.Step();
//------------</ btnCreateDB_Click() >------------
}
 
private async void btnOpenFolder_Click(object sender, RoutedEventArgs e)
{
//------------< btnOpenFolder_Click() >------------
//< Open App Cache Folder >
StorageFolder local_CacheFolder = ApplicationData.Current.LocalCacheFolder;
await Windows.System.Launcher.LaunchFolderAsync(local_CacheFolder);
//</ Open App Cache Folder >
 
//------------</ btnOpenFolder_Click() >------------
}
 
private async void btnReadLibrary_Click(object sender, RoutedEventArgs e)
{
//------------< btnReadLibrary_Click() >------------
//< Open App Cache Folder >
StorageLibrary storageLibrary = await StorageLibrary.GetLibraryAsync(KnownLibraryId.Pictures);
foreach(StorageFolder folder in storageLibrary.Folders)
{
string sFoldername=folder.Name;
string sPath = folder.Path;
 
//< correction >
sFoldername = sFoldername.Replace("'", "''");
sPath = sPath.Replace("'", "''");
//</ correction >
 
string sSQL = @"INSERT INTO [Folders]
([Foldername],[Path])
VALUES
('" + sFoldername + "','"+ sPath +"');";
 
//< execute >
dbConnection.Prepare(sSQL).Step();
//</ execute >
}
//</ Open App Cache Folder >
 
//------------</ btnReadLibrary_Click() >------------
}
 
 
 
private void btnFill_List_Click(object sender, RoutedEventArgs e)
{
//------------< btnFill_List_Click() >------------
//< Open App Cache Folder >
 
var items = new List<String>();
 
//< get records >
string sSQL = @"SELECT [Foldername],[Path] FROM Folders";
ISQLiteStatement dbState = dbConnection.Prepare(sSQL);
//</ get records >
while (dbState.Step()==SQLiteResult.ROW )
{
string sFoldername = dbState["Foldername"] as string ;
string sPath = dbState["Path"] as string;
 
//< correction >
sFoldername = sFoldername.Replace("''", "'");
sPath = sPath.Replace("''", "'");
//</ correction >
 
items.Add(sFoldername + "; " + sPath );
}
//</ Open App Cache Folder >
 
ctlList.ItemsSource = items;
//------------</ btnFill_List_Click() >------------
}
//==========================</ Buttons >==========================
#endregion
 
 
}
}
 

 
 
Und die passende Frontseite

 
Mainpage.xaml

<Page
x:Class="sample_SQL_Folders.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:local="using:sample_SQL_Folders"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d">
 
<Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
<Button x:Name="btnCreateDB" Content="Create DB"
Click="btnCreateDB_Click"
Height="108" Margin="94,57,0,0" VerticalAlignment="Top" Width="216" FontSize="18"/>
 
<Button x:Name="btnOpenFolder" Content="Open Folder"
Click="btnOpenFolder_Click"
Height="108" Margin="94,200,0,0" VerticalAlignment="Top" Width="216" FontSize="18"/>
 
<Button x:Name="btnReadLibrary" Content="Read Library"
Click="btnReadLibrary_Click"
Height="108" Margin="94,344,0,0" VerticalAlignment="Top" Width="216" FontSize="18"/>
 
<Button x:Name="btnFill_List" Content="Fill List"
Click="btnFill_List_Click" Margin="383,57,0,0" VerticalAlignment="Top" Width="216" FontSize="18" Height="108"/>
<ListBox x:Name="ctlList" Margin="383,200,0,372" BorderBrush="Black" BorderThickness="1" Background="Yellow" HorizontalAlignment="Left" Width="553" d:LayoutOverrides="TopPosition, BottomPosition" >
<ListBox.ItemTemplate>
<DataTemplate>
<Grid Width="{Binding ElementName=ctlList , Path=ActualWidth }" Padding="0" Margin="0" >
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="80" />
</Grid.ColumnDefinitions>
<TextBlock Text="{Binding}" Grid.Column="0" TextWrapping="Wrap" />

<Button Content="Select" Grid.Column="1" HorizontalAlignment="Stretch" />
<Button Content="X" Grid.Column="2" HorizontalAlignment="Center" Margin="4,0,0,0"/>
</Grid>
</DataTemplate>
</ListBox.ItemTemplate>
 
</ListBox>
 
</Grid>
</Page>
 

 
 
 
 
 
 
 
Vorbereitungen:
SQLite installieren
Man muss in Visual Studio unter Tools->Extensions and Updates

 
Die zwei Produkte installieren:
1: SQLite for Universal Windows Platform
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL Database engine.
New Version: 3.13.0 SQLite Development Team
 
Und 2: SQL Server Compat / SQLite Toolbox
Von ErikEJ, SQL Server Compact /SQLite Toolbox add-in for Visual Studio.

 
SQLite for Universal Windows Platform

 
Und man muss eine Add Reference zu
SQLite for Universal Window Platforms erstellen
Sowie zu
Visual C++ 2015 Runtime for Universal Windows

 
Mobile
»
Error: Unable to load DLL sqlite3.dll
»
SQLite, UWP: GROUP BY und ORDER BY
»
SQLite, C#: Datum einfügen
»
SQLite: Select TOP Records FROM Table
»
UWP, SQLite : SQLite Code zum Erstellen einer Datenbank, der Tabellen und von Indizes
»
SQLite: komplette SQL Datenbank in Universal Windows App als Beispiel SQLitePCL
»
UWP, SQLite: An update to SQLite for Universal Windows Platform
»
UWP, SQLite Problem: Keine Referenz vorhanden
»
UWP: SQLite Datenbank in Universal Windows App

.

Jobs, Projekte, Angebote für Freiberufler, Selbstständig an Info@CodeDocu.de