SQLite for C#

標籤：

slqlite是個輕量級的資料庫，是目前較為流行的小型資料庫，適用於各個系統。.NET自然也是支援的

1.添加2個引用System.Data.SQLite.Linq，System.Data.SQLite

2.批量插入如下【一定要放在事務裡面才能加快速度。比迴圈插入快好多倍，20W測試資料，不到0.5S完成。】

   string datasource = @"d:\tmp\test.db";                   //此方法若檔案不存在，會報錯。可以用IO實現建立一個，再用這個。            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();            //建立表            DateTime dt1 = DateTime.Now;            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();            string sql = "CREATE TABLE test(username varchar(20))";            cmd.CommandText = sql;            cmd.Connection = conn;            cmd.ExecuteNonQuery();            using (System.Data.Common.DbTransaction dbTrans = conn.BeginTransaction())            {                sql = "INSERT INTO test VALUES(@q1)";                DbParameter Field1 = cmd.CreateParameter();                         Field1.ParameterName = "@q1";                cmd.Parameters.Add(Field1);                for (int i = 0; i < 200001; i++)                {                    //插入資料                    Field1.Value = i.ToString();                    cmd.CommandText = sql;                    cmd.ExecuteNonQuery();                }                dbTrans.Commit();            }            DateTime dt2 = DateTime.Now;            TimeSpan ts = dt2 - dt1;            Console.WriteLine(ts);                        //取出資料            //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(0)).Append("\n")            //    .Append("password:").Append(reader.GetString(1));            //}           // Console.WriteLine(sb.ToString());            Console.ReadKey();

 

SQLite for C#