c# - 创建 SQLite 表 Xamarin 表单

Question

我想创建一个多个 SQLite 表，但我不知道如何继续。

我的项目流程:
- 当我单击登录按钮时，如果填写了用户名和密码，它应该创建 SQLite 数据库和表。

需要创建的表:
用户表 (字段:UserID(字符串)、UsrPassword(字符串)、ContactId(整数)、状态(字符串))
零售商 (字段:Retailer_Name(字符串)，Retailer_Handler(int))

我已经完成的工作:
1. 我已经添加了 SQLite Nuget 包
2. 我加了 SQLiteAsyncConnection GetConnection(); 到我的界面
3.我为每个项目(Android和UWP)添加了数据库创建者
4. 我已经绑定(bind)了我的登录页面 表格到我的查看型号

我的代码如下:

ISQLiteDB.cs

using SQLite;
using System;
using System.Collections.Generic;
using System.Text;

namespace TBSMobileApplication.Data
{
    public interface ISQLiteDB
    {
        SQLiteAsyncConnection GetConnection();
    }
}

LoginPage.xaml

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="TBSMobileApplication.View.LoginPage"
             BackgroundColor="#ecf0f1">
    <ContentPage.Content>
        <StackLayout 
            VerticalOptions="StartAndExpand">
            <StackLayout.Padding>
                <OnPlatform 
                    x:TypeArguments="Thickness"
                    iOS="20"
                    Android="20,100,20,0">
                </OnPlatform>
            </StackLayout.Padding>

            <Label 
                Text="Username"
                TextColor="#34495e"
                Font="Arial,10"/>
            <Entry
                Placeholder="Username"
                PlaceholderColor="#95a5a6"
                FontSize="12"
                FontFamily="Arial"
                x:Name="entUsername"
                Text="{Binding Username}"/>
            <Label 
                Text="Password"
                TextColor="#34495e"
                Font="Arial,10"/>
            <Entry
                Placeholder="Password"
                PlaceholderColor="#95a5a6"
                FontSize="12"
                FontFamily="Arial"
                IsPassword="True"
                x:Name="entPassword"
                Text="{Binding Password}"/>
            <Button 
                Text="Login"
                FontSize="12"
                HorizontalOptions="Start"
                BackgroundColor="#3498db"
                Command="{Binding LoginCommand}"/>
        </StackLayout>
    </ContentPage.Content>
</ContentPage>

LoginPage.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TBSMobileApplication.ViewModel;
using Xamarin.Forms;
using Xamarin.Forms.Xaml;

namespace TBSMobileApplication.View
{
    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class LoginPage : ContentPage
    {
        public LoginPage ()
        {
            InitializeComponent ();
            BindingContext = new LoginPageViewModel();
            MessagingCenter.Subscribe<LoginPageViewModel,string>(this, "Login Alert",(sender,Username) =>
            {
                DisplayAlert("Login Alert", "Please fill-up the form", "Ok");
            });
        }
    }
}

LoginPageViewModel.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Windows.Input;
using TBSMobileApplication.View;
using Xamarin.Forms;

namespace TBSMobileApplication.ViewModel
{
    public class LoginPageViewModel : INotifyPropertyChanged
    {
        void OnProperyChanged(string PropertyName)
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(PropertyName));
        }

        public string username;
        public string password;

        public string Username
        {
            get { return username; }
            set
            {
                username = value;
                OnProperyChanged(nameof(Username));
            }
        }

        public string Password
        {
            get { return password; }
            set
            {
                password = value;
                OnProperyChanged(nameof(Password));
            }
        }

        public ICommand LoginCommand { get; set; }

        public LoginPageViewModel()
        {
            LoginCommand = new Command(OnLogin);
        }

        public void OnLogin()
        {
            if (string.IsNullOrEmpty(Username) || string.IsNullOrEmpty(Password))
            {
                MessagingCenter.Send(this, "Login Alert", Username);
            }
            else
            {

            }
        }

        public event PropertyChangedEventHandler PropertyChanged;
    }
}

AndroidSQLiteDB.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using SQLite;
using TBSMobileApplication.Data;
using TBSMobileApplication.Droid.Data;
using Xamarin.Forms;

[assembly: Dependency(typeof(AndroidSQLiteDb))]

namespace TBSMobileApplication.Droid.Data
{
    public class AndroidSQLiteDb : ISQLiteDB
    {
        public SQLiteAsyncConnection GetConnection()
        {
            var dbFileName = "backend.db3";
            var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsPath, dbFileName);

            return new SQLiteAsyncConnection(path);
        }
    }
}

WindowsSQLiteDB.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLite;
using TBSMobileApplication.Data;
using TBSMobileApplication.UWP.Data;
using Windows.Storage;
using Xamarin.Forms;

[assembly: Dependency(typeof(WindowsSQLiteDb))]

namespace TBSMobileApplication.UWP.Data
{
    public class WindowsSQLiteDb : ISQLiteDB
    {
        public SQLiteAsyncConnection GetConnection()
        {
            var dbFileName = "backend.db3";
            var documentsPath = ApplicationData.Current.LocalFolder.Path;
            var path = Path.Combine(documentsPath, dbFileName);
            return new SQLiteAsyncConnection(path);
        }
    }
}

Answer 1

您不想在用户单击按钮时创建表 - 当您的应用程序第一次在给定设备上启动时应该真正创建数据库和表。但是程序是一样的

// get the connection
var db = DependencyService.Get< ISQLiteDB>();
var conn = db.GetConnection();

// create the tables
if (conn != null) {
  await conn.CreateTableAsync<Users>();
  await conn.CreateTableAsync<Retailer>();
}

每个数据库模型都需要一个类

public class Retailer {
  public string Retailer_Name { get; set; }
  public int Retailer_Handler { get; set; }
}