Spaghetti Tom: June 2011

Friday, June 24, 2011

how to use IN in SQL

SELECT * FROM contact_info WHERE phone_number IN ('1-800-555-1234','1-800-555-1235','1-800-555-1236')

Wednesday, June 22, 2011

SQL Cheat Sheet

...select count...

SELECT COUNT (in_product_id) FROM order_item WHERE in_product_id = 926528

...like...

SELECT * FROM catalog WHERE vc_catalog_name like '%persol%'

...left join...

SELECT a.in_catalog_id, b.vc_product_name AS vc_something_else, b.in_product_id

FROM catalog_product a

LEFT JOIN product b

ON a.in_product_id = b.in_product_id

...execute a stored procedure...

EXECUTE dbo.list_of_names_sp_report @in_condition = 1, @in_something_else = 2

...create a stored procedure... (CREATE becomes ALTER to update a stored procedure)

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[list_of_names_sp_report]

(@in_condition tinyint)

AS

IF(@in_condition = 1)

BEGIN

SELECT * FROM dbo.list_of_names

ORDER BY vc_name DESC

END

...using variables in stored procedures...

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[some_table_sp_updates]

(@in_condition tinyint,

@in_id int = 0,

@vc_whatever bit = 0,

@vc_some_info varchar(200) = '',

@vc_description varchar(200) = '',

@vc_thingy varchar(200) = '',

@vc_hidden_concern varchar(3000) = '',

@vc_another_variable_for_something varchar(1000) = '',

@in_number_of_toes int = 0)

AS

DECLARE @dt_when_was_i_last_touched datetime

SET @dt_when_was_i_last_touched = GETDATE()

IF(@in_condition = 1) -- This is a note

BEGIN

UPDATE some_table

SET vc_whatever = @vc_whatever,

vc_some_info = @vc_some_info,

vc_description = @vc_description,

vc_thingy = @vc_thingy,

vc_hidden_concern = @vc_hidden_concern,

vc_another_variable_for_something = @vc_another_variable_for_something,

in_number_of_toes = @in_number_of_toes,

dt_when_was_i_last_touched = @dt_when_was_i_last_touched

WHERE in_id = @in_id

END

...create a table with an ever unique id...

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

CREATE TABLE dbo.mytable

