UTF8 Encrypt Decrypt in asp.Net

No comments
Imports System.Configuration
Imports System.Security.Cryptography
Imports System.IO

Public Class Encrypt

#Region "Variables"
    Private Shared key As Byte() = {}
    Private Shared IV As Byte() = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
    Private Shared EncryptionKey As String = App.Value("EncryKey")
#End Region

#Region "Functions"
    Public Shared Function UTF8Decrypt(ByVal Input As String) As String
        Dim inputByteArray As [Byte]() = New [Byte](Input.Length - 1) {}
        Try
            key = System.Text.Encoding.UTF8.GetBytes(EncryptionKey.Substring(0, 8))
            Dim des As New DESCryptoServiceProvider()
            inputByteArray = Convert.FromBase64String(Input)
            Dim ms As New MemoryStream()
            Dim cs As New CryptoStream(ms, des.CreateDecryptor(key, IV), CryptoStreamMode.Write)
            cs.Write(inputByteArray, 0, inputByteArray.Length)
            cs.FlushFinalBlock()
            Dim encoding__1 As Encoding = Encoding.UTF8
            Return encoding__1.GetString(ms.ToArray())
        Catch ex As Exception
            Return ""
        End Try
    End Function

    Public Shared Function UTF8Encrypt(ByVal Input As String) As String
        Try
            key = System.Text.Encoding.UTF8.GetBytes(EncryptionKey.Substring(0, 8))
            Dim des As New DESCryptoServiceProvider()
            Dim inputByteArray As [Byte]() = Encoding.UTF8.GetBytes(Input)
            Dim ms As New MemoryStream()
            Dim cs As New CryptoStream(ms, des.CreateEncryptor(key, IV), CryptoStreamMode.Write)
            cs.Write(inputByteArray, 0, inputByteArray.Length)
            cs.FlushFinalBlock()
            Return Convert.ToBase64String(ms.ToArray())
        Catch ex As Exception
            Return ""
        End Try
    End Function
#End Region

End Class

No comments :

Post a Comment

GetIPAddress in Asp.net

No comments
Public Shared Function GetIPAddress() As String
        Try
            Return HttpContext.Current.Request.ServerVariables("REMOTE_ADDR")
        Catch generatedExceptionName As Exception

            Return ""
        End Try


    End Function

No comments :

Post a Comment

GetRandomNumber in asp.net

No comments
Public Shared Function GetRandomNumber() As String
        Dim rand As New Random()
        Dim newpassword As New StringBuilder("")

        For index As Integer = 1 To 2
            newpassword.Append(CChar(CChar(CStr(rand.[Next](65, 90)))))
        Next

        For index As Integer = 0 To 2
            newpassword.Insert(rand.[Next](0, 3 + index), Convert.ToString(rand.[Next](0, 9)))
        Next

        Return ("OD" + newpassword.ToString() + DateTime.Now.Day.ToString())
    End Function

No comments :

Post a Comment

Introduction to Asp.net

No comments
According to Microsoft, "ASP.NET is a technology for building powerful, dynamic Web applications and is part of the .NET Framework". Infact Asp.Net is a programming framework used to develop web applications and web services. Basically it is next version of asp. It provides the easy way to build, deploy & run web application on any browser.

Benefits of Asp.Net

  1. Asp.Net makes development simple and easy to maintain with event-driven and server side programming model.
  2. Asp.Net source code is executed on the server. The source code is complied first time the page is requested. The server serves the complied version of the page for use next time the page is requested.
  3. Asp.Net provides validations controls.
  4. The html produced by Asp.Net is sent back to the browser. The application code that we write is not sent back to the browser and is not stolen easily.
  5. In Asp.Net business logic(in .cs class file) and presentation logic(in .aspx file) are in separate files.

Asp .Net Page Life Cycle Events

