How to delete/update records from Parent table then corresponding records deleted/updated on child table in Sql server

CASCADE in SQL Server with example-

Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table.

Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.

If you specify this option, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The advantage of the ON DELETE CASCADE option is that it allows you to reduce the quantity of SQL statements needed to perform delete actions.

Hide   Shrink    Copy Code

select * from dbo.ProductDetails
select * from dbo.Products

CREATE TABLE [dbo].[Products](
[ProductID] [int] NOT NULL,
[ProductDesc] [varchar](50) NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)) ON [PRIMARY]

CREATE TABLE [dbo].[ProductDetails](
[ProductDetailID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Total] [int] NOT NULL,
CONSTRAINT [PK_ProductDetails] PRIMARY KEY CLUSTERED
(
[ProductDetailID] ASC
)) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ProductDetails] WITH CHECK ADD CONSTRAINT 
[FK_ProductDetails_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE

INSERT INTO Products (ProductID, ProductDesc)
SELECT 1, 'Bike'
UNION ALL
SELECT 2, 'Car'
UNION ALL
SELECT 3, 'Books'

INSERT INTO ProductDetails
([ProductDetailID],[ProductID],[Total])
SELECT 1, 1, 200
UNION ALL
SELECT 2, 1, 100
UNION ALL
SELECT 3, 1, 111
UNION ALL
SELECT 4, 2, 200
UNION ALL
SELECT 5, 3, 100
UNION ALL
SELECT 6, 3, 100
UNION ALL
SELECT 7, 3, 200

SELECT *
FROM Products
SELECT *
FROM ProductDetails

DELETE
FROM Products
WHERE ProductID = 1

DROP TABLE ProductDetails
DROP TABLE Products

Difference between Application & cache object

Application: As in the preceding snapshots you see that the Application and Cache values are the same all over the application. Basically we use a static value in the Application Object that we want to show somewhere in the application that will be visible for every form in the Application.

We can use the Application Object in the global.asax file. 

The Application Object maintains its data until the web application is shut down or we release the data manually by assigning null or call the Clear() method. The Application Object does not have any timeout. 

Basically we use The Application Object to hit a counter on the Application, or read a readonly file or table to display in various web pages.

Cache: A Cache is very useful in web applications for performance optimization. Since application data can only be stored on the server, Cache data is stored on both the client side as well as the server side. We can define the time period for the cache using the Cache.Insert() or Cache.Add() method and the time period can be assigned from seconds to years. We even can maintain an absolute time expiration for the Cache.

We can assign value in the Cache only from web forms, not from the global.asax (we only can pass in the case of the Application and Session) file.

We retrieve the Cache data from the Cache without repeating the full cycle, which doesn't happen in the case of the Application.

And we can use the Cache if we want to calculate the time the user was logged in. Or if we are giving an online examination, then we can maintain the questions in the cache and retrieve them from there for better performance.

OR

Application and Cache objects are used for storing static data for a certain period of time.Both use Key-value pair format for data storage.

Both Application and Cache objects are one per web application.Their data can be accessed in all the pages of a web site.

APPLICATION OBJECT:

1)Application object always stores data on the server side RAM

example: Application["hits"]=1;

(key-value pair).

2)Application object maintains its data till the web application is shut down or we release the data manually by assigning null or Clear() method is called.

3)Application object has no Timeouts or File Dependencies.

4)Its data can be assigned using Global.asax file

5)Application object is not used for performance optimization.

USED in maintaining hit counters, data from readonly files/tables which can then

be displayed on varrious web pages.

CACHE OBJECT:

1)Cache object can store the data on server side RAM as well as client side RAM

example: -- Cache["data"]="asp.net";

2)Cache object maintains the static data as specified by the Absolute Expiration/ Sliding Expiration or File Dependency. The Time Period for Cache can be defined using the Cache.Insert() overloaded method or Cache.Add() method. It can be from seconds to years.

3)Cache object can be assigned data from web page and not from Global.asax file.

4)Cache is used for performance optimization. We retreive the Cache data from

the Cache without repeating the full cycle again, which is not so in the case

of the Application object.

USES OF CACHE OBJECT:

1)Static images for a certain period of time

2)Calculating the time of the users who login in a certain period of time.

3)On line exams: store the questions in the Cache and then retreive them from Cache.

Difference between throw and throw ex in c#

Throw

In Throw, the original exception stack trace will be retained. To keep the original stack trace information, the correct syntax is 'throw' without specifying an exception.

Declaration of throw

