There are one very common interview question related in sequence of event firing when we are using master page, content page and user control. So if we don’t see actual example than there are lots of confusion that’s why I show this with example sample code.

In this example I am taking 1 Master page, 1 Content page, 1 User control on master page and 1 User control on content page and check mainly the Init and load event of each one.

First of all I show you the code what I have written in which page step by step.

Step 1- First create 1 Master page as follow

Step 2- 2^nd creating 1 Content page as follow

 

Step 3- Create one User control for master page

 

Step 4- Creating another user control for content page as follows

 

You can see in all page code file written init a load event and user control contain another button on it.

After that when I run this project the output give clear concept which will fire first or you can say the compete sequence of the event firing in all these case.

Output as follow

 

 

After the entire event the button control fire when click on that button.

 

 You can download complete project from here and simply run that you will see everything...

MasterPageConcept.rar (26.92 kb)

This is very common question in interview, so I try to implement the sample one for the same. Declare two interface IPaint1 and IPaint2 which both having method Paint() and now inherit both the interface in class TestClass. You can see we implement only once time method Paint() in that because the same signature with same name. This is also called Implicitly implementation of the interface

 

Eg

 

        interfaceIPaint1

        {

            string Paint();

        }

        interfaceIPaint2

        {

            string Paint();

        }

        classTestClass : IPaint1,IPaint2

        {

            // Both IPaint1.Paint and IPaint2.Paint call this same method.  It means due to same signature no need to implement separately.

            publicstring Paint()

            {

                return"Paint method";

            }

        }

 

protectedvoid Page_Load(object sender, EventArgs e)

        {

            TestClass sc = newTestClass();

            IPaint1 P1 = (IPaint1)sc;

            IPaint2 P2 = (IPaint2)sc;

 

            // The following lines all call the same method.

            lblTest.Text = sc.Paint();

            lblTest.Text += "<br>" + P1.Paint();

            lblTest.Text += "<br>" + P2.Paint();

 

        }

 

// Output is on label as follows:

       Paint method

       Paint method

       Paint method

 

Also another way Implement Interface Explicitly - When you choose this option, you need to define method names that interface have by prefixing the interface name. If you are using public for explicitly implementation than it giving error “The modifier 'public' is not valid for this item”. No access modifier for this method implementation so by default its scope will be internal.

interfaceIPaint1

        {

            string Paint();

        }

        interfaceIPaint2

        {

            string Paint();

        }

        classTestClass : IPaint1,IPaint2

        {

            // Both IPaint1.Paint and IPaint2.Paint call this same method.  It means due to same signature no need to implement seprately.

            stringIPaint1.Paint()

            {

                return"Paint method from IPaint1";

            }

            stringIPaint2.Paint()

            {

                return"Paint method from IPaint2";

            }

            publicstring Paint()

            {

                return"Paint method from TestClass";

            }

        }

TestClass sc = newTestClass();

IPaint1 P1 = newTestClass();

IPaint2 P2 = newTestClass();

// The following lines all call the same method.

lblTest.Text = sc.Paint();

lblTest.Text += "<br>" + P1.Paint();

lblTest.Text += "<br>" + P2.Paint();

 

Output is as follows:

        Paint method from TestClass

        Paint method from IPaint1

        Paint method from IPaint2

Today I got on question to write the sql query to get the entire table which contains a specific value in any of that column in those columns. e.g.(Suppose developer have one contact number and one database, so developer want to get all table that contain the specific contact number in any of the columns).

I wrote on stored procedure to get all tables with associated column which contain that value. You just simply call this stored procedure and passing the search value (e.g. contact number).

 

 

-- =============================================

-- Author:        <Arvind Kumar>

-- Create date: <29-Apr-2013>

-- Description:   <Description,,>

-- EXEC getAllTablesWhichContainAValue 'smit'

-- =============================================

Create PROC getAllTablesWhichContainAValue

(

      @StrVal nvarchar(1000)

)

AS

