public void SetForeignKeyColumnName(string foreignKeyColumnName)
{
GentleSqlFactory sf = map.Provider != null?map.Provider.GetSqlFactory() : Broker.GetSqlFactory();
foreignKeyColumnName = foreignKeyColumnName.Trim();
if (sf.IsReservedWord(foreignKeyColumnName))
{
this.foreignKeyColumnName = sf.QuoteReservedWord(foreignKeyColumnName);
}
else
{
this.foreignKeyColumnName = foreignKeyColumnName;
}
}
/// <summary>
/// Update a specific named parameter with the given value.
/// </summary>
/// <param name="paramName">The parameter whose value to set</param>
/// <param name="paramValue">The value being assigned to the parameter</param>
public void SetParameter(string paramName, object paramValue)
{
FieldMap fm = map != null?map.GetFieldMapFromColumn(paramName) : null;
// support setting parameters without using the prefix character
if (!cmd.Parameters.Contains(paramName))
{
GentleSqlFactory sf = broker.GetSqlFactory();
paramName = sf.GetParameterPrefix() + paramName + sf.GetParameterSuffix();
}
Check.Verify(cmd.Parameters.Contains(paramName), Error.NoSuchParameter, paramName);
IDataParameter param = (IDataParameter)cmd.Parameters[paramName];
SetParameter(param, paramValue, fm);
}
private static DateTime EnsureValidDate(DateTime val)
{
// ensure date is within the range supported by the persistence engine
// TODO static method cannot access broker instance
GentleSqlFactory sf = Broker.Provider.GetSqlFactory();
if (val < sf.MinimumSupportedDateTime)
{
return(sf.MinimumSupportedDateTime);
}
if (val > sf.MaximumSupportedDateTime)
{
return(sf.MaximumSupportedDateTime);
}
return(val);
}
public void SetDbType(string dbType, bool isUnsigned)
{
GentleSqlFactory sf = map.Provider != null?map.Provider.GetSqlFactory() : Broker.GetSqlFactory();
try
{
long dbt = sf.GetDbType(dbType, isUnsigned);
if (dbt != NO_DBTYPE)
{
this.dbType = dbt;
}
}
catch (GentleException fe)
{
// determine field types from the property type if it hasn't been set and
// the actual database column is unknown..
// TODO maybe we should just throw an error rather than hope this will work ;)
if (fe.Error == Error.UnsupportedColumnType && this.dbType == NO_DBTYPE && memberType != null)
{
this.dbType = sf.GetDbType(memberType);
}
}
}
/// <summary>
/// <p>Update the statement parameter values using the values from the key.</p>
/// <p>Warning! This will erase all parameter values not in the key, so if you
/// need to set additional parameters be sure to call this method before
/// any calls to SetParameter.</p>
/// </summary>
/// <param name="key">The key instance containing the parameter values.</param>
/// <param name="isUpdateAll">If true all statement parameters will be updated. If the
/// key has no value a null value will be assigned.</param>
public void SetParameters(Key key, bool isUpdateAll)
{
Check.VerifyNotNull(key, Error.NullParameter, "key");
GentleSqlFactory sf = broker.GetSqlFactory();
foreach (IDataParameter param in cmd.Parameters)
{
// strip leading @ character if present
string prefix = sf.GetParameterPrefix();
string suffix = sf.GetParameterSuffix();
string paramName = param.ParameterName;
if (paramName.StartsWith(prefix))
{
paramName = paramName.Substring(prefix.Length, paramName.Length - prefix.Length);
}
//if needed, strip out the suffix from the paramName/column name
if (suffix != "" && paramName.EndsWith(suffix))
{
paramName = paramName.Substring(0, paramName.Length - suffix.Length);
}
FieldMap fm = map.GetFieldMapFromColumn(paramName);
// handle special case where parameter is concurrency control column
if (fm == null && map.ConcurrencyMap != null && paramName.StartsWith("New") &&
(paramName.Substring(3, paramName.Length - 3) == map.ConcurrencyMap.ColumnName))
{
fm = map.ConcurrencyMap;
}
// if key contains property names translate parameter name into property name
string index = key.isPropertyKeys ? fm.MemberName : paramName;
// check if we should set parameter value
if (key.ContainsKey(index) || isUpdateAll)
{
Check.VerifyNotNull(fm, Error.Unspecified, "Could not find a value for the parameter named {0}", index);
SetParameter(param, key.ContainsKey(index) ? key[index] : null, fm);
}
}
}
/// <summary>
/// Execute the current instance using the supplied database connection and return
/// the result of the operation as an instance of the <see cref="SqlResult"/> class.
/// </summary>
/// <param name="conn">The database connection to use for executing this statement.</param>
/// <param name="tr">The transaction instance to run this statement in. If there is no
/// transaction context then this parameter should be null. This will cause the
/// connection to be closed after execution.</param>
/// <returns>The result of the operation</returns>
internal SqlResult Execute(IDbConnection conn, IDbTransaction tr)
{
Check.VerifyNotNull(conn, Error.NoNewConnection);
cmd.Connection = conn;
cmd.Transaction = tr;
SqlResult sr = null;
StringBuilder log = IsLoggingEnabled ? new StringBuilder(500) : null;
try
{
if (log != null)
{
log.AppendFormat("Executing query: {0}{1}", cmd.CommandText, Environment.NewLine);
foreach (IDataParameter param in cmd.Parameters)
{
object paramValue = param.Value == null ? "null" : param.Value;
log.AppendFormat("Parameter: {0} = {1}{2}", param.ParameterName, paramValue, Environment.NewLine);
}
}
switch (statementType)
{
case StatementType.Count:
case StatementType.Identity:
sr = new SqlResult(broker, cmd.ExecuteScalar(), this);
break;
case StatementType.Select:
sr = new SqlResult(broker, cmd.ExecuteReader(), this);
break;
case StatementType.Update:
case StatementType.Delete:
case StatementType.SoftDelete:
// returns the number of rows affected
sr = new SqlResult(broker, cmd.ExecuteNonQuery(), this);
break;
case StatementType.Insert:
if (map != null && map.IdentityMap != null)
{
GentleSqlFactory sf = broker.GetSqlFactory();
// check if we need to execute insert statement separately
if (!sf.HasCapability(Capability.BatchQuery))
{
// execute the insert command
cmd.ExecuteNonQuery();
// get the sql string for retrieving the generated id
string sql = sf.GetIdentitySelect(null, map);
// get an sql statement that will execute using ExecuteScalar
SqlStatement stmt = broker.GetStatement(StatementType.Identity, sql);
// execute the query and fetch the id
sr = stmt.Execute(cmd.Connection, tr);
}
else
{
// returns the 1st column of the 1st row in the result set (i.e. new identity)
sr = new SqlResult(broker, cmd.ExecuteScalar(), this);
}
}
else
{
sr = new SqlResult(broker, cmd.ExecuteNonQuery(), this);
}
break;
default:
// returns the number of rows affected
sr = new SqlResult(broker, cmd.ExecuteReader(), this);
break;
}
if (log != null)
{
PerformExecutionLog(log, sr);
}
}
catch (Exception e)
{
if (!Check.IsCalledFrom("Analyzer.Analyze", e) || !GentleSettings.AnalyzerSilent)
{
Check.LogError(LogCategories.StatementExecution, e);
}
if (e is GentleException)
{
throw;
}
else
{
throw new GentleException(Error.StatementError, cmd.CommandText, e);
}
}
finally
{
// close the database connection (only if not in transaction)
if (conn != null && conn.State == ConnectionState.Open && tr == null)
{
conn.Close();
}
// only if not in transaction; would otherwise break with PostgreSQL (not permitted)
if (tr == null)
{
// clear the connection reference
try
{
cmd.Connection = null;
}
catch
{
// ignore errors here.. like for the SQLite-provider which complains for no good reason
}
// clear the transaction reference
cmd.Transaction = null;
}
}
return(sr);
}
/// <summary>
/// Construct a new SqlBuilder instance for constructing a statement of the given type
/// for the specified business class type.
/// </summary>
/// <param name="provider">The IGentleProvider used to obtain an SqlFactory</param>
/// <param name="stmtType">The type of SQL statement to construct</param>
/// <param name="type">The object class to work on</param>
/// <param name="logicalOperator">The logic operator used with constraints (can be either AND or OR)</param>
public SqlBuilder( IGentleProvider provider, StatementType stmtType, Type type, LogicalOperator logicalOperator ) :
base( provider != null ? new PersistenceBroker( provider ) : type != null ? new PersistenceBroker( type ) : null )
{
this.provider = provider ?? broker.Provider;
sf = this.provider.GetSqlFactory();
cmd = sf.GetCommand();
this.stmtType = stmtType;
map = type != null ? ObjectFactory.GetMap( SessionBroker, type ) : null;
this.logicalOperator = logicalOperator == LogicalOperator.Or ? " or " : " and ";
fields = new ArrayList(); // list of fields
quotedFields = new ArrayList(); // list of fields
constraints = new ArrayList(); // list of string constraints
parameterValues = new Hashtable();
parameterOrder = new ArrayList();
}
