SQL Tips – Back up and restore

Here are some SQL bits that I user over and over again

If you try to manually restore the MS SQL server database via console app, it might say, ‘exclusive access could not be obtained’

This is probably there are some connections which have been left hanging or they are actually trying to do some stuff with the database.

I use this script to kill all un-wanted connections

use master

declare @vcdbname varchar(50)

Set @vcdbname = 'DatabaseName'

set nocount on

declare Users cursor for

 select spid

 from master..sysprocesses

 where db_name(dbid) = @vcdbname

declare @spid int, @str varchar(255)

open users

fetch next from users into @spid

while @@fetch_status <> -1

begin

   if @@fetch_status = 0

   begin

      set @str = 'kill ' + convert(varchar, @spid)

      exec (@str)

   end

   fetch next from users into @spid

end

deallocate users

Just change the name to your own database name and run the script

Imagine you have to backup and restore 5-10 database on adhoc bases, going through console app is very pain full and takes lot of time

I normally use scripts and run them in a go which back them up and restore them in a flash

For backups

DECLARE @BackupLocation AS NVARCHAR(250);
DECLARE @BackupDate AS NVARCHAR(8);
DECLARE @BackUpName AS NVARCHAR(350);

SET @BackupLocation = 'E:\SQLBackup\';
SET @BackUpDate = CONVERT(NVARCHAR(8), GETDATE(), 112)

-- replace MyDatabase with your database name
SET @BackUpName = @BackupLocation + 'MyDatabase_' + @BackUpDate + '.Bak';
USE master;
BACKUP DATABASE MyDatabase TO DISK = @BackUpName;

To resotre simply use the following script

USE [master];
ALTER DATABASE MyDatabaseNew SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MyDatabaseNew SET SINGLE_USER;
RESTORE DATABASE MyDatabaseNew FROM DISK = 'E:\SQLBackup\<you-file-name>.Bak'
WITH REPLACE;
ALTER DATABASE MyDatabaseNew SET MULTI_USER;
GO

I would normally create these scripts for each of the databases and when required just run them in SQL

Advertisements

Repeater OnItemCommand

In this post I will show a simple implementation on OnItemCommand of the repeater control.

This is suitable when you render items in a repeater and want to edit individual items

Consider this sample class

public class Products

{
public string ProductCode {get;set;}
public string ProductName {get;set;}
public decimal ProductPrice {get;set;}

}

You can display objects from this class very easily using any database call or Linq

In your repeater, you have a button to ‘Edit’ next to each product, which will allow admin users to change prices

So, the repeater might looks like this

 <asp:Repeater ID="RepeaterListOfProducts" runat="server" OnItemDataBound="RepeaterListOfProducts_OnItemDataBound" OnItemCommand="RepeaterListOfProducts_OnItemCommand">

<HeaderTemplate><ul></HeaderTemplate>

<ItemTemplate>

<li>
<label>

       <asp:Literal ID="LiteralProdcutCode" runat="server"></asp:Literal>
        <asp:Literal ID="LiteralProdcutName" runat="server"></asp:Literal>
        <asp:Literal ID="LiteralProductPrice" runat="server" ></asp:Literal>
</label>
        <asp:Button ID="ButtonEditProduct" runat="server" Text="Edit" CommandName="Edit" />

</li>

</ItemTemplate>

<FooterTemplate></ul></FooterTemplate>

</asp:Repeater>

In code behind, you have a function to populate products something like

RepeaterListOfProducts.DataSource = db.GetAllProducts();
RepeaterListOfProducts.DataBind();

In OnItemDataBound, I will assign ProductCode (assuming that is primary key/unique value per product) to the CommandArgument
for the button, so each button will have different argument when it is clicked

Sample code may look like this

protected void RepeaterListOfProducts_OnItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{

Product product = (Product)e.Item.DataItem;

Literal productCode = (Literal)e.Item.FindControl("LiteralProdcutCode");
productCode.Text = product.ProdcutCode;

Literal productName = (Literal)e.Item.FindControl("LiteralProdcutName");
productName.Text = product.ProdcutName;

Literal productPrice = (Literal)e.Item.FindControl("LiteralProductPrice");
productPrice.Text = product.ProductPrice;

Button update = (Button)e.Item.FindControl("ButtonEditProduct");
update.CommandArgument = product.ProductCode;

}

}

Now comes the interesting part, in you OnItemCommand, I will take the command button argument and setup a predefined text box (in a differnt panel) with the value
and let the user update the price in a panel

