Query To Get No of Records in all the Tables of a Database

declare @table_collection table(table_id int identity(1,1), table_name varchar(200), count_sql nvarchar(500), rec_count int)
declare @table_count int

insert into @table_collection
select name, 'select @rc_count = count(1) from [' + name + ']',0 from sys.tables

select @table_count = count(1) from sys.tables
declare @rec_counter int = 1

declare @temp_id int
declare @temp_count int
declare @sql nvarchar(500)

while @rec_counter<=@table_count
begin
set @temp_count = 0
select @temp_id = table_id, @sql =count_sql from @table_collection where table_id = @rec_counter

EXEC sp_executesql @sql ,N'@rc_count int OUTPUT' ,@temp_count OUTPUT
update @table_collection set rec_count = @temp_count where table_id = @rec_counter

set @rec_counter = @rec_counter + 1
end

select table_name, rec_count from @table_collection

ASP.NET Web Services & WCF Frequently Asked Questions

In this section I will try to cover the frequently asked questions about ASP.NET Web Services and Windows Communication Foundation. Again I would apologies for the questions to not be in proper sequence. Surely I would arrange the same into sequence when this section will have some significant amount of questions.
So, we start now…
Question 1 : What is the data format used by ASP.NET Web Services for communication?
Answer : XML

Question 2 : What do you mean by SOAP?
Answer : SOAP stands for Simple Object Access Protocol.
We know that ASP.NET Web services is platform independent. i.e. Web Services may be used for cross platform communication for example communication between an ASP.NET Web site running on Windows environment and a JSP Application running on Linux environment.
This simply means that there should be a standard or some common format which is used for communication. This standard is known as SOAP.

Question 3: What is the SOAP message format?
Answer : A SOAP message mainly contains Message Header and SOAP Envelope.

Header Contains the information about the message being transmitted e.g. Message Length, Host Server and Requested Service etc.

SOAP Message Format

Question 4 : What do you mean WSDL?
Answer : WSDL Stands for Web Services Description Language.

ASP.NET Web Services Vs WCF

.NET Framework 3.0 introduces new and exciting feature i.e. Windows Communication Foundation (WCF), where as earlier versions of the framework were containg ASP.NET Web Services only.

In this Post I would be mainly comparing the WCF with ASP.NET Web Service

Windows Communication Foundation (WCF) ASP.NET Web Service
WCF supports multiple bindings HTTP, WSHTTP, TCP, MSMQ. ASP.NET Web Services supports only HTTP binding.
WCF supports Atomic Transactions*. ASP.NET Web Services does not support Atomic Transactions*.
By default WCF uses SOAP for sending and receiving the messages. But WCF can support any kind of message format not only SOAP. ASP.NET Web Services can send and receive messages via the SOAP only.
The System.Runtime.Serialization.DataContract and System.Runtime.Serialization.DataMember attributes of the WCF’s System.Runtime.Serialization assembly can be added for .NET types to indicate that instances of the type are to be serialized into XML, and which particular fields or properties of the type are to be serialized. ASP.NET Web Services uses XmlSerializer to translate the XML data (Message Send or received) into .NET objects.

* Atomic Transactions
Following example describes that what does it mean by Atomic Transactions
Consider that bank A and bank B want to interact with someone’s account at the same time. Both banks want to withdraw from the account and the account has $10.00 in it. If bank A takes $7.00 and at the same time bank B tries to get $5.00, what will happen? When they start the transaction each bank believes there is $10.00 available in the account. When one of them finishes the other one will find there is not enough money to finish the transaction.
This scenario is common for computer systems and you can see it many times in memory management, IO operations and database interactions.
Atomic transactions are a way to avoid this problem. They simply lock on a transaction and do not allow any other transaction to interact with the resource. If anything fails during the Atomic transaction, everything will return to the state before the transaction started.

An Introduction To Windows Communication Foundation [WCF]

1.0 What Is WCF?

WCF stands for Windows Communication Foundations.

WCF combines the functionality from ASP.NET Web Services, .NET Remoting, Message Queuing and Enterprise Services.
WCFBlock
WCF provides the following features,

  1. Hosting For Component & Services
    WCF Service can be hosted in ASP.NET Runtime, a Windows Service, a COM+ Component or just a Windows form application for peer-to-peer computing.
  2. Declarative Behavior
    Similar to ASP.NET Web Services, attributes can be used for WCF Services e.g. ServiceContract(), OperationContract, DataContract and DataMember
  3. Communication Channels
    Similar to .NET Remoting WCF Services are flexible in changing the channels. WCF offers multiple channels to communicate using HTTP, TCP or an IPC channel.
  4. Security
  5. Extensibility

2.0 Understanding WCF
These days we are creating the software/application which should be capable of communication with other applications as well. Communication with other application simply means either sharing/exchanging the data or the sharing the logic.

