Querying SQL Server Becomes Simple

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:

using System.Data.SqlClient;



SqlConnection conn = new SqlConnection( "Data Source=(local);Initial Catalog=Northwind;Integrated
Security=SSPI");



SqlDataReader rdr = null;


conn.Open();


SqlCommand cmd = new SqlCommand("select * from database", conn);


rdr = cmd.ExecuteReader();


rdr.Close();


conn.Close();

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.

To create EDM of the 'School' database click on 'Project>Add New Item'. From the list select 'ADO.NET Entity Data Model' and change the name to 'School.edmx'.

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.

Check the EDM of the database by double clicking on 'School.edmx' in 'Solution Explorer'. You may also check the EDMX file by opening it in the XML Editor.

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:

Here's the output of our application. Select department from combo box and then edit values. To update these values click on 'button2'. 'Button1' closes the app.

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':

To see the details of 'School.edmx' file, right click on it and open it in 'XML Editor'. The XML file will show three parts: Conceptual Model, Storage Model and Mappings.

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.