private static void WriteSqlScriptForSynonymSets(TextWriter writer, IList <IList <string> > synonymSets)
{
const string tableName = "EquivalentTerms";
const string columnTerm = "searchTerm";
const string columnGroupId = "equivalentGroupId";
Debug.Assert(synonymSets.Count > 0, "synonymSets.Length > 0");
writer.WriteLine("DECLARE @equivalentId UNIQUEIDENTIFIER");
writer.WriteLine();
writer.WriteLine("DELETE FROM dbo." + tableName);
foreach (IList <string> synonymSet in synonymSets)
{
writer.WriteLine();
writer.WriteLine("SET @equivalentId = '{0}'", Guid.NewGuid().ToString("D"));
writer.WriteLine();
foreach (string synonym in synonymSet)
{
writer.WriteLine("INSERT INTO dbo." + tableName
+ "(" + columnTerm + ", " + columnGroupId + ") VALUES ('{0}', @equivalentId)",
TextUtil.EscapeSqlText(synonym));
}
}
writer.WriteLine();
writer.WriteLine("GO");
}
public override ReportRunOutcome Run(IDbConnectionFactory connectionFactory, XlsFile excelFile, StringBuilder stringOutput)
{
if (string.IsNullOrEmpty(_report.PromoCode))
{
return(ReportRunOutcome.InvalidParameters);
}
var sql = string.Format(@"
SELECT COUNT(1)
FROM dbo.RegisteredUser ru
INNER JOIN dbo.Member m
ON m.id = ru.id
INNER JOIN dbo.JoinReferral jr
ON jr.userId = ru.id
WHERE (jr.promotionCode = '{0}' OR jr.referralCode = '{0}')
AND ru.createdTime BETWEEN @timeStart AND @timeEnd", TextUtil.EscapeSqlText(_report.PromoCode));
using (var connection = connectionFactory.CreateConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = DbCommandTimeout;
command.CommandType = CommandType.Text;
command.CommandText = sql;
DatabaseHelper.AddParameter(command, "@timeStart", DbType.DateTime, _dateRange.Start.Value.Date);
DatabaseHelper.AddParameter(command, "@timeEnd", DbType.DateTime, _dateRange.End.Value.AddDays(1));
DatabaseHelper.InlineParameters(command); // This avoids sp_executesql and make it much faster
var joins = (int)DatabaseHelper.TimeExecuteScalar(command);
if (joins == 0)
{
return(ReportRunOutcome.NoResults);
}
stringOutput.Append(joins.ToString());
return(ReportRunOutcome.TextResultOnly);
}
}
}
/// <summary>
/// Gets the SQL script representation of the specified value, suitable for an INSERT script.
/// </summary>
public static string GetSqlScriptValue(object value)
{
if (value == null)
{
throw new ArgumentNullException("value");
}
if (value is DBNull)
{
return("NULL");
}
else if (value is string)
{
return("'" + TextUtil.EscapeSqlText((string)value) + "'");
}
else if (value is byte[])
{
return(GetBinaryAsSqlText((byte[])value));
}
else if (value is bool)
{
return((bool)value ? "1" : "0");
}
else if (value is Guid)
{
return("'" + ((Guid)value).ToString("D").ToUpper() + "'");
}
else if (value is DateTime)
{
return("'" + ((DateTime)value).ToString(dateTimeFormat) + "'");
}
else
{
return(value.ToString());
}
}
private static string GetParamDeclareValue(IDataParameter param)
{
if (param.Value == null)
{
return(null);
}
else if (param.Value is DBNull)
{
return("NULL");
}
switch (param.DbType)
{
case DbType.AnsiStringFixedLength:
case DbType.AnsiString:
return("'" + TextUtil.EscapeSqlText(param.Value.ToString()) + "'");
case DbType.StringFixedLength:
return("N'" + TextUtil.EscapeSqlText(param.Value.ToString()) + "'");
case DbType.String:
// For some reason other types are defaulted to "string", so don't entirely trust the DbType.
// Check the type of the value.
if (param.Value is bool)
{
goto case DbType.Boolean;
}
else if (param.Value is byte[])
{
goto case DbType.Binary;
}
else
{
return("N'" + TextUtil.EscapeSqlText(param.Value.ToString()) + "'");
}
case DbType.Binary:
return(GetBinaryAsSqlText((byte[])param.Value));
case DbType.Boolean:
return(Convert.ToBoolean(param.Value) ? "1" : "0");
case DbType.Guid:
return("'" + ((Guid)param.Value).ToString("D").ToUpper() + "'");
case DbType.Date:
return("'" + ((DateTime)param.Value).ToString(dateFormat) + "'");
case DbType.DateTime:
case DbType.DateTime2:
return("'" + ((DateTime)param.Value).ToString(dateTimeFormat) + "'");
case DbType.Time:
return("'" + ((DateTime)param.Value).ToString(timeFormat) + "'");
// Converstion are necessary for numeric types, because the actual value may be of a different type,
// eg. an enum, so ToString() may return something other than the number.
case DbType.Byte:
return(Convert.ToByte(param.Value).ToString());
case DbType.Int16:
return(Convert.ToInt16(param.Value).ToString());
case DbType.Int32:
return(Convert.ToInt32(param.Value).ToString());
case DbType.Int64:
return(Convert.ToInt64(param.Value).ToString());
case DbType.Single:
return(Convert.ToSingle(param.Value).ToString());
case DbType.Double:
return(Convert.ToDouble(param.Value).ToString());
case DbType.Currency:
case DbType.Decimal:
return(Convert.ToDecimal(param.Value).ToString());
default:
Debug.Fail("What's the proper way to declare type " + param.DbType + " in SQL?");
return(param.Value.ToString());
}
}
