/// <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); }