Enum Examples or How to Bind the DropDownList With Enum?

Enum is the type provided by dotnet, it stores constant value in it. When we want to specify some constant value to use in the project then it is very usefull for readability.

In this post I will show you how to declare an enum and how to use it in the program.
Here I have bind the enum to the dropdown list

Declare the enum as below

public enum Days { Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday }

In aspx page I have added DropDownList named ddlDays as below

 <asp:DropDownList ID="ddlDays" runat="server">
        </asp:DropDownList>

Now below is the code to bind dropdownlist control with above enum

// Add first item to the drop down list to select lable
        ddlDays.Items.Add("--Select Days--");

        string[] enumNames = Enum.GetNames(typeof(Days));
        foreach (string item in enumNames)
        {
            int value = (int)Enum.Parse(typeof(Days), item);
            ListItem listItem = new ListItem(item, value.ToString());
            //Add one by one item to the drop down list
            ddlDays.Items.Add(listItem);
        }
 

Out put is displayed in the side image.

Supported languages by SyntaxHighlighter

Syntax highlighter is a tool which will highlight the code in correct Syntax. When on your blog or web site you want to show some programming code then it is very useful.

In my next post I will show you how to add Syntax Highlighter in blogger or any webpage.

Below is the list of all the supported languages with their aliases.

Language	Aliases
C++	cpp, c, c++
C#	c#, c-sharp, csharp
CSS	css
Delphi	delphi, pascal
Java	java
Java Script	js, jscript, javascript
PHP	php
Python	py, python
Ruby	rb, ruby, rails, ror
Sql	sql
VB	vb, vb.net
XML/HTML	xml, html, xhtml, xslt

For more detail visit: http://code.google.com/p/syntaxhighlighter/wiki/Languages

Change cursor to hand on mouseover using jQuery

When ever we need to change the mouse cursor using JQuery then we can do it very easily.

In my today's post I will show you how can we change the mouse cursor to the Hand type cursor while user hover mouse on the particular element.
It is very simple just look at the below JQuery code.
   

$(document).ready(function() {
  $("#elementid").hover(function() {
    $(this).css("cursor", "hand");
  });
});

In above code 'elementid' is the name of the element on which you want a mouse pointer as a hand while hovering a mouse over it.

Happy Coding...

SQL SERVER – 2005/2008 – User Defined Function to Strip HTML String Without Regular Expression

Suppose sometimes we are saving HTML string into the database(SQL Server).
Now when we want to display some limited characters on the web page like first 200 char,
return string will not be proper HTML as it will leave some open HTML tag into it and hense it will not render HTML properely on the web page and also disturb our page html.
So to avoide this problem we can strip html from the sql server. I have written an SQL UDF for that as below.

Following UDF takes input as HTML and returns TEXT only.
If there is any single quotes in HTML then it must be replaced with two single quotes while passing to the function as a input.

Create Function [dbo].[StripHTML]
(@HTMLString VarChar(Max))
Returns Varchar(Max)
As
Begin
    Declare @StartChar Int
    Declare @EndChar Int
    Declare @Length Int
    Set @StartChar = CharIndex('<',@HTMLString)
    Set @EndChar = CharIndex('>',@HTMLString,CharIndex('<',@HTMLString))
    Set @Length = (@EndChar - @StartChar) + 1
    While @StartChar > 0 AND @EndChar > 0 AND @Length > 0
    Begin
        Set @HTMLString = Stuff(@HTMLString,@StartChar,@Length,'')
        Set @StartChar = CharIndex('<',@HTMLString)
        Set @EndChar = CharIndex('>',v,CharIndex('<',@HTMLString))
        Set @Length = (@EndChar - @StartChar) + 1
    End
    Return LTrim(RTrim(@HTMLString))
End
Go

Example:
Declare @HTMLString As VarChar(Max) = '<table class="tempwrapper"> '+
 '<tr><td colspan="3">.NET that will convert a string so that it can be eg I would like to convert the string &quot;a &lt; b&quot; '+
 'to &quot;a &#3C; b&quot;.</td> </tr> </table>'
 Select SUBSTRING(@HTMLString,0,100)
 Select SUBSTRING([dbo].[StripHTML](@HTMLString),0,100)
-> <table class="tempwrapper"> <tr><td colspan="3">.NET that will convert a string so that it can be e
-> .NET that will convert a string so that it can be eg I would like to convert the string &quot;a &lt

Here I have attached a screen shot of the UDF at action.

Happy Programming :-)

Script All the Stored Procedures, Views, Triggers and Functions from The Database

In my previous post I have show you how to generate database script using Wizard. In this article I will show you how to generate script for database object programmatically or using query

In SQL Server 2005/2008 Management studio you can script all the database object using ‘SQL Server Script Wizards’. But sometime we do not have the permission to generate a script using wizard then we are help less. But don’t worry now we have a solution for that as well. I will show you how to script all database objects programmatically.

To script All the Stored Procedures from the Database:

DECLARE @ScriptText As VARCHAR(MAX) = ''
DECLARE @AllScriptText As VARCHAR(MAX) = ''

DECLARE script_sp CURSOR FOR
SELECT SM.Definition
FROM SYS.SQL_Modules As SM INNER JOIN SYS.Objects As Obj
ON SM.Object_ID = Obj.Object_ID WHERE Obj.Type = 'P'
OPEN script_sp

FETCH NEXT FROM script_sp INTO @ScriptText