Now, this communication may be of two kinds

  1. Over Intranet (Same Network/Platform i.e. .NET Application to .NET Application)
  2. Over Internet (Cross Platform may be ASP.NET to J2EE Application)

Suppose we are writing a .NET Software with n-tier architecture in which Win Form Client needs to communicate with Server in the same network. In such case we may go for .NET Remoting for communication between Client and Server.

Suppose, once our software mentioned above is ready, we need to expose some business logic to another J2EE application. This J2EE application is supposed to use our .NET Application’s logic over WWW. In such case we will have to write new ASP.NET Web Service to expose the logic.

Picture shown below shows the limitation of .NET Remoting
RemotingLimitation
WCF helps us to overcome this kind of Scenario, as WCF Service can be used as a .NET Remoting Component and ASP.NET Web Services as well.
WCFAdvantage
3.0 WCF in Real Time Scenario
Suppose a 7 Star hotel has contacted you for software which will help the organization to managing the hotel’s room booking say “Room Booking System”. Apart from its own features software should be capable of

  1. Communication with already running software within the hotel’s network for help desk purpose. (.NET Windows Form Application)
  2. Communication with already running software on Tourism Office for booking of rooms. (A J2EE Application supposed to access the application over WWW)

Off course we are suppose to implement the application using Microsoft.NET Technology.
Now, as we know that in order to communicate with another .NET application within same network .NET Remoting is the best option. But as per our requirement our application should be capable of interaction with another J2EE application over WWW. So we can’t go for .NET Remoting. ASP.NET web services may work fine but the correct option for now would be WCF Service.
WCFRealTime

4.0 WCF Communication Model

WCF follows Client-Server Architecture. Communication between Client and Server are established with the help of Endpoints exposed by the WCF Service. Endpoints are nothing but the locations defined by service through which message can be sent and received. Service may have multiple end points.
WCFCommunicationModel

5.0 Know some terms/ Kew words

  1. ServiceContract
    Service contracts describe the operations supported by a service, the message exchange pattern they use, and the format of each message. The service contract may be an interface or class for generating a service description. A service must implement at least one service contract. Interface or class supposed to be exposed as a service should be decorated with ServiceContractAttribute
  2. OperationContract
    Methods in the interface or class which are supposed to be exposed as a service should be decorated with OperationContractAttribute.
  3. DataContract
    Data contracts describe how a CLR type maps to schema. A data contract may be understood as a class or interface which is mapped to database and are supposed to exploit by WCF Service. This class or interface needs to be decorated with DataContract attribute.
  4. DataMember
    Properties or database table columns in the DataContract class which are supposed to be used by WCF Service should be decorated with DataMember attribute.

6.0 How to Create a Sample WCF Application with VS2008?

6.1 Create/ Manage database

Before proceeding toward the WCF Service Creation, we need to create a database say, “WCF” with one table having following schema

DatabaseSchema
Where, ReservationId is an auto generated Primary Key column.

Note: You may also restore the backup of the database given along with the sample application.

6.2 Create WCF Service

1. Open Visual Studio

2. Go to File à New à Project

3. From the left panel, select Web node of your language VB.NET/C#.NET

4. Now among the templates you will see WCF Service Application

5. Select the same (WCF Service Application)

6. Give Suitable Name (Say Practice.WCF) and Click on OK button.

WCFProjectCreation

7. Modify the connection strings section of web.config file of the WCF Service

8. By default Visual Studio would create a Service and an Interface Service1.svc and IService1.cs

9. Add new class RoomReservationRequest.cs (DataContract)

10. Import the name space System.Runtime.Serialization; if not imported

11. Create property with same data type for each columns present into the RoomReservationRequest table. Decorate RoomReservationRequest.cs class with [DataContract] attribute and each property with [DataMember].

namespace Practice.WCF

{
[DataContract]
public class RoomReservationRequest
{
[DataMember]
public int ReservationId
{
get; set;
}

[DataMember]
public int NoOfRooms
{
get; set;
}

[DataMember]
public string TypeOfRoom
{
get; set;
}

[DataMember]
public DateTime FromDate
{
get; set;
}

[DataMember]
public DateTime ToDate
{
get; set;
}

[DataMember]
public string ContactPersonName
{
get; set;
}

[DataMember]
public string ContactPersonMail
{
get; set;
}

[DataMember]
public string ContactPersonMob
{
get; set;
}

[DataMember]
public string Comments
{
get; set;
}

[DataMember]
public string Status
{
get; set;
}

 

 

 

}
}

12. Add a new class RoomReservationData.cs and write two methods say ReserveRoom and GeReservations

