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