SQLite初试...

            string dataPath = "../../UserData.dbx"; //System.IO.Directory.GetCurrentDirectory() + "\\UserData.dbx";
            SQLiteConnection.CreateFile(dataPath);
            SQLiteConnection conn = new SQLiteConnection("Data Source=" + dataPath);
            conn.Open();


            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = "create table Users (UserID int primary key,UserName varchar(100) not null,UserPassword varchar(100) not null)";
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();

            for (int i = 0; i < 10; i++)
            {
                cmd.CommandText = "insert into Users (UserID,UserName,UserPassword) values (" + i + ",'TestUser_" + i + "测试','" + DateTime.Now.ToString().Replace(" ", "-").Replace(":", "-") + "')";
                cmd.ExecuteNonQuery();
            }

 批量插入太慢了····

找了下别的例子

 

 internal string FastInsertMany()
    {
      StringBuilder builder = new StringBuilder();
      using (DbTransaction dbTrans = _cnn.BeginTransaction())
      {
        int dtStart;
        int dtEnd;
 
        using (DbCommand cmd = _cnn.CreateCommand())
        {
          cmd.Transaction = dbTrans;
          cmd.CommandText = "INSERT INTO TestCase(Field1) VALUES(@p1)";
          DbParameter Field1 = cmd.CreateParameter();
 
          Field1.ParameterName = "@p1";
          cmd.Parameters.Add(Field1);
 
          //Console.WriteLine(String.Format("          Fast insert using parameters and prepared statement\r\n          -> (100,000 rows) Begins ... "));
          dtStart = Environment.TickCount;
          for (int n = 0; n < 100000; n++)
          {
            Field1.Value = n + 200000;
            cmd.ExecuteNonQuery();
          }
 
          dtEnd = Environment.TickCount;
          dtEnd -= dtStart;
          builder.AppendFormat("Ends in {0} ms ... ", (dtEnd));
        }
 
        dtStart = Environment.TickCount;
        dbTrans.Commit();
        dtEnd = Environment.TickCount;
        dtEnd -= dtStart;
        builder.AppendFormat("Commits in {0} ms", (dtEnd));
      }
      return builder.ToString();
    }

 

_cnn.BeginTransaction()--报错了···

 

_cnn.BeginTransaction()之前...
_cnn.Open();就可以了····

 ...上面那个比较麻烦...

再次寻到一个...帖子地址...http://bbs.csdn.net/topics/350182114

对于大量的插入操作,可以利用一个空的DataTable加入要插入的行,达到一定数量提交后清空该表就行了,
实现起来并不算复杂:

DateTime begin = DateTime.Now;
string connectionString = ......;
using(SqlConnection conn = new SqlConnection(connectionString))...{
    conn.Open();
    SqlDataAdapter sd = new SqlDataAdapter();
    sd.SelectCommand = new SqlCommand("select devid,data_time,data_value from CurrentTest", conn);
    sd.InsertCommand = new SqlCommand("insert into CurrentTest (devid,data_time,data_value) "
                    + " values (@devid,@data_time,@data_value);", conn);
    sd.InsertCommand.Parameters.Add("@devid", SqlDbType.Char, 18, "devid");
    sd.InsertCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time");
    sd.InsertCommand.Parameters.Add("@data_value", SqlDbType.Int, 8, "data_value");
    sd.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
    sd.UpdateBatchSize = 0;

    DataSet dataset = new DataSet();
    sd.Fill(dataset);
    Random r = new Random(1000);
    for (int i = 0; i < 100000; i++) ...{
        object[] row = ...{"DEVID"+i,DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),r.Next(1,1000) };
        dataset.Tables[0].Rows.Add(row);
        if (i % 300 == 0) ...{
            sd.Update(dataset.Tables[0]);
            dataset.Tables[0].Clear();
        }
    }
    sd.Update(dataset.Tables[0]);
    dataset.Tables[0].Clear();
    sd.Dispose();
    dataset.Dispose();
    conn.Close();
   
}
by id “q107770540”