Introduction

This project is the second version of the
Database Manipulation with ADO.NET for beginners. It is not a perfect but
a simple "Personal Address Program" with Windows Forms controls and
features to show how to use the LINQ queries on
Create (insert), Read (retrieve), Update (modify), Delete operations with Entity
Framework.

The reason I rewrote this program is: When my brother Ali saw the "Database
Manipulation with ADO.NET for beginners" on Codeproject.com in 2010 and he
asked me, "Can you show the person’s photo on the form as well?" I said, "If I had time I could do it". Unexpectedly, he passed away in Oct. 2011 from a heart attack at 49.

Now, I wanted to fulfill his wish and decided to rewrite the application.
This second version is devoted to Ali.

I am using (mostly) Entity Framework LINQ queries for data access and as
Ali wished, displaying photos as well on the Form.

Therefore the first
test record in database is Ali's record with his photo.

I wish he RESTS IN PEACE in his new world.

How it works?

If you open the solution for the first time in Visual Studio 2010 it
will:

A. Check if the SQLServer is not running or stopped or
paused

B. Check if the database on SQLServer exists.

1. If the database does not exist --> Create database on
SQLServer

2. Check if table exists. If not--> Run SQL script to create the
table in the database.

C. Check if the database exists and the table does not
exist

1. Run SQL script to create the table with first test record

D. The database and table are created successfully so
far

1. Get number of records in PersonTable with LINQ query

2.If there is/are one/more record(s) in table, display first record with
FnRefreshAll();

3. If no records in table

a-Run the method FnInsertFirstTestRecordWithVarbinaryImage()
to insert the first test record

b-Display the first record with the method
FnRefreshAll()

You can use buttons "Next, Previous, First, Last" to navigate through the
records.

Determine if SQLServer is Running or Stopped or Paused

Here is the small method to find out whether your SQLServer is running or
stopped or paused.

Insert/Save the First test record with Varbinary(MAX) data type using
Entity Framework

Below is the method to insert the record with Varbinary(MAX) using Entity
Framework.

publicstaticvoid FnInsertFirstTestRecordWithVarbinaryImage()
{
using (pdContext = new PersonDatabaseEntities())
{
#region save/store a field of type "varbinary(max)" into SQLServer using Entity Framework;
//retrieve this file from file system and store it in a byte array//Creates and returns an instance of the requested type
PersonTable pTable = pdContext.PersonTables.CreateObject();
//Opens a binary file, reads the contents of the file into a byte array, and then closes the file
pTable.Photo = File.ReadAllBytes(@"..\..\Resources\ali.jpg");
#endregion
//-----------------------------------------------
pTable.FirstName = "Ali";
pTable.LastName = "Altindag";
pTable.Title = "Restaurant Manager";
pTable.Country = "Turkey";
pTable.City = "Pazarcik";
pTable.Notes = "REST IN PEACE." + Environment.NewLine + "Ali, my brother, was born in Pazarcik-Turkey, studied and lived many years in Germany, He then moved to London in 1990.
Unexpectedly, he passed away in Oct'2011 from heart attack at 49. It was him to persuade me to rewrite the first version of this application.
He asked me in 2010 when he saw the first version of the application on Codeproject.com: -Can you show photos as well on the form ?.
I told him to start rewriting it sometime. Now to fulfill his wish I started rewriting the application using the Entity Framework and displaying photos.
Therefore the first record in database is with Ali's photo";
//------------------------------------------------// Add the new object "pTable" to the "pdContext"
pdContext.PersonTables.AddObject(pTable);
//------------------------------------------------try
{
//save new person to the data source
pdContext.SaveChanges();
}
catch (Exception excp)
{
thrownew Exception("Error: " + excp.Message);
}//try-catch
}//using
}

Use of ToolsStrip button: NEW

If you click New button, the form will look like as follows:

You can enter the new record information, select the photo/image and click
the "Save" button. Only Save, Refresh and Exit buttons are enabled. If no
photo is selected then "nophoto.jpg" will be inserted into the table.

