DataGridView CRUD With Saving Image to SQL Server using C#

• In this sample demo we will learn in detail how to perform CRUD operation for Student Profile Management using DatGridView WinForms.
• How to upload and insert/edit image for Students to SQL Server Database.
• How to display images from SQL Server to DataGridView Image Column.
• Edit and delete Image column in DataGridView.
• Create DataGridView dynamically using my DataGridview Helper Class (Refer my previous article for DataGridView helper Class Create a DatagGridView helper class using C# ).
• We will be using DAL Class and BizClass for performing CRUD Operation.
• How to display round shape image to the DataGridview Student Profile Image Column.

Prerequisites

• Visual Studio 2015 - You can download it from here.

Code part

1. Create Database and Table
   
   We will create a StudentDetails table to be used for the Student Profile CRUD Operations. The following is the script to create a database and Table query. Run this script in your SQL Server. I have used SQL Server 2014.
   
   1. --Script to create DB,Table and sample Insert data
   2. USE MASTER;
   3. -- 1) Check for the Database Exists .If the database is exist then drop and create new DB
   4. IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'StudentsDB' )
   5. BEGIN
   6. ALTER DATABASE StudentsDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   7. DROP DATABASE StudentsDB ;
   8. END
   11. CREATE DATABASE StudentsDB
   12. GO
   14. USE StudentsDB
   15. GO
   17. -- 1) //////////// ToysDetails table
   19. -- Create Table ToysDetails ,This table will be used to store the details like Toys Information
   21. IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'StudentDetails' )
   22. DROP TABLE StudentDetails
   23. GO
   25. CREATE TABLE StudentDetails
   26. (
   27. std_ID int identity(1,1),
   28. StudentName VARCHAR(100) NOT NULL,
   29. Email VARCHAR(100) NOT NULL,
   30. Phone VARCHAR(100) NOT NULL,
   31. Address VARCHAR(100) NOT NULL,
   32. IMAGEs varbinary(MAX)
   33. CONSTRAINT [PK_StudentDetails] PRIMARY KEY CLUSTERED
   34. (
   35. [std_ID] ASC
   36. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   37. ) ON [PRIMARY]
   39. GO
   41. select * from StudentDetails
   After creating our Table we will create a Stored Procedure for our CRUD Operations.

1. 2. CREATE PROCEDURE [dbo].[USP_StudentID_Select]
   3. (
   4. @std_ID int
   5. )
   6. AS
   7. BEGIN
   8. select std_ID as StdNO,
   9. StudentName as StdName,
   10. Email as Email,
   11. Phone as Phone,
   12. Address as Address,
   13. IMAGEs as StdImage
   15. FROM StudentDetails
   16. Where
   17. std_ID = @std_ID
   19. END
   22. -- To Insert Student Detail
   23. CREATE PROCEDURE [dbo].[USP_Student_Insert]
   24. (
   25. @StudentName VARCHAR(100),
   26. @Email VARCHAR(100) = '',
   27. @Phone VARCHAR(100) = '',
   28. @Address VARCHAR(100) = '',
   29. @IMAGEs varbinary(MAX)
   30. )
   31. AS
   32. BEGIN
   33. IF NOT EXISTS (SELECT StudentName FROM StudentDetails WHERE StudentName=@StudentName)
   34. BEGIN
   36. INSERT INTO StudentDetails
   37. (StudentName ,Email ,Phone ,Address ,IMAGEs)
   38. VALUES
   39. (@StudentName ,@Email ,@Phone ,@Address ,@IMAGEs)
   41. Select 'Inserted' as results
   43. END
   44. ELSE
   45. BEGIN
   46. Select 'Exists' as results
   47. END
   48. END
   50. -- To Update Student Detail
   51. CREATE PROCEDURE [dbo].[USP_Student_Update]
   52. ( @std_ID Int=0,
   53. @StudentName VARCHAR(100),
   54. @Email VARCHAR(100) = '',
   55. @Phone VARCHAR(100) = '',
   56. @Address VARCHAR(100) = '',
   57. @IMAGEs varbinary(MAX)
   58. )
   59. AS
   60. BEGIN
   62. UPDATE StudentDetails SET
   63. StudentName = @StudentName ,
   64. Email =@Email,
   65. Phone =@Phone,
   66. Address =@Address,
   67. IMAGEs =@IMAGEs
   68. WHERE
   69. std_ID=@std_ID
   71. Select 'Updated' as results
   74. END
   77. -- to Delete
   78. CREATE PROCEDURE [dbo].[USP_Student_Delete]
   79. ( @std_ID Int=0 )
   80. AS
   81. BEGIN
   83. DELETE FROM StudentDetails WHERE std_ID=@std_ID
   85. Select 'Deleted' as results
   87. END
2. Create your MVC Web Application in Visual Studio 2015
   
   After installing our Visual Studio 2015, click Start, then Programs and select Visual Studio 2015 - Click Visual Studio 2015.
   
   Click New, then Project, select Windows and select Windows Forms Application. Enter your Project Name as “DatagridViewCRUD” and click OK.
   
   

After we have created our WinForms Project. Now we will create a folder named “Images” to add image and “Helper” for creating our DataGridView Helper Class, SQL BIZ and DAL Class from Solution Explorer. Add all the images to the project images folder. And also add the main images like Edit and Delete as Resource file.



1. Creating DatagridView Helper Class

For how to create the Helper Class and its uses kindly refer my previous article: Create a DatagGridView helper class using C#.

Add a new Class named ShanuDGVHelper.cs to the Helper folder. Copy and paste the following code inside the helper class.

Check for your Namespace name and if your namespace name not matched with the class file then change it to your namespace name. For example, here our project name is DatagridViewCRUD, so you can see in the namespace it will be as “DatagridViewCRUD”. If your project name is different than kindly change it.

The complete details of the helper class can be found from the above link.

Note here in this class file for image columns the resource file will be used. In case your resource file name is different then change it.

2. Creating DAL and Biz Class

• Business logic: Here the Business logic is a class. From the UI (our code behind) we pass all our input from the user to the Business Logic class as objects.
  
  
• Data Access Layer: From the Business logic class we pass all the object parameters to this Data Access Layer Class. This class will use the ADO.NET objects like Command (Select), Command Type (Query type is text or Stored Procedure), ExceuteNonQuery (perform Insert/Update and Delete), ExecuteDataset (return select statement) and ExecuteScalar (to return single data).

For creating DAL class right click our Helper folder and add new Class file and give the name as SQLDALClass.cs.

Copy the following code and paste in the DAL Class.AS we have already seen if the Namespace is different then enter your namespace.

Note: In this class file I will be creating a text file for storing the SQL Connection string. Kindly change the connections string with your SQL Connections.

3. Creating Biz Class

Same like this we create Biz class where we created a method to pass Parameter Objects by creating methods. Here is the complete code for our Biz class.
For the Circle Image display we will be using DatagridView CellFormatting Event and for Edit/ Delete we will be using CellContentClick event.

4. Circle Image to Display in Grid

In the DatagridView CellFormatting event we check for the Student Image Column. We pass each Student image to MakeCircleImage method to display the student’s image in circle shape inside DataGridView.


5. Search Student Details

In the search button click I will call the bindData() method to bind the result. We will pass the Student Name as parameter with SP name to our Business Logic class and from Bizx class we get the result as Dataset and bind the result to DataGridView.
6. Insert/Edit Student Details

We will create new form for Insert and Update and will use one form for both Add/Edit Student Details. Also, we will design our form with all the needed controls and add a PictureBox and Upload Button to save the Student Profile photos to SQL Server.



From our main form for Add we pass parameter 0 for Add and Student ID as parameter for Edit.

Here we can see from our Add Student button click we will pass the parameter as “0” for Student Add form.
In frmSudentAdd form constructor we will get the Students ID passed from Main form and stored in local variable.
In Form Load we will check if the StudentIDS is not equal to 0 which means it is for Add new Student. IF the StudentIDS is greater than 0 then it is for edit.
If the Student ID is passed for the Edit then the Student ID will be passed as parameter to get the details of the Student and display the details for edit.
Upload Image: In upload Image Button click select the image file using OpenFileDialog and add the selected image to the PictureBox.

Save Button Click

In Save Button click first we check for New User or Edit the existing user. For New user call the following,
Add New Student:

We will pass all the parameters to the BIZ class with Image as Byte Object. After Successful insert we will display the message to the user.


Edit Student Details

For Edit in Main form DataGridView Cell content click we will get the selected student id and pass the id to frmStudentAdd for editing the student details.
We can edit the Student details and also update any new Profile photos for the students. We will pass all the parameter to the BIZ class with Image as Byte Object. After successful update we will display the message to the user.

7. Delete Student Details



In the DataGridView Cell Content click event we will check that the clicked column is equal to delete. If the clicked column is deleted then we display the confirmation box for user for confirmation to delete. If user confirms for delete then we will delete the selected Student details.