Friday, 28 March 2014

Sample Table Value Function in SQL

Sample Table Value Function in SQL :
CREATE FUNCTION MyTestFunc
(
@UserId INT
)
returns table as
return
(
Select * from User_Info_Master WHERE UserId=@userId
)

We can call this function in our query like as shown below:
SELECT * FROM dbo.MyTestFunc(14) 

Clear Radio Button List Selection by jQuery

Add the script to clear Radio Button List Selection :
<script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script type="text/javascript">
function ClearAge() {
$('input:radio[name=rdbage]').attr('checked', false);
}
</script>

Wednesday, 26 March 2014

Get comma separated values in SQL Server

DECLARE @Result VARCHAR(MAX)
SET @Result = ''   
SELECT @Result = @Result +COLUMN_NAME + ','
FROM dbo.TABLE_NAME        
IF(LEN(@Result) > 0 )
SELECT @Result = SUBSTRING(@Result,1,LEN(@Result)-1)
-- Return the result of the function
SELECT @Result as COLUMN_NAME

Friday, 21 March 2014

Fixed header in grid

Add the css:
<style type="text/css">
        .FixedHeader {
            position: absolute;
            font-weight: bold;
            vertical-align:text-bottom;
        }     
    </style>

<asp:GridView ID="gvDistricts" runat="server" HeaderStyle-CssClass="FixedHeader" HeaderStyle-BackColor="YellowGreen"
                    AutoGenerateColumns="false" AlternatingRowStyle-BackColor="WhiteSmoke" OnRowDataBound="gvDistricts_RowDataBound">
                    <Columns>
                        <asp:TemplateField HeaderText="District ID" HeaderStyle-Width="100px" ItemStyle-Width="101px">
                            <ItemTemplate>
                                <asp:Label ID="lblDistID" runat="server" Text='<%#Eval("district_code")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="District Name" HeaderStyle-Width="300px" ItemStyle-Width="301px">
                            <ItemTemplate>
                                <asp:Label ID="lblDistName" runat="server" Text='<%#Eval("district_name")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Description" HeaderStyle-Width="200px" ItemStyle-Width="200px">
                            <ItemTemplate>
                                <asp:Label ID="lblDistDesc" runat="server" Text='<%#Eval("Description")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>

Thursday, 20 March 2014

show only vertical scroll in div

This will show only vertical scroll when  content height in div  will be greater than 300px.

<div style="overflow-x: hidden; overflow-y: auto; height: 300px;">
----content---
</div>

Merge cell in gridview

Show Record one time in cell 1 if it contain duplicate value.
Create a Class:
public class GridDecorator
{
 public static void MergeRows(GridView gridView, string type)
 {
  int rowIndex = 0;
  for (rowIndex = gridView.Rows.Count - 2; rowIndex <= 0; rowIndex += rowIndex - 1) {
   GridViewRow row = gridView.Rows(rowIndex);
   GridViewRow previousRow = gridView.Rows(rowIndex + 1);

   int i = 0;
   for (i = 0; i <= row.Cells.Count - 1; i += i + 1) {
    if (row.Cells(1).Text == previousRow.Cells(1).Text) {
     row.Cells(1).RowSpan = (previousRow.Cells(1).RowSpan < 2 ? 2 : previousRow.Cells(1).RowSpan + 1);
     previousRow.Cells(1).Visible = false;
    }
   }
  }
 }
}
 
Add in .cs page:
protected void GV_Report_PreRender(object sender, EventArgs e)
{
 GridDecorator.MergeRows(GV_Report, "");
} 

Thursday, 13 March 2014

Calender in Updatepanel

Please replace the following code
----------------------------------------
<script type="text/javascript">
               $(function () {
                   $('.txtdate').datepicker(
                        {
                            dateFormat: "dd-mm-yy",
                            changeMonth: true,
                            changeYear: true
                        });
               });
    </script>

By
---
 <script type="text/javascript">

        function pageLoad(sender, args) {          
            cal_date()
        }

        function cal_date() {
            $('.txtdate').unbind();           
            jQuery(document).ready(function ($) {
                $('.txtdate').datepicker({
                    dateFormat: "dd-mm-yy",
                    autoSize: true,
                    changeMonth: true,
                    changeYear: true
                });
            });
        }
    </script>

Fancybox in Updatepanel

Please replace following code
----------------------------------------
$(document).ready(function () {
            $(".various3").fancybox({
                'width': '99%',
                'height': '99%',
                'autoScale': false,
                'transitionIn': 'none',
                'transitionOut': 'none',
                'type': 'iframe',
                'hideOnOverlayClick': false
            });
        });

