public 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 (!_selectFields.Contains(fieldname))
{
_selectFields.Add(fieldname);
}
if (fieldtype == FieldType.KeyField)
{
if (value == null)
{
_whereClausel.Add(string.Format("{0} IS NULL", fieldname));
}
else
{
SqlParameter 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
{
SqlParameter param = DataBaseProvider.GetParameter($"@param{_parameters.Count}", value);
_setValues.Add(fieldname, param.ParameterName);
_parameters.Add(param);
}
}
}
public void AddField(string fieldname, object value, FieldType fieldtype, Type keyInitializerType, bool isChanged)
{
if (fieldtype == FieldType.KeyField)
{
SqlParameter param = DataBaseProvider.GetParameter($"@param{_parameters.Count}", value);
_whereClausel.Add(string.Format("{0} = {1}", fieldname, param.ParameterName));
_parameters.Add(param);
}
}
public DbCommand GetDbCommand()
{
DbCommand command = DataBaseProvider.GetCommand();
command.Parameters.AddRange(_parameters.ToArray());
command.CommandText = _whereClausel.Count == 0 ?
string.Format("DELETE {0}", _tablename) :
string.Format("DELETE {0} WHERE {1}", _tablename, string.Join(" AND ", _whereClausel.ToArray()));
return(command);
}
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();
command.Parameters.AddRange(_parameters.ToArray());
command.CommandText =
string.Format(_insertFields.Count == 0 ? "{5}INSERT {0} DEFAULT VALUES\r\n{6}SELECT {3} FROM {0} WHERE {4}" :
"{5}INSERT {0} ({1}) VALUES ({2})\r\n{6}SELECT {3} FROM {0} WHERE {4}",
_tablename,
string.Join(", ", _insertFields.ToArray()),
string.Join(", ", _insertValues.ToArray()),
string.Join(", ", _selectFields.ToArray()),
string.Join(" AND ", _whereClausel.ToArray()),
_beforInsert.ToString(),
_afterInsert.ToString());
return(command);
}
public DbCommand GetDbCommand()
{
string[] setStrings = new string[_setValues.Count];
int i = 0;
foreach (KeyValuePair <string, string> item in _setValues)
{
setStrings[i] = string.Format("{0} = {1}", item.Key, item.Value);
i++;
}
DbCommand command = DataBaseProvider.GetCommand();
command.Parameters.AddRange(_parameters.ToArray());
command.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}\r\nSELECT {3} FROM {0} WHERE {2}",
_tablename,
string.Join(", ", setStrings),
string.Join(" AND ", _whereClausel.ToArray()),
string.Join(", ", _selectFields.ToArray()));
return(command);
}
public DbCommand GetDbCommand()
{
if (string.IsNullOrEmpty(_tablename))
{
throw new InvalidOperationException("Tablename is not set.");
}
StringBuilder stringBuilder = new StringBuilder("SELECT");
if (_top > -1)
{
stringBuilder.AppendFormat(" TOP {0}", _top);
}
stringBuilder.AppendFormat(" {2}.{0} FROM {1} {2}",
string.Join(string.Format(", {0}.", _alias), _selectFields.ToArray()), _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()));
}
DbCommand command = DataBaseProvider.GetCommand();
command.Parameters.AddRange(_parameters.ToArray());
command.CommandText = stringBuilder.ToString();
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))
{
SqlParameter 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
{
SqlParameter param = new SqlParameter($"@param{_parameters.Count}", keyInitializer.SqlDbType);
param.Value = DBNull.Value;
param.IsNullable = true;
if (!string.IsNullOrEmpty(keyInitializer.BeforInsert))
{
_beforInsert.AppendLine(keyInitializer.BeforInsert.Replace("{parameter}", param.ParameterName));
}
if (!string.IsNullOrEmpty(keyInitializer.AfterInsert))
{
_afterInsert.AppendLine(keyInitializer.AfterInsert.Replace("{parameter}", param.ParameterName));
}
if (keyInitializer.SetInInsert)
{
_insertFields.Add(fieldname);
_insertValues.Add(param.ParameterName);
}
_whereClausel.Add(string.Format("{0} = {1}", param.ParameterName, fieldname));
_parameters.Add(param);
}
}
else if (fieldtype == FieldType.WriteableField || fieldtype == FieldType.InsertableField)
{
if (value == null)
{
_insertFields.Add(fieldname);
_insertValues.Add("NULL");
}
else
{
SqlParameter param = DataBaseProvider.GetParameter($"@param{_parameters.Count}", value);
_insertFields.Add(fieldname);
_insertValues.Add(param.ParameterName);
_parameters.Add(param);
}
}
}
