Monday 20 January 2014

Exception Management

Introduction
Exception management is one of the key area for all kinds of application development .You should adopt an appropriate strategy for exception management to build high quality and robust application .It is a very powerful concept and makes the development work very easy if its used efficiently. Inappropriate way of handling exception can degrade the performance. Before dig into this its very important to know what is an exception? The general meaning of "an exception is the breaching of predefined assumption of the application". Remember exception and error are not the same. To explain this let me explain couple of examples.
Example1Lets say you are try to log data to a file and your application assumes the file is present in the target path , but if is not then exception would be raised .On the other hand if your job is to trace the file and if it is present then log the data in this scenario raising an exception is a bad coding practice. It should be handled through validation code.
Example 2
Lets say in a normal ASP.NET application you are trying to update all necessary fields to the database , and your application assume the database connection is available suppose the connection is not available then raising an exception is a ideal solution. On the other hand while update the mandatory fields in database and few of them having the null values then raising an exception is not necessary it should be handled through validation code.

How to Handle
As a developer you must take the advantage of structured exception handling mechanism through try, catch and finally block .The .NET framework provides a broad hierarchy of exception classes to handle the different types of exception and all these classes derived from Exception class (base class). The developer can extend exception mechanism through inheritance , it helps to implement the custom error handling or provide a proper gateway to handle the complex error handling for the application. Unfortunately, many developers misuse this architecture capability .One very important thing you should keep in mind is how to react when a exception occur at runtime. The good approach to react the exception is
  • Just ignore the exception or implement different possible catch blocks to catch the exception.
  • Catch the exception and perform required action for your application and if you can not recover from the exception rethrow the exception.
  • Catch the exception and wrap with another exception which is more relevant for your application. Exception wrapping used to avoid breaking the layer abstraction due to exception. For preserving the original exception you can use the InnerException property of the exception class this allows the original exception to be wrapped inside a new exception ( which is more relevant for your application) to understand the wrapping of exception lets look at this example inside a method of your application caught a lower level exception called IOException , you can wrap this original exception with a application level exception called LoadingException or FailtoLoadInfo exception or something else which is more relevant for your application rather then alerting the lower level exception to the user .
The exception management architecture of a application capable to
  • Detect Exception
  • Perform code clean up
  • Wrap one exception inside another
  • Replace one exception with another
  • Logging and reporting error information
  • Generating events that can be monitored externally to assist system operation