At each stage of the page life cycle, the page raises some events, which could be coded. An event handler is basically a function or subroutine, bound to the event, using declarative attributes like Onclick or handle. Asp.Net 3.5 & 4.0 page life cycle has following events in sequence :
  1. PreInit

    It is is entry point of page life cycle. It checks IsPostBack property to check or recreate dynamic controls. In this we can set master pages dynamically & set and get profile property values.
  2. Init

    It is is raised after all controls of page are initilised and skin properties are set. It is used to read or initialise control properties.
  3. InitComplete

    This indicates that page is completely initialised.
  4. Preload

    This event is called before loading the page in the RAM(memory). If any processing on a control or on page is required we use it.
  5. Load

    This invokes the onload event of the page. In this we create connection to the database, get/set controls values and get/set view state values.
  6. loadComplete

    This indicates that page is completely loaded into memory.
  7. Prender

    we use this event to make final changes to the controls or page before rendering it to the browser.
  8. SaveStateComplete

    View state of each control is saved before this event occurs. If we want to change the view state of any control then we use this event. This event can not be used to change the other properties of the controls.
  9. Render/PrenderComplete

    This indicates that page is completely rendered to the browser.Before rendering, view state is saved for the page and all controls. During the rendering stage, the page calls the Render method for each control, providing a text writer that writes its output to the OutputStream object of the page's Response property.
  10. Unload

    The Unload event is raised after the page has been fully rendered, sent to the client, and is ready to be discarded. At this point, page properties such as Response and Request are unloaded and cleanup is performed.We can use this event for closing files & database connection. This event occurs for each control.

No comments :

Post a Comment

Disable right click on web page and images

No comments
Sometimes it is required to prevent your web page images from being copied by another one. You can secure your images by disabling right click or by disabling image context menu on images only. This is an effective approach to prevent your images from being copied or stolen. Here I am going to share the tricks how can you achieve this. This trick will disable right click only on images. Add the below event handler to the img or image tag as shown below:

Disable right click on images

  1. <asp:Image ID="img1" runat="server" ImageUrl=""../ImgLoc/1.png"" oncontextmenu="return false;" />
  2. <img alt="MyImage" src="../ImgLoc/2.png" oncontextmenu="return false;"/>

Note

  1. It is impossible to prevent images from being stolen completely since there are so many tools and software through which any one can steal yours images. But something is better than nothing.

Disable right click on web page

Similarly we can disable right click on whole page by adding oncontextmenu handler in body tag of webpage. Now this will disable right click on each and every control of a webpage.
  1. <html>
  2. <head>
  3. ...
  4. </head>
  5. <body oncontextmenu="return false;" >
  6. ...
  7. </body>
  8. </html>

Show alert on right click

  1. <script type="text/javascript">
  2. function disableRightClick()
  3. {
  4. alert("Sorry, right click is not allowed !!");
  5. return false;
  6. }
  7. </script>
  8. <body oncontextmenu=" return disableRightClick();">
  9. ...
  10. </body>
Summary
In this article, I explain how can you disable right click on webpage and images. I hope after reading this article you will be able to do this. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

No comments :

Post a Comment

Turn off Asp.net Custom Errors in Web.config

No comments
Some times after hosting web application on the server, we get unexpected error as shown in the below fig. But we did get the detailed message for the unexpected errror. In this article, I would like to share how can we get detailed message for the unexpected error.
This type of unexpected error may occurs on local or remote server. In asp.net, we can find the exact error message by setting mode="Off" with in customErrors tag in web.config of our application. This is the way by which we can find out the exact error in our web application.
  1. <system.web>
  2. <customErrors mode="Off">
  3. </customErrors>
  4. ...
  5. ...
  6. </system.web>
