C#把DataTable里的数据批量导入SQLite，

C#把DataTable里的数据批量导入SQLite，

原目的：从一个老的SQLite里读出数据，存到DataTable里，然后保存到一个新的SQLite数据库中。

需要引用System.Data.SQLite.dll

try
            {
                SQLiteConnection Conn = new SQLiteConnection();
                DataTable dt = new DataTable();
                Conn.ConnectionString = <span >"Data Source=.\\OldData.db";</span>
                Conn.Open();  //打开旧数据库

                string sql = "SELECT tbl_name, sql FROM sqlite_master WHERE type='table' ORDER BY name";
                SQLiteDataAdapter oldadapter = new SQLiteDataAdapter(sql, Conn);
                oldadapter.Fill(dt);//获得旧数据库里所有表的名字和CREATE命令

                string output = "NewData.db";
                SQLiteConnection.CreateFile(output);//创建新的数据库
                SQLiteConnection conn = new SQLiteConnection();
                SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
                SQLiteDataAdapter adapter;
                connstr.DataSource = output;
                conn.ConnectionString = connstr.ToString();
                conn.Open(); //打开新的数据库
                Console.WriteLine("Created and opened a new database.");

                DataTable single;
                String tbl_name;
                String sqlstring;
                for (int k = 0; k < dt.Rows.Count; k++)
                {
                    tbl_name = dt.Rows[k][0].ToString();
                    sqlstring = dt.Rows[k][1].ToString();//CREATE TABLE命令

                    SQLiteCommand cmd = new SQLiteCommand();;
                    cmd.CommandText = sqlstring;
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery(); //创建新表

                    sql = string.Format("SELECT * FROM {0}", tbl_name);// "select * from " + tbl_name;
                    oldadapter = new SQLiteDataAdapter(sql, Conn);
                    single = new DataTable();
                    oldadapter.AcceptChangesDuringFill = false; //重要
                    oldadapter.Fill(single); //获得老表里的数据<pre name="code" class="csharp">                

//...................对数据进行处理

cmd = conn.CreateCommand(); cmd.CommandText = sql; adapter = new SQLiteDataAdapter(cmd); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter); adapter.Update(single); //将DataTable里的数据全部导入新表 Console.WriteLine("Successfully inserted table: " + tbl_name); } conn.Close(); Conn.Close(); Console.WriteLine("Finished."); } catch (Exception ex) { Console.WriteLine("Something is wrong."); }

程序运行的时候貌似依然是一条一条的插入，时间上并不效率。一个5M的数据库（不到100个表）跑了将近5个小时。而写到文本文件的话不到一秒钟。。

插入表之前需要先创建表。如果不能事先获得CREATE命令的话，自行创建的话可能会比较麻烦。