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