namespace Practice.WCF
{
internal class RoomReservationData
{
private string connectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
internal bool ReserveRoom(RoomReservationRequest roomReservationReq)
{
SqlConnection connection = GetConnection();
string sqlCommand = "INSERT INTO RoomReservationRequest(NoOfRooms, TypeOfRoom, FromDate, ToDate, ContactPersonName, " +
"ContactPersonMail, ContactPersonMob, Comments, Status) VALUES (" +
"@NoOfRooms, @TypeOfRoom, @FromDate, @ToDate, @ContactPersonName, " +
"@ContactPersonMail, @ContactPersonMob, @Comments, @Status)";
SqlCommand command = connection.CreateCommand();
command.CommandText = sqlCommand;
command.Parameters.Add("@NoOfRooms", System.Data.SqlDbType.Int);
command.Parameters.Add("@TypeOfRoom", System.Data.SqlDbType.NVarChar, 20);
command.Parameters.Add("@FromDate", System.Data.SqlDbType.DateTime );
command.Parameters.Add("@ToDate", System.Data.SqlDbType.DateTime);
command.Parameters.Add("@ContactPersonName", System.Data.SqlDbType.NVarChar, 50);
command.Parameters.Add("@ContactPersonMail", System.Data.SqlDbType.NVarChar, 50);
command.Parameters.Add("@ContactPersonMob", System.Data.SqlDbType.NVarChar, 20);
command.Parameters.Add("@Comments", System.Data.SqlDbType.NVarChar, 200);
command.Parameters.Add("@Status", System.Data.SqlDbType.NVarChar, 200);
command.Parameters["@NoOfRooms"].Value = roomReservationReq.NoOfRooms;
command.Parameters["@TypeOfRoom"].Value = roomReservationReq.TypeOfRoom;
command.Parameters["@FromDate"].Value = roomReservationReq.FromDate;
command.Parameters["@ToDate"].Value = roomReservationReq.ToDate;
command.Parameters["@ContactPersonName"].Value = roomReservationReq.ContactPersonName;
command.Parameters["@ContactPersonMail"].Value = roomReservationReq.ContactPersonMail;
command.Parameters["@ContactPersonMob"].Value = roomReservationReq.ContactPersonMob;
command.Parameters["@Comments"].Value = roomReservationReq.Comments;
command.Parameters["@Status"].Value = roomReservationReq.Status;

int rowsEffected =0;
try
{
rowsEffected = command.ExecuteNonQuery();
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
return rowsEffected > 0;
}

internal RoomReservationRequest[] GetReservations(DateTime fromDate, DateTime toDate)
{
List reservedRooms = new List();
SqlConnection connection = GetConnection();
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT ReservationId, NoOfRooms, TypeOfRoom, FromDate" +
",ToDate, ContactPersonName, ContactPersonMail, ContactPersonMob, Comments, Status "+
"FROM RoomReservationRequest "+
"WHERE FromDate > @FromDate AND ToDate<@ToDate";

command.Parameters.Add("@FromDate", System.Data.SqlDbType.DateTime);
command.Parameters.Add("@ToDate", System.Data.SqlDbType.DateTime);
command.Parameters["@FromDate"].Value = fromDate;
command.Parameters["@ToDate"].Value = toDate;
SqlDataReader reader = null;
try
{
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
RoomReservationRequest roomReservationRequest = new RoomReservationRequest();
roomReservationRequest.ReservationId = Convert.ToInt16(reader[0]);
roomReservationRequest.NoOfRooms = Convert.ToInt16(reader[1]);
roomReservationRequest.TypeOfRoom = reader[2].ToString();
roomReservationRequest.FromDate = Convert.ToDateTime(reader[3]);
roomReservationRequest.ToDate = Convert.ToDateTime(reader[4]);
roomReservationRequest.ContactPersonName = reader[5].ToString();
roomReservationRequest.ContactPersonMail = reader[6].ToString();
roomReservationRequest.ContactPersonMob = reader[7].ToString();
roomReservationRequest.Comments = reader[8].ToString();
roomReservationRequest.Status = reader[9].ToString();
reservedRooms.Add(roomReservationRequest);
}
}
finally
{
if (reader != null)
{
reader.Close();
reader.Dispose();
}

if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
return reservedRooms.ToArray();
}
private SqlConnection GetConnection()
{

 

 

 

SqlConnection connection = new SqlConnection(connectionString);
try
{
connection.Open();
}
finally
{
}
return connection;
}
}
}

13. Declare two methods to the interface IService1 say ReserveRoom and GetReservations

namespace Practice.WCF
{
[ServiceContract]
public interface IService1
{
[OperationContract]
bool ReserveRoom(RoomReservationRequest reservationRequest);

[OperationContract]
RoomReservationRequest[] GetReservations(DateTime fromDate, DateTime toDate);
}
}

14. Implement the Interface ISErvice1 in Service1.svc.cs class. Create instance of class RoomReservationData which we implemented in Step-12 and use the same into the implemented methods.

namespace Practice.WCF
{
public class Service1 : IService1
{
#region IService1 Members
private RoomReservationData roomReservationData = new RoomReservationData();

public bool ReserveRoom(RoomReservationRequest reservationRequest)
{
return roomReservationData.ReserveRoom(reservationRequest);
}

 

 

public RoomReservationRequest[] GetReservations(DateTime fromDate, DateTime toDate)
{
return roomReservationData.GetReservations(fromDate, toDate);
}
#endregion
}
}

15. Now we are done with the implementation of WCF Service. Set the Service1 as Startup page. Run your WCF Application. Should get the following screen.
WCFRunning

16. Publish the WCF Service.

