/// <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()); }