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.