How to detect if SQLite DB has been created

Question

I need create a SQLite DB for my WinRT App. The code is executed but MainPage is not showing.

Questions:

1) Is this code in App.xaml.cs creating a SQLite DB or creating just Table or BOTH?

2) in MainPage, How Do I check One more time if SQLite DB is created.

 3) What could be the Problem: no erro message and MainPage is not showing?

 

-- below code is in checking rootFrame in  App.xaml.cs

public static string DBPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");

if (rootFrame == null)
{

//-- Detect before creating

bool result = await GetIfFileExistsAsync(DBPath);


if (result == true)              
{
      MessageDialog mError = new MessageDialog("DB Created", "DB creation status");
     await mError.ShowAsync();
     return;

}
else
  {
    MessageDialog mError = new MessageDialog("DB NOT Created", "NO DB created");
   await mError.ShowAsync();

   CreateDBNow();

   }

//---- rootFrame

}


private async Task GetIfFileExistsAsync(string strDBPath)
{
    try
     {

     var dbFile = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(strDBPath);

      if (dbFile != null)
      {
       return true;
      }
     else
      {
        return false;
      }


    }
    catch (FileNotFoundException)
    {
      return false;

    }
}

private async void CreateDBNow()
{

    DBPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");


   using (var db = new SQLite.SQLiteConnection(DBPath))
   {
    // Create the tables if they don't exist
    db.CreateTable();

   }

  MessageDialog mError = new MessageDialog("DB now created", "DB created");
  await mError.ShowAsync();


  }


--- in MainPage


I need to check one more time if SQLite DB is created.

Answer 1

I think you need to specify the type for the table to be created.

Vb

Private Sub LoadData()

Dim dbPath =Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path,"db.sqlite")

Using db = New SQLite.SQLiteConnection(dbPath)

db.CreateTable(OfPerson)()

db.RunInTransaction(Sub()

db.Insert(NewPersonWith {.FirstName = "Ken", .LastName = "Tucker"})

EndSub)

EndUsing

EndSub

or c#

         if (!FileExists("db.sqlite").Result)
            {
                using (var db = new SQLiteConnection(dbPath))
                {
                    db.CreateTable<Person>();
                }
            }

Answer 2

Hello FireDance,

Try to specify your table, Like this :

 using (var db = new SQLite.SQLiteConnection(DBPath))
   {
    // Create the tables if they don't exist
    db.CreateTable<tb>();

   }

public class tb
{
       public int id { get; set; }
        public string chack { get; set; }
}

Answer 3

Thanks. Yes, I forgot to place a Class-name in the table.

If I use this DBPath in App.xaml.cs to create the SQLite Db.  How to check again if this DB created in other Page ?

Use this DBPath ?

 DBPath =Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path,"db.sqlite")

Or use this

Uriurlr=newUri("ms-appdata:///local/DBPath")

Answer 4

Hello FireDance,

No you can't use 

Uriurlr=newUri("ms-appdata:///local/DBPath")

Because SqLiteConnection only accept string whatever it is path or a database file name.

So you can use 

 DBPath =Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path,"db.sqlite")

set your DBPath to global variable, so you can get this from any of page.

Answer 5

I will list my questions in (1) and (2) :

---1------- Create DB In App.xaml.cs at OnLaunched event:

1.1) Is there any issue if I place GetIfFileExitsAsync() in App.Xaml.cs

in a)   if (rootFrame == null) {}  or  b)   if (rootFrame.Content == null) {}

for this, I place it in (a)

1.2) After code executed, it shows the msg that SQLite DB is created

public static string DBPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");

if (rootFrame == null)
 {

 bool result = await GetIfFileExistsAsync(DBPath);

        if (result == true)
        {
               
           return;

         }
        else
        {                

           CreateDBNow();
        }
}

 

 private async Task<bool> GetIfFileExistsAsync(string strDBPath)
        {
            try
            {
            
                var dbFile = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(strDBPath);

                if (dbFile != null)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
           catch(Exception ex)   
            {
                return false;
                // return default(StorageFile);
            }

 private async void CreateDBNow()
        {

            DBPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");
           
    
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
              db.CreateTable<Customer>();           
              
            }

            MessageDialog mError = new MessageDialog("DB now created", "DB created");
            await mError.ShowAsync();

        }

---2---- In Other Page :  CheckDB.Xaml

2.1) When I click the button, it shows NO DB is created. What happens?

 private async void btnChkDB_Click(object sender, RoutedEventArgs e)
        {

            bool result = await GetIfFileExistsAsync("customer.sqlite");

            if (result == true)
            {
                MessageDialog mError = new MessageDialog("Yes,DB Created", "DB created status");
                await mError.ShowAsync();
                return;

            }
            else
            {
                MessageDialog mError = new MessageDialog("No DB Created", "NO DB Created");
                await mError.ShowAsync();
            }

        }

 

 private async Task<bool> GetIfFileExistsAsync(string strDBPath)
        {
            try
            {
 
                StorageFile dbfile = await ApplicationData.Current.LocalFolder.GetFileAsync("customer.sqlite");
              
  Or:
                // StorageFile dbfile = await ApplicationData.Current.LocalFolder.GetFileAsync(strDBPath);

                if (dbfile != null)
                {
                    //-- Db created
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch (FileNotFoundException)
            {
                return false;
              
            }
        }

 

 

 

 

Answer 6

Hello FireDance,

You can copy that sqlite database file to your project & set them property

BuildAction => Content

Copy to Output Directory => Copy if newer. Than your second problem is also solwed.

Answer 7

Hi Khan Nipun,

I am not adding a Prepopulated DB ( with Data) into the Project. I am creating a SQLite DB so I don't need to use BuildAction => Content.

These what I wanted to do:

1) Create SQLite DB using code in App.xaml.cs

2) Check if this SQLite DB created in CheckDB.xaml

Thanks.

Answer 8

Hello FireDance, You can try to put your dbpath globally:

//temp.cs 
public class temp
    {
        public string DBPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");
    }

//App.Xaml.cs
temp tem = new temp();
//In onlunched event:
 using (var db = new SQLite.SQLiteConnection(tem.DBPath))
                {
                    // Create the tables if they don't exist
                    db.CreateTable<tb>();
                }
//another page checkdb.xaml.cs onnavigateto event:
temp tem = new temp();
 using (var db = new SQLite.SQLiteConnection(tem.DBPath))
                {
                    // Create the tables if they don't exist
                    db.CreateTable<tb>();
                }

Answer 9

public async Task<bool> IsDbExists(string fileName)
        {
            try
            {
                var item = await ApplicationData.Current.LocalFolder.GetFileAsync(fileName);
                var db = new SQLiteConnection("Your db path");
                var tb1 = db.GetTableInfo("TableName1");
                var tb2 = db.GetTableInfo("TableName2");
                var tb3 = db.GetTableInfo("TableName3");
                var tb4 = db.GetTableInfo("TableName4");
                if (item == null || tb1.Count == 0 || tb2.Count == 0 || tb3.Count == 0 || tb4.Count == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

Try this 

---

&lt;Kabilan&gt;Learning C#&lt;/Kabilan&gt;