Humanized dates in C#

I've been looking for a way to make dates in the Forum sections of the site more user-friendly. Did some googling on how to accomplish this in either SQL or C#, but somehow that came up completely blank.

I did manage to find some scripts for some stuff I never heard about, and 1 javascript file. Just my luck. I seriously dislike javascript. Maxxim from the wrox forum will most likely be chuckling now, since he's been advising me to brush up my skills in Javascript ;)

So here's the translation in C#. Since I'm not sure about the copyright notice, I'll just slap a copy of the original in:


/**
* C# translation: Copyright (c) 2007 Peter Schmitz
* http://entropia-online.blogspot.com
*
* Original Javascript code: Copyright (c) 2007 Blake Householder
* http://www.blake8086.com/
*
* Permission is hereby granted, free of charge, to any person
* obtaining a copy of this software and associated documentation
* files (the "Software"), to deal in the Software without
* restriction, including without limitation the rights to use, copy,
* modify, merge, publish, distribute, sublicense, and/or sell copies
* of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be
* included in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
* NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS
* BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
* ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
* CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
* SOFTWARE.
*
*
*/

public string HumanizeDate(DateTime date)
{
DateTime dateNow = DateTime.Now;

// Determine the start of the date's week
DateTime startOfWeek = (date.Date - TimeSpan.FromDays((double)date.DayOfWeek));

// We'll use this variable for monthly comparison
int intMonthCompare = ((date.Year * 12 + date.Month) - (dateNow.Year * 12 + dateNow.Month));

// Do the monthly comparison first, as that's the biggest possible way to group
if (intMonthCompare > 1)
{
return "Beyond next month";
}

if (intMonthCompare < -1)
{
return "Older";
}

if (intMonthCompare == 1)
{
return "Next month";
}

if (intMonthCompare == -1)
{
return "Last month";
}

// Now do the same, but in weeks
TimeSpan ts = (startOfWeek - dateNow.Date);
if (ts.Days > 28)
{
return "Five weeks from now";
}

if (ts.Days > 21)
{
return "Four weeks from now";
}

if (ts.Days > 14)
{
return "Three weeks from now";
}

if (ts.Days > 7)
{
return "Two weeks from now";
}

if (ts.Days > 0)
{
return "Next week";
}

if (ts.Days < -35)
{
return "Five weeks ago";
}

if (ts.Days < -28)
{
return "Four weeks ago";
}

if (ts.Days < -21)
{
return "Three weeks ago";
}

if (ts.Days < -14)
{
return "Two weeks ago";
}

if (ts.Days < -7)
{
return "Last week";
}

// Nothing found so far. Let's see if it's tomorrow, today, or yesterday
ts = date.Date - dateNow.Date;

if (ts.Days == 1)
{
return "Tomorrow";
}

if (ts.Days == 0)
{
return "Today";
}

if (ts.Days == -1)
{
return "Yesterday";
}

// Still nothing? Must be a different day in this week then
return date.DayOfWeek.ToString();
}

Read more!

A strange error...

It's been a while since my last post. That is mostly due to trying to actually get the website as a whole working.

We planned on going beta last weekend, but illness and distractions (in the form of LOTRO) put a stop to that.

To top things off, I've been worrying about the forums. Our potential members are used to the vBulletin forums, and the current TBH forums are nothing even remotely like it. Heck, there's no subforums, and not even the ability to send one another private messages. In itself, that shouldn't be too hard to implement, but one also should take into account that just building it is not going to cut it.


The end-user will also demand to actually use the functionality, and vBulletin has all that conveniently located.

So for now, I'm undecided where to go. I downloaded a free forum coded in C# yesterday, courtesy of Frans Bouma, that I will give our beta users to test. I also can try just buying vBulleting, and then writing code to integrate the users from my site into the forum databases (which is mySql). As a third alternative, I can try and code things myself, and see if other TBH users are interested as well in adding functionality.

Time will tell.

In the meantime, I came across a particular error today:

"Error 110 It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS. C:\...\EntropiaOnline\forumsbck\web.config 56 "

Never seen it before, but what happened is that yesterday I changed some code. In order to ensure I'd not lose anything, before I overwrote the code on the actual site, I grabbed the code I was going to replace, and put it in a local subfolder of my website.

