Find Tables With Foreign Key Constraint in Database

This is very long query. Optionally, we can limit the query to return results for one or more than one table.

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')

ASP.NET HttpPostedFile Image Resize

Many websites have forums and other features where members can upload an avatar image or photo of themselves. One of the most common problems with this is that people upload images that are too big.

You can work around this by specifying one parameter in your tags, a width property. This will automatically constrain the displayed image to your required size. However, it does not change the size of the uploaded image file. To do that, you must resize the image the user has uploaded before saving it.

This handy ImageResizer class does just that.

using System;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Web;

namespace PAB.ImageResizer
{
///

/// A class to resize uploaded images
///

public class ImageResizer
{
private int _imgQuality;
private int _maxHeight;
private int _maxWidth;
private PAB.ImageResizer.ImageFormat _outputFormat;

///

/// Initializes a new instance of the class.
///

public ImageResizer()
{
this._maxWidth = 800;
this._maxHeight = 800;
this._imgQuality = 80;
this._outputFormat = PAB.ImageResizer.ImageFormat.Jpeg;
}

///

/// Initializes a new instance of the class.
///

/// Maximum Width .
/// Maximum Height
/// The image quality.
public ImageResizer(int maxWidth, int maxHeight, int imgQuality)
{
this._maxWidth = 800;
this._maxHeight = 800;
this._imgQuality = 80;
this._outputFormat = PAB.ImageResizer.ImageFormat.Jpeg;
this._maxHeight = maxHeight;
this._maxWidth = maxWidth;
this._imgQuality = imgQuality;
}

///

/// Resizes the specified source image.
///

/// The source image.
///
internal System.Drawing.Image Resize(System.Drawing.Image sourceImage)
{
System.Drawing.Image source = new Bitmap(sourceImage);
int width = sourceImage.Width;
int height = sourceImage.Height;
if (width > this.MaxWidth)
{
height = (height * this.MaxWidth) / width;
width = this.MaxWidth;
}
if (height > this.MaxHeight)
{
width = (width * this.MaxHeight) / height;
height = this.MaxHeight;
}
if ((width != sourceImage.Width) || (height != sourceImage.Height))
{
source = new Bitmap(source, width, height);
}
return source;
}

///

/// Resizes by specified image path.
///

/// The image path.
public void Resize(string imagePath)
{
this.Resize(imagePath, imagePath);
}

///

/// Resizes the specified posted file.
///

/// The posted file.
///
public byte[] Resize(HttpPostedFile postedFile)
{
if (postedFile.ContentLength == 0)
{
return new byte[0];
}
System.Drawing.Image sourceImage = System.Drawing.Image.FromStream(postedFile.InputStream);
System.Drawing.Image image2 = this.Resize(sourceImage);
sourceImage.Dispose();
EncoderParameters encoderParams = new EncoderParameters(1);
encoderParams.Param[0] = new EncoderParameter(Encoder.Quality, (long) this.ImgQuality);
ImageCodecInfo encoder = ImageCodecInfo.GetImageEncoders()[(int) this.OutputFormat];
MemoryStream stream = new MemoryStream();
image2.Save(stream, encoder, encoderParams);
byte[] buffer = stream.GetBuffer();
image2.Dispose();
stream.Close();
return buffer;
}

///

/// Resizes the specified original image path.
///

/// The original image path.
/// The resized image path.
public void Resize(string originalImagePath, string resizedImagePath)
{
System.Drawing.Image image;
try
{
image = System.Drawing.Image.FromFile(originalImagePath);
}
catch
{
if (!File.Exists(originalImagePath))
{
throw new Exception("File " + originalImagePath + " doesn't exist; resize failed.");
}
throw new Exception("File " + originalImagePath + " is not a valid image file or No read permission on the file; resize failed.");
}
System.Drawing.Image image2 = this.Resize(image);
image.Dispose();
EncoderParameters encoderParams = new EncoderParameters(1);
encoderParams.Param[0] = new EncoderParameter(Encoder.Quality, (long) this.ImgQuality);
ImageCodecInfo encoder = ImageCodecInfo.GetImageEncoders()[(int) this.OutputFormat];
try
{
image2.Save(resizedImagePath, encoder, encoderParams);
}
catch (Exception exception)
{
string userName;
try
{
userName = Environment.UserName;
}
catch
{
userName = null;
}
if (String.IsNullOrEmpty( userName))
{
userName = "'ASPNET' or 'Network Service'";
}
userName = userName + " windows account";
throw new Exception("Could not save resized image to " + resizedImagePath + "; resize failed.\r\n" + exception.Message + "\nTry the following:\r\n1. Ensure that " + resizedImagePath + " is a valid file path.\r\n2. Ensure that the file " + resizedImagePath + " is not already being used by another process.\r\n3. Ensure that " + userName + " has write/modify permission on " + resizedImagePath + " file.\r\n");
}
finally
{
image2.Dispose();
}
}

///

/// Resizes the specified posted file.
///

/// The posted file.
/// The resized image path.
public void Resize(HttpPostedFile postedFile, string resizedImagePath)
{
postedFile.SaveAs(resizedImagePath);
this.Resize(resizedImagePath);
}

public int ImgQuality
{
get
{
return this._imgQuality;
}
set
{
if ((value < 2) || (value > 100))
{
this._imgQuality = 80;
}
else
{
this._imgQuality = value;
}
}
}

///

/// Gets or sets the max height.
///

/// The max height.
public int MaxHeight
{
get
{
return this._maxHeight;
}
set
{
this._maxHeight = value;
}
}


///

/// Gets or sets the max width.
///

/// The max width.
public int MaxWidth
{
get
{
return this._maxWidth;
}
set
{
this._maxWidth = value;
}
}

///

/// Gets or sets the output format.
///

/// The output format.
public PAB.ImageResizer.ImageFormat OutputFormat
{
get
{
return this._outputFormat;
}
set
{
this._outputFormat = value;
}
}
}
}


