Difference between stored procedure and user defined functions

Stored Procedure

In our projects we are frequently using the stored procedures and functions.Here I will explain the difference and example.Stored Procedures are set of executable statement which are already pre-compiled and stored in SQL server.Stored procedures no need to return output.We cannot use Stored Procedures in DML statements like (insert/update/delete.In Stored Procedures we can call functions and Stored Procedures inline.Stored Procedures cannot be used in SQL statements like where/having/select section.We can use try/catch block for error handling in stored procedures.

CREATE TABLE  Employees
(
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](200) NOT  NULL
)

Create Procedure Procedure_name 
(
Input parameters ,
Output Parameters (Optional)
)
As
Begin
     Set of Sql statement 
End

//select empname based on empid
Create  PROCEDURE GetEmpName(

@empID INT                    

)
AS
BEGIN
SELECT EmpName FROM Employees WHERE EmpId=@empID 
END

//select all empname names
Create  PROCEDURE GetEmpName()
AS
BEGIN
SELECT EmpName FROM Employees
END

Functions

Functions can be divided into aggregate functions and user defined functions. User Defined functions are stored in SQL server which are compiled and executed by everytime.User Defined functions must return output.We can use User Defined functions in DML statements like (insert/update/delete).In User Defined functions we cannot call Stored Procedures inline.User Defined functions can be used in SQL statements like where/having/select section.We cannot use try/catch block for error handling in User Defined functions.

CREATE FUNCTION udfEmpCount()
RETURNS int 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @EmpCount int;
    SELECT @EmpCount = count(EmpName) 
    FROM Employees
     IF (@EmpCount IS NULL) 
        SET @EmpCount = 0;
    RETURN @EmpCountt;
END;

Output
In this post i tried to explain the difference between SQL Stored procedure and functions. 

Difference between DataSet and DataReader

DataSet

DataSet is a type of disconnected architecture which means sql live database connections is not required while fetch the data from database. DataSet contains the collection of multiple DataTables. We are able to keep the tables, views values from database in DataSet. Also we can fetch the XML values and store in DataSet. We can use DataAdapter as a mediator between DataSet and SQL DataBase tables.

public DataSet GetData()
{
    SqlConnection conString = new SqlConnection("DataBase Connection");
    conString.Open();
    SqlCommand cmdQuery = new SqlCommand("SQL Statement", conString);
    SqlDataAdapter sda = new SqlDataAdapter(cmdQuery);
    DataSet dsData = new DataSet();
    sda.Fill(dsData);
    return dsData;
}

DataReader

DataReader is also used to fetch the data from SQL database. When compared to DataSet, DataReader is a connection oriented architecture. Also it is a read/forward only approach while fetching the data from SQL database. We can use DataReader for fast fetching the data from SQL database. DataReader will loop one by one record and return the data. Here we will see a sample for binding the GridView using DataReader.

Protected void BindDataGrid()
{
    SqlConnection conString = new SqlConnection("DataBase Connection");
    conString.Open();
    SqlCommand cmdQuery = new SqlCommand("SQL Statement", conString);
    SqlDataReader sdrData = cmdQuery.ExecuteReader();
    gvEmployees.DataSource = sdrData;
    gvEmployees.DataBind();
    conString.Close();
}

Output
In this post i tried to explain the difference between SQL Stored procedure and functions. 

Effective ways to Improve IIS Performance

IIS

Internet Information System simply called as IIS which is used host our application. While hosting an Application we need to concentrate on performance and concurrent users hits. Basic things we need to see, While user enter some invalid page IIS will automatically show 404 error page instead of we should provide some meaningful message with custom errors. We have developed our project in 32 bit and server may have 64 bit by default, so we have an option "Enable 32 bit Application" in Advanced settings kindly mark as true. 



Shortcut to open IIS using run command just type inetmgr

While hosting the Application avoid .pdb files in published folder.While publish the application use release mode instead of giving debug mode.Use encrypted password in web.config connection string settings. Create individual Application Pool for every websites, because we can avoid Out of memory exception due to wpw3.exe cache size increases.This will improve the performance of the site.

Large SQL query execution avoid connection timeout by increasing the execution time out in web.config file

<httpRuntime executionTimeout="3600" />

We are able to show temporary shutdown message by simply adding app_offline.html(we can use any image or text in this file) in published folder. Note this will work only on .aspx pages if we may have .html page it will show the orginal page. Best way to restart the IIS, go to run command and type IISRESET this will immediately close and restart the application and release the temporary memory. But carefully do this because if you are having multiple application in IIS means it will stop/start all the Application pool including default Application Pool.

Configure HTTP Response Header expiration. This option will helps us to minimize the number of hits to IIS by viewers. HTTP Response header expires the client browser page cache and its contents such as images,JavaScript, CSS etc. In IIS double click HTTP response headers, and then select "set common HTTP Response headers". Then click "Expire web content" and select immediately option.

We can disable the ASP debugging for server and client in IIS. While running Application in Live server, we do not need to use the ASP debugging mode in IIS. Application will be very fast by disabling this mode in IIS. To disable debugging, Go to Advanced settings select the "compilation" and then click on "debugging properties" and set false for Server side debugging and Client side debugging

Enabling the output caching in IIS will keep a copies of the user requested pages. If the same page has been requested by a different user, IIS will send the copies from cache by without reproducing the same page. 

Conclusion
In this post i tried to explain the IIS configuration performance tuning and settings.