Due to there being a web.config file in it as well, I started getting the error. The solution was to remove the newly created folder from the project-folder. The error is caused by having duplicate web.configs in subfolders, that are not "registered" in Visual Studio (by registered, I mean you did not add the folder itself to the project).

Read more!

Reusing command parameters

A while ago, at that hobby I practise during the day (some call it work. I resent that. Work is what I do when I come home), I was writing a piece of VB.NET code (I know... I know...) that would fire off a bunch of stored procedures, all with the exact same parameters.

So I wondered if I really had to define the parameters once for every time I called the procedures, or whether I could actually just change the CommandText.

I created a little test app, and went away. Today, I rewrote the code to C#, and decided to put it up here, in order to show that reusing command parameters is perfectly legal in .NET (and to me, it actually sounds logical too, as it prevents the coder from having to rewrite the same code over and over again).


I created a page with one single button, and two labels (I left their names unchanged, as I was doing this during worktime, and I wanted to have the test take as little time as possible.

I added the code, with a few comments thrown in for clarity, and used the labels to post the ID's of the first records I got back from my stored procedures.

Next I fired off the code, while keeping an eye on my profiler, and sure enough, both the stored procedures were executed. I then changed the code a little and increased the value for the second stored procedure, in order to see if that would still work. It did:



Click here to download the sample project and test it for yourself. The project assumes you have the Adventureworks database installed. you will have to create the two stored procedures usp_Test1, and usp_Test2 as well (they're also included in the ZIP file).

Read more!

I won something...

After I finished up my CRUD script, I submitted it to a competition I stumbled upon while reading up at www.sql-server-performance.com. Mostly in the hope of trying to gain some attention for the script. Besides, when do I ever win anything?

Well, this time I did! I just received an E-mail stating I won a full-conference pass to the PASS summit in Denver, Colorado.

I won't be able to attend, but I definitely am proud at the moment :)

Read more!

CreateUserWizard - SendingMail event

After following the examples in the book, I figured I wanted to add some extra functionality to the Membership and UserProfiling section.

While it allows your users to signup smoothly and simply, using a CreateUserWizard, I wanted to add an option where my site sends the user a link with a GUID embedded in it, in order to verify that the user didn't only fill in a properly formatted E-mail address, but actually does own the mail address that was entered.

I went off on my merry way, using a code snippet from my local installation of MSDN as a basis:

e.Message.Body.Replace("<%PasswordQuestion%>", Createuserwizard1.Question);
e.Message.Body.Replace("<%PasswordAnswer%>", Createuserwizard1.Answer);

So my code became:

protected void SendMail(object sender, MailMessageEventArgs e)
{
// Generate a GUID
string guidResult = System.Guid.NewGuid().ToString();

// Code to paste into a URL, which I cannot paste cause Blogger throws a fit.

e.Message.IsBodyHtml = true;
e.Message.Body.Replace("<%Link%>", url);
}

After running the code, nothing happened. I set a breakpoint and tried again. Nothing. I commented out several options that the book carried as extra steps that I thought might interfere. Still nothing.

I then tried hooking the UserCreated event to the SendMail event that I defined, but ran into trouble when trying to create EmailEventargs to pass as a parameter.

And luckily, then I googled on "CreateUserWizard MailMessageEventArgs", and came across Bryant's blog.

The last line of code now reads:

e.Message.Body = e.Message.Body.Replace("<%Link%>", url);

And lo and behold, problem solved, and I find a neat link included in my mail :)

Bryant, I owe you a beer!

And to those still unconvinced by the Internet, I predict that some day it will be a big hit!

Read more!

E-mailing from ASP.NET

I've been working on a website for a while now. As a base, I use the book ASP.NET 2.0 Website Programming: Problem - Design - Solution (Programmer to Programmer). Now using that, you actually add code to have E-mail sent from within your application using SMTP.

However, I couldn't get it to work. My E-mail address seemed valid enough, as did the code in my web.config:




Since I was planning on adding a new feature to the existing code to E-mail a new member a link they have to click before having their account activated, it seemed like a good idea to first sort out the whole mail thing.

I fiddled with a few things, but kept getting a: "5.7.1 Unable to relay for " error.


The solution is laughably simple (thankfully). Go to Internet Information Services (under Administrative Tools), expand your IIS server, and select right-click the Default virtual SMTP server. Select Properties, and go to the Access tab.

Under the "Relay restrictions" section, click the "relay" button. There, ensure the "Only the list below" option is selected, and press the "Add" button.

Under "Single computer", fill in 127.0.0.1, and click OK. That's it!

For security's sake, I chose to uncheck the checkbox named "Allow all computers which succesfully authenticate to relay".

Read more!

A free script to create CRUD procedures.

Recently, both at my job, as well as while working on the site, I found myself having to write countless CRUD stored procedures in order to facilitate retrieving and storing data in my tables. The majority of these reflected an exact replication of the database table model (i.e. updating all columns), and I found writing those stored procedures to be a real burden. It was boring, repetitive and error prone work (especially the UPDATE procedures, where one has to replicate columns three times (once as input variables (which includes data types), once as columnnames, and once again as variables (but this time without the columnnames).

Being the lazy guy I am, I decided I would automate the matter, and write a script to automatically create the procedures for me. Before I started, I decided I would make a few starting assumptions:

1) I wanted the SELECT procedure to be able to accept either an ID-number or a Name (assuming that like me, you called the column something involving '%Name') for an individual row, or neither, in which case the whole table should be returned.
2) I wanted to be able to have the DELETE procedures to be optional. I achieved this by using a bit-flag and IF statements to see if it ought to be created or not.
3) I wanted the user to be able to specify certain columns to exclude. In the database I'm working on for the site, there are numerous audit columns to keep track of when data is entered or modified, and by who. Naturally, this is not required in all of the procedures, so I wanted a mechanism for the end-user to include or exclude these columns.