To use this, you can apply it's Resize method directly to the HttpPostedFile object:

if (FileUpload1.HasFile)
{
PAB.ImageResizer.ImageResizer resizer = new PAB.ImageResizer.ImageResizer();
resizer.MaxHeight = int.Parse(txtMaxHeight.Text);
resizer.MaxWidth = int.Parse(txtMaxWidth.Text);
resizer.ImgQuality = 100;
resizer.OutputFormat = PAB.ImageResizer.ImageFormat.Gif;
byte[] bytes = resizer.Resize(FileUpload1.PostedFile);
File.WriteAllBytes(Server.MapPath("~/test.gif"), bytes);
Response.Redirect("Default.aspx",true);
}

Then you simply save the new image file. You can even specify the Image Format you want - Bmp, Gif, Jpeg, or Png.

I have put in several overloads of the Resize method that allow you to load an image from a path and resize it, along with resizing from an HttpPostedFile object directly after an upload.

The downloadable Visual Studio 2010 Solution has a sample ASP.NET project with a web page that will allow you to upload an image, have it resized, and it will then display the saved image on the page.

Update from a Select

I know many people won't find this new, but it's the first time I've had to do it today.
I needed to update one table, from another table, and here's how I did it: -

UPDATE C

SET C.Phone = O.[Tel No],

C.Fax = O.[Fax]

FROM Outputs O

JOIN Contacts C

ON C.MemberId = O.MemberId

Using Coordinated Universal Time (UTC) to Store Date/Time Values

Introduction

Many data-driven applications have date/time values that are automatically populated with the current date and time. For example, in an online messageboard, whenever a user makes a post the current date and time is recorded and saved with that post. Another example is in applications that track created on or last modified dates. For these types of applications, whenever a new record is created or an existing one modified, the current date and time are written into these respective fields.