(

myid int NOT NULL IDENTITY (1, 1),

mycopy varchar(50) NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE dbo.mytable ADD CONSTRAINT

PK_mytable PRIMARY KEY CLUSTERED

(

myid

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ORMs prefer Guids for unique keys. Outside of ORMing, expect auto-incrementing integers.

...create a view...

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[cmn_vw_list_of_names]

AS

SELECT vc_name FROM dbo.list_of_names

WHERE in_abc = 123

GO

...create a function...

USE [MyDatabase]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FlaggedPerson]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[FlaggedPerson]

GO

CREATE FUNCTION [dbo].[FlaggedPerson] (@id INT, @altid INT)

RETURNS

@FlaggedTable TABLE

(

NameOfFlaggedPerson VARCHAR(100)

)

AS

BEGIN

DECLARE @Response VARCHAR(100)

SET @Response = 'No'

IF EXISTS(SELECT * FROM [SomeOtherTable] WHERE in_alt_id = @altid AND in_id <> @id AND in_third_id > 0)

BEGIN

SET @Response =

ISNULL(

(SELECT TOP 1

b.vc_first_name + ' ' + b.vc_last_name

FROM

dbo.[SomeOtherTable] a

LEFT JOIN

dbo.other_table_to_use b

ON a.in_third_id = b.in_other_table_to_use_id

WHERE

a.in_alt_id = @altid

AND

b.bt_active = 1

AND

a.in_id <> @id

AND

ISNULL(b.vc_first_name + ' ' + b.vc_last_name, 'x') <> 'x'

ORDER BY

a.dt_order_by_me ),'')

END

INSERT INTO @FlaggedTable (NameOfFlaggedPerson)

VALUES (@Response)

RETURN

END

GO

...access the function in a stored procedure, inside a join, like this...

OUTER APPLY dbo.FlaggedPerson(a.in_id, a.in_alt_id) h

...after having first having defined h like this...

h.[NameOfFlaggedPerson],

...parsing a string of comma seperated integers...

DECLARE @bigString VARCHAR(MAX), @id1 INT, @id2 INT

SET @bigString = @some_other_variable_set_beforehand

IF RIGHT(RTRIM(@bigstring),1) <> ','

BEGIN

SET @bigstring = @bigstring + ','

END

DECLARE @commaPt INT

SET @commaPt = CHARINDEX(',',@bigstring)

IF @commaPt > 1

BEGIN

SET @id1 = LEFT(@bigstring,@commaPt -1)

SET @bigString = RIGHT(@bigstring,LEN(@bigstring) - LEN(@id1) - 1)

WHILE LEN(@bigString) > 0

BEGIN

SET @commaPt = CHARINDEX(',',@bigstring)

SET @id2 = left(@bigstring,@commaPt - 1)

UPDATE

a

SET

in_unique_id = @id1

FROM

dbo.[some_table] a

WHERE

in_other_id = @id2

AND EXISTS(SELECT * FROM dbo.some_other_table WHERE in_third_id = @id1)

IF LEN(@bigstring) > LEN(@id2)

BEGIN

SET @bigString = RIGHT(@bigstring,LEN(@bigstring) - LEN(@id2) - 1)

END

ELSE

BEGIN

SET @bigString = RIGHT(@bigstring,LEN(@bigstring) - LEN(@id2))

END

END

END

END

GO

...update a table...

UPDATE myTable SET vc_greeting = 'hi' WHERE in_id = 123 OR in_id = 456

...where one of many columns contains a value...

SELECT TOP 500 in_id, vc_address, vc_name, vc_url FROM dbo.some_table WHERE CONTAINS((vc_address, vc_name, vc_url), 'match on me') AND vc_something_else = 'yay' ORDER BY vc_url desc, vc_address, vc_name

Friday, June 17, 2011

The Big Four (things I've learned so far at Headspring's ASP.NET MVC Boot Camp)

At this point I am more than halfway through taking the Headspring ASP.NET MVC Boot Camp. Today is the last day. So far I have learned of numerous neat tricks such as placing [AcceptVerbs(HttpVerbs.Post)] before an Action and placing a constructor on a Controller to painlessly instantiate a repository via a little help from StructureMap (custom model binding). There are four big things I'm learning that so far have really made the class worth my investment in it. They are:

Extensibility
Wouldn't it be neat if you could roll your own ActionResult? Well, you can. (This was a great learning exercise Jimmy Bogard!) The MVC framework is remarkably extensible. In experimenting last night I made a silly new MVC app in which one could navigate to /Home/Digit/88 and get back HTML which simply contained a random number between zero and eighty-eight. HomeController.cs looked like so:

using System;

using System.Web.Mvc;

using MvcApplication.Helpers;

namespace MvcApplication.Controllers

{

    public class HomeController : DefaultController

    {

        public ActionResult Index()

        {

            ViewBag.Message = "Welcome to ASP.NET MVC!";

            return View();

        }

        public RandomNumber Digit(string id)

        {

            return Foo(Convert.ToInt32(id));

        }

    }

}

So what is "DefaultController" anyways? Why are we inheriting from it instead of Controller? Well, we are still inheriting from Controller as it turns out. We inherit from DefaultController which in turn inherits from Controller allowing us to extend Controller and override Controller's methods if desired. DefaultController is a new class we have built. It reads like...

using System.Web.Mvc;

using MvcApplication.Helpers;

namespace MvcApplication.Controllers

{

    public class DefaultController : Controller

    {

        protected RandomNumber<T> Foo<T>(T topEnd)

        {

            return new RandomNumber<T>

            {

                rangeToRandomizeIn = topEnd

            };

        }

    }

}

The remaining piece holds the extension of ActionResult. I have it in a new file called RandomNumber.cs in a new folder called Helpers.

using System;

using System.Web.Mvc;

namespace MvcApplication.Helpers

{

    public class RandomNumber<T> : ActionResult

    {

        public T rangeToRandomizeIn { get; set; }

        public override void ExecuteResult(ControllerContext context)

        {

            Random random = new Random();

            Int32 range = Convert.ToInt32(rangeToRandomizeIn);

            var contentResult = new ContentResult();

            contentResult.Content = random.Next(0,range).ToString();

            contentResult.ContentType = "text/html";

            contentResult.ExecuteResult(context);

        }

    }

}
Filters
Hey, in the example above, what happens if one goes to /Home/Digit/Wonk instead of /Home/Digit/88 wherein the id cannot be converted from string to Int32? We can find out by adding <customErrors mode="On" /> to Web.config and then testing. We should see, in a box that appears...
- FormatException was unhandled by user code
- Input string was not in a correct format.
- Troubleshooting tips:
  - Make sure your method arguments are in the right format.
  - When converting a string to DateTime, parse the string to take the date before putting each variable into the DateTime object.
  - Get general help for this exception.
  - Search for more Help Online...
- Actions:
  - View Detail...
  - Enable editing
  - Copy exception detail to the clipboard
So, let's refactor the example above to account for the exception. Let's put...

public static void RegisterGlobalFilters(GlobalFilterCollection filters)

{

filters.Add(new HandleErrorAttribute());

}

...in Global.asax.cs if it is not already there, and let's put...
[HandleError(View = "FormatError", ExceptionType = typeof(FormatException))]
...immediately above the Digit Action in the Home Controller. Finally, let's create a FormatError View that just contains some text that alerts a user that he/she did not enter an integer. Run the app and get the error where applicable. Yay! So, why this aside...

In attempting to run the code in an Action, C#'s process may:
1. pass through an Authorize filter before the Action
2. pass through a Result filter after the Action
3. pass through an Exception filter if something goes wonk
These may be used to abstract away a lot of if/then sanity checking that might otherwise muck up Controllers.

(note that FormatException is just applicable in this case and that NullReferenceException or a different exception might be applicable in other cases/stage exceptions to get the exception type)
Use filters as Attributes on Actions, Controllers, base classes that extend Controllers, and Global. Add an Order parameter to an Attribute to specify the order in which filters fire off.
Razor
ASP.NET MVC 3 uses a new view engine called Razor which is... pretty sharp. It offers new markup. An example:

@using (Html.BeginForm("Save", "Foo"))

{

    <h1>ViewBag.ShoutingText</h1>

    <text>Complete to update Foo:</text>

    <div>

        @Html.LabelFor(m => m.Foo)

        @Html.TextBoxFor(m => m.Foo)

        @Html.HiddenFor(m => m.Id)

    </div>

    <input type="submit" value="Save" />

}

OK, so what does this do? The at symbol opens Razor syntax. Everything nested inside the curly braces is Razor markup with one exception. The only place we break out of Razor is in using the text tag to show copy instead of interpreting the copy as Razor markup, as, otherwise, everything is interpreted as either Razor markup or HTML markup which Razor is smart enough to know isn't of Razor. This blob of code will make a form. Some form controls may be made with Razor syntax and some may not. Something interesting here is the ViewBag which uses C# 4.0's dynamic feature to allow coders to assign whatever parameters they wish to it. ShoutingText isn't a parameter of ViewBag until we define it in advance of calling the View (a .cshtml file) that will contain the markup above. We do so like so:

public ActionResult Edit()

{

    ViewBag.ShoutingText = "Update Foo!";

    return View();

}
Routing
This is an example of customizing the routes found in Global.asax.cs. Herein, I create an exception to the last route with the first route. i.e. if one goes to http://www.example.com/ww1/Woodrow%20Wilson/ ASP.NET will not attempt to find a ww1 Controller as is the norm, it will find the Ally Controller, the Show Action (View is not available as an Action name given the nature of MVC) by default without an Action being specified, and Woodrow Wilson as the id

public static void RegisterRoutes(RouteCollection routes)

{

    routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

    routes.MapRoute(

        "Allies",

        "ww1/{id}/{action}",

        new { controller = "Ally", action = "Show" }

    );

    routes.MapRoute(

        "Default",

        "{controller}/{action}/{id}",

        new { controller = "Home", action = "Index", id = "" }

    );

}

This was a new insight for me. I did not know I could approach custom routing in such a manner previously or I would have approached building a CMS in MVC very differently. One thing I certainly would have done would have been to abstract administration into an Area (good call Justin Pope). To add an area, right click on the MVC project and select "Add Area," then put AreaRegistration.RegisterAllAreas(); in the Application_Start method of Global.asax.cs. If you drag existing Controllers to your Area you will have to update the namespace with Resharper. The area will have a SecurityAreaRegistration.cs file where its route logic is tucked away independent of those in Global.asax.cs. An example:

public override void RegisterArea(AreaRegistrationContext context)

{

    context.MapRoute(

        "Administration_default",

        "Administration/{controller}/{action}/{id}",

        new { action = "Index", id = UrlParameter.Optional }

    );

}

Other routing-related syntax includes:
- RedirectToRoute("Default", new { controller = "Thing", action = "Update" });
- @Html.RouteLink("Default", "Click Me", new { controller = "Thing", action = "Index" })
- @Html.ActionLink("Axe", "Delete", new { area = "Administration", controller = "Thing" })

You sure are silly for a guy with glasses.

My job with Essilor at framesdirect.com previously gave me the opportunity to work on a Greenfield MVC application and the opportunity for me to serve as de facto architect. I wish I had known then what I know now.

Saturday, June 11, 2011

Transitions. One thing fades to another.

At TXJS presently. Breaking for lunch. I sat with Tim Thomas earlier. He showed me CSS3 transitions. I have kind of shied away from CSS3 due to the downwards compatibility issues, but I know I cannot do so forever. I saw Divya Manian of Opera speak on Transitions and she mention that even IE10 will support them so they will eventually become the de facto way to do CSS animations.

a:hover {

   color: red;

   -webkit-transition: color .25s linear;

   transition: color .25s linear;

}

Thursday, June 9, 2011

The World Beyond the Maps

At my prior place of employment, I largely worked in an ORM (Object-relational mapping) environment. At my current place of employment, I am learning to get really dirty with MSSQL Management Studio Express. I have rediscovered Stored Procedures and moreover am learning of Functions and Views. With regards to functions, there was a Stored Procedure that called a function like so:

...some code...

SELECT

...some code...

isnull(b.thingamabob,'test') as Thingy,

...some code...

FROM

...some code...

OUTER APPLY dbo.somethingerother(a.id) b

...some code...

The function itself looks like:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[somethingerother]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[somethingerother]

GO

CREATE function [dbo].[somethingerother] (@theID int)

RETURNS

@WhateverTable table

(

thingamabob varchar(100)

)

AS

BEGIN

declare @whatever varchar(100)

set @whatever = 'test'

if exists(select * from [OrderExpansion] where orderid = @theID and somethingelse <> 33)

begin

...some code...

end

insert into @WhateverTable (thingamabob)

Values (@whatever)

Return

end

GO

It was pointed out to me that if the stored procedure returns 300 rows that it will run 301 queries as the function independently calls a separate query each time it is fired. I am learning a lot about optimization here. This is a better approach:

...some code...

SELECT

...some code...

ISNULL((SELECT TOP 1

FROM dbo.[order] x

WHERE x.orderid = a.id AND x.somethingelse <> 33),'test') as Thingy,

...some code...

FROM

...some code...

There are two approaches to data mapping:

automation
trust only yourself

My supervisor prefers the later distancing himself from the subselects of ORM in favor of simple joins and other performance optimizations. I am learning.

Tuesday, June 7, 2011

I need a new blog. Twitter sucks for notekeeping.

I'm no longer with Headspring and cannot break into the blog I created there. I'm going to need a new blog for notes and I am finding Twitter painful. I guess this is it.

Spaghetti Tom

Sunday, June 26, 2011

fighting with ASP.NET web forms