public void AssignParameters(System.Data.Common.DbCommand command, object[] parameterValues)
{
DbParameter ps0 = command.CreateParameter();
ps0.ParameterName = SqlPara + "RestaurantId";
ps0.DbType = DbType.String;
ps0.Value = parameterValues[0];
command.Parameters.Add(ps0);
DbParameter ps1 = command.CreateParameter();
ps1.ParameterName = SqlPara + "PeopleCount";
ps1.DbType = DbType.String;
ps1.Value = parameterValues[1];
command.Parameters.Add(ps1);
}
public void AssignParameters(System.Data.Common.DbCommand command, object[] parameterValues)
{
DbParameter ps0 = command.CreateParameter();
ps0.ParameterName = SqlPara + "Uid";
ps0.DbType = DbType.String;
ps0.Value = parameterValues[0];
command.Parameters.Add(ps0);
}
private static System.Data.IDbDataParameter CreateParam(System.Data.IDbCommand Command, string ParamName, object ParamValue)
{
System.Data.IDbDataParameter dbParam = Command.CreateParameter();
dbParam.ParameterName = ParamName;
dbParam.Value = ParamValue;
return dbParam;
}
public T LoadByCode(string code, DBColumn column, DBLoadParam param, DBTransaction transaction = null)
{
var row = SelectOne(column, code);
if (row == null && (param & DBLoadParam.Load) == DBLoadParam.Load)//&& !IsSynchronized
{
var command = System.CreateCommand(Schema.Connection, CreateQuery($"where a.{column.Name}={Schema.System.ParameterPrefix}{column.Name}", "a", Columns));
System.CreateParameter(command, Schema.System.ParameterPrefix + column.Name, code, column);
row = Load(command, param, transaction).FirstOrDefault();
}
return(row);
}
public void AssignParameters(System.Data.Common.DbCommand command, object[] parameterValues)
{
DbParameter ps0 = command.CreateParameter();
ps0.ParameterName = SqlPara + "CompanyId";
ps0.DbType = DbType.String;
ps0.Value = parameterValues[0];
command.Parameters.Add(ps0);
//DbParameter ps1 = command.CreateParameter();
//ps1.ParameterName = SqlPara + "OrderId";
//ps1.DbType = DbType.String;
//ps1.Value = parameterValues[1];
//command.Parameters.Add(ps1);
}
public static System.Data.IDbDataParameter AddParameter(System.Data.IDbCommand command, string strParameterName, object objValue, System.Data.ParameterDirection pad, System.Data.DbType dbType)
{
System.Data.IDbDataParameter parameter = command.CreateParameter();
if (!strParameterName.StartsWith("@"))
{
strParameterName = "@" + strParameterName;
} // End if (!strParameterName.StartsWith("@"))
parameter.ParameterName = strParameterName;
parameter.DbType = dbType;
parameter.Direction = pad;
// Es ist keine Zuordnung von DbType UInt64 zu einem bekannten SqlDbType vorhanden.
// No association DbType UInt64 to a known SqlDbType
if (objValue == null)
parameter.Value = System.DBNull.Value;
else
parameter.Value = objValue;
command.Parameters.Add(parameter);
return parameter;
}
protected virtual void GetEntityDeleteParameter(System.Data.IDbCommand CM, BaseEntitie ent)
{
ColumnCollection _column = ent.Column;
for (int i = 0; i < _column.Count; i++)
{
System.Data.IDbDataParameter sParameter = CM.CreateParameter();
sParameter.ParameterName = _column[i].FieldName;
sParameter.Value = _column[i].FieldValue;
sParameter.DbType = _column[i].FieldType;
CM.Parameters.Add(sParameter);
}
}
/// <summary> Parses a line of data, and sets the prepared statement with the
/// values. If a token contains "<null>" then a null value is passed
/// in.
///
/// </summary>
/// <param name="data">the tokenized string that is mapped to a row
/// </param>
/// <param name="stmt">the statement to populate with data to be inserted
/// </param>
/// <returns> false if the header is returned, true otherwise
/// </returns>
/// <throws> SQLException if an error occurs while inserting data into the database </throws>
protected internal override bool insert(System.String data, System.Data.Common.DbCommand stmt)
{
if (!parsedHeader)
{
parsedHeader = true;
log.Info("Header returned: " + data);
return false;
}
int counter = 1;
log.Info("Row being parsed: " + data);
stmt.Parameters.Clear();
foreach (string colVal in data.Split(Delimiter))
{
System.Data.Common.DbParameter parameter = stmt.CreateParameter();
parameter.ParameterName = PARAMETER_NAME_PREFIX + counter.ToString();
if (colVal.ToLower().Equals("<null>"))
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = colVal;
}
stmt.Parameters.Add(parameter);
counter++;
}
return true;
}
/// <summary>
/// Sets the value of a parameter using an object. The given argument object will be converted to the
/// corresponding SQL type before being sent to the database.
/// </summary>
/// <param name="command">Command object to be changed.</param>
/// <param name="parameterIndex">One-based index of the parameter to be set.</param>
/// <param name="parameter">The object containing the input parameter value.</param>
public void SetObject(System.Data.OleDb.OleDbCommand command, int parameterIndex, System.Object parameter)
{
if (command.Parameters.Count < parameterIndex)
command.Parameters.Add(command.CreateParameter());
command.Parameters[parameterIndex - 1].Value = parameter;
}
/// <summary>
/// Sets a parameter to SQL NULL.
/// </summary>
/// <param name="command">Command object to be changed.</param>
/// <param name="parameterIndex">One-based index of the parameter to be set.</param>
/// <param name="targetSqlType">The SQL type to be sent to the database.</param>
public void SetNull(System.Data.OleDb.OleDbCommand command, int parameterIndex, int sqlType)
{
if (command.Parameters.Count < parameterIndex)
command.Parameters.Add(command.CreateParameter());
command.Parameters[parameterIndex - 1].Value = System.Convert.DBNull;
command.Parameters[parameterIndex - 1].OleDbType = (System.Data.OleDb.OleDbType)sqlType;
}
public void BuildCommandWithParameters(System.Data.IDbCommand command, bool append, string query, object[] par, bool isRaw)
{
if (append)
{
if (command.CommandText == null)
command.CommandText = "";
else if (command.CommandText.Length > 0)
command.CommandText += ";\n";
}
else
{
command.CommandText = "";
command.Parameters.Clear();
}
System.Text.StringBuilder sb = new System.Text.StringBuilder(query.Length * 2);
StringCollection paramNames = new StringCollection();
for (int i = 0; i < query.Length; ++i)
{
char c = query[i];
if (c == '\'')
{
int j = ++i;
for (;;++j)
{
if (j >= query.Length)
throw new ArgumentException("Query has unbalanced quotes");
if (query[j] == '\'')
{
if (j + 1 >= query.Length || query[j + 1] != '\'')
break;
// double apostrophe
j++;
}
}
string stringValue = query.Substring(i, j - i);
char modifier = j + 1 < query.Length ? query[j + 1] : ' ';
string paramName;
switch (modifier)
{
case 'V':
sb.Append('\'');
sb.Append(stringValue);
sb.Append('\'');
j++;
break;
case 'D':
paramName = AddParameterFromValue(command, DateTime.ParseExact(stringValue, "yyyyMMddHH:mm:ss", CultureInfo.InvariantCulture), null);
sb.Append(paramName);
j++;
break;
case 'A':
stringValue = stringValue.Replace("''", "'");
paramName = AddParameterFromValue(command, stringValue, SoqlLiteralValueModifiers.AnsiString);
sb.Append(paramName);
j++;
break;
default:
if (!isRaw && (!UseSafeLiterals || !IsStringSafeForLiteral(stringValue)))
{
stringValue = stringValue.Replace("''", "'");
paramName = AddParameterFromValue(command, stringValue, null);
sb.Append(paramName);
}
else
{
sb.Append('\'');
sb.Append(stringValue);
sb.Append('\'');
}
break;
}
i = j;
}
else if (c == '{')
{
c = query[i + 1];
if (c == 'L')
{
// {L:fieldDataTypeName:value
int startPos = i + 3;
int endPos = query.IndexOf(':', startPos);
if (endPos < 0)
throw new ArgumentException("Missing ':' in literal specification");
SoqlLiteralValueModifiers modifier = SoqlParser.ParseLiteralValueModifiers(query.Substring(startPos, endPos - startPos));
FieldDataType fdt = modifier.DataTypeOverride;
int valueStartPos = endPos + 1;
bool anyEscape = false;
for (i = valueStartPos; i < query.Length && query[i] != '}'; ++i)
{
if (query[i] == '\\')
{
i++;
anyEscape = true;
}
}
string literalValue = query.Substring(valueStartPos, i - valueStartPos);
if (anyEscape)
{
literalValue = literalValue.Replace("\\}", "}");
literalValue = literalValue.Replace("\\\\", "\\");
}
SoodaFieldHandler fieldHandler = FieldHandlerFactory.GetFieldHandler(fdt);
object v = fieldHandler.RawDeserialize(literalValue);
if (v == null)
{
sb.Append("null");
}
else if (UseSafeLiterals && v is int)
{
sb.Append((int)v);
}
else if (UseSafeLiterals && v is string && IsStringSafeForLiteral((string)v))
{
sb.Append('\'');
sb.Append((string)v);
sb.Append('\'');
}
else
{
IDbDataParameter p = command.CreateParameter();
p.Direction = ParameterDirection.Input;
p.ParameterName = GetNameForParameter(command.Parameters.Count);
fieldHandler.SetupDBParameter(p, v);
command.Parameters.Add(p);
sb.Append(p.ParameterName);
}
}
else if (c >= '0' && c <= '9')
{
i++;
int paramNumber = 0;
do
{
paramNumber = paramNumber * 10 + c - '0';
c = query[++i];
} while (c >= '0' && c <= '9');
SoqlLiteralValueModifiers modifiers = null;
if (c == ':')
{
int startPos = i + 1;
i = query.IndexOf('}', startPos);
if (i < 0)
throw new ArgumentException("Missing '}' in parameter specification");
modifiers = SoqlParser.ParseLiteralValueModifiers(query.Substring(startPos, i - startPos));
}
else if (c != '}')
throw new ArgumentException("Missing '}' in parameter specification");
object v = par[paramNumber];
if (v is SoodaObject)
{
v = ((SoodaObject)v).GetPrimaryKeyValue();
}
if (v == null)
{
sb.Append("null");
}
else if (UseSafeLiterals && v is int)
{
sb.Append((int)v);
}
else if (UseSafeLiterals && v is string && IsStringSafeForLiteral((string)v))
{
sb.Append('\'');
sb.Append((string)v);
sb.Append('\'');
}
else
{
sb.Append(AddNumberedParameter(command, v, modifiers, paramNames, paramNumber));
}
}
else
{
throw new ArgumentException("Unexpected character in parameter specification");
}
}
else if (c == '(' || c == ' ' || c == ',' || c == '=' || c == '>' || c == '<' || c == '+' || c == '-' || c == '*' || c == '/')
{
sb.Append(c);
if (i < query.Length - 1)
{
c = query[i + 1];
if (c >= '0' && c <= '9' && !UseSafeLiterals)
{
int v = 0;
double f = 0;
double dp = 0;
bool isDouble = false;
do
{
if (c != '.')
{
if (!isDouble)
v = v * 10 + c - '0';
else
{
f = f + dp * (c - '0');
dp = dp * 0.1;
}
}
else
{
isDouble = true;
f = v;
dp = 0.1;
}
i++;
if (i < query.Length - 1)
c = query[i+1];
} while (((c >= '0' && c <= '9') || c == '.') && (i < query.Length - 1));
if (!isDouble)
{
string paramName = AddParameterFromValue(command, v, null);
sb.Append(paramName);
}
else
{
string paramName = AddParameterFromValue(command, f, null);
sb.Append(paramName);
}
}
}
}
else
{
sb.Append(c);
}
}
command.CommandText += sb.ToString();
}
private List<Dictionary<string, object>> DumpTable(System.Data.IDbCommand cmd, string tablename, string pagingfield, string offset_str, string pagesize_str)
{
var result = new List<Dictionary<string, object>>();
long pagesize;
if (!long.TryParse(pagesize_str, out pagesize))
pagesize = 100;
pagesize = Math.Max(10, Math.Min(500, pagesize));
cmd.CommandText = "SELECT * FROM \"" + tablename + "\"";
long offset = 0;
if (!string.IsNullOrWhiteSpace(offset_str) && long.TryParse(offset_str, out offset) && !string.IsNullOrEmpty(pagingfield))
{
var p = cmd.CreateParameter();
p.Value = offset;
cmd.Parameters.Add(p);
cmd.CommandText += " WHERE \"" + pagingfield + "\" < ?";
}
if (!string.IsNullOrEmpty(pagingfield))
cmd.CommandText += " ORDER BY \"" + pagingfield + "\" DESC";
cmd.CommandText += " LIMIT " + pagesize.ToString();
using(var rd = cmd.ExecuteReader())
{
var names = new List<string>();
for(var i = 0; i < rd.FieldCount; i++)
names.Add(rd.GetName(i));
while (rd.Read())
{
var dict = new Dictionary<string, object>();
for(int i = 0; i < names.Count; i++)
dict[names[i]] = rd.GetValue(i);
result.Add(dict);
}
}
return result;
}
public class LogData : IRESTMethodGET, IRESTMethodDocumented
void AddParameter (System.Data.Common.DbCommand cm, string name, object value)
{
var param = cm.CreateParameter ();
param.ParameterName = ":" + name;
param.Value = value;
cm.Parameters.Add (param);
}
