Rebuild index of complete database

for maintaining performance of queries we need to rebuild indexes so below is the way we can use to rebuild all indexes of complete database so we need not to rebuild indexes of all tables one by one.

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Solution By: Rajesh Rolen

Read More

Remove Scripts from database/ Remove Sql Injections

USE [DBFORUMSNEW]
GO
/****** Object:  StoredProcedure [dbo].[sp_sqlinjection]    Script Date: 11/26/2010 19:46:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_sqlinjection]  
@DBName varchar(100)  
as  
Begin  
declare @DB_Name varchar(40), @Table_Name varchar(40),@Column_Name varchar(40)  
declare @sql1 varchar(1000), @sql2  varchar(1000)
Set @DB_Name  = @DBName  
begin  
exec ('use ' + @Db_Name)  
 DECLARE Table_Cursor CURSOR FOR SELECT name from sysobjects where type= 'U'  
 OPEN Table_Cursor  
 FETCH NEXT FROM Table_Cursor into @Table_Name  
 WHILE @@FETCH_STATUS = 0  
 begin  
  declare Column_Cursor CURSOR for select name from syscolumns where id = object_id(@Table_Name) and xtype in (239,175,231,167) and length  > 20  
  open Column_Cursor  
  Fetch next from Column_Cursor into @Column_Name  
  WHILE @@FETCH_STATUS = 0  
  begin  
  set @sql1='if exists (SELECT 1 FROM ['+@Table_Name+'] where ['+@Column_Name+'] like ''%< script%'')  
                begin
                update ['+@Table_Name+'] set ['+@Column_Name+'] = replace(['+@Column_Name+'],  
substring(['+@Column_Name+'],charindex(''< script'',['+@Column_Name+']),  
case when charindex(''< /script'',['+@Column_Name+']) >charindex(''< script'',['+@Column_Name+']) then  
charindex(''< /script'',['+@Column_Name+'])-charindex(''< script'',['+@Column_Name+'])+9  
else   
len (['+@Column_Name+'])  
end ),  
'''') where ['+@Column_Name+'] like ''%< script%''
 end '     
   exec (@sql1)  
     set @sql2='if exists (SELECT 1 FROM ['+@Table_Name+'] where ['+@Column_Name+'] like ''%< title%'')  
                begin
                update ['+@Table_Name+'] set ['+@Column_Name+'] = replace(['+@Column_Name+'],  
substring(['+@Column_Name+'],charindex(''< title'',['+@Column_Name+']),  
case when charindex(''< /title'',['+@Column_Name+']) >charindex(''< title'',['+@Column_Name+']) then  
charindex(''< /title'',['+@Column_Name+'])-charindex(''< title'',['+@Column_Name+'])+9  
else   
len (['+@Column_Name+'])  
end ),  
'''') where ['+@Column_Name+'] like ''%< title%''
 end '     
   exec (@sql2)  
   FETCH NEXT FROM Column_Cursor into @Column_Name  
  end  
         close Column_Cursor  
  DEALLOCATE Column_Cursor  
         FETCH NEXT FROM Table_Cursor into @Table_Name  
 end  
 CLOSE Table_Cursor  
 DEALLOCATE Table_Cursor  
 --FETCH NEXT FROM DB_Cursor into @DB_Name  
end  
end

Solution By: Rajesh Rolen

Read More

What is Bookmarklet / what is use of Bookmarklet

Each bookmarklet is a tiny program (a JavaScript application) contained in a bookmark (the URL is a "javascript:" URL) which can be saved and used the same way you use normal bookmarks. The idea was suggested in the Netscape JavaScript Guide.

JavaScript has been used by page authors on millions of webpages; Bookmarklets allow anybody to use JavaScript - on whatever page you choose (not just your own page).

so A bookmarklet is an applet, a small computer application, stored as the URL of a bookmark in a web browser or as a hyperlink on a web page. The term is a portmanteau of the terms bookmark and applet. Whether bookmarklet utilities are stored as bookmarks or hyperlinks, they are designed to add one-click functionality to a browser or web page. When clicked, a bookmarklet performs some function, one of a wide variety such as a search query or data extraction. Usually the applet is a JavaScript program.

Web browsers use URIs for the href attribute of the < a > tag and for bookmarks. The URI scheme, such as http:, file:, or ftp:, specifies the protocol and required form for the rest of the string. Browsers also implement a prefix javascript: that to a parser is just like any other URI. Internally, the browser sees that the protocol is javascript, treats the rest of the string as javascript code which is then executed, and uses the resulting string as the new page.

The executing script has access to the current page, which it may inspect and change. If the script returns an undefined type (rather than, say, a string), the browser will not load a new page, with the result that the script simply runs against the current page content. This permits in-place font size and color changes, for example, without a page reload.

An anonymous function can be used to force the script to return an undefined type:

javascript:(function(){
/* Statements returning a non-undefined type, e.g. assignments */
})();