protected void RepeaterListOfUsers_OnItemCommand(object sender, RepeaterCommandEventArgs e)
{
if (e.CommandName == "Edit")
{
LiteralUpdateProductCode.Text = e.CommandArgument.ToString();
TextBoxUpdatePrice.Text = ((Literal)e.Item.FindControl("LiteralProductPrice")).Text;
//add your custom logic here

}
}

The above codes takes the price and code from the repeater items and allows user to update with your custom logic.

In the panel, I could also have another button, which will perform my custom logic.

This code can be extended to include ‘Delete’ operation as well.

Thanks

Sitecore : Programmatically add and publish Sitecore items through Workflow

In this post, I will show how to auto publish an Item in Sitecore. This is handy as sometimes you have to create items from external XML feeds or databases.

The code snippet also includes adding item to workflow and auto executing it to the final stage

Steps to follow

1. We need to switch from the current site context to shell (if your code is running from within the site)
2. Then use SecurityDisabler() to switch into admin mode
3. Get the master database
4. Get the template
5. Get the path where Item has to be placed
6. Check if the item path does not exists, if it doesnt then create a new item
7. Create and Edit your item
8. Move to workflow (if required)
9. Do an incremental publish

Here is the code snippet (some values are hard coded, but again you can get them from config/settings)

//step 1-Switch context
string currentSiteName = Sitecore.Context.Site.Name;
Sitecore.Context.SetActiveSite("shell");
//step 2-Switch to admin mode
using (new Sitecore.SecurityModel.SecurityDisabler())
{
//step 3-Get master database
Sitecore.Data.Database master = Sitecore.Configuration.Factory.GetDatabase("master");

Item newItem = null;
//step 4 - Get data template
TemplateItem templateItem = master.GetTemplate("User Defined/My Template");
//step 5 - Define static path
string path = "/sitecore/content/Home/My Template Items/";
//myItemPath might change with in a for loop or by passing variables
string myItemPath = "My new Item";
//define complete path
myItemPath = path + myItemPath
//step 6 - check if it exists
if (master.GetItem(myItemPath ) == null)
{
//step 7 - create and edit
newItem = master.CreateItemPath(myItemPath, templateItem);
newItem.Editing.BeginEdit();
newItem.Fields["Title"].Value = "My Item Title"; //programattically update the field
//amend more fields if required
newItem.Editing.EndEdit();
newItem.Editing.AcceptChagnes();

//step 8 - add to workflow if requried and place it in start state and then execute the final stage
Sitecore.Workflows.IWorkflow workflow = master.WorkflowProvider.GetWorkflow(newItem);
workflow.Start(newItem);
workflow.Execute(Config.AutoPublishCommandID, newItem, "auto approved", false);

//step 9 - publish to pre-defined targets and langugaes
Database[] targetDBs = new Database[] { Sitecore.Configuration.Factory.GetDatabase("web") };
Language[] languages = new Language[] { LanguageManager.GetLanguage("en") };
Sitecore.Publishing.PublishManager.PublishIncremental(master, targetDBs, languages);


}

}

//switch back to current site
Sitecore.Context.SetActiveSite(currentSiteName);

Cheers

Sitecore : Related Links

In this post, I will make use of Sitecore’s MultilistField data property and bit of code behind how to bind the list of pages to a repeater.

I would assume that you know what is a Sitecore’s MultilistField property and have basic knowledge of Sitecore before you read any further.

Sometimes we have to display related links on the right hand side of the page using Sitecore CMS.

The links are meant to cross-sell or cross-promote other sections of the website.

They cannot be dynamic as they will differ from page to page.

 

Here is the repeater control markup

<asp:PlaceHolder ID="PlaceHolderRelatedLinks" runat="server" >
<div>
<asp:Repeater ID="RepeaterRelatedLinks" runat="server" OnItemDataBound="RepeaterRelatedLinks_ItemDataBound">
<HeaderTemplate><h2>Related links</h2><ul></HeaderTemplate>

<ItemTemplate>
<li>
<asp:HyperLink ID="HyperLinkRelatedLinks" runat="server"></asp:HyperLink>
</li>
</ItemTemplate>

<FooterTemplate></ul></FooterTemplate>
</asp:Repeater>
</div>

</asp:PlaceHolder>

 

Here is the code behind markup

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Sitecore.Data.Items;
using Sitecore.Links;
using Sitecore.Data;