BEGIN

      --First Create temp table to get the column name

      CREATE TABLE #temp

      (

            ColumnName nvarchar(1000),

            ColumnValue nvarchar(MAX)

      )

 

     

      DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchFinal nvarchar(110)

      SET  @TableName = ''

      --here build the final search string

      SET @SearchFinal = QUOTENAME('%' + @StrVal + '%','''')

 

      WHILE @TableName IS NOT NULL

      BEGIN

            SET @ColumnName = ''

            SET @TableName =

            (

                  SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

                  FROM INFORMATION_SCHEMA.TABLES

                  WHERE             TABLE_TYPE = 'BASE TABLE'

                        AND   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

                        AND   OBJECTPROPERTY(

                                    OBJECT_ID(

                                          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

                                           ), 'IsMSShipped'

                                             ) = 0

            )

 

            WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

            BEGIN

                  SET @ColumnName =

                  (

                        SELECT MIN(QUOTENAME(COLUMN_NAME))

                        FROM INFORMATION_SCHEMA.COLUMNS

                        WHERE             TABLE_SCHEMA      = PARSENAME(@TableName, 2)

                              AND   TABLE_NAME  = PARSENAME(@TableName, 1)

                              AND   DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

                              AND   QUOTENAME(COLUMN_NAME) > @ColumnName

                  )

 

                  IF @ColumnName IS NOT NULL

                  BEGIN

                        INSERT INTO #temp

                        EXEC

                        (

                              'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000)

                              FROM ' + @TableName + ' (NOLOCK) ' +

                              ' WHERE ' + @ColumnName + ' LIKE ' + @SearchFinal

                        )

                  END

            END  

      END

 

      SELECT ColumnName, ColumnValue FROM #temp

END

 

Run above stored proc to get all table which contain the contact number 9810022233

EXEC getAllTablesWhichContainAValue '9810022233'

 

Output

ColumnName                         ColumnValue

[dbo].[NameOfTable].[Mob]   9810022233, 9899898898

[dbo].[TempTable].[Contact]   9810022233

 

For Reference- see these links

http://msdn.microsoft.com/en-in/library/ms176114.aspx

Today, someone asked me how can find those stored procedure which contain a table and specially he want to know those stored procedure which contain specific column of a table. So I write this query and sharing here also.

We can get such list of stored procedure by using either sys.objects or sys.procedures

 SELECT * FROM sys.procedures

 WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

 GO

 SELECT * FROM sys.objects

 WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

 If you want to get all sp those contain a specific column of table

 SELECT * FROM sys.procedures

 WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

 AND  OBJECT_DEFINITION(OBJECT_ID) LIKE '%Notes%'

 GO

 SELECT * FROM sys.objects

 WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

 AND  OBJECT_DEFINITION(OBJECT_ID) LIKE '%Notes%'

Enojoy it....

 

Now I start the asp.net MVC step by step and my focus is practical scenario so we will be able to develop real time application finally. So here first I discuss what is MVC? Why we move asp.net code behind model to MVC? Etc.

Model–view–controller (MVC) is a software architecture pattern which separates the representation of information from the user's interaction. MVC design pattern has mainly three parts.

1.Controller-Controllers are the components that handle user interaction, work with the model, and finally select a view to render that displays UI. Or In simple word you can say it is connecting/communicating Model and View.

2.Model-Model objects mainly retrieve and store model state in a database. The high level class associated with Model that does not contain any information about the user interface.

3.View- A view requests from the model the information that it needs to generate an user interface.

Now I come on point why MVC? Developers already familiar with asp.net code behind model and that is also good. The main two things because of that developer move asp.net code behind to new MVC model. Microsoft divided the code behind page and UI separate but in that case also there are very difficult to decuple both for mainly unit testing, the ASP.NET code-behind is completely tied up with the ASP.NET HttpContext object. Second thing is even code behind and UI page are separated into two different file but one can’t exists without other.eg if developer want to use button click event that always required both .aspx and .cs page.

So now the new concept comes as MVC. In this design pattern the code behind things simply moved into controller and user request always first hit the controller, the controller class than invoke the model and attaches the model to view for the UI. Controller is simple .net class and so that will easily reuse and perform unit testing easily.

Here just see few advantages and disadvantages of MVC over old Code behind model.

Advantages-

1.This model easily manages complexity by dividing an application into three different parts Model, View and Controller.

2.It supports multiple views using same model.

3.It better support for unit testing in Visual studio 2008 onward.

4.One is most important thing, in old asp.net page view state stores all the data rendered and final HTML gets too large so we all notices the load time delay. The new MVC doesn't have view state concept so it is faster.

5.Client caching is available now with the help of Silverlight. By integrating Silverlight, we can take advantage of it.

6.Clear separation of input logic, business logic and Ui logic.

 

   When design new pattern that also facing some challenges, so you may face these challenges when using MVC framework.

1.If you are using Silverlight part of it, the data access is limited to Web Services/WCF/ADO.NET Data Services.  You cannot make direct calls via ADO.NET or stored procedures to a database.

2.If you have frequent changes in your database, you may find it annoying to keep building application again and again.

 

Reference link-

 http://msdn.microsoft.com/en-us/library/ff649643.aspx

 

 

 

Just quick code for copy file from one folder to another folder using sql server tsql script.

You can use xp_cmdshell command to copy file as follows

Declare @SqlCopy varchar(2000)

DECLARE @FileName varchar(500)

Declare @OutputFolderPath varchar(500)

set @OutputFolderPath = 'C:\targetfoldername'

Set @ZipFileName = 'C:\sourcefolder\filename.txt'

 

SET @SqlCopy =  ' COPY /Y ' + @ZipFileName + ' /B ' + @OutputFolderPath

print @SqlCopy

EXEC master..xp_cmdshell @SqlCopy

 

Enjoy!

I already posted my old article regarding the creating zip/compressed file using sql server here- http://blog.akumars.esoftera.in/post/2012/05/17/Create-zip-file-in-stored-procedure-in-Sql-server.aspx

And http://blog.akumars.esoftera.in/post/2012/05/17/Create-zip-or-rar-file-in-stored-procedure-in-Sql-server.aspx

But some body asks me for that will be done using vbScript, so I also tried to create vbscript .vbs file for compressed file. You can call that .vbs file either from command line directly or call from stored procedure its users choice, with this article I also try to figure out how can working vbScript and that can be run from sql server.

Step 1- Just open a notepad and copy this below vbscript into and save with .vbs, so you will get the .vbs file created.

Option Explicit

 

Dim arrResult

Dim folder

Dim ZipFile

 

folder = WScript.Arguments.Item(0)

ZipFile = WScript.Arguments.Item(1)

'Here WScript.Arguments using for geting parameter value from command line

'or from sql server, we can get any number of parameter using index

'Arguments for folder path  should be fully qualified path

'If zip file exist it overwritten not appended and empty folder will be skipped

arrResult = ZipFolder( folder,ZipFile)

If arrResult(0) = 0 Then

    If arrResult(1) = 1 Then

        WScript.Echo "Done with at least 1 empty folder was skipped."

    Else

        WScript.Echo "Done with " & arrResult(1) & " empty subfolders were skipped."

    End If

Else

    WScript.Echo "ERROR " & Join( arrResult, vbCrLf )

End If

 

 

Function ZipFolder( myFolder, myZipFile )

' This function recursively zip all file from specified folder into a single ZIP file

    Dim intSkipped, intSrcItems

    Dim objApp, objFolder, objFSO, objItem, objTxt

    Dim strSkipped

 

    Const ForWriting = 2

 

    intSkipped = 0

 

    ' Make sure the path ends with a backslash

    If Right( myFolder, 1 ) <> "\" Then

        myFolder = myFolder & "\"

    End If

 

    ' Use custom error handling

    On Error Resume Next

 

    ' Create an empty ZIP file here using Scripting.FileSystemObject

    Set objFSO = CreateObject( "Scripting.FileSystemObject" )

    Set objTxt = objFSO.OpenTextFile( myZipFile, ForWriting, True )

    objTxt.Write "PK" & Chr(5) & Chr(6) & String( 18, Chr(0) )

    objTxt.Close

    Set objTxt = Nothing

 

    ' Abort if errors occurd

    If Err Then

        ZipFolder = Array( Err.Number, Err.Source, Err.Description )

        Err.Clear

        On Error Goto 0

        Exit Function

    End If

   

    ' Creating a Shell object

    Set objApp = CreateObject( "Shell.Application" )

 

    ' Copy the files only to the compressed folder

    For Each objItem in objApp.NameSpace( myFolder ).Items

        If objItem.IsFolder Then

            ' Check if the subfolder is empty, and if

            ' so, skip it to prevent an error message

            Set objFolder = objFSO.GetFolder( objItem.Path )

            If objFolder.Files.Count + objFolder.SubFolders.Count = 0 Then

                intSkipped = intSkipped + 1

            Else

                objApp.NameSpace( myZipFile ).CopyHere objItem

            End If

        Else

            objApp.NameSpace( myZipFile ).CopyHere objItem

        End If

    Next

 

    Set objFolder = Nothing

    Set objFSO    = Nothing

 

    ' Abort if errors occured

    If Err Then

        ZipFolder = Array( Err.Number, Err.Source, Err.Description )

        Set objApp = Nothing

        Err.Clear

        On Error Goto 0

        Exit Function

    End If

 

    ' Keep script waiting until compression is finished

    intSrcItems = objApp.NameSpace( myFolder  ).Items.Count

    Do Until objApp.NameSpace( myZipFile ).Items.Count + intSkipped = intSrcItems

        WScript.Sleep 200

   Loop

    Set objApp = Nothing

 

    ' Abort if errors occured

    If Err Then

        ZipFolder = Array( Err.Number, Err.Source, Err.Description )

        Err.Clear

        On Error Goto 0

        Exit Function

    End If

 

    ' Restore default error handling

    On Error Goto 0

 

    ' Return message if empty subfolders were skipped

    If intSkipped = 0 Then

        strSkipped = ""

    Else

        strSkipped = "skipped empty subfolders"

    End If

 

    ' Return code 0 if no error occurred

    ZipFolder = Array( 0, intSkipped, strSkipped )

 

End Function

Step2-Now you can call this .vbs file from Sql server as follows

EXEC XP_CMDSHELL 'CScript C:\vScriptForZip.vbs "C:\testfolder"  "C:\testfile.zip"'

See here one more important thing you can see how passing the parameter from command line or from sql to .vbs file. Just put the parameter with double quotes after the .vbs file and that parameter value are getting as arguments list in .vbs file using WScript.Arguments. eg WScript.Arguments.Item(0)—1^st parameter

WScript.Arguments.Item(1)—2^nd parameter and so on if more will be needed.

 

There are one most important thing if any single line of code written than need to debug most of the time so there are one good way to debug the .vbs file using command line

Eg. Above .vbs file need to debug than just run as follows

EXEC XP_CMDSHELL 'CScript /x /d C:\vScriptForZip.vbs "C:\testfolder"  "C:\testfile.zip"'

This will ask you for debug environment eg visual studio 2005 or 2010 what ever installed on system and once you choose that one you can debug as normal c#.net code.

 

Note- some time vbScript are not register on system so that didn’t running so first you need to register it using register32

Eg-Start->Run-> Type “regsvr32 vbscript.dll” and press Enter

 

Reference:for xp_cmdshell-- http://msdn.microsoft.com/en-us/library/ms175046(v=sql.90).aspx

For running command line issue also see this link-- http://technet.microsoft.com/en-us/library/ee156587.aspx

Today I stuck with one downloading issue of old working code which was working perfectly in older version of IE but the same code doesn’t work in IE10.It giving error like “filename couldn’t be downloaded”. Basically this problem was reported for large files. So it was little bit frustration.

 

After long R&D I got solution. Actually in IE10 enable the Content-Length/Transfer-Encoding validation to check in IE's Download Manager and due to that validation if encounter a transfer fails to include the Content-Length header, the above message will be shown. This reported as incomplete file's signature was corrupted.

 

Even user clicks on Retry button that will restart download and in lots of cases it showing download completed but once user open that downloaded file encounter the file was corrupted.

 

This whole things happened due one bad practice is calling Response.Close(); Calling the Response.Close() is basically the remove the final chunk, and would cause the server's output fail in Download Manager if the response body is not as single chunk.

 

Or you can say Response.Close terminates the connection and fails to send the closing chunk so this is bad practice mainly for large file.

 

So good practice is you can Call CompleteRequest instead of Close. Because when you are using Close, it terminates the connection with the client where as if you calling CompleteRequest it jumps ahead to EndRequest and send a response to the client.

 

So Instead of End and Close, the HttpApplication.CompleteRequest call is a better way to end a request.

 

1. This is old code that don’t working in IE10

private void lnkFile_Click(object sender, System.EventArgs e)

            {

                  strFilePath = Server.MapPath("Download// 13thMar_2013.pdf");

                  if(strFilePath != "")

                  {

                        string strfileName=Path.GetFileName(strFilePath);   

                        System.IO.FileInfo toDownload = new System.IO.FileInfo(strFilePath);

                        const int ChunkSize= 100000;

                        byte[] buffer=new byte[ChunkSize];

                        Response.Clear();

                        FileStream fl= File.OpenRead(strFilePath);

                        long dtlength= fl.Length;

                        Response.ContentType = "application/octet-stream";

                        Response.AddHeader("Content-Disposition", "attachment; filename=" + strfileName);

                        while(dtlength > 0 && Response.IsClientConnected)

                        {

                              int lengthRead = fl.Read(buffer, 0, ChunkSize);

                              Response.OutputStream.Write(buffer, 0, lengthRead);

                              Response.Flush();

                              dtlength = dtlength - lengthRead;

                        }

                        fl.Close();            

                        Response.Close();

                  }

            }

 

2. This is new code that perfectly working in IE10 also.

private void lnkFileNew_Click(object sender, System.EventArgs e)

            {

                  strFilePath = Server.MapPath("Download// 13thMar_2013.pdf");

                  if(strFilePath != "")

                  {

                        string strfileName=Path.GetFileName(strFilePath);                      

                        System.IO.FileInfo toDownload = new System.IO.FileInfo(strFilePath);

                        const int ChunkSize= 100000;

                        byte[] buffer=new byte[ChunkSize];

                        Response.Clear();

                        FileStream fl= File.OpenRead(strFilePath);

                        long dtlength= fl.Length;

                        Response.ContentType = "application/octet-stream";

                        Response.AddHeader("Content-Disposition", "attachment; filename=" + strfileName);

                        while(dtlength > 0 && Response.IsClientConnected)

                        {

                              int lengthRead = fl.Read(buffer, 0, ChunkSize);

                              Response.OutputStream.Write(buffer, 0, lengthRead);

                              Response.Flush();

                              dtlength = dtlength - lengthRead;

                        }

                        fl.Close();            

                        //Response.Close();

                        //Only change this line commented close method and calling CompleteRequest

                        HttpContext.Current.ApplicationInstance.CompleteRequest();

                  }

            }

 

See more details about power view is here

http://blogs.msdn.com/b/aspnetue/archive/2010/05/25/response-end-response-close-and-how-customer-feedback-helps-us-improve-msdn-documentation.aspx

 

 

 

Today someone asked how to get the first day and last day of the month in T-Sql query. So I write the query for both case one just get the first and last day of current month as well as the first and last day of the passing date value.

1. First query gives you the first day and last day of current month.

DECLARE @inputdate DATETIME

SELECT @inputdate = GETDATE()

SELECT 'First Day of Current Month' AS Date_Type, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@inputdate)-1),@inputdate),101) AS Date

UNION

SELECT 'Today' AS Date_Type,CONVERT(VARCHAR(25),@inputdate,101) AS Date

UNION

SELECT 'Last Day of Current Month' AS Date_Type, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@inputdate))),DATEADD(mm,1,@inputdate)),101) AS Date

 

Output is

Date_Type                             Date

First Day of Current Month       03/01/2013

Last Day of Current Month       03/31/2013

Today  03/04/2013

 

2.2^nd query for the getting first and last day of the month when passing a date value

Eg. Let passing value is 20130217 for date 17 fab 2013.

 

DECLARE @inputdate DATETIME

SELECT @inputdate = Convert(datetime,'20130217')

Print @inputdate

SELECT 'First Day of Month for passing date' AS Date_Type, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@inputdate)-1),@inputdate),101) AS Date

UNION

SELECT 'Last Day of Month for passing date',CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@inputdate))),DATEADD(mm,1,@inputdate)),101)

 

Output is

Date_Type                                         Date

First Day of Month for passing date      02/01/2013

Last Day of Month for passing date       02/28/2013

Today I am looking the new database for RnD purpose and need some columns analysis to find out the table list which contains that particular column. So I am sharing this simple script.

SELECT c.name AS ColName, t.name AS TableName

FROM sys.columns c

    JOIN sys.tables t ON c.object_id = t.object_id

WHERE c.name LIKE '%column_name%'

 

OR

 

SELECT COLUMN_NAME, TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE '%column_name%'