Share a. NET Compact framework-based SQLite data access component (1)

: Http://download.csdn.net/source/3508105

Recently, I have developed a mobile app, and I need to use the SQLite database. I haven't found a suitable data access component for a long time on the Internet, I had to make a new one based on the existing linq4anydb data access component. After two weeks of efforts, I have achieved some success. Now I am making a contribution for your criticism.

This component is easy to use and supports addition, deletion, modification, and query of the most basic conditions. It supports conditional deletion, conditional modification, and LINQ. To attract your attention, first show the ease of use in the project, and then introduce the usage of this component in detail.

I. convenience in the Development of COM. Yuan. Mobile. sqlitedac:

In the following demo, we use the famous northwind database as our demo database.

1. Add a record:

   For example, we need to insert a new record in the products table of northwind. How can this problem be solved? See the following code.

   // Insert a single record using (var db = new northwind () {dB. products. insertentity (New Products {productname = "iPhone", unitprice = 100.0 M, discontinued = true });}

    

2. Add multiple records:

   If we want to insert multiple new records in the products table of northwind, what should we do? It is also very simple.

   Using (var db = new northwind () {for (INT I = 0; I <10; I ++) {dB. products. insertentity (New Products {productname = string. format ("multiple {0}", I), discontinued = true });}}

   You can also write this statement:

   Using (var db = new northwind () {var newrecords = new products [10]; for (INT I = 0; I <10; I ++) newrecords [I] = new products {productname = string. format ("multiple {0}", I), discontinued = true}; dB. products. insertentity (newrecords );}

   Some may ask, why are you not using transactions when inserting multiple entries? In fact, there is a hidden transaction, which calls dB at the end of the using block. dispose (). At this time, if there is no error, the transaction will be committed. If there is an error, the transaction will be rolled back.

   Of course, transactions can also be explicitly used, as shown below:

   VaR northwind = new northwind (false); northwind. begintransaction (); try {for (INT I = 0; I <10; I ++) {northwind. products. insertentity (New Products {productname = string. format ("multiple {0}", I), discontinued = true});} northwind. commit ();} catch {northwind. rollback ();}

    

3. Delete a record:

   If you want to delete the productid = 11 Record in the products table of the northwind database, write

   Using (var db = new northwind () {var COUNT = dB. products. deleteentity (New Products {productid = 11}); MessageBox. show (string. format ("delete {0} records in total", count ));}

    

4. Delete multiple records:

   If you want to delete the productid = 12 and 13 records in the pruducts table in the northwind database, write as follows:

   Using (var db = new northwind () {var delarray = new products [2]; delarray [0] = new products {productid = 12 }; delarray [1] = new products {productid = 13}; var COUNT = dB. products. deleteentity (delarray); MessageBox. show (string. format ("delete {0} records in total", count ));}

   If you want to delete the productid> = 13 and productid <20 records in the products table of the northwind database, how do you write?

   Using (var db = new northwind () {var COUNT = dB. products. conditionaldeleteentity (New Products {productid = 12}, new products {productid = 20}); MessageBox. show (string. format ("delete productid> = 12 and \ r \ n productid <20 records \ r \ n Delete {0} records", count ));}

   More complicated. Now I need to delete the data with the first word "A" in the products table of the northwind database. How can I write it? This is a little troublesome. You need to use LINQ:

   Using (var db = new northwind () {var COUNT = dB. products. deleteentity (from P in dB. products where p. productname. startswith ("A") select P); MessageBox. show (string. format ("delete all records whose initial character is" A ", \ r \ n total {0} records", count ));}

5. Modify a single record:

   Change the productname record of productid = 11 in the products table of the northwind database to "modify a single test"

   Using (var db = new northwind () {var lst = from P in dB. products where p. productid = 11 select P; If (lst. count ()> 0) {var Product = lst. first (); product. productname = "single modification test"; var COUNT = dB. products. updateentity (product); MessageBox. show ("productid = 11 data modified successfully");} else MessageBox. show ("data with productid = 11 does not exist ");}

6. Modify multiple records:

   Query the productid> 11 and productid <20 records in the products table of the northwind database and modify productname.

   Using (var db = new northwind () {var lst = (from P in dB. products where p. productid> 11 & P. productid <20 select P ). tolist (); For (INT I = 0; I <lst. count; I ++) lst [I]. productname = string. format ("batch modify {0}", I); var COUNT = dB. products. updateentity (LST); MessageBox. show (string. format ("{0} records modified in total", count ));}

   Modify conditions in batches. For example, to modify a field of all records that meet a condition, you can use the condition modification method as follows:

   Using (var db = new northwind () {var COUNT = dB. products. conditionalupdateentity (New Products {unitprice = 10.0 m}, new products {productid = 20}, new products {productid = 30}); If (count> 0) {MessageBox. show (string. format ("modifying productid >=20 and productid <30 data \ r \ n total modification {0} records", count ));}}

   The code above indicates that the unitprice of all records with productid> = 20 and productid <30 is changed to 10.0

Now, the above example proves the ease-of-use and flexibility of the SQLite data access component. I will explain in detail how to use it in the next blog.