Monday 27 February 2012

ProgrammerFail–Using System Functions

 

Nice example that shows what happens if we don’t have enough time to read a book about ‘Teach Yourself SQL Server Programming in 24 hours’ and start programming without knowing the tool that we use.

After we check msdn, we can easily check if two dates are in the same day:


create FUNCTION [dbo].[isSameDay](@DATE1 datetime, @DATE2 datetime)
RETURNS bit
AS
BEGIN
    DECLARE @result bit=0
    if (year(@DATE1)=year(@DATE2))
    and (month(@DATE1)=month(@DATE2))
    and (day(@DATE1)=day(@DATE2))
    set @result=1
    return @result
END

ProgrammerFail

!!!FAIL!!!

Friday 24 February 2012

Sql Server–Query random N elements from a table with identity PK

 

A simple solution how to query random N records from a database table without iteration on a full table:

 


--- because cannot call rand() in scalar valued function
CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

go

--- returns a randon number between min and max
CREATE FUNCTION RandNumber(@Min int, @Max int)
RETURNS int
AS
BEGIN
RETURN ceiling(@Min + (select RandNumber from vRandNumber) * (@Max-@Min))
END

go


-- selecting 100 random rows from a table
declare @cnt int

select @cnt = MAX(<pk_column_name, sysname, ID>)
from <table_name, sysname, sample_table>



select top <number_of_rows,, 100> *
from <table_name, sysname, sample_table>
where <pk_column_name, sysname, ID> in (
    SELECT dbo.RandNumber(1, @cnt) as rnd
    from <table_name, sysname, sample_table>
)
option (recompile) -- it selects only one row without recompile

 

That’s all.

 

Morzels

Monday 20 February 2012

ProgrammerFail–Stored Procedures

 

I have found this construction in a living project’s db layer:

ALTER PROCEDURE [dbo].[GetSomething] @param VARCHAR(100)
    AS
    BEGIN
    exec [dbo].[GetSomething2] @param
/*
-- Original Select
select .....

*/
END


And what is in the stored procedure that we call:

ALTER PROCEDURE [dbo].[GetSomething2] (
    @param    varchar(100)
) AS
BEGIN
    --- new select
    select ....
    
END

 

Nice code if we collect stored procedures instead of stamps…

ProgrammerFail

!!!FAIL!!!

Thursday 9 February 2012

ProgrammerFail–The Fail of Naming

 

 

if (!need_to_update)
    update_all_views();

Let's update all the views, when there is no need to update.. As we can see high quality comments describe what this piece of shit does.

 

ProgrammerFail

!!!FAIL!!!

Tuesday 7 February 2012

ProgrammerFail–Advanced techniques using Sql Server in C#

 

[WebMethod]
public static string CreateProfileID(string firstName, string lastName)
{
    string profileID = "";

    using (SqlConnection conn = Common.getConnection())
    {
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = string.Format("select dbo.[create-something] ('{0}', '{1}') as somethingID", firstName, lastName);

            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                profileID = dr["somethingID"] as string;
            }
        }
        catch (Exception e)
        {

        }
    }

    return profileID;
}

Everything in one method: How to handle exceptions.

 

ProgrammerFail

!!!FAIL!!!

Thursday 2 February 2012

ProgrammerFail–Go To The Hell

 

Without any comment, let’s go to the hell:

/// <summary>
/// WriteRaw writes out the given string "unescaped", in other words it better be well formed XML markup.
/// So for the XmlNodeWriter we parse this string and build the resulting tree, so it maps to setting the
/// InnerXml property.  
/// </summary>
/// <param name="data"></param>
public override void WriteRaw(string data)
{
    if (data.IndexOf("<") < 0)
    {
        WriteString(data);
        return;
    }

    switch (state)
    {
        case WriteState.Start:
            goto case WriteState.Content;
        case WriteState.Prolog:
            goto case WriteState.Content;
        case WriteState.Element:
            state = WriteState.Content;
            goto case WriteState.Content;
        case WriteState.Attribute:
            {
                ArrayList saved = new ArrayList();
                if (ca.HasChildNodes)
                {
                    while (ca.FirstChild != null)
                    {
                        saved.Add(ca.FirstChild);
                        ca.RemoveChild(ca.FirstChild);
                    }
                }
                ca.InnerXml = data;
                for (int i = saved.Count - 1; i >= 0; i--)
                {
                    ca.PrependChild((XmlNode)saved[i]);
                }
            }
            break;
        case WriteState.Content:
            {
                ArrayList saved = new ArrayList();
                if (current.HasChildNodes)
                {
                    while (current.FirstChild != null)
                    {
                        saved.Add(current.FirstChild);
                        current.RemoveChild(current.FirstChild);
                    }
                }
                current.InnerXml = data;
                for (int i = saved.Count - 1; i >= 0; i--)
                {
                    current.PrependChild((XmlNode)saved[i]);
                }
                state = WriteState.Content;
            }
            break;
        case WriteState.Closed:
            throw new InvalidOperationException("Writer is closed");
    }

}

Nice combination of using goto and switch statement together. I hope I will never work with the author.

 

ProgrammerFail

!!!FAIL!!!