/// <summary>
/// Constructor.
/// </summary>
/// <param name="table">The table to work our magic on.</param>
/// <param name="objectWriteMode">The write mode.</param>
public SQLGenerator(string table, DataManager.ObjectWriteMode objectWriteMode)
: this(table)
{
_writeMode = objectWriteMode;
}
/// <summary>
/// Generate the SQL.
/// </summary>
/// <param name="mode">The write mode to generate.</param>
/// <returns>A SQL string.</returns>
public string GenerateSQL(DataManager.ObjectWriteMode mode)
{
string sql = string.Empty;
if (mode == DataManager.ObjectWriteMode.Insert)
{
string scopeIdentity = string.Empty;
string fieldNames = string.Empty;
string fieldValues = string.Empty;
foreach (TableFieldValue fieldValue in _values)
{
if (fieldValue.ScopeIdentity != TableFieldValue.SpecialColumnBehavior.Nothing)
{
if (fieldValue.ScopeIdentity == TableFieldValue.SpecialColumnBehavior.ReturnTheValue ||
fieldValue.ScopeIdentity == TableFieldValue.SpecialColumnBehavior.BothReturnAndSet)
{
_identityReturnName = string.Format("new_{0}", fieldValue.FieldName);
scopeIdentity += string.Format("select SCOPE_IDENTITY() as '{0}' \r\n\r\n", _identityReturnName);
}
if (fieldValue.ScopeIdentity == TableFieldValue.SpecialColumnBehavior.SetVariableInScope ||
fieldValue.ScopeIdentity == TableFieldValue.SpecialColumnBehavior.BothReturnAndSet)
{
_identityScopeVarName = string.Format("@new_{0}", fieldValue.FieldName);
scopeIdentity += string.Format("declare {0} BIGINT\r\nselect {0} = SCOPE_IDENTITY()\r\n\r\n", _identityScopeVarName);
}
}
else
{
fieldNames += string.Format("{0}{1}", fieldNames == string.Empty ? "" : ", ", fieldValue.FieldName);
string value;
if (fieldValue.FieldValue == null)
{
// set to default in case of min val? As of now, no. || (fieldValue.FieldValue.GetType() == typeof(DateTime) && (DateTime)fieldValue.FieldValue == DateTime.MinValue)
value = "null";
}
//else if (fieldValue.SQuoteIt != TableFieldValue.SQuoteBehavior.NoSQuote &&
// (fieldValue.FieldValue.GetType() == typeof(string) ||
// fieldValue.FieldValue.GetType() == typeof(DateTime) ||
// fieldValue.SQuoteIt == TableFieldValue.SQuoteBehavior.YesSQuote)
// )
else if (NeedToSQuote(fieldValue))
{
value = DataManager.SQuote(fieldValue.FieldValue.ToString());
}
else
{
value = fieldValue.FieldValue.ToString();
}
fieldValues += string.Format("{0}{1}", fieldValues == string.Empty ? "" : ", ", value);
}
}
sql = string.Format("INSERT INTO {0} ({1}) \r\n VALUES ({2}) \r\n\r\n", _table, fieldNames, fieldValues);
if (!Functions.IsEmptyString(scopeIdentity))
{
sql += scopeIdentity;
}
}
else if (mode == DataManager.ObjectWriteMode.Update)
{
string updateValues = string.Empty;
string whereClause = string.Empty;
foreach (TableFieldValue fieldValue in _values)
{
if (fieldValue.ScopeIdentity == TableFieldValue.SpecialColumnBehavior.AddToWhereFilterForUpdate)
{
whereClause += string.Format("{0}{1} = {2}",
whereClause == string.Empty ? "" : "\r\n and ",
fieldValue.FieldName,
NeedToSQuote(fieldValue) ? DataManager.SQuote(fieldValue.FieldValue.ToString()) : fieldValue.FieldValue.ToString());
}
else
{
updateValues += string.Format("{0}{1} = {2}",
updateValues == string.Empty ? "" : ",\r\n ",
fieldValue.FieldName,
NeedToSQuote(fieldValue) ? DataManager.SQuote(fieldValue.FieldValue.ToString()) : fieldValue.FieldValue.ToString());
}
}
sql = string.Format("UPDATE {0}\r\nSET {1}\r\nWHERE {2}\r\n\r\n", _table, updateValues, whereClause);
}
else
{
throw new ApplicationException("Sorry, only insert and update supported at this time.");
}
return(sql);
}