The result of my work is the script below. I've briefly tested the generated procedures against my own database, and there, they work like a charm. Perhaps others who run more specialised or customised databases will be able to come up with unexpected errors etc. If so, please do let me know.

Of course, the script will not cover 100% of anybody's specific wishes. In fact, I'm quite sure that some of the procedures will need to be customised to suit your needs, but you will find this script will enable you to start with at least some foundation in place.

I'm releasing this script under the Creative Commons Attribution Non-Commercial No Derivatives license, which means you are free to download and share the script, provided you mention and link back to me. However, you are not allowed to change or commercially use the script. I might change this to a less restrictive license in the future, however for now, I want to be able to evaluate comments etc first. This is the first time I ever released anything under a license, so any comments regarding that are more than welcome.

A list of things I have figured I could enhance the script with:

1) Add error handling to the stored procedures.
2) Add support for schemas in SQL Server 2005.
3) Add paging support to the select procedures, by adding two additional parameters.

Also, I might later on decide to automatically generate .NET custom classes based on the exact database tables.

Click here to download the script.

One last comment, should you decide to alter the generated stored procedures to suit your specific needs, do remember to rename the new procedure. The script will automatically drop its standard procedures, in order to always reflect the latest table schemas.





This work is licensed under a
Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 License.

Read more!

Hierarchical data with Common Table Expressions

Today, I finally managed to crack a problem that's been bothering me for some time. Some weeks ago I started working on the database schema that the site will be using, and the Entropia auction system gave me some trouble.

