How to render MVC View on a Modal Popup Window

You might be wondering how to place an MVC View easily on a pop-up window like the image above that’s why you are in this page now?

Well you a bit lucky as I will tell you how easy is this to execute, but first of all let me tell you that I am using the Window Control from Telerik Extensions for ASP.NET MVC to make my life easy and not re-invent the wheel and if you don’t have problems with that then read ahead.

Now lets start.  Lets pretend we want an application to set up a new client Account and what we want is when a user clicks a link create account it will pop up a window for account creation.  So like any other MVC Application you need your Model which in this case we will call “AccountSetupViewModel” which is a model for setting up accounts, a View for the pop up which we can call “NewAccount.cshtml” and Controller which we can call “SetupController”.  We also need some JavaScript file to separate our JavaScript commands to others for a cleaner implementation.

Lets first make our ViewModel in AccountSetupViewModel.cs under Controllers -> ViewModels -> Setup folder, we will make it simple so it will only contain AccountCode and AccountName

public class AccountSetupViewModel
{
    [Required]
    public string AccountCode { get; set; }

    [Required]
    public string AccountName { get; set; }

}

Now lets create a query to execute with firstName and lastName as a parameter and name it as GetAccountViewModel which we will place in  AccountSetupQuery.cs under Controllers -> Queries -> Setup, you can also create an interface for it if you wish.  This method will combine the firstName and lastName and sets the AccountName viewModel.

public AccountSetupViewModel GetAccountViewModel(string firstName, string lastName)
{
    var viewModel = new AccountSetupViewModel();

    viewModel.AccountCode = "RSM";
    viewModel.AccountName = firstName + " " + lastName;

    return viewModel;
}

Now lets create our Controller called SetupController.cs just in the Controllers directory and create a method called “GetNewAccountViewHtml”, this will be the method that will output a JsonResult to render our view

[HttpPost]
public JsonResult GetNewAccountViewHtml(string firstName, string lastName)
{
    string viewHtml = string.Empty;
    var viewModel = accountSetupQuery.GetAccountViewModel(firstName, lastName);

    viewHtml = RenderRazorViewToString("NewAccount", viewModel);

    var hashtable = new Hashtable();
    hashtable["viewHtml"] = viewHtml;

    return Json(hashtable);
}

You notice we have a method called RenderRazorViewToString, like how it’s called its purpose is to Render the MVC Razor View to string.   We will be using the MVC engine to render the view model as HTML so we can easily place it on the pop-up window.  You can place it in the controller but best if there is a separate class for this as you will  definitely reuse this a lot.

private string RenderRazorViewToString(string viewName, object model)
{
    ViewData.Model = model;

    using (var stringWriter = new StringWriter())
    {
        var viewResult = ViewEngines.Engines.FindPartialView(ControllerContext, viewName);
        var viewContext = new ViewContext(ControllerContext, viewResult.View, ViewData, TempData, stringWriter);
        viewResult.View.Render(viewContext, stringWriter);
        viewResult.ViewEngine.ReleaseView(ControllerContext, viewResult.View);

        return stringWriter.GetStringBuilder().ToString();
    }
}

Next lets create a view and call it “NewAccount.cshtml” and place it in Views -> Setup, this is the view that we will be using on the pop-up window.

@model CI5.Web.Mvc.Controllers.ViewModels.Setup.AccountSetupViewModel
@using Telerik.Web.Mvc.UI;
@{
    Layout = null;
}

This is an MVC view in a pop up <br />
Account Code : @Model.AccountCode <br />
Account Name : @Model.AccountName

Now you have all of  the contents you need for that pop-up, now lets create a view to call the pop-up we can use “Default.cshtml” under View -> Setup Folder in this instance.  On your view it will be simple as registering JavaScript on an a link to pop up the window so put this on your view.

<li class="newclient"><a href="#newClient" title="" onclick="javascript:AccountSetupForm.displayPopUpWindow('Raymund', 'Macaalay');">New Client</a></li>

Then using a javascript file that initializes on load of the “_SiteLayout.cshtml“

<script src="@Url.Content("~/Scripts/AccountSetup.js")" type="text/javascript"></script>

which we call “AccountSetup.js” located in Scripts folder we will create a function trigger the Telerik window pop up.

var AccountSetupForm = (function () {
    return {
        init: function () {
        },
        displayPopUpWindow: function (firstName, lastName) {

            var postData = {
                firstName: firstName,
                lastName: lastName
            };

            $.post("/Setup/GetNewAccountViewHtml", postData, function (data) {
                $.telerik.window.create({
                    title: "Sample Window",
                    html: unescape(data.viewHtml),
                    modal: true,
                    resizable: false,
                    visible: false,
                    width: 500,
                    height: 200
                })
            .data('tWindow').center().open();
            });
        }
    };

})();

