Friday, August 25, 2017

Sealed Vs Private Class

Sealed Class:
Sealed keyword is used to avoid the inheritance of that class. it cannot be inherited.
In inherited hierarchy Sealed class is the leaf node.
Even a public class is declared as Sealed it will be ban to inherit.
No instance of a Sealed class is possible because we cannot create constructor of it.

Private Class:
Private keyword is used to limit the scope. if we declare a class private within a class means it will not be visible out of base class.
Private classes cannot be used directly as this will throw compilation error.
We have must nest a private class within another public class.
We can create constructor for a private class so instance of private class is possible.
Private class is only visible to that class it is defined and within it.
Private class can be inherited but a private class cannot be inherited from a public class. because a child class can not be more accessible than base class. Either parent should be more accessible or equal.
Comments and Suggestions are always Welcome!

Wednesday, August 23, 2017

Ado.Net Components, DataSet or DataReader

ADO.Net:
There are two main components of ado.net which are .Net framework Data provider and DataSet.

1. .Net frameword Data provider are very lightweight, used to connect with database, run the command/query by using Command object and get the results. then it depends upon us how to treat that Result set either we directly process the data or we place in DataSet as per requirements. there are different types of Data Providers in .Net Framework like:
a. .NET Framework Data Provider for SQL Server
it gives data access for Microsoft SQL Server. Uses namespace System.Data.SqlClient.
b. .NET Framework Data Provider for OLE DB
Used for data sources discovered by using OLE DB. Uses namespace System.Data.OleDb.
c. .NET Framework Data Provider for ODBC
It is used for data sources discovered by using ODBC. Uses namespace System.Data.Odbc.
d. .NET Framework Data Provider for Oracle
It provides supports for Oracle data sources. namespace System.Data.OracleClient
e. EntityClient Provider
give data access to Entity Data Model (EDM) applications. System.Data.EntityClient
f. .NET Framework Data Provider for SQL Server Compact 4.0.
It is for Microsoft SQL Server Compact 4.0. using System.Data.SqlServerCe.

