20 main differences between Stored procedures and Functions in Sql Server

Stored Procedure:

A stored procedure is a pre-compiled group of Transact-SQL statements .We can say a stored procedure is a prepared SQL code that we save so that we can reuse the code over and over again.  If a repetitive T-SQL task has to be executed within an application, then the best way for it is to create stored procedure.

It is always recommended to create Stored Procedure instead of writing Inline queries so that we can just call the Stored Procedures whenever required instead of writing Inline queries again and again each time.

You can also pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed to it.

Function:

 Function in Sql Server is a Transact-SQL or common language runtime (CLR) routine that takes 
parameters, performs an action, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table.

Difference between Stored procedure and Function

1.       Function can return only 1 value whereas Stored Procedure can return many values(maximum 1024)

2.       Functions can have only input parameters for it whereas Stored Procedures can have input/output parameters.

3.       Function takes one input parameter which is mandatory but Stored Procedure may take Zero to n input parameters.

4.       Functions can be used in a select statement where as Stored Procedures cannot.

5.       Functions can be called from Stored Procedure whereas Stored Procedures cannot be called from Function.

6.       Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.

7.       Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.

8.       Stored Procedure can be used to read and modify data but function can only read data.

9.       Stored Procedure allows SELECT as well as DML (Data Manipulation Language) statements like INSERT/UPDATE/DELETE in it whereas Function allows only SELECT statement in it.

10.   Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

11.   Stored Procedures cannot be used as an inline with a select statement while Functions can. 

12.   Stored procedures are compiled for first time and compiled format is saved and executes compiled code whenever it is called. But Function is compiled and executed every time it is called. 

13.   Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

14.   Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

15.   Stored Procedure allows Transaction Management whereas Function doesn’t.

16.   Stored procedures can be used to change server configuration settings (in terms of security-e.g. setting granular permissions of user rights) whereas function can't be used for this

17.   The Stored Procedures can perform certain tasks in the database by using insert, delete, update and create commands but in Function you can’t perform use these commands. 

18.   Normally the Stored procedures are used to process certain task but the Functions are used to compute the values i.e. we can pass some value as input and then it perform some task on the passed value and return output. 

19.   Stored Procedures can be executed using Execute or Exec command where as Functions can run as an executable file. 

20.   Functions can be used as user defined data types in create table but procedures cannot.

15 main Difference between DataSet and DataReader in asp.net

Asp.net developer uses DataSet and DataReader to fetch data from the data source while developing asp.net application. But most of them don’t know exactly what are the main difference between DataSet and DataReader and what to use and when to use out of these two.

Both DataSet and DataReader are widely used in asp.net applications for the same purpose i.e. to get/fetch the data from the database. But one has to know the best practices in developing fast, reliable and scalable application. So I have tried to list some main differences between the DataSet and DataReader which are as follows:

DataSet Vs DataReader

1.       DataReader is used to retrieve read-only (cannot update/manipulate data back to datasource) and forward-only (cannot read backward/random) data from a database. It provides the ability to expose the data from database while DataSet is a collection of in-memory tables.

 

2.       DataReader fetches the records from database and stores in the network buffer and gives whenever requests. It releases the records as query executes and do not

      wait for the entire query to execute. Hence very fast as compare to the DataSetwhich releases the data after loading all the data in memory.

3.       DataReader is like a forward only recordset. It fetches one row at a time so very less network cost compare to DataSet which fetches all the rows at a time i.e. it fetches all data from the datasource at a time to its memory area.

4.       As one row at a time is stored in memory in DataReader it increases application performance and reduces system overheads while there is more system overheads in DataSet as it fetches all the data from the datasource at a time in memory.

5.       As DataReader is forward only, we can’t fetch random records as we can’t move back and forth .While in DataSet we can move back and forth and fetch records randomly as per requirement.

6.       DataReader fetches data from a single table while DataSet can fetch data from multiple tables.

