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); }
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); } }
/// <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)); } }
/// <summary> /// Logs an error to the database. /// </summary> /// <remarks> /// Use the stored procedure called by this implementation to set a /// policy on how long errors are kept in the log. The default /// implementation stores all errors for an indefinite time. /// </remarks> public override string Log(Error error) { if (error == null) { throw new ArgumentNullException("error"); } string errorXml = ErrorXml.EncodeString(error); const string query = @" INSERT INTO Error ( Application, Host, Type, Source, Message, User, StatusCode, TimeUtc, AllXml) VALUES ( @Application, @Host, @Type, @Source, @Message, @User, @StatusCode, @TimeUtc, @AllXml); SELECT last_insert_rowid();"; using (SQLiteConnection connection = new SQLiteConnection(ConnectionString)) using (SQLiteCommand command = new SQLiteCommand(query, connection)) { SQLiteParameterCollection parameters = command.Parameters; parameters.Add("@Application", DbType.String, 60).Value = ApplicationName; parameters.Add("@Host", DbType.String, 30).Value = error.HostName; parameters.Add("@Type", DbType.String, 100).Value = error.Type; parameters.Add("@Source", DbType.String, 60).Value = error.Source; parameters.Add("@Message", DbType.String, 500).Value = error.Message; parameters.Add("@User", DbType.String, 50).Value = error.User; parameters.Add("@StatusCode", DbType.Int64).Value = error.StatusCode; parameters.Add("@TimeUtc", DbType.DateTime).Value = error.Time.ToUniversalTime(); parameters.Add("@AllXml", DbType.String).Value = errorXml; connection.Open(); return(Convert.ToInt64(command.ExecuteScalar()).ToString(CultureInfo.InvariantCulture)); } }
/// <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); } } } }
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); }
internal static void Agregar(this SQLiteParameterCollection cmdParam, String pStrParametro, object pValor) { // Generamos un nuevo parametro SQLiteParameter nuevoParametro = new SQLiteParameter(); // Agregamos los valores a utilizar nuevoParametro.ParameterName = pStrParametro; if (pValor != null) { nuevoParametro.Value = pValor; nuevoParametro.DbType = pValor.TraerTipoBase(); } else { nuevoParametro.Value = DBNull.Value; nuevoParametro.DbType = System.Data.DbType.Object; } // Agregamos el parametro al comando cmdParam.Add(nuevoParametro); }
private void GetWhereClauseByKey(StringBuilder sql, SQLiteParameterCollection parameters) { PropertyInfo[] keys = GetKeyProperties(); if (keys == null) { return; } string prefix = "WHERE "; foreach (PropertyInfo p in keys) { sql.Append(prefix); object o = p.GetValue(this); if (o == null) { if (p.PropertyType == typeof(string) || p.PropertyType.IsSubclassOf(typeof(string))) { o = string.Empty; } else { o = DBNull.Value; } } string f = GetFieldName(p); if (o is DBNull) { sql.Append(f); sql.AppendLine(" IS NULL"); } else { sql.Append(f); sql.Append(" = @"); sql.Append(f); sql.AppendLine(); parameters.Add(new SQLiteParameter("@" + f, o)); } prefix = " AND "; } }
/// <summary> /// Parses parameter names from SQL Statement, assigns values from object array , /// and returns fully populated ParameterCollection. /// </summary> /// <param name="commandText">Sql Statement with "@param" style embedded parameters</param> /// <param name="paramList">object[] array of parameter values</param> /// <returns>SQLiteParameterCollection</returns> /// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement.</remarks> private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList) { if (paramList == null || paramList.Length == 0) { return(null); } SQLiteParameterCollection coll = cmd.Parameters; string parmString = commandText.Substring(commandText.IndexOf("@")); // pre-process the string so always at least 1 space after a comma. parmString = parmString.Replace(",", " ,"); // get the named parameters into a match collection string pattern = @"(@)\S*(.*?)\b"; Regex ex = new Regex(pattern, RegexOptions.IgnoreCase); MatchCollection mc = ex.Matches(parmString); string[] paramNames = new string[mc.Count]; int i = 0; foreach (Match m in mc) { paramNames[i] = m.Value; i++; } // now let's type the parameters int j = 0; Type t = null; foreach (object o in paramList) { t = o.GetType(); SQLiteParameter parm = new SQLiteParameter(); switch (t.ToString()) { case ("DBNull"): case ("Char"): case ("SByte"): case ("UInt16"): case ("UInt32"): case ("UInt64"): throw new SystemException("Invalid data type"); case ("System.String"): parm.DbType = DbType.String; parm.ParameterName = paramNames[j]; parm.Value = (string)paramList[j]; coll.Add(parm); break; case ("System.Byte[]"): parm.DbType = DbType.Binary; parm.ParameterName = paramNames[j]; parm.Value = (byte[])paramList[j]; coll.Add(parm); break; case ("System.Int32"): parm.DbType = DbType.Int32; parm.ParameterName = paramNames[j]; parm.Value = (int)paramList[j]; coll.Add(parm); break; case ("System.Boolean"): parm.DbType = DbType.Boolean; parm.ParameterName = paramNames[j]; parm.Value = (bool)paramList[j]; coll.Add(parm); break; case ("System.DateTime"): parm.DbType = DbType.DateTime; parm.ParameterName = paramNames[j]; parm.Value = Convert.ToDateTime(paramList[j]); coll.Add(parm); break; case ("System.Double"): parm.DbType = DbType.Double; parm.ParameterName = paramNames[j]; parm.Value = Convert.ToDouble(paramList[j]); coll.Add(parm); break; case ("System.Decimal"): parm.DbType = DbType.Decimal; parm.ParameterName = paramNames[j]; parm.Value = Convert.ToDecimal(paramList[j]); break; case ("System.Guid"): parm.DbType = DbType.Guid; parm.ParameterName = paramNames[j]; parm.Value = (System.Guid)(paramList[j]); break; case ("System.Object"): parm.DbType = DbType.Object; parm.ParameterName = paramNames[j]; parm.Value = paramList[j]; coll.Add(parm); break; default: throw new SystemException("Value is of unknown data type"); } // end switch j++; } return(coll); }
/// <summary> /// Returns a page of errors from the databse in descending order /// of logged time. /// </summary> public override int GetErrors(int pageIndex, int pageSize, IList errorEntryList) { if (pageIndex < 0) { throw new ArgumentOutOfRangeException("pageIndex", pageIndex, null); } if (pageSize < 0) { throw new ArgumentOutOfRangeException("pageSize", pageSize, null); } const string sql = @" SELECT ErrorId, Application, Host, Type, Source, Message, User, StatusCode, TimeUtc FROM Error ORDER BY ErrorId DESC LIMIT @PageIndex * @PageSize, @PageSize; SELECT COUNT(*) FROM Error"; using (SQLiteConnection connection = new SQLiteConnection(ConnectionString)) using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { SQLiteParameterCollection parameters = command.Parameters; parameters.Add("@PageIndex", DbType.Int16).Value = pageIndex; parameters.Add("@PageSize", DbType.Int16).Value = pageSize; connection.Open(); using (SQLiteDataReader reader = command.ExecuteReader()) { if (errorEntryList != null) { while (reader.Read()) { string id = Convert.ToString(reader["ErrorId"], CultureInfo.InvariantCulture); Error error = new Error(); error.ApplicationName = reader["Application"].ToString(); error.HostName = reader["Host"].ToString(); error.Type = reader["Type"].ToString(); error.Source = reader["Source"].ToString(); error.Message = reader["Message"].ToString(); error.User = reader["User"].ToString(); error.StatusCode = Convert.ToInt32(reader["StatusCode"]); error.Time = Convert.ToDateTime(reader["TimeUtc"]).ToLocalTime(); errorEntryList.Add(new ErrorLogEntry(this, id, error)); } } // // Get the result of SELECT COUNT(*) FROM Page // reader.NextResult(); reader.Read(); return(reader.GetInt32(0)); } } }
//Adicionar parametros public static void AdicionarParametros(string nomeParametro, object valorParametro) { sqlParameterCollection.Add(new SQLiteParameter(nomeParametro, valorParametro)); }
/// <summary> /// Parses parameter names from SQL Statement, assigns values from object array , /// and returns fully populated ParameterCollection. /// </summary> /// <param name="commandText">Sql Statement with "@param" style embedded parameters</param> /// <param name="paramList">object[] array of parameter values</param> /// <returns>SQLiteParameterCollection</returns> /// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement.</remarks> private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, Dictionary <string, object> paramList) { if (paramList == null || paramList.Count == 0) { return(null); } SQLiteParameterCollection coll = cmd.Parameters; // now let's type the parameters int j = 0; Type t = null; foreach (KeyValuePair <string, object> o in paramList) { var oVal = null == o.Value ? String.Empty : o.Value; t = oVal.GetType(); SQLiteParameter parm = new SQLiteParameter(); switch (t.ToString()) { case ("DBNull"): case ("Char"): case ("SByte"): case ("UInt16"): case ("UInt32"): case ("UInt64"): throw new SystemException("Invalid data type"); case ("System.String"): parm.DbType = DbType.String; parm.ParameterName = o.Key; parm.Value = (string)o.Value; coll.Add(parm); break; case ("System.Byte[]"): parm.DbType = DbType.Binary; parm.ParameterName = o.Key; parm.Value = (byte[])o.Value; coll.Add(parm); break; case ("System.Int32"): parm.DbType = DbType.Int32; parm.ParameterName = o.Key; parm.Value = (int)o.Value; coll.Add(parm); break; case ("System.Boolean"): parm.DbType = DbType.Boolean; parm.ParameterName = o.Key; parm.Value = (bool)o.Value; coll.Add(parm); break; case ("System.DateTime"): parm.DbType = DbType.DateTime; parm.ParameterName = o.Key; parm.Value = Convert.ToDateTime(o.Value); coll.Add(parm); break; case ("System.Double"): parm.DbType = DbType.Double; parm.ParameterName = o.Key; parm.Value = Convert.ToDouble(o.Value); coll.Add(parm); break; case ("System.Decimal"): parm.DbType = DbType.Decimal; parm.ParameterName = o.Key; parm.Value = Convert.ToDecimal(o.Value); break; case ("System.Guid"): parm.DbType = DbType.Guid; parm.ParameterName = o.Key; parm.Value = (System.Guid)(o.Value); break; case ("System.Object"): parm.DbType = DbType.Object; parm.ParameterName = o.Key; parm.Value = o.Value; coll.Add(parm); break; default: throw new SystemException("Value is of unknown data type"); } // end switch j++; } return(coll); }
public void AdicionarParametros(string nome, object valor) { sQLiteParameterCollection.Add(new SQLiteParameter(nome, valor)); }
static public void AddRange(this SQLiteParameterCollection item, IEnumerable <SQLiteParameter> to_add) { to_add.Process(p => item.Add(p)); }
public void AdicionarParamentro(string paramentro, object valor) { liteParameterCollection.Add(new SQLiteParameter(paramentro, valor)); }