By
---
function pageLoad(sender, args) {
            $(".various3").fancybox({
                'width': '99%',
                'height': '99%',
                'autoScale': false,
                'transitionIn': 'none',
                'transitionOut': 'none',
                'type': 'iframe',
                'hideOnOverlayClick': false
            });
        }

ASP.NET Error Handling



Overview

ASP.NET applications must be able to handle errors that occur during execution in a consistent manner. ASP.NET uses the common language runtime (CLR), which provides a way of notifying applications of errors in a uniform way. When an error occurs, an exception is thrown. An exception is any error, condition, or unexpected behavior that an application encounters.
In the .NET Framework, an exception is an object that inherits from the System.Exception class. An exception is thrown from an area of code where a problem has occurred. The exception is passed up the call stack to a place where the application provides code to handle the exception. If the application does not handle the exception, the browser is forced to display the error details.
As a best practice, handle errors in at the code level in Try/Catch/Finally blocks within your code. Try to place these blocks so that the user can correct problems in the context in which they occur. If the error handling blocks are too far away from where the error occurred, it becomes more difficult to provide users with the information they need to fix the problem.

Exception Class

The Exception class is the base class from which exceptions inherit. Most exception objects are instances of some derived class of the Exception class, such as the SystemException class, the IndexOutOfRangeException class, or the ArgumentNullException class. The Exception class has properties, such as the StackTrace property, the InnerException property, and the Message property, that provide specific information about the error that has occurred.

Exception Inheritance Hierarchy

The runtime has a base set of exceptions deriving from the SystemException class that the runtime throws when an exception is encountered. Most of the classes that inherit from the Exception class, such as the IndexOutOfRangeException class and the ArgumentNullException class, do not implement additional members. Therefore, the most important information for an exception can be found in the hierarchy of exceptions, the exception name, and the information contained in the exception.

Exception Handling Hierarchy

In an ASP.NET Web Forms application, exceptions can be handled based on a specific handling hierarchy. An exception can be handled at the following levels:
  • Application level
  • Page level
  • Code level
When an application handles exceptions, additional information about the exception that is inherited from the Exception class can often be retrieved and displayed to the user. In addition to application, page, and code level, you can also handle exceptions at the HTTP module level and by using an IIS custom handler.

Application Level Error Handling

You can handle default errors at the application level either by modifying your application’s configuration or by adding an Application_Error handler in the Global.asax file of your application.
You can handle default errors and HTTP errors by adding a customErrors section to the Web.config file. The customErrors section allows you to specify a default page that users will be redirected to when an error occurs. It also allows you to specify individual pages for specific status code errors.
<configuration>
  <system.web>
    <customErrors mode="On" defaultRedirect="ErrorPage.aspx?handler=customErrors%20section%20-%20Web.config">
      <error statusCode="404" redirect="ErrorPage.aspx?msg=404&amp;handler=customErrors%20section%20-%20Web.config"/>
    </customErrors>
  </system.web>
</configuration>
Unfortunately, when you use the configuration to redirect the user to a different page, you do not have the details of the error that occurred.
However, you can trap errors that occur anywhere in your application by adding code to the Application_Error handler in the Global.asax file.
void Application_Error(object sender, EventArgs e)
{
    Exception exc = Server.GetLastError();

    if (exc is HttpUnhandledException)
    {
        // Pass the error on to the error page.
        Server.Transfer("ErrorPage.aspx?handler=Application_Error%20-%20Global.asax", true);
    }
}

Page Level Error Event Handling

A page-level handler returns the user to the page where the error occurred, but because instances of controls are not maintained, there will no longer be anything on the page. To provide the error details to the user of the application, you must specifically write the error details to the page.
You would typically use a page-level error handler to log unhandled errors or to take the user to a page that can display helpful information.
This code example shows a handler for the Error event in an ASP.NET Web page. This handler catches all exceptions that are not already handled within try/catch blocks in the page.
        private void Page_Error(object sender, EventArgs e)
        {
            Exception exc = Server.GetLastError();

            // Handle specific exception.
            if (exc is HttpUnhandledException)
            {
                ErrorMsgTextBox.Text = "An error occurred on this page. Please verify your " +                  
                "information to resolve the issue."
            }
            // Clear the error from the server.
            Server.ClearError();
        }
