CODEWITHSUNDEEP
c#sqliteobjectmapping
Profile2ForStack
Profile2ForStack

Reputation: 493

How to map C# object to SQLite database table?

I have the following SQLite table

enter image description here

And I have the following C# classes:

[XmlRoot(ElementName = "articlessql", Namespace = "http://tempuri.org/DataSet1.xsd")]
public class Article
{
    [XmlElement(ElementName = "id", Namespace = "http://tempuri.org/DataSet1.xsd")]
    public string Id { get; set; }
    [XmlElement(ElementName = "name", Namespace = "http://tempuri.org/DataSet1.xsd")]
    public string Name { get; set; }
    [XmlElement(ElementName = "quantity", Namespace = "http://tempuri.org/DataSet1.xsd")]
    public string Quantity { get; set; }
    [XmlElement(ElementName = "sell_price", Namespace = "http://tempuri.org/DataSet1.xsd")]
    public string SellPrice { get; set; }
    [XmlElement(ElementName = "sellid", Namespace = "http://tempuri.org/DataSet1.xsd")]
    public string SellId { get; set; }
    [XmlElement(ElementName = "whole_price", Namespace = "http://tempuri.org/DataSet1.xsd")]
    public string WholePrice { get; set; }
    [XmlElement(ElementName = "group", Namespace = "http://tempuri.org/DataSet1.xsd")]
    public string Group { get; set; }
    [XmlElement(ElementName = "barcode", Namespace = "http://tempuri.org/DataSet1.xsd")]
    public string Barcode { get; set; }
    [XmlElement(ElementName = "measure", Namespace = "http://tempuri.org/DataSet1.xsd")]
    public string Measure { get; set; }
}

[XmlRoot(ElementName = "DataSet1", Namespace = "http://tempuri.org/DataSet1.xsd")]
public class Articles
{
    [XmlElement(ElementName = "articlessql", Namespace = "http://tempuri.org/DataSet1.xsd")]
    public List<Article> AllArticles { get; set; }
    [XmlAttribute(AttributeName = "xmlns")]
    public string Xmlns { get; set; }
}

I get list of articles like that:

XmlSerializer deserializer = new XmlSerializer(typeof(Articles));
TextReader textReader = new StreamReader(xmlFilePath);
Articles articles = new Articles();
articles = (Articles)deserializer.Deserialize(textReader);
textReader.Close();

But when I try to insert the list of articles in the table articles like that:

using (var connection = new SQLiteConnection(System.IO.Path.Combine(apkFolder, "MobileSell.db")))
{
    connection.InsertAll(articles.AllArticles);
}

It throws exception:

SQLite.SQLiteException: no such table: Article at SQLite.SQLite3.Prepare2

My question is how can I make the program understand that Article object must be a single row in table articles

Upvotes: 1

Views: 5472

Answers (1)

David Hruška
David Hruška

Reputation: 168

did you read SQLite specification?

SQLite has attributes like [Column] and [Table] attributes. You should use those. Like this:

[Table("ContactInfo")] // SQLite attribute
[DataContract(Name = "ContactInfo", Namespace = "")]
public sealed class ContactInfo : DatabaseTableBase
{
    /// <summary>
    /// Unique Id of the codebook.
    /// </summary>
    [PrimaryKey] // SQLite attribute
    [AutoIncrement] // SQLite attribute
    [Column("UniqueId")] // SQLite attribute
    [IgnoreDataMember]
    public override long UniqueId { get; set; }

    [Column("ClaimId")] // SQLite attribute
    [NotNull] // SQLite attribute
    public long ClaimId { get; set; }

    [Column("ContactType")] // SQLite attribute
    [NotNull] // SQLite attribute
    public ContactTypes ContactType { get; set; }

    public ContactInfo()
    {
    }
}

In your case, you will be annotating with attributes your class Article

Hope it helps.

Upvotes: 2

Related Questions