At the beginning of the design you must plan for a consistency and robust exception management architecture and it should be well encapsulated and abstract the details of logging and reporting throughout all architecture layer of your application .Lets discuss some of the best practices of exception
Best Practices
The following list contains tips/suggestions to be consider while handling the exceptions:
  • Exception is a expensive process , for this reason, you should use exceptions only in exceptional situations and not to control regular logic flow. For example
    void EmpExits ( string EmpId)
    {
    //... search for employeeif ( dr.Read(EmpId) ==0 ) // no record found, ask to create{thrownew Exception("Emp Not found"));
    }

    The best practice is :

    bool EmpExits ( string EmpId)
    {
    //... search for Productif ( dr.Read(EmpId) ==0 ) // no record found, ask to create{return false}
    }
  • Avoid exception handling inside loops, if its really necessary implement try/catch block surround the loop
  • Adopt the standard way of handling the exception through try, catch and finally block .This is the recommended approach to handle exceptional error conditions in managed code, finally blocks ensure that resources are closed even in the event of exceptions. For example

    SqlConnection conn = new SqlConnection("...");try{
    conn.Open();
    //.some operation 
    // ... some additional operations
    }catch(…)
    {
    // handle the exception}finally{if (conn.State==ConnectionState.Open)
    conn.Close(); 
    // closing the connection}
  • Where ever possible use validation code to avoid unnecessary exceptions .If you know that a specific avoidable condition can happen, precisely write code to avoid it. For example, before performing any operations checking for null before an object/variable can significantly increase performance by avoiding exceptions. For example

    double result = 0;try{
    result = firstVal/secondVal;
    }
    catch( System.Exception e)
    {
    //handling the zero divided exception }
    This is better then the above code

    double result = 0;if(secondVal >0)
    result = firstVal/secondVal;
    elseresult = System.Double.NaN;
  • Do not rethrow exception for unnecessary reason because cost of using throw to rethrow an existing exception is approximately the same as creating a new exception and rethrow exception also makes very difficult to debug the code. For example

    try{// Perform some operations ,in case of throw an exception…}
    catch (Exception e)
    {
    // Try to handle the exception with ethrow;
    }
  • The recommended way to handle different error in different way by implement series of catch statements this is nothing but ordering your exception from more specific to more generic for example to handle file related exception its better to catch FileNotFoundException, DirectoryNotFoundException, SecurityException,IOException, UnauthorizedAccessException and at last Exception .
  • ADO.NET errors should be capture through SqlException or OleDbException

    • Use the ConnectionState property for checking the connection availability instead of implementing an exception 
       
    • Use Try/Finally more often, finally provides option to close the connection or the using statement provides the same functionality.
       
    • Use the specific handler to capture specific exception, in few scenarios if you know that there is possibility for a specific error like database related error it can be catch through SqlException or OleDbException as below

      try
      ...
      }
      catch (SqlException sqlexp) // specific exception handler{ ...
      }
      catch (Exception ex) // Generic exception handler
      { ...
      }
  • Your exception management system capable to detect, wrap one exception inside another, Replace one exception with another, logging and reporting the exception information for monitoring the application.
  • Its recommend to use "Exception Management Application Block" provided by Microsoft .It is a simple and extensible framework for logging exception information to the event log or you can customize to write the exception information to other data sources without affecting your application code and implemented all best practices and tested in Microsoft Lab.
For more information

Security in ADO.NET

This article gives a sound idea how to write secure code for ADO.NET. Data Access Layer (DAL)  is a common and very curtail  for your application. Its very important know some of the basic security points while writing ADO.NET program.

  1. One of the key point of security is "never ever trust on user inputs". You must validate the user's data properly before process. The hacker always tries to crash your application through malicious inputs (especially dynamic SQL statements). As a developer you must take care of all vulnerable inputs pass through SQL statements for example lets says you are trying to search customer details by taking the customer name as input and you are build a dynamic SQL to fetch the details from SQL Server, if you do not validate the user's input and directly  process can cause a heavy damage to your application assume the user (smart user) pass the customer name as "1;DROP TABLE Cust". The code snippet will be as below:


  1. string strQuery = "SELECT * from Cust WHERE custName="+txtCustName.Text;
    SqlCommand cmd =
     new SqlCommand( strQuery, conn);
    conn.Open();
    SqlDataReader myReader = cmd.ExecuteReader();
    myReader.Close();
    conn.Close();

    The solution to the above problem is validate such vulnerable before execute the query.
     
  2. The next point is parameterize store procedures. This is a convenient way to safeguard your application against SQL injection attacks, make sure your stored procedures or methods  accept only  values not the SQL statements  and also recommend to validate the user inputs as explained in above point before execute.

  3. Use Regex to validate user input for a particular format (pattern)  the other way it helps quickly parse large  amount of text to find specific character patterns, also help to edit or replace or delete text substring. For example  to validate the input value should have 5 character alphanumeric string.

    public void CheckString(string inputValue)
    {
    Regex rg =
     new Regex("^[A-Za-z0-9]{5}$");
    return rg.IsMatch(inputValue)
    }

  4. One of the way a  hacker can reach your database or  data source  through system generated exception. The most keep point for everyone is do not display complete   system exception information to the user, display only required exception information  to client, suggest to implement exception wrapping or replace to display custom exception by hiding the actual  database exception. To know more about exception management click here.

  5. The other key point is never ever try to connect to database through user name and password in plain text it is a serious vulnerable i.e if the  user name and password is a part of your source code that can be exploited by disassemble the IL code. This is the big plus point for the hacker to play with your application .When connecting to Microsoft SQL Server it is  highly recommended to use Integrated Security, which uses the identity of the current active user rather than passing a user name and password. Do not forget to set  Persist Security Info to true or yes this allow security sensitive information including the user name  and password  to be obtained from the connection after the connection has been opened.
These are the some of the basic security points every body should keep in mind  while working with ADO.NET or database.

Thursday 16 January 2014

Creating and managing User Defined Functions in SQL Server 2008

What are UDF: SQL server provides list of many predefined functions that are built in to the T-SQL language. The supplied functions helps extend the capabilities of T-SQL, providing the ability to perform string manipulation, mathematical calculations data type conversion etc. but often we need something which is not provided using these functions. So we can create stored procedure to perform custom processing, but the problem is that we can’t use the result of stored procedure in WHERE or SELECT list, for this type of scenario we need UDF.
Why to use User Defined Functions: The main benefit of UDF is that we are not just limited to sql provided functions. We can write our own functions to meet our specific needs or to simplify complex SQL codes.
Let’s take an example:
SQL getdate() returns current system date and time. It always includes both data and time components. We want to get just date and have the time always set to midnight. One solution is to to the conversion like below;
select convert(datetime,CONVERT(date,getdate()))
But the problem is that when we want to have date with time always set to midnight, we need to do this conversion. Solution is to make UDF for this.
create function getonlydate()
returns datetime
as
begin
return(select convert(datetime,convert(date,getdate())))
end
go
Now we can call this UDF in our SQL query.
select dbo.getonlydate()
Let us see how we can use this UDF in other SQL statements.
Let us create a table Order
CREATE TABLE Orders (
OrderID int IDENTITY (1, 1) NOT NULL Primary Key,
CustomerID nchar (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
EmployeeID int NULL ,
OrderDate datetime NULL default dbo.getonlydate(),
RequiredDate datetime NULL ,
ShippedDate datetime NULL
)
Let us INSERT values in this table using UDF function we created.
INSERT Orders(CustomerID,EmployeeID,RequiredDate)
values(‘BERGS’,3,dbo.getonlydate() +7)
Let us UPDATE values in this table using UDF function we created.
UPDATE Orders set ShippedDate = dbo.getonlydate()
where OrderID=1
SELECT OrderDate,RequiredDate,ShippedDate
FROM orders
WHERE orderdate = dbo.getonlydate()
Orderdate               Requireddate            Shippeddate
——————————————————————–
2011-05-01 00:00:00.000       2011-05-08 00:00:00.000       2011-05-01 00:00:00.000

Types of User Defined Functions:
1) Scalar functions
2) Inline table valued function
3) Multistatement table valued functions.
For all examples shared below I have used Pubs database. You can download its msi file from here and then attach .mdf file in your Sql Sever 2008.