7.       As DataReader can have data from a single table so no relationship can be maintained while relationship between multiple tables can be maintained inDataSet.

8.       DataReader is read only so no transaction like insert, update and delete is possible while these transactions are possible in DataSet.

9.       DataSet is a bulky object that requires lot of memory space as compared toDataReader .

10.   DataReader is a connected architecture: The data is available as long as the connection with database exists while DataSet is a disconnected architecture that automatically opens the connection, fetches the data into memory and closes the connection when done.

11.   DataReader requires connection to be open and close manually in code while DataSet automatically handles it.

12.   DataSet can be serialized and represented in XML so easily passed around to other tiers but DataReader can't be serialized.

13.   DataReader will be the best choice where we need to show the data to the user which requires no manipulation while DataSet is best suited where there is possibility of manipulation on the data.

14. Since DataSet can be serialized it, can be used in wcf services  and web service that will return retrieved data. But DataReader can’t be serialized so can’t be used in wcf services and web services.

15. When you need to navigate through the data multiple times then DataSet is better choice e.g.  we can fill data in multiple controls But DataReader can only be read once so it can be bound to a single control and requires data to be retrieved for each control.

Difference between Response.Redirect and Server.Transfer in asp.net

What is the difference between ‘Response.Redirect’ and ‘Server.Transfer’  is one of the most important interview questions asked to freshers.
 I have tried to explain some of the main differences between the two so that freshers can better understand the difference between them. Both the “Server” and “Response” are objects of asp.net. ‘Server. Transfer’ and ‘Response. Redirect’ both are used for the same purpose i.e. to transfer from one page to another page but still there are some differences that are as follows:

Response.Redirect vs Server.Transfer 

1.     ‘Response. Redirect’ sends message to the browser saying it to move to some different page, while ‘Server. Transfer’ does not send any message to the browser but rather it redirects the user directly from the server itself. So in case of ‘Server. Transfer’ there is no round trip while Response. Redirect has a round trip and hence puts extra load on server.

2.     Using ‘Server. Transfer’ we cannot redirect to external websites or website pages. E.g. if your website is www.webcodeexpert.com then you cannot use ‘Server. Transfer’ to move to www.google.com but yes, you can move to internal pages  www.webcodeexpert.com/asp.net, i.e. within the websites. Cross server redirection is possible only by using ‘Response.Redirect’ i.e. it allows redirection to internal as well as external websites and website pages.

3.     With ‘Response. Redirect’ we can redirect the user to the both type of pages .html or .aspx e.g. Response. Redirect (“OtherPage.html”) OR Response. Redirect (“OtherPage.aspx”) But in case of ‘Server. Transfer’ we can redirect user to .asp or .aspx pages only e.g. Server. Transfer (“OtherPage.asp”) OR Server. Transfer (“OtherPage.aspx”) not to Server. Transfer (“OtherPage.html”).

4.     In ‘Server. Transfer’ URL doesn’t change but in case of ‘Response. Redirect’ URL changes.

5.     When we want to allow our website’s URL can be copied then ‘Response. Redirect’ is better but for security reasons ‘Server. Transfer’ is better because URL cannot be copied.

6.     ‘Response. Redirect’ has a round trip but ‘Server.Transfer’ has no round trip. (Roundtrip is the combination of a request being sent to the server and response being sent back to browser.)

7.     ‘Server. Transfer’ is a server process whereas ‘Response. Redirect’ is a client process.

8.     ‘Server.Transfer’ preserves Query String and Form Variables (optionally). ‘Response. Redirect’ doesn’t preserve Query String and Form Variables from the original request.

9.     ‘Server. Transfer’ is faster since there is one less round trip as compared to ‘Response. Redirect’. Transferring to another page using ’Server. Transfer’ conserves server resources. Instead of telling the browser to redirect, it simply changes the focus on the Web server and transfers the request. This means you don't get quite as many HTTP requests coming through, which therefore eases the pressure on your Web server and makes your applications run faster.

