How to detach all user databases in sql server

-- Script to Detach All User Databases
 
SET NOCOUNT ON
 
DECLARE 
   @dbName     varchar(80),
   @ServerName varchar(20)
 
SELECT @ServerName = @@servername
 
DECLARE dbCursor CURSOR FOR 
   SELECT name 
   FROM master.dbo.sysdatabases 
   WHERE name NOT IN ( 'model', 'master', 'msdb', 'tempdb', 'distribution', 'repldata' )
 
OPEN dbCursor 
 
FETCH NEXT FROM dbCursor INTO @dbName
 
IF ( @@FETCH_STATUS <> 0 )
   PRINT 'No User databases found!!!'
   
WHILE ( @@FETCH_STATUS = 0 )
   BEGIN
      DECLARE @SQLStr varchar(8000) 
      SET @SQLStr = 
        'DECLARE 
            @SPIDStr    varchar(8000),
            @ConnKilled smallint
         SELECT 
            @ConnKilled = 0
            @SPIDStr    = ''''
         SELECT @SPIDStr = coalesce( @SPIDStr, '', '' ) + ''KILL '' + convert( varchar, spid ) + ''; ''
         FROM master.dbo.sysprocesses 
         WHERE dbid = db_id( ''' + @dbName + ''' )
         IF LEN( @SPIDStr ) > 0 
            BEGIN
               EXECUTE( @SPIDStr )
               SELECT @ConnKilled = COUNT(1)
               FROM master..sysprocesses 
               WHERE dbid = db_id( ''' + @dbName + ''' )
            END' + char(10) + ';' + char(10) + 
        'EXECUTE sp_detach_db ' + @dbName
      EXECUTE ( @SQLStr )
      PRINT 'Detach of ' + upper( @dbName ) + ' Database Successfully Completed'
      PRINT ''
      FETCH NEXT FROM dbCursor INTO @dbName
   END
   
CLOSE dbCursor
DEALLOCATE dbCursor
 
PRINT ' '
PRINT upper( @ServerName ) + ' --> All User Databases Successfully Detached'

Another method:-

set nocount on
declare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','alert_db','mssecurity')
open rs_cursor 
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0 
   PRINT 'No database to backup...Please check your script!!!'
WHILE @@FETCH_STATUS = 0
BEGIN
  print 'sp_detach_db ' +  @dbname
  print 'go'
  print 'print ''Detach of ' + upper(@dbname) + ' database successfully completed'''
  print 'go'
  PRINT ' '
  FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
print ' '
print 'print ''SERVER NAME : ' + upper(@server_name) + '-->  All databases successfully detached'''

Bind TreeView in asp.net

.aspx page

<body>
    <form id="form1" runat="server">
    <div>
    <asp:TreeView ID="treeviwExample"  runat="server" ImageSet="Arrows">
            <HoverNodeStyle Font-Underline="True" ForeColor="#5555DD" />
            <NodeStyle Font-Names="Verdana" Font-Size="8pt" ForeColor="Black" HorizontalPadding="5px" NodeSpacing="0px" VerticalPadding="0px" />
            <ParentNodeStyle Font-Bold="False" />
            <SelectedNodeStyle Font-Underline="True" ForeColor="#5555DD" HorizontalPadding="0px" VerticalPadding="0px" />          
        </asp:TreeView>
    </div>
    </form>
</body>


.cs page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class TreeView : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con1"].ToString());
    SqlDataAdapter da, da2;
    SqlCommand cmd;
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindTreeViewControl();
        }
    }

        private void BindTreeViewControl()
        {
            try
            {
                DataSet ds = GetDataSet("Select id,name,pid from treeview");

                SqlDataAdapter da = new SqlDataAdapter("select * from treeview where id=1", con);
                DataTable dt = new DataTable();
                da.Fill(dt);
                //DataRow Rows = dt.Rows[0];
                //}
                //DataRow[] Rows = ds.Tables[0].Select("pid IS 3"); // Get all parents nodes
             
                    TreeNode root = new TreeNode(dt.Rows[0]["name"].ToString(), dt.Rows[0]["id"].ToString());
                    root.SelectAction = TreeNodeSelectAction.Expand;
                    CreateNode(root, ds.Tables[0]);
                    treeviwExample.Nodes.Add(root);
             
            } catch (Exception Ex) { throw Ex; }
        }

        public void CreateNode(TreeNode node , DataTable Dt)
        {
            DataRow[] Rows = Dt.Select("pid =" + node.Value);          
            if (Rows.Length == 0) { return; }
            for (int i = 0; i < Rows.Length; i++)
            {
                TreeNode Childnode = new TreeNode(Rows[i]["name"].ToString(), Rows[i]["id"].ToString());
                Childnode.SelectAction = TreeNodeSelectAction.Expand;
                node.ChildNodes.Add(Childnode);
                CreateNode(Childnode, Dt);
            }
        }
        private DataSet GetDataSet(string Query)
        {          
            DataSet Ds = new DataSet();
            try {
                string strCon = @"Data Source=ANIL-PC;Initial Catalog=anil;Integrated Security=True";
                SqlConnection Con = new SqlConnection(strCon);
                SqlDataAdapter Da = new SqlDataAdapter(Query, Con);
                Da.Fill(Ds);
            } catch (Exception) { }
            return Ds;
        }
   

}