When we set the customErrors mode="Off" then we can easily track the error in the application as shown in the fig.
In Asp.net, there are three error modes to trace an error. These modes decide whether or not an error message is displayed. RemoteOnly mode is default mode for displaying error messages.
  1. Off Mode

    This mode is responsible for displaying error mesage on local and remote server in case of an error.
  2. On Mode

    This mode is responsible for displaying custom error page with message on local and remote server in case of an error. By using this mode, we can show our own custom error messages page for specific errors on local and remote server.
  3. RemoteOnly

    This mode is responsible for displaying error mesage on remote server only in case of an error. By using this mode, we can show our own custom error messages page for specific errors on remote server only.
What do you think?
I hope you will enjoy these tricks while programming with Asp.Net. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

No comments :

Post a Comment

Difference Between GridView and DataGrid and ListView

No comments

Difference between GridView and DataGrid

GridView
DataGrid
It was introduced with Asp.Net 2.0.
It was introduced with Asp.Net 1.0.
Built-in supports for Paging and Sorting.
For sorting you need to handle SortCommand event and rebind grid required and for paging you need to handle the PageIndexChanged event and rebind grid required.
Built-in supports for Update and Delete operations.
Need to write code for implementing Update and Delete operations.
Supports auto format or style features.
This features is not supported.
Performance is slow as compared to DataGrid
Performance is fast as compared to GridView.

Difference between ListView and GridView

ListView
GridView
It was introduced with Asp.Net 3.5.
It was introduced with Asp.Net 2.0.
Template driven.
Rendered as Table.
Built-in supports for Data grouping.
Need to write custom code.
Built-in supports for Insert operation.
Need to write custom code.
Provides flexible layout to your data.
Need to write custom code.
Performance is fast is compared to GridView.
Performance is slow as compared to ListView.
What do you think?
I hope you will enjoy the tips while programming with Asp.Net. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

No comments :

Post a Comment

Understanding Detailed Architecture of ASP.NET 4.5

No comments

Components of Asp.NET 4.5 Architecture

  1. .NET Framework

    .Net framework is an integrated component of windows operating system that supports development and execution of next generation applications, Windows store apps and services.
  2. ASP.NET Framework

    ASP.Net Framework is used to create dynamic website, web application and web services. It is built on the top of .NET Framework.
    Asp.NET Framework provides you various capabilities like Hosting Model, Site/Service Management, Protocol Abstraction, Security, Caching capability, Routing and Model Binding etc.
    asp.net 4.5 architecture diagram
    Mainly, Asp.Net can be divides into two parts - Asp.Net Sites and Asp.Net Services.
  3. Asp.NET Site

    There are following flavours of Asp.NET Site -
    1. Web Forms

      This is the traditional event driven development model. It has drag and drop server controls, server events and state management techniques. This best for rapid application development (RAD) with powerful data access.
    2. MVC

      This is a lightweight and MVC (Model, View, Controller) pattern based development model. It provides full control over mark-up and support many features that allow fast & agile development. This best for developing lightweight, interactive and device oriented (i.e. compatible to smart phones, iPhone, tablet, laptop etc.) web application with latest web standards.
    3. Web Pages

      This is also a lightweight and Razor syntax based development model. It has built-in template and helpers also provide full control over mark-up. It is best for developing beautiful web application with latest web standards. You can also use WebMatrix which is a free tool and has built-in template; for developing Asp.Net Web Page.
    4. SPA

      SPA stands for Single Page Application which helps you to build web applications that include significant client-side interactions using HTML5, CSS3 and JavaScript. It is best to make highly interactive single page dashboard web applications.
  4. Asp.NET Services

    There are two ways to make Asp.Net Services as given below –
    1. Web API

      Asp.Net Web API is a framework for building HTTP services that can be consume by a broad range of clients including browsers, mobiles, iphone and tablets.
    2. SignalR

      ASP.NET SignalR is a library that simplifies the process of adding real-time web functionality to applications. Real-time web functionality is the ability to have server code push content to connected clients instantly as it becomes available, rather than having the server wait for a client to request new data.
  5. Visual Studio 2012

    The Visual Studio IDE offers a set of tools that help you to write and modify the code for your programs, and also detect and correct errors in your programs. Using Visual Studio 2012 you can build Windows Store apps, desktop apps, mobile apps, ASP.NET web apps, and web services.
