DZone Snippets: t-sql code

Timeslice temp table in SQL, useful for joining from and reporting counts of stuff

rizlbay (JohnC) — Tue, 07 Aug 2007 09:11:44 GMT

// @table full of timeslices







declare @TimeSlices table

(

	StartTime datetime,

	EndTime datetime

)

declare @StartTime datetime

declare @EndTime datetime

declare @IncrementMinutes int



set @StartTime  =	'2007-08-06 17:00:00.000'

set @EndTime  =		'2007-08-07 08:00:00.000'

set @Incrementminutes  = 60





while (1=1)

begin

	insert into @TimeSlices

	select @StartTime, dateadd(mi, @IncrementMinutes, @StartTime)



	set @StartTime = dateadd(mi, @IncrementMinutes, @StartTime)

	if (@StartTime > @EndTime) BREAK

end



select	count(TransmissionAttemptId) as FilesSent, 

		StartTime as StartTime, 

		dateadd(mi, @IncrementMinutes, StartTime) as EndTime

	from @TimeSlices T

	left join TransmissionAttempt TA

	on	TA.SentAt > T.StartTime

		and 

		TA.SentAt < T.EndTime

	group by T.StartTime

C#: Execute A Query & Return A Reader

cornerblue (CornerBLUE, Inc.) — Thu, 19 Jul 2007 22:02:34 GMT



public static SqlDataReader GetReader(string Query)

{

	string ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["CONNECTION_STRING_NAME"].ConnectionString;

	SqlConnection con = new SqlConnection(ConnectionString);

	SqlCommand command = new SqlCommand();



	command.Connection = con;

	command.Connection.Open();

	command.CommandText = Query;

	return command.ExecuteReader();

}

PRNG In SQL Select

jokeyxero (xero) — Thu, 17 May 2007 00:39:32 GMT

Simple example of a PRNG (pseudo-random number generator) written into a SQL statement

Example is in T-SQL, but it ports well

Actual application should use either a better random algorithm, or the output be used with randomized seeds. This is definitely not cryptographically secure. It's very handy if you need a simple random number with your recordset though.



-- Setup some vars we'll need

DECLARE @prng TABLE (seed BIGINT, rnum nchar(10))

DECLARE @seeds TABLE (seed BIGINT)

DECLARE @seed BIGINT

DECLARE @C1 BIGINT, @C2 BIGINT, @C3 BIGINT

SET @seed = 0

SET @C1 = 1664525

SET @C2 = 4294967296

SET @C3 = 1013904223



-- Create a seed table so we can have some data to use

WHILE @seed < 10

BEGIN

    INSERT INTO @seeds (seed) VALUES (@seed)

    SET @seed = @seed + 1

END



-- Create our PRNG (inserts into table for illustrative purposes)

-- prng(seed) ::= ((((C1 * seed) % C2) + C3) % C2) / C2

-- Then convert prng(seed) into a string

-- of 10 chars, 8 of which are decimal places

INSERT INTO @prng

SELECT

    seed,

    REPLACE(

        STR(

            ( CAST((((@C1*seed)%@C2)+@C3)%@C2 AS FLOAT) )

            / ( CAST(@C2 AS FLOAT)),

            10, 8

        ),

        ' ', '0') AS rnum

FROM @seeds



-- Let's take a look at what we created

SELECT * FROM @prng