Displaying mobile friendly page without changing the URL in ASP.NET with C# (NO CSS)

Increasing use of handheld devices such as Mobile, iPad, Tablet has made mobile friendly website a mandatory for any website or web applications. In this article, we will learn how to develop mobile friendly website easily without using jQuery or even screen specific CSS. All you need to know is ASP.NET with C# and all of it is done without changing the URL of the page.

 Download source code for Displaying mobile friendly page without changing the URL in ASP.NET with C# (NO CSS)

In general following approaches are followed to develop a mobile friendly website 

1. Create a separate website and redirect the user to mobile friendly website if user is coming from mobile devices
   - The problem with this approach is that user is redirected to different url that may create confusion to the user. Also the same content in two different url may confuse the SEO robots.
   
2. Create a page with CSS3 @media screen element that detects the screen width of the device and apply the CSS written for mobile devices (to decrease the width of the page or shifting right/left content to the bottom of the page).
   - The problem with this approach is that it loads the original bulky pages that was originally designed for Desktop but because user is coming from Mobile device so HTML elements are manipulated to fit into the screen.
   
3. Using jQuery that detects the mobile device request and manipulate the element that will fit the page in the screen.
   - This has again almost same problem that has been explained in point number 2.

All above three approaches has their own drawbacks and I personally do not feel comfortable using any of them being a pure server side programmer. 

  

Objective

In this article, we will learn following

1. Detecting mobile request using a plugin (simple, easy, reliable and free)
2. Sending mobile request to a mobile page developed for mobile screen (internally, without changing the URL)
3. Sending response to the users browser that is purely for mobile with no extra content

In all above process the URL doesn't change, so the user experience is surprisingly awesome.

Using the code

As written above, we are going to use a plugin that detects mobile request, so go ahead and visit this link http://51degrees.mobi/ and download the plugin (Once the download is complete, Add reference of FiftyOne.Foundation.dll into your project). Alternatively, use Manage NuGet Packages ... by right clicking the Project that automatically does all the back end work for you.

To demonstrate this example, I have created a sample project and my project solution looks like below. Where we have reference of the FiftyOne.Foundation.dll and six .aspx pages, 3 each for Desktop and Mobile.

The .aspx page name ends with "M" is for mobile. For example Page1.aspx is for Desktop and Page1M.aspx is for Mobile devices.

For all .aspx pages that is for desktop, we need to do a minor change in their code behind and that change is to inheritParentPage class from App_Code folder as shown below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : ParentPage
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
}

Now lets see ParentPage.cs code.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using FiftyOne.Foundation.Mobile.Detection;
/// <summary>
/// Summary description for ParentPage
/// </summary>
public class ParentPage : System.Web.UI.Page
{
    public ParentPage()
    {
        string screen = string.Empty;

        bool isMobile = this.Context.Request.Browser["IsMobile"] == "True";

        if (isMobile) // if coming from Mobile device
        {
            screen = "handheld";
            SetScreenSpecificPageSettings(screen);
        }
        else // if user has selected to view mobile version from Desktop
        {
            if (this.Context.Request.Cookies["screen"] != null || !string.IsNullOrWhiteSpace(this.Context.Request.QueryString["screen"]))
            {
                if (!string.IsNullOrWhiteSpace(this.Context.Request.QueryString["screen"]))
                {
                    screen = this.Context.Request.QueryString["screen"];
                    // create the cookies for subsequent request
                    this.Context.Response.Cookies["screen"].Value = this.Context.Request.QueryString["screen"];
                }
                else if (this.Context.Request.Cookies["screen"] != null)
                {
                    screen = this.Context.Request.Cookies["screen"].Value;
                }
                SetScreenSpecificPageSettings(screen);
            }
        }
    }