What do you think?
I hope you have got ASP.NET 4.5 Architecture and how it works. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

No comments :

Post a Comment

A brief version history of ASP.NET

No comments
ASP.NET Framework is a part of .NET framework. It is used to create dynamic website, web application and web services. It is a server side technology that uses all .NET compatible language such as C#, VB.NET, J# etc. which are compiled to Microsoft Intermediate Language (MSIL). ASP.NET uses server control to develop rapid and interactive application in easy way.
ASP.NET Version History
ASP.NET Version
Introduced with .NET & IDE
Features Detail
4.5.1
4.5.1 and Visual Studio 2013
  1. One ASP.NET
  2. ASP.NET Scaffolding
  3. ASP.NET Identity
4.5
4.5 and Visual Studio 2012
  1. Strongly Typed Data Controls
  2. Model Binding
  3. Unobtrusive Validation
  4. Bundling and Minification
  5. Async Support
  6. Support for asynchronous modules and handlers
  7. Friendly URL
  8. HTML5 Features enhancements
  9. Support for WebSocket protocol
  10. oAuth Support
4.0
4.0 and Visual Studio 2010
  1. Introduced ClientIdMode property for Server Control
  2. Routing
  3. Introduced Meta tags MetaKeyword and MetaDescription
  4. Chart Control
3.5
3.5 and Visual Studio 2008
  1. Integrated ASP.NET AJAX
  2. Support LINQ
  3. New Data controls LINQ DataSource, ListView and DataPager
  4. Dynamic Data
  5. Multi-targeting Framework Support
2.0
2.0 and Visual Studio 2005
  1. New Data controls GridView, FormView and DetailsView
  2. DataSource controls SQLDataSource, Object DataSource, AccessDataSource, XMLDataSource and SiteMapDataSource
  3. Navigation controls
  4. Master pages
  5. Cross Page Postbacks
  6. Validation Groups
  7. Themes
  8. Skins
  9. Login controls
  10. Role Management
  11. Profiles
  12. Membership Service
  13. Localization and Globalization
1.1
1.1 and Visual Studio .NET 2003
  1. ASP.NET Mobile controls
  2. Built-in support for ODBC and databases
  3. Internet Protocol version 6 (IPv6) support
1.0
1.0 and Visual Studio .NET
  1. Support for Object-oriented Web application development
  2. Use of DLL class libraries
What do you think?
I hope you will enjoy the ASP.NET features. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

No comments :

Post a Comment

Step by Step Tutorial to backup database in SQL Server Management Studio 2005, 2008, 2008R2 and 2012

No comments
In this article I will provide a step by step tutorial to backup database in SQL Server Management Studio 2005, 2008, 2008R2 and 2012.
 
Preparing for Backup
In the Object Explorer of SQL Server 2005, right click on the database then select Tasks and within that Backup. In the figure below I have selected NorthWind Database.

Preparing for backup

Choose the database and Backup type
Though you have right clicked the appropriate database still you can change the database using the dropdown. Also you have to choose the backup type since we need to transfer the complete database we will choose Full Backup.

Choose the database and Backup type

Define Backup Set
Here you can specify the name of the Backup set and also when the backup set will expire. By default there is no expiry.

Define Backup Set

Set the location of the backup file
Here one has to choose the folder where the backup file will be created. Click on the Add button to set the path and the backup file name

Set the location of the backup file

Choose the folder path
Here by clicking on the browse button you can choose the path where you want the backup file to be created.

Choose the folder path

As you can see the Locate Database Window is opened. Here choose the folder where you want to create the backup of the database also provide a name to the backup file e.g. NorthWindDB.BAK. Backup files have a .BAK extension.