You see, the auction in the game is built up hierarchically, and I eventually want to show individual items in the database under the auction section they belong to (as with a lot of items, it's unclear exacty where to find them).

That in itself was easy enough, but not if you want to store the data in third normal form, but at the same time want to use some form of a cookie-trail, so the user will know exactly where to go. (With cookie-trail I mean something along the lines of "Items \ Tools \ Misc Tools"). I chose to store my data in a table with an AuctionCategoryID, AuctionCategoryName, and an AuctionCategoryParentID, the latter of which represents a SELF-JOIN.

(The figure above doesn't actually represent my own table. I chose to give the very bottom nodes of the tree a ParentID of -1, and did not define -1 as an AuctionCategoryID. Hence, creating a Foreign Key is just not possible. The figure is just for clarification).

I first tried meddling with code a bit myself, but found that my code just didn't do the trick. Sure, I could hard-code a number of self-joins, but what if suddenly more categories were added? I'd have to alter my function. That was definitely not an option.

I tried a few more things, but found my code was not robust enough, and that there were sorting troubles (among other things).

I Googled a bit, and initially ended up with a solution like this (Joe Celko describes a similar method in his "SQL for Smarties"). Hardly elegant, and hardly dynamic (although it would be possible, of course, to ensure there would be a difference of a few numbers between each of the individual "nodes". However, that still potentially means I could run out of space, and would then have to manually alter stuff again. Not an option if you ask me).

Enter the recursive Common Table Expression. BOL doesn't seem very helpful, but hopefully the code below will make things easier to see.

The first query within the Common Table Expression defines the root level. As you see, the -1 value I mentioned above is hard-coded. The UNION ALL is the key to ensuring the self-join will work for an indefinte number of child nodes. The second query just fills in those child nodes.

Note that it is possible to potentially create an infinite loop, though CTEs also offer a way to cap this.

The code:

WITH AuctionPath -- The name of the CTE
(
[Name]
, [AuctionCategoryID]
, [Path]
, [Level]
)
AS
(
SELECT
AuctionCategoryName -- Name
, AuctionCategoryID -- AuctionCategoryID
, CONVERT(varchar(255), AuctionCategoryName) -- Path
, 1 -- Level
FROM
AuctionCategories
WHERE
AuctionCategoryParentID = -1 -- Only the parent nodes here
UNION ALL
SELECT
AuctionCategoryName -- Name
, AuctionCategories.AuctionCategoryID -- AuctionCategoryID
, CONVERT(varchar(255), Path + ' \ ' + AuctionCategoryName) -- Path
, [Level] + 1 -- Level
FROM
AuctionCategories
INNER JOIN AuctionPath ON AuctionCategories.AuctionCategoryParentID = AuctionPath.[AuctionCategoryID]
)
SELECT
[Name]
, [AuctionCategoryID]
, [Path]
, [Level]
FROM
AuctionPath
ORDER BY AuctionCategoryID
OPTION (MAXRECURSION 5); -- The maximum amount of recursions

Some things of note. There are certain conditions to recursive CTEs. From BOL:

• The number of columns in the anchor and recursive members must be the same.
• The data type of a column in the recursive member must be the same as the
  data type of the corresponding column in the anchor member.

In the quoted text, anchor is what I have refered to as root node, and recursive member is a child node.

I hope this helps a little if you're facing a situation where you need to do hierarchies. Once you manage to get the trick, there's nothing to it, really :)

Many thanks to both Kalman Toth and russellb over at the sqlmag forums.

Read more!

Roman numerals

If you play Entropia Universe (formerly Project Entropia), you're probably aware that mining depositsizes have two distinctive identifiers. The first being the name of the claimsize (i.e. tiny, very poor, poor, all the way to Immense). However, those terms do not clearly indicate which of the two is larger.

In the case of very poor vs. poor, it's obvious, but is Significant bigger or smaller than Substantial? In order to avoid that kind of confusion, MindArk also decided to give claims a Roman numeral indication.

I order to have the database reflect these numerals, I decided to create a function that will translate our number system into Roman numerals. It works surprisingly easy, actually. The function takes an input parameter, which is the number to be translated.


It then determines the regular numbers, the tens, the hundreds, and the thousands, and translates those into their Roman counterparts using a case statement. When it encounters a 4 or a 9 (which, obviously, are the special numbers because they are not a simple repetitive sequence of characters), it will translate those as well.

CREATE FUNCTION [dbo].[fn_ToRoman]
(
@InputNumber INT
)
RETURNS VARCHAR(16)
AS
BEGIN
DECLARE
@Singles INT
, @Tens INT
, @Hundreds INT
, @Thousands INT

SELECT
@Singles = @InputNumber%10
, @Tens = @InputNumber%100/10
, @Hundreds = @InputNumber%1000/100
, @Thousands = @InputNumber%10000/1000

RETURN
(
SELECT
REPLICATE('M', @Thousands) +
CASE
WHEN @Hundreds THEN REPLICATE('C', @Hundreds) WHEN @Hundreds = 4 THEN 'CD' WHEN @Hundreds = 9 THEN 'CM' WHEN @Hundreds >= 5 THEN 'D' +
REPLICATE('C', @Hundreds - 5)
ELSE ''
END +
CASE
WHEN @Tens THEN REPLICATE('X', @Tens) WHEN @Tens = 4 THEN 'XL' WHEN @Tens = 9 THEN 'XC' WHEN @Tens >= 5 THEN 'L' + REPLICATE('X', @Tens - 5)
ELSE ''
END +
CASE
WHEN @Singles THEN REPLICATE('I',@Singles) WHEN @Singles = 4 THEN 'IV' WHEN @Singles = 9 THEN 'IX' WHEN @Singles >=5 THEN 'V' + REPLICATE('I',@Singles-5)
ELSE ''
END
)
END

Read more!

Yes! Code regions in SQL :)