A) Scalar Function:
1) They are like standard built in functions provided with SQL Server.
2) It return scalar values that can be used anywhere a constant expression can be used.
3) It typically takes one or more arguments and returns a value of a specified data types.
4) Every T-SQL function must return a result using the RETURN statement.
Example:
The following two functions are variations of a function that returns the average price for a specified type of book from the titles table:
CREATE FUNCTION AverageBookPrice (@booktype varchar(12) = ‘%’)
RETURNS money
AS
BEGIN
DECLARE @Avg money
SELECT @Avg = AVG(price)
FROM titles
WHERE type  like @booktype
RETURN @Avg
END
GO
CREATE FUNCTION   AverageBookPrice2 (@booktype varchar(12) =’%')
RETURNS money
AS
BEGIN
RETURN (SELECT AVG(PRICE)
FROM TITLES
WHERE TYPE LIKE @booktype)
END
## SQL Server doesn’t allow aggregate functions in a WHERE clause unless they are contained in a subquery.
The AvgBookPrice() function lets you compare against the average price without having to use a subquery:
SELECT  title_id, type, price from titles
where price > dbo.AverageBookPrice(‘popular_comp’)
titleid type      price   
———————–
PC1035 popular_comp  22.95
PS1372 psychology    21.59
You can return the value from a user-defined scalar function into a local variable in two ways. You can assign the result to a local variable by using the SET statement or an assignment select, or you can use the EXEC statement. The following commands are functionally equivalent:
declare @avg1 money,
@avg2 money,
@avg3 money
select @avg1 = dbo.AverageBookPrice(‘popular_comp’)
set @avg2 = dbo.AverageBookPrice(‘popular_comp’)
exec @avg3 = dbo.AverageBookPrice ‘popular_comp’
select @avg1 as avg1, @avg2 as avg2, @avg3 as avg3
go
Result is below
avg1     avg2       avg3
———————————–
21.475 21.475 21.475
B) Table Value Function:
1) A table-valued user-defined function returns a rowset instead of a single scalar value.
2) Can be invoked in the FROM clause of a SELECT statement, just as we would a table or view.
3) A table-valued function can almost be thought of as a view that accepts parameters, so the result set is determined dynamically.
4) A table valued function specifies the keyword TABLE in its RETURNS clause.
5) They are of two types.
1) Inline table valued function
A) An inline table-valued function specifies only the TABLE keyword in the RETURNS clause,
Without table definition information.
B) The code inside the function is a single RETURN statement that invokes a SELECT            statement.
Example:
CREATE FUNCTION AveragePriceByType (@price money = 0.0)
RETURNS table
AS
RETURN (SELECT type,avg(isnull(price,0)) as avg_price
FROM titles
GROUP BY type
HAVING avg(isnull(price,0)) > @price )
select * from AveragePriceByType(15.0)
      type        averageprice
