Personal Address Book Using EntityFramework LINQ

• Download PersonalAddressBookUsingEntityFramework.zip - 1.1 MB

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:

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.

public static bool FnCheckSQLServerIsNotRunning()
{			
  ServiceController sctl= new ServiceController("MSSQL$SQLEXPRESS");
  if (sctl.Status != ServiceControllerStatus.Running || sctl.Status == ServiceControllerStatus.Stopped || sctl.Status == ServiceControllerStatus.Paused)
  {
    //sctl.Start();
    return true;
  }
  return false;
}

Check Database Exists on SQLServer

Below is the method to check whether the database exists on the SQLServer:

public static bool FnCheckDatabaseExists(string strDBname)
{
   //using Microsoft.SqlServer.Management.Smo; to use Server class
   Server dbServer = new Server(FnGetSqlServerName());
   if (dbServer.Databases[strDBname] != null)
   {
     return true;
   }
   return false;
}

Get SQLServer Name on your PC

You can find out your SQLServer name on your computer using the following method.

//loop through ServiceController; try to get "MSSQL$SQLEXPRESS" to build PCName\SQLEXPRESS as strSqlServerName
private static string FnGetSqlServerName()
{
   //local PC name
   string strPCname = Environment.MachineName;
   // service name of SQL Server Express
   string strInstance = "MSSQL$SQLEXPRESS";
   string strSqlServerName = string.Empty;
   // Add Reference: System.ServiceProcess;
   ServiceController[] services = ServiceController.GetServices();
   foreach (ServiceController service in services)
   {
     if (service == null)
       continue;
     string strServicename = service.ServiceName;
     if (strServicename.Contains(strInstance))>
     {
       strSqlServerName = strServicename;
      }
   }//foreach
   int startIndex = strSqlServerName.IndexOf("$");
   if (startIndex > -1)
   {
     // strSqlServerName=YourPCName\SQLEXPRESS;
     strSqlServerName = strPCname + @"\" + strSqlServerName.Substring(startIndex + 1);
    }
    return strSqlServerName;
}

Creates Database on the SQLServer

Once you have the SQLServer and database name you can create the database on the SQLServer with the method below:

public static void FnCreateDatabase(string strDBname)
{
   // Add Reference: Microsoft.SqlServer.Smo.dll which is in project folder "ScriptsDLLs"
   Server dbServer = new Server(FnGetSqlServerName());
   Database myDatabase = new Database(dbServer, strDBname);
   myDatabase.Create();
}

Check if Table Exists in SQLServer Database

Here is the method to check whether the table in the database exists. The return valus is true(found)/false(not found).

public static bool FnCheckDatabaseTableExists(string strDBname, string strTablename)
{
   // Add Reference: Microsoft.SqlServer.Smo.dll which is in project folder "ScriptsDLLs"
   Server dbServer = new Server(FnGetSqlServerName());
   Database myDatabase = dbServer.Databases[strDBname];
   // loop through all the tables of myDatabase
   foreach (Table myTable in myDatabase.Tables)
   {
     if (myTable.Name == strTablename)
     {
       return true; //table found
     }//if
    }//foreach
    return false; //table not found
}

Run SQL Script Using ADO.NET to Create the Table "PersonTable"

public static void FnRunSQLScriptToCreatePersonTable(string strPathFile)
{
   Assembly asm = Assembly.GetEntryAssembly();
   string appDir = Path.GetDirectoryName(asm.Location);
   string filePath = Path.Combine(appDir, strPathFile); 
   FileInfo file = new FileInfo(filePath);
   string strScript = file.OpenText().ReadToEnd();
   strScript = strScript.Replace("GO\r\n", "");
   using (SqlConnection conn = new SqlConnection(FnBuildConnectionString()))
   {
     conn.Open();
     SqlCommand cmd = new SqlCommand(strScript, conn);
     try
     {
       cmd.ExecuteNonQuery();
     }
     catch (Exception excp)
     {
      throw;
     }
   }//using
}

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.