Mainly .Net Framework Data Providers uses few core Objects which are give below:
a. Connection: Used to establish connection. its base class is DbConnection
b. Command: used to execute a database command. this object base class is DbCommand
c. DataReader: it reads a forward-only(DataReader reads one row at a time then move forward to next row then next, it will not move back thats why it's called Forward only), read-only(it will just read records from data source does not allow any editing). base class for DataReader objects is DbDataReader class.
d. DataAdapter: it is used to populates the DataSet. basically it provides communication between data source and DataSet to enable data access and manipulation of data.

There are some additional Objects also names as Transaction, CommandBuilder, ConnectionStringBuilder, Parameter, Exception, Error and ClientPermission.

2. The DataSet: it is second component of ado.net. it is mainly designed to data access from any data source or we can say independent of data source, it can be used with different data source like XML data or to manage data local. DataSet can have one or more than one DataTable objects which have rows, columns even primary and foreign keys contraints. we can perform within application qurieson DataSet to manipulate the data.

Choose DataReader or DataSet: when you think you want only to read data and there is no requirements to perform editing then DataReader is preferable and fast also but when you require data to cache it locally in application and do some manipulation and perform some other operations then DataSet is best.

Tuesday, August 22, 2017

SqlDataSource and ObjectDataSource Differences


why we should use SqlDataSource and why ObjectDataSource.
why we do not directly use SqlDatasource and prefer ObjectDataSource even SqlDatasource is less code and fast also.

> Basically ObjectDatasource is business object that calls the stored procedure to get records from database whereas we can do directly this one by using Sqldatasource in very less code.

> Objectdatasource provide a perfect abstraction of our design and code.

> While using objectdatasource our pages does not depend upon the database information used in code behind.

> SqlDataSource is typically two-tier in nature but ObjectDataSource is based on three-tier architecture.

> SqlDataSource requires very less line of code to connect with database and ObjectDataSource needs more coding to build data access classes to communicate with database.

> SqlDataSource does not support data encapsulation whereas ObjectDataSource is fully designed for encapsulation.

> For SqlDataSource we write complete connection string for database connection. ObjectDataSource exposes TypeName attribute as a middle layer class mainly used to perform database operations.

> Sqldatasource uses ADO.NET mainly. we have to write all database queries, connections and commands on the page which looks very raw data.

> Objectdatasource behave like a wrapper which control all crud operations.

> Normally if we have to do same thing for a short time span and for a limited time perioed then it's okay to use SqlDatasource but when we talk about a big application and for long run we prefer to use Objectdatasource because direcltly use sqldatasource in your code behind is very quick but dirty habbit which is not preferable always.

> When we face frequent changes in our system then we have to go for page by page and change all sections of code which are directly accessing that change. so by using objectdatasource it is very professional and pretty to have such changes in practice.

Reference: https://forums.asp.net/t/1876836.aspx?When+do+I+use+SqlDataSource+vs+EntityDataSource+vs+ObjectDataSource

Get Total Count From Second Table - SQL Server

How to get count of a column from second table if there is any entry using SQL Server.

Hi, Today we will check that how we can get count or number of rows in a second table agains a column in first table. for example we have a table of products and we want to check total number of orders against a product. this can be done in various ways but we want to avoid sub query as mostly i don't prefer sub query due to efficiency of execution.

For this problem resolution we can use COMMON TABLE EXPRESSION. basically a common table expression (CTE) is a temporary result set. there are some limitations also but as above mentioned task we can use CTE which is not going to fall us in performance issue. As a comparison to temp table there is no need to worry about it, in CTE, as these have an execution scope within statement. CTE can also be used within Views.

Anyway Let's have a look practically: we want to get count for each product from order table but it should also be shown as column in normal query we don't want to use union or some other technique to combine the result set.

For example: normally we are showing mobile products in table and we get result from below query:
SELECT PROD_ID,PROD_TITLE 
    FROM PRODUCT_TABLE  
    WHERE CATEGORY_NAME = 'MOBILE'

and result show like this

but we want to show the count of each product as third column as shown below:

so we will use a common table expression to get this done very simple. below is shown common table expression:
;WITH CTE_PROD_COUNT (PROD_ID,TOTAL_COUNT) 
AS (
     SELECT PROD_ID,COUNT(*)TOTAL_COUNT 
      FROM ORDER_TABLE GROUP BY PROD_ID)

so how we will combine this query with our simple product query to get it done in a single result set. we will use CTE common table expression as table simple table and join it will our original query to get count for each table as below:
;WITH CTE_PROD_COUNT (PROD_ID,TOTAL_COUNT) 
  AS (
        SELECT PROD_ID,COUNT(*)TOTAL_COUNT 
          FROM ORDER_TABLE GROUP BY PROD_ID)
    
   SELECT P.PROD_ID,P.PROD_TITLE,CT.TOTAL_COUNT 
   FROM PRODUCT_TABLE P 
   LEFT OUTER JOIN CTE_PROD_COUNT CT ON CT.PRODID1 = P.PROD_ID
    WHERE P.CATEGORY_NAME = 'MOBILE'

With this query you can use your current filter and CTE will simple add a total count of each product in query as a column.
it will look like this:

Hopefully you like this experience. Comments and suggestions are always welcome!!

Sunday, August 20, 2017

Disadvantage of Session or Requests stuck in IIS due to RequestAcquireState

Some of the clients were complaining about website speed so Last month I was monitoring web server and found out that there is a request queue and server is not efficiently handling the entire request rather stuck on the event called RequestAcquireState due to which a queue is built on server. After searching I’ve come to the conclusion that it was happening due to session used in the website and there are pages where we are using sessions. So whenever user request the page where session is used it locks down other requests from entering into that page which causes slowness in web application and our clients were complaining about speed.



I’ve read about session in details and came to know about concurrency issue of sessions. If you send two requests at the same time, ASP won't even start on the second request until the first one is finished. This can cause unexpected and poor performance on applications that use a lot of AJAX or other logic that is supposed to be asynchronous.

So finally I’ve removed session from website pages and rather use query string and other techniques to send data from one page to another which solves my problem.

Hope this tutorial will be helpful for you.

Difference between SingleOrDefault and FirstOrDefault in C# Linq

In this tutorial we will see the difference between SingleOrDefault () and FirstOrDefault ().

SingleOrDefault():-
When you are exactly sure that there is only 1 result/record you should use SingleOrDefault(). If there is no result/record returned or there is more than 1 result/record this function will throw exception.
Example:-
string[] fruits = { "orange" };

string singleFruit = fruits.SingleOrDefault();

Console.WriteLine(singleFruit);

/*
 This code produces the following output:

 orange
*/


FirstOrDefault():- It will not throw any exception in case when there is no result/record returned from database rather it returns null when there is no result. It is slightly better in performance than SingleOrDefault()that is why it is recommended to use when you need single value from database.
Example:-

int[] integers = { };
int first = integers.FirstOrDefault();
Console.WriteLine(first);

/*
 This code produces the following output:

 0
*/

Wednesday, April 26, 2017

social profiles with schema.org markup on google


Google uses schema.org structured data to highlight the social profiles of a website(organization) or a person on their knowledge Panel. it is a good way as per SEO experts to show an organization company profile in google search knowledge base by using schema.org.
Whenever you will perform a search on Google to check any brand, you will be given that brand's information on Google right side of search. This google knowledge base box will give you an overlook about company CEO name, logo, stock exchange, contact information and social profile, which is now a days very important for any brand to compete in the market.
We can also add for our organization these information by using schema.org structured data to increase our SEO rank and our website traffic.
Now google is supporting a limited range of social media like, Facebook, Twitter, Google+, Instagram, YouTube, LinkedIn, Myspace, Pinterest, SoundCloud and Tumblr. we can use other social profiles also but google does not included that in search results. We can use two way one is JSON-LD and other is microdata.
Here is the example of JSON-LD:
<script type="application/ld+json">
{
  "@context": "http://schema.org",
  "@type": "Person",
  "name": "your name",
  "url": "http://www.your-site.com",
  "sameAs": [
    "http://www.facebook.com/your-profile",
    "http://instagram.com/yourProfile",
    "http://www.linkedin.com/in/yourprofile",
    "http://plus.google.com/your_profile"
  ]
}
</script>

If you want to set for your organization just need to change type to "Organization". you need to add this json-ld part in the head section of your website.
Here is the example for microdata:

<span itemscope itemtype="http://schema.org/Organization">
  <link itemprop="url" href="https://your-company.com">
  <a itemprop="sameAs" href="https://www.facebook.com/your-company">FB</a>
  <a itemprop="sameAs" href="https://www.twitter.com/YourCompany">Twitter</a></span>

you can change Organization to person as per your requirements. once you are finished with coding and you updated on your website then check with view source code its showing their perfectly and then you can check your markup with google structured data from here:
https://search.google.com/structured-data/testing-tool
you can enter directly your page url or paste that specific part of code in testing tool it will show you its working fine or not, if there is any error it will show there also.

Wednesday, April 19, 2017

Constraints in SQL Database

Today we will learn what are constraints in SQL. in our daily routine work we are using many constraints but in actual we don't know what are constrains. basically constraints are rules, such rules which are enforced on data column of a table. we can apply a constraint on a table or a column. these are used to check the accuracy of data and reliability of data in any circumstances.
So, let's have a look on constraints one by one which are mostly used in SQL.
NOT NULL Constraint − will make sure this column cannot be NULL.
For example we have a PRODUCTS table and we want to add NOT NULL constraint on PRICE Column:
ALTER TABLE PRODUCTS 
       MODIFY PRICE DECIMAL (18, 2) NOT NULL;


DEFAULT Constraint − it will set Default value for a column if none is given.
how to add Default Constraints:
ALTER TABLE PRODUCTS 
        MODIFY PRICE DECIMAL (18, 2) DEFAULT 10.00; 

To DROP a default Constraint we will use this query other than normal DROP constraint query:
ALTER TABLE PRODUCTS 
   ALTER COLUMN PRICE DROP DEFAULT;


UNIQUE Constraint − this will check Column Values are unique.
How to Add Unique Constraint:
ALTER TABLE PRODUCTS 
   MODIFY QTY INT NOT NULL UNIQUE;


PRIMARY Key − it will set unique identity for each row/record.
How to add:
ALTER TABLE PRODUCTS 
   ADD CONSTRAINT PK_PRODTID PRIMARY KEY (PROD_ID, PRODUCT_NAME);


FOREIGN Key − it will also Uniquely identifies a row/record for any given database table.
How to add:
ALTER TABLE ORDERS 
   ADD FOREIGN KEY ((PRODUCT_ID) REFERENCES PRODUCTS (PROD_ID);


CHECK Constraint − it used to check that all values of column are according to a specific condition.
How to Add:
ALTER TABLE PRODUCTS
   MODIFY QTY INT NOT NULL CHECK (QTY >= 2);


INDEX − Basically Indexes are used to retreive data from a table quickly.
How to Create Index:
CREATE INDEX idx_price
   ON PRODUCTS (PRICE);


Constraints can also be added while creating a table with CREATE TABLE Statement otherwise you can set later with ALTER TABLE statement.

Dropping Constraints:
We can also remove a constraint by using below query:
For example, to drop the primary key constraint in the PRODUCTS table, you can use the following command.
ALTER TABLE PRODUCTS DROP CONSTRAINT PK_PRODTID;

Comments and Suggestions are always Welcome!

Tuesday, April 18, 2017

Tracing in asp .net Application for Beginners

Today we will learn how to use Tracing in Asp.Net to check the performance of Web Application.
In Web Development the most concerned part is Performance. so how we can check which part of our application is slower either its Asp.Net code or our Database queries. you can check your SQL queries performance by using SQL Profiler, look the execution plan which part of query is taking more time than normal and then you can easily find alternative or some good way to resolve query slowness.
But if query is performing fast in database then it means there is something in our application which is making causing slowness. In production environment its not easy to check which line of code is making problem so by using Tracing we can easily check where is the problem actually. We can turn on Tracing by setting page directive Trace="true". once its set on page then we can check at the end of page or in trace.axd file. from trace log in the file we can easily check which piece of code is making problem.
Let's have look on code:
First in page directive you need to set:
<%@ Page Language="C#" AutoEventWireup="true" Trace="true" CodeFile="WebPage.aspx.cs" Inherits="WebPage" %>
Now we will create three Gridviews to check each gridview data load performance on Page load.
<div>
            <b>All Orders</b>
            <asp:GridView ID="GridView1" autogeneratecolumns="True" runat="server"></asp:GridView>    
            <br />
            <b>Delivered Order</b>
            <asp:GridView ID="GridView2" autogeneratecolumns="True" runat="server"></asp:GridView>    
            <br />
            <b>Cancelled Order</b>
            <asp:GridView ID="GridView3" autogeneratecolumns="True" runat="server"></asp:GridView>    
        </div>


And In code behind we will call our Stored Procedure on Page load, here i am not going to write full code for calling stored procedures, we will just understand how Tracing will work:
protected void Page_Load(object sender, EventArgs e)
    {
        Trace.Warn("GetAllOrders() Stored procedure started");
        GetAllOrder();
        Trace.Warn("GetAllOrders() Stored procedure End");

        Trace.Warn("GetDeliveredOrder() Stored procedure started");
        GetDeliveredOrder();
        Trace.Warn("GetDeliveredOrder() Stored procedure End");

        Trace.Warn("GetCancelledOrder() Stored procedure started");
        GetCancelledOrder();
        Trace.Warn("GetCancelledOrder() Stored procedure End");

    }


Above we discussed it was page level Tracing, We can also set Tracing on application level by using below web.config code. it save trace data for up to 40 requests.
<configuration>
  <system.web>
    <trace enabled="true" requestLimit="40" localOnly="false" />
  </system.web>
</configuration>


Comments and Suggestions are always Welcome!

Sunday, April 16, 2017

Insert Data from one Database Table to another Database Table on Different Server

Today we are going to learn how to insert data from one database server table to another database server table.
So let’s start writing the query.

Insert into [Server/Machine Name].[Database Name].[Schema].[Table Name]
Select * from [Server/Machine Name]. [Database Name].[Schema].[Table Name]

If you want to pick specific columns then you will write this query like this

Insert into [Server/Machine Name]. [Database Name].[Schema].[Table Name](Column1,Column2)
Select Column1, Column2 from [Server/Machine Name]. [Database Name].[Schema].[Table Name]

Comments and Suggestions are Always Welcome!

Insert Data From one Database Table to another Database Table on Same Server

Today we are going to learn how to insert data from one database table to another database table. I am going to write a separate post in coming days to insert records from different database servers.
So without wasting time let’s have a look at the query to insert records from one DB table to another DB table on a same server.

Insert into [Database Name].[Schema].[Table Name]
Select * from [Database Name].[Schema].[Table Name]

If you want to pick specific columns then you will write this query like this

Insert into [Database Name].[Schema].[Table Name](Column1,Column2)
Select Column1, Column2 from [Database Name].[Schema].[Table Name]

For example you are moving customer table’s data from Northwind database to AdventureWorks database on the same server/machine/computer then your query will be

Insert into [AdventureWorks].[dbo].[Customer]
Select * from [Northwind].[dbo].[Customer]

Comments and Suggestions are Always Welcome!

Format currency without jQuery plugin

In this tutorial you will learn how to format currency without using any jQuery plugin.
Let’s have a look how to accomplish this

Javascript Function:-
function formatCurrency(total) {
    var neg = false;
    if(total < 0) {
        neg = true;
        total = Math.abs(total);
    }
    return (neg ? "-$" : '$') + parseFloat(total, 10).toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, "$1,").toString();
}

Call of the function will be like this:
formatCurrency(8000000000);
formatCurrency(0.700);
formatCurrency(100);

Output will be
$8,000,000,000.00
$0.70
$100.00

Tuesday, April 11, 2017

Retrieving Selected Item Text, Value and Index of Dropdown

Today we will check in Asp.Net, how to get a value from dropdown, its item text and index also. so i think without discussing more on theory let's go to code and create a dropdown with some sample data.

<asp:DropDownList ID="ddlSampleData" runat="server">
    <asp:ListItem Text="Select Name" Value="-1"></asp:ListItem>
    <asp:ListItem Text="Pakistan" Value="1"></asp:ListItem>
    <asp:ListItem Text="England" Value="2"></asp:ListItem>
    <asp:ListItem Text="Ireland" Value="3"></asp:ListItem>
</asp:DropDownList>

<asp:Button ID="btnGetValddl" runat="server" Text="Button" onclick="btnGetValddl_Click" /> 

You can create a dropdown by using Asp.net toolbox, just drag and drop the dropdown control after that add some listitem to dropdown from proprty or by code. We created a asp button also to perform the operations.
On this button click event we will get the value, text and index of selected item with a very simple way and then we will write this to our page by using HttpResponse's Write method. here is the code for that button click event.
protected void btnGetValddl_Click(object sender, EventArgs e)
    {
        if (ddlSampleData.SelectedValue != "-1")
        {
            Response.Write("Selected Item's Text = " + ddlSampleData.SelectedItem.Text + "<br/>");
            Response.Write("Selected Item's Value = " + ddlSampleData.SelectedItem.Value + "<br/>");
            Response.Write("Selected Item's Index = " + ddlSampleData.SelectedIndex.ToString());
        }
        else
        {
            Response.Write("Select Countrt First!");
        }
    }


As on the first item of dropdown we set the value as -1, this value will tell us either a country is selected from the dropdown or not and if no country is selected then we will show message "select country first".
To get the select item Text we used:
ddlSampleData.SelectedItem.Text

To get the Select Item's Value we can:
ddlSampleData.SelectedItem.Value

or
ddlSampleData.SelectedValue
To get the index of Select Item's, we can do:
ddlSampleData.SelectedIndex

we can perform many other operation like if we want to set a value by default it should be select like if we want in our dropdown pakistan should come selected by default then we can set on page load event like:
 ddlSampleData.SelectedValue = "1";

or we can achieve this by using selectedIndex:
 ddlSampleData.SelectedIndex = 1;

if we want to bind our dropwdown directly data from data then we can do something like below, for example we have to bind a city dropdown:
<asp:DropDownList ID="slctCity" runat="server"></asp:DropDownList>


we are going to create a function so we can call this from different pages:
public void ddlbind(DropDownList ddl, string query, string text, string val)
    {
        DataSet ds = new DataSet();
        da = new SqlDataAdapter(query, con);
        da.Fill(ds);
        ddl.DataSource = ds.Tables[0];
        ddl.DataTextField = text;
        ddl.DataValueField = val;
        ddl.DataBind();
    }

in this you need to set con with your SQL Connection and after that pass just query and dropdown id.
we will call like this:
ddlbind(slctCity, "select ct_name,ct_charges from tbl_city", "ct_name", "ct_charges"); 

Comments and Suggestions are Always Welcome!

Sunday, April 9, 2017

Import Data in SQL Server Table From Excel

How to import data in sql server table from excel spreadsheet
In this tutorial you will learn how to import data into sql server table from an excel spreadsheet using SQL Import and Export Wizard with easy steps. Let start doing it step by step.
Step 1: Pick your Excel source


Step 2: Pick your SQL Server target database


Step 3: Pick your source worksheet (from Excel) and your target table in your SQL Server database; see the "Edit Mappings" button!


Step 4: Check (and change, if needed) your mappings of Excel columns to SQL Server columns in the table:


Step 5: If you want to use it later on, save your SSIS package to SQL Server:


Step 6: Success! This is on a 64-bit machine, works like a charm - just do it!!

Comments and Suggestions are always Welcome!

Saturday, April 8, 2017

Web Application Life Cycle Event ASP.NET

Events in the life cycle of a web application:
Today we will discuss the events in the life cycle of a web application. As the name shows Application events are used across the application. these events are used to initialize some data which will be available to all current user of application. As we know Session are also used to initialize the data but that data will be available for individual session not for whole application. First let's have a look on Web application events available in Global.asax file:
void Application_Start(object sender, EventArgs e)
{
    //This Code will run on application start
}
void Application_End(object sender, EventArgs e)
{
    //This Code will run on application shutdown
}
void Application_Error(object sender, EventArgs e)
{
    //This Code will run when some unhandled error occured
}
void Session_Start(object sender, EventArgs e)
{
        //This Code will run when a new session starts
}
void Session_End(object sender, EventArgs e)
{
    //This Code will run on when a session ends
} 


1. Application_Start: This event runs when an application starts running first time if it was not running before. mostly this even is used to initialize data that needs to be available across all the application. we can set an application state variable to keep records that when an application starts and we can also count of application start event fired.
void Application_Start(object sender, EventArgs e)
{
    // Create Application state variables
    Application["AppStartDateTime"] = DateTime.Now;
}

This will available across the application if required on some pages.

2. Application_End: This event will be fired when application shutdown. we can use this event to keep records and also, we can generate some alert so we could be aware of application is running or shutdown. this event will be fired when IIS is restarted basically it is a once per application event.
void Application_End(object sender, EventArgs e) 
{
    //Code
}


3. Application_Error: This event is mostly used than above mentioned events because when ever and error occurred then this event will be fired and we can set redirect according to error status code so our used should be face some terrible error page and we present some user-friendly message to use and mention the reason why user come to this page.
void Application_Error(object sender, EventArgs e) 
    { 
        // Code runs when an unhandled error occurs
    Exception ex = Server.GetLastError();
    if (ex is HttpException && ((HttpException)ex).GetHttpCode() == 404)
    {
        Response.Redirect("~/404.aspx");
        //Server.Transfer("~/Error.aspx");
    }
    else
    {
        // your global error handling here!
    }
}


4. Session_Start: This event will be fired every time when a new user/browser visits the application with different session id. by using this Event we can keep record how many user visits our application and we can check how many users currently on our application.
void Session_Start(object sender, EventArgs e)
{
    //Application["TotalUser"],Application["CurrentUser"] will be initialize in application_start
    // Increment TotalUserSessions by 1
    Application["TotalUser"] = (int)Application["TotalUser"] + 1;
    Application["CurrentUser"] = (int)Application["CurrentUser"] + 1;
}


5. Session_End: This event is fired when a single user's session ends or timed out.
void Session_End(object sender, EventArgs e) 
    {
        // Code that runs when a session ends.  
    Application["CurrentUser"] = (int)Application["CurrentUser"] - 1;   
    }
Note: Session End event is fired only when we have set session state mode to "InProc". In other state mode, it will not fire Session End event.

Comments and Suggestions are Always Welcome!

Stored Procedures Best Practices

Stored Procedures Best Practices:
Today we will check which performance parameters and other point we should keep in mind while creating a stored procedure.
There are many benefits of using Stored procedures. basically a stored procedure is a set of SQL statements which are stored in database in compiled form with a specific name and most important benefit is, we can use this stored procedure in number of programs, with this feature we are not required to write query again and again.
we can also perform many operations at database level so it will not load our application and application will perform well. we can use many programming concept like IF-ELSE, WHILE loop and other constructs also.
Here i am going to share some good approaches while creating a stored procedure which, a developer should use to enhance the performance of stored procedure. there are some standard rules and signature to create stored procedure but we can add other things as per our requirements. i will not go to details of each benefit, will give a short description. so let’s have a look:
1. SET NOCOUNT ON: we should always use SET NOCOUNT ON statement at the start of your stored procedure code to minimize the unnecessary network round trips.
2. PARAMETER SNIFFING: This is a very good concept to study, we should never use our stored procedure parameters directly in SQL statement because it will cause Parameter Sniffing. first, we should assign the parameter to local variable and then we should pass to SQL statements.
3. Use table variables: Table Variables are very good for small data instead of temp table. it will give your stored procedure a good rise in performance.
4. Use of Temp Tables: Temp tables are not always recommended but if it is required and no other option then always create indexes on temp table. it is for big data like millions of records in this case do not use table variables.
5. Avoid using CURSORS: CURSORS are used to manipulate the data row-by-row but it will make your Stored procedure very slow, always try to avoid CURSORS and check for other SET based approaches.
6. Use TRY-CATCH block: This is a very good approach to use TRY-CATCH in SQL statement so if there is any error you can handle at Database and return a proper flag to application. it will also never let your application do any operation and check data for exception handling.
7. Using Transactions: BEGIB TRANSACTION, COMMIT/ROLLBACK these are good to use when you are going to perform some action on your database but always try to use short transaction with them otherwise it will create blocking in database and deadlocks. but by using Transactions we can keep track of successful/unsuccessful of your transaction.
8. Aliasing objects: although database will allow you to use SQL reserve keyword but try to avoid use reserved keywords otherwise it will affect your statement performance and its good approach to use meaningful naming conventions.
9. Avoid GOTO Statement: Like all other programming language its bad practice to use GOTO statement. it good to use and filter you statements with IF-ELSE without using GOTO.
10. Avoid 'sp_' prefix: your Stored procedure should not start with 'sp_' because in this case compiler will go first to master database and then it will come back to current database so it will slow down your Stored procedure.
11. Use fully qualified objects name: In SQL queries, it will help the compiler to find cached and compiled object quickly.
12. Use WITH ENCRYPTION: if it is required you can use this to hide the Stored Procedure statement.
13. Add Comments: always add comments in your procedure like a good programmer.
14. Use BEGIN-END code block: Whole Stored procedure should be in BEGIN-END block.
Below is the standard template for creating stored procedure which is generated by SQL Management studio.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO


Comments and Suggestions are Always Welcome!

Monday, April 3, 2017

Highlight Duplicate Records in GridView

How to highlight Duplicate Records in GridView.
Hi, today I will share my experience with you which I face long time ago. problem was how to identify if there is any duplicate record. so, after spending too much time on search and finding solution I reached to a solution. below is given a function which finds the duplicate rows in a Gridview without much effort, just we need to pass our Gridview ID and it will find and highlight the duplicate rows. we can find duplicate row depending upon a cell value within a row or for multiple cells also.
Here is the function:

public void HighlightDuplicate(GridView gridview)
    {
        for(int currentRow = 0; currentRow < gridview.Rows.Count - 1; currentRow++)
        {
            GridViewRow rowToCompare = gridview.Rows[currentRow];
            for (int otherRow = currentRow + 1; otherRow < gridview.Rows.Count; otherRow++)
            {
                GridViewRow row = gridview.Rows[otherRow];
                bool duplicateRow = true;
                //check Duplicate on Name Coloumn
                if ((rowToCompare.Cells[2].Text) != (row.Cells[2].Text))
                {
                    duplicateRow = false;
                }
                else if (duplicateRow)
                {
                    rowToCompare.BackColor =System.Drawing.Color.Yellow;
                    row.BackColor = System.Drawing.Color.Yellow;
                }
            }
        }
    }


we will call this function after DataBind() is done to the Gridview.
Call function
HighlightDuplicate(GridView1);

In current function i am checking value of Cell[2] you can set as per your requirements.
This function can also be used for some other operations on the Gridview because you have full control on rows and cell of Gridview even you can find a specific value or control in the Gridview row and do whatever you want.
Comments and Suggestions are Always Welcome!

Sunday, April 2, 2017

Difference in ExecuteNonQuery ExecuterReader ExecuteScalar

ExecuteNonQuery() Vs ExecuterReader() Vs ExecuteScalar()
Today we will check the difference between ExecuteNonQuery, ExecuterReader and ExecuteScalar and we will try to understand which one should be used for which type of Database operations:
1. ExecuteNonQuery():
ExecuteNonQuery is always recommended for such operation in which you are not required to return anything from the query. it's used for INSERT,UPDATE and DELETE operations or other Action Queries like Alter, Drop,Create. it's function is same either we use direct SQL Query or Stored Procedure. so becarefull and do not use with SELECT Statements because it will always return total count of affected rows by query or stored procedure. we can not do much with its return because it is only returning int and its assigned to integer variable only.
Example:
        ExecuteNonQuery("DELETE FROM MYTABLE");
    ExecuteNonQuery("UPDATE TABLE SET COLUMNAL = 'Code'");

If we use ExecuteNonQuery with a Select Statement then it will do nothing. it will execute the Query but it's row affected will be negative -1 you can throw any error also.

2. ExecuterReader():
ExecuteReader is used to Read the Data from SQL Statement and its recommended to use with SELECT Operations. ExecuteReader returns IDataReader object when fetch the records from Database and we can perform other operation on this object as per our requirements like we can bind GridView etc. we use .Read() method of SqlDataReader which read the data one by one. it will also work for Action Queries but best to use for SELECT non action queries. it will return a collection of all data on which we can perform operation row by row or column by column also.

       IDataReader dr = ExecuteReader("SELECT * FROM MYTABLE"); 
     while(dr.Read())
     {
             Response.Write("Name: " + dr["ColumnName"]);
    } 


3. ExecuteScalar():
ExecuteScalar is always used for a single column value. it will return first column of first row of Query. it is used when we are required one column or a single value like we want to get SUM of a column,Average of Column or Count and also it's usable when we want to know ID of employee from employees table against a single employee. it will be usefull for Non Action quries. it will return the an object type.

       Int32 Value = Convert.ToInt32(ExecuteScalar("SELECT SUM(SALARY) FROM EMPLOYEES")); 
 Int32 Value = Convert.ToInt32(ExecuteScalar("SELECT AVG(INCENTIVE) FROM EMPLOYEES")); 


it's always used for single value but if we used with multiple column statement then it's behavior will also remain same it will return first row first column value.
Comments and Suggestions are Always Welcome!

Tuesday, March 28, 2017

Difference Between Abstract Class and an Interface

What is Difference Between Abstract Class and an Interface:
Today we will discuss the differences between an Interface and Abstract Class. For the beginners, it’s a little bit difficult to understand the concept and difference between them. As we know in C# multiple inheritance is not allowed but we can achieve this by using Interface and this is the main difference between them, A class can implement more than one Interface but can inherit only one abstract class.
However, let’s have a look on some other features and differences.
1. Default Implementation: An interface does not have any type of code implementation it just provides the signature of methods and properties but An Abstract class can have code implementation, definition and may have details to be overridden.
2. Access Modifier: As we know interface provide signature and which is going to implement, this interface, that will define and provide the code implementation so in Interface there is no access modifier all properties and functions are by default public whereas an abstract class can have access modifier for subs, properties and functions.
3. Multiple Inheritance: Interface provide multiple inheritance in C# by inheriting multiple interfaces whereas a class may inherit only one abstract class.
4. Performance: Interfaces are slower than abstract class because an interface requires more time to check the signature and exact method in related classes.
5. Changes or Add New Functions: when we update or add new function to an interface then we have to trace all implementations of interface and need to update all references which is a tough task. In abstract class if we add some new function then we have default code implementation, due to this, all code will work properly no need to change.
Now let's see code implementation.
Abstract Class: abstract class cannot be instantiated so its methods can be called with class name or by using inherited class object.

abstract class ABS_CLS1
{
    public int Addition(int a, int b)
    {
        return (a + b);
    }
}
class Simple_CLS : ABS_CLS1
{
    public int Multiplication(int a, int b)
    {
        return a * b;
    }
}
class Check_Impl
{
    static void Main(string[] args)
    {
        Simple_CLS ob = new Simple_CLS();
        int Total = ob.Addition(5, 10);
        Console.WriteLine("Result is {0}", Total);
        Console.ReadLine();
    }
}


In the above example, we can see, we created the object of class Simple_CLS which inherited our abstract class, so, by using that object we call abstract class method so it clears one more thing abstract class provide the public method in it and also those members which inherited from abstract class's base class.
Interface: interface is not a class, it is by self an entity nominated with word Interface.

interface TestInterface
{
    void testMethod();
}
class CallClass : TestInterface
{
    public static void Main()
    {
        CallClass cls = new CallClass();
        cls.testMethod();
    }
    public void testMethod()
    {
        Console.WriteLine("Called Interface method from implemented class");
        Console.ReadLine();
    }
}


As interface, cannot have code implementation so only one method signature is in interface and calling class have code implementation of that method because its mandatory to calling class implement interface method and properties.
Comments and Suggestions are always welcome!

Sunday, March 26, 2017

Differences between Class and Struct Asp Net


Today we will discuss the difference between class and struct in Asp.Net. As we already know that .Net is object oriented based so it has inside all concepts of OOP with their context.
If you have a little bit idea what is Reference Type and What is Value Type, then it’s very easy to understand the difference between classes and structs because this is the main and first difference in both.
Let's understand with a simple example Reference Type and Value Type:
Basically, structures are Value Type and classes are Reference Type, when we said reference type it shows from the name of this concept.
Reference Type is a reference to some location in the memory and if there is any change in a reference type or class object then it will be reflected on all instances of that object because it’s going to update the referred memory location not specific value on a single position. have a look on below example:

        Class Vehicle
    {
        Public Int NoOfTyres;     
    }


when we will use this class, and create an instance of this class then it will allocate the memory for this instance of this class type and will store the address of memory the class.

        Vehicle Vehicle_car1 =new Vehicle();
    Vehicle_obj1.NoOfTyres=4;
    Vehicle Vehicle_car2 =Vehicle_car1;
    Vehicle_car2.NoOfTyres=6;


In this example, we can check both instances of Vehicle class belong to same memory location because we assign Vehicle_car1 to Vehicle_car2 so they have same location. it also indicates that if there is any change in one it will update both because they both have same reference of memory.
As we update the value of second object Vehicle_car2.NoOfTyres=6; it will set both instances to 6. basically, we are getting NoOfTyres of an object by using different pointers.
But Structures are value type as we discuss earlier,
if we take same example,

        Structure Struct_Vehicle
    {
        Public Int NoOfTyres;     
    }
    
    Struct_Vehicle Str_Car1 =new Struct_Vehicle();
    Str_Car1.NoOfTyres=4;
    Struct_Vehicle Str_Car2 =Str_Car1;
    Str_Car2.NoOfTyres=6;


As per Value type when we create different instance and assign the first to second one then it will make copy of first and assign to second it will not refer to same location in memory. so, by updating the second it will not affect first's value and vice versa.
Value Type always contains a value and reference type can have null reference means it does not refer to anything.
Class also support the inheritance but struct does not.
Classes may have parameter less constructor but struct does not.
Structure cannot have destructors but a class can have a destructor.
Struct are in real an actual value and these can be empty but not null whereas class be referred to null.
Struct are best to use on small data of related groups.
Please share your suggestions and feel free to comments.

Saturday, March 25, 2017

How to format number in javascript jquery


Today we will figure out how to format a number utilizing JavaScript or jQuery. I was taking a shot at a report when I need the add up to be formatted and after some seeking on web I discovered some plugins however I would prefer not to use plugin on my website for such a little errand. At long last I've discovered strategy which works for me so I've chosen to compose a post to impart these little strategies to every one of you. Here I am sharing two strategies so it's your decision to use anybody of them which is appropriate for you.
Function 1 :-
//-----------------Apply comma formation on Amount using JavaScript ------------------

function CommaFormatted(amount) {
            var delimiter = ","; // replace comma if desired
            var a = amount.split('.', 2)
            var d = a[1];
            var i = parseInt(a[0]);
            if (isNaN(i)) { return ''; }
            var minus = '';
            if (i < 0) { minus = '-'; }
            i = Math.abs(i);
            var n = new String(i);
            var a = [];
            while (n.length > 3) {
                var nn = n.substr(n.length - 3);
                a.unshift(nn);
                n = n.substr(0, n.length - 3);
            }
            if (n.length > 0) { a.unshift(n); }
            n = a.join(delimiter);
            if (d.length < 1) { amount = n; }
            else { amount = n + '.' + d; }
            amount = minus + amount;
            return amount;
        }


Function 2:-
 This Function take string as parameter and by using jQuery .test() function and regular expression we are adding comma to the amount.i.e. 2000 will become 2,000

function addCommas(nStr) {
            nStr += '';
            x = nStr.split('.');
            x1 = x[0];
            x2 = x.length > 1 ? '.' + x[1] : '';
            var rgx = /(\d+)(\d{3})/;
            while (rgx.test(x1)) {
                x1 = x1.replace(rgx, '$1' + ',' + '$2');
            }
            return x1 + x2;
        }


Tuesday, March 21, 2017

Non-WWW to WWW with HTTP to HTTPS


Today we will discuss how to redirect non www to www.domain.com with http to https both at time. few days back while moving a domain from http to https as now google chrome 56 is marking the website with http as non-secure and it’s also mentioned by google that it’s just a warning and near future it will be fully implemented so i suggest you people also to move your domain to https. there are many benefits of https we are not going to discuss here so let's come to the point.
we will discuss the web.config rule to resolve the problem. i implemented https perfectly and on my website all URL mentioned below are working fine:

http://domain.com
    https://domain.com
    http://www.domain.com
    https://www.domain.com

but the problem was that google consider every URL different and in SEO it is not a good practice. so I want something like below:
http://domain.com => https://www.domain.com
    https://domain.com => https://www.domain.com
    http://www.domain.com => https://www.domain.com
    https://www.domain.com => https://www.domain.com

so all URL should be resolved to same URL so it will not make problem in SEO and also good for alexa ranking. In Asp.Net we can do this by URL Redirecting rules in your web.config. for URL Redirecting more information please check here Let's have a look on code how it will be done by using web.config URL Redirect rewrite rules.
In web.config section you can put these rule like below:


<rewrite>
      <rules>
        <clear />
        <rule name="Redirect to https" stopProcessing="true">
          <match url=".*" />
          <conditions>
            <add input="{HTTPS}" pattern="off" ignoreCase="true" />
          </conditions>
          <action type="Redirect" url="https://{HTTP_HOST}{REQUEST_URI}" redirectType="Permanent" appendQueryString="false" />
        </rule>
    </rewrite>

First i make this rule and it was working perfect for redirecting http to https for all url on my domain.

http://domain.com => https://www.domain.com working ok
http://www.domain.com => https://www.domain.com working ok
https://www.domain.com => https://www.domain.com working ok

but there was a problem with https non www url.
https://domain.com => https://www.domain.com not working with above URL Redirect rule. after that i tries to change the

From: <action type="Redirect" url="https://{HTTP_HOST}{REQUEST_URI}" redirectType="Permanent" appendQueryString="false" />
    TO: <action type="Redirect" url="https://www.{HTTP_HOST}{REQUEST_URI}" redirectType="Permanent" appendQueryString="false" />

but there was no change so after searching and too much R&D i came back to my old url redirect rule which was before working fine from simple http to www http and i try that rule again. give below:

<rule name="Redirects to www" patternSyntax="ECMAScript" stopProcessing="true">
            <match url=".*" />
            <conditions logicalGrouping="MatchAny">
                <add input="{HTTP_HOST}" pattern="^domain.com$" />
            </conditions>
            <action type="Redirect" url="https://www.domain.com/{R:0}" />
        </rule>

so I came to a result that with a single rule it’s not possible to resolve both problems, from http to https and from non www to www.
Above mentioned both rules we have to put in same web.config file and it's working fine for both issues.
here is the full version of both rules:

<system.webServer>
    <rewrite>
      <rules>
        <clear />
        <rule name="Redirect to https" stopProcessing="true">
          <match url=".*" />
          <conditions>
            <add input="{HTTPS}" pattern="off" ignoreCase="true" />
          </conditions>
          <action type="Redirect" url="https://{HTTP_HOST}{REQUEST_URI}" redirectType="Permanent" appendQueryString="false" />
        </rule>
        <rule name="Redirects to www.domain.com" patternSyntax="ECMAScript" stopProcessing="true">
            <match url=".*" />
            <conditions logicalGrouping="MatchAny">
                <add input="{HTTP_HOST}" pattern="^domain.com$" />
            </conditions>
            <action type="Redirect" url="https://www.domain.com/{R:0}" />
        </rule></rules>
</rewrite>
</system.webServer>


Note: its not final, i am still working on this if you people can comment/suggest some good thing better than this so please share it will be appreciable.
Comments and Suggestion are always welcome!

Monday, March 20, 2017

Validation Controls in Asp Net


Today we will discuss Validation Controls in Asp.Net and we will have a deep look on Custom Validator control in Asp.net.
Validation is a most important process either it is a web based application, desktop application or some other like android or iPhone. without proper validation, it is very difficult for a system to survive because we don't know the nature of client/audience so as per our requirements we will must have to check the input from user and then process it.
Asp.Net provides very powerful controls for validation. In Asp.Net we have basically six validation control which can be used as per the requirements. we can validate the useless or contradictory data input from user. if something is going to harm the system we can validate and stop the user request with proper message or user friendly alert.
Asp.Net gives following validation controls:
  • RequiredFieldValidator
  • RangeValidator
  • CompareValidator
  • RegularExpressionValidator
  • CustomValidator
  • ValidationSummary

What is BaseValidator Class: All validation control performs some operation according to their nature and these control have their built-in classes, these control classes are inherited from a base class that's called BaseValidator class, which give some common properties and method to all validation controls. you can check in below image:


Now we will check validation control in details.

1. RequiredFieldValidator Control:
RequiredFieldValidator is generally used with input text field to check whether the input field is empty or have some text and it will validate that input text field should must have some text not empty.
Example/use of RequiredFieldValidator:

<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvcUserName" runat="server" ControlToValidate ="txtUserName" ErrorMessage="Please enter Username"></asp:RequiredFieldValidator>


In above example, we can check how we will use RequiredFieldValidator with a Asp.Net TextBox.
2. RangeValidator Control:
On mostly website we have seen it asks for password should be minimum 6 and maximum 20 in length. if we enter less than 6 or greater than 20 it will never let us proceed further. RangeValidator control is used to check whether input from the user falls between this range or no.
RangeValidator has three specific properties:
Type: It check the type of input from the user. The available values are: Date, Double, Currency, Integer, and String.
MinimumValue specifies minimum value of the range.
MaximumValue specifies maximum value of the range.
Syntax/Example of RangeValidator control is as:

<asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox>
        <asp:RangeValidator ID="rvPasswords" runat="server" ControlToValidate="txtPassword" ErrorMessage="Enter your password between 6 - 12 (only number)" MaximumValue="12" MinimumValue="6" Type="Integer"></asp:RangeValidator>


In above example, we create a password textbox field and in RangeValidator we have set the properties like user can only enter length between 6 to 12 because MinimumValue will check that input should not be less than 6 and MaximumValue will verify input should not b greater than 12, we have set Type is Integer so user can only enter Integers otherwise RangeValidtor will not pass the validation.

3. CompareValidator Control:
When we are submitting a registration form to create a user profile then mostly it requires to set Password for your account, always there are two password fields with label Password and Confirm Password. whatever password we set for the account we have to match the Password and Confirm Password fields. how this confirm password verifies the user input is same that could be done in Asp.Net by using CompareValidator control. we can perform this operation by many other ways and also, we can use CompareValidator control for many other purposes but it was most general and used example for this control.
however, let's have a look on some specific properties of this control:
Type: specifies the data type.
ControlToCompare check the value of the input control to compare with.
ValueToCompare verifies constant value to compare with.
Operator it provides comparison operator, the available values are: GreaterThan, GreaterThanEqual, LessThan, Equal, NotEqual, LessThanEqual, and DataTypeCheck.
Syntax/Example of this control is:

<asp:TextBox id="TextBox1" runat="server"/><br />
        <asp:TextBox id="TextBox2" runat="server"/>
        <asp:CompareValidator id="Compare1" ControlToValidate="TextBox1" ControlToCompare="TextBox2" Type="String" Text="Value does not match" runat="server"/>


In above example, we have two Textboxes and we are comparing their values with each other. if user input is different in both textboxes then it will not pass validation and show the error message "Value does not match".

4. RegularExpressionValidator Control:
RegularExpressionValidator is used when we want our input to be verified with a specific pattern,it may be some phone number format,currency.etc,or we want our input should only contain alphabets not numbers.basically we use a regular expression to match the input which can be done by using RegularExpressionValidator control. we set a regular expression in this control and tell the validator control name of input field then it verifies that input field should validate the expression.
Let's have a look on simple example:

<asp:TextBox ID="txtName" runat="server"/>
    <asp:RegularExpressionValidator ID="regexpName" runat="server" ErrorMessage="This expression does not validate." ControlToValidate="txtName" ValidationExpression="^[a-zA-Z']$" />


5. CustomValidator Control:
Sometime we do not require above all control to check our input and we want to perform some other operation on the user input. so, for such purpose we can use customValidator control. we can perform client side operation and server side also by using this customer control validator. for check input at client side we can use ClientValidationFunction property and for server we can use ServerValidate event handler. client routine can be built by using any script language like Javascript or VBScript or any other understand by browser and in server side we can use any Asp.Net language like C# or VB.Net.
Syntax/Example for the control is:

<asp:TextBox id="Text1" runat="server" />
    <asp:CustomValidator id="CustomValidator1" ControlToValidate="Text1" ClientValidationFunction="ClientValidateFunction" OnServerValidate="ServerValidationFunc" ErrorMessage="Not an even number!" runat="server"/>


In above example, we can see we set the client side and server side routines to perform the operation.
6. ValidationSummary Control:
ValidationSummary is basically used to summarize the error messages from all validation control on the page. it shows the error messages per the DisplayMode property. it collects all validation control error message from a common property "ErrorMessage" of each control. if we skip the ErrorMessage property of any validation control then it will not show error message for that control. there are different properties to present error messages nicely on page by using ValidationSummary Control.
Syntax/Example Lets understand with an example:

<asp:TextBox ID="TextBox2" runat="server" />
            <asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="TextBox2" ErrorMessage="Enter Card Type" Text="*" runat="server"/>
            <asp:TextBox ID="TextBox1" runat="server" />
            <asp:RequiredFieldValidator ID="RequiredFieldValidator2" ControlToValidate="TextBox1" ErrorMessage="Enter Card Number" Text="*" runat=server/>
            <asp:ValidationSummary id="valSum" DisplayMode="BulletList" EnableClientScript="true" HeaderText="You must enter a value in the following fields:" runat="server"/>


In above example validationsummary will show error message, in bullets, from the both required field validator control with a header text.
Comments and Suggestions are always welcome

Difference between document.ready and window onload

Today we are going to look at the difference between document.ready and window.onload events.
In JavaScript we use window.onload() whereas in JQuery we are using $(document).ready() and normally developer think these both are same but it’s not true because there are many differences between them. Basically JQuery wrap up the window.onload() from JavaScript and give the world $(document).ready() but still both are different.
Let's have a look with example:

Window.onload():
This method called up when all data/content on a page is fully loaded here all content means DOM, images, async scripts etc all we have on a page. onload method must wait for images and other frames to load and after that this event is fired.

$(window).load(function () {
             alert("all content is loaded");
    });


By adding these lines to any page, it will show alert when page is fully loaded.There is another way to call this event is:

<body onload="func_call();" >


we can add this to the body tag of page and it will work same like $(window).onload.

$(document).ready():
JQuery execute earlier than window.onload. document.ready event is fired when all DOM is loaded on the page. DOM (document Object Model) include all html tag and scripts it does not include the images and other frames. DOM load is early stage of a page to load that’s why this event starts earlier than window.onload(). so when DOM is ready document.ready() event will start execution.

$(document).ready(function () {
              alert("DOM is ready.");
    });


Another way which work same like document.ready or we can say shorthand for document.ready is:

$(function() {
        alert("DOM is ready." );
    });


Notes:
  1. Window.onload is a JavaScript event and document.ready is specific event from JQuery.
  2. If you have to perform some manipulation on images then its preferred to use window.load but if your operations are only related to DOM then use document.ready.
  3. On a single page we cannot use window.load event more than one whereas document.ready is allowed to use more than once. we can have multiple document.ready but window.load will be single on a page.

Comment and suggestion are always welcome.

Thursday, March 16, 2017

Order by clause with Union in Sql Server

Problem: use of order by with union statement.
We will discuss different ways of using order by statement with union statement.
mostly developer tries to add order by in both union statements like below:

SELECT COLUMN1,COLUMN2 FROM TABLE1
    ORDER BY COLUMN1
    UNION ALL
    SELECT COLUMN1,COLUMN2 FROM TABLE2
    ORDER BY COLUMN1

This query is not correct and it will throw an error:
incorrect syntax near order by.
SQL union does not support two order by statement as union is used to combine two queries result as one so how is it possible to have two order by statements for one result set.
However, there are other ways to order both query as per requirements but not with two order by statements. Let's have a look first on simple syntax that how we can use order by with union statement.

SELECT COLUMN1,COLUMN2 FROM TABLE1 
    UNION ALL
    SELECT COLUMN1,COLUMN2 FROM TABLE2 
    ORDER BY COLUMN1




This query will order the union result per Column1 value. this is the most simple and cleaner statement we can use for order by with union statement.
Now another scenario if you have some complex query and it’s difficult for you to handle the order by as mentioned in above query then you can use below query it will give you little more control over the query and you can perform some more operation.

SELECT * FROM (
    SELECT COLUMN1,COLUMN2 FROM TABLE1 
    UNION ALL
    SELECT COLUMN1,COLUMN2 FROM TABLE2 
    )R
    ORDER BY R.COLUMN1,R.COLUMN2,GETDATE()




I share above query because I face on issue on simple order by with union statement so I resolved with this query. maybe you require the union result be joined with some other table so it will be easy for you to handle with this query. however, it is not as much cleaner and its using also subquery so may be some performance factor affect your results.
Now if you want to order you both queries should be order by independently from each other than you can use below query. for example, you want first and second query of union should be sorted differently/independently then have a look:

SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' as orderbyKey FROM TABLE1
    UNION ALL
    SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' as orderbyKey FROM TABLE2 
    ORDER BY orderbyKey




This query will show first record of top query and then bottom query records accordingly.
If we want to sort the result set that it first shows the top query result and then bottom query result and these should be order by Created Date descending then we can do this below:

SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' AS ORDERBYKEY FROM TABLE1 
    UNION ALL
    SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' AS ORDERBYKEY FROM TABLE2 
    ORDER BY ORDERBYKEY ASC,CREATED_DATE DESC 




Complete Script:

USE tempdb
GO
-- Create table
CREATE TABLE TABLE1 (COLUMN1 INT, COLUMN2 VARCHAR(50),CREATED_DATE DATETIME);
CREATE TABLE TABLE2 (COLUMN1 INT, COLUMN2 VARCHAR(50),CREATED_DATE DATETIME);
GO
--INSERT Sample Data 
INSERT INTO TABLE1 (COLUMN1, COLUMN2,CREATED_DATE)
SELECT 1, 'VAL1_TABLE1',GETDATE()
UNION ALL
SELECT 2, 'VAL2_TABLE1',GETDATE()+1
UNION ALL
SELECT 3, 'VAL3_TABLE1',GETDATE()+2;
INSERT INTO TABLE2 (COLUMN1, COLUMN2,CREATED_DATE)
SELECT 3, 'VAL1_TABLE2',GETDATE()
UNION ALL
SELECT 2, 'VAL2_TABLE2',GETDATE()+3
UNION ALL
SELECT 1, 'VAL3_TABLE2',GETDATE()+5;
GO

 --1ST
 SELECT COLUMN1,COLUMN2 FROM TABLE1
 ORDER BY COLUMN1
 UNION ALL
 SELECT COLUMN1,COLUMN2 FROM TABLE2
 ORDER BY COLUMN1
 go
 --2ND
 SELECT COLUMN1,COLUMN2 FROM TABLE1 
 UNION ALL
 SELECT COLUMN1,COLUMN2 FROM TABLE2
 ORDER BY COLUMN1
 go
 --3RD 
 SELECT * FROM
 SELECT COLUMN1,COLUMN2 FROM TABLE1
 UNION ALL
 SELECT COLUMN1,COLUMN2 FROM TABLE2
 )R
 ORDER BY R.COLUMN1,R.COLUMN2,GETDATE()
 go
 --4TH
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' as orderbyKey FROM TABLE1
 UNION ALL
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' as orderbyKey FROM TABLE2 
 ORDER BY orderbyKey
 go
 --5TH<
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' AS ORDERBYKEY FROM TABLE1 
 UNION ALL
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' AS ORDERBYKEY FROM TABLE2 
 ORDER BY ORDERBYKEY ASC,CREATED_DATE DESC 
