Difference between COM Component and .Net component

COM componets contains unmanaged code where as .NET Components comes with managed code.

We can call a dotnet component from COM Component Vice versa

.NET componets are called as Assemblies and these are DLL files.these files are pre compiled.

COM components are binary standurd (platform dependent) towards windows platform where as .NET components are platform independent.

for coming to the memory management,CLR doesn't provide any memory management features to COM components becose these belongs unmanaged code.

 
Unlike pure .NET Components COM components must register before they can be used.After
Registered these components need to be imported by using Type library Importer Tool (Tlbimp.exe).

COM components cannot support Shared/Static members

COM does't support the parameterized constructor

COM cann't support the Inheritance

Operating systems other than windows don't have a registry.So very less portablity .

SQL function

CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0)
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END

SET DATEFIRST 1
SELECT employee.dbo.ISOweek('12/26/1999') AS 'ISO Week'



create function employeefun()
returns table
as
return(select * from employee)
 
select * from employeefun()



create function employeeupdate()
returns int
as
begin
DECLARE @id int
set @id=(select id from employee where id=1)
return @id
end


select  employee.dbo.employeeupdate() as no
select * from  employee where id=employee.dbo.employeeupdate() 






CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
      empname nvarchar(50),
      mgrid nchar(5) REFERENCES employees(empid),
      title nvarchar(30)
      )

select * from employees

CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
   empname nvarchar(50) NOT NULL,
   mgrid nchar(5),
   title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
   DECLARE @RowsAdded int
   -- table variable to hold accumulated results
   DECLARE @reports TABLE (empid nchar(5) primary key,
      empname nvarchar(50) NOT NULL,
      mgrid nchar(5),
      title nvarchar(30),
      processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
   INSERT @reports
   SELECT empid, empname, mgrid, title, 0
   FROM employees
   WHERE empid = @InEmpId
   SET @RowsAdded = @@rowcount
   -- While new employees were added in the previous iteration
   WHILE @RowsAdded > 0
   BEGIN
      /*Mark all employee records whose direct reports are going to be
   found in this iteration with processed=1.*/
      UPDATE @reports
      SET processed = 1
      WHERE processed = 0
      -- Insert employees who report to employees marked 1.
      INSERT @reports
      SELECT e.empid, e.empname, e.mgrid, e.title, 0
      FROM employees e, @reports r
      WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
      SET @RowsAdded = @@rowcount
      /*Mark all employee records whose direct reports have been found
   in this iteration.*/
      UPDATE @reports
      SET processed = 2
      WHERE processed = 1
   END
  
   -- copy to the result of the function the required columns
   INSERT @retFindReports
   SELECT empid, empname, mgrid, title
   FROM @reports
   RETURN
END
GO

SELECT *
FROM fn_FindReports('1')

Abstract and interface

Abstract Class vs an Interface.

I normally used this “What is the difference between an Abstract Class and an Interface” as a quick way to gauge someone.
At the top level, the are a few basic difference. Abstract classes allow for default function definition. This means that whatever class extends the abstract class will have access to this. If we have a base class where all the classes will perform the same function, then we can define that in our Abstract class. An interface is a list of functions or properties that if a class implements it, it will have to have those functions defined within it. However, I want to know why one would want to use an interface over an abstract class, and vice versa.

When to prefer an interface

Lets say you have an interface for a Director and another interface for a Actor.
public interface Actor{
   Performance say(Line l);
}
public interface Director{
   Movie direct(boolean goodmovie);
}
 
In reality, there are Actors who are also Directors. If we are using interfaces rather than abstract classes, we can implement both Actor and Director. We could even define an ActorDirector interface that extends both like this:

public interface ActorDirector extends Actor, Director{
...
}
 
We could achieve the same thing using abstract classes. Unfortunately the alternative would require up to 2^n (where n is the number of attributes) possible combinations in order to support all possibilities.

When to prefer an Abstract class

Abstract classes allow you to provide default functionality for the subclasses. Common knowledge at this point. Why is this extremely important though? If you plan on updating this base class throughout the life of your program, it is best to allow that base class to be an abstract class. Why? Because you can make a change to it and all of the inheriting classes will now have this new functionality. If the base class will be changing often and an interface was used instead of an abstract class, we are going to run into problems. Once an interface is changed, any class that implements that will be broken. Now if its just you working on the project, that’s no big deal. However, once your interface is published to the client, that interface needs to be locked down. At that point, you will be breaking the clients code.

 Another general rule is if you are creating something that provides common functionality to unrelated classes, use an interface. If you are creating something for objects that are closely related in a hierarchy, use an abstract class. An example of this would be something like a business rules engine. This engine would take in multiple BusinessRules as classes perhaps? Each one of these classes will have an analyze function on it.

public interface BusinessRule{
   Boolean analyze(Object o);
}
 
This can be used ANYWHERE. It can be used to verify the state of your application. Verify data is correct. Verify that the user is logged in. Each one of these classes just needs to implement the analyze function, which will be different for each rule.
Where as if we were creating a generic List object, the use of abstract classes would be better. Every single List object is going to display the data in a list in some form or another. The base functionality would be to have it go through its dataprovider and build that list. If we want to change that List object, we just extend it, override our build list function, change what we want and call super.buildList();
Almost everyone knows that interfaces means you are just defining a list of functions and that abstract classes has the option of providing default functionality. The snags come when you drop the ‘why would I use one over the other?’.

 What is an Abstract Class?

An abstract class is a special kind of class that cannot be instantiated. So the question is why we need a class that cannot be instantiated? An abstract class is only to be sub-classed (inherited from). In other words, it only allows other classes to inherit from it but cannot be instantiated. The advantage is that it enforces certain hierarchies for all the subclasses. In simple words, it is a kind of contract that forces all the subclasses to carry on the same hierarchies or standards.

 What is an Interface?

An interface is not a class. It is an entity that is defined by the word Interface. An interface has no implementation; it only has the signature or in other words, just the definition of the methods without the body. As one of the similarities to Abstract class, it is a contract that is used to define hierarchies for all subclasses or it defines specific set of methods and their arguments. The main difference between them is that a class can implement more than one interface but can only inherit from one abstract class. Since C# doesn�t support multiple inheritance, interfaces are used to implement multiple inheritance.

Both Together

When we create an interface, we are basically creating a set of methods without any implementation that must be overridden by the implemented classes. The advantage is that it provides a way for a class to be a part of two classes: one from inheritance hierarchy and one from the interface.
When we create an abstract class, we are creating a base class that might have one or more completed methods but at least one or more methods are left uncompleted and declared abstract. If all the methods of an abstract class are uncompleted then it is same as an interface. The purpose of an abstract class is to provide a base class definition for how a set of derived classes will work and then allow the programmers to fill the implementation in the derived classes.
There are some similarities and differences between an interface and an abstract class that I have arranged in a table for easier comparison:
Feature
Interface
Abstract class
Multiple inheritance
A class may inherit several interfaces.
A class may inherit only one abstract class.
Default implementation
An interface cannot provide any code, just the signature.
An abstract class can provide complete, default code and/or just the details that have to be overridden.
Access Modfiers An interface cannot have access modifiers for the subs, functions, properties etc everything is assumed as public An abstract class can contain access modifiers for the subs, functions, properties
Core VS Peripheral
Interfaces are used to define the peripheral abilities of a class. In other words both Human and Vehicle can inherit from a IMovable interface.
An abstract class defines the core identity of a class and there it is used for objects of the same type.
Homogeneity
If various implementations only share method signatures then it is better to use Interfaces.
If various implementations are of the same kind and use common behaviour or status then abstract class is better to use.
Speed
Requires more time to find the actual method in the corresponding classes.
Fast
Adding functionality (Versioning)
If we add a new method to an Interface then we have to track down all the implementations of the interface and define implementation for the new method.
If we add a new method to an abstract class then we have the option of providing default implementation and therefore all the existing code might work properly.
Fields and Constants No fields can be defined in interfaces An abstract class can have fields and constrants defined

N-Tier architecture

What is n-Tier Architecture?

This is a very important topic to consider when developing an application. Many elements need to be considered when deciding on the architecture of the application, such as performance, scalability and future development issues. When you are deciding on which architecture to use, first decide on which of the three aforementioned elements you think is most valuable -- as some choices you make will impact on others. For example, some choices that boost performance will impact on the scalability or future development of your design, etc.
Here we will talk generally about what n-Tier architecture is, and then we will have a look at different n-Tier architectures you can use to develop ASP.NET applications and issues that arise relating to performance, scalability and future development issues for each one.

Firstly, what is n-Tier architecture? N-Tier architecture refers to the architecture of an application that has at least 3 "logical" layers -- or parts -- that are separate. Each layer interacts with only the layer directly below, and has specific function that it is responsible for.

Why use n-Tier architecture? Because each layer can be located on physically different servers with only minor code changes, hence they scale out and handle more server load. Also, what each layer does internally is completely hidden to other layers and this makes it possible to change or update one layer without recompiling or modifying other layers.

This is a very powerful feature of n-Tier architecture, as additional features or change to a layer can be done without redeploying the whole application. For example, by separating data access code from the business logic code, when the database servers change you only needs to change the data access code. Because business logic code stays the same, the business logic code does not need to be modified or recompiled.

[Note] tier and layer mean the same thing [End Note]

An n-Tier application usually has three tiers, and they are called the presentation tier, the business tier and the data tier. Let's have a look at what each tier is responsible for.

Presentation Layer
Presentation Layer is the layer responsible for displaying user interface and "driving" that interface using business tier classes and objects. In ASP.NET it includes ASPX pages, user controls, server controls and sometimes security related classes and objects.

Business Tier
Business Tier is the layer responsible for accessing the data tier to retrieve, modify and delete data to and from the data tier and send the results to the presentation tier. This layer is also responsible for processing the data retrieved and sent to the presentation layer.

In ASP.NET it includes using SqlClient or OleDb objects to retrieve, update and delete data from SQL Server or Access databases, and also passing the data retrieved to the presentation layer in a DataReader or DataSet object, or a custom collection object. It might also include the sending of just an integer, but the integer would have been calculated using the data in the data tier such as the number of records a table has.

BLL and DAL
Often this layer is divided into two sub layers: the Business Logic Layer (BLL), and the Data Access Layers (DAL). Business Logic Layers are above Data Access Layers, meaning BLL uses DAL classes and objects. DAL is responsible for accessing data and forwarding it to BLL.

In ASP.NET it might be using SqlClient or OleDb to retrieve the data and sending it to BLL in the form of a DataSet or DataReader. BLL is responsible for preparing or processing the data retrieved and sends it to the presentation layer. In ASP.NET it might be using the DataSet and DataReader objects to fill up a custom collection or process it to come up with a value, and then sending it to Presentation Layer. BLL sometimes works as just transparent layer. For example, if you want to pass a DataSet or DataReader object directly to the presentation layer.

Data Tier
Data tier is the database or the source of the data itself. Often in .NET it's an SQL Server or Access database, however it's not limited to just those. It could also be Oracle, mySQL or even XML. In this article we will focus on SQL Server, as it has been proven to be the fastest database within a .NET Application.

Logical Layers vs. Physical Layers (Distributed)
Logical Layers and Physical Layers are the ones that confuse people. Firstly, a logical layer means that layers are separate in terms of assembly or sets of classes, but are still hosted on the same server. Physical layer means that those assemblies or sets of classes are hosted on different servers with some additional code to handle the communication between the layers. E.g. remoting and web services.

Deciding to separate the layers physically or not is very important. It really depends on the load your application expects to get. I think it's worth mentioning some of the facts that might affect your decision.

Please DO note that separating the layers physically WILL slow your application down due to the delay in communicating between the servers throughout the network, so if you are using the physical layer approach, make sure the performance gain is worth the performance loss from this.

Hopefully you would have designed your application using the n-Tier approach. If this is the case, then note that you can separate the layers in the future.

Cost for deploying and maintaining physically separated applications is much greater. First of all, you will need more servers. You also need network hardware connecting them. At this point, deploying the application becomes more complex too! So decide if these things will be worth it or not.

Another fact that might affect your decision is how each of the tiers in the application are going to be used. You will probably want to host a tier on a separate server if more than 1 service is dependent on it, e.g. You might want to host business logic somewhere else if you have multiple presentation layers for different clients. You might also want a separate SQL server if you have other applications using the same data.

Performance, Scalability and Future Development

As I said earlier, each choice you make in the architecture will affect one of the following:
  • Performance
  • Scalability
  • or Future Development.
Here we will look at the most common choices you have to make in a typical ASP.NET application. I'll present you with some of the available options, cons and pros of each.
[Note] You should develop an application bottom up. This means build the data tier layer first. You should always design all three layers before building the actual application. [End Note]

Performance vs. Scalability vs. Future Development
When deciding on your architecture, decide what your priority is going to be. If your application is time critical, then you might have to sacrifice its scalability and extendibility just because you need more procedural like class design, which will offer that.
If you want scalability then you might have to sacrifice performance, because the scalability comes from scaling out and scaling out means communicating through a slower medium such as a LAN. Extensibility is a good thing to have but what's the point if you won’t be updating the application in the future, but instead be rewriting the whole thing? Think about whether you need extensibility or not because it can affect performance due to having more round trips to the database and also due to the simple fact that the size of the application will be bigger.

Think about what you really need for the project you are doing not what other people are doing for their projects. Architecture is a topic that really doesn't have best practices so rather than going for architecture for all of your applications, think about different architecture for each of your applications.

Presentation Tier Issues
The presentation layer must be well planned before building it. If this layer is badly designed, it could impact on the applications performance and future development paths. I'll explain why.

Firstly, let's start with performance. Remember how I said that the presentation layer 'uses' BLL to do any operations that need interaction with the data tier? No matter how well you've designed your BLL to reduce the round trips or unnecessary trips to the data tier, if the presentation layer code calls that method one or more times unnecessarily, you are still doing a round trip to the data tier. This impacts on your applications overall performance.

Performance is also affected by the different types of controls you decide to use. For example, using a DataGrid instead of a DataList or Repeater will make a noticeable performance difference. So, when do you use a DataGrid and when do you use others? We will discuss this in a minute.

Secondly, future development is also impacted upon if the presentation layer is badly designed. How? If you have repetitive HTML code in all pages of your website, what happens if you want to change a color of a table or something? Do you go to each page and change it? What would you do if your boss said "No, I don't like that... Change it back!"? You really need to implement a template system into your web pages, so when you make one change to your site design, it's propagated to all pages.

Although not related to performance and future development, the presentation layer has another role in ASP.NET applications – security. You have 4 security options in ASP.NET, and we will have a look at each option in detail shortly. Before that though, let's get back to the DataGrid, DataList and Repeater issue.

Data Grid, Data List, Repeater
Knowing the differences between these controls is very important because they are very different controls made for different uses, but people tend to get them confused.

Performance wise, you must always use a Repeater wherever possible. It is the fastest control out of the three. Unfortunately, it is also very limited in what it can do. All a repeater can do is iterate through the data source and display its properties.


A data source can be a data object such as a DataReader or dataset, but it can also be your custom collection that implements the IEnumerable and/or the IEnumerator interface. This is very good for displaying read only items such as list of news headlines, a summary of recent articles, etc.

There are times when you want to be able to "select" a row. When the row is selected, you want different item templates to be displayed. This situation is when you use a data list. DataLists allow you to select a row and display data using different templates. This is very useful for items that might need some frequent modification such as a list of news headlines for administrators where you want administrators to be able to edit headlines, etc.

The DataGrid is an interesting control. I always use it when I have a dataset as my data source. Although it's really slow, it is very flexible. It's also capable of producing a professional looking list or grid automatically. It knows the database's schema. It knows the type of fields and displays them accordingly. But one of the best features it has is its ability to allow editing of the data without having us code any postback functions, etc.