The eighth assignment

The last said ... SQLite in Windows Store app with Windows Phone app ...

Let's talk about the application of SQLite in WPF today ...

1. Pre-application preparation

First of all.. SQLite does not have a built-in library in WPF development like the last two apps ... So you can only use the existing database files for database operation ...

Then the first to build a database file ... So after Baidu after a lot of screening. Selected a DatabaseNet4 Such a database visualizer:

Support a lot of databases ... We choose SQLite

Build the library. The generated files are placed in the debug directory under the Bin directory under the project folder ...

So we're done with the database file ...

Then how do we connect to the database via C # code?

At this point we need an interface to implement this function ...

However, the VS2013 is not self-bringing this interface. So we need to go to the Sqlite website to download the System.Data.Sqlite.dll file

Http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

This is probably the site. Locate the file that corresponds to the. NET version and operating system that you are using.

This interface can be referenced in WPF engineering after download and installation ...

Right-click Project, add-on reference

Click OK next to browse. Find the System.Data.Sqlite.dll in the directory you just installed, OK.

It's not over yet ... One last step.

Locate the project configuration file, app. Config, and empty the contents into the following code

<?xml version= "1.0"?>
<configuration>

<startup uselegacyv2runtimeactivationpolicy= "true" >
<supportedruntime version= "v4.0"/>
<requiredruntime version= "v4.0.20506"/>
</startup>

</configuration>

So the preparation is finished .....

2. Application in the program

The first is to connect the database ...

string DataSource;        Sqliteconnection Conn; = Path.Combine ("Data Source =" @ "\test.sqlite")           );         New sqliteconnection (datasource);        Conn. Open ();

Insert data (the statement will be different according to the previous table, in fact, it is a normal SQL statement

　　　　　 Public void insert (Affair Affair)        {            stringstring. Format ("INSERT into Affair VALUES (' {0} ', ' {1} ', ' {2} ', {3},{4},{5},{6})",                         Affair.date,affair.name,affair.content,affair.importance,affair.year,affair.month,affair.day);             New Sqlitecommand (sql,conn);            Cmd. ExecuteNonQuery ();        }

Delete data

  　　　　  Public voidDelete (Affair affair) {stringsql =string. Format ("Delete from affair where date= ' {0} ' and name = ' {1} '"+"and importance ={2} and Year = {3} and month = {4} and day = {5}", Affair.date,affair.name,affair.importance,affair.year,affair.month,affair.day); Sqlitecommand cmd=Newsqlitecommand (SQL, conn); intA=cmd.            ExecuteNonQuery (); if(A = =0) MessageBox.Show ("Delete Failed"); }

SELECT statement

 Publicdatarow[] SelectAll () {stringsql =string. Format ("SELECT * from Affair"); Sqlitecommand cmd=Newsqlitecommand (SQL, conn); DataTable DT=NewDataTable (); Sqlitedataadapter da=NewSqlitedataadapter (); Da. SelectCommand=cmd; Da.            Fill (DT); Datarow[] FoundRow=dt.            Select (); returnFoundRow; }

Attention. This write returns an array of DataRow, equivalent to a table that can be accessed by foundrow[i][j] to access the elements of the i-1 row j-1 column

The order of the columns is the same as the columns of the table you built:

Just like the following code

Database db =Newdatabase (); Datarow[] FoundRow= db.SelectAll();  for(intI=0; I<foundrow. length;i++) {Affair Tempa=NewAffair (); Tempa.date= (string) foundrow[i][0]; Tempa.name= (string) foundrow[i][1]; Tempa.content= (string) foundrow[i][2]; Tempa.importance= (int) foundrow[i][3]; Tempa.year= (int) foundrow[i][4]; Tempa.month= (int) foundrow[i][5]; Tempa.day= (int) foundrow[i][6]; MyItems Item=Newmyitems (Tempa);//myitems is the control I wrote myself. I just want you to see what you can do with the data you've chosen .LISTVIEW1.ITEMS.ADD (item); //            }

Of course, if you want to choose other data can be in my selectall () in the form of free play.

Well. Today's class is the first to talk about here ...

The eighth assignment