System. Data. SQLite database Introduction

Copyright:Original Works are allowed to be reproduced. During reprinting, be sure to mark them as hyperlinks.ArticleOriginal source, author information, and this statement. Otherwise, legal liability will be held. Http://zhoufoxcn.blog.51cto.com/792419/292670

SQLite Introduction In the introduction of system. data. SQLite is a single-host database management system similar to access. It defines all databases (including definitions, tables, indexes, and data) are saved in a single file. In addition, SQLite is a class library implemented in C. It has good performance in terms of memory consumption, file size, and simplicity. If the data size is less than 10 million, the query speed is also quite fast. SQLite has the following features: Standard for most sql92 implementations, including transactions (atomicity, consistency, isolation and persistence), triggers, and most complex queries. If you do not check the type of the inserted or updated data, you can insert the string into the integer column (this may make some users not suitable ). Supports mainstream systems such as Windows, Linux, and UNIX, as well as embedded systems such as Android and Windows Mobile. System. Data. SQLite System. data. SQLite is an enhanced version of SQLite. NET framework, because it contains an ADO. NET 2.0 engine, so. NET developers can use system. data. SQLite is easy to develop. net Program . System. data. SQLite and SQLite also have some restrictions. For example, row-level and table-level locks are not supported. When a connection locks the database for writing data, other database connections can only be read and written after the connection operation is complete, SQLite. net has tried many times in the ultra-short period. In fact, for large applications, we will choose some large and professional databases. system. Data. SQLite and SQLite are suitable for some limited occasions, such as mobile phones. Here I am going to talk about a real experience. Before that, I had a small system and wanted to analyze three Excel files, two of which had about 400 records, the other one is about 10 thousand. If such a system uses a database, even with the single-host access, it is relatively simple to use the database features for analysis after import, because we can use connection queries in the database and database functions for records, the information provided by the other party is that although the Office is installed on the deployed machine, however, only Word, Excel, and outlook are installed without access, and the other party does not want to install other software, because I am not sure whether access can be accessed through oledb on the machine without access. MDB file, so there is no way, only in the form of a memory table, will read the data in Excel to the datatable, and then analyze the three datatable, despite a lot of optimization, however, the efficiency is still not ideal. In this case, if I knew. data. SQLite is much easier to do. It imports data from three Excel files to system. data. SQLite, and then use system. data. the functions provided by SQLite are easy to process. For system. Data. SQLite, you do not need to install it during deployment. Only one system. Data. SQLite. dll is required. This dll is only 866 kb! In addition, it does not need to be registered as it uses COM components. Operate System. Data. SQLite in vs2008 To facilitate developers, system. data. SQLite provides support for vs2005 and vs2008, and even supports. net 3.5 SP1 Entity Framework, the following is the use of system in vs2008. data. SQLite designer: First open the server resource manager in vs2008, such: Right-click the data connection, as shown in: Then select "add connection", as shown in: In this case, select system. data. the database file used by SQLite. The default file suffix is. db3, you can also click the "test connection" button below. If no problem exists, the correct dialog box is displayed, after you click "OK", the following error occurs in the server resource manager: In this way, we can operate the tables in system. Data. SQLite just like the database in SQL Server. System. Data. SQLite Common Database Class Database operations can be divided into the following situations: Create Database files; Return datatable; Return datareader; Add, delete, modify, and return the number of affected rows; Execute the query and return the first column of the First row (usually used for queries with row functions, such as sum/AVG/count ); Returns all tables in the database; Because in system. data. SQLite does not contain stored procedures. Therefore, all operations are text-based SQL statements. To avoid SQL injection, parameterized SQL statements are used. The common database class is as follows: using system; Using system. Data; Using system. Data. Common; Using system. Data. SQLite; Namespace sqlitequerybrowser { /// <Summary> /// Description: This is a general class encapsulated by conventional database operations for system. Data. SQLite. /// Author: zhoufoxcn (Zhou Gong) /// Date: 2010-04-01 // Blog: http://zhoufoxcn.blog.51cto.com or http://blog.csdn.net/zhoufoxcn /// Version: 0.1 /// </Summary> Public class sqlitedbhelper { Private string connectionstring = string. empty; /// <Summary> /// Constructor /// </Summary> /// <Param name = "dbpath"> SQLite database file path </param> Public sqlitedbhelper (string dbpath) { This. connectionstring = "Data Source =" + dbpath; } /// <Summary> /// Create an SQLite database file /// </Summary> /// <Param name = "dbpath"> path of the SQLite database file to be created </param> Public static void createdb (string dbpath) { Using (sqliteconnection connection = new sqliteconnection ("Data Source =" + dbpath )) { Connection. open (); Using (sqlitecommand command = new sqlitecommand (connection )) { Command. commandtext = "create table demo (ID integer not null primary key autoincrement unique )"; Command. executenonquery (); Command. commandtext = "Drop table Demo "; Command. executenonquery (); } } } /// <Summary> /// Add, delete, modify, and return the number of affected rows. /// </Summary> /// <Param name = "SQL"> SQL statement to be executed </param> /// <Param name = "Parameters"> parameters required for executing the add, delete, and modify statements. The parameters must follow the order in the SQL statements. </param> /// <Returns> </returns> Public int executenonquery (string SQL, sqliteparameter [] parameters) { Int affectedrows = 0; Using (sqliteconnection connection = new sqliteconnection (connectionstring )) { Connection. open (); Using (dbtransaction transaction = connection. begintransaction ()) { Using (sqlitecommand command = new sqlitecommand (connection )) { Command. commandtext = SQL; If (parameters! = NULL) { Command. Parameters. addrange (parameters ); } Affectedrows = command. executenonquery (); } Transaction. Commit (); } } Return affectedrows; } /// <Summary> /// Execute a query statement and return an associated sqlitedatareader instance /// </Summary> /// <Param name = "SQL"> query statement to be executed </param> /// <Param name = "Parameters"> the parameters required to execute an SQL query statement must follow the order of the parameters in the SQL statement. </param> /// <Returns> </returns> Public sqlitedatareader executereader (string SQL, sqliteparameter [] parameters) { Sqliteconnection connection = new sqliteconnection (connectionstring ); Sqlitecommand command = new sqlitecommand (SQL, connection ); If (parameters! = NULL) { Command. Parameters. addrange (parameters ); } Connection. open (); Return command. executereader (commandbehavior. closeconnection ); } /// <Summary> /// Execute a query statement and return a datatable containing the query result /// </Summary> /// <Param name = "SQL"> query statement to be executed </param> /// <Param name = "Parameters"> the parameters required to execute an SQL query statement must follow the order of the parameters in the SQL statement. </param> /// <Returns> </returns> Public datatable executedatatable (string SQL, sqliteparameter [] parameters) { Using (sqliteconnection connection = new sqliteconnection (connectionstring )) { Using (sqlitecommand command = new sqlitecommand (SQL, connection )) { If (parameters! = NULL) { Command. Parameters. addrange (parameters ); } Sqlitedataadapter adapter = new sqlitedataadapter (command );