/// <summary>
/// A SQLiteParameterCollection extension method that adds a range with value to 'values'.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="values">The values.</param>
public static void AddRangeWithValue(this SQLiteParameterCollection @this, Dictionary <string, object> values)
{
foreach (var keyValuePair in values)
{
@this.AddWithValue(keyValuePair.Key, keyValuePair.Value);
}
}
/// <summary>
/// Used for debugging only.
/// </summary>
/// <param name="parms"></param>
/// <returns></returns>
private string GetParametersAsString(SQLiteParameterCollection parms)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < parms.Count; i++)
{
if (i > 0)
{
sb.Append(", ");
}
if (parms[i].Value.GetType() == typeof(System.Byte[]))
{
System.Byte[] arr = parms[i].Value as System.Byte[];
if (arr == null)
{
sb.Append("null-geom;;;");
}
else
{
sb.Append("geom.len=" + arr.Length.ToString() + ";;;");
}
}
else
{
sb.Append(parms[i].Value);
}
}
return(sb.ToString());
}
public string BuildSelect(DatabaseTable table, SQLiteParameterCollection parameters)
{
return(string.Format("SELECT * FROM {0} WHERE {1}",
table.GetName(),
Build(parameters)
));
}
public SQLiteDataReader ExecuteSPReader(string procedureName)
{
SQLiteDataReader reader;
SQLiteCommand cmd = new SQLiteCommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
CommandBehavior behavior = CommandBehavior.Default;
if (this.AutoCloseConnection)
{
behavior = behavior | CommandBehavior.CloseConnection;
}
if (_isSingleRow)
{
behavior = behavior | CommandBehavior.SingleRow;
}
reader = cmd.ExecuteReader(behavior);
_parameterCollection = cmd.Parameters;
cmd.Dispose();
return(reader);
}
public static void AddIfSet(
this SQLiteParameterCollection parameters,
string paramName,
string[] tokens,
int index,
Func <string, string> modifier = null)
{
if (index >= tokens.Length)
{
return;
}
if (!string.IsNullOrWhiteSpace(tokens[index]) && tokens[index] != "\\N")
{
var value = tokens[index];
if (modifier != null)
{
value = modifier(value);
}
if (string.IsNullOrWhiteSpace(value))
{
return;
}
parameters.AddWithValue(paramName, value);
}
}
public void ExecuteSPDataSet(ref DataSet dataSet, string procedureName, string tableName)
{
SQLiteCommand cmd = new SQLiteCommand();
this.Connect();
SQLiteDataAdapter da = new SQLiteDataAdapter();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
da.SelectCommand = cmd;
da.Fill(dataSet, tableName);
_parameterCollection = cmd.Parameters;
da.Dispose();
cmd.Dispose();
if (this.AutoCloseConnection)
{
this.Disconnect();
}
}
public static void SaveAccount(string appId, string pass)
{
string str = Common.AES_Encrypt(pass, string.Concat(AppConst.m_randomKey, appId));
string str1 = "select * from account where appleId=@appleId";
SQLiteCommand sQLiteCommand = new SQLiteCommand(str1, AppConst.m_dbConnection);
sQLiteCommand.get_Parameters().AddWithValue("@appleId", appId);
AppConst.logger.Debug(string.Concat("SaveAccount: 1. ", str1, ", ", appId));
SQLiteDataReader sQLiteDataReader = sQLiteCommand.ExecuteReader();
if (!sQLiteDataReader.Read())
{
str1 = "INSERT INTO account(appleId, password) VALUES(@appleId, @password)";
sQLiteCommand = new SQLiteCommand(str1, AppConst.m_dbConnection);
SQLiteParameterCollection parameters = sQLiteCommand.get_Parameters();
parameters.AddWithValue("@appleId", appId);
parameters.AddWithValue("@password", str);
parameters = null;
AppConst.logger.Debug(string.Concat(new string[] { "SaveAccount: 3. ", str1, ", ", appId, ",", str }));
sQLiteCommand.ExecuteNonQuery();
}
else if (!Operators.ConditionalCompareObjectEqual(sQLiteDataReader.get_Item("password"), str, false))
{
str1 = "UPDATE account SET appleId=@appleId, password=@password";
sQLiteCommand = new SQLiteCommand(str1, AppConst.m_dbConnection);
SQLiteParameterCollection sQLiteParameterCollection = sQLiteCommand.get_Parameters();
sQLiteParameterCollection.AddWithValue("@appleId", appId);
sQLiteParameterCollection.AddWithValue("@password", str);
sQLiteParameterCollection = null;
AppConst.logger.Debug(string.Concat(new string[] { "SaveAccount: 2. ", str1, ", ", appId, ",", str }));
sQLiteCommand.ExecuteNonQuery();
}
}
/// <summary>
/// Executes non-query sql Statment with Transaction
/// </summary>
/// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, and Command, all of which must be created prior to making this method call. </param>
/// <param name="commandText">Command text.</param>
/// <param name="paramList">Param list.</param>
/// <returns>Integer</returns>
/// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, object[] paramList)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction != null && transaction.Connection == null)
{
throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
}
IDbCommand cmd = transaction.Connection.CreateCommand();
cmd.CommandText = commandText;
SQLiteParameterCollection coll = DeriveParameters(cmd.CommandText, paramList);
foreach (SQLiteParameter parm in coll)
{
cmd.Parameters.Add(parm);
}
if (transaction.Connection.State == ConnectionState.Closed)
{
transaction.Connection.Open();
}
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
return(result);
}
/// <summary>
/// Executes the dataset with Transaction and object array of parameter values.
/// </summary>
/// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, and Command, all of which must be created prior to making this method call. </param>
/// <param name="commandText">Command text.</param>
/// <param name="commandParameters">object[] array of parameter values.</param>
/// <returns>DataSet</returns>
/// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, object[] commandParameters)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction != null && transaction.Connection == null)
{
throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
}
IDbCommand cmd = transaction.Connection.CreateCommand();
cmd.CommandText = commandText;
SQLiteParameterCollection coll = DeriveParameters(cmd.CommandText, commandParameters);
foreach (SQLiteParameter parm in coll)
{
cmd.Parameters.Add(parm);
}
if (transaction.Connection.State == ConnectionState.Closed)
{
transaction.Connection.Open();
}
DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
return(ds);
}
///<overloads></overloads>
/// <summary>
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of /// parameter values
/// </summary>
/// <param name="cn">Connection.</param>
/// <param name="commandText">Command text.</param>
/// <param name="paramList">Param list.</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
{
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null)
{
SQLiteParameterCollection parms = DeriveParameters(commandText, paramList);
foreach (SQLiteParameter p in parms)
{
cmd.Parameters.Add(p);
}
}
DataSet ds = new DataSet();
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
cn.Close();
return(ds);
}
public override string Build(SQLiteParameterCollection parameters)
{
string parameter_name = "@CONSTANT_" + id;
parameters.AddWithValue(parameter_name, constant);
return(parameter_name);
}
public void ExecuteSP(string procedureName)
{
SQLiteCommand cmd = new SQLiteCommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
cmd.ExecuteNonQuery();
_parameterCollection = cmd.Parameters;
cmd.Dispose();
if (this.AutoCloseConnection)
{
this.Disconnect();
}
}
public void AddToParameters(SQLiteParameterCollection parameters)
{
parameters.Clear();
parameters.AddWithValue("id", this.Id);
parameters.AddWithValue("text", this.Text);
parameters.AddWithValue("lang", this.Language);
parameters.AddWithValue("trans_of", this.TranslationOf);
}
public string BuildCount(DatabaseTable table, SQLiteParameterCollection parameters)
{
return(string.Format("SELECT COUNT({0}) FROM {1} WHERE {2}",
table.GetPrimaryField().GetName(),
table.GetName(),
Build(parameters)
));
}
private void SetParam <T>(string name, T value, SQLiteParameterCollection param)
{
if (!param.Contains(name))
{
param.AddWithValue(name, value);
return;
}
param[name].Value = value;
}
private string toString(SQLiteParameterCollection parameters)
{
string res = "";
foreach (SQLiteParameter p in parameters)
{
res += $"[{p.ParameterName}={p.Value}]";
}
return(res);
}
public static SQLiteParameterCollection AddWithValue(this SQLiteParameterCollection parameters, string parameterName, DbType dbType, int size, object value)
{
var parameter = new SQLiteParameter(parameterName, dbType, size)
{
Value = value
};
parameters.Add(parameter);
return(parameters);
}
protected override string BuildSubExpression(string label, SQLiteParameterCollection parameters)
{
switch (label)
{
case "LEFT": return(left.Build(parameters));
case "RIGHT": return(right.Build(parameters));
}
throw new UnaccountedBranchException("label", label);
}
public static void AddRange(this SQLiteParameterCollection @this, IEnumerable <SQLiteParameter> parameters)
{
if (parameters == null)
{
return;
}
foreach (var p in parameters)
{
@this.Add(p);
}
}
public static void AddRange(this SQLiteParameterCollection @this, IEnumerable <KeyValuePair <string, object> > parameters)
{
if (parameters == null)
{
return;
}
foreach (var kvp in parameters)
{
@this.Add(kvp);
}
}
void ProcessAirBasePlaneDeploymentConsumption(StringBuilder rpBuilder, SQLiteParameterCollection rpParameters, MapInfo rpMap, AirForceGroup[] rpGroups)
{
var rStatement = string.Join(", ", rpGroups.Select(r => r.ID));
rpBuilder.AppendFormat("INSERT OR IGNORE INTO sortie_consumption_detail(id, type, bauxite) VALUES(@id, 3, (SELECT sum(bauxite) FROM airbase_plane_deployment_consumption WHERE area = @area AND [group] IN ({0})));{1}" +
"DELETE FROM sortie_consumption_detail WHERE id = @id AND type = 3 AND bauxite IS NULL;{1}" +
"DELETE FROM airbase_plane_deployment_consumption WHERE area = @area AND [group] IN ({0});", rStatement, Environment.NewLine);
rpBuilder.AppendLine();
rpParameters.AddWithValue("@area", rpMap.MasterInfo.AreaID);
rpParameters.AddWithValue("@group", rpMap.AvailableAirBaseGroupCount);
}
public void Reset()
{
if (_parameters != null)
{
_parameters.Clear();
}
if (_parameterCollection != null)
{
_parameterCollection = null;
}
}
public static void AddIfSet(
this SQLiteParameterCollection parameters,
string paramName,
string value,
Func <string, string> modifier = null)
{
if (modifier != null)
{
value = modifier(value);
}
parameters.AddWithValue(paramName, value);
}
/// <summary>
/// Returns the specified error from the database, or null
/// if it does not exist.
/// </summary>
public override ErrorLogEntry GetError(string id)
{
if (id == null)
{
throw new ArgumentNullException("id");
}
if (id.Length == 0)
{
throw new ArgumentException(null, "id");
}
long key;
try
{
key = long.Parse(id, CultureInfo.InvariantCulture);
}
catch (FormatException e)
{
throw new ArgumentException(e.Message, "id", e);
}
const string sql = @"
SELECT
AllXml
FROM
Error
WHERE
ErrorId = @ErrorId";
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
SQLiteParameterCollection parameters = command.Parameters;
parameters.Add("@ErrorId", DbType.Int64).Value = key;
connection.Open();
string errorXml = (string)command.ExecuteScalar();
if (errorXml == null)
{
return(null);
}
Error error = ErrorXml.DecodeString(errorXml);
return(new ErrorLogEntry(this, id, error));
}
}
public static void MapSQLiteParameters(this SQLiteParameterCollection sqliteParameter, string sql, params object[] parameters)
{
var paramsInSql = Regex.Matches(sql, "@[a-zA-Z0-9_]*").Cast <Match>().Select(match => match.Value).ToList();
if (paramsInSql.Count != parameters.Length)
{
throw new ArgumentException($"No of argument in sql({paramsInSql.Count}) doesn't match with arguments in parameter list ({parameters?.Length})." +
$" Require parameters : {string.Join(" , ", paramsInSql)}");
}
for (int i = 0; i < paramsInSql.Count; i++)
{
sqliteParameter.AddWithValue(paramsInSql[i], parameters[i]);
}
}
void ProcessLBASConsumption(StringBuilder rpBuilder, SQLiteParameterCollection rpParameters, SortieInfo rpSortie)
{
var rMap = rpSortie.Map;
var rAllGroups = Port.AirBase.Table[rMap.MasterInfo.AreaID].Values;
var rGroups = rAllGroups.Take(rMap.AvailableAirBaseGroupCount).Where(r => r.Option == AirForceGroupOption.Sortie)
.Concat(rAllGroups.Where(r => r.Option == AirForceGroupOption.AirDefense))
.ToArray();
if (rGroups.Length == 0)
{
return;
}
ProcessAirBaseSquadronParticipants(rpBuilder, rGroups);
ProcessAirBasePlaneDeploymentConsumption(rpBuilder, rpParameters, rMap, rGroups);
ProcessAirForceGroupSortieConsumption(rpBuilder, rpParameters, rGroups);
}
/// <summary>
/// 将Request里的参数转成SqlParameter[]
/// </summary>
/// <returns></returns>
public static void RequestParasToSqlParameters(SQLiteParameterCollection oldParas)
{
var oldParaList = oldParas.Cast <SQLiteParameter>().ToList();
var paraDictionarAll = SqlSugarTool.GetParameterDictionary();
if (paraDictionarAll != null && paraDictionarAll.Count() > 0)
{
foreach (KeyValuePair <string, string> it in paraDictionarAll)
{
var par = new SQLiteParameter("@" + it.Key, it.Value);
if (!oldParaList.Any(oldPara => oldPara.ParameterName == ("@" + it.Key)))
{
oldParas.Add(par);
}
}
}
}
static string BuildInsertSQL(SQLiteCommandBuilder thisbuilder, string tablename)
{
StringBuilder builder = new StringBuilder();
int index = 0;
string str2 = " (";
builder.Append("INSERT INTO ");
builder.Append(thisbuilder.QuotePrefix + tablename + thisbuilder.QuoteSuffix);
SQLiteParameterCollection rowArray = thisbuilder.DataAdapter.InsertCommand.Parameters;
string[] strArray = new string[rowArray.Count];
for (int i = 0; i < rowArray.Count; i++)
{
SQLiteParameter row = rowArray[i];
if (((row != null) && (row.SourceColumn.Length != 0))) //父类补充条件：&& thisbuilder.IncludeInInsertValues(row))
{
string columnName = row.SourceColumn;
builder.Append(str2);
str2 = ", ";
builder.Append(thisbuilder.QuotePrefix + row.SourceColumn + thisbuilder.QuoteSuffix);
index++;
}
}
if (index == 0)
{
builder.Append(" DEFAULT VALUES");
}
else
{
builder.Append(")");
builder.Append(" VALUES ");
builder.Append("(");
builder.Append(rowArray[0].ParameterName);
for (int j = 1; j < index; j++)
{
builder.Append(", ");
builder.Append(rowArray[j].ParameterName);
}
builder.Append(")");
}
//command.CommandText = builder.ToString();
//RemoveExtraParameters(command, index);
//this.InsertCommand = command;
//return command;
return(builder.ToString());
}
private static SQLiteParameterCollection AttachParametersEx(SQLiteCommand cmd, string commandText, params object[] paramList)
{
if (paramList == null || paramList.Length == 0)
{
return(null);
}
SQLiteParameterCollection coll = cmd.Parameters;
foreach (object o in paramList)
{
var t = o.GetType();
if (t.ToString().Equals("System.Data.SQLite.SQLiteParameter"))
{
coll.Add((SQLiteParameter)o);
}
}
return(coll);
}
static string BuildSubInsertSQL(SQLiteCommandBuilder thisbuilder, string tablename, DataRow datarow)
{
StringBuilder builder = new StringBuilder();
StringBuilder builderForValues = new StringBuilder();
int index = 0;
string str2 = " (";
builder.Append("INSERT INTO ");
builder.Append(thisbuilder.QuotePrefix + tablename + thisbuilder.QuoteSuffix);
builderForValues.Append(" VALUES");
SQLiteParameterCollection ColumnsToWrite = thisbuilder.DataAdapter.InsertCommand.Parameters;
string[] strArray = new string[ColumnsToWrite.Count];
for (int i = 0; i < ColumnsToWrite.Count; i++)
{
SQLiteParameter row = ColumnsToWrite[i];
if (((row != null) && (row.SourceColumn.Length != 0)))//父类补充条件：&& thisbuilder.IncludeInInsertValues(row))
{
string columnName = row.SourceColumn;
builder.Append(str2);
builderForValues.Append(str2);
str2 = ", ";
builder.Append(thisbuilder.QuotePrefix + row.SourceColumn + thisbuilder.QuoteSuffix);
//values build
builderForValues.Append("'");
builderForValues.Append(datarow[columnName].ToString());
builderForValues.Append("'");
index++;
}
}
builder.Append(")");
builderForValues.Append(")");
if (index == 0)
{
builder.Append(" DEFAULT VALUES");
}
else
{
builder.Append(builderForValues);
}
return(builder.ToString());
}
