Summary of operations related to WinPhone development database

1. First of all, let's talk about WP's support for .sqlite database operations. The following methods were found from Google, Baidu, Codeplex, etc.;

(1) The oldest one is to use the third-party Community.CsharpSqlite.WP.dll assembly (Note: If you want to operate an existing .sqlite database, instead of creating a data table from scratch, adding data, etc., then you need to find this source code on the codeplex website and make corresponding modifications to support use. Otherwise, you will encounter very depressing errors such as "Unable to open database connection...")

(2) The second type is similar to the first type, but its package is different. C#-SQLiteWP7.Preview1.Release, which is also on Codeplex. The code usage is similar to the first type, but the method inside returns objects such as DataReader, which makes it convenient for us to perform corresponding data reading operations. Although the database is also copied to the root directory of the independent storage, the connection string here is different. The format is as follows:
"Version=database version number, uri=file:your database full name"

Simple Code operation process:

 using (SqliteConnection conn = new SqliteConnection( "Version=3,uri=file:test.db" )) 
 
 { 
 
 conn.Open(); 
 
 using (SqliteCommand cmd = conn.CreateCommand()) 
 
 { 
 
 cmd.CommandText = "sql statement" ; 
 
 cmd.ExecuteNonQuery(); 
 
 
 
 cmd.Transaction = conn.BeginTransaction(); 
 
 //SQL statement to add parameters  
 cmd.CommandText = "INSERT INTO test(col, col2, col3, col4, col5) VALUES(@col, @col2, @col3, @col4, @col5);SELECT last_insert_rowid();" ; 
 
 cmd.Parameters.Add( "@col" , null ); 
 
 cmd.Parameters.Add( "@col2" , null ); 
 
 cmd.Parameters.Add( "@col3" , null ); 
 
 cmd.Parameters.Add( "@col4" , null ); 
 
 cmd.Parameters.Add( "@col5" , null ); 
 
 cmd.Transaction.Commit(); 
 
 cmd.Transaction = null ;

If you don't want to modify the source code of Community.CsharpSqlite.WP, then look for the two assemblies Vici.CoolStorage.WP7 and Vici.Core.WP7 on the Internet. I personally feel that this method is simple to operate and has slightly better performance than the first method.

 //Note: Add Vici.CoolStorage.WP7.dll and Vici.Core.WP7.dll to the project first  
 string fn = "MNSECRET.DB" ; //your database name, please put it in the project root directory, and set the generation operation to Resource, do not copy  
            Assembly assem = Assembly.GetExecutingAssembly();
            string assemeblyName=assem.FullName.Substring( 0 , assem.FullName.IndexOf( ',' ));
            Uri dbURi= new Uri( "/" + assemeblyName + ";component/" + fn,
 UriKind.Relative);
 //The first run of the program copies the SQLite database to local storage  
            StreamResourceInfo sr = Application.GetResourceStream(dbURi);
            IsolatedStorageFile iStorage = IsolatedStorageFile.GetUserStoreForApplication();
 if (!iStorage.FileExists(fn))
 {
                using (var outputStream = iStorage.OpenFile(fn, FileMode.CreateNew))
 {
 byte [] buffer = new   byte [ 10000 ];
 for (; ; )
 {
 int read = sr.Stream.Read(buffer, 0 , buffer.Length); 
 
 if (read <= 0 )
 break ; 
 
                        outputStream.Write(buffer, 0 , read);
 }
 }
 }
 //Connect to the database  
            CSConfig.SetDB(fn);
 //Data operation  
 CSGenericRecordList cslis = CSDatabase.RunQuery( "SELECT* FROM CITY" ); //It can be understood as returning a table  
             foreach (CSGenericRecord cs in cslis)
 {
 //Get each row of data in the table  
 string result= cs[ "data table field name" ].ToString(); 
 
 }

Link to this article: http://wp.662p.com/thread-8290-1-1.html