—————————————-
trad_cook        15.9633
————————————————————
2) Multi statement table valued function:
a) Multistatement table-valued functions differ from inline functions in two major ways
A) The RETURNS clause specifies a table variable and its definition.
B) The body of the function contains multiple statements, at least one of which                                populates the table variable with data values.
b) The scope of the table variable is limited to the function in which it is defined.
c) Within the function in which a table variable is defined, that table variable can be treated like a regular table. You can perform any SELECT, INSERT, UPDATE, or DELETE statement            on the rows in a table variable, except for SELECT INTO.
The following example defines the inline table-valued function AveragePricebyType() as a multistatement table-valued function called AveragePricebyType3():
CREATE FUNCTION   AveragePricebyType3 (@price money =0.0)
RETURNS @table table(type varchar(12) null,avg_price money null)
AS
BEGIN
INSERT @table
SELECT type,avg(isnull(price,0)) as avg_price
FROM titles
GROUP BY type
HAVING avg(isnull(price,0))> @price
RETURN
END
SELECT * FROM AveragePricebyType3(15.0), this also gives same result.
type        averageprice
—————————————
trad_cook        15.9633
————————————————————
Big Question: Why use multi-statement table-valued functions instead of inline table-valued functions?
1) Generally, we use multi-statement table-valued functions when we need to perform further operations (for example, inserts, updates, or deletes) on the contents of the table variable before returning a result set.
2) We would also use them if we need to perform more complex logic or additional processing on the input parameters of the function before invoking the query to populate the table variable.
Types of SQL statements allowed in a function include the following:

