c#操作sqlite

记得在项目中加入引用System.Data.SQLite.dll

class Program

    {
        private  static SQLiteConnection conn;
        private static int isExist;
        private static SQLiteDataReader reader;
        static void Main(string[] args)
        {
            try {
            //建数据库，工程的debug目录中
            conn = new SQLiteConnection("Data Source=test.db");
            conn.Open();
            SQLiteCommand com = conn.CreateCommand();

            //判断数据表test是否存在
            com.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='test'";
            reader = com.ExecuteReader();
            while (reader.Read())
            {
                isExist = reader.GetInt32(0);//存在返回1， 不存在返回0
            }
            reader.Close();
            if (isExist == 0)//不存在则新建数据表test
            {
               // com.CommandText = "drop table test";//删除表test
                com.CommandText = "create table test(id int,name varchar(10))";
                com.ExecuteNonQuery();//执行上边的sql
            }

            //当一次性插入多条数据时，使用事物，提高效率
            SQLiteTransaction t = conn.BeginTransaction();
            Stopwatch watch = new Stopwatch();
            watch.Start();
            for (int i = 0; i < 1000; i++)
            {
                // com.CommandText = "insert into test values(" + i + ",'A" + i + "')";
                // com.CommandText = string.Format("insert into test values({0},'{1}')",i,"A"+i);
                com.CommandText = "insert into test values(@id,@name)";
                com.Parameters.AddWithValue("@id", i);
                com.Parameters.AddWithValue("@name", "A" + i);
                com.ExecuteNonQuery();
            }
            t.Commit();//提交事物
            watch.Stop();
            Console.Write("操作成功 " + watch.Elapsed);

            //删除数据
            //com.CommandText = "delete from test";
            //com.ExecuteNonQuery();

            //查询数据
            com.CommandText = "select * from test";
            reader = com.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader.GetInt32(0) + "   " + reader.GetString(1));
            }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            finally
            {
                reader.Close();
                conn.Close();
            }
            Console.Write("操作成功 ");
            Console.ReadKey();
        }
    }