Locate Database

Verify the backup file
Once the OK button is clicked the backup file is created on the location that you have chosen. You can navigate to the folder to verify it

Verify the backup file

No comments :

Post a Comment

Simple Insert Update and Delete Triggers in SQL Server with example

No comments
In this article I will explain with simple examples, how to write Insert, Update and Delete Triggers in SQL Server.
This tutorial is applicable for all versions of SQL Server i.e. 2005, 2008, 2012, 2014, etc.
 
Database
I have made use of the following table Customers with the schema as follows.
Simple Insert Update and Delete Triggers in SQL Server with example
 
I have already inserted few records in the table.
Simple Insert Update and Delete Triggers in SQL Server with example
 
Below is the CustomerLogs table which will be used to log the Trigger actions.
Simple Insert Update and Delete Triggers in SQL Server with example
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
Triggers
Triggers are database operations which are automatically performed when an action such as Insert, Update or Delete is performed on a Table or a View in database.
Triggers are associated with the Table or View directly i.e. each table has its own Triggers.
 
Types of Triggers
There are two types of Triggers. After and Instead of Triggers.
After Triggers
These triggers are executed after an action such as Insert, Update or Delete is performed.
 
Instead of Triggers
These triggers are executed instead of any of the Insert, Update or Delete operations. For example, let’s say you write an Instead of Trigger for Delete operation, then whenever a Delete is performed the Trigger will be executed first and if the Trigger deletes record then only the record will be deleted.
 
After Triggers
Now I will explain you with examples the After Triggers for Insert, Update and Delete operations.
Insert Trigger
Below is an example of an After Insert Trigger. Whenever a row is inserted in the Customers Table, the following trigger will be executed. The newly inserted record is available in the INSERTED table.
The following Trigger is fetching the CustomerId of the inserted record and the fetched value is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_INSERT]
       ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @CustomerId INT
 
       SELECT @CustomerId = INSERTED.CustomerId       
       FROM INSERTED
 
       INSERT INTO CustomerLogs
       VALUES(@CustomerId, 'Inserted')
END
 
Update Trigger
Below is an example of an After Update Trigger. Whenever a row is updated in the Customers Table, the following trigger will be executed. The updated record is available in the INSERTED table.
The following Trigger is fetching the CustomerId of the updated record. In order to find which column is updated, you will need to use UPDATE function and pass the Column name of the Table to it.
The UPDATE function will return TRUE for a Column if its value was updated else it will return false.
Finally based on which column of the record has been updated a record (containing the CustomerId and the appropriate action) is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_UPDATE]
       ON [dbo].[Customers]
AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @CustomerId INT
       DECLARE @Action VARCHAR(50)
 
       SELECT @CustomerId = INSERTED.CustomerId       
       FROM INSERTED
 
       IF UPDATE(Name)
       BEGIN
              SET @Action = 'Updated Name'
       END
 
       IF UPDATE(Country)
       BEGIN
              SET @Action = 'Updated Country'
       END
 
       INSERT INTO CustomerLogs
       VALUES(@CustomerId, @Action)
END
 
Delete Trigger
Below is an example of an After Delete Trigger. Whenever a row is delete in the Customers Table, the following trigger will be executed. The deleted record is available in the DELETED table.
The following Trigger is fetching the CustomerId of the deleted record and the fetched value is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_DELETE]
       ON [dbo].[Customers]
AFTER DELETE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @CustomerId INT
 
       SELECT @CustomerId = DELETED.CustomerId       
       FROM DELETED
 
       INSERT INTO CustomerLogs
       VALUES(@CustomerId, 'Deleted')
END
 
