/// <summary>
/// 执行sql集
/// </summary>
/// <param name="sqls">要执行sql集</param>
public static void ExecuteNonQuery(string conn, List <string> sqls)
{
using (System.Data.SQLite.SQLiteConnection Conn = new System.Data.SQLite.SQLiteConnection(conn))
{
Conn.Open();
using (System.Data.SQLite.SQLiteTransaction transaction = Conn.BeginTransaction())
{
using (System.Data.SQLite.SQLiteCommand command = Conn.CreateCommand())
{
try
{
foreach (string sql in sqls)
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
Conn.Close();
}
}
public static int ExecuteNonQueryWithTransaction(string sql, params System.Data.SQLite.SQLiteParameter[] arrayOfParameters)
{
if (string.IsNullOrEmpty(sql))
{
return(0);
}
CreateDatabaseFileIfNotExist();
using (System.Data.SQLite.SQLiteConnection cn = new System.Data.SQLite.SQLiteConnection(GetConnectionString()))
{
cn.Open();
int iReturn = -2;
cn.Open();
System.Data.SQLite.SQLiteTransaction trans = cn.BeginTransaction();
try
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(sql, cn, trans))
{
if ((arrayOfParameters?.Length ?? 0) > 0)
{
com.Parameters.AddRange(arrayOfParameters);
}
iReturn = com.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
trans.Rollback();
iReturn = -2;
throw;
}
return(iReturn);
}
}
public void RollBackTran()
{
if (tran != null)
{
tran.Rollback();
tran = null;
}
}
/// Return value = Number of rows affected.
/// sConnectionString = Connection string.
/// arrayOfSqlItems = Array of SQL items.
/// KeyValuePair<string, SQLiteParameter[]> o1 = new KeyValuePair<string, SQLiteParameter[]>
/// (
/// "INSERT INTO SpeedStage (SpeedStageValue, Description) VALUES (@SpeedStageValue, @Description)",
/// new SQLiteParameter[]
/// {
/// new SQLiteParameter("@SpeedStageValue", SqlDbType.Int) { Value = 1001 },
/// new SQLiteParameter("@Description", SqlDbType.VarChar) { Value = "test1" }
/// }
/// );
/// KeyValuePair<string, SQLiteParameter[]> o2 = new KeyValuePair<string, SQLiteParameter[]>
/// (
/// "INSERT INTO SpeedStage (SpeedStageValue, Description) VALUES (@SpeedStageValue, @Description)",
/// new SQLiteParameter[]
/// {
/// new SQLiteParameter("@SpeedStageValue", SqlDbType.Int) { Value = 1002 },
/// new SQLiteParameter("@Description", SqlDbType.VarChar) { Value = "test2" }
/// }
/// );
/// KeyValuePair<string, SQLiteParameter[]> o3 = new KeyValuePair<string, SQLiteParameter[]>
/// (
/// "INSERT INTO SpeedStage (SpeedStageValue, Description) VALUES (@SpeedStageValue, @Description)",
/// new SQLiteParameter[]
/// {
/// new SQLiteParameter("@SpeedStageValue", SqlDbType.Int) { Value = 1003 },
/// new SQLiteParameter("@Description", SqlDbType.VarChar) { Value = "test3" }
/// }
/// );
/// Example 1:
/// int i = ExecuteNonQuery(ConnectionString, o1, o2);
/// Example 2:
/// KeyValuePair<string, SQLiteParameter[]>[] arrayOfSqlItems = new KeyValuePair<string, SQLiteParameter[]>[] { o1, o2, o3 };
/// int i = ExecuteNonQuery(ConnectionString, arrayOfSqlItems);
public static int ExecuteNonQuery(params KeyValuePair <string, System.Data.SQLite.SQLiteParameter[]>[] arrayOfSqlItems)
{
if (arrayOfSqlItems == null)
{
return(0);
}
CreateDatabaseFileIfNotExist();
using (System.Data.SQLite.SQLiteConnection cn = new System.Data.SQLite.SQLiteConnection(GetConnectionString()))
{
cn.Open();
int iReturn = 0;
System.Data.SQLite.SQLiteTransaction trans = cn.BeginTransaction();
try
{
foreach (KeyValuePair <string, System.Data.SQLite.SQLiteParameter[]> o in arrayOfSqlItems)
{
if (string.IsNullOrEmpty(o.Key) == false)/// o.Key is SQL.
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(o.Key, cn, trans))
{
/// o.Value is array of parameters.
if ((o.Value?.Length ?? 0) > 0)
{
com.Parameters.AddRange(o.Value);
}
iReturn += com.ExecuteNonQuery();
}
}
}
trans.Commit();
}
catch
{
trans.Rollback();
iReturn = -2;
throw;
}
return(iReturn);
}
}
public void UpdateColumn(string ColumnName, Object[] data)
{
bool wasOpen = _isOpen;
if (!_isOpen)
{
Open();
}
if (data.Count() == 0)
{
return;
}
//check if column exists...
string cmdstring = "UPDATE [" + _tableName + "] SET [" + ColumnName + "]='";
using (System.Data.SQLite.SQLiteTransaction trans = _connection.BeginTransaction())
{
try
{
using (System.Data.SQLite.SQLiteCommand cmd = _connection.CreateCommand())
{
cmd.Transaction = trans;
for (int i = 0; i < data.Count(); i++)
{
cmd.CommandText = cmdstring + data[i] + "' WHERE rowid=" + _rowIds[i];
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}catch (Exception ex)
{
trans.Rollback();
}
}
if (!wasOpen)
{
Close();
}
}
public static JSONArray ExecuteQuerySync(string sql, Dictionary <string, object> parameters, bool useTransaction)
{
// Force transaction if it is an update, insert or delete.
if (GAUtilities.StringMatch(sql.ToUpperInvariant(), "^(UPDATE|INSERT|DELETE)"))
{
useTransaction = true;
}
// Get database connection from singelton sharedInstance
SqliteConnection sqlDatabasePtr = Instance.SqlDatabase;
// Create mutable array for results
JSONArray results = new JSONArray();
SqliteTransaction transaction = null;
SqliteCommand command = null;
try
{
if (useTransaction)
{
transaction = sqlDatabasePtr.BeginTransaction();
}
command = sqlDatabasePtr.CreateCommand();
if (useTransaction)
{
command.Transaction = transaction;
}
command.CommandText = sql;
command.Prepare();
// Bind parameters
if (parameters.Count != 0)
{
foreach (KeyValuePair <string, object> pair in parameters)
{
command.Parameters.AddWithValue(pair.Key, pair.Value);
}
}
using (SqliteDataReader reader = command.ExecuteReader())
{
// Loop through results
while (reader.Read())
{
// get columns count
int columnCount = reader.FieldCount;
JSONObject row = new JSONObject();
for (int i = 0; i < columnCount; i++)
{
string column = reader.GetName(i);
if (string.IsNullOrEmpty(column))
{
continue;
}
row[column] = reader.GetValue(i).ToString();
}
results.Add(row);
}
}
if (useTransaction)
{
transaction.Commit();
}
}
catch (SqliteException e)
{
// TODO(nikolaj): Should we do a db validation to see if the db is corrupt here?
GALogger.E("SQLITE3 ERROR: " + e);
results = null;
if (useTransaction)
{
if (transaction != null)
{
try
{
transaction.Rollback();
}
catch (SqliteException ex)
{
GALogger.E("SQLITE3 ROLLBACK ERROR: " + ex);
}
finally
{
transaction.Dispose();
}
}
}
}
finally
{
if (command != null)
{
command.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
}
}
// Return results
return(results);
}