為了方便存取SQLite 故寫了一段code(AccSqliteData) 專門處理 SQLite的存取
本一切安好但在一次人員資料同步中出現了執行效能的ISSUE
原做法是在取得資料後(Dt),跑回圈將每一段SQL insert Statement 傳入AccSqliteData 執行
傳入AccSqliteData 執行,在AccSqliteData中執行就是一個transaction 所以造成此龜速的執行結果
後來原做法中把AccSqliteData 處理的內容搬到外部並迴圈的內容包在一個transaction 中
----------------------------------------------------------------------------------------------------------------------------------------------------
#region AccSqliteData
public void AccSqliteData(string strConnectionString, string sqlStr, System.Data.SQLite.SQLiteParameter[] sqlParas, System.Data.CommandType cmdType)
{
if (sqlStr == null)
{
return;
}
System.Data.SQLite.SQLiteConnection Conn = new System.Data.SQLite.SQLiteConnection(strConnectionString);
System.Data.SQLite.SQLiteCommand Comm;
Comm = new SQLiteCommand(sqlStr, Conn);
Comm.CommandType = cmdType;
if (sqlParas != null)
{
int i;
for (i = 0; i <= sqlParas.Length - 1; i++)
{
if (sqlParas[i] != null && sqlParas[i].Value != null)
{
Comm.Parameters.Add(sqlParas[i]);
}
}
}
try
{
Conn.Open();
Comm.ExecuteNonQuery();
}
catch (System.Exception ex)
{
//出錯紀錄,紀錄Log
System.String err;
err = "";
err += "AccSqliteData.Error:" + ex.Message.Trim();
err += System.Environment.NewLine;
err += "CommandType:" + cmdType.ToString();
err += System.Environment.NewLine;
err += "sqlStr:[" + sqlStr.Trim() + "]";
if (sqlParas != null)
{
foreach (System.Data.SQLite.SQLiteParameter sqlPara in sqlParas)
{
if (sqlPara != null)
{
err += System.Environment.NewLine;
err += "[Parameter:" + sqlPara.ParameterName + "]";
err += ";";
err += "[Direction:" + sqlPara.Direction.ToString() + "]";
if (sqlPara.Value != null)
{
err += ";";
err += "[Value:" + sqlPara.Value.ToString() + "]";
}
}
}
}
cls_log objLog = new cls_log();
objLog.WriteLog("AccSqliteData", err, "AccSqliteData_ERR");
//拋出異常
throw ex;
}
finally
{
Comm.Parameters.Clear();
Conn.Close();
Conn.Dispose();
Comm.Dispose();
}
} //end public void AccSqliteData
#endregion AccSqliteData