The following screenshot displays the Log table after the above Triggers were executed.
Simple Insert Update and Delete Triggers in SQL Server with example
 
 
Instead Of Triggers
Below is an example of an Instead Of Delete Trigger. Whenever anyone tries to delete a row from the Customers table the following trigger is executed.
Inside the Trigger, I have added a condition that if record has CustomerId value 2 then such a record must not be deleted and an error must be raised. Also a record is inserted in the CustomerLogs table.
If the CustomerId value is not 2 then a delete query is executed which deletes the record permanently and a record is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_InsteadOfDELETE]
       ON [dbo].[Customers]
INSTEAD OF DELETE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @CustomerId INT
 
       SELECT @CustomerId = DELETED.CustomerId       
       FROM DELETED
 
       IF @CustomerId = 2
       BEGIN
              RAISERROR('Mudassar Khan''s record cannot be deleted',16 ,1)
              ROLLBACK
              INSERT INTO CustomerLogs
              VALUES(@CustomerId, 'Record cannot be deleted.')
       END
       ELSE
       BEGIN
              DELETE FROM Customers
              WHERE CustomerId = @CustomerId
 
              INSERT INTO CustomerLogs
              VALUES(@CustomerId, 'Instead Of Delete')
       END
END
 
The following error message shown when record with CustomerId 2 is deleted.
Simple Insert Update and Delete Triggers in SQL Server with example
 
The following screenshot displays the Log table after the Instead Of Trigger is executed.
Simple Insert Update and Delete Triggers in SQL Server with example

No comments :

Post a Comment

Simple Cursor Tutorial with Syntax example in SQL Server

No comments
In this article I will provide a tutorial with simple example that explains
- What are Cursors in SQL Server?
- What is the syntax for writing Cursors in SQL Server?
- How to write and use Cursors in SQL Server?
- Disadvantages of a Cursor in SQL Server.
Cursor is supported in all SQL Server versions i.e. 2000, 2005, 2008, 2008R2, 2012 and 2014.
 
 
Database
I have made use of the following table Customers with the schema as follows.
Simple Cursor Tutorial with Syntax example in SQL Server
 
I have already inserted few records in the table.
Simple Cursor Tutorial with Syntax example in SQL Server
 
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 
What are Cursors in SQL Server?
Cursor is a Database object which allows us to process each row and manipulate its data. A Cursor is always associated with a Select Query and it will process each row returned by the Select Query one by one.
Using Cursor we can verify each row data, modify it or perform calculations which are not possible when we get all records at once.
A simple example would be a case where you have records of Employees and you need to calculate Salary of each employee after deducting Taxes and Leaves.
 
 
What is the syntax for writing Cursors in SQL Server?
Below is the syntax for writing a Cursor. The very first thing is to declare some variables based on the columns you are fetching in your Select Query.
Note: In this article I am considering only READ_ONLY Cursors.
Then you need to declare the Cursor by giving it a name and setting its type as READ_ONLY and along with the FOR keyword you need to write the Select Query which will return the records you need to process.
Once the Cursor is setup, we need to open it using the OPEN command and then the first record is fetched and saved into the variable.
Whenever a record is fetched the @@FETCH_STATUS has value 0 and as soon as all the records returned by the Select Query are fetched, its value changes to -1.
A Cursor is associated with a WHILE LOOP which executes until the @@FETCH_STATUS has value 0.
Inside the WHILE LOOP, the processing is done for the current record and then again the next record is fetched and this process continues until @@FETCH_STATUS is 0.
Finally the Cursor is closed and deallocated using CLOSE and DEALLOCATE commands respectively.
Note: It is very important to DEALLOCATE a Cursor as otherwise it will stay in database and when you declare a Cursor with same name again, SQL Server will throw an error: A cursor with the name 'Cursor1' already exists.
 
Simple Cursor Tutorial with Syntax example in SQL Server
 
 
How to write and use Cursors in SQL Server?
Following is an example of simple Cursor in SQL Server which prints all the records of Customers table
--DECLARE THE VARIABLES FOR HOLDING DATA.
DECLARE @CustomerId INT
      ,@Name VARCHAR(100)
      ,@Country VARCHAR(100)
 
