Forum Xamarin.Android
We are excited to announce that the Xamarin Forums are moving to the new Microsoft Q&A experience. Q&A is the home for technical questions and answers at across all products at Microsoft now including Xamarin!

We encourage you to head over to Microsoft Q&A for .NET for posting new questions and get involved today.

how to locate or open and edit my sqlite db using adb shell?

JunCainJunCain Member ✭✭✭
edited June 2019 in Xamarin.Android

DBAdapter.cs

 public class DBAdapter
    {
        private Context con;
        private SQLiteDatabase db;
        private DBHelper dBHelper;

        public DBAdapter(Context con)
        {
            this.con = con;
            dBHelper = new DBHelper(con);
        }

        // Open DB Connection
        public DBAdapter openDB()
        {
            try
            {
                db = dBHelper.WritableDatabase;
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return this;
        }

        // Close DB Connection
        public void closeDB()
        {
            try
            {
                dBHelper.Close();
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

        // Insert SendDate
        public bool AddSendDate(string SendDate)
        {
            try
            {
                ContentValues conVal = new ContentValues();
                conVal.Put(Constant.sendDate, SendDate);
                db.Insert(Constant.TBSendDate, null, conVal);
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return false;
        }

        // Retrieving sendDate
        public ICursor GetSendDate()
        {
            String[] SendDateColumns = { Constant.sendDateId, Constant.sendDate, Constant.TBSendDate };
            return db.Query(Constant.TBSendDate, SendDateColumns, null, null, null, null, null);
        }

        // Insert data into Orders table
        public bool AddOrders(int AgentID, int CustID, string Orders, int Status, string SendDate)
        {
            try
            {
                ContentValues conVal = new ContentValues();
                conVal.Put(Constant.AgentID, AgentID);
                conVal.Put(Constant.CustID, CustID);
                conVal.Put(Constant.Orders, Orders);
                conVal.Put(Constant.Status, Status);
                conVal.Put(Constant.SendDate, SendDate);
                db.Insert(Constant.TBOrders, null, conVal);
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            return false;
        }

        // Retrieve data from Orders Table
        public ICursor GetOrdersData()
        {
                String[] OrdersColumns = { Constant.orderId, Constant.AgentID, Constant.CustID, Constant.Orders, Constant.Status, Constant.SendDate };
                return db.Query(Constant.TBOrders, OrdersColumns, null, null, null, null, null);

        }

        // Adding Customer
        public bool AddCustomer(int custID, string custName)
        {
            try
            {
                ContentValues conVal = new ContentValues();
                conVal.Put(Constant.custId, custID);
                conVal.Put(Constant.custName, custName);
                db.Insert(Constant.TBCustomer, null, conVal);
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return false;
        }

        // Retrieve data from cutomer table
        public ICursor GetCustomerData()
        {
            String[] customerColumns = { Constant.custId, Constant.custName };
            return db.Query(Constant.TBCustomer, customerColumns, null, null, null, null, null);
        }

        // Adding Agent 
        public bool AddAgent(int agentID, string agentName)
        {
            try
            {
                ContentValues conVal = new ContentValues();
                conVal.Put(Constant.agentId, agentID);
                conVal.Put(Constant.agentName, agentName);
                db.Insert(Constant.TBAgent, null, conVal);
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return false;
        }

        // Retrieve data from agent table
        public ICursor GetAgentData()
        {
            String[] agentColumns = { Constant.agentId, Constant.agentName };
            return db.Query(Constant.TBAgent, agentColumns, null, null, null, null, null);
        }

        // Add product category data 
        public bool AddProdCat(int catID, string catName)
        {
            try
            {
                ContentValues conVal = new ContentValues();
                conVal.Put(Constant.catId, catID);
                conVal.Put(Constant.catName, catName);
                db.Insert(Constant.TBProdCat, null, conVal);
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return false;
        }

        //retreive data from product category table
        public ICursor getProdCatData()
        {
            String[] prodCatColumns = { Constant.catId, Constant.catName };
            return db.Query(Constant.TBProdCat, prodCatColumns, null, null, null, null, null);
        }

        // adding product data
        public bool AddProduct(int prodID, string prodName)
        {
            try
            {
                ContentValues conVal = new ContentValues();
                String count = "select count(*) from TBProduct";
                ICursor cur = db.RawQuery(count, null);
                cur.MoveToFirst();
                int i = cur.GetInt(0);
                if(i == 0)
                {
                    var ID = 0;
                    var Name = "Select Product";
                    conVal.Put(Constant.prodId, ID);
                    conVal.Put(Constant.prodName, Name);
                    db.Insert(Constant.TBProduct, null, conVal);
                }
                conVal.Put(Constant.prodId, prodID);
                conVal.Put(Constant.prodName, prodName);
                db.Insert(Constant.TBProduct, null, conVal);
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return false;
        }

        // retreive data from product table
        public ICursor getProductData()
        {
            String[] productColumns = { Constant.prodId, Constant.prodName };
            return db.Query(Constant.TBProduct, productColumns, null, null, null, null, null);
        }
    }

DBHelper.cs

public class DBHelper : SQLiteOpenHelper
    {
        public DBHelper(Context context) : base(context, Constant.DBName, null, Constant.DBVersion)
        {
        }

        public override void OnCreate(SQLiteDatabase db)
        {
            try
            {
                db.ExecSQL(Constant.createOrdersTB);
                db.ExecSQL(Constant.createAgentTB);
                db.ExecSQL(Constant.createCustomer);
                db.ExecSQL(Constant.createprodCatTB);
                db.ExecSQL(Constant.createProduct);
                db.ExecSQL(Constant.createSendDate);
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

        public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            db.ExecSQL(Constant.dropOrders);
            db.ExecSQL(Constant.dropAgent);
            db.ExecSQL(Constant.dropCustomer);
            db.ExecSQL(Constant.dropProdCat);
            db.ExecSQL(Constant.dropProduct);
            db.ExecSQL(Constant.dropSendDate);
            OnCreate(db);
        }
    }

Constant.cs

 public class Constant
    {
        // Order Columns
        public static String orderId = "orderId";
        public static String AgentID = "AgentID";
        public static String CustID = "CustID";
        public static String Orders = "Orders";
        public static String Status = "Status";
        public static String SendDate = "SendDate";

        // Agent Columns
        public static String agentId = "agentId";
        public static String agentName = "agentName";

        // Product Category
        public static String catId = "catId";
        public static String catName = "catName";

        // Product
        public static String prodId = "prodId";
        public static String prodName = "prodName";

        // Customer
        public static String custId = "custId";
        public static String custName = "custName";

        // SendDate 
        public static String sendDateId = "sendDateId";
        public static String sendDate = "sendDate";

        // Database Name and Table Name
        public static int DBVersion = 1;
        public static String DBName = "OrderingApp";
        public static String TBOrders = "TBOrders";
        public static String TBAgent = "TBAgent";
        public static String TBProdCat = "TBProdCat";
        public static String TBProduct = "TBProduct";
        public static String TBCustomer = "TBCustomer";
        public static String TBSendDate = "TBSendDate";

        //Create SendDate table 
        public static String createSendDate = "create table TBSendDate(sendDateId integer primary key autoincrement," + "sendDate text not null)";

        //DRop if SendDate table exist
        public static String dropSendDate = "drop table if exists" + TBSendDate;

        //Create Customer Table
        public static String createCustomer = "create table TBCustomer(custId integer primary key," + "custName text not null)";

        // Drop if Customer table exist
        public static String dropCustomer = "drop table if exists" + TBCustomer;

        //Creating Product Table 
        public static String createProduct = "create table TBProduct(prodId integer primary key," + "prodName text not null);";

        // Drop if Product table exist
        public static String dropProduct = "drop table if exists" + TBProduct;

        //Creating Product Category Table
        public static String createprodCatTB = "create table TBProdCat(catId integer primary key," + "catName text not null);";

        // Drop if Product Category table exist
        public static String dropProdCat = "drop table if exists" + TBProdCat;

        //Creating Agent Table
        public static String createAgentTB = "create table TBAgent(agentId integer primary key," + "agentName text not null);";

        // Drop if Agent table exist
        public static String dropAgent = "drop table if exists" + TBAgent;

        //Creating Orders Table 
        public static String createOrdersTB = "create table TBOrders(orderId integer primary key autoincrement," + "AgentID  integer not null,"
            + "CustID integer not null," + "Orders text not null," + "Status integer not null" + "SendDate text not null)";

        // Drop if Orders table exist
        public static String dropOrders = "drop table if exists" + TBOrders;
    }

Answers

  • slink99slink99 Member ✭✭

    I have found https://stackoverflow.com/questions/18370219/how-to-use-adb-in-android-studio-to-view-an-sqlite-db but am failing on the run-as command. Haven't yet tried adb shell.

  • slink99slink99 Member ✭✭

    VS2019 - Android Device Monitor - File Explorer would seem to be the answer, for VS2019 user at least.

  • JarvanJarvan Member, Xamarin Team Xamurai

    Here is a tutorial about How to Access SQLite Database in Android using ADB Shell:

    1. Finding SDK Location in your VS
    2. Going to the SDK Location in Command Prompt
    3. Enabling ADB Root
    4. Listing Devices
    5. Connecting with Device Shell
    6. Going to Database Folder of the Device
    7. Connecting to SQLite Database

    you can refer to: https://www.simplifiedcoding.net/access-sqlite-database/

  • JunCainJunCain Member ✭✭✭

    I already tried this but the output is like this..

  • JarvanJarvan Member, Xamarin Team Xamurai

    Have you created the table TBProduct successfully? If the table is created, you can view it by '.tables' command. But nothing shows in your screenshot.

  • JunCainJunCain Member ✭✭✭

    all the table created successful and all the have a value

  • JarvanJarvan Member, Xamarin Team Xamurai

    I've tested the code above. After entering database folder, you can use 'ls' command to view the content in it. 'sqlite3 xx.db' will create a database if the file not exist.

    From the picture you posted, the OrderingApp.db is just created. Because nothing shows in the database when executing ’.table‘ command.

    Is the path of databases correct?

  • JunCainJunCain Member ✭✭✭
    edited June 2019

    Hi Sir @yelinzh if I input ls all my project will display
    OrderingApp.OrderingApp this app had an OrderingApp.db

    I didn't specify the path of my database, I think my database place into default folder

  • JarvanJarvan Member, Xamarin Team Xamurai

    cd OrderingApp.OrderingApp -> ls, if the OrderingApp.db exists, sqlite3.OrderingApp.db. Then perform sqlite operation.

Sign In or Register to comment.