  1. To publish the service Right Click on Practice.WCF.csproj and select the Publish option
    PublishContextMenu
  2. Enter the location where you need to publish/ host the service.
    PublishDialog
  3. Click on Publish button to publish the site.

17. You may check the published WCF Service by typing the URL into browser
e.g. http://localhost/wcf/Service1.svc , a page same as Step-14 will appear.

7.0 How to Consume the WCF Service?

7.1 Generate the Class and Config File using svcutil.exe

You might have noticed the message displayed while browsing the service
ServiceMessage

Now, we need to generate the classes which our Client Application would use to consume the service.

We may generate the classes/configuration with the help of tool svcutil.exe. Follow the steps to generate the Class/Config file

  1. Open the command prompt and Go to the location where svcutil.exe is placed. You may find the same at following place “C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin”
  2. Write the following command and hit the “enter key” to generate the Class/Config files
    SVCUtil
  3. This would generate the Service1.cs and output.config files at the same location where svcutil.exe is placed i.e. “C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin”
  4. The class generated and the config file we need to include into our client application for consuming the service.

7.2 Create Client to Consume Service

1. Open Visual Studio

2. Go to Fileà New à Project

3. Select Windows Form Application

4. Create a Form similar to below
ClientApplication
5. Include the class generated by Step iii of Section 7.1

6. Right Click on project and select the option “Add Service Reference”
ServiceRefrence
7. Enter the URL/ Address where service is hosted and hit on “Go” button to find the service. You may see the Services Methods/Logic exposed by WCF Service
ServiceRefDialog

8. Click “OK” to add the service.
9. Double Click Room Reservation Enquiry button to write the logic. Make sure that you have imported the namespace of the generated class added to the project.

private void btnEnquiry_Click(object sender, EventArgs e)
{
Service1Client client = new Service1Client();
RoomReservationRequest[] reservationEnquiry = null;
reservationEnquiry = client.GetReservations(dateFromDate.Value, dateToDate.Value);

if (reservationEnquiry.Length > 0)
{
gvReservationData.DataSource = reservationEnquiry;
gvReservationData.Visible = true;
}
else
{
gvReservationData.Visible = false;
lblMessage.Text = "Sorry data not available.";
}
}

10. When we had added the reference of the Service to the client project one configuration file also would have been added to the application i.e. App.Config. Replace the node of this file by the node of the output.config file generated into “Step- iii” of “Section 7.1”

8.0 How to Use the Source?

Unzip the file WCF.zip, you may find the following files

1. DBBackup.zip

2. Source.zip

Restore/ Create Database

Unzip the file DBBackup.zip and restore the WCF.bak file to SQL Server Database or you may create the database as suggested in Step 6.1.

Now, Unzip the file Source.zip, you may find
1. WCFService
2. WCFClient
Publish/Host Client
i. Open the directory WCFService.
ii. Double click Practice.WCF.sln file to open the solution.
iii. Modify the web.config file of Service as suggested in Step-7 of Section-6.2.
iv. Publish the service as suggested in Step-16 of Section-6.2.
Use WCF Client to consume the services
i. Open WCFClient directory.
ii. Double click HelpDeskService.sln file to open the solution.
iii. Run the solution.
iv. Select the dates and hit on Enquiry button.
Note: You may need to modify App.Config file, if you have not published the WCF Service to local machine.

OOPS FAQ’s

Firs of all I would  like to inform and say sorry that you may find the questions in random order. In future if I could build the FAQ section big then definitely I would arrange the FAQ’s in proper seq.

Your feedback/ suggestion is most welcome to make the post more better. Thanks in advance….

In fact in FAQ section we will try to brush-up our OOPS knowledge with the help of some questions,
So, we will start now..

Question/ Scenario 1.0: 

Very frequently we use to write create the instance of a class like 

ClassA objClassA = new ClassA();

what does it mean by new ClassA();  or  what is the significance of new keyword?

Answer:

In order to answer this question I would like to take the example of an Web application,

Suppose we have a website which adds two numbers. Code for adding up the two numbers we have written in some class say Operation.cs or Operation.vb.

Now there are two person’s accessing the website say Person A and Person B. Both the persons may interested in adding the different different numbers for example (2,2) or (7,8) or any thing.

Writting new keword is nothing but intimiating the Framework/ Compiler that I want to create a new instance of the class or do not want to use the same instance which Person A is using to add (2,2). 
Question/ Scenario 2.0: 
We have tow C#/VB.NET classes say BaseClass and DerivedClass. BaseClass is a public class and contains four public methods,

