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.
public static bool FnCheckSQLServerIsNotRunning()
{
ServiceController sctl= new ServiceController("MSSQL$SQLEXPRESS");
if (sctl.Status != ServiceControllerStatus.Running || sctl.Status == ServiceControllerStatus.Stopped || sctl.Status == ServiceControllerStatus.Paused)
{
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)
{
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.
private static string FnGetSqlServerName()
{
string strPCname = Environment.MachineName;
string strInstance = "MSSQL$SQLEXPRESS";
string strSqlServerName = string.Empty;
ServiceController[] services = ServiceController.GetServices();
foreach (ServiceController service in services)
{
if (service == null)
continue;
string strServicename = service.ServiceName;
if (strServicename.Contains(strInstance))>
{
strSqlServerName = strServicename;
}
}
int startIndex = strSqlServerName.IndexOf("$");
if (startIndex > -1)
{
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)
{
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)
{
Server dbServer = new Server(FnGetSqlServerName());
Database myDatabase = dbServer.Databases[strDBname];
foreach (Table myTable in myDatabase.Tables)
{
if (myTable.Name == strTablename)
{
return true;
}
}
return false;
}
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;
}
}
}
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;
PersonTable pTable = pdContext.PersonTables.CreateObject();
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";
pdContext.PersonTables.AddObject(pTable);
try
{
pdContext.SaveChanges();
}
catch (Exception excp)
{
throw new Exception("Error: " + excp.Message);
}
}
}
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";
if (dlg.ShowDialog() == DialogResult.OK)
{
pictureBox1.Image = Image.FromFile(dlg.FileName);
this.strNewImageFileName = dlg.FileName;
boNewimageSelected = true;
}
}
}
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)
{
string strButtonText = toolStripStatusLabel1.Text = e.ClickedItem.ToString();
switch (strButtonText)
{
case "Save":
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
{
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;
}
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();
}
break;
}
}
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();
var vpt = pdContext.PersonTables.Where(c => c.FirstName.Equals(strFname) && c.LastName.Equals(strLname));
if (vpt.Any())
{
return true;
}
return false;
}
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
PersonTable ppersonnew = pdContext.PersonTables.CreateObject();
if (boNewimageselected == false)
{
frmPA.strNewImageFileName = @"..\..\Resources\nophoto.jpg";
}
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;
pdContext.PersonTables.AddObject(ppersonnew);
pdContext.SaveChanges();
}
catch (Exception excp)
{
throw new Exception(excp.Message);
}
}
}
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)
{
string strButtonText = toolStripStatusLabel1.Text = e.ClickedItem.ToString();
switch (strButtonText)
{
case "Delete":
if (DALHelpers.FnDeleteCurrentRecord(pdContext, iCurrentPersonID))
{
toolStripStatusLabel1.Text = "Record deleted...";
FnRefreshAll();
this.textboxFirstname.Focus();
}
break;
}
}
Delete the Current Record with Entity Framework LINQ
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;
}
}
}
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();
if (ptRecordCount > 0)
{
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
if (DALHelpers.FnCheckRecordByID(ptResult.PersonID))
{
#region if-else; record exist and check Photo exists
if (DALHelpers.myPersontableProperty1.Photo != null)
{
#region display varbinary(MAX) field "Photo" in PictureBox;
byte[] byteArrayIn = DALHelpers.myPersontableProperty1.Photo.ToArray();
pictureBox1.Image = DALHelpers.FnByteArrayToImage(byteArrayIn);
#endregion
FnDisplayRecordsInTextBoxes(ptResult, ptRecordCount);
DALHelpers.FnEnableDisableTextBox(this.Controls, false);
}
else
{
pictureBox1.Image = Properties.Resources.nophoto;
}
#endregion
}
else
{
pictureBox1.Image = Properties.Resources.nophoto;
}
#endregion
}
else
{
pictureBox1.Image = Properties.Resources.nophoto;
FnDisplayRecordsInTextBoxes(ptResult, ptRecordCount);
DALHelpers.FnEnableDisableTextBox(this.Controls, false);
}
#endregion
FnEnableDisableToolStripButtons(true, false, true, true, true);
this.panelNavigation.Enabled = true;
DALHelpers.FnChangeTextAndColorInPanelSelectNewImage(this.textBoxHeader, "Person details", Color.Blue, this.panelSelectNewImage.Visible = false);
}
else
{
#region no records; table is empty
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
}
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)
{
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);
}
this.panelNavigation.Enabled = true;
break;
}
}
public static bool FnUpdateRecord(frmPersonalAddress frm1, bool boNewimageselected))
{
bool bRet=false;
using(pdContext=new PersonDatabaseEntities())
{
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)
{
pperson.Photo = File.ReadAllBytes(frm1.strNewImageFileName);
}
#endregion
pperson.FirstName = frm1.strFirstname;
pperson.LastName = frm1.strLastname;
pperson.Title = frm1.strTitle;
pperson.City = frm1.strCity;
pperson.Country = frm1.strCountry;
pdContext.SaveChanges();
bRet = true;
}
}
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)
{
byte[] byteArrayIn = DALHelpers.myPersontableProperty1.Photo.ToArray();
pictureBox1.Image = DALHelpers.FnByteArrayToImage(byteArrayIn);
}
}
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.
Example 1- Display image with ADO.NET
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;
}
Example 2 - Display image with ADO.NET
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);
}
}
return image;
}
Example 3 - Display image with LINQ
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);
memStream.Write(byteArrayIn, 78, byteArrayIn.Length - 78);
pictureBox1.Image = Bitmap.FromStream(memStream);
}
}
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
Employee ppersonnew=dbContext.Employees.CreateObject();
ppersonnew.Photo = File.ReadAllBytes(@"C:\Users\Public\Pictures\Sample Pictures\Penguins.jpg");
#endregion
ppersonnew.FirstName = "TestFNLinq";
ppersonnew.LastName = "TestLNLinq";
dbContext.Employees.AddObject(ppersonnew);
dbContext.SaveChanges();
this.labelMsg.Text = "Saved...";
}
catch (Exception excp)
{
throw new Exception(excp.Message);
}
}
}
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;
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;
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);
pictureBox1.Image = Image.FromStream(stream);
}
}
}
History
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).