go
-- Clean up
DROP TABLE TABLE1;
DROP TABLE TABLE2;
GO

Comments and suggestions are always welcome!

Wednesday, March 15, 2017

What is HTTP Protocol

HTTP: HTTP stands for Hypertext Transfer Protocol. HTTP is base for WWW (World Wide Web => internet) to communicate data between client and server. HTTP is stateless protocol that is used by its request methods, status like error code and headers of request or response. In this post, we will discuss HTTP for computer students and for developers. Let’s have a look on few parts of HTTP. There are mainly three features of HTTP.

First: Connection-less, HTTP is a connectionless. what happens when we make request to server for some data or images. so, it makes connection from client to server and get response also that is also a connection from server to client. In fact, the scenario is that when a client browser send request to server after sending request it discontinue the connection and wait for server, at the other hand server receive request and complete process and then make again connection to client and send back the response to client.

Second: Media independent, by using HTTP we can send any type of data but our client and server, both should know the content type and understand that how they will handle the data. there is no restriction just we must pass content type by using correct MIME-TYPE so it become easy to handle for the server.

Third: Stateless, HTTP is connectionless and stateless because client and server don't know each other after the request is completed. server and client know each other only when they are communicating with each other after completion connection is finished and client and server become unknown to each other. 

In HTTP/1.0 for each new request/response between server and client they create a new connection, but in HTTP/1.1 have the capability to make one or more request/response.
When we use term HTTP protocol then it means request/response based to the client/server architecture. as shown in below picture that web browser or search engines behave like a HTTP client and Web Server becomes servers.