Such auto-entered timestamp values can be stored in the database in either server time or Coordinated Universal Time (UTC). If you use SQL Server's built-in getdate() function or DateTime.Nowproperty to determine the current time, then you are using server time and saving the date and time respective to the web or database server's time zone settings. Alternatively, if you use SQL Server's built-in getutcdate() function or DateTime.UtcNow property then you are storing the values in UTC.

There are a number of advantages in storing such date/time values in UTC rather than server time. In this article we will explore those advantages as well as look at a simple sample application that illustrates how to save dates in UTC and still display them relative to the time zone's offset.

What is UTC?

UTC, or Coordinated Universal Time, is the standard international time that all time zones are expressed as offsets of. UTC does not get adjusted for daylight savings. To compute local time from UTC, simply add the time zone offset and then add an additional hour if daylight savings time is in effect.

As I write this sentence, the time in UTC is Friday, August 3, 2007 18:18:36. California has a UTC offset of -8 and is in daylight savings time. Therefore my local time is Friday, August 3, 2007 11:18:36. To compute the local time I simply subtracted eight hours then added an hour back in for daylights savings.

Why and When You Should Use UTC

UTC should be used in situations when date/time values are automatically entered by the system to record when a record was added or last updated. For example, in an eCommerce website, when an order was placed you'd want to record the date and time the order was made.

The primary advantage of storing date/time values in UTC is that it makes the data transportable. To see what I mean, imagine that following scenario: you have an eCommerce website that is being hosted in a web server located in the Pacific time zone (UTC -8) and this application stores the date and time orders were placed in server time. Say a user, Bob, makes an order on August 1, 2007 at 9:00 AM UTC -8. After many months of phenomenal growth, you decide to switch to a larger web hosting company, one on the east coast where the time zone is UTC -5. Since the date/time is stored in server time, Bob's previous order still shows that it was made on August 1 2007 at 9:00 AM. But since we are now in UTC -5, it is as if Bob's order was made three hours earlier than it really was (since when it was 9:00 AM on August 1, 2007 in the west coast it was really 12:00 noon on the east coast).

One way around this, you might contend, is to execute a SQL query that adds three hours to the order date for all records in the table. Something like:

UPDATE Orders SET    OrderDate = DATEADD(hh, 3, OrderDate)

And such an approach would suffice... for this situation. But imagine that you moved to a web hosting company situated in the US state of Arizona, where daylight savings is not observed. Eep. Now you would have to write a more complex UPDATE statement that adjusted the hours based on whether the order date fell within daylight savings. Ick.

Another pitfall of server time is if you have multiple servers in multiple time zones. Now the values for each server's date/time fields is relative to that server's geographical location. What UTC buys you is a single reference point.

Other Challenges of Date/Time Values

There are numerous other date/time-related challenges that I am going to ignore at this point. The thrust of this article is to highlight UTC, how to use it in your data-driven applications for auto-generated timestamp-like date/time values, and its advantages in that arena.

Other challenges of date/time values include displaying dates relative to one's time zone and daylight savings. This adds some complexity as you must know the user's time zone offset and whether they are in a location that observes daylight savings and whether the date/time value being displayed falls within the boundaries of daylight savings. But UTC is still a better format to save dates than server time because it makes this complication a bit simpler. For example, if I know a user's time zone offset is UTC -4 and I am storing date/time values in UTC, then I can simply subtract four hours to get the correct non-daylight savings time. If I am using server time, then I have to determine the difference between my server time and the users time zone and determine whether the server time is in daylight savings time and if the user's time zone is observing daylight savings time. Double ick.

Getting the Current Time in UTC

Both the .NET Framework and Microsoft SQL Server provide functions to return the current time in UTC. In the .NET Framework, use the DateTime.UtcNow property, like so:

' VB (use instead of DateTime.Now) Dim currentTime As DateTime = DateTime.UtcNow // C# (use instead of DateTime.Now) DateTime currentTime = DateTime.UtcNow;

For SQL Server, use the getutcdate() function.