After you handle an error, you must clear it by calling the ClearError method of the Server object (HttpServerUtility class), otherwise you will see an error that has previously occurred.

Code Level Error Handling

The try-catch statement consists of a try block followed by one or more catch clauses, which specify handlers for different exceptions. When an exception is thrown, the common language runtime (CLR) looks for the catch statement that handles this exception. If the currently executing method does not contain a catch block, the CLR looks at the method that called the current method, and so on, up the call stack. If no catch block is found, then the CLR displays an unhandled exception message to the user and stops execution of the program.
The following code example shows a common way of using try/catch/finally to handle errors.
    try
    {
        file.ReadBlock(buffer, index, buffer.Length);
    }
    catch (FileNotFoundException e)
    {
        Server.Transfer("NoFileErrorPage.aspx", true);
    }
    catch (System.IO.IOException e)
    {
        Server.Transfer("IOErrorPage.aspx", true);
    }

    finally
    {
        if (file != null)
        {
            file.Close();
        }
    }
In the above code, the try block contains the code that needs to be guarded against a possible exception. The block is executed until either an exception is thrown or the block is completed successfully. If either a FileNotFoundException exception or an IOException exception occurs, the execution is transferred to a different page. Then, the code contained in the finally block is executed, whether an error occurred or not.

Tuesday, 11 March 2014

Testing Internet Connectivity

Method 1: WebRequest

public static bool WebRequestTest()
        {
            string url = "http://www.google.com";
            try
            {
                System.Net.WebRequest myRequest = System.Net.WebRequest.Create(url);
                System.Net.WebResponse myResponse = myRequest.GetResponse();
            }
            catch (System.Net.WebException)
            {
                return false;
            }
            return true;
        }
 

Method 2: TCP Socket

public static bool TcpSocketTest()
        {
            try
            {
                System.Net.Sockets.TcpClient client =
                    new System.Net.Sockets.TcpClient("www.google.com", 80);
                client.Close();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }

Method 3: Ping

public bool PingTest()
        {
            System.Net.NetworkInformation.Ping ping = new System.Net.NetworkInformation.Ping();
 
            System.Net.NetworkInformation.PingReply pingStatus =
                ping.Send(IPAddress.Parse("208.69.34.231"),1000);
 
            if (pingStatus.Status == System.Net.NetworkInformation.IPStatus.Success)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

Method 4: DNS Lookup 

public static bool DnsTest()
        {
            try
            {
                System.Net.IPHostEntry ipHe =
                    System.Net.Dns.GetHostByName("www.google.com");
                return true;
            }
            catch
            {
                return false;
            }
        }

 

 

 

 

 

 

 

Friday, 7 March 2014

Fill DataTable Form another DataTable with Selected Column

Private Sub CopyColumns(source As DataTable, dest As DataTable, ParamArray columns As String())
        For Each sourcerow As DataRow In source.Rows
            Dim destRow As DataRow = dest.NewRow()
            For Each colname As String In columns
                destRow(colname) = sourcerow(colname)
            Next
            dest.Rows.Add(destRow)
        Next
    End Sub

###
            Dim dt As New DataTable()
            dt ''source table
            Dim dt1 As New DataTable()
            dt1.Columns.Add("columnname", GetType([String]))
            'dt1 = dt.Clone()
            CopyColumns(dt, dt1, "columnname")

Thursday, 6 March 2014

Create a connection with MSAcess and open the conection object.

1) First add "using System.Data.OleDb;" at to top of the CS file.
2) Create connection string and open connection.
For access 2007

string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\abc.mdb;Jet OLEDB:Database Password=password";

OleDbConnection MyConn = new OleDbConnection(ConnStr);

3) Open this connection.

MyConn.Open();


4) Create object for command and reader to get the data from access database.

OleDbCommand Cmd = new OleDbCommand(StrCmd, MyConn);;
OleDbDataReader ObjReader = Cmd.ExecuteReader();


5) Now lood through the reader object to get the data

if (ObjReader != null)
{
}

6) After completing the processing

ObjReader.Close();
MyConn.Close();

Wednesday, 5 March 2014

How to Build and Execute Dynamic SQL in stored procedures

A dynamically build Transact-SQL statements can be executed using EXECUTE Command or sp_executesql statement. Here, in this article in my examples, I'll be using sp_executesql which is more efficient, faster in execution and also supports parameter substitution. If we are using EXECUTE command to execute the SQL String, then all the parameters should be converted to character and made as a part of the Query before execution. But the sp_executesql statement provides a better way of implementing this. It allows us to substitute the parameter values for any parameter specified in the SQL String. Before getting into the actual example, let me differentiate these two commands with a simple example. Say - selecting a record from the employee table using the ID in the WHERE clause.
The basic syntax for using EXECUTE command:

EXECUTE(@SQLStatement)
 
The basic syntax for using sp_executesql:
 
sp_executesql [@SQLStatement],[@ParameterDefinitionList],
[@ParameterValueList]
 

Example 1.0

/* Using EXECUTE Command */
/* Build and Execute a Transact-SQL String with a single parameter 
 value Using EXECUTE Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String with parameter value */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' + 
CAST(@EmpID AS NVARCHAR(10))
/* Execute Transact-SQL String */
EXECUTE(@SQLQuery)
 

Example 1.1

/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID 

Example 2.0

Let us take a simple example - Employee Table with common fields such as EmployeeID, Name, Department, Designation, JoiningDate, Salary and Description. You can use the following Transact-SQL CREATE TABLE statement to create a Employee Table within your database.

/* Transact-Sql to create the table tblEmployees */
CREATE TABLE tblEmployees
(
    EmployeeID       SMALLINT IDENTITY(1001,1) NOT NULL,
    EmployeeName     NVARCHAR(100) NOT NULL,
    Department       NVARCHAR(50) NOT NULL,
    Designation      NVARCHAR(50) NOT NULL,
    JoiningDate      DATETIME NOT NULL,
    Salary           DECIMAL(10,2) NOT NULL,
    [Description]    NVARCHAR(1000) NULL 
)
 
/* Transact SQL to insert some sample records into tblEmployee table */
INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('John Smith', 'IT Research', 'Research Analyst', 
 '02/08/2005', 23000.00, 'Analyst since 2005')

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('John Micheal', 'IT Operations', 'Manager', 
 '07/15/2007', 15000.00, NULL)

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('Will Smith', 'IT Support', 'Manager', 
 '05/20/2006', 13000.00, 'Joined last year as IT Support Manager')
 
/* This stored procedure builds dynamic SQL and executes 
using sp_executesql */
Create Procedure sp_EmployeeSelect
    /* Input Parameters */
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary    Decimal(10,2)
        
AS
    Set NoCount ON
    /* Variable Declaration */
    Declare @SQLQuery AS NVarchar(4000)
    Declare @ParamDefinition AS NVarchar(2000) 
    /* Build the Transact-SQL String with the input parameters */ 
    Set @SQLQuery = 'Select * From tblEmployees where (1=1) ' 
    /* check for the condition and build the WHERE clause accordingly */
    If @EmployeeName Is Not Null 
         Set @SQLQuery = @SQLQuery + ' And (EmployeeName = @EmployeeName)'

    If @Department Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Department = @Department)' 
  
    If @Designation Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Designation = @Designation)'
  
    If @Salary Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Salary >= @Salary)'

    If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
         Set @SQLQuery = @SQLQuery + ' And (JoiningDate 
         BETWEEN @StartDate AND @EndDate)'
    /* Specify Parameter Format for all input parameters included 
     in the stmt */
    Set @ParamDefinition =      ' @EmployeeName NVarchar(100),
                @Department NVarchar(50),
                @Designation NVarchar(50),
                @StartDate DateTime,
                @EndDate DateTime,
                @Salary    Decimal(10,2)'
    /* Execute the Transact-SQL String with all parameter value's 
       Using sp_executesql Command */
    Execute sp_Executesql     @SQLQuery, 
                @ParamDefinition, 
                @EmployeeName, 
                @Department, 
                @Designation, 
                @StartDate, 
                @EndDate,
                @Salary
                
    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO
 

Example 3.0 - Using LIKE Operator

  /* Variable Declaration */
DECLARE @EmpName AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @EmpName = 'John' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeName LIKE '''+ '%' + @EmpName + '%' + '''' 
EXECUTE sp_executesql @SQLQuery
 

Example 3.1 - Using IN Operator

/* Variable Declaration */
DECLARE @EmpID AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single 
parameter value Using sp_executesql Command */
SET @EmpID = '1001,1003' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeID IN(' + @EmpID + ')'
EXECUTE sp_executesql @SQLQuery
 
 

Example 3.2 - Using Order By Clause

 /* Variable Declaration */
DECLARE @OrderBy AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @OrderBy = 'Department' 
SET @SQLQuery = 'SELECT * FROM tblEmployees Order By ' + @OrderBy

EXECUTE sp_executesql @SQLQuery