SQLite use

Tags: begin LSE file image into tab view unzip each

First download from https://www.sqlite.org/download.html

When the download is complete, unzip the file to D:\sqlite

Run Sqlite3.exe to see if it works

Download visualization tool https://github.com/sqlitebrowser/sqlitebrowser/releases

Creating table structures with visual tools

The preparation is done and the next step is C # operation SQLite

Add the required DLLs in the NuGet package management tool

To configure the database connection string in the config file

  < connectionStrings >    <  name= "Sqliteconn"  connectionString= "Data source=: \.. \database\dbareainfo.db; Pooling=true; Failifmissing=false "  providerName=" System.Data.SQLite "/>  </ connectionStrings >

Next is a simple sqlitehelper.

1      Public classSqlitehelper2     {3         Private Static stringconnstring = system.configuration.configurationmanager.connectionstrings["Sqliteconn"]. ToString ();4         Private StaticSqliteconnection Conn;5 6         StaticSqlitehelper ()7         {8conn =Newsqliteconnection (connstring);9         }Ten  One  A         /// <summary> -         ///querying data, returning a dataset -         /// </summary> the         /// <param name= "SQL" ></param> -         /// <returns></returns> -          Public StaticDataSet Querysql (stringsql) -         { +DataSet ds =NewDataSet (); -             Try +             { A                 using(Sqlitedataadapter adapter =Newsqlitedataadapter (SQL, conn)) at                 { - Conn. Open (); - adapter. Fill (DS); - Conn. Close (); -                 } -             } in             Catch { } -             returnds; to         } +  -         /// <summary> the         ///execute a single SQL statement *         /// </summary> $         /// <param name= "SQL" ></param>Panax Notoginseng         /// <returns></returns> -          Public Static BOOLExecuteSQL (stringsql) the         { +             BOOLresult =false; A             Try the             { +                 using(Sqlitecommand cmd =NewSqlitecommand (conn)) -                 { $ Conn. Open (); $Cmd.commandtext =SQL; -result = cmd. ExecuteNonQuery () >0; - Conn. Close (); the                 } -             }Wuyi             Catch { } the             returnresult; -         } Wu  -         /// <summary> About         ///transactions execute multiple SQL statements $         /// </summary> -         /// <param name= "Lstsql" ></param> -         /// <returns></returns> -          Public Static BOOLExecuteSQL (list<string>lstsql) A         { +             BOOLresult =false; the             Try -             { $                 using(Sqlitecommand cmd =NewSqlitecommand (conn)) the                 { the Conn. Open (); theDbtransaction trans =Conn. BeginTransaction (); the                     foreach(varSqlinchlstsql) -                     { inCmd.commandtext =SQL; the cmd. ExecuteNonQuery (); the                     } About trans.commit (); theresult =true; the Conn. Close (); the                 } +             } -             Catch { } the             returnresult;Bayi         } the}

Two simple invocation examples

Inquire

1         /// <summary>2         ///Get Zone name3         /// </summary>4         /// <param name= "AreaCode" >Area number</param>5         /// <returns></returns>6          Public stringGetareaname (stringAreaCode)7         {8             stringresult =string. Empty;;9             stringsql ="Select AreaName from Areainfo where areacode= '"+ AreaCode +"'";TenDataSet ds =sqlitehelper.querysql (SQL); One             if(ds! =NULL&& ds. Tables.count >0&& ds. tables[0]. Rows.Count = =1) A             { -result = ds. tables[0]. rows[0]["AreaName"]. ToString (); -             } the             returnresult; -}

Delete and add

1          Public voidInsertareasource ()2         {3             stringsql ="Delete from Areainfo";4 sqlitehelper.executesql (SQL);5 6Areainfo Areainfo =Newareainfo ();7List<area> Lstarea =Areainfo.getareainfofromweb ();8list<string> lstsql =Newlist<string>();9             foreach(Area iteminchLstarea)Ten             { Onesql ="INSERT into Areainfo (areacode,areaname) VALUES ('"+ Item. AreaCode +"', '"+ Item. AreaName +"')"; A lstsql.add (SQL); -             } - Sqlitehelper.executesql (lstsql); the}

SQLite use