  1. public double Add(double num1, double num2) {.......}
  2. public double Substract(double num1, double num2){.......}
  3. public double Multiply(double num1, double num2){.......}
  4. public double Divide(double num1, double num2){.......}

DerivedClass is also a public class and contains two public methods

  1. public double SimpleInterest(double price, double rate, double time){...............}
  2. public double CompoundInterest(double price, double rate, double time){...............}

Now DerivedClass derives BaseClass

public class DerivedClass : BaseClass { ...........}
Based on above scenario answer the following questions,

Question 2.1
Which of the following is a valid?  

       

  1. BaseClass baseClass = new DerivedClass();
  2. DerivedClass derClass = new BaseClass(); 
  3.  

Ans: 1, i.e. BaseClass baseClass = new DerivedClass();

but like this you would be able to access only public methods of BaseClass not the methods of derived class.

Question 2.2
Instance of the BaseClass is created like
BaseClass baseClass = new DerivedClass();
Which of the following is a valid?

  1. baseClass.SimpleInterest(1000,2,3);
  2. baseClass.Add(3,7);

Ans: 2, i.e. baseClass.Add(3,7);
because we had created object of BaseClass

Question 3.0: Does C# support multiple inheritance?
Answer: No. C# does not support multiple inheritance but multiple inheritance is possible with the help of Interfaces.

Question 4.0: What do you mean by an interface?
Read the illustration and answer the questions,
Answer: Interfaces describe a group of related functionalities that can belong to any class or struct. Interfaces can consist of methods, properties, events, indexers, or any combination of those four member types. An interface cannot contain fields. Interfaces members are automatically public.
When a class or struct is said to inherit an interface, it means that the class or struct provides an implementation for all of the members defined by the interface. The interface itself provides no functionality that a class or struct can inherit in the way that base class functionality can be inherited. However, if a base class implements an interface, the derived class inherits that implementation.

Classes and structs can inherit from interfaces in a manner similar to how classes can inherit a base class or struct, with two exceptions:

  1. A class or struct can inherit more than one interface.
  2. When a class or struct inherits an interface, it inherits only the method names and signatures, because the interface itself contains no implementations.

To implement an interface member, the corresponding member on the class must be public, non-static, and have the same name and signature as the interface member. Properties and indexers on a class can define extra accessors for a property or indexer defined on an interface. For example, an interface may declare a property with a get accessor, but the class implementing the interface can declare the same property with both a get and set accessor. However, if the property or indexer uses explicit implementation, the accessors must match.
Interfaces and interface members are abstract; interfaces do not provide a default implementation. For more information, see Abstract and Sealed Classes and Class Members.
Interfaces can inherit other interfaces. It is possible for a class to inherit an interface multiple times, through base classes or interfaces it inherits. In this case, the class can only implement the interface one time, if it is declared as part of the new class. If the inherited interface is not declared as part of the new class, its implementation is provided by the base class that declared it. It is possible for a base class to implement interface members using virtual members; in that case, the class inheriting the interface can change the interface behavior by overriding the virtual members.

Interfaces Quick Overview
An interface has the following properties:

  1. An interface is like an abstract base class: any non-abstract type inheriting the interface must implement all its members.
  2. An interface cannot be instantiated directly.
  3. Interfaces can contain events, indexers, methods and properties.
  4. Interfaces contain no implementation of methods.
  5. Classes and structs can inherit from more than one interface.
  6. An interface can itself inherit from multiple interfaces.

Question 4.1: Implement an interface say IOperations containing following method signatures

  1. Add : a method to add two double numbers
  2. Multiply : a method to multiply two double numbers


public interface IOperations
{
double Add(doubleb a, double b);
double Multiply(double a, double b);
}

Database Normalization

What does it mean by Normalization of a Database?

Definition: “Normalization is the process of removing anomoly from a Database.”

Removing Anomoly in a database is mainly concern with,

  1. Eliminating redundant data (Storing the data in more than one table with proper relationships among the tables)
  2. Ensuring data dependencies make sense (data is should be stored into related table). 

Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

Understanding Normalization in simple words.

You may consider the following example\scenario in order to understand the concept of normalization.

Suppose we have a normalized database (simple) with three tables 

  • TableA  (with 5 Columns)
  • TableB  (with 10 Columns)
  • TableC  (with 15 Columns)

An un normalized database for the above given database will contain only one table with 30 Columns (5 + 10 + 15) and off course no relationship between the data. But in such case data would be repeated very frequently i.e. data would be redundant.

The Normal Forms

Normal Forms are the guidelines provided by the Database community to achieve an optimal database model.

First Normal Form (1NF): First normal form (1NF) sets the very basic rules for an organized database:

  • Eliminate duplicative columns from the same table.
  • Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second Normal Form (2NF): Second normal form (2NF) further addresses the concept of removing duplicative data:

