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

  • Question

  • User340533 posted

    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;
            }
        }
    
    Thursday, September 19, 2019 2:58 PM

All replies

  • User340533 posted

    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}"; } } }

    Thursday, September 19, 2019 4:13 PM