vendredi 16 janvier 2015

Using SQLite with Xamarin Android C#

i'm a new developper in Xamarin mobile cross platform development and i have a mistake. I have a database with only one table on this time. Actually i can insert into this table but i don't know how to read in the table after. Can you help me please. This is my database class :



//SQLiteDatabase object for database handling
private SQLiteDatabase sqldb;

//String for Query handling
private string sqldb_query;

//String for Message handling
private string sqldb_message;

//Bool to check for database availability
private bool sqldb_available;

//Zero argument constructor, initializes a new instance of Database class
public Database()
{
sqldb_message = "";
sqldb_available = false;
}

//One argument constructor, initializes a new instance of Database class with database name parameter
public Database(string sqldb_name)
{
try
{
sqldb_message = "";
sqldb_available = false;
CreateDatabase(sqldb_name);
}
catch (SQLiteException ex)
{
sqldb_message = ex.Message;
}
}

//Gets or sets value depending on database availability
public bool DatabaseAvailable
{
get{ return sqldb_available; }
set{ sqldb_available = value; }
}

//Gets or sets the value for message handling
public string Message
{
get{ return sqldb_message; }
set{ sqldb_message = value; }
}

//Creates a new database which name is given by the parameter
public void CreateDatabase(string sqldb_name)
{
try
{
sqldb_message = "";
string sqldb_location = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
string sqldb_path = Path.Combine(sqldb_location, sqldb_name);
bool sqldb_exists = File.Exists(sqldb_path);
if(!sqldb_exists)
{
sqldb = SQLiteDatabase.OpenOrCreateDatabase(sqldb_path,null);
sqldb_query = "CREATE TABLE IF NOT EXISTS informations (_id INTEGER PRIMARY KEY AUTOINCREMENT, borneCode VARCHAR, magasinCode VARCHAR, subsidiary VARCHAR);";
sqldb.ExecSQL(sqldb_query);
sqldb_message = "Database: " + sqldb_name + " created";
}
else
{
sqldb = SQLiteDatabase.OpenDatabase(sqldb_path, null, DatabaseOpenFlags.OpenReadwrite);
sqldb_message = "Database: " + sqldb_name + " opened";
}
sqldb_available=true;
}
catch(SQLiteException ex)

{
sqldb_message = ex.Message;
}
}

//Adds a new record with the given parameters
public void AddRecord(string sborneCode, string smagasinCode, string isubsidiary)
{
try
{
sqldb_query = "INSERT INTO informations (borneCode, magasinCode, subsidiary) VALUES ('" + sborneCode + "','" + smagasinCode + "','" + isubsidiary + "');";
sqldb.ExecSQL(sqldb_query);
sqldb_message = "Record saved";
}
catch(SQLiteException ex)
{
sqldb_message = ex.Message;
}
}

//Updates an existing record with the given parameters depending on id parameter
public void UpdateRecord(int iId, string sborneCode, string smagasinCode, string isubsidiary)
{
try
{
sqldb_query="UPDATE informations SET borneCode ='" + sborneCode + "', magasinCode ='" + smagasinCode + "', subsidiary ='" + isubsidiary + "' WHERE _id ='" + iId + "';";
sqldb.ExecSQL(sqldb_query);
sqldb_message = "Record " + iId + " updated";
}
catch(SQLiteException ex)
{
sqldb_message = ex.Message;
}
}

//Deletes the record associated to id parameter
public void DeleteRecord(int iId)
{
try
{
sqldb_query = "DELETE FROM informations WHERE _id ='" + iId + "';";
sqldb.ExecSQL(sqldb_query);
sqldb_message = "Record " + iId + " deleted";
}
catch(SQLiteException ex)
{
sqldb_message = ex.Message;
}
}


And this is how i insert :



Database db;
string subsidiary = "fr";
string magasinCode = "0000";

protected override void OnCreate (Bundle bundle)
{
base.OnCreate (bundle);

db = new Database ("borne");

// Récupère les éléments de l'UI dans resources/layout/main.axml
SetContentView (Resource.Layout.Main);

// Récupère la textBox où l'on tape le code et le bouton de validation:
EditText code = FindViewById<EditText>(Resource.Id.codeMagasin);
Button button = FindViewById<Button>(Resource.Id.getMagasin);
TextView nomMagasin = FindViewById<TextView> (Resource.Id.nomMagasin);

nomMagasin.Text = db.Message;

// Quand on clique sur valider ...
button.Click += async (sender, e) => {

// On récupère le code saisie et on créé la requête.
string url = website + "?code=" + code.Text;

// Récupère la réponse asynchronously,
// On parse le résultat, puis on met à jour l'écran:
JsonValue json = await FetchWeatherAsync (url);
if (json != null){

db.AddRecord(code.Text, magasinCode, subsidiary);
Console.WriteLine("Insertion OK");

ParseAndDisplay (json);

}
else{
nomMagasin.Text = "Erreur serveur";
}
};
}

// Récupère les informations du magasin en passant en paramètre l'URL du webService.
private async Task<JsonValue> FetchWeatherAsync (string url)
{
// On essaye de récupérer la réponse du serveur
try{
// On créé une requête web HTTP avec l'url:
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create (new Uri (url));
request.ContentType = "application/json";
request.Method = "GET";

// On envoi la requête au serveur et on attend sa réponse:
using (WebResponse response = await request.GetResponseAsync ())
{
// On créé un canal de lecture pour lire la réponse du serveur:
using (Stream stream = response.GetResponseStream ())
{
// On construit le JSON document OBJECT:
JsonValue jsonDoc = await Task.Run (() => JsonObject.Load (stream));
Console.Out.WriteLine("Response: {0}", jsonDoc.ToString ());

// On retourne le json récupéré:
return jsonDoc;
}
}
}
//Si on y arrive pas, on affiche qu'on a une erreur
catch(Exception e){
JsonObject j = null;
return j;
}
}

// Parse le json récupéré et affiche les informations sur l'écran.
private void ParseAndDisplay (JsonValue json)
{
// On récupère la textBox où sera écrit le nom du magasin:
TextView nomMagasin = FindViewById<TextView>(Resource.Id.nomMagasin);

// Récupère le tableau ayant pour nom "magasin".
JsonValue magasin = json["title"];

// Récupère le nom du magasin et l'écrit dans la textBox correspondant:
nomMagasin.Text = magasin;
}

Aucun commentaire:

Enregistrer un commentaire