  • Meet all the requirements of the first normal form.
  • Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
  • Create relationships between these new tables and their predecessors through the use of foreign keys.

Third Normal Form (3NF): Third normal form (3NF) goes one large step further:

  • Meet all the requirements of the second normal form.
  • Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF): Finally, fourth normal form (4NF) has one additional requirement:

  • Meet all the requirements of the third normal form.
  • A relation is in 4NF if it has no multi-valued dependencies.

Note: These normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database. 

What is De-Normalization & its significance?
In order to understand the concept of De-Normalization we consider the example of Data Warehousing,
Data Warehousing is a concept which helps us to manage (store and retrieve) the data in an efficient manner. 
Going Back to De-Normalization….
Suppose you have Data Warehouse. You have come across a requirement to provide some set of data which needs to join 25 tables and all the tables contains huge data, huge means really very huge data. As we know that joins are little bit costly in terms of time, then you can imagine the scenario for joining 25 tables.
In this scenario rather than storing the data in a Normalized fashion, we can denormalize the database which will help us to achieve performance.

Understanding Database

What is a Database?
Definition: Database is a structured collection of records or data that is stored in a computer system i.e. Secondary Memory.
One small question…
Que : What’s the difference between Database & Data Structure?
Ans : Database is concerned with the Secondary Memory (Hard Disk) of the machine where as Data Structure is concerned with the Primary Memory (RAM) of the computer.

In simple words database is nothing but a software which is capable of storing the data and,

  • Simple retrieval of data stored
  • Simple way to insert, update and delete the record.
  • Data should be stored in an efficient manner.

Above are the features of a simple database. Whereas an ideal database (RDBMS) should follow following characteristics/ rules in order to be a true Relational Database.

Codd’s rurle for Relational Database Management System

Rule 1 : The information Rule.

“All information in a relational data base is represented explicitly at the logical level and in exactly one way – by values in tables.”

Everything within the database exists in tables and is accessed via table access routines.

Rule 2 : Guaranteed access Rule.

“Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.”

 To access any data-item you specify which column within which table it exists, there is no reading of characters 10 to 20 of a 255 byte string.

 Rule 3 : Systematic treatment of null values.

 “Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.”

If data does not exist or does not apply then a value of NULL is applied, this is understood by the RDBMS as meaning non-applicable data.

 Rule 4 : Dynamic on-line catalog based on the relational model.

“The data base description is represented at the logical level in the same way as-ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.”

 The Data Dictionary is held within the RDBMS, thus there is no-need for off-line volumes to tell you the structure of the database.

 Rule 5 : Comprehensive data sub-language Rule.

“A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all the following items

  •  Data Definition
  • View Definition
  • Data Manipulation (Interactive and by program).
  • Integrity Constraints
  • Authorization.

Every RDBMS should provide a language to allow the user to query the contents of the RDBMS and also manipulate the contents of the RDBMS. 

Rule 6 : View updating Rule

 “All views that are theoretically updatable are also updatable by the system.”

Not only can the user modify data, but so can the RDBMS when the user is not logged-in.

 Rule 7 : High-level insert, update and delete.

 “The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update and deletion of data.”

 The user should be able to modify several tables by modifying the view to which they act as base tables.

 Rule 8 : Physical data independence.

“Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods.”

The user should not be aware of where or upon which media data-files are stored

 Rule 9 : Logical data independence.

 “Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit un-impairment are made to the base tables.”

 User programs and the user should not be aware of any changes to the structure of the tables (such as the addition of extra columns).

 Rule 10 : Integrity independence.

 “Integrity constraints specific to a particular relational data base must be definable in the relational data sub-language and storable in the catalog, not in the application programs.”

 If a column only accepts certain values, then it is the RDBMS which enforces these constraints and not the user program, this means that an invalid value can never be entered into this column, whilst if the constraints were enforced via programs there is always a chance that a buggy program might allow incorrect values into the system.

 Rule 11 : Distribution independence.

 “A relational DBMS has distribution independence.”

 The RDBMS may spread across more than one system and across several networks, however to the end-user the tables should appear no different to those that are local.

 Rule 12 : Non-subversion Rule.

 “If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity Rules and constraints expressed in the higher level relational language (multiple-records-at-a-time).”

 The RDBMS should prevent users from accessing the data without going through the Oracle data-read functions.

Note:

In Rule 5 Codd stated that an RDBMS required a Query Language, however Codd does not explicitly state that SQL should be the query tool, just that there should be a tool, and many of the initial products had their own tools, Oracle had UFI (User Friendly Interface), Ingres had QUEL (QUery Execution Language) and the never released DB1 had a language called sequel, the acronym SQL is often pronounced such as it was sequel that provided the core functionality to SQL.

 

Events & Delegates

“What’s the difference between an Event and a Delegate?”

Yes…The most popular question, which almost every one would have been asked during an interview.

