SQLite是一款轻型的数据库,在一些数据量不太大的程序中,它暂用的资源非常低。支持很多操作系统和许多语言,所以还是很方便的。在C#中,要用的话可以通过网站来下载或者在VS中通过NuGet来下载。这个就不多说了,当你下载并且装完数据库后,就可以直接使用它,下面就先看下最基础的数据库操作:
首先就是关于创建数据库和创建表:
private static void Create() { if (!Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + "Data")) { Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + "Data"); SQLiteConnection.CreateFile(AppDomain.CurrentDomain.BaseDirectory + "Data\\test2.db3"); } if (!File.Exists(AppDomain.CurrentDomain.BaseDirectory + "Data\\test2.db3")) { string datasource = "Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "Data\\test2.db3"; SQLiteConnection conn = new SQLiteConnection(datasource); conn.Open(); SQLiteCommand cmd = conn.CreateCommand(); string sql = "Create TABLE Test(ID GUID PRIMARY KEY,Content INTEGER NOT NULL)"; cmd.CommandText = sql; cmd.Connection = conn; cmd.ExecuteNonQuery(); conn.Close(); } }
然后么就是什么增删改查之类的东西了,这其实都差不多的:
private static void Create() { if (!Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + "Data")) { Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + "Data"); SQLiteConnection.CreateFile(AppDomain.CurrentDomain.BaseDirectory + "Data\\test2.db3"); } if (!File.Exists(AppDomain.CurrentDomain.BaseDirectory + "Data\\test2.db3")) { string datasource = "Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "Data\\test2.db3"; SQLiteConnection conn = new SQLiteConnection(datasource); conn.Open(); SQLiteCommand cmd = conn.CreateCommand(); string sql = "Create TABLE Test(ID GUID PRIMARY KEY,Content INTEGER NOT NULL)"; cmd.CommandText = sql; cmd.Connection = conn; cmd.ExecuteNonQuery(); conn.Close(); } } private static void Add() { using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "Data\\test2.db3")) { conn.Open(); SQLiteCommand cmd = conn.CreateCommand(); DbTransaction trans = conn.BeginTransaction(); //延时事务 try { for (int i = 0; i < 1000; i++) { cmd.CommandText = "insert into Test values(@ID,@Content)"; cmd.Parameters.Add(new SQLiteParameter("@ID", Guid.NewGuid())); cmd.Parameters.Add(new SQLiteParameter("@Content", i)); cmd.ExecuteNonQuery(); } trans.Commit(); //提交事务 } catch { trans.Rollback(); //事务回滚 throw; } } } private static void DeleteOne() { using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "Data\\test2.db3")) { conn.Open(); SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = string.Format("delete from Test where Content=@value"); cmd.Parameters.Add(new SQLiteParameter("@value", 100)); cmd.ExecuteNonQuery(); } } private static List<Guid> GetGuid() { using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "Data\\test2.db3")) { List<Guid> list = new List<Guid>(); conn.Open(); SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from Test where Content=@content"; cmd.Parameters.Add(new SQLiteParameter("content", 101)); SQLiteDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Guid ID = dr.GetGuid(0); list.Add(ID); } return list; } }
然后再看到上面的Add()的方法中,如果一次性的批量加入数据时,需要对事务进行一个处理,因为如果不处理的话一次的执行就是一条事务,这样时间都花在了开始事务、提交事务上,速度会很慢。应该把这个添加变为一个统一的事务,同事如果出错,还可以进行事务回滚。
跟事务有关联的是一个锁的问题,当有两个以上链接要读写同一个数据库的时候就会发生死锁的问题。虽然我还没碰到过,不过还是先知道下比较好,免的以后出错。具体的概念嘛还是看看别人写的:链接1&&链接2。
最后是我的Demo