public static void 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)
   {
     throw new 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:

• FnEnableDisableToolStripButtons(false, true,false, false,   
    true);

  to enable/disable the buttons on the ToolStrip.
• FnClearAllTextBoxes(this.Controls); to clear the TextBoxes.
• FnEnableDisableTextBox(this.Controls, true)to enable the TextBoxes for entering information.
• FnChangeTextAndColor(this.textBoxHeader, "New record",   
    Color.Red);

  to change TextBox "textBoxHeader" Text and color.
• this.textboxFirstname.Focus();
• this.panelSelectNewImage.Visible = true;
• this.panelNavigation.Enabled = false;
• pictureBox1.Image = Properties.Resources.nophoto;display nophoto.jpg from Resources in pictureBox1

If no information is entered and the "Save" button is clicked, it will show a warning message to force user to enter at least First Name and Last Name.

"Select new image or photo" button to display the "jpg/png/gif" image in PictureBox

private void btSelectNewimage_Click(object sender, EventArgs e)
{
  pictureBox1.Image = null;
  using (OpenFileDialog dlg = new OpenFileDialog())
  {
    dlg.Filter = "Image Files(jpg/png/gif)|*.jpg;*.png; *.gif";
    //or
    //dlg.Filter = @"JPEG Files (*.jpeg)|*.jpeg|PNG Files (*.png)|*.png|JPG Files (*.jpg)|*.jpg|GIF Files (*.gif)|*.gif";
    if (dlg.ShowDialog() == DialogResult.OK)
    {
      // assign selected photo/image to the PictureBox.Image property 
      pictureBox1.Image = Image.FromFile(dlg.FileName); 	
      //assign the image name to properties declared in frmPersonalAddress.cs
      this.strNewImageFileName = dlg.FileName;
      boNewimageSelected = true; //user selected a new image;
     }//if
   }//using 
}

Use of ToolsStrip Button: Save

After some information is entered, a photo is selected and the "Save" button is clicked, the following method calls and actions are executed.

• FnCheckFirstLastNameExistInDB(this.textboxFirstname.Text, 
    this.textboxLastname.Text))

  to check whether the first and last name exist in the database
• FnSaveRecord(this, boNewimageSelected);to save the new record.
• FnEnableDisableToolStripButtons(true, false, true, true,   
    true);

  to enable/disable the buttons on the ToolStrip.
• this.panelNavigation.Enabled = true; enable the buttons in the panel "panelNavigation".
• this.textboxFirstname.Focus();
• FnChangeTextAndColorInPanelSelectNewImage(this.textBoxHeader,   
    "Person details",			Color.Blue, this.panelSelectNewImage.Visible =   
    false);

  to change the "textBoxHeader" Text/Color and make the panel "panelSelectNewImage" invisible.
• FnRefreshAll();refresh and display first record.

