access中不能批量导入数据,所以形如以下的sql拼装是不能在access完成的。
INSERT INTO stockData ( stockCode, transDate, [open], [close], high, low, turn, volume ) VALUES (),(),();
要完成大批量的插入数据还需要借助事务来处理:
//批量数据的插入
public staticvoid insertToStockDataByBatch(String[] sqlArray)
{
try
{
OleDbConnection aConnection = new OleDbConnection(DB.getConnectStr());
aConnection.Open();
OleDbTransaction transaction = aConnection.BeginTransaction();
OleDbCommand aCommand = new OleDbCommand();
aCommand.Connection = aConnection;
aCommand.Transaction = transaction;
for (int i = 0; i < sqlArray.Length; i++)
{
aCommand.CommandText = sqlArray[i];
aCommand.ExecuteNonQuery();
LogHelper.log(Convert.ToString(i));
}
transaction.Commit();
aConnection.Close();
}
catch(Exception e)
{
LogHelper.log(e.Message);
}
}
//产生sql语句,为批量执行做准备
public staticString generateSQLSentence(String stockCode,String transDate, String open,
String close, String high,String low, String turn,String volume)
{
String sql ="INSERT INTO stockData ( stockCode, transDate, [open], [close], high, low, turn, volume ) " +
"VALUES (\"" + stockCode + "\"" +
",\"" + transDate + "\"" +
",\"" + open + "\"" +
",\"" + close + "\"" +
",\"" + high + "\"" +
",\"" + low + "\"" +
",\"" + turn + "\"" +
",\"" + volume + "\")";
return sql;
}