Categories panel

Please welcome: the category panel prototype:

 Add a category. It is on top, so you don't need to scroll down to add a new category.

Remove the category.

Edit the category. Edit and remove buttons are on different sides so you are not going to click remove by mistake.

Love my new category panel.

Free icons downloaded from http://www.styleprone.com/free-web-icons

Web colors found at http://cloford.com/resources/colours/websafe1.htm

Dreaming of User-Defined Data Types

Keeping the T-SQL variables (like stored procedure parameters) consistent with actual table column definitions might be a real headache, if you don't rely on ORM framework to do the job for you. You modify your table and you go searching for any place in your code where you might use the variable to keep your column value.

Dependency viewer is a great help, still it has it's limitations.

1. Prior to SQL Server 2008, dependency information between objects was not reliable (Dependency information was recorded only if the referenced object existed when the referencing object was created).
2. Still, SQL Server doesn’t record dependency information for references that appear in dynamic SQL and CLR code.

Using UDDT sounds like a remedy. Let's see.

Ideally, instead of creating table using primitive types, as follows:

CREATE TABLE dbo.Category
(
    Id UNIQUEIDENTIFIER NOT NULL
    , UserName NVARCHAR(256) NOT NULL
    , Title NVARCHAR(256) NOT NULL
    , Color INT NOT NULL
), CONSTRAINT CategoryIdPK PRIMARY KEY NONCLUSTERED (Id)
GO

I'd like to have something like this:

CREATE TYPE dbo.TUSERNAME FROM NVARCHAR(256);
GO
CREATE TYPE dbo.TCATEGORYTITLE FROM NVARCHAR(256);
GO
CREATE TYPE dbo.TCOLOR FROM INT;
GO


CREATE TABLE dbo.Category
(
    Id UNIQUEIDENTIFIER NOT NULL
    , UserName TUSERNAME NOT NULL
    , Title TCATEGORYTITLE NOT NULL
    , Color TCOLOR NOT NULL
), CONSTRAINT CategoryIdPK PRIMARY KEY NONCLUSTERED (Id)
GO

And it does, actually, look promising.

