public void AddField(string fieldname, object value, FieldType fieldtype, Type keyInitializerType, bool isChanged)
{
if (!_selectFields.Contains(fieldname))
{
_selectFields.Add(fieldname);
}
if (fieldtype == FieldType.KeyField)
{
if (value == null)
{
_whereClausel.Add(string.Format("{0} IS NULL", fieldname));
}
else
{
SqliteParameter param = DataBaseProvider.GetParameter($"@param{_parameters.Count}", value);
_whereClausel.Add($"{fieldname} = {param.ParameterName}");
_parameters.Add(param);
}
}
else if (isChanged && (fieldtype == FieldType.WriteableField || fieldtype == FieldType.UpdateableField) && !_setValues.ContainsKey(fieldname))
{
if (value == null)
{
_setValues.Add(fieldname, "NULL");
}
else
{
SqliteParameter param = DataBaseProvider.GetParameter($"@param{_parameters.Count}", value);
_setValues.Add(fieldname, param.ParameterName);
_parameters.Add(param);
}
}
}
private DbParameter AddDbParameter(object value)
{
DbParameter returnValue = DataBaseProvider.GetParameter($"@param{_databaseProvider.GetUniqe()}", value);
_parameters.Add(returnValue);
return(returnValue);
}
public void AddField(string fieldname, object value, FieldType fieldtype, Type keyInitializerType, bool isChanged)
{
if (fieldtype == FieldType.KeyField)
{
SqliteParameter param = DataBaseProvider.GetParameter($"@param{_parameters.Count}", value);
_whereClausel.Add($"{fieldname} = {param.ParameterName}");
_parameters.Add(param);
}
}
public SelectCommandBuilder(DataBaseProvider databaseProvider)
{
_databaseProvider = databaseProvider;
_parameters = new List <DbParameter>();
_selectFields = new List <string>();
_orderbyExpressions = new List <Tuple <LambdaExpression, bool> >();
_joins = new List <Join>();
_top = -1;
_alias = $"T{_databaseProvider.GetUniqe()}";
_whereExpressions = new List <LambdaExpression>();
}
public DbCommand GetDbCommand()
{
DbCommand command = DataBaseProvider.GetCommand();
foreach (SqliteParameter parameter in _parameters)
{
command.Parameters.Add(parameter);
}
command.CommandText = _whereClausel.Count == 0 ? $"DELETE FROM \"{_tablename}\"" : $"DELETE FROM \"{_tablename}\" WHERE {string.Join(" AND ", _whereClausel.ToArray())}";
return(command);
}
public DbCommand GetDbCommand()
{
DbCommand command = DataBaseProvider.GetCommand();
foreach (SqliteParameter parameter in _parameters)
{
command.Parameters.Add(parameter);
}
command.CommandText = _insertFields.Count == 0 ?
$"INSERT INTO \"{_tablename}\" DEFAULT VALUES;\r\nSELECT {string.Join(", ", _selectFields.ToArray())} FROM {_tablename} WHERE {string.Join(" AND ", _whereClausel.ToArray())}" :
$"INSERT INTO \"{_tablename}\" ({string.Join(", ", _insertFields.ToArray())}) VALUES ({string.Join(", ", _insertValues.ToArray())});\r\nSELECT {string.Join(", ", _selectFields.ToArray())} FROM \"{_tablename}\" WHERE {string.Join(" AND ", _whereClausel.ToArray())}";
return(command);
}
public void AddField(string fieldname, object value, FieldType fieldtype, Type keyInitializerType, bool isChanged)
{
if (!_selectFields.Contains(fieldname))
{
_selectFields.Add(fieldname);
}
if (_insertFields.Contains(fieldname))
{
return;
}
if (fieldtype == FieldType.KeyField)
{
KeyInitializer keyInitializer = keyInitializerType != null?KeyInitializer.GetInitializer(keyInitializerType) : null;
if (keyInitializer == null || !keyInitializer.CheckEmpty(value))
{
SqliteParameter param = DataBaseProvider.GetParameter($"@param{_parameters.Count}", value);
_insertFields.Add(fieldname);
_insertValues.Add(param.ParameterName);
_whereClausel.Add(string.Format("{0} = {1}", param.ParameterName, fieldname));
_parameters.Add(param);
}
else
{
_whereClausel.Add(keyInitializer.GetWhereClause(fieldname, _tablename));
}
}
else if (fieldtype == FieldType.WriteableField || fieldtype == FieldType.InsertableField)
{
if (value == null)
{
_insertFields.Add(fieldname);
_insertValues.Add("NULL");
}
else
{
SqliteParameter param = DataBaseProvider.GetParameter($"@param{_parameters.Count}", value);
_insertFields.Add(fieldname);
_insertValues.Add(param.ParameterName);
_parameters.Add(param);
}
}
}
public DbCommand GetDbCommand()
{
string[] setStrings = new string[_setValues.Count];
int i = 0;
foreach (KeyValuePair <string, string> item in _setValues)
{
setStrings[i] = $"{item.Key} = {item.Value}";
i++;
}
DbCommand command = DataBaseProvider.GetCommand();
foreach (SqliteParameter parameter in _parameters)
{
command.Parameters.Add(parameter);
}
command.CommandText = $"UPDATE \"{_tablename}\" SET {string.Join(", ", setStrings)} WHERE {string.Join(" AND ", _whereClausel.ToArray())};\r\nSELECT {string.Join(", ", _selectFields.ToArray())} FROM \"{_tablename}\" WHERE {string.Join(" AND ", _whereClausel.ToArray())}";
return(command);
}
public SqliteDataBaseInitializer(string connectionString, DataBaseProvider databaseProvider, Func <DbCommand> getCommandFunc) : base(connectionString, databaseProvider, getCommandFunc)
{
_databaseProvider = databaseProvider;
_connectionString = connectionString;
}
public DbCommand GetDbCommand()
{
if (string.IsNullOrEmpty(_tablename))
{
throw new InvalidOperationException("Tablename is not set.");
}
StringBuilder stringBuilder = new StringBuilder("SELECT");
stringBuilder.Append($" {string.Join($", ", _selectFields.Select(x => $"{_alias}.{x} {x}").ToArray())} FROM \"{_tablename}\" {_alias}");
string whereClause = null;
if (_whereExpressions.Count == 1)
{
whereClause = _databaseProvider.ExpressionParser.ParseExpression(_whereExpressions[0], this);
}
else if (_whereExpressions.Count > 1)
{
foreach (LambdaExpression expression in _whereExpressions)
{
if (whereClause == null)
{
whereClause = "(";
}
else
{
whereClause += " AND (";
}
whereClause += _databaseProvider.ExpressionParser.ParseExpression(expression, this) + ")";
}
}
foreach (Join join in _joins)
{
stringBuilder.Append(" LEFT OUTER ").Append(join);
}
if (!string.IsNullOrWhiteSpace(whereClause))
{
stringBuilder.Append(" WHERE ").Append(whereClause);
}
if (_orderbyExpressions.Count != 0)
{
stringBuilder.Append(" ORDER BY ").Append(string.Join(", ", _orderbyExpressions.Select(
exp => exp.Item2 ?
_databaseProvider.ExpressionParser.ParseExpression(exp.Item1, this) + " DESC":
_databaseProvider.ExpressionParser.ParseExpression(exp.Item1, this)
).ToArray()));
}
if (_top > -1)
{
stringBuilder.AppendFormat(" LIMIT {0}", _top);
}
DbCommand command = DataBaseProvider.GetCommand();
foreach (SqliteParameter parameter in _parameters)
{
command.Parameters.Add(parameter);
}
command.CommandText = stringBuilder.ToString();
return(command);
}