In order to illustrate the difference we need to understand first that, What an Event is? and What a Delegate is?

What is an Event? Event is nothing but the action done on any control. e.g. Clicking a Button, Check-Un Check a Radio Button or a Check Box etc..

Suppose you have a simple form (ASP.NET/ WINDOWS) ame as below

Simple form to explain Event & Delegates
Simple form to explain Event & Delegates

Its a simple search functionality form, on which you need enter Employee Id and you will be displayed corresponding Employee Name on click of Find button. When ever you Click on the “Find” button one method/piece of code (Where logic for displaying Emp Name is written) should be executed in order to display name of the employee. 

Now the concept of delegates will come into picture. In fact in simple words delegate means an intermediate person who connects you to other. In the same way on your form when ever you do any action, then delegate only directs the event to that particular method.

In other words “Event is an Array List of delegates”.

What is a Delegate? You can understand delegates as a method which is capable of taking reference of any method/ function. Its similar to C++ function pointers.

Now some differences,

Events Delegates
Events are raised by using Deligates. Deligate is a Function Pointer that stores address of any function.
Events are the an action done on the control. delegate is an intermediate which work between the control and the method.
Event can fire more than one delegate, each delegate call one method. Delegate can fire only one method.
Event can fire more than one delegate, each delegate call one method. Delegate can fire only one method.

Implementation of Delegate 
Again we would implement a very frequently asked interview question in order to understand the practical uses of delegate,

Question/ Scenario : You have a Window Form Application containing two Windows Forms say Parent and PopUp. Now you are suppose to open the PopUp Form from Parent Form. Once PopUp Form is opened, change the color of Parent Form by some action/ event on PopUp Form using Delegates.

DelegateExample

Implementation:

  1. Create a Windows Application
  2. Add two Forms to the same (Parent and PopUp)
  3. Create a button on Parent Form say “Display Pop-Up”
  4. Write the code to bring the Pop-Up form
  5. i.e.

    PopUp popUpWindow = new PopUp();
    popUpWindow.Show();
    /*
    TODO : HERE WE WILL WRITE CODE TO CHANGE THE COLOR OF PARENT
    */

  6. Now, go to the PopUp Form, declare one delegate and an event which would be used to change the color of parent form

  7. public delegate void ColorChanger(Color color);
    public event ColorChanger ColorChangerDelegate;

  8. Create a button on the PopUp Form say “Change Parent Color”
  9. Double click “Change Parent Color” button to write the event for the same
  10. Make a call to the to the event i.e. ColorChangerDelegate

    private void btnSetParentColor_Click(object sender, EventArgs e)
    {
    ColorChangerDelegate(Color.Yellow);
    }
  11. Now, we are done with PopUp Form by raising the event. Now, parent form is supposed to receive the event raised on PopUp Form and change its color.
  12. In order to change the color of parent we are supposed write the lines of code which we left in Step-4 with TODO comment

    PopUp popUpWindow = new PopUp();
    popUpWindow .Show();

    popUpWindow .ColorChangerDelegate +=new PopUp.ColorChanger(Change_Color);

  13. Now we are supposed to implement a method Change_Color which would change the color of parent

    public void Change_Color(Color color)
    {
    this.BackColor = color;
    }

Note: Please do not hesitate if you require the source code or more information. I would be reachable to ak.tripathi@yahoo.com

LINQ For Beginners

1.0 What is LINQ?

LINQ stands for Language INtegrated Query. Means query language integrated with Microsoft .NET supporting languages i.e. C#.NET, VB.NET, J#.NET etc. Need not to write\ use explicit Data Access Layer.

Writing Data Access Layer require much proficiency as a Data Access Layer should be capable of at least

  1. Efficient Extraction (Select) \ Add\ Update\ Delete of data.
  2. Support to multiple database, ORACLE\ SQL Server\ MySQL etc.
  3. Transaction Management.
  4. Logging\ Tracing
  5. And many more.

LINQ enables you to use all the above features in very simple and efficient way with very less codes.

2.0 Why LINQ? \ What are benefits of LINQ?

A simple architecture of any software is like

LINQ Vs Without LINQ
LINQ Vs Without LINQ

3.0 What is LINQ entity class?

A .NET class(s) which maps you to\ from database. This class provides you flexibility to access database in very efficient way.

Usually LINQ entity class contains that many number of partial classes how many tables are present into the database. Each partial class contains properties same as columns present into the database table. Instance of the entity class acts as a single row.

4.0 How to generate LINQ Entity class?

Using Visual Studio IDE:

