/// <summary>
/// 方法说明：批量插入大数据
/// </summary>
/// <param name="sql">执行sql</param>
/// <param name="comoanyCode">公司编码</param>
/// <param name="userId">用户ID</param>
/// <returns>是否成功插入数据</returns>
public static bool InsertBigData(string sql, string comoanyCode, string userId)
{
SQLiteTransaction transaction = null;
var conn = new SQLiteConnection(ConnStr(comoanyCode, userId));
var cmd = new SQLiteCommand(sql, conn);
conn.Open();
try
{
lock (Obj)
{
transaction = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
transaction.Commit();
return(true);
}
}
catch (Exception ex)
{
LogHelper.WriteError("[AntSdkSqliteHelper_InsertBigData]:" + ex.Message);
transaction?.Rollback();
return(false);
}
finally
{
conn.Close();
transaction?.Dispose();
conn.Dispose();
}
}
/// <summary>
/// Execute Insert Query - Async
/// </summary>
/// <param name="query">Query</param>
/// <param name="parameters">Optional. Parameters</param>
/// <param name="isNeedTransactionBlock">Optional. IsNeedTransactionBlock default false</param>
/// <returns>Inseted Id</returns>
public static async Task <int> ExecuteInsertQueryAsync(string query, IEnumerable <KeyValuePair <string, object> > parameters = null, bool isNeedTransactionBlock = false)
{
int insertedId = -1;
await Task.Run(() =>
{
using (SQLiteConnection conn = GetConnection)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = query;
if (parameters != null && parameters.Count() > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
cmd.Prepare();
lock (readWriteLock)
{
conn.Open();
SQLiteTransaction transaction = null;
try
{
if (isNeedTransactionBlock)
{
transaction = conn.BeginTransaction();
}
int changesCount = cmd.ExecuteNonQuery();
if (changesCount > 0)
{
cmd.CommandText = LastInsertedIdQuery;
cmd.Prepare();
insertedId = int.Parse(cmd.ExecuteScalar().ToString());
}
transaction?.Commit();
}
catch
{
transaction?.Rollback();
applicationErrorLog.ErrorLog("Database", "DB Transaction", "DB Transaction Main queries Error");
throw;
}
finally
{
conn.Close();
}
}
}
}
});
return(insertedId);
}
/// <summary>
/// Metodo Comunes para Insertar, Actualizar y Eliminar
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
private bool Execute(string SQL, SQLiteParameter[] param, string nameConsulta)
{
bool error = true;
if (this.Conectar())
{
SQLiteTransaction trans = Cx.BeginTransaction();
try
{
Cmd.CommandText = SQL;
if (param != null)
{
Cmd.Parameters.AddRange(param);
}
if (Cmd.ExecuteNonQuery() == -1)
{
this.Log.Write("* " + nameConsulta + " 0 rows afected");
this.Mensaje = "No hay Columnas Afectadas";
error = false;
}
trans.Commit();
this.Log.Write("* " + nameConsulta + " Ok");
}
catch (SQLiteException ex)
{
trans.Rollback();
this.Log.Write("* " + nameConsulta + "\n" + ex.ToString());
this.Mensaje = ex.ToString();
error = false;
}
catch (NullReferenceException ex)
{
trans.Rollback();
this.Log.Write("* " + nameConsulta + "\n" + ex.ToString());
this.Mensaje = ex.ToString();
error = false;
}
finally
{
Desconectar();
}
}
return(error);
}
/// <summary>
/// 新增一星走势数据
/// </summary>
/// <param name="type"></param>
/// <param name="tendencys"></param>
public static void SaveTendency1(Tendency1Enum type, List <TendencyModel> tendencys)
{
string tableName = getTendency1Table(type);
using (SQLiteConnection conn = CreateConnection())
{
conn.Open();
SQLiteTransaction trans = conn.BeginTransaction();
string sqlCount = string.Format("SELECT count(1) FROM {0} where Ymd = @Ymd and Sno=@Sno", tableName);
try
{
string sql = null;
for (int i = 0; i < tendencys.Count; i++)
{
TendencyModel lm = tendencys[i];
int count = conn.Query <int>(sqlCount, lm).FirstOrDefault();
if (count == 0)
{
///新增
sql = string.Format("insert into {0}(Ymd ," +
@"Sno ," +
@"Lottery ," +
@"Big ," +
@"Small ," +
@"Odd ," +
@"Pair ," +
@"Dtime )" +
@" VALUES (" +
@"@Ymd ," +
@"@Sno ," +
@"@Lottery ," +
@"@Big ," +
@"@Small ," +
@"@Odd ," +
@"@Pair ," +
@"@Dtime " +
@")", tableName);
}
else
{
///修改
sql = string.Format("Update {0} set Big =@Big ," +
"Small =@Small ," +
"Odd =@Odd ," +
"Pair =@Pair " +
"where Ymd = @Ymd and Sno=@Sno ", tableName);
}
conn.Execute(sql, lm, trans);
}
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
}
}
}
/// <summary>
/// Execute Query Async
/// </summary>
/// <typeparam name="T">Type of list need to returned</typeparam>
/// <param name="query">Query</param>
/// <param name="readerFunc">Reader function</param>
/// <param name="parameters">Optional. Parameters</param>
/// <param name="isNeedTransactionBlock">Optional. IsNeedTransactionBlock default false</param>
/// <returns>List of T</returns>
public static async Task <IEnumerable <T> > ExecuteQueryAsync <T>(string query, Func <SQLiteDataReader, T> readerFunc, IEnumerable <KeyValuePair <string, object> > parameters = null, bool isNeedTransactionBlock = false)
{
IList <T> items = new List <T>();
await Task.Run(() =>
{
using (SQLiteConnection conn = GetConnection)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = query;
if (parameters != null && parameters.Count() > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
cmd.Prepare();
lock (readWriteLock)
{
conn.Open();
SQLiteTransaction transaction = null;
try
{
if (isNeedTransactionBlock)
{
transaction = conn.BeginTransaction();
}
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
items.Add(readerFunc(reader));
}
}
transaction?.Commit();
}
catch
{
transaction?.Rollback();
applicationErrorLog.ErrorLog("Database", "DB Transaction", "DB Transaction Main queries Error");
throw;
}
finally
{
conn.Close();
}
}
}
}
});
return(items);
}
private void btnEdit_Click(object sender, EventArgs e)
{
Customer customer = new Customer();
customer.LastName = this.TxtBoxLastName.Text;
customer.FirstName = this.TxtBoxFirstName.Text;
customer.Cnp = this.TxtBoxCnp.Text;
customer.BirthDate = this.DpBirthdate.Text;
Dictionary <String, String> errors = Validations.validateCustInfo(customer);
if (errors.Count != 0)
{
String errorMessage = null;
errors.TryGetValue(errors.Keys.First <string>(), out errorMessage);
MessageBox.Show(errorMessage);
return;
}
customer.Phone = this.TxtBoxPhone.Text;
customer.Email = this.TxtBoxEmail.Text;
customer.Country = this.TxtBoxCountry.Text;
customer.County = this.TxtBoxCounty.Text;
customer.City = this.TxtBoxCity.Text;
customer.Locality = this.TxtBoxLocality.Text;
customer.Street = this.TxtBoxStreet.Text;
customer.StreetNo = Int32.Parse(this.NudStreetNo.Value.ToString());
errors = Validations.validateCustDetails(customer);
if (errors.Count != 0)
{
String errorMessage = null;
errors.TryGetValue(errors.Keys.First <string>(), out errorMessage);
MessageBox.Show(errorMessage);
return;
}
if (errors.Count == 0)
{
SQLiteConnection connection = DatabaseConnection.getConnection();
connection.Open();
SQLiteTransaction transaction = connection.BeginTransaction();
ICustomersDAO customersDao = new CustomersDAO();
try
{
customersDao.updateCustomer(customer, connection);
transaction.Commit();
MessageBox.Show("The Customer was successfully added!");
}
catch (SQLiteException exception)
{
Console.WriteLine(exception.Message);
transaction.Rollback();
MessageBox.Show("The Customer can't be update!");
}
connection.Close();
}
}
public void Rollback()
{
if (_transaction != null)
{
_transaction.Rollback();
}
_transaction = null;
}
public override void RollbackTransaction()
{
if (_transaction != null)
{
_transaction.Rollback();
}
GetConnectionBase().EndUse();
}
public int EndTransaction(bool success) //-1 error, 1 - success
{
if (trans == null)
{
return(-1);
}
if (success)
{
trans.Commit();
}
else
{
trans.Rollback();
}
trans = null;
return(1);
}
public void Rollback()
{
if (_IsRunTrans)
{
_SQLiteTrans.Rollback();
_IsRunTrans = false;
}
}
public void Rollback()
{
_isRunningWithinTransaction = false;
_sqLiteTransaction.Rollback();
_sqLiteTransaction.Dispose();
_sqliteConnection.Close();
_sqliteConnection.Dispose();
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollbackTran()
{
if (_tran != null)
{
_tran.Rollback();
_tran = null;
}
}
public void RollbackTrans()
{
if (isStartTrans)
{
trans.Rollback();
}
isStartTrans = false;
}
public static void Rollback()
{
if (ts != null)
{
ts.Rollback();
ts = null;
}
}
public void Rollback()
{
if (null != _Transaction)
{
_Transaction.Rollback();
_Transaction = null;
}
}
private object ReadItem(string itemName, object defaultValue)
{
SQLiteConnection cnn = null;
SQLiteTransaction trn = null;
SQLiteCommand cmd = null;
try
{
cnn = new SQLiteConnection(_connectionString);
cnn.Open();
trn = cnn.BeginTransaction();
cmd = new SQLiteCommand();
cmd.Connection = cnn;
cmd.Transaction = trn;
cmd.CommandText = "select count(name) from scalars where key = ? and name = ?";
cmd.Parameters.AddWithValue("key", _sectionKey);
cmd.Parameters.AddWithValue("name", itemName);
long count = (long)cmd.ExecuteScalar();
if (count < 1)
{
cmd.CommandText = "insert into scalars (key, name, value) values (?, ?, ?)";
cmd.Parameters.AddWithValue("value", defaultValue);
cmd.ExecuteNonQuery();
}
cmd.CommandText = "select value from scalars where key = ? and name = ?";
object val = cmd.ExecuteScalar();
trn.Commit();
return(val);
}
catch
{
trn.Rollback();
throw;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (trn != null)
{
trn.Dispose();
}
if (cnn != null)
{
cnn.Dispose();
}
}
}
private static void createDatabase()
{
string executableLocation = Path.GetDirectoryName(
Assembly.GetExecutingAssembly().Location);
string dbLocation = Path.Combine(executableLocation, dbFileName);
Dictionary <string, string> neededTables = new Dictionary <string, string> {
{ "character", db_createCharacterTable },
{ "round", db_createRoundTable },
{ "match", db_createMatchTable },
{ "player", db_createPlayerTable },
{ "alias", db_createAliasTable }
};
if (!File.Exists(dbLocation))
{
SQLiteConnection.CreateFile(dbFileName);
SQLiteTransaction transaction = BeginTransaction();
try {
foreach (var nt in neededTables)
{
NonQuery(nt.Value);
}
transaction.Commit();
} catch (Exception) {
transaction.Rollback();
}
Program.Log("DB file created");
}
else
{
Program.Log("DB file already exists");
List <string> tables = GetTableList();
SQLiteTransaction transaction = BeginTransaction();
try {
foreach (var nt in neededTables)
{
bool f = false;
foreach (string t in tables)
{
if (nt.Key == t)
{
f = true;
break;
}
}
// create new table if not found
if (!f)
{
NonQuery(nt.Value);
}
}
transaction.Commit();
} catch (Exception) {
transaction.Rollback();
}
}
}
static void runUpdateOperation()
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
connection.Open();
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
try
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
string sql = string.Format("UPDATE {0} SET hexchars = '0F0F0F' WHERE name = 'Color #01'", colorsTable_);
command.CommandText = sql;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
transaction.Rollback();
//throw;
}
}
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
try
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
string sql = string.Format("UPDATE {0} SET hexchars = '0F0F0F' WHERE name = 'Color #20'", colorsTable_); // a new record is not inserted.
command.CommandText = sql;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
transaction.Rollback();
//throw;
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
}
////////////////////////////////////////////////////////
// Delete Profiles //
//----------------------------------------------------//
public override int DeleteProfiles(string[] usernames)
{
//SecUtility.CheckArrayParameter(ref usernames, true,true,true,255,"usernames");
SQLiteConnection holder = new SQLiteConnection(_connectionString);
SQLiteTransaction trans = null;
try
{
int numDeleted = 0;
bool fBeginTransCalled = false;
try
{
holder.Open();
trans = holder.BeginTransaction();
fBeginTransCalled = true;
int appId = GetApplicationId(holder);
foreach (string username in usernames)
{
if (DeleteProfile(holder, username, appId))
{
numDeleted++;
}
}
trans.Commit();
fBeginTransCalled = false;
}
catch (Exception e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "Delete Profiles");
throw new ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (fBeginTransCalled)
{
try
{
trans.Rollback();
}
catch { }
}
holder.Close();
}
return(numDeleted);
}
catch
{
throw;
}
}
public DataSet DeleteForm1(string FormID, string spName, string fldvalue, string visitid)
{
SQLiteCommand cmd = null;
CConnection cn = null;
SQLiteDataAdapter da = null;
DataSet ds = null;
SQLiteTransaction trans = null;
try
{
cn = new CConnection();
cn.MConnOpen();
trans = cn.cn.BeginTransaction();
cmd = new SQLiteCommand();
cmd.Connection = cn.cn;
cmd.CommandText = spName;
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
if (FormID == "")
{
cmd.Parameters.AddWithValue("FormID", "NULL");
}
else
{
cmd.Parameters.AddWithValue("FormID", FormID);
}
cmd.Parameters.AddWithValue("fldvalue", fldvalue);
cmd.Parameters.AddWithValue("visitid", visitid);
da = new SQLiteDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
cn.MConnClose();
cmd = null;
cn = null;
}
return(ds);
}
/// <summary>
/// ロールバック
/// </summary>
/// <remarks></remarks>
public void Rollback()
{
if (_trans != null)
{
_trans.Rollback();
_logger.Info("Rollback:" + _trans.ToString());
_trans = null;
}
}
public override bool TransactionRollback()
{
if (_transaction == null)
{
return(false);
}
_transaction.Rollback();
return(true);
}
public static int Delete(string deleteQuery, params object[] parameters)
{
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
try
{
SQLiteCommand cmd = new SQLiteCommand(deleteQuery, connection);
cmd.Transaction = transaction;
int i = 0;
object name = null;
object value = null;
foreach (object o in parameters)
{
if (i % 2 == 0)
{
name = o;
}
else
{
value = o;
cmd.Parameters.AddWithValue(name.ToString(), value);
}
i++;
}
cmd.ExecuteNonQuery();
int rowsAffected = connection.Changes;
transaction.Commit();
return(rowsAffected);
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
}
}
public int ExecuteNonQuery()
{
int returnResult;
try
{
using (connection = new SQLiteConnection(ConnectionString))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
if (command == null)
{
connection.Close();
return(0);
}
if (QueryParameters.Count > 0 && BindParametersToQuery == true)
{
command.Parameters.AddRange(QueryParameters.ToArray());
}
command.Connection = connection;
if (TransActional)
{
transaction = connection.BeginTransaction(transactionIsolation);
command.Transaction = transaction;
}
returnResult = command.ExecuteNonQuery();
if (TransActional)
{
transaction.Commit();
}
}
}
catch (SQLiteException ex)
{
//Will be a good idea to add fault logging here;
returnResult = 0;
try
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
transaction.Rollback();
}
catch (Exception)
{
}
}
finally
{
command.Dispose();
CleanProvider();
}
return(returnResult);
}
public void Rollback()
{
if (tran != null)
{
tran.Rollback();
tran.Dispose();
tran = null;
}
}
public void UninstallGame(Game game)
{
if (!_cachedGames.Any(g => g.Id == game.Id))
{
return;
}
SQLiteTransaction trans = null;
try
{
trans = DatabaseConnection.BeginTransaction();
#region remove the custom properties from the database
using (SQLiteCommand com = DatabaseConnection.CreateCommand())
{
//Build Query
com.CommandText = @"DELETE FROM [custom_properties] WHERE [game_id]=@id;";
com.Parameters.AddWithValue("@id", game.Id.ToString());
com.ExecuteNonQuery();
}
#endregion
#region remove the game from the database
using (SQLiteCommand com = DatabaseConnection.CreateCommand())
{
//Build Query
com.CommandText = @"DELETE FROM [games] WHERE [id]=@id;";
com.Parameters.AddWithValue("@id", game.Id.ToString());
com.ExecuteNonQuery();
}
#endregion
//remove obsolete columns from the cards table
DatabaseHandler.RebuildCardTable(DatabaseConnection);
trans.Commit();
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
if (Debugger.IsAttached)
{
Debugger.Break();
}
return;
}
var existingGame = _cachedGames.FirstOrDefault(g => g.Id == game.Id);
if (existingGame != null)
{
_cachedGames.Remove(existingGame);
}
}
public void Rollback()
{
lock (transactionLock)
{
Transaction.Rollback();
Transaction.Dispose();
Transaction = null;
}
}
public void Rollback()
{
if (_dbConnection == null || _transaction == null)
{
return;
}
_transaction.Rollback();
_transaction = null;
}
private void RollBackTransaction()
{
if (transaction != null)
{
transaction.Rollback();
}
isTransactionStart = false;
CloseConnection();
}
public void rollback()
{
if (currTrans == null)
{
return;
}
currTrans.Rollback();
currTrans = null;
}
