Getting started with SQLite in C#

Sqlite 前端之家
前端之家收集整理的这篇文章主要介绍了Getting started with SQLite in C#，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

This tutorial will teach you how to create and connect to an sqlite database in C#. You will also learn how to create and modify tables and how to execute sql queries on the database and how to read the returned results.

I’ll assume that you’re already familiar with sql and at least have some knowledge of how it works (for example: what to expect as a result from “select * from table1″ )

There will probably be two parts,with the first one discussing the basics needed to do pretty much anything,and in the second part I’ll discuss some miscellaneous subjects like how to parameterize your queries to make them much faster and safer.

Let’s get started.

Create a standard C# console project.

Since we’re working in C# we’ll be using the System.Data.sqlite library. This library is not a standard library (packaged with .NET for example) so we’ll need to download it. It is being developed by the people who’re also working on the (original) sqlite.

All you’ll need are two files,a .dll and a .xml file for some documentation. These files are available for download at the end of this article,you can also download these from theirwebsite,but you’ll also get some files that you don’t need.

Put these files in the folder of your project and add an assembly reference to the .dll. Just browse to System.Data.sqlite.dll and select it.

Now add using System.Data.sqlite; to the usings and you’re done. You’ve successfully added the sqlite library to you project!

Creating a database file:

You usually don’t need to create a new database file,you work with an existing one,but for those cases where you do need to create a brand new one,here’s the code:

1
sqliteConnection.CreateFile( "MyDatabase.sqlite" );

Connecting to a database:

Before you can use the database,you’ll need to connect to it. This connection is stored inside a connection object. Every time you interact with the database,you’ll need to provide the connection object. Therefore,we’ll declare the connection object as a member variable.

1
sqliteConnection m_dbConnection;

When creating a connection,we’ll need to provide a “connection string” this string can contain information about the… connection. Things like the filename of the database,the version,but can also contain things like a password,if it’s required.

You can find a few of these at:http://www.connectionstrings.com/sqlite

The first one is good enough to get our connection up and running,so we get:

1

2
3
m_dbConnection =
new sqliteConnection( "Data Source=MyDatabase.sqlite;Version=3;" );
m_dbConnection.Open();

After we create the connection object,we’ll have to open it. And with every Open() there comes a Close(),so don’t forget to call that after you’re done with your connection.

Creating a table:

Let’s write some sql now. We’ll create a table with two columns,the first one contains names and the second one contains scores. See it as a high scores table.

string sql = "create table highscores (name varchar(20),score int)" ;
You could also spam caps if you like and get something like this:

"CREATE TABLE highscores (name VARCHAR(20),score INT)";

Now we’ll need to create an sql command in order to execute it. Luckily,we’ve got the sqliteCommand class for this. We create a command by entering the sql query along with the connection object.

sqliteCommand command = sqliteCommand(sql,m_dbConnection);

Afterwards,we execute the command. But before we execute our command,i’d like to mention that not all commands are the same,some commands return results (like SELECT etc.) and others don’t (like the one we just wrote) That’s why there are two execute methods (actually,there are three) One returns the actual results (the rows of the table) the other returns an integer indicating the number of rows that have been modified. We’ll use the last one now.

command.ExecuteNonQuery();

At this time,we’re not interested in the number of rows that have been modified (it’s 0) But you could imagine that it might be interesting to know this information in UPDATE queries.

Filling our table:

Let’s fill our table with some values,so we can do some SELECT queries. Let’s create a new command. We’ll see later that this process can be made a bit easier and faster with command parameters.

"insert into highscores (name,score) values ('Me',9001)" We create and execute the command the same way as we created the table. I added two more rows (or records) to the table. Here’s the code:

3
4
5
6
7
8
9
;
command.ExecuteNonQuery();
score) values ('Myself',6000)" ;
command = command.ExecuteNonQuery();
;

As you can see,this is three times pretty much the same piece code. But it works!

Getting the high scores out of our database:

Let’s query the database for the high scores sorted on score in descending order. Our sql query becomes: “select * from highscores order by score desc”

We create a command in the regular fashion:

2

"select * from highscores order by score desc" However,we execute this command using a different method,we’ll use the ExecuteReader() method which returns an sqliteDataReader object. We’ll use this object to read the results of the query.

sqliteDataReader reader = command.ExecuteReader();

With this reader you can read the result row by row. Here’s some code that iterates trough all the rows and writes them to the console:

5
sql= "select*fromhighscoresorderbyscoredesc" sqliteCommandcommand= sqliteDataReaderreader=command.ExecuteReader();
while (reader.Read())
Console.WriteLine( "Name:" +reader[ "name" ]+ "\tscore:" "score" ]);

The Read() method of the reader moves the reader to the next row. With the [] operators,you can read the value of a certain column. The value returned is of the type object. So you’ll usually need to cast it before you can use it. Fortunately,you usually know what this type is.

Well,that’s about it for this tutorial. You should now be able to do pretty much anything you want with your database.

Click here to download the project files that implement what we’ve discussed in this article.

The above link also contains the libraries. If you’d like to have only the library,click the link below: The version of the library provided here is the .NET 4.0 x86 version. This should work fine for anyone working in Visual Studio 2010. If it doesn’t work,download the correct version from their website (mentiond at the beginning of this article)

Download only the libraries.

猜你在找的Sqlite相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Wi...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite 这么娇小可爱，不多了解点都不行啊
（点击上方蓝字，快速关注我们) 作者：空之境界 网址：http://supermao.cn/sqlitezhe-yao-...
SQLITE3 使用总结
SQLITE3 使用总结 投稿：hebedich 字体：[ 增加  减小] 类型：转载 时间：2014-08-19 Sql...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问