namespace SampleSite.Controls
{
public partial class RelatedLinksControl : System.Web.UI.UserControl
{
protected void Page_Load(object sender, EventArgs e)
{
//get a list of links for the current page "Related Links" property and cast it as MultilistField
Sitecore.Data.Fields.MultilistField relatedLinks = (Sitecore.Data.Fields.MultilistField)Sitecore.Context.Item.Fields["Related Links"];

//Multilist returns list of GUID ids of items, we need to create Sitecore Items before we can
//bind it to the Repeater
List<Item> children = new List<Item>();
//safety first, as foreach loop may break the page if this is null
if (relatedLinks != null)
{
//loop through each ID and create a new Item and add it to list
foreach (string childItem in relatedLinks)
{
children.Add(Sitecore.Context.Database.GetItem(new ID(childItem)));
}

//bind it to the repeater
RepeaterRelatedLinks.DataSource = children;
RepeaterRelatedLinks.DataBind();

}

//your right hand links section will be surrounded with <div> with a coloured background, make sure that
// link only appear if there are some items assigned to the page
if (children.Count > 0)
PlaceHolderRelatedLinks.Visible = true;
else
PlaceHolderRelatedLinks.Visible = false;

}

public void RepeaterRelatedLinks_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
//get the current Item and cast it as Sitecore Item
Item currentItem = (Item)e.Item.DataItem;

//Assign the current item to HyperLink control define on repeater
if (currentItem != null)
{

if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{

HyperLink relatedLink = (HyperLink)e.Item.FindControl("HyperLinkRelatedLinks");
sideNavigation.NavigateUrl = LinkManager.GetItemUrl(currentItem);
sideNavigation.Text = currentItem.Name;

}
}
}
}
}

 

Explanation :

Step 1 : Create a new user control and name it as RelatedLinksControl
Step 2 : Add repeater markup on the code behind (add your custom design)
Step 3 : On Page_Load function, get the list of related links iDs, convert them to Sitecore Items and bind the list to repeater
Step 4 : On RepeaterRelatedLinks_ItemDataBound function, bind the individual items to the link

Assign it to a sub layout or drop it on a layout as you wish

Job done!

Sitecore : Top Navigation

This is my first Sitecore CMS post, so I thought I will start with something very simple, what could be more simpler than ‘Top Navigation’

Sitecore gives you the flexibility to render items by XSLT or by .NET UserControls.

I prefer using .NET Controls over XSLT for various reasons

1. XSLT are easy to implement and fast but difficult to maintain when you have to introduce complex logic for navigation (like if user is logged in or not)
2. XSLT are difficult to debug (imagine going through 3 to 4 foreach loops!)
3. XSLT are so old, I feel like working in 90s (but people still use it)

Anyways, I will implement a very simple top navigation with a repeater in a .NET UserControl which will take top childrens of ‘Home’ Item/Node and render them
as top navigation

Here is the code for repeater (pretty standard)

<asp:Repeater ID="RepeaterTopNavigation" runat="server" OnItemDataBound="RepeaterTopNavigation_ItemDataBound">
<HeaderTemplate>
<ul>
<li><a href="/home.aspx">Home</a></li>
</HeaderTemplate>

<ItemTemplate>
<li>
<asp:HyperLink ID="HyperLinkTopNavigation" runat="server"></asp:HyperLink>
</li>
</ItemTemplate>

<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>

And here is the code behind for the repeater

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Sitecore.Data.Items;
using Sitecore.Links;

namespace SampleSite.Controls
{
public partial class TopNavigation : System.Web.UI.UserControl
{
protected void Page_Load(object sender, EventArgs e)
{
//get the home item, this is hardcoded but you can define it in web.config
Item home = Sitecore.Context.Database.GetItem("/sitecore/content/home");
//get all children from home using Sitecore API
Item[] children = home.Children.ToArray();
//Bind the children to repeater
RepeaterTopNavigation.DataSource = children;
RepeaterTopNavigation.DataBind();

}

public void RepeaterTopNavigation_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
//gets the current data item from RepeaterItemEventsArgs and cast it as a Sitecore Item
Item currentItem = (Item)e.Item.DataItem;

//check if it is not null, safety first
if (currentItem != null)
{

//check if it is coming from Item or Alternating Item template
if (e.Item.ItemType == ListItemType.Item || 
          e.Item.ItemType == ListItemType.AlternatingItem)
{

//Find the HyperLink control that has been defined in repeater
HyperLink topNavigation = (HyperLink)e.Item.FindControl("HyperLinkTopNavigation");
//Use Sitecore API to get the link to the Item and upadte the href property of link
topNavigation.NavigateUrl = LinkManager.GetItemUrl(currentItem);
//Assign name to the link
topNavigation.Text = currentItem.Name;

}
}
}
}
}

Explanation

1. Get the home Item
2. Get the children of home Item as array
3. Bind them to repeater
4. For OnItemDataBound, cast the DataItem as Sitecore Item and update the HyperLink property for each