$(document).ready(function () {
    AccountSetupForm.init();
});

So for a full view on how this is structured please refer to the image below.  I highlighted what was used on the codes above.

Foreign Key Drop Downs on Telerik MVC Grid

If you are using MVC on your projects and you are using a Grid View control most probably it will be the Telerik Grid.  While it is one of the best ones around it still have its cons but whats good with it is that the product is regularly updated even for the open source license.  One thing I noticed with the older version before 2011.3.1115.0 is that foreign keys are not natively supported, which means if you have a data structure similar to below then it will be task to perform operations on those in the grid as you will go by the template method.

Logically you will put a drop down on the grid so when you edit or add an item then you will be presented with the contact type options.  Well with that old version it would not be straightforward as you have to make your own template to display that drop down.  For those who are interested well here is how made it.

First is to create your view model, for this sample lets call it AccountContactViewModel

public class AccountContactViewModel
{
    public int Id { get; set; }

    public string LastName { get; set; }

    public string FirstName { get; set; }

    public string TelephoneNumber { get; set; }

    public string FaxNumber { get; set; }

    public string MobileNumber { get; set; }

    public string EmailAddress { get; set; }

    private NumericKeyValuePair contactTypes;

    [UIHint("AccountContactType"), Required]
    public NumericKeyValuePair ContactTypes
    {
        get
        {
            if (this.contactTypeViewModel == null)
            {
                NumericKeyValuePair o = new NumericKeyValuePair();
                o.Key = 0;
                o.Value = string.Empty;

                return o;
            }

            return this.contactTypeViewModel;
        }

        set
        {
            this.contactTypeViewModel = value;
        }
    }
}

If you noticed I used a NumericKeyValuePair which is just a class to define Key and value parings to be used for dropdowns

public class NumericKeyValuePair
{
    public int Key { get; set; }

    public string Value { get; set; }
}

Also you will notice the UIHint property, that’s the property to set the name of the field template to use to display the data field which is used for rendering data fields in a data model.  In simple words this is what you need to name your Editor Template to show your drop down so it automatically maps the data field in the model and render it in the control.

Next is you need an Editor template to show that drop down values and like I said above we will name it AccountContactType

The code behind is easy as it is just using a DropDownListFor and it is feeding data from a ContactType Model, you can also manually add items like such

@Html.DropDownList(ViewData.TemplateInfo.GetFullHtmlFieldName(string.Empty), new List<SelectListItem>
{
    new SelectListItem{ Value = "0", Text = string.Empty },
    new SelectListItem{ Value="1", Text = "Primary Debtor" }, 
    new SelectListItem{ Value="2", Text = "Other Debtor" },
    new SelectListItem{ Value="3", Text = "Primary Internal" },  
    new SelectListItem{ Value="4", Text = "Other Internal" }
})

Then on your View where your MVC Grid is, one column should be defined as a Client Template which calls the Client Template above and assigns the ContactTypeId to be linked to the ContactTypes, this is important for the grid to know which item to choose when the row is bound.

