Category Archives: SQL

Entity Framework Basics


What is Entity Framework (EF)?

Microsoft has defined the Entity Framework in following words:

The Microsoft ADO.NET Entity Framework is an Object/Relational Mapping (ORM) framework that enables developers to work with relational data as domain-specific objects, eliminating the need for most of the data access plumbing code that developers usually need to write. Using the Entity Framework, developers issue queries using LINQ, then retrieve and manipulate data as strongly typed objects. The Entity Framework’s ORM implementation provides services like change tracking, identity resolution, lazy loading, and query translation so that developers can focus on their application-specific business logic rather than the data access fundamentals.

It is an enhancement to ADO.NET that gives developers an automated mechanism for accessing & storing the data in the database.

ORM is a tool for storing data from objects to relational database like MS SQL Server in an automated way without much programming. Other well known ORM frameworks for .net are DataObjects.Net, NHibernate, OpenAccess, SubSonic etc. ADO.NET Entity Framework (Open source) is from Microsoft.

Please note: Entity framework not only supports MS SQL Server, but also other database like Oracle, DB2, MySQL etc.

Types of Entities in Entity Framework:

1. EntityObject

2. POC (Plain Old CLR Object)

3. POCO Proxy

4. Self-Tracking Entities.

Each entity can have two types of properties, Scalar properties and Navigation properties.

Scalar properties are properties whose actual values are contained in the entity. Whereas Navigation properties are pointers to other related entities.

Modeling techniques using Entity Framework 4.1

1. Code first

In the Code First approach, You write your classes first and then create database from these classes. There are two new objects introduced for Code First approach, DbContext and DbSet. DbContext is the primary object for interacting with a database using a specific model. DbSet(Of TEntity) is used to perform CRUD (Create, Read, Update and Delete) operations against a specific type from the model in Code First approach.

2. Model First

In Model First approach, you create Entities, relationships, and inheritance hierarchies directly on EDMX File. So in Model First approach, when you add ADO.NET Entity Data Model, you should select ‘Empty Model’.

After creating required entities, associations and inheritance on design surface of the empty model, you can use designer’s context menu option ‘Generate database from model’ to generate the database.

3. Database First

In the Database First approach, you will reverse engineer a model from an existing database. The model is stored in an EDMX file (.edmx extension) and can be viewed and edited in the Entity Framework Designer. The classes that you interact with in your application are automatically generated from the EDMX file.

We will see examples of this approaches in upcoming articles to clear the terms more better.

Please comment below for any doubts or questions.

Error: Unable to Open Database Project in Visual Studio 2012


If you upgrade SQL Server 2012 by installing Service pack 1 (SP1), you may encounter following popup when Visual studio solution with “SQL Server Database Projects” is opened:

Unable to Open Database Project in Visual Studio 2012:

This version of SQL Server Data Tools is not compatible with the database run time components installed on this computer.

To fix this problem you need to download and install SQL Server Data Tools update

Installation:

1. Once downloaded, run the file “SSDTSetup.exe”, you will see following screen.

2. Read and agree the terms and conditions and click on Install button.

3. Installation will start. It will download some files and install them.

4. It will take some time for downloading required files depends upon your internet connection.

5. Once it finishes, you need to reboot your system.

6. Click “Restart” to reboot your system.

7. Once restarted, open the project. You will see that “SQL Server Database Projects” is loaded. If not, migrate the database projects, it will solve your problem.

DELETE and TRUNCATE Command In SQL


Delete command removes the rows from a table based on the condition that we provide with a WHERE clause, whereas Truncate command will actually remove all the rows from a table.

DELETE:

1. DELETE removes rows one at a time and records an entry in the transaction log for each deleted row. So it’s comparatively slower than TRUNCATE.

2. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

3. DELETE Can be used with or without a WHERE clause

4. DELETE Activates Triggers.

5. DELETE Can be Rolled back using logs.

6. DELETE is Data Manipulation Language (DML) Command.

7. DELETE does not reset identity of the table.

The Syntax of a DELETE statement is:

DELETE FROM TableName [WHERE condition]

TRUNCATE:

1. TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

2. TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

3. TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes remain.

4. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

5. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

6. TRUNCATE can not be Rolled back using logs.

7. TRUNCATE is Data Definition Language (DDL) Command.

8. TRUNCATE Resets identity of the table. The counter used by an identity for new rows is reset to the seed for the column.

The Syntax of a TRUNCATE statement is:

TRUNCATE TABLE TableName

Basics Of Clustered And Non-Clustered Index In SQL Server


What is an Index ?

Index is database object. It works the same way as book index do. It can be created on one or more columns (16 Max column combination). It will improve the performance of data retrieval, but it slows down data modification operations such as Insert, Update.

1. Clustered Index:

They are physical stored. Therefore a table can have only one clustered index. When a primary key is created on the table, a clustered index is automatically created. A clustered index is particularly efficient on columns that are often searched. Clustered indexes are not a good choice for columns that undergo frequent changes.

Following is the T-sql syntax of creating Primary key Clustured index:

ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
    [IDTableName] ASC
)

2. Non Clustered Index:

They are logical ordering of records. A table can have more than one Non-Clustered index. Non clustered indexes can be defined on a table or view with a clustered index. Non-Clustered column always depends on the Clustered column on the database.

Following is the T-sql syntax of creating Non Clustured index:

CREATE NONCLUSTERED INDEX index_nonClustered ON TableName ColumnName

SQL Dumper


Probably most of the developers may be knowing about SQL Dumper.

SQL Server Dumper enables you to dump selected SQL Server database tables into SQL Insert statements, that are saved as local .sql files and contain all the data required to create a duplicate table. It can also be used to backup table data.

You can choose to create an individual .sql file for each table, or combine all selected tables into a single file.

How to use SQL Dumper:
1. Download it from the following official site and install it.

 

2. Once you open it, you have 2 options. You can use either SQL server authentication or
    windows authentication. I have used SQL server authentication.
    Enter server name, login ID and password and click on connect.

3. Once connected, select database from right hand dropdown. Once selected, it will
    automatically fetch all the tables within that database.

4. Select a table from “available tables” list and it to “tables to dump” list.

5. From “Output settings”, tick the checkbox “Set output file name”, enter the name of the
    backup file without including the extension e.g like Author or tablename etc.

6. Click on “Execute” button, it will show you progress bar on the bottom of the window. Once
   done, browse the script file.

To conclude:
SQL Dumper is very handy utility for developers. Please try and let me know what do you
think on this.