Forum Xamarin.Android

Announcement:

The Xamarin Forums have officially moved to the new Microsoft Q&A experience. Microsoft Q&A is the home for technical questions and answers at across all products at Microsoft now including Xamarin!

To create new threads and ask questions head over to Microsoft Q&A for .NET and get involved today.

ADO.NET SQlite error

alexanderrogers241alexanderrogers241 Member
edited July 2020 in Xamarin.Android

Hi,

I'm newbie working my way through the documentation on xamarin android. I've working through the examples on ADO.net. Now, I'm trying to build a class to handle building a database in ADO.net using most of the syntax I saw in the example. I'm having difficulty. All help is appreciated, I'm not asking for you to fix it, but to point me in the right direction.

I'm getting a error message : Mono.Data.Sqlite.SqliteException: 'SQLite error no such table: Chord

MainActivity.cs
using Android.App;
using Android.OS;
using Android.Support.V7.App;
using Android.Runtime;
using Android.Widget;
using Android.Database;
using System;

namespace ADONET_Tutorial
{
    [Activity(Label = "@string/app_name", Theme = "@style/AppTheme", MainLauncher = true)]
    public class MainActivity : AppCompatActivity
    {

        ADODatabase test2;
        protected override void OnCreate(Bundle savedInstanceState)
        {
            base.OnCreate(savedInstanceState);
            Xamarin.Essentials.Platform.Init(this, savedInstanceState);
            SetContentView(Resource.Layout.activity_main);
            // Set our view from the "main" layout resource
            Button button_check = FindViewById<Button>(Resource.Id.BUTTON696);
            button_check.Click += CLICK_EVENT;

            test2 = new ADODatabase("Chord");
            test2.AddItem(1, "C7");





        }

        private void CLICK_EVENT(object sender, EventArgs e)
        {
            test2.Check();
        }

        public override void OnRequestPermissionsResult(int requestCode, string[] permissions, [GeneratedEnum] Android.Content.PM.Permission[] grantResults)
        {
            Xamarin.Essentials.Platform.OnRequestPermissionsResult(requestCode, permissions, grantResults);

            base.OnRequestPermissionsResult(requestCode, permissions, grantResults);
        }
    }
}

ADODatabse.cs

using System;
using System.Collections.Generic;
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 System.IO;
using Mono.Data.Sqlite;


namespace ADONET_Tutorial
{
    public class ADODatabase
    {
        string mName;
        string dbPath;
        SqliteConnection connection;

        public ADODatabase(string name)
        {
            string ext = ".db3";

            mName = name;

            dbPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), (name + ext));

            bool exists = File.Exists(dbPath);

            if (!exists)
            {
                Console.WriteLine("Creating database");
                // Need to create the database before seeding it with some data
                Mono.Data.Sqlite.SqliteConnection.CreateFile(dbPath);
                connection = new SqliteConnection("Data Source=" + dbPath);
            }


        }

        public void CreateTable()
        {
            connection = new SqliteConnection("Data Source=" + dbPath);
            connection.Open();
            FormattableString command = $"CREATE TABLE {mName}( [id_] INTEGER PRIMARY KEY [name] TEXT )";

            this.connection.CreateCommand().CommandText = command.ToString();

            Console.WriteLine("\tExecuted " + command);

            this.connection.Close();
        }

        public void AddItem(int value1, string value2)

            connection = new SqliteConnection("Data Source=" + dbPath);
            connection.Open();

            FormattableString command = $"INSERT INTO {mName}( [id_], [name]) VALUES({value1}, '{value2}' )";

            this.connection.CreateCommand().CommandText = command.ToString();

            Console.WriteLine("\tExecuted " + command);

            this.connection.Close();

        }

        public void Check()
        {

            connection = new SqliteConnection("Data Source=" + dbPath);
            connection.Open();

            // query the database to prove data was inserted!
            using (var contents = connection.CreateCommand())
            {
                FormattableString command = $"SELECT [id_], [name] FROM {mName}";
                contents.CommandText = command.ToString();
                var r = contents.ExecuteReader();
                Console.WriteLine("Reading data");
                while (r.Read())
                    Console.WriteLine("\tKey={0}; Value={1}",
                                      r["id_"].ToString(),
                                      r["name"].ToString());
            }
            connection.Close();
        }
    }
}

Best Answer

Answers

Sign In or Register to comment.