--DECLARE AND SET COUNTER.
DECLARE @Counter INT
SET @Counter = 1
 
--DECLARE THE CURSOR FOR A QUERY.
DECLARE PrintCustomers CURSOR READ_ONLY
FOR
SELECT CustomerId, Name, Country
FROM Customers
 
--OPEN CURSOR.
OPEN PrintCustomers
 
--FETCH THE RECORD INTO THE VARIABLES.
FETCH NEXT FROM PrintCustomers INTO
@CustomerId, @Name, @Country
 
--LOOP UNTIL RECORDS ARE AVAILABLE.
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @Counter = 1
      BEGIN
            PRINT 'CustomerID' + CHAR(9) + 'Name' + CHAR(9) + CHAR(9) + CHAR(9) + 'Country'
            PRINT '------------------------------------'
      END
 
      --PRINT CURRENT RECORD.
      PRINT CAST(@CustomerId AS VARCHAR(10)) + CHAR(9) + CHAR(9) + CHAR(9) + @Name + CHAR(9) + @Country
     
      --INCREMENT COUNTER.
      SET @Counter = @Counter + 1
 
     --FETCH THE NEXT RECORD INTO THE VARIABLES.
      FETCH NEXT FROM PrintCustomers INTO
      @CustomerId, @Name, @Country
END
 
--CLOSE THE CURSOR.
CLOSE PrintCustomers
DEALLOCATE PrintCustomers
 
The following screenshot displays the records printed by the above Cursor.
Simple Cursor Tutorial with Syntax example in SQL Server
You can also checkout an advanced SQL Server Cursor that sends email in my article Automated Email Notifications using SQL Server Job Scheduler.
 
 
Disadvantages of Cursor
The major disadvantage of a Cursor is its performance issue. A Cursor can be really slow when performing operations on large number of records and your SQL Query may take minutes to execute and produce results.
Thus you must wisely choose and decide on the right scenario where you want to use a Cursor.

No comments :

Post a Comment

What is Stored Procedure in SQL Server, how to create and use a Stored Procedure and its Advantages

No comments
In this article I will explain what is Stored Procedure in SQL Server, how to create (write) and use a Stored Procedure and finally the advantages of using Stored Procedure in SQL Server.
Stored Procedures are compatible in all SQL Server versions namely 2000, 2005, 2008, 2008R2, 2012 and 2014.


What is a Stored Procedure?
A Stored Procedure is a collection of SQL statements (queries) compiled together as a one unit. A Stored Procedure can contain multiple SQL statements such as SELECT, INSERT, UPDATE or DELETE.


Advantages of using a Stored Procedure
It is good to follow practice of using Stored Procedures as they are precompiled thus reduce the overhead of compiling each time.
Stored Procedures also help in preventing SQL Injections since parameters are used in it. You can refer my article on Parameterized queries to avoid SQL Injection Parameterized Queries ADO.Net
Other benefits include
1. Create once and call it N number of times
2. Reduce traffic since instead of whole query only stored procedure name is sent from front end
3. You can give selected users right to execute a particular stored procedure.
4. A Stored Procedure can be easily modified at a given point of time without modifying any code in the Application.
5. We can easily use Transaction within a Stored Procedure.
 
Creating a Stored Procedure
Below figure displays the syntax for creating a stored procedure. As you can see below to create a stored procedure CREATE keyword is used.
What is Stored Procedure in SQL Server, how to create and use a Stored Procedure and its Advantages

Example
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployeeDetails
      @EmployeeID int = 0
AS
BEGIN
      SET NOCOUNT ON;
      SELECT FirstName, LastName, BirthDate, City, Country
      FROM Employees WHERE EmployeeID=@EmployeeID
