public InsertQueryBuilder(DataMapper db)
{
_db = db;
_tableName = MapRepository.Instance.GetTableName(typeof(T));
_previousSqlMode = _db.SqlMode;
_mappingHelper = new MappingHelper(_db);
_mappings = MapRepository.Instance.GetColumns(typeof(T));
_dialect = QueryFactory.CreateDialect(_db);
}
public virtual bool Any()
{
SqlModes previousSqlMode = _db.SqlMode;
// Generate a row count query
string where = _whereBuilder != null?_whereBuilder.ToString() : string.Empty;
bool useAltNames = _isFromView || _isGraph || _isJoin;
IQuery query = QueryFactory.CreateRowCountSelectQuery(_tables, _db, where, SortBuilder, useAltNames);
string queryText = query.Generate();
_db.SqlMode = SqlModes.Text;
int count = Convert.ToInt32(_db.ExecuteScalar(queryText));
_db.SqlMode = previousSqlMode;
return(count > 0);
}
public int Delete <T>(string tableName, Expression <Func <T, bool> > filter)
{
// Remember sql mode
var previousSqlMode = this.SqlMode;
SqlMode = SqlModes.Text;
var mappingHelper = new MappingHelper(this);
if (tableName == null)
{
tableName = MapRepository.Instance.GetTableName(typeof(T));
}
var dialect = QGen.QueryFactory.CreateDialect(this);
TableCollection tables = new TableCollection();
tables.Add(new Table(typeof(T)));
var where = new WhereBuilder <T>(Command, dialect, filter, tables, false, false);
IQuery query = QueryFactory.CreateDeleteQuery(dialect, tables[0], where.ToString());
Command.CommandText = query.Generate();
int rowsAffected = 0;
try
{
OpenConnection();
rowsAffected = Command.ExecuteNonQuery();
}
finally
{
CloseConnection();
}
// Return to previous sql mode
SqlMode = previousSqlMode;
return(rowsAffected);
}
/// <summary>
/// Executes the query and returns a list of results.
/// </summary>
/// <returns>A list of query results of type T.</returns>
public virtual List <T> ToList()
{
SqlModes previousSqlMode = _db.SqlMode;
ValidateQuery();
BuildQueryOrAppendClauses();
if (_isGraph || _isJoin)
{
_results = (List <T>)_db.QueryToGraph <T>(_queryText, EntGraph, _childrenToLoad);
}
else
{
_results = (List <T>)_db.Query <T>(_queryText, _results, _isFromView);
}
// Return to previous sql mode
_db.SqlMode = previousSqlMode;
return(_results);
}
public Select(SqlModes SqlMode)
: base(SqlMode) { }
protected Command(SqlModes SqlMode)
: this()
{
m_Mode = SqlMode;
}
public BeginTransactionCommand(SqlModes sqlMode)
: base(sqlMode) { }
public TextCommand(SqlModes sqlMode)
: base(sqlMode)
{
}
protected TableCommand(SqlModes sqlMode)
: base(sqlMode) { }
protected Command(SqlModes SqlMode)
: this()
{
m_Mode = SqlMode;
}
private string SqlText(SqlModes sqlMode, bool valuesOnly)
{
System.Text.StringBuilder FieldList = new System.Text.StringBuilder();
System.Text.StringBuilder ParamList = new System.Text.StringBuilder();
foreach (Lfx.Data.Field ThisField in this.Fields)
{
if (FieldList.Length == 0)
{
FieldList.Append(@"""" + ThisField.ColumnName + @"""");
}
else
{
FieldList.Append(@", """ + ThisField.ColumnName + @"""");
}
string ParamValue = "";
if (ThisField.Value == null || ThisField.Value == DBNull.Value)
{
ParamValue = "NULL";
}
else
{
string Tipo = ThisField.Value.GetType().Name.Replace("System.", "");
switch (Tipo)
{
case "qGen.SqlFunctions":
switch (((qGen.SqlFunctions)(ThisField.Value)))
{
case SqlFunctions.Now:
ParamValue = "NOW()";
break;
default:
throw new NotImplementedException();
}
break;
case "Lfx.Data.SqlLiteral":
ParamValue = ThisField.Value.ToString();
break;
case "Lfx.Data.LDateTime":
ParamValue = "'" + ((NullableDateTime)(ThisField.Value)).Value.ToString(Lfx.Types.Formatting.DateTime.SqlDateTimeFormat) + "'";
break;
case "DateTime":
ParamValue = "'" + System.Convert.ToDateTime(ThisField.Value).ToString(Lfx.Types.Formatting.DateTime.SqlDateTimeFormat) + "'";
break;
case "Single":
case "Double":
case "Decimal":
ParamValue = Lfx.Types.Formatting.FormatNumberSql(System.Convert.ToDecimal(ThisField.Value), 8);
break;
case "Integer":
case "Int16":
case "Int32":
case "Int64":
ParamValue = System.Convert.ToInt32(ThisField.Value).ToString();
break;
default:
ParamValue = "'" + Lfx.Data.Connection.EscapeString(ThisField.Value.ToString(), sqlMode) + "'";
break;
}
}
if (ParamList.Length == 0)
{
ParamList.Append(ParamValue);
}
else
{
ParamList.Append(", " + ParamValue);
}
}
if (valuesOnly)
{
return("(" + ParamList.ToString() + ")");
}
else
{
string Res = @"INSERT INTO """ + this.Tables + @""" (" + FieldList.ToString() + ") VALUES (" + ParamList.ToString() + ")";
if (this.OnDuplicateKeyUpdate)
{
string UpdateClause = null;
foreach (Lfx.Data.Field ThisField in this.Fields)
{
if (UpdateClause == null)
{
UpdateClause = @" ON DUPLICATE KEY UPDATE """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")";
}
else
{
UpdateClause += @", """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")";
}
}
Res += UpdateClause;
}
return(Res);
}
}
public Insert(SqlModes SqlMode)
: base(SqlMode)
{
}
private string SqlText(SqlModes sqlMode)
{
return(this.SqlText(sqlMode, false));
}
public string ToString(SqlModes mode)
{
this.SqlMode = mode;
return(this.ToString());
}
public CommitCommand(SqlModes sqlMode)
: base(sqlMode)
{
}
public SetCommand(SqlModes sqlMode)
: base(sqlMode)
{
}
public RollBackCommand(SqlModes sqlMode)
: base(sqlMode) { }
public BeginTransactionCommand(SqlModes sqlMode)
: base(sqlMode)
{
}
public string SqlText(SqlModes sqlMode)
{
System.Text.StringBuilder FieldList = new System.Text.StringBuilder();
foreach (Lfx.Data.Field ThisField in this.Fields)
{
if (FieldList.Length == 0)
{
FieldList.Append(@"""" + ThisField.ColumnName + @"""");
}
else
{
FieldList.Append(@", """ + ThisField.ColumnName + @"""");
}
string ParamValue = "";
if (ThisField.Value == null || ThisField.Value == DBNull.Value)
{
ParamValue = "NULL";
}
else
{
switch (ThisField.Value.GetType().Name)
{
case "qGen.SqlFunctions":
switch (((qGen.SqlFunctions)(ThisField.Value)))
{
case SqlFunctions.Now:
ParamValue = "NOW()";
break;
default:
throw new NotImplementedException();
}
break;
case "Lfx.Data.SqlLiteral":
ParamValue = ThisField.Value.ToString();
break;
case "System.Single":
case "System.Double":
case "System.Decimal":
ParamValue = Lfx.Types.Formatting.FormatNumberSql(System.Convert.ToDecimal(ThisField.Value), 8);
break;
case "System.Integer":
case "System.Int16":
case "System.Int32":
case "System.Int64":
ParamValue = System.Convert.ToInt32(ThisField.Value).ToString();
break;
default:
ParamValue = "'" + Lfx.Data.Connection.EscapeString(ThisField.Value.ToString(), sqlMode) + "'";
break;
}
}
FieldList.Append("=" + ParamValue);
}
return(@"UPDATE """ + this.Tables + @""" SET " + FieldList.ToString() + " WHERE " + this.WhereClause.ToString());
}
public RollBackCommand(SqlModes sqlMode)
: base(sqlMode)
{
}
public Select(SqlModes SqlMode)
: base(SqlMode)
{
}
public string SqlText(SqlModes sqlMode)
{
System.Text.StringBuilder FieldList = new System.Text.StringBuilder();
foreach (Lfx.Data.Field ThisField in this.Fields) {
if (FieldList.Length == 0)
FieldList.Append(@"""" + ThisField.ColumnName + @"""");
else
FieldList.Append(@", """ + ThisField.ColumnName + @"""");
string ParamValue = "";
if (ThisField.Value == null || ThisField.Value == DBNull.Value) {
ParamValue = "NULL";
} else {
switch (ThisField.Value.GetType().Name) {
case "qGen.SqlFunctions":
switch (((qGen.SqlFunctions)(ThisField.Value))) {
case SqlFunctions.Now:
ParamValue = "NOW()";
break;
default:
throw new NotImplementedException();
}
break;
case "Lfx.Data.SqlLiteral":
ParamValue = ThisField.Value.ToString();
break;
case "System.Single":
case "System.Double":
case "System.Decimal":
ParamValue = Lfx.Types.Formatting.FormatNumberSql(System.Convert.ToDecimal(ThisField.Value), 8);
break;
case "System.Integer":
case "System.Int16":
case "System.Int32":
case "System.Int64":
ParamValue = System.Convert.ToInt32(ThisField.Value).ToString();
break;
default:
ParamValue = "'" + Lfx.Data.Connection.EscapeString(ThisField.Value.ToString(), sqlMode) + "'";
break;
}
}
FieldList.Append("=" + ParamValue);
}
return @"UPDATE """ + this.Tables + @""" SET " + FieldList.ToString() + " WHERE " + this.WhereClause.ToString();
}
protected TableCommand(SqlModes sqlMode)
: base(sqlMode)
{
}
public Update(SqlModes SqlMode)
: base(SqlMode)
{
}
public Update(SqlModes SqlMode)
: base(SqlMode) { }
public CommitCommand(SqlModes sqlMode)
: base(sqlMode) { }
private string SqlText(SqlModes sqlMode)
{
return this.SqlText(sqlMode, false);
}
public SetCommand(SqlModes sqlMode)
: base(sqlMode) { }
private string SqlText(SqlModes sqlMode, bool valuesOnly)
{
System.Text.StringBuilder FieldList = new System.Text.StringBuilder();
System.Text.StringBuilder ParamList = new System.Text.StringBuilder();
foreach (Lfx.Data.Field ThisField in this.Fields) {
if (FieldList.Length == 0)
FieldList.Append(@"""" + ThisField.ColumnName + @"""");
else
FieldList.Append(@", """ + ThisField.ColumnName + @"""");
string ParamValue = "";
if (ThisField.Value == null || ThisField.Value == DBNull.Value) {
ParamValue = "NULL";
} else {
string Tipo = ThisField.Value.GetType().Name.Replace("System.", "");
switch (Tipo) {
case "qGen.SqlFunctions":
switch (((qGen.SqlFunctions)(ThisField.Value))) {
case SqlFunctions.Now:
ParamValue = "NOW()";
break;
default:
throw new NotImplementedException();
}
break;
case "Lfx.Data.SqlLiteral":
ParamValue = ThisField.Value.ToString();
break;
case "Lfx.Data.LDateTime":
ParamValue = "'" + ((NullableDateTime)(ThisField.Value)).Value.ToString(Lfx.Types.Formatting.DateTime.SqlDateTimeFormat) + "'";
break;
case "DateTime":
ParamValue = "'" + System.Convert.ToDateTime(ThisField.Value).ToString(Lfx.Types.Formatting.DateTime.SqlDateTimeFormat) + "'";
break;
case "Single":
case "Double":
case "Decimal":
ParamValue = Lfx.Types.Formatting.FormatNumberSql(System.Convert.ToDecimal(ThisField.Value), 8);
break;
case "Integer":
case "Int16":
case "Int32":
case "Int64":
ParamValue = System.Convert.ToInt32(ThisField.Value).ToString();
break;
default:
ParamValue = "'" + Lfx.Data.Connection.EscapeString(ThisField.Value.ToString(), sqlMode) + "'";
break;
}
}
if (ParamList.Length == 0)
ParamList.Append(ParamValue);
else
ParamList.Append(", " + ParamValue);
}
if (valuesOnly) {
return "(" + ParamList.ToString() + ")";
} else {
string Res = @"INSERT INTO """ + this.Tables + @""" (" + FieldList.ToString() + ") VALUES (" + ParamList.ToString() + ")";
if (this.OnDuplicateKeyUpdate) {
string UpdateClause = null;
foreach (Lfx.Data.Field ThisField in this.Fields) {
if (UpdateClause == null)
UpdateClause = @" ON DUPLICATE KEY UPDATE """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")";
else
UpdateClause += @", """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")";
}
Res += UpdateClause;
}
return Res;
}
}
public Insert(SqlModes SqlMode)
: base(SqlMode) { }
public TextCommand(SqlModes sqlMode)
: base(sqlMode) { }