private void toolStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
{
  //get name of button clicked on ToolStrip
  string strButtonText = toolStripStatusLabel1.Text = e.ClickedItem.ToString();
  switch (strButtonText)
  {
    case "Save":
     //force user to enter firstname and lastname
     if (string.IsNullOrEmpty(this.textboxFirstname.Text) && string.IsNullOrEmpty(this.textboxLastname.Text))
     {
       MessageBox.Show("Please enter First Name and Last Name", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
       return;
     }
     else
     {
       //check whether the new record with first+last name exists in the database 
       if (DALHelpers.FnCheckFirstLastNameExistInDB(this.textboxFirstname.Text, this.textboxLastname.Text))
       {
         MessageBox.Show("First Name and Last Name already exist in database", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
         return;
       }
       //in order to have access to all of public properties in "frmPersonal Address" class from DALHelpers class we pass as this "frmPersonalAddress" class 				
       DALHelpers.FnSaveRecord(this, boNewimageSelected);
       MessageBox.Show("New record has been inserted successfully...");
       FnEnableDisableToolStripButtons(true, false, true, true, true);
       this.panelNavigation.Enabled = true;
       this.textboxFirstname.Focus();
       DALHelpers.FnChangeTextAndColorInPanelSelectNewImage(this.textBoxHeader, "Person details", Color.Blue, this.panelSelectNewImage.Visible = false);
       FnRefreshAll();
     }//else

     break;

   }//switch
}

Check Whether the New Record by First and Last Name Exists in the Database

Here is the method in order to find out whether the new record by first and last name exists in the database: Entity Framework LINQ query with lambda expression

public static bool FnCheckFirstLastNameExistInDB(string strFname, string strLname)
{
  pdContext = new PersonDatabaseEntities();
  //LINQ query with lambda expression
  var vpt = pdContext.PersonTables.Where(c => c.FirstName.Equals(strFname) && c.LastName.Equals(strLname));
  if (vpt.Any())
  {
    return true; //record exists.
  }
  return false;//record does not exist.
}

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

public static void FnSaveRecord(frmPersonalAddress frmPA, bool boNewimageselected)
{
  using (pdContext = new PersonDatabaseEntities())
  {
    try
    {
      #region make photo ready to be inserted
        //Creates and returns an instance of the requested type 
        PersonTable ppersonnew = pdContext.PersonTables.CreateObject();
        //in case user did not select a new image--> insert nophoto.jpg
        if (boNewimageselected == false)
        {
          frmPA.strNewImageFileName = @"..\..\Resources\nophoto.jpg";
        }
        //Opens a binary file, reads the contents of the file into a byte array, and then closes the file
        ppersonnew.Photo = File.ReadAllBytes(frmPA.strNewImageFileName);
     #endregion

     ppersonnew.FirstName = frmPA.strFirstname;
     ppersonnew.LastName = frmPA.strLastname;
     ppersonnew.Title = frmPA.strTitle;
     ppersonnew.City = frmPA.strCity;
     ppersonnew.Country = frmPA.strCountry;
     ppersonnew.Notes = frmPA.strNotes;
     //------- save --------------------------------
     pdContext.PersonTables.AddObject(ppersonnew);
     pdContext.SaveChanges();
    //----------------------------------------------		
    }
    catch (Exception excp)
    {
      throw new Exception(excp.Message);
    }//try-catch 
  }//using
}

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")"

private void toolStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
{
  //get name of button clicked on ToolStrip
  string strButtonText = toolStripStatusLabel1.Text = e.ClickedItem.ToString();
  switch (strButtonText)
  {
    case "Delete":
      if (DALHelpers.FnDeleteCurrentRecord(pdContext, iCurrentPersonID))
      {
        toolStripStatusLabel1.Text = "Record deleted...";
        FnRefreshAll();
        this.textboxFirstname.Focus();
      }//if
      break;
  }//switch
}

Delete the Current Record with Entity Framework LINQ

//It returns true if record is deleted otherwise false
public static bool FnDeleteCurrentRecord(PersonDatabaseEntities pdContext, int iCurrentPersonID)
{
   bool boRet = false;
   DialogResult dr = MessageBox.Show("Are you sure you want to delete this record ? ", "Confirm deleting", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
   if (dr == DialogResult.Yes)
   {
      using (pdContext = new PersonDatabaseEntities())
      {
         PersonTable pperson = pdContext.PersonTables.Where(c => c.PersonID == iCurrentPersonID).FirstOrDefault();
         if (pperson != null)
         {
           pdContext.PersonTables.DeleteObject(pperson);
           pdContext.SaveChanges();
           boRet = true;
         }//if
				 
      }//using
    }//if 

   return boRet;
}

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

private void FnRefreshAll()
{
  using (pdContext = new PersonDatabaseEntities())
  {   
    #region check record and show image in pictureBox
    var ptRecordCount = (from ptbl in pdContext.PersonTables
                         select ptbl).Count();
    //check whether table has any records
    if (ptRecordCount > 0)
    {
      //get first record
      var ptResult = (from ptbl in pdContext.PersonTables
                      select ptbl).Take(1).FirstOrDefault();

      #region check just in case if first record exists
       if (ptResult != null)					
       {
         #region if-else
          //pass ProductPhotoID of first record and check record exist
          if (DALHelpers.FnCheckRecordByID(ptResult.PersonID))
          {
            #region if-else; record exist and check Photo exists
             //make use of property "myPersontableProperty1" which is declared in DALHelper.cs
             if (DALHelpers.myPersontableProperty1.Photo != null)
             {
              #region  display varbinary(MAX) field "Photo" in PictureBox;
                //convert varbinary(MAX) field "Photo" to byte Array
                byte[] byteArrayIn = DALHelpers.myPersontableProperty1.Photo.ToArray();
                //Convert byte[] array to Image and display in PictureBox
                pictureBox1.Image = DALHelpers.FnByteArrayToImage(byteArrayIn);
              #endregion
               FnDisplayRecordsInTextBoxes(ptResult, ptRecordCount);
               DALHelpers.FnEnableDisableTextBox(this.Controls, false);
             }             
             else   //no photo -> display nophoto.jpg
             {
               // get/show nohoto.jpg from project folder "Resources"
               pictureBox1.Image = Properties.Resources.nophoto;
              }//else	 
            #endregion
            }
            else  //DALHelpers.myPersontableProperty1.Photo == null->no photo display nophoto.jpg
            {
              // get/show nophoto.jpg from project folder "Resources"
              pictureBox1.Image = Properties.Resources.nophoto;
            }//else	  
         #endregion
        }//if (phResult != null)
        else 
        {
          // get/show nohoto.jpg from project folder "Resources"
          pictureBox1.Image = Properties.Resources.nophoto;
         FnDisplayRecordsInTextBoxes(ptResult, ptRecordCount);
          DALHelpers.FnEnableDisableTextBox(this.Controls, false);
        }//else
      #endregion		
      FnEnableDisableToolStripButtons(true, false, true, true, true);
      this.panelNavigation.Enabled = true;	
      DALHelpers.FnChangeTextAndColorInPanelSelectNewImage(this.textBoxHeader, "Person details", Color.Blue, this.panelSelectNewImage.Visible = false);							
    }//if no records; table is empty
    else
    {
      #region no records; table is empty
       // get/show nohoto.jpg from project folder "Resources"
       pictureBox1.Image = Properties.Resources.nophoto;
       DALHelpers.FnClearAllTextBoxes(this.Controls);
       DALHelpers.FnEnableDisableTextBox(this.Controls, false);
       this.panelNavigation.Enabled = false;
       DALHelpers.FnChangeTextAndColorInPanelSelectNewImage(this.textBoxHeader, "No records exist in database", Color.Blue, this.panelSelectNewImage.Visible = false);
       FnEnableDisableToolStripButtons(true, false, false, false, false);
      #endregion
     }
  #endregion 
  }//using
  boNewimageSelected = false;
}

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.

The following actions are executed for the "Edit" button:

• FnEnableDisableToolStripButtons(false, false, true, true, 
    true);

  to enable/disable the toolstrip buttons: (new, save,delete,edit,refresh).
• FnEnableDisableTextBox(this.Controls, true); to enable the textboxes on the form.
•   FnSetButtonTextEditToTextUpdate(this.editToolStripButton,
  "Update");
  

  to change the text of "Edit" button to "Update".
• this.panelNavigation.Enabled = true; enable the buttons in the panel "panelNavigation".
• this.textboxFirstname.Focus();
• FnChangeTextAndColorInPanelSelectNewImage(this.textBoxHeader,  
    "Update details", Color.Blue, this.panelSelectNewImage.Visible =  
    true);

  to change the "textBoxHeader" Text/Color and make the panel "panelSelectNewImage" invisible.

The following actions are executed for the "Update" button:

• FnUpdateRecord(this);to update the current record.
• FnSetButtonTextEditToTextUpdate(this.editToolStripButton, 
    "Edit");

  to change the text of "Update" button to "Edit".
• FnEnableDisableToolStripButtons(this.newToolStripButton, 
    true);

  to enable the "New" toolstrip button.
• FnEnableDisableTextBox(this.Controls, false); to disable the textboxes on the form.
• this.textboxFirstname.Focus();
• FnChangeTextAndColorInPanelSelectNewImage(this.textBoxHeader,  
    "Person details", Color.Blue, this.panelSelectNewImage.Visible =  
    false);

  to change the "textBoxHeader" Text/Color and make the panel "panelSelectNewImage" invisible.

Update record with Entity Framework LINQ

private void toolStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
{
  //get name of button clicked on ToolStrip
  string strButtonText = toolStripStatusLabel1.Text = e.ClickedItem.ToString();

  switch (strButtonText)
  {
    case "Update":
    if (DALHelpers.FnUpdateRecord(this, boNewimageselected))
    {
      DALHelpers.FnSetButtonTextEditToTextUpdate(this.editToolStripButton, "Edit");
      DALHelpers.FnEnableDisableToolStripButtons(this.newToolStripButton, true);
      DALHelpers.FnEnableDisableTextBox(this.Controls, false);
      this.textboxFirstname.Focus();
      DALHelpers.FnChangeTextAndColorInPanelSelectNewImage(this.textBoxHeader, "Person details", Color.Blue, this.panelSelectNewImage.Visible = false);
    }//if
    this.panelNavigation.Enabled = true;
    break;
  }//switch
}

public static bool FnUpdateRecord(frmPersonalAddress frm1, bool boNewimageselected))
{
  bool bRet=false;
  using(pdContext=new PersonDatabaseEntities())
  {
    //LINQ query with lambda expression
    var pperson = pdContext.PersonTables.Where(c => c.PersonID .Equals(frm1.iCurrentPersonID)).FirstOrDefault();
    if (pperson != null)
    {					
      #region make photo ready to be inserted
       if (boNewimageselected == true)
       {
         //Opens a binary file, reads the contents of the file into a byte array, and then closes the file
         pperson.Photo = File.ReadAllBytes(frm1.strNewImageFileName);
       }
      #endregion

      //access to public properties in Form frmPersonalAddress
      pperson.FirstName = frm1.strFirstname;
      pperson.LastName = frm1.strLastname;
      pperson.Title = frm1.strTitle;
      pperson.City = frm1.strCity;
      pperson.Country = frm1.strCountry;

      pdContext.SaveChanges();

      bRet = true;
    }//if
  }//using

  return bRet;
}

Use of ToolStrip Button: Exit

There is not much to explain about the Exit button but I think it's worth mentioning closing the Form if user clicks X button or Close ALT+F4 in Menu on the Form.

User Clicks Exit Button

public static void FnExit()
{
   DialogResult dr = MessageBox.Show("Are you sure You want to exit application?", "Confirm Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);

   if (dr == DialogResult.Yes)
   {
     Application.Exit();
   }
}

The method FnExit() is self-explanatory.

User clicks X button or Close ALT+F4 in Menu on the Form

Occurs whenever user closes the form before the form has been closed and specifies the close reason and the event of it:

private void frmPersonalAddress_FormClosing(object sender, FormClosingEventArgs e)
{
  DALHelpers.FnFormClosing(e);
}

public static void FnFormClosing(FormClosingEventArgs e)
{
   if (e.CloseReason == CloseReason.UserClosing)
   {
     if (DialogResult.No == MessageBox.Show("Are you sure You want to exit application?", "Confirm Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2))
       e.Cancel = true;
     else
       Application.Exit();
   }
}

Display image or photo with Varbinary(MAX) data type in PictureBox using Entity Framework

int iID=1;
using (pdContext = new PersonDatabaseEntities())
{
   var personRecord = (from ptbl in pdContext.PersonTables
                      where ptbl.PersonID == iID
                      select ptbl).FirstOrDefault();
   if (personRecord !=null)
   {
     //convert varbinary(MAX) field "Photo" to byte Array
     byte[] byteArrayIn = DALHelpers.myPersontableProperty1.Photo.ToArray();
     //Convert byte[] array to Image and display in PictureBox
     pictureBox1.Image = DALHelpers.FnByteArrayToImage(byteArrayIn);
   }//if
}//using

//Convert byte[] array to Image
public static Image FnByteArrayToImage(byte[] byteArrayIn)
{
  MemoryStream memstream = new MemoryStream(byteArrayIn);
  Image ret = Image.FromStream(memstream);
  return ret;
}

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.

int iId = 5;
pictureBox1.Image = FnRetrieveImage(iId);
 
private Image FnRetrieveImage(int iEmployeeid)
{
  SqlConnection connect = new SqlConnection(strConString);
  connect.Open();
  string sSql = "select Photo from Employees where EmployeeID = '" + iID + "' ";

  SqlCommand command = new SqlCommand(sSql, connect);
  SqlDataAdapter dp = new SqlDataAdapter(command);
  DataSet ds = new DataSet();
  dp.Fill(ds, "Employees");

  int iCoount = ds.Tables["Employees"].Rows.Count;
  DataRow dr = ds.Tables["Employees"].Rows[0];
  MemoryStream ms = new MemoryStream();
  Bitmap bm = default(Bitmap);
  byte[] arData = (Byte[])(ds.Tables["Employees"].Rows[iCoount - 1]["Photo"]);
  ms.Write(arData, 78, arData.Length - 78);
  bm = new Bitmap(ms);

  return bm;

}

int iId=1;
pictureBox1.Image = FnRetrieveImage(iId);

private Image FnRetrieveImage(int iEmployeeid)
{
  Image image = null;
  using (SqlConnection connection = new SqlConnection(strConString))
  {
    using (SqlCommand command = connection.CreateCommand())
    {
      command.CommandText = "SELECT Photo FROM Employees WHERE EmployeeID = @EmpID";
      command.Parameters.AddWithValue("@EmpID", iEmployeeid);
      connection.Open();
      byte[] imageData = (byte[])command.ExecuteScalar();
      MemoryStream memStream = new MemoryStream(imageData);
      memStream.Write(imageData, 78, imageData.Length - 78);
      image = Bitmap.FromStream(memStream);
    }//using
  }//using

  return image;

}

using (dbContext = new NorthwindEntities())
{
  var photoRecord = (from accom in dbContext.Employees
                     where accom.EmployeeID == 1
                     select accom).FirstOrDefault();
   if (photoRecord != null)
   {
     byte[] byteArrayIn = photoRecord.Photo.ToArray();
     MemoryStream memStream = new MemoryStream(byteArrayIn);
     // the database BLOB field contains normally only the image itself.
     //but this is not the case with Northwind, in which images are prefixed with a 78-byte header.
     memStream.Write(byteArrayIn, 78, byteArrayIn.Length - 78);
     pictureBox1.Image = Bitmap.FromStream(memStream);
   }
}//using

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.

Insert a Record with Image: ADO.NET

Example 1 - with ADO.NET

private void FnInsertImageIntoEmployeesWithADONET()
{
  SqlConnection conn = new SqlConnection("YourConnectionString");
  string strSql = "INSERT INTO Employees (FirstName, LastName, Photo) VALUES ('TestFirstname', 'TestLastname', @image)";
  conn.Open();
  byte[] image = File.ReadAllBytes(@"C:\Users\Public\Pictures\Sample Pictures\Tulips.gif");
  SqlCommand sqlCommand = new SqlCommand(strSql, conn);
  sqlCommand.Parameters.AddWithValue("@image", image);
  sqlCommand.ExecuteNonQuery();
  this.labelMsg.Text = "Saved...";
}

Insert a record with Image: LINQ

Example 1 - with LINQ

private void FnInsertImageIntoEmployeesWithLINQ()
{
  using (dbContext = new NorthwindEntities())
  {
    try
    {
      #region make photo ready to be inserted
       //creates and returns an instance of the requested type 
       Employee ppersonnew=dbContext.Employees.CreateObject();
      //opens a binary file, reads the contents of the file into a byte array, and then closes the file
      ppersonnew.Photo = File.ReadAllBytes(@"C:\Users\Public\Pictures\Sample Pictures\Penguins.jpg");
     #endregion
     ppersonnew.FirstName = "TestFNLinq";
     ppersonnew.LastName = "TestLNLinq";
     //------- save -------------------------
     dbContext.Employees.AddObject(ppersonnew);
     dbContext.SaveChanges();
     //----------------------------------------------	
     this.labelMsg.Text = "Saved...";
    } 
    catch (Exception excp)
    {
      throw new Exception(excp.Message);
     }//try-catch 
  }//using	
}

After running the methods "FnInsertImageIntoEmployeesWithADONET()" and "FnInsertImageIntoEmployeesWithLINQ" you can use following methods to display image in PictureBox

Example: Display image with ADO.NET

private void FnDisplayImageExampleADO.NET()
{
int iID = 11;//i.e : the new record
string sSql = "select Photo from Employees where EmployeeID = '" + iID + "' ";
SqlConnection connect = new SqlConnection(strConString);
connect.Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(new SqlCommand(sSql, connect));
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);

if (dataSet.Tables[0].Rows.Count == 1)
{
  Byte[] data = new Byte[0];
  data = (Byte[])(dataSet.Tables[0].Rows[0]["Photo"]);
  MemoryStream mem = new MemoryStream(data);
  pictureBox1.Image = Image.FromStream(mem);
}

Example: Display Image with LINQ

private void FnDisplayImageExampleWithLINQ()
{
  int iId=11; //i.e : the new record
  using (dbContext = new NorthwindEntities())
  {
    var photoRecord = (from accom in dbContext.Employees
                      where accom.EmployeeID == iId
                      select accom).FirstOrDefault();
    if (photoRecord != null)
    {
       byte[] byteArrayIn = photoRecord.Photo.ToArray();
       MemoryStream stream = new MemoryStream(byteArrayIn);
       //converting the byte array of image to the real image
        pictureBox1.Image = Image.FromStream(stream);
    }//if
  }//using
}

History

• 11 June 2012: Initial version
• 20 June 2012: Added

  FnCheckFirstLastNameExistInDB(this.textboxFirstname.Text, 
    this.textboxLastname.Text)

  - Check before inserting new record already exists in database.
• 20 June 2012: Added FnCheckSQLServerIsNotRunning() - Determine if SQLServer is running.
• 9 July 2012: Minor changes and various small improvements.

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).