Currently I'm working on a long long long SQL script that automatically writes CRUD (Create, Read, Update, Delete) procedures based on a table's definition. If you are like me, you will find that you group certain code together.

In Visual Studio, you can conveniently wrap #region tags around the code, so you can just collapse the region when you know certain stuff works, and doesn't need to be edited anymore. Not so in SQL Query Analyzer (or SQL Server Management Studio for that matter).

SQL Visualizer does offer that option though. You start a region with a -- REGION comment, and end with -- END REGION.
Because it works with comment blocks, your code will work anywhere. Except within this tool, you will be able to now use regions:



I've used other SQL tools before, and quickly found myself abandoning them due to being, well frankly, crap. I'll have to work with this one a bit more before I can actually judge it properly, but regions in SQL code are definitely something I miss in the Microsoft environment.

Update 08/27/2007: I've decided to not use SQL Analyzer a lot, unless it's for developing large scripts like the CRUD script I mentioned before. Reason is that it's not possible to copy/paste output messages (which, if you're building dynamic SQL scripts, is kind of neccessary to test).

Read more!

SSIS - DataReader issue

Just a quick note, unrelated to anything to do with the actual website.

Today, at work, I was working on a task that involves moving data from a production database to a Historic database. The production environment has been misused at times in the past, and as a result, there's numerous backups of tables residing in the database.

Due to regulations, just dropping the tables or wiping data is not an option. Hence, we chose to move it to the historic database. Now to accomplish this, I selected SSIS as our prefered tool. Stupidly enough, I chose to use ADO.NET as a data connection. The fun started when I was creating the actual "Data Flow" objects. I must have created a dozen of them when suddenly I was confronted with an error that SSIS was unable to translate from unicode to non-unicode data.

Huh?


I checked my CREATE TABLE scripts, and sure enough, the data types in both source and destination were set to char. I tried altering the Source's External columns, which seemed to work from the colum's properties, but actually didn't if you would check the datatype coming into the Source. I tried fiddling with the Source's Output column, but got an error message that I was not allowed to. I tried explicitly casting the column as char in the Source's SELECT statement. No luck.

A colleague came over and suggested using a Dereived Column object. Again, that didn't work. I ended up Googling, and came to an MSDN link. There, it stated somewhere halfway the page:

"One issue when using ADO.NET is that there is no ADO.NET data type corresponding to VARCHAR (non-Unicode character) in relational databases. Therefore, ADO.NET data sources output all character data as Unicode. This can cause problems with simple components that use ADO.NET sources and destinations that expect single-byte character types. You can work around this by using a Data Conversion transformation."

I tested this, and using a Data Conversion did indeed work. But what a lot of extra work to do something relatively simple. I decided to just switch all connections to OLEDB.

Conclusion: When dealing with non-unicode char columns, OLEDB might be the better choice.

Read more!

Moan: Commenting while programming

Att: Microsoft, Oracle, Sun
CC: WWC, Google (they're in on anything, so why not CC them? At the very least they'll get a chuckle out of it)

Subject: Programming comments

Dear Sirs/Madams,

If you can find some time in your undoubtedly busy schedules, would it be possible for you to discuss a universal combination of characters to note a line or block of text within code as comment, please?


As a programmer, I love commenting my code. It makes it easy for me to identify what bits of code do, to indicate why I used a certain approach, and my peers will get the same benefits. All in all, comments are our friends.

However, I have never understood the reason for having numerous ways to define comments. For instance, see the following snippets:

Visual Basic:
' This is a comment. Start the line with an apostrophe.

C# / Java:
// This is a comment.

