How to Set User login Windows Phone 8 using sqlite database?

Question

Hi i am using windows phone 8 app. i want to set existing user login, i can add user registration but i can't do user login my code is give below.

public partial class LoginPage : PhoneApplicationPage
{
    public LoginPage()
    {
        InitializeComponent();

    }
    public static class dal
    {

        public static SQLiteAsyncConnection connection;
        public static bool isDatabaseExisting;

        public static async void ConnectToDB()
        {
            try
            {
                StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync("Bestin.sqlite");
                isDatabaseExisting = true;
            }
            catch (Exception ex)
            {
                isDatabaseExisting = false;
            }

            if (!isDatabaseExisting)
            {
                try
                {
                    StorageFile databaseFile = await Package.Current.InstalledLocation.GetFileAsync("Bestin.sqlite");
                    await databaseFile.CopyAsync(ApplicationData.Current.LocalFolder);
                    isDatabaseExisting = true;
                }
                catch (Exception ex)
                {
                    isDatabaseExisting = false;
                }
            }

            if (isDatabaseExisting)
            {

                connection = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "Bestin.sqlite"), true);

            }
        }
    }

    private void Click_Login(object sender, RoutedEventArgs e)
    {
        dal.ConnectToDB();
        var query = dal.connection.QueryAsync<Task>("SELECT * FROM Task Where Email=" + "\'" + txtEmailaddress.Text + "\'" + "and Password=" + "\'" + txtPassword.Password + "\'").Result;

        if (query == null)
        {
            // invalid Login credentials
        }
        else
        {
            // do login
        }
    }

}

I am using your code.I got error The system cannot find the file specified. (Exception from HRESULT: 0x80070002)

Answer 1

ok so do this ....

public static class dal
{
    public static SQLiteAsyncConnection connection;
    public static bool isDatabaseExisting;

    public static async void ConnectToDB()
    {
        try
        {
            StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync("Bestin.sqlite");
            isDatabaseExisting = true;
        }
        catch (Exception ex)
        {
            isDatabaseExisting = false;
        }

        if (!isDatabaseExisting)
        {
            try
            {
                StorageFile databaseFile = await Package.Current.InstalledLocation.GetFileAsync("Bestin.sqlite");
                await databaseFile.CopyAsync(ApplicationData.Current.LocalFolder);
                isDatabaseExisting = true;
            }
            catch (Exception ex)
            {
                isDatabaseExisting = false;
            }
        }

        if (isDatabaseExisting)
        {

            connection = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "Bestin.sqlite"), true);

        }
    }
}

make a class like above code for your database connection and call this at your application startup like this dal.ConnectToDB();

then in your loginpage do like this...

private void Click_Login(object sender, RoutedEventArgs e)
{

   var query = dal.connection.QueryAsync<Task>("SELECT * FROM Task Where Email=" + "\'" + txtEmailaddress.Text + "\'" + "and Password=" + "\'" + txtPassword.Password + "\'").Result;

   if(query == null)
   {
     // invalid Login credentials
   }    
   else
   {
     // do login
   }
}