-- MS SQL (use instead of GETDATE()) DECLARE @currentTime datetime SET @currentTime = GETUTCDATE()

Converting from UTC Time to Server Time

Given a time in UTC, the .NET Framework makes it easy to convert it to server time. Just use the DateTime class's ToLocalTime() method, like so:

' VB Dim currentServerTime As DateTime = DateTime.UtcNow.ToLocalTime() currentServerTime = CType(Eval("DateCreated"), DateTime).ToLocalTime() // C# DateTime currentServerTime = DateTime.UtcNow.ToLocalTime(); currentServerTime = ((DateTime) Eval("DateCreated")).ToLocalTime()

A Complete Example

To further illustrate using UTC to store auto-entered date/time values, let's examine a sample application that has fields to track when a record was created and last updated. This application, which is available at the end of this article, has a single database table, Employees, with a very simple schema:

Employees
Column	Data Type
EmployeeID	int - Primary Key and AutoIncrement
FullName	varchar(50)
Salary	money
DateCreated	datetime
DateUpdated	datetime

In the download you'll find a single ASP.NET page, UTCFunctions.aspx, that contains a DetailsView control for adding new employees and a GridView for listing and editing existing employees. Both are wired up to a SqlDataSource on the page that contains the associated INSERT, UPDATE, and DELETE statements. (For more information on working with data in ASP.NET 2.0, see my multi-part article series Accessing and Updating Data in ASP.NET 2.0.)

As you can see from the screen shot above, the DetailsView does not allow the user to enter values for the DateCreated or DateUpdated fields. Instead, it populates these with the current date/time in UTC. The INSERT statement used by the SqlDataSource control follows:

INSERT INTO [Employees] ([FullName], [Salary], [DateCreated], [DateUpdated]) VALUES (@FullName, @Salary, @DateCreated, getutcdate())

For the DateUpdated value, the built-in SQL Server function getutcdate() is used. For the DateCreated value, a parameter is specified (@DateCreated) and its value is specified programmatically in the DetailsView control's ItemInserting event handler:

' VB Protected Sub dvNewEmployee_ItemInserting(sender As Object, e As DetailsViewInsertEventArgs)    e.Values("DateCreated") =  DateTime.UtcNow End Sub // C# protected void dvNewEmployee_ItemInserting(object sender, DetailsViewInsertEventArgs e) {    e.Values["DateCreated"] = DateTime.UtcNow; }

I used both techniques - getutctime() and DateTime.UtcNow - to illustrate both options. In practice you'd probably want to choose one approach and use it for both parameters.

The GridView displays the DateUpdated and DateCreated values in UTC time. These are the raw values from the database. The two fields' values are also converted to local time and displayed in columns in the grid. This conversion is done using the ToLocalTime method. In particular, a TemplateField is used to show the local times and the ToLocalTime method is used directly within the Label control's Text property:

' VB                // C#

Lastly, note that the GridView's editing interface does not allow the date fields to be modified. Whenever a record is edited the DateUpdated field is updated to the current date/time (in UTC) via theUPDATE statement:

UPDATE [Employees] SET    [FullName] = @FullName,    [Salary] = @Salary,     [DateUpdated] = getutcdate() WHERE [EmployeeID] = @EmployeeID

Conclusion

UTC time provides a universal point of reference by which all time zones are offsetted from. Therefore, UTC is an ideal choice for storing date/time formats in a web application when the web server and its users might reside in different time zones. Things like daylight savings further complicate working with dates and times, but UTC does not observe any time zone, simplifying things a tiny bit. In short, working with dates and times in a web application is one of those things that is harder than it should be and has a lot of wrinkles that make the whole thing a bit of a mess. One step in the right direction, however, is to make sure you are storing auto-entered timestamp-like date and time values in UTC rather than in server time.

For guidance on displaying date and time values, see Advice for Storing and Displaying Dates and Times Across Different Time Zones.

Happy Programming!