If "New" button is clicked, the following methods and actions are
invoked:

The Use of the Method:
DALHelpers.FnSaveRecord(this,boNewimageSelected);

We pass 2 parameters: this and boNewimageSelected. In order to
have access to all public properties in "frmPersonalAddress" from
"DALHelpers" we pass as "this"
the "frmPersonalAddress" class. "boNewimageSelected"
is a static boolean variable which holds true or false in the method "private void btSelectNewimage_Click(object sender, EventArgs e)"
; namely it will indicate whether
a photo/image is selected or not. If no photo is selected we assign
"nophoto.jpg" to the string "strNewImageFileName" from
Resources\nophoto.jpg so that the content of the file can be read into
a byte array. We can then use the LINQ query to save the new record with the
jpg/gif/png image/photo which is a VARBINARY(MAX) data type.

Insert/Save new record into the database with selected Image using Entity
Framework LINQ

Use of ToolStrip Button: Delete

In case of deleting a record only the "Save" button is disabled. When
clicking the "Delete" button, user will be asked whether to delete the record
or not. If yes it will try to delete the record by PersonID. The return value
is true(deleted) or false(not deleted). After the record by PersonID is
deleted it will call the method "FnRefreshAll() and
FnSetButtonTextEditToTextUpdate(this.editToolStripButton, "Edit")"

Use of the ToolStrip Button: Refresh and the Method "FnRefreshAll()"

The use of the method "DALHelpers.FnRefreshAll();"

The method "FnRefreshAll()" gets the first record from the
table and displays it in TextBoxes and the VARBINARY(MAX) data type photo in
PictureBox. With a LINQ query we return the number of records available in the
table and check whether the table has any records. If there is one or more
records we try to run a select LINQ query to get the first record using
FirstOrDefault(). The FirstOrDefault() can return
null if it doesn't find any matching record; it means the table is empty so we
will show the message "No records exist in database". This can occur if you
delete the first test record.

Display First Record of the Database Table with Image in PicturBox with
Entity Framework LINQ

Use of the ToolStrip buttons: Edit and Update

If the "Edit" button is clicked while navigation through the records, the
text of "Edit" button is changed to "Update" and the Text in the TextBox will
be changed to "Update details" to show the user is in Edit/Update mode. The
Texboxes and the "Select new image or photo" button are enabled to be edited.

Display Image or Photo with "image" Data Type in PictureBox Using ADO.NET
and LINQ Query: Northwind-Employee

If you install and use the "Employees" table in the Northwind
you will see only 9 records, in which images are prefixed with a 78-byte
header. To display the image with these 9 records you can use the following 3
methods.

Suppose you use the following methods(Example 1 - with ADO.NET, Example 1 -
with LINQ) to insert a new record with "gif/png/jpg" image into
Northwind-Employees table and want to call the above methods to display
the image. The "Insert" will be executed but the "Display" with Example 1, 2, 3
will fail (Error message: Parameter is not valid or similar) running to
display images in PicturBox because images in Northwind-Employees are
prefixed with a 78-byte header. In this case use the methods
"FnDisplayImageExampleADO.NET()" and/or
"FnDisplayImageExampleWithLINQ()" for displaying.

Conclusion

As you can see, there is a lot of room for enhancement in the application and
it's not a perfect address program. I hope that some tips and tricks provided
here can boost your productivity with C# and it will serve as a good start for
performing CRUD operations using Entity Framework LINQ. Personally, I used most
of the methods in my Windows Form applications.

"Happiness comes to them who bring happiness to others." (Zarathustra).

First of all, the application was not meant to be about a design pattern (MVP/MVC/MVVM etc) and how to use a design pattern to discuss.

My intention was to provide the new developers some tips/tricks regarding Entity Framework LINQ so that they can profit from.

Initially I started implementing MVP design pattern in the application but later on I gave up because this would be too much to explain for the new developers to understand at the same time using Entity Framework LINQ and MVP design pattern.