What is Client: An HTTP client may be a browser or search engine or robot, is used to send request to server in the form of method, any protocol version or using URI which is attached with a specific MIME-TYPE which tell the server it contains a client information, a modifier and may a having content in the body of request.

What is Server: HTTP server who process the client request and send response to client. This response may have any protocol version or maybe it gives response in error code like status code 404 Not found, 401 Unauthorized, a response has some MIME-TYPE and may be the response data in body content. 

HTTP Versions: HTTP basically used HTTP/. version scheme. now updated is HTTP/2 actually HTTP/2.0. Example:

HTTP/1.0 or HTTP/1.1 or HTTP/2.0

What is URI: URI stands for Uniform Resource Identifier. it is used to represent any website or WebApi and it have all basic info of the resource like location, name etc.
Syntax for URI:
URI = "http:" "//" HTTP_HOST/HTTPS [ ":" port ] [ abs_path [ "?" query string ]]

Let have a look on below example for understanding:
1. http://abc.com:80/~jdoe/finaltext.html
2. http://ABC.com/%7Ejdoe/finaltext.html
3. http://ABC.com:/%7ejdoe/finaltext.html

These all above mentioned URI are same/equivalent. why? because as we know 80 is default port so it does not matter either its written or not, ~ notation HEX is %7E so either we enter the ~ or %7E it will be same and browser will convert this to HEX and understand automatically and also there is no restriction for case sensitive.

HTTP Request Methods:
1. GET: it is used to get some data or retrieve any information from the server. The basic purpose for what it is used for getting something from the server only.
2. HEAD: it’s used is same like GET but it is basically for header section information and give the status of request.
3. POST: mostly POST is used to send data or record to server for processing either insert into data or some other operation like file upload etc.
4. PUT: used to update the data or file or records, its function is to replace the current data with newly uploaded.
5. DELETE: as the name show it will remove all record on target server by the provided URI.
6. CONNECT: is used for tunneling between client and server. it will create a tunnel to server indicated from the URI.
7. OPTIONS: it is used to check the communication is allowed between the client and server.
8. TRACE: is used to send a response back to test with the path to the target resource.
In this post, we try to just a have look on mandatory part required for a developer to understand exactly what is HTTP this is not full description but it will give you good understanding as web developer.