@(Html.Telerik().Grid<AccountContactViewModel>()
.Name("grdAccountContact")
.Columns(columns =>
{
    columns.Command(a =>
    {
        a.Delete().ButtonType(GridButtonType.Image);
    }).Width(80);
    columns.Bound(a => a.FirstName);
    columns.Bound(a => a.LastName);
    columns.Bound(a => a.TelephoneNumber);
    columns.Bound(a => a.MobileNumber);
    columns.Bound(a => a.FaxNumber);
    columns.Bound(a => a.EmailAddress);
    columns.Bound(a => a.ContactTypeViewModel).ClientTemplate("<#= ContactTypes.Value #>").Title("Contact Type");
    columns.Bound(a => a.Id).Hidden();
})
.ToolBar(commands => commands.Insert())
.DataBinding(d => d.Ajax()
    .OperationMode(GridOperationMode.Server)
    .Select("SelectAccountContacts", "Setup", new { accountId = Model.AccountId })
    .Update("UpdateAccountContacts", "Setup", new { accountId = Model.AccountId })
    .Delete("DeleteAccountContacts", "Setup", new { accountId = Model.AccountId })
.Editable(e => e.Mode(GridEditMode.InCell))
.DataKeys(k => k.Add(a => a.Id))
.ToolBar(b => b.SubmitChanges())
.ClientEvents(e => e
    .OnDataBound("GridHelper.onDataBound")
    .OnEdit("GridHelper.onEdit")
    .OnSave("GridHelper.onSave"))
)

Now for your Controller you will have something simple as this

[GridAction]
public ActionResult SelectAccountContacts(int? accountId)
{
    var viewModel = accountSetupQuery.GetAccountContacts(accountId.Value);

    return View(new GridModel<AccountContactViewModel>(viewModel));
}

which calls this query

public IList<AccountContactViewModel> GetAccountContacts(int accountId)
{
    var accountContacts = 
    (from a in Session.Query<AccountContact>()
    where a.Account.Id == accountId
    select new AccountContactViewModel
    {
        Id = a.Account.Id,
        LastName = a.LastName,
        FirstName = a.FirstName,
        TelephoneNumber = a.TelephoneNumber,
        FaxNumber = a.FaxNumber,
        MobileNumber = a.MobileNumber,
        EmailAddress = a.EmailAddress,
        ContactTypeViewModel = new NumericKeyValuePair() { Key = a.ContactType.Id, Value = a.ContactType.Name }
    }).ToList();

    return accountContacts;
}

Take note of that Session.Query<AccountContact> as I am using S#arp Architecture, my LINQ is executing its query to a nHibernateQuery Session of the Account Contact Entity defined in my domain, you can use any queryable collection here.

Now thanks to the new version you don’t have to use the Client Template and all you have to do is to define the column as a Foreign Key.  Here is how it should be done now for those interested.  (BTW the codes above can still be used as a reference for using Client Template)

First is we modify our view model and we remove all instances of the NumericKeyValuePair and replace it with the Foreign Key Id and we will name it ContactTypeId.

public class AccountContactViewModel
{
    public int Id { get; set; }

    public string LastName { get; set; }

    public string FirstName { get; set; }

    public string TelephoneNumber { get; set; }

    public string FaxNumber { get; set; }

    public string MobileNumber { get; set; }

    public string EmailAddress { get; set; }

    public int ContactTypeId { get; set; }  
}

Next is we get rid of the client template then your Grid should be defined like this

@(Html.Telerik().Grid<AccountContactViewModel>()
.Name("grdAccountContact")
.Columns(columns =>
{
    columns.Command(a =>
    {
        a.Delete().ButtonType(GridButtonType.Image);
    }).Width(80);
    columns.Bound(a => a.FirstName);
    columns.Bound(a => a.LastName);
    columns.Bound(a => a.TelephoneNumber);
    columns.Bound(a => a.MobileNumber);
    columns.Bound(a => a.FaxNumber);
    columns.Bound(a => a.EmailAddress);
    columns.ForeignKey(a => a.ContactTypeId, Model.ContactTypes, "Id", "Name");
    columns.Bound(a => a.Id).Hidden();
})
.ToolBar(commands => commands.Insert())
.DataBinding(d => d.Ajax()
    .OperationMode(GridOperationMode.Server)
    .Select("SelectAccountContacts", "Setup", new { accountId = Model.AccountId })
    .Update("UpdateAccountContacts", "Setup", new { accountId = Model.AccountId }))
.Editable(e => e.Mode(GridEditMode.InCell))
.DataKeys(k => k.Add(a => a.Id))
.ToolBar(b => b.SubmitChanges())
.ClientEvents(e => e.OnDataBound("GridHelper.onDataBound")
    .OnEdit("GridHelper.onEdit")
    .OnSave("GridHelper.onSave"))
)

You notice that the old ClientTemplate is now replaced to ForeignKey and it is populated by from the Model.ContactTypes so make sure before the MVC Grid is populated the Model.ContactTypes already have a value.

Now you can still use the same controller but you will need to change your query to something like this

public IList<AccountContactViewModel> GetAccountContacts(int accountId)
{
    var accountContacts = (from a in Session.Query<AccountContact>()
    where a.Account.Id == accountId
    select new AccountContactViewModel
    {
        Id = a.Account.Id,
        LastName = a.LastName,
        FirstName = a.FirstName,
        TelephoneNumber = a.TelephoneNumber,
        FaxNumber = a.FaxNumber,
        MobileNumber = a.MobileNumber,
        EmailAddress = a.EmailAddress,
        ContactTypeId = a.ContactType.Id
    }).ToList();

    return accountContacts;
}

You will notice that you don’t have that NumericKeyValuePair and it was replaced with the Foreign Key Id in your AccountContactViewModel.

That’s it, its simpler than before, now you can put a of foreign keys on that table without creating lots of Client Template.

The Easy way of changing Collation of all Database objects in SQL Server

Did you encountered a problem where you wanted to change your database collation to default or even just changing it to a different type? I guess what you had initially done (like me) was to change the collation of the Database

well that does not quite work well as the existing columns will not be changed and retain its current collation type, only the newly created objects will use this new collation type. So you are left to the option of changing the columns one at a time by going to the column property and restoring it to default or choosing the collation type you want.

Well that’s great if you need to change 10 columns or less but what if you want to change the whole database? What if it’s a primary key or a foreign key? Well isn’t that a nightmare? Well I will give you an easy solution and all you need to do is to run 6 easy steps. If you don’t want to recreate the database and pump data by using DTS or SSIS then this is the solution for you just make sure backup and restore everything before doing any changes.

Step 1: Prepare your DB and Change the collation to your desired one.

Like I had said backup your database as a part of the preparation, once that’s done change your collation to the desired type by going to the database properties by right clicking on the database and choosing properties, once your on the properties window choose options and you can see the collation from there, choose what you want then hit OK. This will ensure that new objects created will be using the new collation.

Step 2: Create you Change Collation Script.

Next is to create a script to change the collation of every object in your database. You need to use the information_schema to extract columns needed to be changed and from there we run a loop on all objects creating alter scripts on each item. Since it is a collation change we will only need fields that uses character types and text types. What you need is to have a lot of commands similar to this

ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME varchar(100) COLLATE Latin1_General_CI_AS NULL

So here is the code to generate that

OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE MyColumnCursor Cursor
		FOR 
		SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE from information_schema.columns
			WHERE table_name = @TableName AND  (Data_Type LIKE '%char%' OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
			ORDER BY ordinal_position 
		Open MyColumnCursor

		FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, @CharacterMaxLen, @IsNullable
		WHILE @@FETCH_STATUS = 0
			BEGIN
			SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' + @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END + ') COLLATE ' + @CollationName + ' ' + CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
			PRINT @SQLText 

		FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, @CharacterMaxLen, @IsNullable
		END
		CLOSE MyColumnCursor
		DEALLOCATE MyColumnCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor

Run it then save the script for later use. Lets call the script “ChangeCollation.sql”

If you don’t have relationships, primary keys and foreign keys then you don’t need to do the next step.

Step 3: Create a Stored Procedure to Script Indexes and Relationships

Well if you have relationships, primary keys and foreign keys then that’s a good practice but you need to script them as you need to drop those before changing the collation. Initially I thought I can do this with the wizard and choose to script indexes but it does not create on its own the table creation is always included so with a little help from Google I don’t have to write a single piece of code. I found this really good script to do it and I got it from here http://sqlblog.com/blogs/adam_machanic/archive/2010/04/04/rejuvinated-script-creates-and-drops-for-candidate-keys-and-referencing-foreign-keys.aspx
I only separated the Create Indexes and Drop Indexes as we need to run a process in the middle.

Here is the Create Index script courtesy of Adam Machanic

/*
Script Table Keys
(C) 2010 Adam Machanic - amachanic@gmail.com
http://sqlblog.com/blogs/adam_machanic/archive/2010/04/04/rejuvinated-script-creates-and-drops-for-candidate-keys-and-referencing-foreign-keys.aspx
This script produces a script of all of the candidate keys (primary keys or unique 
constraints) as well as referencing foreign keys, for the target table. To use, put
SSMS into "results in text" mode and run the script. The output will be a formatted
script that you can cut and paste to use elsewhere.

Don't forget to configure the maximum text size before using. The default is 256
characters--not enough for many cases.

Tools->Options->Query Results->Results to Text->Maximum number of characters->8192
*/
CREATE PROC [dbo].[ScriptCreateTableKeys]
	@table_name SYSNAME
AS
BEGIN
	SET NOCOUNT ON

	--Note: Disabled keys and constraints are ignored
	--TODO: Drop and re-create referencing XML indexes, FTS catalogs

	DECLARE @crlf CHAR(2)
	SET @crlf = CHAR(13) + CHAR(10)
	DECLARE @version CHAR(4)
	SET @version = SUBSTRING(@@VERSION, LEN('Microsoft SQL Server') + 2, 4)
	DECLARE @object_id INT
	SET @object_id = OBJECT_ID(@table_name)
	DECLARE @sql NVARCHAR(MAX)

	IF @version NOT IN ('2005', '2008')
	BEGIN
		RAISERROR('This script only supports SQL Server 2005 and 2008', 16, 1)
		RETURN
	END

	SET @sql = '' +
		'SELECT ' +
			'CASE ' +
				'WHEN 1 IN (i.is_primary_key, i.is_unique_constraint) THEN ' +
					'''ALTER TABLE '' + ' +
						'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
						'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
					'''ADD '' + ' +
						'CASE k.is_system_named ' +
							'WHEN 0 THEN ''CONSTRAINT '' + QUOTENAME(k.name) + @crlf ' +
							'ELSE '''' ' +
						'END + ' +
					'CASE k.type ' +
						'WHEN ''UQ'' THEN ''UNIQUE'' ' +
						'ELSE ''PRIMARY KEY'' ' +
					'END + '' '' + ' +
					'i.type_desc  + @crlf + ' +
					'kc.key_columns + @crlf ' +
				'ELSE ' +
					'''CREATE UNIQUE '' + i.type_desc + '' INDEX '' + ' +
						'QUOTENAME(i.name) + @crlf + ' +
					'''ON '' + ' +
						'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
						'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
					'kc.key_columns + @crlf + ' +
					'COALESCE ' +
					'( ' +
						'''INCLUDE '' + @crlf + ' +
						'''( '' + @crlf + ' +
							'STUFF ' +
							'( ' +
								'( ' +
									'SELECT ' +
									'( ' +
										'SELECT ' +
											''','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()] ' +
										'FROM sys.index_columns AS ic ' +
										'JOIN sys.columns AS c ON ' +
											'c.object_id = ic.object_id ' +
											'AND c.column_id = ic.column_id ' +
										'WHERE ' +
											'ic.object_id = i.object_id ' +
											'AND ic.index_id = i.index_id ' +
											'AND ic.is_included_column = 1 ' +
										'ORDER BY ' +
											'ic.key_ordinal ' +
										'FOR XML PATH(''''), TYPE ' +
									').value(''.'', ''VARCHAR(MAX)'') ' +
								'), ' +
								'1, ' +
								'3, ' +
								''''' ' +
							') + @crlf + ' +
						''')'' + @crlf, ' +
						''''' ' +
					') ' +
			'END + ' +
			'''WITH '' + @crlf + ' +
			'''('' + @crlf + ' +
				''' PAD_INDEX = '' + ' +
						'CASE CONVERT(VARCHAR, i.is_padded) ' +
							'WHEN 1 THEN ''ON'' ' +
							'ELSE ''OFF'' ' +
						'END + '','' + @crlf + ' +
				'CASE i.fill_factor ' +
					'WHEN 0 THEN '''' ' +
					'ELSE ' +
						''' FILLFACTOR = '' + ' +
								'CONVERT(VARCHAR, i.fill_factor) + '','' + @crlf ' +
				'END + ' +
				''' IGNORE_DUP_KEY = '' + ' +
						'CASE CONVERT(VARCHAR, i.ignore_dup_key) ' +
							'WHEN 1 THEN ''ON'' ' +
							'ELSE ''OFF'' ' +
						'END + '','' + @crlf + ' +
				''' ALLOW_ROW_LOCKS = '' + ' +
						'CASE CONVERT(VARCHAR, i.allow_row_locks) ' +
							'WHEN 1 THEN ''ON'' ' +
							'ELSE ''OFF'' ' +
						'END + '','' + @crlf + ' +
				''' ALLOW_PAGE_LOCKS = '' + ' +
						'CASE CONVERT(VARCHAR, i.allow_page_locks) ' +
							'WHEN 1 THEN ''ON'' ' +
							'ELSE ''OFF'' ' +
						'END + ' +
				CASE @version
					WHEN '2005' THEN ''
					ELSE			 
						''','' + @crlf + ' +
						''' DATA_COMPRESSION = '' + ' +
							'( ' +
								'SELECT ' +
									'CASE ' +
										'WHEN MIN(p.data_compression_desc) = MAX(p.data_compression_desc) THEN MAX(p.data_compression_desc) ' +
										'ELSE ''[PARTITIONS USE MULTIPLE COMPRESSION TYPES]'' ' +
									'END ' +
								'FROM sys.partitions AS p ' +
								'WHERE ' +
									'p.object_id = i.object_id ' +
									'AND p.index_id = i.index_id ' +
							') '
				END + '+ @crlf + ' +
			''') '' + @crlf + ' +
			'''ON '' + ds.data_space + '';'' + ' +
				'@crlf + @crlf COLLATE database_default AS [-- Create Candidate Keys] ' +
		'FROM sys.indexes AS i ' +
		'LEFT OUTER JOIN sys.key_constraints AS k ON ' +
			'k.parent_object_id = i.object_id ' +
			'AND k.unique_index_id = i.index_id ' +
		'CROSS APPLY ' +
		'( ' +
			'SELECT ' +
				'''( '' + @crlf + ' +
					'STUFF ' +
					'( ' +
						'( ' +
							'SELECT ' +
							'( ' +
								'SELECT ' +
									''','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()] ' +
								'FROM sys.index_columns AS ic ' +
								'JOIN sys.columns AS c ON ' +
									'c.object_id = ic.object_id ' +
									'AND c.column_id = ic.column_id ' +
								'WHERE ' +
									'ic.object_id = i.object_id ' +
									'AND ic.index_id = i.index_id ' +
									'AND ic.key_ordinal > 0 ' +
								'ORDER BY ' +
									'ic.key_ordinal ' +
								'FOR XML PATH(''''), TYPE ' +
							').value(''.'', ''VARCHAR(MAX)'') ' +
						'), ' +
						'1, ' +
						'3, ' +
						''''' ' +
					') + @crlf + ' +
				''')'' ' +
		') AS kc (key_columns) ' +
		'CROSS APPLY ' +
		'( ' +
			'SELECT ' +
				'QUOTENAME(d.name) + ' +
					'CASE d.type ' +
						'WHEN ''PS'' THEN ' +
							'+ ' +
							'''('' + ' +
								'( ' +
									'SELECT ' +
										'QUOTENAME(c.name) ' +
									'FROM sys.index_columns AS ic ' +
									'JOIN sys.columns AS c ON ' +
										'c.object_id = ic.object_id ' +
										'AND c.column_id = ic.column_id ' +
									'WHERE ' +
										'ic.object_id = i.object_id ' +
										'AND ic.index_id = i.index_id ' +
										'AND ic.partition_ordinal = 1 ' +
								') + ' +
							''')'' ' +
						'ELSE '''' ' +
					'END ' +
			'FROM sys.data_spaces AS d ' +
			'WHERE ' +
				'd.data_space_id = i.data_space_id ' +
		') AS ds (data_space) ' +
		'WHERE ' +
			'i.object_id = @object_id ' +
			'AND i.is_unique = 1 ' +
			--filtered and hypothetical indexes cannot be candidate keys
			CASE @version
				WHEN '2008' THEN 'AND i.has_filter = 0 '
				ELSE ''
			END +
			'AND i.is_hypothetical = 0 ' +
			'AND i.is_disabled = 0 ' +
		'ORDER BY ' +
			'i.index_id '

	EXEC sp_executesql
@sql,
		N'@object_id INT, @crlf CHAR(2)',
		@object_id, @crlf

	SELECT
		'ALTER TABLE ' + 
			QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + 
			QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + @crlf +
		CASE fk.is_not_trusted
			WHEN 0 THEN 'WITH CHECK '
			ELSE 'WITH NOCHECK '
		END + 
			'ADD ' +
				CASE fk.is_system_named
					WHEN 0 THEN 'CONSTRAINT ' + QUOTENAME(name) + @crlf
					ELSE ''
				END +
		'FOREIGN KEY ' + @crlf + 
		'( ' + @crlf + 
			STUFF
(
(
					SELECT
(
						SELECT 
							',' + @crlf + ' ' + QUOTENAME(c.name) AS [text()]
						FROM sys.foreign_key_columns AS fc
						JOIN sys.columns AS c ON
							c.object_id = fc.parent_object_id
							AND c.column_id = fc.parent_column_id
						WHERE 
							fc.constraint_object_id = fk.object_id
						ORDER BY
							fc.constraint_column_id
						FOR XML PATH(''), TYPE
					).value('.', 'VARCHAR(MAX)')
				),
				1,
				3,
				''
			) + @crlf + 
		') ' +
		'REFERENCES ' + 
			QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.' + 
			QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + @crlf +
		'( ' + @crlf + 
			STUFF
(
(
					SELECT
(
						SELECT 
							',' + @crlf + ' ' + QUOTENAME(c.name) AS [text()]
						FROM sys.foreign_key_columns AS fc
						JOIN sys.columns AS c ON
							c.object_id = fc.referenced_object_id
							AND c.column_id = fc.referenced_column_id
						WHERE 
							fc.constraint_object_id = fk.object_id
						ORDER BY
							fc.constraint_column_id
						FOR XML PATH(''), TYPE
					).value('.', 'VARCHAR(MAX)')
				),
				1,
				3,
				''
			) + @crlf + 
		');' + 
			@crlf + @crlf COLLATE database_default AS [-- Create Referencing FKs]
	FROM sys.foreign_keys AS fk
	WHERE
		referenced_object_id = @object_id
		AND is_disabled = 0
	ORDER BY
		key_index_id

END

Step 4: Create Stored Procedure to Script Drop Indexes and Relationships

Now you also need to create the drop scripts, these is the other half of Adam Machanic’s script

CREATE PROC [dbo].[ScriptDropTableKeys]
	@table_name SYSNAME
AS
BEGIN
	SET NOCOUNT ON

	--Note: Disabled keys and constraints are ignored
	--TODO: Drop and re-create referencing XML indexes, FTS catalogs

	DECLARE @crlf CHAR(2)
	SET @crlf = CHAR(13) + CHAR(10)
	DECLARE @version CHAR(4)
	SET @version = SUBSTRING(@@VERSION, LEN('Microsoft SQL Server') + 2, 4)
	DECLARE @object_id INT
	SET @object_id = OBJECT_ID(@table_name)
	DECLARE @sql NVARCHAR(MAX)

	IF @version NOT IN ('2005', '2008')
	BEGIN
		RAISERROR('This script only supports SQL Server 2005 and 2008', 16, 1)
		RETURN
	END

	SELECT
		'ALTER TABLE ' + 
			QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + 
			QUOTENAME(OBJECT_NAME(parent_object_id)) + @crlf +
		'DROP CONSTRAINT ' + QUOTENAME(name) + ';' + 
			@crlf + @crlf COLLATE database_default AS [-- Drop Referencing FKs]
	FROM sys.foreign_keys
	WHERE
		referenced_object_id = @object_id
		AND is_disabled = 0
	ORDER BY
		key_index_id DESC

	SET @sql = '' +
		'SELECT ' +
			'statement AS [-- Drop Candidate Keys] ' +
		'FROM ' +
		'( ' +
			'SELECT ' +
				'CASE ' +
					'WHEN 1 IN (i.is_unique_constraint, i.is_primary_key) THEN ' +
						'''ALTER TABLE '' + ' +
							'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
							'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
						'''DROP CONSTRAINT '' + QUOTENAME(i.name) + '';'' + ' +
							'@crlf + @crlf COLLATE database_default ' +
					'ELSE ' +
						'''DROP INDEX '' + QUOTENAME(i.name) + @crlf + ' +
						'''ON '' + ' +
							'QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + ''.'' + ' +
							'QUOTENAME(OBJECT_NAME(object_id)) + '';'' + ' +
								'@crlf + @crlf COLLATE database_default ' +
				'END AS statement, ' +
				'i.index_id ' +
			'FROM sys.indexes AS i ' +
			'WHERE ' +
				'i.object_id = @object_id ' +
				'AND i.is_unique = 1 ' +
				--filtered and hypothetical indexes cannot be candidate keys
				CASE @version
					WHEN '2008' THEN 'AND i.has_filter = 0 '
					ELSE ''
				END +
				'AND i.is_hypothetical = 0 ' +
				'AND i.is_disabled = 0 ' +
		') AS x ' +
		'ORDER BY ' +
			'index_id DESC '

	EXEC sp_executesql 
@sql,
		N'@object_id INT, @crlf CHAR(2)',
		@object_id, @crlf

END

Step 5: Bringing them all together

Now you have the two stored procedure all you have to do is to loop though all tables in you database and pass that as the parameter of the Stored Procedure. First we use the ScriptCreateTableKeys.

DECLARE @TableName nvarchar(255)
DECLARE MyTableCursor Cursor
FOR 
SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name 
OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
	BEGIN
	EXEC ScriptCreateTableKeys @TableName

	FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor

Then lets use the ScriptDropTableKeys

DECLARE @TableName nvarchar(255)
DECLARE MyTableCursor Cursor
FOR 
SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name 
OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
	BEGIN
	 EXEC ScriptDropTableKeys @TableName

	FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor

Just make sure when you execute them output the results as text so you can easily copy and paste the results.
Save the first results as “CreateKeysAndIndexes.sql” and the second as “DropKeysAndIndexes.sql”

Step 6: Run you saved scripts

In this order run your scripts and wait for the results, time wait might vary depending on your database size.

a. DropKeysAndIndexes.sql
b. ChangeCollation.sql
c. CreateKeysAndIndexes.sql

Not all files showing on Twonky Media Server / Vodafone Broadband Complete HG553

This post will not be programming related but more of a simple hack on a device I just recently acquired, anyways this post still is technology related and I wanted to share this simple solution that others might find useful.  Recently I moved to a different internet provider as my ADSL modem and Wi-Fi Router is getting old, service price is also expensive compared to the new offers currently on the market and there’s a really good offer on Vodafone NZ where they give a boradband modem device for free while keeping the rates reasonable compared to similar data plans. The device that I am talking about is a ADSL2+ Modem with Wi-Fi Router that has DLNA, 2 USB ports and 1 USB/HSPA port (for wireless modem).  I was searching before for a similar device and the only ones out there are limited to Buffalo WZR-HP-G300NH, Netgear N600 and Linksys WAG320N which all are ADSL2+ Modem Wi-Fi Router with DLNA and USB ports, well I am out of luck as we don’t have it here in New Zealand and the next best thing that I could only have is the  Netgear DGN2200M which costs NZ$185 and that’s not DLNA certified yet.  Now you might ask why DLNA, well I have a lot of DLNA devices at home and I want to stream media files such as music, video and photos on those devices and a single device that can integrate them all while staying on a budget would be good.  Given that scenario Vodafone have my answer by giving their device free, now I saved NZ$185 for the device and $15/month on phone and internet bills and my data plan increased by 35GB how good is that (Note: this is not an ad, I am just happy with my experience with Vodafone).

This device that I am talking about is a rebranded Huawei Echolife HG553 for a full information on the product here is a downloadable manual from Vodafone, it’s an amazing device and does DLNA very well now I stream media files from this device to my Smart TV, 3D Home Theatre, Android Devices, iPhones, PS3, PC’s and even an old XBOX with XBMC installed I don’t have to plug and unplug my USB hard drive to share media at home.  The sharing methods are through DLNA, FTP and/or a normal network file share; it also has a Print Server for those who have non-wireless printers at home.  Now let’s go to my problem, when I plugged the 2TB USB hard drive (take note it can take FAT or NTFS formatted drives) it immediately read the media files on it and it indexed it really fast, now I tried to browse the media files on my TV and I only see some of the media files on the USB drive.   I was worried there might be something wrong with the device so I browsed the hard drive though a PC and check whether files are in there, to my surprise it was there and was wondering why it was not indexed.  Knowing that it’s a Twonky software that works on the background, I immediately contacted their support, they replied quick but did not get anywhere as they don’t support OEM device, and here was their response which is fair enough.

Now I contacted Vodafone through their support forum, well I got a really bad answer which is totally unrelated to my question, a Vodafone forum administrator told me that I was posting it in a wrong location, so I moved it to the right one and got no answer after that, I did not mind to call their support as support in any Internet Service providers here in NZ sucks in my experience the average waiting time was 30 minutes and sometimes you don’t get the answer you are looking for specially if I am talking to a less technical person than me.  So anyway here was my post on the forum for those who are interested.

At this point I am on my own, so I tried everything by logging in to the router as support, Vodafone and advanced (all yield a different screen) and the only setting I find regarding DLNA is enabling and disabling it. I even went to the port http://192.168.1.1:9000 as that’s the advanced setup default for Twonky and get the red access denied screen.  Now I browsed through the files in the shared hard drive and found out that it has a twonkymedia.db folder.

So I went in and saw an .ini file, at last there is hope!

While reading the settings it looks like the most probable cause was an item called maxitemsmaxitems=10000#, now I counted how many media files I got on my hard drive and its more than 50K items most of it are images, that definitely is the reason why it’s not showing everything.  So I changed the settings to 100000, saved and rebooted the device. I browsed again once the device started using my TV and no luck, so I went back to the .ini file and found out it went back to its normal state, it looks like the device rewrites the ini file on reboot.  I also tried not rebooting, changed the maxitemsmaxitems=10000# to maxitemsmaxitems=100000# and wait for 60 minutes and that’s also a setting called minutesscantime=60# hoping that it will rescan and change the limit to 100000 but still no luck.  Finally I saw this setting ignoredirignoredir=AppleDouble,AppleDB,AppleDesktop,TemporaryUtems#, and if it’s true to its name it looks like it will ignore anything inside a directory.  So I created an AppleDB folder and placed everything there leaving only the files I want to stream on my PS3, Smart TV and 3D Home Theatre, videos are more logical as they are the ones you usually play on those device, I then rebooted and whoalla!

Now it’s showing all the videos I wanted and not the massive amounts of photos from 1999 to 2011 which kept my videos not showing due to the file count limit imposed.  I don’t care about the photos as we usually view them on Laptops and Android Devices which a normal share and mapped drive to the router will be suitable and no need for DLNA.

Sharepoint List View Threshold Error

You might have encountered the error below in Sharepoint thats why you stumbled upon this site.

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.<nativehr>0×80070024</nativehr><nativestack></nativestack>

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.0×80070024</nativehr><nativestack></nativestack>

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Before I give you the solution, lets see why does it happen on the first place so you can better understand what you are fixing.  The error basically about the size of the database operation that you are executing in one go, which means it can be viewing a list, loading a page or other database related operations.  That error simply says that the number of operations that exceed the limit set on the server this is now treated as an Expensive Query Exception.  Now you might notice that your user’s experience this error but not yourself this is because if you are an Administrator then you have higher thresholds.

Now how do you adjust this thresholds.  All you have to do is go to Central Administration -> Application Management -> Manage Web Applications then highlight the Name of the site you are adjusting the threshold

Then choose General Settings on the ribbon, then Resource Throttling.

Now you can change your setting on the List View Threshold, by default it is 5000 so you can increase it to something like 10000 to double the operation count.  Now you can also see the setting List View Threshold for Auditors and Administrators, that’s the reason why you don’t see the error as it is defaulted to 20000, but if you are an administrator and you have the same issue you can increase both settings.

This should fix your issue now, so go back what you were doing before it happened and it will not throw any exceptions anymore.