    private void SetScreenSpecificPageSettings(string screen)
    {
        if (screen.Equals("handheld", StringComparison.CurrentCultureIgnoreCase)) // if coming to Mobile screen, redirect to mobile page
        {
            // frame the mobile page name for this page
            var pathWithFile = string.Empty;
            if (Page.RouteData.RouteHandler == null)
            {
                pathWithFile = this.Context.Request.Url.AbsolutePath;
            }
            else
            {
                pathWithFile = ((System.Web.Routing.PageRouteHandler)Page.RouteData.RouteHandler).VirtualPath;
            }
            var extensionLength = Path.GetExtension(pathWithFile).Length;
            var mobilePage = pathWithFile.Substring(0, (pathWithFile.Length - extensionLength)) + "M" + Path.GetExtension(pathWithFile);

            var physicalFile = Server.MapPath(mobilePage);

            if (File.Exists(physicalFile)) // transfer only when the file exists for the Handheld device
            {
                this.Context.Server.Transfer(mobilePage + this.Context.Request.Url.Query, true);
            }
        }
    }
}

In the above code snippet, we have added two extra namespaces and they are following

1. System.IO - to get the file extension
2. FiftyOne.Foundation.Mobile.Detection - to detect whether request is coming from mobile device 

In the constructor of the ParentPage first we are checking for this.Context.Request.Browser["IsMobile"] that works only when FiftyOne namespace is added and corresponding .dll is referenced into the project.

If the request is from mobile, we are setting the screen variable  value to "handheld" and passing it to another function named "SetScreenSpecificPageSettings" else we are checking for the screen Cookies or querystring. If anyone of them are there then going to another condition.

If querystring is there then we are retrieving the value of querystring and setting to the screen variable and setting screen cookies value else if screen cookie exists then retrieving the value of cookie and setting to thescreen variable.

We are setting the cookies to remember the user preference, in case user is on desktop and want to visit mobile version of the page. If you do not want this functionality, just remote this codes.

SetScreenSpecificPageSettings method

This method checks whether screen value is "handheld", if yes then sets the pathWithFile variable to the current url of the page. Gets the extension name of the page and then creates a string with mobile page name for the  requested page ("the page name ends with M") and then checks if that page exists. If it does then sends this mobile page as response to the client using Server.Transfer method.

How it works

In case user has browsed http://localhost:54082/Page1.aspx page from Desktop

Page1.aspx page is requested, as this page is inheriting ParentPage class so its constructor is called. As the request is coming from Desktop so isMobile is false and it checks whether user wants to visit the current page in mobile version (coming with screen=handheld querystring now or earlier he/she has opted for this choice that would have set cookies), if yes then sends mobile page (Page1M.aspx) content to the browser. If not, just renders the current page that is Page1.aspx.

In case user has browsed http://localhost:54082/Page1.aspx page from Mobile

In the ParentPage class constructor isMobile will be true and request will be sent to SetScreenSpecificPageSettings method that will send mobile page content (Page1M.aspx) to the browser.

Now here is my sample website project, when browsed from Desktop (Notice the URL in the address bar).

When browsed from Handheld (mobile) devices, notice the URL in the address bar.

Other advantages when we follow this approach

1. We can have separate MasterPages for Desktop and Mobile for total control, easy implementation and maintenance.
   
2. We can have separate style sheets for Desktop and Mobile to make them lightweight and load only those .css that is required for either Desktop or Mobile.
   
3. As Desktop and Mobile pages are two separate physical pages so we have complete control and total freedom to render the data in the way we want and write only code that is applicable for respective version of the page that ultimately makes it easy to load and perform better.
   
4. The URL doesn't change, not even querystring is needed to denote that whether to render a page for Desktop or Mobile. The link at the footer of the page is just to given an option to the user in case he/she wants to browser mobile version of the page on Desktop explicitly.

Conclusion

Developing a mobile friendly page in ASP.NET with C# was not that easy. Thanks to 51Degree.mobi for providing reliable, lightweight plugin that  makes our life easier.

Hope this article will be useful, thanks for reading and do let us know your comments.

Reference

http://51degrees.mobi/

EnableViewState vs ViewStateMode in asp.net 2010

With ASP.Net 4.0, a new property for View State was introduced, it was called ViewStateMode. Prior to ASP.Net 4.0, we used to use EnableViewState property. Now for an ASP.Net 4.0 application, both of these properties are available. So the question comes in which one we should use? And how are they different?

To answer the first question, you can use whichever you want, if it suits you.

Now lets see what the difference between these two.

ViewStateMode property allows you to Disable View State at parent level and Enable it selectively at child level.

          EnableViewState property does not allow this. Simple.