If you are using visual studio IDE then its very simple to create LINQ entity classes. Follow the steps below to create LINQ entity classes in your .NET Winform project

  1. Go to Start –> Microsoft Visual Studio 2008
  2. Once VS 2008 IDE is launched. Go to File –> New –> Project
  3. A “New Project” dialog would open. Select Windows Form Application templates from the templates listed right side and click ‘OK’. (Make sure you have selected right language from left panel and .NET Framework 3.5 is selected on top right)
  4. This action will create a new windows form project with name “Windows Form Application1” having default form Form1.
  5. Now in order to generate LINQ entity class Right click on project i.e. WindowsFormApplication1 node available on the right side tree.
  6. Select Add–> New Item
  7. A new dialog “Add New Item” would be opened. Select “LINQ to SQL Classes” from the various templates listed on right side and Click on Add button.
  8. Above action will bring Object Relational Designer for you. Click on Server Explorer Link available. This will bring a Server Explorer on left side. Right Click on “Data Connection” and select “Add Connection..”
  9. Now you will see a new dialog “Add Connection”. Provide your database information i.e. Server/ Username/ Password/ Database Name ad hit OK button.
  10. Above action will bring your desired database connection as a child node into “Data Connection” tree available on left.
  11. Select all the tables available and drag them to the middle area.
  12. You might get a dialog regarding saving sensitive information. You may choose ‘No’.

  13.  Now you will see the database diagram on the center panel. Save the .dbml file and build if required.
  14. Now, you are done with your entity class creation.

Without Using Visual Studio IDE

In case you don not have Visual Studio IDE, Mi.NET provides a simple utility SQLMetal.exe to generate the LINQ Entity class.

By default, the SQLMetal file is located at

drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin.

Follow the steps below to generate LINQ entity class-

  1. Start – > Run
  2. Write cmd and click on “OK” button.
  3. Go to the location drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin
  4. Type

sqlmetal /server:<SERVER NAME> /database:<DATABASE NAME> /namespace:<NAMESPASE> /code:<GENERATED CODE LOCATION> / language:csharp

example:

sqlmetal /server:myserver /database:northwind /namespace:nwind /code:nwind.cs /language:csharp

SQLMetal to generate LINQ entity class
SQLMetal to generate LINQ entity class

Start using LINQ:

Suppose we have a simple database containing three tables with Structure/ Relations as follows-

Relational Database Diagram For Sample Database
Relational Database Diagram For Sample Database
  • Generate the LINQ Entity class for the above database (Use step 4.0 to generate entity class)
  • Add the newly created entity class to your project. For a better architecture LINQ Entity class should be placed into separate class library.
  • Create instance of the LINQ Entity class. There are various overloads of LINQ entity class.

1.0 How to select a record?
As explained earlier that in order to access database first of all we need to create instance of entity class. Below is the lin of code which creates the instance of the entity class,

DataClasses1DataContext objEntityClass= new DataClasses1DataContext();

1.1 How to select all Columns and all the records?

selectrecord

 

1.2 How to use where clause?

selectwhere1

Same LINQ Sql may be written with the help of Lambda Expression as well in only one line of code

Employee employee = _dataContext.Employees.Single(emp => emp.FirstName == “First Name”);

1.3 How to select particular columns only?

selectselected

1.4 Display EmpId, FirstName, Designation and Department of the employee.

If we want to select the above record without using LINQ then we will have to Join Employee table with Department and Designation tables and the Sql will look like

traditionalquery

With the help of LINQ we can obtain the same result very easily

linqqueryforlookups

1.5 How to use alias for any column name?

columnalias2

1.6 How to bind LINQ data with GridView?

gridviewbinding

1.7 How to use Joins?

linqjoin

2.0 How to Update a Record?

linqupdate1

3.0 How to Delete a Record?

linqdelete

4.0 How to use Transactions with LINQ?

linqtransaction

5.0 How to Iterate / Loop through the records?

linqiteration1

6.0 How to execute or use Stored Procedures?

6.1 Generate Entity Class for Stored Procedures

In order to use Stored Procedures using LINQ you need to create entity classes for the stored procedures in the same way created the entity class for the tables.

Follow the steps below to create LINQ Entity class for Stored Procedures

  • Start – > Run
  • Write cmd and click on “OK” button.
  • Go to the location drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin
  • Type

storedprocentityclass

sqlmetal /server:<SERVER NAME> /database:<DATABASE NAME> /sprocs /namespace:<NAMESPASE> /code:<GENERATED CODE LOCATION> / language:csharp

Note:

1.       If you have created Database Diagram then above command will fail to generate the entity class for the Stored Procedures. You need to create a new table into your database with the name dtproperties. This table will contain following columns

2.       Above class will contain system stored procedures also. So far it was not possible avoid including system Stored Procedures. May be into recent releases of SQLMetal.exe we may get this flexibility.

3.       Using /sprocs will generate the complete entity class which will include Stored Procedures as well as database tables.

dtproperties

6.2 Execute Stored Procedures

Now your newly created entity class will contain a method with the name same as the stored procedure name. You simply need to call the method

linqstoredproc1

In Progres…….

(Complete article would be available within couple of days. Sorry for Inconvenience ……….