WHILE @@FETCH_STATUS = 0
BEGIN
        --Set @AllScriptText =  @AllScriptText + 'go' +@ScriptText;
        Print @ScriptText;
        FETCH NEXT FROM script_sp INTO @ScriptText
END
--Select @AllScriptText;
CLOSE script_sp
DEALLOCATE script_sp

But Remember if you have created all the procedure with encryption then you will get the null value.

 Now suppose you want all the views as a script then use below query,

DECLARE @ScriptText As VARCHAR(MAX) = ''
DECLARE @AllScriptText As VARCHAR(MAX) = ''

DECLARE script_sp CURSOR FOR
SELECT SM.Definition
FROM SYS.SQL_Modules As SM INNER JOIN SYS.Objects As Obj
ON SM.Object_ID = Obj.Object_ID WHERE Obj.Type = 'V'
OPEN script_sp

FETCH NEXT FROM script_sp INTO @ScriptText

WHILE @@FETCH_STATUS = 0
BEGIN
        --Set @AllScriptText =  @AllScriptText + 'go' +@ScriptText;
        Print @ScriptText;
        FETCH NEXT FROM script_sp INTO @ScriptText
END
--Select @AllScriptText;
CLOSE script_sp
DEALLOCATE script_sp

Now suppose you want all the functions as a script then use below query,

DECLARE @ScriptText As VARCHAR(MAX) = ''
DECLARE @AllScriptText As VARCHAR(MAX) = ''

DECLARE script_sp CURSOR FOR
SELECT SM.Definition
FROM SYS.SQL_Modules As SM INNER JOIN SYS.Objects As Obj
ON SM.Object_ID = Obj.Object_ID WHERE Obj.Type = 'FN'
OPEN script_sp

FETCH NEXT FROM script_sp INTO @ScriptText

WHILE @@FETCH_STATUS = 0
BEGIN
        --Set @AllScriptText =  @AllScriptText + 'go' +@ScriptText;
        Print @ScriptText;
        FETCH NEXT FROM script_sp INTO @ScriptText
END
--Select @AllScriptText;
CLOSE script_sp
DEALLOCATE script_sp

If you need to generate a script for all the triggers from the database then we have to make little bit changes in query. We will join sys.triggers table in place of sys.objects as below.

DECLARE @ScriptText As VARCHAR(MAX) = ''
DECLARE @AllScriptText As VARCHAR(MAX) = ''

DECLARE script_sp CURSOR FOR
SELECT SM.Definition
FROM SYS.SQL_Modules As SM INNER JOIN SYS.triggers As Obj
ON SM.Object_ID = Obj.Object_ID
OPEN script_sp

FETCH NEXT FROM script_sp INTO @ScriptText

WHILE @@FETCH_STATUS = 0
BEGIN
        --Set @AllScriptText =  @AllScriptText + 'go' +@ScriptText;
        Print @ScriptText;
        FETCH NEXT FROM script_sp INTO @ScriptText
END
--Select @AllScriptText;
CLOSE script_sp
DEALLOCATE script_sp

Happy Scripting…….

SQL Server – 2008 – Generate Script of Database, Generate script to copy whole database schemas and All other object in database, like Tables with data, SPs, Views, Triggers, Functions, Database constraints etc.

Check how to create a script using T-SQL

While we are working on any project and uses SQL Server as a database, we often need to copy database schema either with data or without data to create database on other server. So it inspires me to write down the blog post about ‘how to generate SQL script using SQL Server Management Studio’

Here I will explain step by step process with screen shot that how we can copy database schema with all the database objects.

First of all open SQL Server Management Studio and expand databases node like below images step 1.

Step 1

Now select the database for which you want to generate SQL Script as like below image Step 2. Here I have selected pubs database for demo. Right click on selected database, click on Tasks option and then click Generate Scripts...  I have marked option with red circle in image.

Step 2

Once you click on Generate Scripts option from above image it will show up the below image step 3

Just click on the next button from below image step 3

Step 3

Now in below image you can see the list of all the available databases, by default on which database you have right clicked it is selected but you can change here if you want to generate script for other database. In Step 4 image there is one check box labelled with ‘Script all objects in the selected database’.  Check this check box if you want to generate script for all the objects from database, if you will not check this check box it will ask on next step for database object selection.

  

Step 4

After selecting the database and checking check box it will show up the below image step 5.

Here you can select what you want to include in script, like script table with data or without data, you want to add script like if exist then drop the object first then create it etc. For scripting table with all the data select true in script data option as I have selected in below image. Then click on next button.

Step 5

Now this step 6 has some output file type and file location related options. I have selected 3^rd radio button in below image for generating a script in New Query Window but if you don’t want to generate script in new query window and want to generate script into clipboard then select 2^nd radio button labelled with Script to Clipboard. If you want to generate script in a file then select 1^st radio button Script to file, once you select this option it will ask file name and directory to save the file on hard drive.

I have marked files to generate option as a 3 and then 3.1 and 3.2 in the red colour. If you want to generate whole script in one single file then select single file (3.1), by default it is selected. If you want to generate different file per object then select 3.2 option. If you select 3.2 option and suppose you have 10 tables in database then it will generate 10 different SQL file.

Step 6

Step 7 below image is just a summary page, showing all the options you have selected till step. Just click on the finish button to proceed.

Step 7

Step 8 showing the progress for generating database script

Step 8

Step 9 showing the successfully completion of script generation process.

Step 9

Step 10 shows that all database object has been scripted in new query window for pubs database. Now your script is ready...

Step 10

Please let me know if you have any question on this script generation wizard.