10. ‘Server. Transfer’ allow us to directly access the values, controls and properties of the previous page which we can’t do with ‘Response. Redirect’. The ’Server. Transfer’ method also has a second parameter—"preserveForm". If you set this to True, using a statement such as Server. Transfer ("OtherPage.aspx", True), the existing query string and any form variables will still be available to the page you are transferring to. For example, if your CurrentPage.aspx has a TextBox control called TextBox1 and you transferred to OtherPage.aspx with the preserveForm parameter set to True, you'd be able to retrieve the value of the original page’s TextBox control by referencing Request. Form ("TextBox1").

11. ‘Response. Redirect’ involves a roundtrip to the server whereas ‘Server. Transfer’ conserves server resources by avoiding the roundtrip. It just changes the focus of the web server to a different page and transfers the page processing to a different page. Roundtrip means in case of ‘Response. Redirect’ it first sends the request for the new page to the browser then browser sends the request for the new page to the web server only then a your page changes But in case of ‘Server. Transfer’ it directly communicate with the server to change the page hence it saves a roundtrip in the whole process.

How to send emails in asp.net using Gmail

Introduction: Sometimes it is required to send emails from our website to other person.It’s very easy.In my previous article i explained How to send mail with multiple attachments in asp.net with C#,Vb.Net and Send email to multiple users based on CheckBox selection inside GridView and How to send emails in asp.net | How to set Smtp setting in web.config file to send email in asp.net and Send emails in asp.net using Gmail | How to set Smtp setting in web.config file to send emails in asp.net using Gmail in asp.net.
Now in this article i am going to explain how you can send email using your GMAIL account credentials i.e. gmail email id and password in asp.net.Just follow the article.

Implementation: Let's create an application to understand:

In the design page (.aspx) design the page as:

<table style="width:100%;">

    <tr>

        <td>

            Send To(Email Address)</td>

        <td>

            <asp:TextBox ID="txtEmailId" runat="server" Columns="60"></asp:TextBox>

            </td>

    </tr>

    <tr>

        <td>

            Subject</td>

        <td>

            <asp:TextBox ID="txtSubject" runat="server" Columns="60"></asp:TextBox>

            </td>

    </tr>

    <tr>

        <td>

            Body</td>

        <td>

            <asp:TextBox ID="txtBody" runat="server" Columns="60"Rows="5"

                TextMode="MultiLine"></asp:TextBox>

            </td>

    </tr>

    <tr>

        <td>

             </td>

        <td>

            <asp:Button ID="btnSendEmail" runat="server"onclick="btnSendEmail_Click"

                Text="Send Email" />

            </td>

    </tr>  

</table>

C#.NET Code to send emails in asp.net using Gmail 
 

Include following namespaces:

using System.Configuration;

using System.Net;

using System.Net.Mail;

• Now in the code behind file(.aspx.cs) write the code to send Email on send mail button as:

protected void btnSendEmail_Click(object sender, EventArgs e)

    {

        if (SendEmailUsingGmail("YourGmailId@gmail.com", txtEmailId.Text.Trim(), txtSubject.Text.Trim(), txtBody.Text.Trim(),"YourGmailPassword"))

        {

            Response.Write("Mail send");

        }

        else

        {

            Response.Write("Error in sending mail");

        }

    }

    private static Boolean SendEmailUsingGmail(string fromEmailAddress,string toEmailAddress, string subject, string messageBody, stringgmailPassword)

    {

        try

        {

            SmtpClient smtp = new SmtpClient();

            smtp.Credentials = new NetworkCredential(fromEmailAddress, gmailPassword);

            smtp.Port = 587;

            smtp.Host = "smtp.gmail.com";

            smtp.EnableSsl = true;

            MailMessage message = new MailMessage();

            message.From = new MailAddress(fromEmailAddress);

            message.To.Add(toEmailAddress);

            message.Subject = subject;

            message.Body = messageBody;

            smtp.Send(message);

            return true;

        }

        catch

        {

            return false;

        }

    }