Both of these properties allow you to Enable view state at parent level and Disable it at child level.

Lets learn by example. This is how my foo.aspx page looks (using ASP.net 4.0)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="foo.aspx.cs" Inherits="WebApplication4.DemoPages.foo" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        <asp:Button ID="Button1" runat="server" Text="Button" />
    </div>
    </form>
</body>
</html>

At Page Load, I am setting label’s Text property to current date time stamp.

        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            {
                Label1.Text = DateTime.Now.ToString();
            }
        }

Now, if we run the application, this is how the foo.aspx page will look on browser.

First Request:

Post Back (button click):

So the label is showing the date time stamp as expected. Now if we click on the Button, page will be posted back and the result will be the same. Label is still showing the date time stamp but that is not the current time, instead, it is the time of the first request. Which means, the label text is getting populated from ViewState.

Now lets disable View State at the page level. Lets use ViewStateMode property. This is how foo.aspx page directive looks like.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="foo.aspx.cs" Inherits="WebApplication4.DemoPages.foo" 

ViewStateMode="Disabled" %>

Now, if I re-run the application, first time when the page loads, label will show current date time (which is expected). And on post back (clicking the button), we will not see the date time stamp any more. Reason? ViewState is disabled now.

First request after disabling View State at page level (ViewStateMode = Disabled)

Post Back:

So far so good. Till this point, there is almost no difference between the two properties ViewStateMode and EnableViewState. Now what if I want to enable view state, but only for the label. This is what we do.

<asp:Label ID="Label1" runat="server" Text="Label" ViewStateMode="Enabled"></asp:Label>

Now lets run the application once again.

Since we have enabled View Sstate at label, we would expect the date time stamp to persist during post back.

First request: At page level –> ViewStateMode=Disabled; at control level(label) –> ViewStateMode=Enabled

Post back

THIS is what you could not do using EnableViewState property. If you try to use EnableViewState, once view state is disabled at page you can not selectively enable it at controls.  Lets try it

I have changed my foo.aspx and to use EnableViewState now.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="foo.aspx.cs" Inherits="WebApplication4.DemoPages.foo" 

EnableViewState="false" %>

And at label:

<asp:Label ID="Label1" runat="server" Text="Label" EnableViewState="true" ></asp:Label>

So lets run the application.

First Request: at page level –> EnableViewState=False; at control (label) –> EnableViewState=True 

Post back:

So since the date time stamp does not persists, it indicates that View State is not enabled for label control. We can confirm this by enabling Tracing

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="foo.aspx.cs" Inherits="WebApplication4.DemoPages.foo" 

EnableViewState="false" Trace="true" %>

This is relevant trace result for the post back

As we see, the ViewState size is 0 for label.

Now a note of caution: While using EnableViewState, we can still Enable view state at parent level and Disable it at control level.

To summarize:

Using ViewStateMode: Disable view state at Parent, Enable it at child level –> Works well

                         Enable view state at Parent, Disable it at child level –> Works well

Using EnableViewState: Disable view state at parent, Enable it at child –> Does not work

                           Enable view state at parent, Disable it at child –> Works well

Bind Dropdownlist with XML File

Here I am explaining how to bind an XML fine to ASP.Net DropDownList control.

XML file

Below is my Cities.xml which I contains the list of some Indian cities.

<?xmlversion="1.0"encoding="utf-8" ?>

<cities>

 <city>

    <id>1</id>

    <name>Mumbai</name>

 </city>

 <city>

    <id>2</id>

    <name>Delhi</name>

 </city>

 <city>

    <id>3</id>

    <name>Chennai</name>

 </city>

 <city>

    <id>4</id>

    <name>Kolkatta</name>

 </city>

</cities>

The name of the City will be assigned to the Text part and the id will be assigned to the Value
 part of the ASP.Net DropDownList Control.

Namespace

You will need to import the following namespace.

C#

using System.Data;

Reading the XML file and Binding the DropDownList

Below is the method that reads the XML file into a Dataset object and then binds the same to the ASP.Net DropDownList Control

C#

private void BindXml()

{

    string filePath = Server.MapPath("~/Cities.xml");

    using (DataSet ds = new DataSet())

    {

        ds.ReadXml(filePath);

        ddlCities.DataSource = ds;

        ddlCities.DataTextField = "name";

        ddlCities.DataValueField = "id";

        ddlCities.DataBind();

    }

}

I am calling the above method in the Page Load event of the Page in the following way

C#

protected void Page_Load(object sender, EventArgs e)

{

    if (!IsPostBack)

    {

        this.BindXml();

    }

}