Alas! When you try to modify your Title column type or size (isn't it what you were trying to get ready for), you briefly discover that there is no good way to do it. There is no ALTER TYPE statement. You might thing that dropping a type and creating it again will do the work. Mistake. What you will actually get will be:

Msg 3732, Level 16, State 1, Line 1
Cannot drop type 'dbo.TUSERNAME' because it is being referenced by object 'Category'. There may be other objects that reference this type.

It turns out you need to create a new UDDT, and alter you column to use this new UDDT, then only you can get rid of the old one.

Imperfect, imperfect, imperfect world.

UPD: And yes, the scope of the UDDT is the current database, so if you want to create a temporary table using UDDT, you should create this UDDT in tempdb as well.

GUIDe me

Joel Spolsky in his article Lord Palmerston on Programming wrote:

We live with a hockey stick learning curve: you can learn 90% of what you use day by day with a week of learning. But the other 10% might take you a couple of years catching up. That's where the really experienced programmers will shine over the people who say "whatever you want me to do, I can just pick up the book and learn how to do it."

At one of my previous jobs there was one guy who once asked this: "What would be if some day you get the same GUID generated by NEWID() function?". That would be a disaster, actually. That's how the apocalypse begins, if you ask me. We fired him. Of course, not because of this question.

He was not stupid. Actually, he was smart. He was just like that - knew the basics and could code anything after picking up the book and learning how to do it - in theory. In practice, he might end up with just a couple of lines of code in a whole day - simply because EVERY line of code required him to pick a dozen of books.

Somehow I feel like this digging into ASP.NET. I can explain how it works in two minutes, but I have to answer a millions of questions every time I want to solve a simpliest task. Should I use any type of container to put my controls into? Should I create my custom control or just put a group of separate controls on the page? Should I write JavaScript code calling a Web service to update my page, or just use asp:UpdatePanel control? Does asp:RadioButton trigger a postback when checked? Should I make text bold using the control property or using the new CSS class? May I get rich text editor out-of-box?

To finish with the GUIDs: there is one good reason not to build clustered indexes on GUID fields: since GUIDs are usually generated in non-sequential order, it is very likely to cause a page split when inserting a new data into the table. Well, this one can be answered now by using NEWSEQUENTIALID() instead NEWID(), which creates a GUID that is greater than any GUID previously generated on a specified computer since Windows has started (not to forget, this is a potential security issue). Anyway, the bigger is the index field, the more space it takes on the index pages in B-tree, the less effective is the index, so, generally speaking, you'd be better off GUIDs, still, if you have a choice.

OhRM

Every time I start writing a new application it makes me think how terribly tedious is to write all this CRUD logic running through the tiers and how extremely difficult and expensive (yet magical) is to write the custom ORM solution. Damn it.

I decided to keep things simple this time and use what Fowler calls Transaction script. Still I want some DTO's, at least not to pass the data into methods as separate variables.

So this will be my solution:

• first I will generate Xml schemas from tables
• second, I will use XSD.EXE to generate classes from Xml schemas.

Here is the implementation of this great idea:

List<string> tables = new List<string>();

string connectionString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;

using (var connection = new SqlConnection(connectionString))

{

        var command = connection.CreateCommand();

        command.CommandType = CommandType.Text;

        command.CommandText =

                "SELECT [name] FROM dbo.sysobjects WHERE TYPE = N'U' AND name NOT LIKE N'sys%'";

        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        {

                if (reader.HasRows)

                {

                        while (reader.Read())

                        {

                                tables.Add(reader[0].ToString());

                        }

                }

        }

        reader.Close();

        foreach (var tableName in tables)

        {

                command.CommandText = String.Format(

                        @"SELECT * FROM {0} FOR XML AUTO, ELEMENTS, XMLSCHEMA('{0}Schema')", tableName);

                XmlReader xmlReader = command.ExecuteXmlReader();

                var stream = File.Create(Path.ChangeExtension(tableName, "xsd"));

                using (StreamWriter sw = new StreamWriter(stream))

                {

                        while (xmlReader.Read())

                        {

                                sw.WriteLine(xmlReader.ReadOuterXml());

                        }

                }

                xmlReader.Close();

        }

}

StringBuilder sb = new StringBuilder();

foreach (var tableName in tables)

{

        sb.AppendLine(String.Format("xsd.exe {0}.xsd /c SqlTypes.xsd", tableName));

}

File.WriteAllText("GenerateClasses.bat", sb.ToString());

UserId VS UserName

In my Notes database, I want every record to belong to some particular user (it is not Wikipedia, after all).

In the ApplicationServices database, so kindly created for me by the aspnet_regsql tool, there is a table dbo.aspnet_Users, which keeps all the user-related info.

So, when creating my dbo.Note table, I will store, together with all the note-related info, some user identity, to be able to distinguish which note actually belongs to the current user.

The question is: What should I use - UserId or UserName?

Both are unique, UserId by virtue of being a GUID (and also, a primary key), and UserName by virtue of the unique clustered index (technically, it is unique within the application).

So, pro and contra:

Pro:

• The UserName, being used in every query, will be the perfect candidate for the clustered index, which will guarantee the user-related records are fetched very quickly.

Contra:

• I am going to have a serious problem once I decide it is possible for two users to have the same user name. Somehow I really doubt it.
• I might have a problem if I decide I would like to re-use user names, and I don't properly remove the user data left from the previous owner.

So, I go for UserName with the clustered index built on this column.

 

Services

So, my first service is going to be Notes.

The idea is simple. You have the fridge to put the sticky notes on, and it works perfectly fine, except you want your fridge to be with you all the time.

This is what it is about: you can create Notes, which belong to Categories, and have Tags assigned.
Then you can search quickly by tags/ slowly by content (ok, maybe).

At work, at home, in a public internet cafe, in your impressively big mobile phone there will be your useful stuff with you. This is going to be more loyal than your wife.

And just for the record, this is:

CLOUD CLOUD CLOUD CLOUD CLOUD

The beginning

Hi,

This is the story of my new web project - romama.com.

What suddenly turned out to be extremely difficult to beging with, was a good name (assuming this name is!), and apparently not because of lack of imagination, but because of army of evil folks having already bought, in their naive attempt to get rich, ALL the domain names, even those you could hardly imagine people to come up with.

So, romama be it, brief, easy-to-remember-not-that-easy-to-forget, and innocent name.

I expect a great commercial success, because my motto is happened to be

NEAT SIMPLE WORKIN'

I can beat Google and Facebook at the drop of a dime.

Well, this is not really about THAT. This is about learning things and having fun. Fun starts here.