Drop the control on your main layout and check for any build errors.

Thats it!

Simple String Extensions

This is a very simple string extension that you might need to limit the amount of sentence length.

It is useful in situations where one content area can be used to show full text or just the summary.

Now, you would say I could limit that the sentence length with

myVar.SubString(0,maxLength)

function, but it will chop off the word of a sentense where it will meet the limit

What to do?

Consider the following snippet

public static string LimitSentenceLength(this string s, int maximumLength)
{
//safe type checking
if (s == null)
return null;

//if length of the content is greater than
//maximum length define, then we do some magic
if (s.Length > maximumlength)
{

//split the sentence into individual words
string[] sentence = s.Split(' ');

//initilaize string to return
string toReturn = string.Empty;

//loop through each word in sentense
foreach (string word in sentence)
{
//if the length of return word plus current word plus
//a constant 3 (for "...")    is more than the max
//length define, then add "..." , break the for loop and return
if ((toReturn.Length + word.Length + 3) > maximumlength)
{
toReturn += "...";
break;
}

//if not, then keep on adding words with spaces
toReturn += word + " ";

}

//for loop ends, return modified string
return toReturn;
}

//entered string is shorter than max length define, return original sentence
else
{
return s;
}

}

Code is pretty much self explanatory but I will try to explain this simpleton

1. It checks if string is not null
2. Then it checks that if the length of string is more than maximum limit define in code
2a. If it does, the function does it magic and returns the modified word
2b. Otherwise, it returns the orginal word

To use this extension else where in the code, add namespace of your class and do something like

var myString = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nullam tincidunt metus at elit condimentum auctor.";
myString = myString.LimitSentenceLength(20);

Happy coding!

CSS Friendly RadioButtonList

It was lovely Monday afternoon and a designer from team and said, “Hey Naveed!, can you do the same thing with <asp:RadioButtonList> as you did with CheckBoxList and make it accessible and I would also like to have a “class” on the selected radiobutton, so i can replace it with an image from CSS”

I said, “Your wish is my command, maestro!”.

Taking the similar approach as in this post, we create a custom ULRadioButtonList class, extend it to have two extra properties

1. ItemCssClass          //class attribute for non-selected item
2. ItemSelectedCssClass  //class attribute for selected class

Modify the Render() method to assign these attributes to selected or non-selected radio button’s <li>.

Here is the complete code snippet

[ToolboxData("<{0}:ULRadioButtonList runat=server></{0}:ULRadioButtonList>")]
public class ULRadioButtonList : RadioButtonList
{
public string ItemCssClass { get; set; }
public string ItemSelectedCssClass { get; set; }

protected override void Render(HtmlTextWriter writer)
{

// We start our un-ordered list tag.
writer.WriteBeginTag("ul");

// If the CssClass property has been assigned, we will add
// the attribute here in our <ul> tag.
if (this.CssClass.Length > 0)
{
writer.WriteAttribute("class", this.CssClass);
}

// We need to close off the <ul> tag, but we are not ready
// to add the closing </ul> tag yet.
writer.Write(">");

// Now we will render each child item, which in this case
// would be our checkboxes.
for (int i = 0; i < this.Items.Count; i++)
{
// We start the <li> (list item) tag.
writer.WriteBeginTag("li");

//if the radio button is selected and
//ItemSelectedCssClass  property is set then add attribute of
//class with ItemSelectedCssClass
if (this.Items[i].Selected && !string.IsNullOrEmpty(ItemSelectedCssClass))
{
writer.WriteAttribute("class", this.ItemSelectedCssClass);

}

//add a normal item class if that has been set
else if (!string.IsNullOrEmpty(ItemCssClass))
{
writer.WriteAttribute("class", this.ItemCssClass);
}
writer.Write(">");

this.RenderItem(ListItemType.Item, i, new RepeatInfo(), writer);

// Close the list item tag.
writer.WriteEndTag("li");
}

// We finish off by closing our un-ordered list tag.
writer.WriteEndTag("ul");
}
}

Modify the web.config or user control’s .ascx file as

<%@ Register Assembly="Web.Helper" TagPrefix="myControls"  Namespace="Web.Helper.WebControls" %>

Add your customised ULRadioButtonList as

<myControls:ULRadioButtonList runat="server"  CssClass="list" ID="myRadioButtonList" />

Bind the DataSource property as

myRadioButtonList.DataSource = myItems; myRadioButtonList.DataBind();

Remove any build errors, deploy and view source of the page

If you have read so far, your wish has also been completed

Cheers !