How to use SQLite with PetaPoco

Question

I'm try to use PetaPoco with SQLite on MVC5. I created a model to make the mapping.

namespace LittleDemo.Models.UserModel
{
    [PetaPoco.TableName("User")]
    [PetaPoco.PrimaryKey("UserId", AutoIncrement = true)]
    public class User
    {
        [Column]
        public int UserId { get; set; }

        [Column]
        public string Name { get; set; }
    }
}

Then I create a controller file

public ActionResult UserView()
        {
            ViewBag.Message = "Your User page.";

            var dbUser = new PetaPoco.Database("sqlite");

            var userA = new User {Name = "A"};
            dbUser.Save(userA);
            return View();
        }

create the database and put some data in. then create a view to show all the list of the data. but it returns a error message "no user table found". How can I assign the table with PetaPoco.

This seems more linked to SQL lite than PetaPoco... Make sure you have rights to read/write onto your database. And assign User's security correctly so PetaPoco can access it (maybe put more infos on the connection string)... — Minus, Feb 06 '18 at 16:41

score 0 · Answer 1 · answered Mar 21 '18 at 12:36

I have invested a lot of time to make it work but I found nothing.

You could achieve same using https://github.com/schotime/NPoco

Nuget available here https://www.nuget.org/packages/NPoco/

You need to do some work around to make it work.

open app.config/web.config

Step 1:

install https://www.nuget.org/packages/NPoco/

Step 2:

  <system.data>
  <DbProviderFactories>
  <remove invariant="System.Data.SQLite.EF6" />
  <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
  <remove invariant="System.Data.SQLite" /><add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
  </DbProviderFactories>
  </system.data>

Step 3:

<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source = C:\db\test.db;" providerName="System.Data.SQLite" />
</connectionStrings>

Step 4:

 public class User
{
    public int Id { get; set; }
    public string Email { get; set; }
}

static void Main(string[] args)
{
   var config = ConfigurationManager.ConnectionStrings["DefaultConnection"];
   var db = new MyDb(config.ConnectionString, config.ProviderName);
   List<User> users = db.Fetch<User>(string.Empty);
   Console.WriteLine(users.Count);
}

public class MyDb : NPoco.Database
{
    public MyDb()
        : base("DefaultConnection")
    {
    }

    public MyDb(string connectionString, string providerName)
        : base(connectionString, providerName)
    {
    }
}

Reference : https://github.com/venkata-ramana/SQLiteSample

Let me know if you have any queries, Thank you.

I try to make it simple to create dbUser.Execute("create table if not exists UserStatus.... I use this line to create, but this seems little bit hmmm. not convenient — vVvegetable, Mar 27 '18 at 14:13

How to use SQLite with PetaPoco

1 Answers1