Bookmarklets are saved and used as normal bookmarks. As such, they are simple "one-click" tools which add functionality to the browser. For example, they can:

- Modify the appearance of a web page within the browser (e.g., change font size, background color, etc.).
- Extract data from a web page (e.g., hyperlinks, images, text, etc.).
- Submit the current page to a blogging service such as Posterous, link-shortening service such as su.pr, or bookmarking service such as Delicious.
- Query a search engine, with search term(s) provided by previously selected text, or by a dialog box.
- Submit the current page to a link validation service, or translation service.
- Set commonly chosen configuration options when the page itself provides no way to do this.

Bookmarklets are safer than traditional software for the following reasons:

- They are extremely simple. With only a few lines of code it's hard to make a programming error that isn't detected immediately.

- You don't install software. Traditional software programs can produce conflicts with other programs on your hard drive. Bookmarklets work entirely within your web browser, so they can't independently interfere with the functioning of other programs.

- Even if something goes wrong (say, you try to use a Netscape-only bookmarklet on Internet Explorer) the worst thing that is likely to happen is that you will get a JavaScript error message. Furthermore, this site has been designed (through server-side scripting) to make it impossible for you to get a bookmarklet that doesn't work on your browser.

- Because you don't install software, you don't have the security risks of traditional software (which can install files all over your hard drive). Your hard drive is protected by JavaScript security restrictions.

Read More

FileUploader in asp.net MVC2

as we all know ASP.NET MVC sits on top of ASP.NET. That means ASP.NET MVC didn't do any special work for File Upload support. It uses whatever stuff is built into ASP.NET itself.

So the core process is still same for file upload:
below is code for view

Code for how to Upload file in ASP.NET MVC2

< %@ Page Title="" Language="C#" 
  MasterPageFile="~/Views/Shared/Site.Master" 
  Inherits="System.Web.Mvc.ViewPage" % >
 
< asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server" >
            FileUpload
< /asp:Content >
< asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server" >
    < h2 >FileUpload< /h2 >
    
     < % using (Html.BeginForm("FileUpload", "FileUpload", 
                    FormMethod.Post, new { enctype = "multipart/form-data" }))
        {% >
        < input name="uploadFile" type="file" / >
        < input type="submit" value="Upload File" / >
< %} % >
 
< /asp:Content >


//and this is code for controller class:
[HandleError]
public class FileUploadController : Controller
{
    public ActionResult FileUpload()
    {
        return View();
    }

    [AcceptVerbs(HttpVerbs.Post)]
    public ActionResult FileUpload(HttpPostedFileBase uploadFile)
    {
        if (uploadFile.ContentLength > 0)
        {
            string filePath = Path.Combine(HttpContext.Server.MapPath("../Uploads"), 
                                           Path.GetFileName(uploadFile.FileName));
            uploadFile.SaveAs(filePath);
        }
        return View();
    }
}

How to Upload file in ASP.NET MVC2 by Rajesh Rolen

Read More

difference between exec and sp_executeSql

both are used for executing dynamic sql in sql server.
both are not preferred because, If we are using dynamic sql in stored procedure, SQL Server may not use the execution plan. It will recreate the execution plan every time with different string of SQL.So, we have to think about the performance while using dynamic sql.

Main Difference:

Exec is non parametrized
sp_executeSql is parametrized

the main difference between above two is difference in performance.
the for Exec the execution plan is created every time it gets executed where as with sp_executeSql the same execution plan (will try) will be used only its value will be changed.

but if both's queries are getting changed on every call than there is no difference between them. but even though the plus point is with sp_executeSql that if their comes same query to it than it use same execution plan with different parameter and perform operation faster with compare to Exec.

