How to convert sqlite content into json and save to network db using web api using PostSync

I am attempting to PostSync content from a Sqlite database within my Android app. "var content" always comes up empty and nothing is passed. Any suggestions?

        btnUpdate.Click += async delegate
        {
            using (var client = new HttpClient())
            {
                var uri = new Uri(string.Format("http://###.##.#.##:8181/api/customers", string.Empty));
                Customers newCust = new Customers();

                var conn = new SqliteConnection("Data Source=" + databasePath);
                var sql = "SELECT CustomerID, FirstName, LastName, HouseName FROM Customers";
                var cmd = new SqliteCommand(sql, conn) { CommandType = CommandType.Text };

                 try
                 {
                        Customers cust = new Customers {  };
                        conn.Open();
                        string s = "";
                        SqliteDataReader reader = cmd.ExecuteReader();
                        //NEED TO CAST EACH JSON 'Customer' INTO A NEW OBJECT AND SAVE IT
                        while (reader.Read())
                        {
                            var myContent = Serialize(reader);
                            string json = JsonConvert.SerializeObject(myContent, Formatting.None);
                            var content = new StringContent(json, Encoding.UTF8, "application/json");
                            HttpResponseMessage res2 = await client.PostAsync(uri.ToString(), content);
                            await HandleResponse(res2);
                        }
                        txtProof.Text = res2.RequestMessage.ToString();
                        //AFTER EACH IS UPLOADED TO SANDBOXDB, DELETE ALL ENTRIES
                 }
                 catch (Exception ex)
                 {
                        txtProof.Text = "Exception: " + ex.Message;
                 }
             }
         };
   public IEnumerable<Dictionary<string, object>> Serialize(Mono.Data.Sqlite.SqliteDataReader reader)
    {
        var results = new List<Dictionary<string, object>>();
        var cols = new List<string>();
        for (var i = 0; i < reader.FieldCount; i++)
            cols.Add(reader.GetName(i));

        while (reader.Read())
            results.Add(SerializeRow(cols, reader));

        return results;
    }

Here is the function used to create the Sqlite database

    void BtnCreateDatabase_Click(object sender, EventArgs e)
    {
        if (File.Exists(databasePath))
            File.Delete(databasePath);
        if (!File.Exists(databasePath))
        {
            SqliteConnection.CreateFile(databasePath);
        }
        CreateDatabase(databasePath);
    }
    public void CreateDatabase(string DatabasePath)
    {
        var conn = new SqliteConnection("Data Source=" + DatabasePath);
        try
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                conn.Open();

                cmd.CommandText = "CREATE TABLE IF NOT EXISTS State (" +
                    "StateID INTEGER PRIMARY KEY AUTOINCREMENT, " + 
                    "Name VARCHAR(50))";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE TABLE IF NOT EXISTS Customers (" +
                    "CustomerID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "FirstName VARCHAR(100), " + 
                    "LastName VARCHAR(100), " + 
                    "Phone VARCHAR(25), " + 
                    "Address VARCHAR(100), " + 
                    "City VARCHAR(50), " +
                    "StateID INTEGER, " + 
                    "HouseName VARCHAR(25), " +
                    "FOREIGN KEY(StateID) REFERENCES State(StateID))";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE INDEX IF NOT EXISTS IDX_LastName ON Customers (LastName)";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO State (Name) VALUES ('Virginia');";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO State (Name) VALUES ('Maryland');";
                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }
        catch (Exception ex)
        {
            txtProof.Text = "Exception: " + ex.Message;
        }
    }

Answers

  • I left off the Customers class:

    using System;
    using Newtonsoft.Json;
    namespace CreatingSqlLiteDatabase
    {
    class Customers
    {
    public int CustomerID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Phone { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public int? StateID { get; set; }
    public string HouseName { get; set; }
    public override string ToString()
    {
    return $"Customer Id: {CustomerID}\nLast Name: {LastName}\nFirst Name: {FirstName}";
    }
    }
    }

Sign In or Register to comment.