try { // do some operation that can fail } catch (Exception ex) { // do some local cleanup throw; }

Throw

In Throw ex, the original stack trace information will get override and you will lose the original exception stack trace. I.e. 'throw ex' resets the stack trace.

Declaration of throw ex

try { // do some operation that can fail } catch (Exception ex) { // do some local cleanup throw ex; } }

Example -

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ThrowAndThrowEx
{
    class Program
    {
        static void Main(string[] args)
        {
              try{
                    ThrowException1(); // line 19
                } catch (Exception x) {
                    Console.WriteLine("Exception 1:");
                    Console.WriteLine(x.StackTrace);
                }
                try {
                    ThrowException2(); // line 25
                } catch (Exception x) {
                    Console.WriteLine("Exception 2:");
                    Console.WriteLine(x.StackTrace);
                }

                Console.ReadLine();

        }

        private static void ThrowException1()
        {
            try
            {
                DivByZero(); // line 34
            }
            catch
            {
                throw; // line 36
            }
        }
        private static void ThrowException2()
        {
            try
            {
                DivByZero(); // line 41
            }
            catch (Exception ex)
            {
                throw ex; // line 43
            }
        }

        private static void DivByZero()
        {
            int x = 0;
            int y = 1 / x; // line 49
        }
    }
}

Output -

Named and optional parameters in C# 4.0

Introduction

In this article, I will explore named and optional parameters in C# 4.0. Named and optional parameters are really two distinct features, and allow us to either omit parameters which have a defined default value, and/or to pass parameters by name rather than position. Named parameters are passed by name instead of relying on its position in the parameter list, whereas optional parameters allow us to omit arguments to members without having to define a specific overload matching.

Let’s have a look at Optional parameters:

Hide   Copy Code

//In old way we will write the code as shown below.
public static double MyOldCurrencyExchange(double amount, double rate) 
{ 
    return (amount * rate); 
}

We will call the above method as shown below:

Hide   Copy Code

MyOldCurrencyExchange(500, 1.18);

Now, by using Optional parameters:

Hide   Copy Code

//In new way we will write the code as shown below
public static double MyNewCurrencyExchange(double amount, double rate=1) 
{ 
    return (amount * rate); 
}

We will call the above method as shown below:

Hide   Copy Code

MyNewCurrencyExchange (500, 1.18);  // ordinary call 
MyNewCurrencyExchange (500);  // omitting rate

Now, by using Named parameters:

Hide   Copy Code

MyNewCurrencyExchange (rate:1.18, amount:500); // reversing the order of arguments.

Now, by using Named and Optional parameters:

Hide   Copy Code

MyNewCurrencyExchange (amount:500);

Difference between IEnumerable and Ilist

In LINQ to query data from collections, we use IEnumerable and IList for data manipulation. IEnumerable is inherited by IList, hence it has all the features of it and except this, it has its own features. IList has below advantage over IEnumerable. In previous article, I explain the difference between IEnumerable and IQueryable.

IList

1. IList exists in System.Collections Namespace.
2. IList is used to access an element in a specific position/index in a list.
3. Like IEnumerable, IList is also best to query data from in-memory collections like List, Array etc.
4. IList is useful when you want to Add or remove items from the list.
5. IList can find out the no of elements in the collection without iterating the collection.
6. IList supports deferred execution.
7. IList doesn't support further filtering.

IEnumerable

1. IEnumerable exists in System.Collections Namespace.
2. IEnumerable can move forward only over a collection, it can’t move backward and between the items.
3. IEnumerable is best to query data from in-memory collections like List, Array etc.
4. IEnumerable doesn't support add or remove items from the list.
5. Using IEnumerable we can find out the no of elements in the collection after iterating the collection.
6. IEnumerable supports deferred execution.
7. IEnumerable supports further filtering.

Difference between IEnumerable and Iqueryable

In LINQ to query data from database and collections, we use IEnumerable and IQueryable for data manipulation. IEnumerable is inherited by IQueryable, Hence IQueryable has all the features of IEnumerable and except this, it has its own features. Both have its own importance to query data and data manipulation. Let’s see both the fetures and take the advantage of both the fetures to boost your LINQ Query performance.

IEnumerable

1. IEnumerable exists in System.Collections Namespace.
2. IEnumerable can move forward only over a collection, it can’t move backward and between the items.
3. IEnumerable is best to query data from in-memory collections like List, Array etc.
4. While query data from database, IEnumerable execute select query on server side, load data in-memory on client side and then filter data.
5. IEnumerable is suitable for LINQ to Object and LINQ to XML queries.
6. IEnumerable supports deferred execution.
7. IEnumerable doesn’t supports custom query.
8. IEnumerable doesn’t support lazy loading. Hence not suitable for paging like scenarios.
9. Extension methods supports by IEnumerable takes functional objects.

IEnumerable Example

 MyDataContext dc = new MyDataContext ();
IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10); 

Generated SQL statements of above query will be :

 SELECT [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0

Notice that in this query "top 10" is missing since IEnumerable filters records on client side

IQueryable

1. IQueryable exists in System.Linq Namespace.
2. IQueryable can move forward only over a collection, it can’t move backward and between the items.
3. IQueryable is best to query data from out-memory (like remote database, service) collections.
4. While query data from database, IQueryable execute select query on server side with all filters.
5. IQueryable is suitable for LINQ to SQL queries.
6. IQueryable supports deferred execution.
7. IQueryable supports custom query using CreateQuery and Execute methods.
8. IQueryable support lazy loading. Hence it is suitable for paging like scenarios.
9. Extension methods supports by IQueryable takes expression objects means expression tree.

IQueryable Example

 MyDataContext dc = new MyDataContext ();
IQueryable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10); 

Generated SQL statements of above query will be :

 SELECT TOP 10 [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0

Notice that in this query "top 10" is exist since IQueryable executes query in SQL server with all filters.