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();

 

转载于:https://www.cnblogs.com/liuruitao/p/4936680.html