How to create thumbnail, small and large version of the uploaded image in Asp.net?

Description: While working on asp.net application it is sometimes required to createthumbnail, small and large version of the image as per application requirement. Suppose there is image upload functionality in your website where user can upload their pictures and you want to store and display thumbnail, small and large version of the uploaded image. Here is the solution.

Implementation: Let's create an asp.net website to see the example in action.

Source Code:

• In the design page (.aspx) place a FileUpload Control and a Button control as:

<table>

        <tr>

                <td>

                    <asp:FileUpload ID="FileUpload1" runat="server" />

                </td>

            </tr>

            <tr>

                <td>

                      <asp:Button ID="btnUpload" runat="server" Text="Submit"

            onclick="btnUpload_Click" /></td>

          </tr>          

  </table>

• Create a folder “images” and three sub folder “thumbnail”, “small” and “large” inside the folder “images”.

C#.NET Code to create thumbnail, small and large version of the uploaded image

• In the code behind file (.aspx.cs) write the code as:

First Include following namespaces:

using System.Drawing;

using System.Drawing.Drawing2D;

using System.Drawing.Imaging;

using System.Drawing.Design;

using System.IO;

• Then write the code as:

protected void btnUpload_Click(object sender, EventArgs e)

    {

        if (FileUpload1.HasFile)

        {

            string imgThumb = string.Empty;

            string imgSmall = string.Empty;

            string imgLarge = string.Empty;

            Bitmap bmpImgThumb = null;

            Bitmap bmpImgSmall = null;

            Bitmap bmptImgLarge = null;

            try

            {

                bmpImgThumb = Resize_Image(FileUpload1.PostedFile.InputStream, 100, 67);

                bmpImgSmall = Resize_Image(FileUpload1.PostedFile.InputStream, 411, 274);

                bmptImgLarge = Resize_Image(FileUpload1.PostedFile.InputStream, 1280, 854);

                imgThumb = Server.MapPath("images/thumbnail/") + Guid.NewGuid().ToString() + ".png";

                imgSmall = Server.MapPath("images/small/") + Guid.NewGuid().ToString() + ".png";

                imgLarge = Server.MapPath("images/large/") + Guid.NewGuid().ToString() + ".png";

                bmpImgThumb.Save(imgThumb, ImageFormat.Jpeg);

                bmpImgSmall.Save(imgSmall, ImageFormat.Jpeg);

                bmptImgLarge.Save(imgLarge, ImageFormat.Jpeg);

            }

            catch (Exception ex)

            {

                Response.Write("Error occured: " + ex.Message.ToString());              

            }

            finally

            {

                imgThumb = string.Empty;

                imgSmall = string.Empty;

                imgLarge = string.Empty;

                bmpImgThumb.Dispose();

                bmpImgSmall.Dispose();

                bmptImgLarge.Dispose();

            }

        }

    }

    private Bitmap Resize_Image(Stream streamImage, int maxWidth, int maxHeight)

    {

        Bitmap originalImage = new Bitmap(streamImage);

        int newWidth = originalImage.Width;

        int newHeight = originalImage.Height;

        double aspectRatio = Convert.ToDouble(originalImage.Width) / Convert.ToDouble(originalImage.Height);

        if (aspectRatio <= 1 && originalImage.Width > maxWidth)

        {

            newWidth = maxWidth;

            newHeight = Convert.ToInt32(Math.Round(newWidth / aspectRatio));

        }

        else if (aspectRatio > 1 && originalImage.Height > maxHeight)

        {

            newHeight = maxHeight;

            newWidth = Convert.ToInt32(Math.Round(newHeight * aspectRatio));

        }

        return new Bitmap(originalImage, newWidth, newHeight);

    }