SQL / C# / Java / Oracle:
/* This is a comment block */

SQ: / Oracle:
-- This is a comment

HTML:
<% -- This is a comment -- %>

Etc.

Could it be arranged so that there is one standard approach that works in every language? It would make things so much easier for those of us who regularly switch between languages/flavours.

Kthanks.

My next moan will demand we get the exact same syntax, too ;)

Read more!

Preventing accidental data deletion in SQL Server

After a few hours of working on the proper table definition for your base table, and filling it with data, you sit back, and decide to do some minor maintenance on a few other tables. You truncate a table, and seconds later realise to your horror that you just purged the table you spent quite some time on filling.

Sound familiar?

It recently happened to me, and I decided to put some mechanisms in place to prevent accidental deletion of data. Of course the best way to prevent accidental deletion is by making sure you are always 100% awake, and you simply do not make errors. Unfortunately that is unrealistic, so I have a few tips to help out.


As a very basic and rather obvious tip, I'll advise you to just make a database backup every time you spent a lot of time on filling in basetables. Now for some added technical tricks:

1) To prevent accidental deletion of data, I decided to use INSTEAD OF triggers. BOL again:

INSTEAD OF
Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL triggers.

At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, you can define views on views where each view has its own INSTEAD OF trigger.

INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Specifies the data modification statements that activate the DML trigger when it is tried against this table or view. At least one option must be specified. Any combination of these options in any order is allowed in the trigger definition.

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.

The definition of my trigger is similar to this:

CREATE TRIGGER DeleteTable_1 ON Table_1
INSTEAD OF DELETE
AS
BEGIN
IF @@rowcount > 0
BEGIN
RAISERROR( 'Rows in Table_1 cannot be deleted!', 16, 2 )
ROLLBACK
END
END
GO

When I now try to delete rows (using DELETE FROM Table_1), I will see the following errors in my Query result:

Msg 50000, Level 16, State 2, Procedure DeleteTable_1, Line 7
Rows in Table_1 cannot be deleted!
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Unfortunately, that does NOT prevent the table from being Truncated.

2) To avoid accidently dropping a table, I create views over them and use the option WITH SCHEMABINDING. From Bol (my emphasis):

SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Microsoft SQL Server 2005 Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

SCHEMABINDING cannot be specified if the view contains alias data type columns."

The first bit I emphasised clearly shows that using views in this way is a neat little feature to prevent accidental drops of a table.

The second quote is IMHO also important, as it shows that clearly the table should be "final". If you later on decide that the table in fact needs extra (or less) columns, the VIEW will actually make your life harder.

As an extra tip, when you create a view to use WITH SCHEMABINDING, always use the fully qualified name of the table (i.e. dbo.Salaries instead of just Salaries). Also, you cannot use *, but instead you will have to define all the columns in the query statement.

Nice, but still no protection against a TRUNCATE.

3) In fact, the only way to prevent TRUNCATE from executing on your table is to ensure there is a Foreign Key relationship to the table. If you are (like me) still working on the tables that will eventually link to the base table, you can always create a dummy table that references your base table:

CREATE TABLE [dbo].[Table_2]
(
[TableID] [int] NULL,
[Table_1ID] [int] NULL
)
ON [PRIMARY]

GO
ALTER TABLE [dbo].[Table_2]
WITH CHECK
ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([Table_1ID])
REFERENCES [dbo].[Table_1] ([TableID])
GO
ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]

Now when you try to TRUNCATE Table_1, you will receive the following error:

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'TABLE_1' because it is being referenced by a FOREIGN KEY constraint.

Well, that concludes this post. I hope you will find it helpful in preventing you from accidentally destroying some of your hard work. Of course all comments are welcome :)

Read more!

Welcome!

Hi there,

We'd like to take the time to welcome you to the official developer blog of http://www.entropiaonline.com/. This blog will be mostly used to describe some pieces of code that we found to be tremendously helpful while developing the site, discussion of programming techniques, and as a way for us to get feedback from our users as to what they would like us to work on next.

As some initial information, the techniques we will be using include ASP.NET (2.0), SQL Server 2005, and C#. Once we feel the content of the site is to our liking, we will most likely also look into AJAX to enhance the user experience.

Thanks for stopping by!

Read more!