Querying SQL server becomes simple

BANGALORE, INDIA: Often developers need to understand the database before they can write access codes for it. Understanding complex database structure that is controlled by administrator becomes a bottleneck in application development cycle. Using Microsoft's ADO.NET Entity Framework, developers can create an entity relationship model of the database (ie SQL Server) inside Visual Studio 2008. This conceptual model is then used by developers to write code against database and that in turn reduces code development time along with lines of code. This is a step by Microsoft to increase abstraction, making things efficient for developers.

Consider this example: when we write code to access data from a database (SQL Server), we often have to write queries containing 'Joins,' which can only be used if there is complete understanding of the underlying database structure. Another issue is regarding writing 'connection strings' to connect and disconnect from database. Here is a sample code that shows how developers normally write the code:

In this code, developer is spending too much time on database related issues. The real aim behind developing application logic is delayed. ADO.NET Entity Framework along with LINQ tries to address these issues making application development simple and quick.

Implementing ADO.NET Entity Framework

In this section we will show how to use ADO.NET entity framework feature for existing database and how it simplifies the above code. Before you actually start using this feature, you should have Visual Studio 2008 SP1 installed on your system. We will start with creating a 'Windows Form Application' project in VS by the name of 'PCQLADOEF.' We shall use C# as the programming language. Once the project has been created, we will add an 'ADO.NET Entity Data Model' item to the project. This can be done by clicking on 'Project' and then on 'Add New Item.' This action will start a wizard; from this wizard select the appropriate database to create the entity model. Here we used 'School' database.

Now once the wizard finishes, you shall have the 'School.edmx' file created. Double clicking on this file will show the model in the Designer window. This is very helpful in understanding the structure of database in question. To understand the EDMX file more, right click on 'School.edmx' and open it with 'XML Editor'. There are three major portions of this file: Conceptual Model, Storage Model and Mappings. EntityType represents the table along with properties defining different fields and Association represents relationship of entity. Storage Model is the representation of database schema and Mappings defines movement between conceptual layer and actual storage. One can easily push back the changes made to conceptual layer to actual database by writing a single line of code.

The next step is to query the conceptual model or EDM (entity data model) model that we have just created. This can be done by adding the following controls to our form: DataGridView and ComboBox, and two buttons. Here DataGridView and ComboBox are for displaying data from EDM. One button is to update changes made to EDM back to actual database and the other is used to close the application. Here's the sample code:

using System.Data.Objects;

using System.Data.Objects.DataClasses;

namespace PCQLADOEF

{ public partial class Form1 : Form

{ private SchoolEntities schoolContext;

public Form1()

{ InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{ schoolContext = new SchoolEntities();

ObjectQuery departmentQuery =

schoolContext.Department.Include("Course").OrderBy("it.Name");

try

{ this.comboBox1.DataSource = departmentQuery;

this.comboBox1.DisplayMember = "Name";

} catch (Exception ex)

{ MessageBox.Show(ex.Message);

} }

Here's the code for 'button1.' It closes the form and disconnects connection to the EDM:

private void button1_Click(object sender, EventArgs e)

{ this.Close();

schoolContext.Dispose();

}

To populate data from EDM to data grid of the form, the following code is written:

private void com-

boBox1_SelectedIndexChanged(object sender, EventArgs e)

{ try

{

Department department =

(Department)this.comboBox1.SelectedItem;

dataGridView1.DataSource = department.Course;

dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);

} catch (Exception ex)

{ MessageBox.Show(ex.Message);

} }

Finally the changes made to EDM need to be translated back to actual database, this is done by writing the following code on click event of 'button2':

private void button2_Click(object sender, EventArgs e)

{ try

{ int numChanges;

numChanges = schoolContext.SaveChanges();

MessageBox.Show(numChanges.ToString() +

" change(s) saved to the database.");

this.Refresh();

} catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

}

}

}

With ADO.NET Entity Framework programming is simplified with the introduction of a new layer between actual database and application. The actual database here can only be SQL Server and not any other database; this can be seen as limiting factor though there are many open source APIs available for other databases.