Example:

DECLARE @ItemID INT
DECLARE @Query NVARCHAR(200)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = '

SET @ItemID = 1
EXEC( @Query + @ItemID)

SET @ItemID = 2
EXEC( @Query + @ItemID)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = @ID'

SET @ItemID = 1
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID -- this will be faster because its only value will be changed and will use same execution plan.

Solution By:

Rajesh Rolen

Read More

what is cls compliant code

cls compliant is mostly used when we wants our dll or any other code to be used by other language which are supported by .net.
lets say we wants to create a library in c# which we wants to be use in c# , vb.net , visual c++ or any other such languages. so for that we will have to create it as a cls compliant so that it can be consumed by all other .net supported language.

You can apply the CLSCompliant attribute on a class, an assembly (or a program element) and have the compiler check if your code is CLS (Common Language System) compliant. This means it works properly when consumed by other .NET languages. For example, you can place the following attribute on your .NET AssemblyInfo.cs files:

[assembly: CLSCompliant(true)]

Some of the things the compiler checks:

Class and member names cannot differ only by case. For example, you can't have one property named Counter and another named counter. This is important for cross-language compatibility since VB .NET isn't case sensitive.

Overloaded class methods cannot differ only by out or ref parameter designations.

Publicly exposed members cannot start with an underscore ( _ ).

Operators can't be overloaded

Unsigned types can't be part of the public interface of a class

Unfortunately, although you can apply the CLSCompliant attribute in VB .NET, the VB .NET compiler doesn't check for CLS compliance. In VB.NET 2005, this has apparently been fixed.


basic rules that should be followed when writing a CLS complaint C# code.

1. Unsigned types should not be part of the public interface of the class. What this means is public fields should not have unsigned types like uint or ulong, public methods should not return unsigned types, parameters passed to public function should not have unsigned types. However unsigned types can be part of private members.

2. Unsafe types like pointers should not be used with public members. However they can be used with private members.

3. Class names and member names should not differ only based on their case. For example we cannot have two methods named MyMethod and MYMETHOD.

4. Only properties and methods may be overloaded, Operators should not be overloaded.

The above-mentioned rules should be followed only for the types and member that are publicly exposed by your program. Private classes, private methods and local variables need to follow the rules.

By default the C# complier does not check for CLS compliance of the code. We should explicitly make the C# compiler check for CLS compliance by using the CLSCompliantAttribute class. By specifying the value of true to this attribute we specify that the C# compiler should check for the CLS compliance of the code. The CLSCompliantAttribute can be applied to assemblies, modules, types, and members.

For marking an entire assembly as CLS compliant the following syntax is used

using System;
[assembly:CLSCompliant(true)]

For marking a particular method as CLS compliant the following syntax is used

[CLSCompliant(true)]
public void MyMethod()

Example

using System;
//setting CLSCompliant attribute to true
[assembly:CLSCompliant(true)]
[CLSCompliant(true)]
public class Test
{
    public void MyMethod()
    {
    }
    //error because methods differ only in their case
    public void MYMETHOD()
    {
    }
    static void Main()
    {
    }
}

Read More

How to refresh view when table structure got changed

Lets we have got a table named tblstudent which contains following fields

studentRNo, studentName, StudentAddress

now we have create a view on it like:

create view viewName
as
select * from tblstudent

now when we will perform select operation on view then it will show us columns:

select * from viewName

output:
studentRNo, studentName, StudentAddress

now we have added a new column to tblstudent named 'studentFee'

alter table tblstudent add StudentFee int

now when we will run select operation on view again like:

select * from viewName

it will show use only those three columns which was exists while creating view:

select * from viewName

output:
studentRNo, studentName, StudentAddress

so as we are using '*' in query so it should show the newly added column in result but it will not show it. so to get desired result you will have to use a system storedprocedure 'sp_refreshView'

sp_refreshView 'ViewName'

and its done.

select * from viewName

output:
studentRNo, studentName, StudentAddress, studentFee

Solution by

Rajesh Rolen

Read More

Gridview with master-slave data

some times we wants to show gridview with master-child data. so that on click of master the details should be shown in child.
To do so here is a very good article:

http://www.progtalk.com/viewarticle.aspx?articleid=54

Read More