Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 173 lines (114 sloc) 7.605 kb
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
1
d809287 @praeclarum Updated README info and formatting
authored
2 # sqlite-net
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
3
d809287 @praeclarum Updated README info and formatting
authored
4 sqlite-net is an open source, minimal library to allow .NET and Mono applications to store data in [http://www.sqlite.org SQLite 3 databases]. It is written in C# and is meant to be simply compiled in with your projects. It was first designed to work with [MonoTouch](http://xamarin.com) on the iPhone, but has grown up to work on all the platforms (Mono for Android, .NET, Silverlight, WP7, WinRT, Azure, etc.).
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
5
6 sqlite-net was designed as a quick and convenient database layer. Its design follows from these *goals*:
7
d809287 @praeclarum Updated README info and formatting
authored
8 * Very easy to integrate with existing projects and with MonoTouch projects.
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
9
d809287 @praeclarum Updated README info and formatting
authored
10 * Thin wrapper over SQLite and should be fast and efficient. (The library should not be the performance bottleneck of your queries.)
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
11
d809287 @praeclarum Updated README info and formatting
authored
12 * Very simple methods for executing CRUD operations and queries safely (using parameters) and for retrieving the results of those query in a strongly typed fashion.
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
13
d809287 @praeclarum Updated README info and formatting
authored
14 * Works with your data model without forcing you to change your classes. (Contains a small reflection-driven ORM layer.)
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
15
d809287 @praeclarum Updated README info and formatting
authored
16 * 0 dependencies aside from a [compiled form of the sqlite2 library](http://www.sqlite.org/download.html).
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
17
18 *Non-goals* include:
19
d809287 @praeclarum Updated README info and formatting
authored
20 * Not an ADO.NET implementation. This is not a full SQLite driver. If you need that, use [Mono.Data.SQLite](http://www.mono-project.com/SQLite) or [csharp-sqlite](http://code.google.com/p/csharp-sqlite/).
21
22
23 ## Meta
24
25 This is an open source project that welcomes contributions/suggestions/bug reports from those who use it. If you have any ideas on how to improve the library, please [post an issue here on github](https://github.com/praeclarum/sqlite-net/issues). Please check out the [How to Contribute](https://github.com/praeclarum/sqlite-net/wiki/How-to-Contribute).
26
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
27
d809287 @praeclarum Updated README info and formatting
authored
28 # Example Time!
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
29
d809287 @praeclarum Updated README info and formatting
authored
30 Please consult the Wiki for, ahem, [complete documentation](https://github.com/praeclarum/sqlite-net/wiki).
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
31
32 The library contains simple attributes that you can use to control the construction of tables. In a simple stock program, you might use:
33
34 public class Stock
35 {
36 [PrimaryKey, AutoIncrement]
37 public int Id { get; set; }
38 [MaxLength(8)]
39 public string Symbol { get; set; }
40 }
41
42 public class Valuation
43 {
44 [PrimaryKey, AutoIncrement]
45 public int Id { get; set; }
46 [Indexed]
47 public int StockId { get; set; }
48 public DateTime Time { get; set; }
49 public decimal Price { get; set; }
50 }
51
6b56e20 Updated readme to include async documentation
mbrit authored
52 Once you've defined the objects in your model you have a choice of APIs. You can use the "synchronous API" where calls
53 block one at a time, or you can use the "asynchronous API" where calls do not block. You may care to use the asynchronous
54 API for mobile applications in order to increase reponsiveness.
55
56 Both APIs are explained in the two sections below.
57
d809287 @praeclarum Updated README info and formatting
authored
58 ## Synchronous API
59
6b56e20 Updated readme to include async documentation
mbrit authored
60 Once you have defined your entity, you can automatically generate tables in your database by calling `CreateTable`:
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
61
62 var db = new SQLiteConnection("foofoo");
63 db.CreateTable<Stock>();
64 db.CreateTable<Valuation>();
65
66 You can insert rows in the database using `Insert`. If the table contains an auto-incremented primary key, then the value for that key will be available to you after the insert:
67
68 public static void AddStock(SQLiteConnection db, string symbol) {
69 var s = db.Insert(new Stock() {
70 Symbol = symbol
71 });
72 Console.WriteLine("{0} == {1}", s.Symbol, s.Id);
73 }
74
6b56e20 Updated readme to include async documentation
mbrit authored
75 Similar methods exist for `Update` and `Delete`.
76
77 The most straightforward way to query for data is using the `Table` method. This can take predicates for constraining via WHERE clauses and/or adding ORDER BY clauses:
78
79 var conn = new SQLiteConnection("foofoo");
80 var query = conn.Table<Stock>().Where(v => v.Symbol.StartsWith("A"));
81
82 foreach (var stock in query)
83 Debug.WriteLine("Stock: " + stock.Symbol);
84
85 You can also query the database at a low-level using the `Query` method:
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
86
87 public static IEnumerable<Valuation> QueryValuations (SQLiteConnection db, Stock stock)
88 {
89 return db.Query<Valuation> ("select * from Valuation where StockId = ?", stock.Id);
90 }
91
92 The generic parameter to the `Query` method specifies the type of object to create for each row. It can be one of your table classes, or any other class whose public properties match the column returned by the query. For instance, we could rewrite the above query as:
93
94 public class Val {
95 public decimal Money { get; set; }
96 public DateTime Date { get; set; }
97 }
98 public static IEnumerable<Val> QueryVals (SQLiteConnection db, Stock stock)
99 {
100 return db.Query<Val> ("select 'Price' as 'Money', 'Time' as 'Date' from Valuation where StockId = ?", stock.Id);
101 }
102
6b56e20 Updated readme to include async documentation
mbrit authored
103 You can perform low-level updates of the database using the `Execute` method.
104
d809287 @praeclarum Updated README info and formatting
authored
105 ## Asynchronous API
106
6b56e20 Updated readme to include async documentation
mbrit authored
107 The asynchronous library uses the Task Parallel Library (TPL). As such, normal use of `Task` objects, and the `async` and `await` keywords
108 will work for you.
109
110 Once you have defined your entity, you can automatically generate tables by calling `CreateTableAsync`:
111
112 var conn = new SQLiteAsyncConnection("foofoo");
113 conn.CreateTableAsync<Stock>().ContinueWith((results) =>
114 {
115 Debug.WriteLine("Table created!");
116 });
117
118 You can insert rows in the database using `Insert`. If the table contains an auto-incremented primary key, then the value for that key will be available to you after the insert:
119
120 Stock stock = new Stock()
121 {
122 Symbol = "AAPL"
123 };
124
125 var conn = new SQLiteAsyncConnection("foofoo");
126 conn.InsertAsync(stock).ContinueWith((t) =>
127 {
128 Debug.WriteLine("New customer ID: {0}", stock.Id);
129 });
130
131 Similar methods exist for `UpdateAsync` and `DeleteAsync`.
132
133 Querying for data is most straightforwardly done using the `Table` method. This will return an `AsyncTableQuery` instance back, whereupon
134 you can add predictates for constraining via WHERE clauses and/or adding ORDER BY. The database is not physically touched until one of the special
135 retrieval methods - `ToListAsync`, `FirstAsync`, or `FirstOrDefaultAsync` - is called.
136
137 var conn = new SQLiteAsyncConnection("foofoo");
138 var query = conn.Table<Stock>().Where(v => v.Symbol.StartsWith("A"));
139
140 query.ToListAsync().ContinueWith((t) =>
141 {
142 foreach (var stock in t.Result)
143 Debug.WriteLine("Stock: " + stock.Symbol);
144 });
145
146 There are a number of low-level methods available. You can also query the database directly via the `QueryAsync` method. Over and above the change
147 operations provided by `InsertAsync` etc you can issue `ExecuteAsync` methods to change sets of data directly within the database.
148
149 Another helpful method is `ExecuteScalarAsync`. This allows you to return a scalar value from the database easily:
150
151 var conn = new SQLiteAsyncConnection("foofoo");
152 conn.ExecuteScalarAsync<int>("select count(*) from Stock", null).ContinueWith((t) =>
153 {
154 Debug.WriteLine(string.Format("Found '{0}' stock items.", t.Result));
155 });
156
157
d809287 @praeclarum Updated README info and formatting
authored
158 ## Special note on use within WinRT/Windows 8/Metro Style/whatevr
6b56e20 Updated readme to include async documentation
mbrit authored
159
d809287 @praeclarum Updated README info and formatting
authored
160 sqlite-net is fully compliant with WinRT Metro-style apps and will pass Microsoft Store validation.
161
162 Please note:
6b56e20 Updated readme to include async documentation
mbrit authored
163
164 * Database files will always be created in the path returned by `Windows.Storage.ApplicationData.Current.LocalFolder.Path`.
165
166 * You will need a copy of sqlite3.dll that has been compiled against SQLite.org's WinRT branch. Although this isn't in mainstream
167 support, it is expected to be. You can find more information on that and download a properly compiled sqlite3.dll from
168 [https://github.com/mbrit/sqlite-metrostyle].
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
169
d809287 @praeclarum Updated README info and formatting
authored
170 * We supply a [WinRT/Windows 8 sqlite3.dll](https://github.com/praeclarum/sqlite-net/tree/master/lib/metro)
33e404b @praeclarum Patched koush's fork to sync with sqlite-net 71
authored
171
172
Something went wrong with that request. Please try again.