1.数据库连接（常用连接方法，示例）
1). 添加引用: System.Data.SQLite.DLL 。
2). 打开或创建数据库文件: SQLiteConnection.CreateFile(fileName);
3). 连接数据库: var connection = new SQLiteConnection(connectionString);
connectionString 中包含了数据库的一些配置信息，比如数据库文件路径，数据库密码等，可用 SQLiteConnectionStringBuilder 来创建 connectionString
string dbPath = Environment.CurrentDirectory + "/test.db";/*指定数据库路径 */
using(SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath))
{
conn.Open();
DbCommand comm = conn.CreateCommand();
comm.CommandText = "select * from customer";
comm.CommandType = CommandType.Text;
using (IDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
Response.Write(reader[]);
}
}
}
在web.config或者是app.config中添加如下配置,connectionstring配置节的db就是 SQLite的数据库文件，将它放在Web应用的App_Data目录，|DataDirectory|就代表这个目录的位置，
后面的就是文件名,剩下的就是我们使用企业库访问SQL Server是一样的了。
<configuration>
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings,
Microsoft.Practices.EnterpriseLibrary.Data, Version=4.1.0.0, Culture=neutral, PublicKeyToken=null />
</configSections>
<dataConfiguration defaultDatabase=" ">
<providerMappings>
<add databaseType="EntLibContrib.Data.SQLite.SQLiteDatabase, EntLibContrib.Data.SqLite, Version=4.1.0.0, Culture=neutral, PublicKeyToken=null"
name="System.Data.SQLite" />
</providerMappings>
</dataConfiguration>
<connectionStrings>
<add name="sqlite" connectionString="Data Source=|DataDirectory|\db;Pooling=true;FailIfMissing=false"
providerName="System.Data.SQLite" />
</connectionStrings>
</configuration>
在该工程上引入System.Data.SQLite.DLL（即wrapper），无需引入SQLite3.dll，因为前者已经包装了后者。
在代码中import下： "using System.Data.SQLite;"
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite; namespace TestSQLite
{
class MyCSharpSQLite
{
private static string connStr; static void Main(string[] args)
{ connStr = SQLiteConnectionString.GetConnectionString(".\\Data\\EasyDataBase.db");
Console.WriteLine("SQLite Connect String is : {0}", connStr); //连接测试
//testConnect(connStr); //执行查询
ExecQuery("select * from customers"); Console.ReadKey();
} private static void testConnect(string connStr)
{
try
{
SQLiteConnection conn = new SQLiteConnection(connStr); conn.Open();
Console.WriteLine("SQLite Connect OK.");
}
catch (Exception ex)
{
Console.WriteLine("SQLite Connect fail: {0} ", ex.Message);
}
} private static void ExecQuery(string sqlStr)
{
Console.WriteLine(">> Start to query the database....");
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
SQLiteCommand comm = conn.CreateCommand();
comm.CommandText = sqlStr;
//comm.CommandType = comm.CommandText; using (SQLiteDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader[].ToString());
}
}
}
} } /// <summary> /// 生成SQLite连接字符串 /// </summary> public static class SQLiteConnectionString
{ public static string GetConnectionString(string path)
{
return GetConnectionString(path, null);
} public static string GetConnectionString(string path, string password)
{
if (string.IsNullOrEmpty(password))
{
return "Data Source=" + path;
}
else
{
return "Data Source=" + path + ";Password=" + password;
}
} }
}
示例1
using System;
using System.Reflection;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite; //需要安装sqlite for vs
namespace MobileTest
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} public DataSet GetData()
{
try
{
//得到数据库位置
string dbfile = string.Format("{0}\\SqlLiteTest", Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase));
//连接字符串
string connStr = string.Format("Data Source={0};Password=123456", dbfile);
SQLiteConnection conn = new SQLiteConnection(connStr);
string sql = "SELECT Id,Name,age FROM tbl_liteTest";
SQLiteDataAdapter slda = new SQLiteDataAdapter(sql, conn);
DataSet ds = new DataSet();
slda.Fill(ds);
return ds;
}
catch (Exception)
{ throw;
}
}
private void Form1_Load(object sender, EventArgs e)
{
DataSet ds = GetData();
foreach (DataRow item in ds.Tables[].Rows)
{
this.comboBox1.Items.Add(item["Name"].ToString());
}
}
}
}
示例2
SQLiteConnection cnn = new SQLiteConnection();
cnn.ConnectionString = @"Data Source=c:sqlite-3_5_0mytest.db";
cnn.Open(); SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cnn;
cmd.CommandText = "SELECT * FROM mytable1";
SQLiteDataAdapter da = new SQLiteDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
示例3
下载ADO.NET2.0 Provider for SQLite。下载binaries zip版就可以了。下载完后解压缩，可以在bin目录下找到System.Data.SQLite.DLL。在vs2008中用Add Reference功能把System.Data.SQLite.DLL加到工程里就可以了。
string datasource = "d:/sqllite/sqlite.net/bin/test.db3";
system.data.sqlite.sqliteconnection.createfile(datasource);
//连接数据库
system.data.sqlite.sqliteconnection conn = new system.data.sqlite.sqliteconnection();
system.data.sqlite.sqliteconnectionstringbuilder connstr = new system.data.sqlite.sqliteconnectionstringbuilder();
connstr.datasource = datasource;
connstr.password = "admin";//设置密码，sqlite ado.net实现了数据库密码保护
conn.connectionstring = connstr.tostring();
conn.open();
//创建表
system.data.sqlite.sqlitecommand cmd = new system.data.sqlite.sqlitecommand();
string sql = "create table test(username varchar(20),password varchar(20))";
cmd.commandtext = sql;
cmd.connection = conn;
cmd.executenonquery();
//插入数据
sql = "insert into test values('a','b')";
cmd.commandtext = sql;
cmd.executenonquery();
//取出数据
sql = "select * from test";
cmd.commandtext = sql;
system.data.sqlite.sqlitedatareader reader = cmd.executereader();
stringbuilder sb = new stringbuilder();
while (reader.read())
{
sb.append("username:").append(reader.getstring()).append("\n")
.append("password:").append(reader.getstring());
}
console.writeline(sb.tostring());
console.read();
2.C#对SQLLite的DML操作示例
/*这里新建了一个HyData目录存放数据库*/
string connStr = @"Data Source=" + System.Environment.CurrentDirectory + @"\HyData\HyData.db3;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";/*执行Sql语句
创建一个表： ExecuteSql("create table HyTest(TestID TEXT)");
插入些数据： ExecuteSql("insert into HyTest(TestID) values('1001')");
*/
private void ExecuteSql(string sqlStr)
{
using (DbConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
DbCommand comm = conn.CreateCommand();
comm.CommandText = sqlStr;
comm.CommandType = CommandType.Text;
comm.ExecuteNonQuery();
}
}/*执行查询
ExecQuery("select * from HyTest");
*/
private void ExecQuery(string sqlStr)
{
using (DbConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
DbCommand comm = conn.CreateCommand();
comm.CommandText = sqlStr;
comm.CommandType = CommandType.Text; using (IDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
MessageBox.Show(reader[].ToString());
}
}
}
} /* 执行查询返回DataSet */private DataSet ExecDataSet(string sqlStr)
{
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = sqlStr;
cmd.CommandType = CommandType.Text; SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds); return ds;
}
}
System.Data.SQLite.SQLiteConnection conn =new System.Data.SQLite.SQLiteConnection(@"Data Source=d:\maindb.db;Version=3");string datasource = "d:\\maindb.db";
//连接数据库
System.Data.SQLite.SQLiteConnection conn =new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
conn.ConnectionString = connstr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
//取出数据
string sql = "SELECT * FROM Source";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("ID:").Append(reader.GetString()).Append("\n")
.Append("NAME:").Append(reader.GetString()); }
MessageBox.Show(sb.ToString());
3.对SQLLite DML操作类封装