a) DECLARE statements to define variables and cursors that are local to the function.
b) Assignments of values to variables that are local to the function, using the SET command or an assignment select.
c) Cursor operations on local cursors that are declared, opened, closed, and de-allocated within the function. FETCH statements must assign values to local variables by using the INTO clause.
d) Control-of-flow statements such as IF, ELSE, WHILE, GOTO, and so on, excluding the TRY…CATCH statements.
e) UPDATE, INSERT, and DELETE statements that modify table variables defined within the function.
f) EXECUTE statements that call an extended stored procedure. (Any results returned by the extended stored procedure are discarded.)
Nesting of User Defined Function: User-defined functions can also call other user-defined functions, with a limit of 32 levels of nesting. Nesting of functions can help improve the modularity and reusability of function code.
CREATE FUNCTION dbo.getonlydate3()
RETURNS datetime
as
BEGIN
DECLARE @date datetime
SET @date = dbo.striptime( getdate())
RETURN @date
End
How to get information about Functions: To get information by using the provided system procedures and queries against the INFORMATION_SCHEMA.routines view. The following sections describe these methods.

exec sp_helptext getonlydate
Text
create function getonlydate()
returns datetime
as
begin
return(select convert(datetime,convert(date,getdate())))
end
29
In addition to sp_helptext, you can write queries against the INFORMATION_SCHEMA.routines view to display the source code for a function:
SELECT routine_definition
from INFORMATION_SCHEMA.routines
where routine_name = ‘getonlydate’
and specific_schema = ‘dbo’
and specific_catalog = ‘bigpubs2008′
Conclusion: User-defined functions in SQL Server 2008 allow you to create reusable routines that can
Help make your SQL code more straightforward and efficient. Table-valued functions provide a way to create what are essentially parameterized views, and you can include them inline in your queries, just as you would in a table or view.

Insert Data Into SQL Server Using jQuery in ASP.Net

In this article I provide a quick overview of how to insert a record into SQL Server using jQuery. You can do it in many ways such as using Generic Handlers and using a WCF service. Here you will see it using jQuery with Ajax in ASP.Net. First we create a database table named "TestTable".


Creating SQL Database Table

This database contains one table named test.
CREATE TABLE [dbo].[TestTable](
      [Name] [varchar](50) NULL,
      [Email] [varchar](100) NULL

)

Designer Source Code :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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 id="Head1" runat="server">
    <title>AutoComplete Box with jQuery</title>
    <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css"
        rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $('#Button1').click(function () {
                $.ajax({
                    type: 'POST',
                    contentType: "application/json; charset=utf-8",
                    url: 'Default.aspx/InsertMethod',
                    data: "{'Name':'" + document.getElementById('txtUserName').value + "', 'Email':'" + document.getElementById('txtEmail').value + "'}",
                    async: false,
                    success: function (response) {
                        $('#txtUserName').val('');
                        $('#txtEmail').val('');
                        alert("Record Has been Saved in Database");
                    },
                    error: function ()
                    { console.log('there is some error'); }
                });
            });
        });      
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div class="demo">
        <div class="ui-widget">
            <table>
                <tr>
                    <td>
                        <label for="tbAuto">
                            Enter UserName:
                        </label>
                    </td>
                    <td>
                        <asp:TextBox ID="txtUserName" runat="server" ClientIDMode="Static" Width="202px"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Email:
                    </td>
                    <td>
                        <asp:TextBox ID="txtEmail" runat="server" ClientIDMode="Static" Width="210px"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    </td>
                    <td>
                        <asp:Button ID="Button1" runat="server" Text="Button" ClientIDMode="Static" />
                    </td>
                </tr>
            </table>
        </div>
    </div>
    </form>
</body>
</html>

 Code Behind :

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Services;
using System.Web;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    [WebMethod]
    public static string InsertMethod(string Name, string Email)
    {
        //List<string> result = new List<string>();
        SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Gangwar; integrated security=true;");
        {
            SqlCommand cmd = new SqlCommand("Insert into TestTable values('" + Name + "', '" + Email + "')", con);
            {
                con.Open();
                cmd.ExecuteNonQuery();
                return "True";
            }
        }
    }
}