END
GO
 
      
Alter or Modify a Stored Procedure
Below figure displays the syntax for alter a stored procedure. As you can see below to modify a stored procedure ALTER keyword is used rest all remains the same.
What is Stored Procedure in SQL Server, how to create and use a Stored Procedure and its Advantages

Example
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetEmployeeDetails
      @EmployeeID int = 0
AS
BEGIN
      SET NOCOUNT ON;
      SELECT FirstName, LastName, BirthDate, City, Country
      FROM Employees WHERE EmployeeID=@EmployeeID
END
GO
 
 
Drop or Delete a Stored Procedure
Figure below displays how to drop a stored procedure. As you can see below to delete a stored procedure DROP keyword is used proceeded by the name of the stored procedure.
What is Stored Procedure in SQL Server, how to create and use a Stored Procedure and its Advantages

Example
DROP PROCEDURE GetEmployeeDetails
 

No comments :

Post a Comment

Reduce Page size Compress HTML,CSS,JS

No comments
Place below code in Web config


<httpCompression>
      <scheme name="gzip" dll="%Windir%\system32\
inetsrv\gzip.dll"/>
      <dynamicTypes>
        <add mimeType="text/*" enabled="true"/>
        <add mimeType="message/*" enabled="true"/>
        <add mimeType="application/javascript" enabled="true"/>
        <add mimeType="*/*" enabled="false"/>
      </dynamicTypes>
      <staticTypes>
        <add mimeType="text/*" enabled="true"/>
        <add mimeType="message/*" enabled="true"/>
        <add mimeType="application/javascript" enabled="true"/>
        <add mimeType="*/*" enabled="false"/>
      </staticTypes>
    </httpCompression>
    <urlCompression doStaticCompression="true" doDynamicCompression="true"/>

No comments :

Post a Comment

ASP.NET 5 is a significant redesign of ASP.NET.

No comments

 ASP.NET, MVC, and Web Pages are now merged into a single framework named MVC 6.
It includes the following features:
  • Linux support
  • OSX support
  • Node.js support
  • AngularJS support
  • Tag Helpers
  • View Components
  • Web API
  • GruntJS support
  • Bower support
  • No Visual Basic
  • No Web Forms

No comments :

Post a Comment

Sql querys

No comments







SQL SELECT Statement:
SELECT column1, column2....columnN
F­­­­­ROM table_name;

SQL DISTINCT Clause:
SELECT DISTINCT column1, column2....columnN
FROM table_name; 

SQL WHERE Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;

SQL AND/OR Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2; 

SQL IN Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);

SQL BETWEEN Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;

SQL LIKEClause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };

SQL ORDER BY Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};

SQL GROUP BY Clause:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;

SQL COUNT Clause:
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;

SQL HAVING Clause:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

SQL CREATE TABLE Statement:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);

SQL DROP TABLE Statement:
DROP TABLE table_name;

SQL CREATE INDEX Statement:
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);

SQL DROP INDEX Statement:
ALTER TABLE table_name
DROP INDEX index_name;
SQL DESC Statement:
DESC table_name;

SQL TRUNCATE TABLE Statement:
TRUNCATE TABLE table_name;

SQL ALTER TABLE Statement:
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};

SQLALTER TABLE Statement (Rename):
ALTER TABLE table_name RENAME TO new_table_name;

SQL INSERT INTO Statement:
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

SQL UPDATE Statement:
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];

SQL DELETE Statement:
DELETE FROM table_name
WHERE {CONDITION};

SQL CREATE DATABASE Statement:
CREATE DATABASE database_name;

SQL DROP DATABASE Statement:
DROP DATABASE database_name;

SQL USE Statement:
USE DATABASE database_name;

SQL COMMIT Statement:
COMMIT;

SQL ROLLBACK Statement:
ROLLBACK;

The basic syntax of INNER JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

The basic syntax of SELF JOIN is as follows:
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;

The basic syntax of table alias is as follows:
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
 (SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

No comments :

Post a Comment