1 sqlite资源
sqlite资源下载页面:https://www.sqlite.org/download.html
下载以下两个文件即可
2 sqlite安装
解压sqlite到相应位置,然后把里面的这几个文件复制到相应文件夹,并修改坏境变量的Path,增加指向这个文件夹的路径。
3 sqlite教程
sqlite菜鸟教程:https://www.runoob.com/sqlite/sqlite-tutorial.html
4 sqlite查看程序
navicat for sqlite
链接:https://pan.baidu.com/s/1piHAofhscobi_ocNeQys6w
提取码:8n5i
5 sqlite的应用代码示例(c#)
5.1 nuget添加以下dll
5.2 代码示例
工具类及使用方法
internal sealed class SqliteCmd
{
private string m_dbPath;
public SqliteCmd(string dbPath)
{
m_dbPath = dbPath;
}
//---创建数据库
internal void CreateDB()
{
SQLiteConnection cn = new SQLiteConnection("data source=" + m_dbPath);
cn.Open();
cn.Close();
}
//---删除数据库
internal void DeleteDB()
{
if (System.IO.File.Exists(m_dbPath))
{
System.IO.File.Delete(m_dbPath);
}
}
/// <summary>
/// 添加表
/// 示例CREATE TABLE t1(id varchar(4),score int)
/// </summary>
/// <param name="cmdStr"></param>
internal void CreateTable(string cmdStr)
{
SQLiteConnection cn = new SQLiteConnection("data source=" + m_dbPath);
if (cn.State != System.Data.ConnectionState.Open)
{
cn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cn;
cmd.CommandText = cmdStr;
cmd.ExecuteNonQuery();
}
cn.Close();
}
/// <summary>
/// 删除表
/// 示例:DROP TABLE IF EXISTS t1
/// </summary>
/// <param name="cmdStr"></param>
internal void DeleteTable(string tableName)
{
SQLiteConnection cn = new SQLiteConnection("data source=" + m_dbPath);
if (cn.State != System.Data.ConnectionState.Open)
{
cn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cn;
cmd.CommandText = $"DROP TABLE IF EXISTS {tableName}";
cmd.ExecuteNonQuery();
}
cn.Close();
}
internal void ReadTable(string tableName)
{
SQLiteConnection cn = new SQLiteConnection("data source=" + m_dbPath);
cn.Open();
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = $"PRAGMA table_info('{tableName}')";
//写法一
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
foreach (DataRow r in table.Rows)
{
Console.WriteLine($"{r["cid"]},{r["name"]},{r["type"]},{r["notnull"]},{r["dflt_value"]},{r["pk"]} ");
}
Console.WriteLine();
}
/// <summary>
/// 执行命令行
/// </summary>
/// <param name="cmdStr"></param>
internal void ExecuteCmdstr(string cmdStr)
{
SQLiteConnection cn = new SQLiteConnection("data source=" + m_dbPath);
if (cn.State != System.Data.ConnectionState.Open)
{
cn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cn;
cmd.CommandText = cmdStr;
cmd.ExecuteNonQuery();
}
cn.Close();
}
/// <summary>
/// 加上transaction,批量执行命令行
/// </summary>
/// <param name="SQLStringList"></param>
internal void ExecuteSqlTran(List<string> SQLStringList)
{
using (SQLiteConnection conn = new SQLiteConnection("data source=" + m_dbPath))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
SQLiteTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SQLite.SQLiteException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
}
SqliteCmd sqliteCmd = new SqliteCmd(@"D:\mytest\SqilteTest.db");
sqliteCmd.CreateDB();//创建数据库
sqliteCmd.ExecuteCmdstr("CREATE TABLE REVITPROPERTY("+
"ID INT PRIMARY KEY NOT NULL,"+
"NAME TEXT NOT NULL,"+
"CATEGORY CHAR(50)"
);//创建表格