准备工作:
1,下载SqlLite管理工具sqliteadmin(管理工具很多,推荐这个)
2,SqlLite for .Net(C#开发用)
3,引用命名空间:using System.Data.SQLite;
以上包需要可以提供
建库建表代码:
//创建一个数据库文件
string datasource = "E:/test.db";
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();
insert代码:
//打开连接
public bool openConn()
{
bool init = false;
try
{
conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + datasource);
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
conn.ConnectionString = connstr.ToString();
conn.Open();
init = true;
}
catch (Exception ex)
{
return false;
}
return init;
}
//执行Sql
public void ExcuteSql(string sql)
{
if (openConn())
{
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}
}
查询:
string datasource = "D:/people.s3db"; System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + datasource); System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); connstr.DataSource = datasource; //connstr.Password = "admin";有密码可用 conn.ConnectionString = connstr.ToString(); conn.Open(); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); string sql = "SELECT * FROM person"; cmd.CommandText = sql; cmd.Connection = conn; System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); StringBuilder sb = new StringBuilder(); string name = ""; List<string> l = new List<string>(); while (reader.Read()) { name = reader.GetString(1); l.Add(name); }