DataGridView CRUD With Saving Image to SQL Server using C#

 

Introduction

Few members requested me to write an article for DataGridView with CRUD (Insert, Update, Select and Delete). As a result here I have created a simple demo program with the following features.

This article will explain:
  • 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 [nameFROM sys.databases WHERE [name] = 'StudentsDB' )  
    5. BEGIN  
    6. ALTER DATABASE StudentsDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
    7. DROP DATABASE StudentsDB ;  
    8. END  
    9.   
    10.   
    11. CREATE DATABASE StudentsDB  
    12. GO  
    13.   
    14. USE StudentsDB  
    15. GO  
    16.   
    17. -- 1) //////////// ToysDetails table  
    18.   
    19. -- Create Table  ToysDetails ,This table will be used to store the details like Toys Information  
    20.   
    21. IF EXISTS ( SELECT [nameFROM sys.tables WHERE [name] = 'StudentDetails' )  
    22. DROP TABLE StudentDetails  
    23. GO  
    24.   
    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  = ONON [PRIMARY]      
    37. ON [PRIMARY]    
    38.   
    39. GO  
    40.   
    41. select * from StudentDetails  
    After creating our Table we will create a Stored Procedure for our CRUD Operations. 
  1. -- 1) Stored procedure to Select Student Details  
  2. -- Author      : Shanu                                                               
  3. -- Create date : 2015-12-01                                                                
  4. -- Description : Student Details                                   
  5. -- Tables used :  Student Details                                                      
  6. -- Modifier    : Shanu                                                                 
  7. -- Modify date : 2015-12-01                                                                  
  8. -- =============================================    
  9. -- exec USP_Student_Select ''  
  10. -- =============================================                                                            
  11. CREATE PROCEDURE [dbo].[USP_Student_Select]                                              
  12.    (                            
  13.      @StudentName           VARCHAR(100)     = ''   
  14.       )                                                        
  15. AS                                                                
  16. BEGIN        
  17.          select   std_ID as StdNO,  
  18.                    StudentName as StdName,  
  19.                    Email as Email,  
  20.                    Phone as Phone,  
  21.                    Address as Address,  
  22.                    IMAGEs as StdImage  
  23.                   
  24.          FROM StudentDetails   
  25.           Where     
  26.                  StudentName like @StudentName +'%'           
  27.           ORDER BY  
  28.               StudentName  
  29.            
  30. END  
    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  
    14.                   
    15.          FROM StudentDetails   
    16.           Where     
    17.                  std_ID = @std_ID         
    18.           
    19. END  
    20.   
    21.   
    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    
    35.     
    36.                  INSERT INTO StudentDetails  
    37.            (StudentName   ,Email     ,Phone      ,Address  ,IMAGEs)  
    38.      VALUES  
    39.            (@StudentName    ,@Email       ,@Phone       ,@Address      ,@IMAGEs)  
    40.                             
    41.             Select 'Inserted' as results    
    42.                             
    43.             END    
    44.          ELSE    
    45.              BEGIN    
    46.                      Select 'Exists' as results    
    47.               END     
    48. END    
    49.   
    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            
    61.         
    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    
    70.       
    71.             Select 'Updated' as results    
    72.                             
    73.              
    74. END    
    75.   
    76.   
    77. -- to Delete  
    78. CREATE PROCEDURE [dbo].[USP_Student_Delete]                                                  
    79.    (  @std_ID               Int=0 )                                                            
    80. AS                                                                    
    81. BEGIN            
    82.       
    83.         DELETE FROM StudentDetails WHERE  std_ID=@std_ID    
    84.     
    85.          Select 'Deleted' as results    
    86.                 
    87. END    
  1. 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.

    Windows for application

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.

Helper

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.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System;  
  6. using System.Collections.Generic;  
  7. using System.ComponentModel;  
  8. using System.Data;  
  9. using System.Drawing;  
  10. using System.Linq;  
  11. using System.Text;  
  12. using System.Windows.Forms;  
  13. using System.Windows.Forms;  
  14. using System.ComponentModel;  
  15. using System.Collections.Generic;  
  16. using System.IO;  
  17. using System.Diagnostics;  
  18. using System.Text.RegularExpressions;  
  19. using System.Drawing.Imaging;  
  20. using System.Runtime.InteropServices;  
  21. using System.Drawing.Text;  
  22. using System.Drawing.Drawing2D;  
  23. /// <summary>  
  24. /// Author : Shanu  
  25. /// Create date : 2015-12-01  
  26. /// Description :Student Register  
  27. /// Latest  
  28. /// Modifier :   
  29. /// Modify date :   
  30. namespace DatagridViewCRUD  
  31. {  
  32.     public partial class Form1: Form  
  33.     {#  
  34.         region Attribute  
  35.         // ReceptionSystemSrc.Helper.Webcamera.WebCam webcam;  
  36.         Boolean keypadOn = false;  
  37.         DataGridView Master_shanuDGV = new DataGridView();  
  38.         Button btn = new Button();  
  39.         Boolean Iscaptuered = false;  
  40.         Helper.BizClass bizObj = new Helper.BizClass();  
  41.         Helper.ShanuDGVHelper objshanudgvHelper = new Helper.ShanuDGVHelper();  
  42.         DataSet ds = new DataSet();  
  43.         PrivateFontCollection pfc = new PrivateFontCollection();  
  44.         int ival = 0;#  
  45.         endregion  
  46.         public Form1()  
  47.         {  
  48.             InitializeComponent();  
  49.         }  
  50.         private void Form1_Load(object sender, EventArgs e)  
  51.         {  
  52.             try  
  53.             {  
  54.                 MasterGrid_Initialize();  
  55.             }  
  56.             catch (Exception ex)  
  57.             {}  
  58.         }  
  59.         public void MasterGrid_Initialize()  
  60.         {  
  61.             Helper.ShanuDGVHelper.Layouts(Master_shanuDGV, Color.White, Color.White, Color.White, false, Color.SteelBlue, falsefalsefalse, Color.White, 46, 60, "small");  
  62.             //Set Height,width and add panel to your selected control  
  63.             Helper.ShanuDGVHelper.Generategrid(Master_shanuDGV, pnlGrid, 1000, 600, 10, 10);  
  64.             // Color Image Column creation  
  65.             Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageEditColumn, "Edit""Edit""Edit"true, 60, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  66.             // Color Image Column creation  
  67.             Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageDelteColumn, "Delete""Delete""Delete"true, 60, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  68.             // Color Image Column creation  
  69.             Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageBoundcolumn, "StdImage""Image""Image"true, 60, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  70.             //// BoundColumn creation  
  71.             Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn, "StdNO""StdNO""StdNO"true, 80, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  72.             //// BoundColumn creation  
  73.             Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn, "StdName""StdName""StdName"true, 180, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  74.             //// BoundColumn creation  
  75.             Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn, "Email""Email""Email"true, 180, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  76.             //// BoundColumn creation  
  77.             Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn, "Phone""Phone""Phone"true, 180, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  78.             //// BoundColumn creation  
  79.             Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn, "Address""Address""Address"true, 180, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  80.             //// Color Image Column creation  
  81.             //Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.ImageColumn, "StaffID", "", "", true, 40, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleRight, Color.Transparent, null, "", "", Color.Black);  
  82.             //// Color Image Column creation  
  83.             //Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageEditColumn, "Edit", "", "", true, 38, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleRight, Color.Transparent, null, "", "", Color.Black);  
  84.             //// Color Image Column creation  
  85.             //Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageDelteColumn, "Delete", "", "", true, 38, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleRight, Color.Transparent, null, "", "", Color.Black);  
  86.             bindData();  
  87.             //objshanudgvHelper.MasterDGVs_CellFormatting(Master_shanuDGV, Master_shanuDGV.Columns["IMG"].Index, ShanuEventTypes.cellContentClick, ShanuControlTypes.ImageColumn ds.Tables[0], "IMG");  
  88.             Master_shanuDGV.CellFormatting += new DataGridViewCellFormattingEventHandler(MasterDGVs_CellFormatting);  
  89.             Master_shanuDGV.SelectionChanged += new EventHandler(Master_shanuDGV_SelectionChanged);  
  90.             Master_shanuDGV.CellContentClick += new System.Windows.Forms.DataGridViewCellEventHandler(Master_shanuDGV_CellContentClick);  
  91.             // Master_shanuDGV.DefaultCellStyle.ForeColor = Color.FromA#333333;  
  92.         }  
  93.         private void Master_shanuDGV_SelectionChanged(Object sender, EventArgs e)  
  94.         {  
  95.             Master_shanuDGV.ClearSelection();  
  96.         }  
  97.         private void bindData()  
  98.             {  
  99.                 try  
  100.                 {  
  101.                     // Bind data to DGV.  
  102.                     SortedDictionary < stringstring > sd = new SortedDictionary < stringstring > ()  
  103.                     {};  
  104.                     sd.Add("@StudentName", txtName.Text.Trim());  
  105.                     ds = bizObj.SelectList("USP_Student_Select", sd);  
  106.                     Master_shanuDGV.DataSource = null;  
  107.                     if (ds.Tables[0].Rows.Count > 0)  
  108.                     {  
  109.                         Master_shanuDGV.DataSource = ds.Tables[0];  
  110.                     }  
  111.                 }  
  112.                 catch (Exception ex)  
  113.                 {}  
  114.             }  
  115.             // Cell Content Click Event  
  116.         private void Master_shanuDGV_CellContentClick(object sender, DataGridViewCellEventArgs e)  
  117.         {  
  118.             if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "Edit")  
  119.             {  
  120.                 try  
  121.                 {  
  122.                     string studentID = ds.Tables[0].Rows[e.RowIndex]["StdNO"].ToString();  
  123.                     frmSudentAdd obj = new frmSudentAdd(studentID);  
  124.                     obj.ShowDialog();  
  125.                     bindData();  
  126.                 }  
  127.                 catch (Exception ex)  
  128.                 {}  
  129.             }  
  130.             else if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "Delete")  
  131.             {  
  132.                 try  
  133.                 {  
  134.                     string studentID = ds.Tables[0].Rows[e.RowIndex]["StdNO"].ToString();  
  135.                     if (MessageBox.Show("Are You Sure to Delete Student Details ?""Delete Student", MessageBoxButtons.YesNo) == DialogResult.Yes)  
  136.                     {  
  137.                         SortedDictionary < stringstring > sd = new SortedDictionary < stringstring > ()  
  138.                         {};  
  139.                         sd.Add("@std_ID", studentID);  
  140.                         DataSet ds1 = new DataSet();  
  141.                         ds1 = bizObj.SelectList("USP_Student_Delete", sd);  
  142.                         if (ds1.Tables[0].Rows.Count > 0)  
  143.                         {  
  144.                             string result = ds1.Tables[0].Rows[0][0].ToString();  
  145.                             if (result == "Deleted")  
  146.                             {  
  147.                                 MessageBox.Show("Student Deleted Successful, Thank You!""Successfull", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  148.                                 bindData();  
  149.                             }  
  150.                         }  
  151.                     }  
  152.                 }  
  153.                 catch (Exception ex)  
  154.                 {}  
  155.             }  
  156.         }#  
  157.         region Image Colukmn  
  158.         public static Image MakeCircleImage(Image img)  
  159.         {  
  160.             Bitmap bmp = new Bitmap(img.Width, img.Height);  
  161.             using(GraphicsPath gpImg = new GraphicsPath())  
  162.             {  
  163.                 gpImg.AddEllipse(0, 0, img.Width, img.Height);  
  164.                 using(Graphics grp = Graphics.FromImage(bmp))  
  165.                 {  
  166.                     grp.Clear(Color.White);  
  167.                     grp.SetClip(gpImg);  
  168.                     grp.DrawImage(img, Point.Empty);  
  169.                 }  
  170.             }  
  171.             return bmp;  
  172.         }  
  173.         void MasterDGVs_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)  
  174.         {  
  175.             try  
  176.             {  
  177.                 if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "StdImage")  
  178.                 {  
  179.                     if (ds.Tables[0].Rows[e.RowIndex]["StdImage"] != "" && ds.Tables[0].Rows[e.RowIndex]["StdImage"] != DBNull.Value)  
  180.                     {  
  181.                         byte[] bits = new byte[0];  
  182.                         bits = (byte[]) ds.Tables[0].Rows[e.RowIndex]["StdImage"];  
  183.                         MemoryStream ms = new MemoryStream(bits);  
  184.                         System.Drawing.Image imgSave = System.Drawing.Image.FromStream(ms);  
  185.                         e.Value = MakeCircleImage(imgSave);  
  186.                     }  
  187.                     else  
  188.                     {  
  189.                         System.Drawing.Image imgSave = (Image) DatagridViewCRUD.Properties.Resources.gridUserImage;  
  190.                         e.Value = MakeCircleImage(imgSave);  
  191.                     }  
  192.                 }  
  193.             }  
  194.             catch (Exception ex)  
  195.             {}  
  196.         }  
  197.         public Image byteArrayToImage(byte[] byteArrayIn)  
  198.         {  
  199.             using(MemoryStream mStream = new MemoryStream(byteArrayIn))  
  200.             {  
  201.                 return Image.FromStream(mStream);  
  202.             }  
  203.         }#  
  204.         endregion  
  205.         private void btnSearch_Click(object sender, EventArgs e)  
  206.         {  
  207.             bindData();  
  208.         }  
  209.         private void btnStaffAdd_Click(object sender, EventArgs e)  
  210.         {  
  211.             frmSudentAdd obj = new frmSudentAdd("0");  
  212.             obj.ShowDialog();  
  213.             bindData();  
  214.         }  
  215.     }  
  216. }  
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.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Data;  
  6. using System.Data.SqlClient;  
  7. using System.IO;  
  8. using System.Windows.Forms;  
  9. /// <summary>  
  10. /// Author : Shanu  
  11. /// Create date : 2015-05-09  
  12. /// Description :MYSQL DBCONNECT Helper CLASS  
  13. /// Latest  
  14. /// Modifier :   
  15. /// Modify date :   
  16. /// </summary>  
  17. namespace DatagridViewCRUD.Helper  
  18. {  
  19.     class SQLDALClass  
  20.     {  
  21.         public String ConnectionString = "server=.; database=StudentsDB; user id=URID; password=PWD;";  
  22.         public SqlConnection connection;#  
  23.         region Initiallize  
  24.         public SQLDALClass()  
  25.             {  
  26.                 Initialize();  
  27.             }  
  28.             //Initialize values  
  29.         private void Initialize()  
  30.         {  
  31.             ConnectionString = ReadConnectionString();  
  32.             connection = new SqlConnection(ConnectionString);  
  33.         }  
  34.         public String ReadConnectionString()  
  35.         {  
  36.             string path = Application.StartupPath + @ "\DBConnection.txt";  
  37.             String connectionString = "";  
  38.             if (!File.Exists(path))  
  39.             {  
  40.                 using(StreamWriter tw = File.CreateText(path))  
  41.                 {  
  42.                     tw.WriteLine("server=.; database=StudentsDB; user id=URID; password=PWD;");  
  43.                     tw.Close();  
  44.                     ConnectionString = "server=.; database=StudentsDB; user id=URID; password=PWD;";  
  45.                 }  
  46.             }  
  47.             else  
  48.             {  
  49.                 TextReader tr = new StreamReader(path);  
  50.                 connectionString = tr.ReadLine();  
  51.                 tr.Close();  
  52.             }  
  53.             return connectionString;  
  54.         }#  
  55.         endregion# region DB ConnectionOpen  
  56.         public bool OpenConnection()  
  57.         {  
  58.             try  
  59.             {  
  60.                 connection.Open();  
  61.                 return true;  
  62.             }  
  63.             catch (SqlException ex)  
  64.             {  
  65.                 writeLogMessage(ex.Message.ToString());  
  66.             }  
  67.             return false;  
  68.         }#  
  69.         endregion# region DB Connection Close  
  70.         //Close connection  
  71.         public bool CloseConnection()  
  72.         {  
  73.             try  
  74.             {  
  75.                 connection.Close();  
  76.                 return true;  
  77.             }  
  78.             catch (SqlException ex)  
  79.             {  
  80.                 writeLogMessage(ex.Message.ToString());  
  81.                 return false;  
  82.             }  
  83.         }#  
  84.         endregion# region ExecuteNonQuery  
  85.         for insert / Update and Delete  
  86.             // For Student  
  87.             // Insert  
  88.         public DataSet SP_Student_ImageInsert(String SP_NAME, string StudentName, string Email, string Phone, string Address, byte[] IMAGEs)  
  89.             {  
  90.                 DataSet ds = new DataSet();  
  91.                 //open connection  
  92.                 if (OpenConnection() == true)  
  93.                 {  
  94.                     //create command and assign the query and connection from the constructor  
  95.                     SqlCommand cmd = new SqlCommand(SP_NAME, connection);  
  96.                     cmd.CommandType = CommandType.StoredProcedure;  
  97.                     cmd.Parameters.Add("@StudentName", SqlDbType.VarChar);  
  98.                     cmd.Parameters.Add("@Email", SqlDbType.VarChar);  
  99.                     cmd.Parameters.Add("@Phone", SqlDbType.VarChar);  
  100.                     cmd.Parameters.Add("@Address", SqlDbType.VarChar);  
  101.                     cmd.Parameters.Add("@IMAGEs", SqlDbType.VarBinary);  
  102.                     cmd.Parameters["@StudentName"].Value = StudentName;  
  103.                     cmd.Parameters["@Email"].Value = Email;  
  104.                     cmd.Parameters["@Phone"].Value = Phone;  
  105.                     cmd.Parameters["@Address"].Value = Address;  
  106.                     if (IMAGEs == null)  
  107.                     {  
  108.                         cmd.Parameters["@IMAGEs"].Value = DBNull.Value;  
  109.                     }  
  110.                     else  
  111.                     {  
  112.                         cmd.Parameters["@IMAGEs"].Value = IMAGEs;  
  113.                     }  
  114.                     //Execute command  
  115.                     SqlDataAdapter da = new SqlDataAdapter(cmd);  
  116.                     da.Fill(ds);  
  117.                     //close connection  
  118.                     CloseConnection();  
  119.                 }  
  120.                 return ds;  
  121.             }  
  122.             // Update  
  123.         public DataSet SP_Student_ImageEdit(String SP_NAME, int std_ID, string StudentName, string Email, string Phone, string Address, byte[] IMAGEs)  
  124.         {  
  125.             DataSet ds = new DataSet();  
  126.             //open connection  
  127.             if (OpenConnection() == true)  
  128.             {  
  129.                 //create command and assign the query and connection from the constructor  
  130.                 SqlCommand cmd = new SqlCommand(SP_NAME, connection);  
  131.                 cmd.CommandType = CommandType.StoredProcedure;  
  132.                 cmd.Parameters.Add("@std_ID", SqlDbType.Int);  
  133.                 cmd.Parameters.Add("@StudentName", SqlDbType.VarChar);  
  134.                 cmd.Parameters.Add("@Email", SqlDbType.VarChar);  
  135.                 cmd.Parameters.Add("@Phone", SqlDbType.VarChar);  
  136.                 cmd.Parameters.Add("@Address", SqlDbType.VarChar);  
  137.                 cmd.Parameters.Add("@IMAGEs", SqlDbType.VarBinary);  
  138.                 cmd.Parameters["@std_ID"].Value = std_ID;  
  139.                 cmd.Parameters["@StudentName"].Value = StudentName;  
  140.                 cmd.Parameters["@Email"].Value = Email;  
  141.                 cmd.Parameters["@Phone"].Value = Phone;  
  142.                 cmd.Parameters["@Address"].Value = Address;  
  143.                 if (IMAGEs == null)  
  144.                 {  
  145.                     cmd.Parameters["@IMAGEs"].Value = DBNull.Value;  
  146.                 }  
  147.                 else  
  148.                 {  
  149.                     cmd.Parameters["@IMAGEs"].Value = IMAGEs;  
  150.                 }  
  151.                 //Execute command  
  152.                 SqlDataAdapter da = new SqlDataAdapter(cmd);  
  153.                 da.Fill(ds);  
  154.                 //close connection  
  155.                 CloseConnection();  
  156.             }  
  157.             return ds;  
  158.         }#  
  159.         endregion# region Write Log Message to textFile  
  160.         public void writeLogMessage(String logMessage)  
  161.         {  
  162.             string path = Application.StartupPath + @ "\LogFile.txt";  
  163.             if (!File.Exists(path))  
  164.             {  
  165.                 using(StreamWriter tw = File.CreateText(path))  
  166.                 {  
  167.                     tw.WriteLine(logMessage);  
  168.                     tw.Close();  
  169.                 }  
  170.             }  
  171.             else  
  172.             {  
  173.                 StreamWriter tr = new StreamWriter(path);  
  174.                 tr.WriteLine(logMessage);  
  175.                 tr.Close();  
  176.             }  
  177.         }#  
  178.         endregion# region DataTable  
  179.         for select result and  
  180.         return as DataTable  
  181.             //for select result and return as DataTable  
  182.         public DataSet SP_Dataset_return(String ProcName, params SqlParameter[] commandParameters)  
  183.         {  
  184.             DataSet ds = new DataSet();  
  185.             //open connection  
  186.             if (OpenConnection() == true)  
  187.             {  
  188.                 //for Select Query   
  189.                 SqlCommand cmdSel = new SqlCommand(ProcName, connection);  
  190.                 cmdSel.CommandType = CommandType.StoredProcedure;  
  191.                 // Assign the provided values to these parameters based on parameter order  
  192.                 AssignParameterValues(commandParameters, commandParameters);  
  193.                 AttachParameters(cmdSel, commandParameters);  
  194.                 SqlDataAdapter da = new SqlDataAdapter(cmdSel);  
  195.                 da.Fill(ds);  
  196.                 //close connection  
  197.                 CloseConnection();  
  198.             }  
  199.             return ds;  
  200.         }  
  201.         private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)  
  202.         {  
  203.             if (command == nullthrow new ArgumentNullException("command");  
  204.             if (commandParameters != null)  
  205.             {  
  206.                 foreach(SqlParameter p in commandParameters)  
  207.                 {  
  208.                     if (p != null)  
  209.                     {  
  210.                         // Check for derived output value with no value assigned  
  211.                         if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))  
  212.                         {  
  213.                             p.Value = DBNull.Value;  
  214.                         }  
  215.                         command.Parameters.Add(p);  
  216.                     }  
  217.                 }  
  218.             }  
  219.         }  
  220.         private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)  
  221.         {  
  222.             if ((commandParameters == null) || (parameterValues == null))  
  223.             {  
  224.                 // Do nothing if we get no data  
  225.                 return;  
  226.             }  
  227.             // We must have the same number of values as we pave parameters to put them in  
  228.             if (commandParameters.Length != parameterValues.Length)  
  229.             {  
  230.                 throw new ArgumentException("Parameter count does not match Parameter Value count.");  
  231.             }  
  232.             // Iterate through the SqlParameters, assigning the values from the corresponding position in the   
  233.             // value array  
  234.             for (int i = 0, j = commandParameters.Length; i < j; i++)  
  235.             {  
  236.                 // If the current array value derives from IDbDataParameter, then assign its Value property  
  237.                 if (parameterValues[i] is IDbDataParameter)  
  238.                 {  
  239.                     IDbDataParameter paramInstance = (IDbDataParameter) parameterValues[i];  
  240.                     if (paramInstance.Value == null)  
  241.                     {  
  242.                         commandParameters[i].Value = DBNull.Value;  
  243.                     }  
  244.                     else  
  245.                     {  
  246.                         commandParameters[i].Value = paramInstance.Value;  
  247.                     }  
  248.                 }  
  249.                 else if (parameterValues[i] == null)  
  250.                 {  
  251.                     commandParameters[i].Value = DBNull.Value;  
  252.                 }  
  253.                 else  
  254.                 {  
  255.                     commandParameters[i].Value = parameterValues[i];  
  256.                 }  
  257.             }  
  258.         }#  
  259.         endregion  
  260.     }  
  261. }  
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.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Data;  
  6. using System.Data.SqlClient;  
  7. using System.IO;  
  8. using System.Windows.Forms;  
  9. /// <summary>  
  10. /// Author : Shanu  
  11. /// Create date : 2015-12-01  
  12. /// Description : Biz Class  
  13. /// Latest  
  14. /// Modifier :   
  15. /// Modify date :   
  16. /// </summary>  
  17. namespace DatagridViewCRUD.Helper  
  18. {  
  19.     class BizClass  
  20.     {  
  21.         DatagridViewCRUD.Helper.SQLDALClass objDAL = new DatagridViewCRUD.Helper.SQLDALClass();  
  22.         //All Business Method here  
  23.         #  
  24.         region ALL Business method here  
  25.         public DataSet SelectList(String SP_NAME, SortedDictionary < stringstring > sd)  
  26.             {  
  27.                 try  
  28.                 {  
  29.                     return objDAL.SP_Dataset_return(SP_NAME, GetSdParameter(sd));  
  30.                 }  
  31.                 catch (Exception ex)  
  32.                 {  
  33.                     throw ex;  
  34.                 }  
  35.             }  
  36.             // Insert  
  37.         public DataSet SP_student_ImageInsert(String SP_NAME, string StudentName, string Email, string Phone, string Address, byte[] IMAGEs)  
  38.             {  
  39.                 try  
  40.                 {  
  41.                     return objDAL.SP_Student_ImageInsert(SP_NAME, StudentName, Email, Phone, Address, IMAGEs);  
  42.                 }  
  43.                 catch (Exception ex)  
  44.                 {  
  45.                     throw ex;  
  46.                 }  
  47.             }  
  48.             // EDIT  
  49.         public DataSet SP_student_ImageEdit(String SP_NAME, int std_ID, string StudentName, string Email, string Phone, string Address, byte[] IMAGEs)  
  50.         {  
  51.             try  
  52.             {  
  53.                 return objDAL.SP_Student_ImageEdit(SP_NAME, std_ID, StudentName, Email, Phone, Address, IMAGEs);  
  54.             }  
  55.             catch (Exception ex)  
  56.             {  
  57.                 throw ex;  
  58.             }  
  59.         }#  
  60.         endregion# region Methods Parameter  
  61.         /// <summary>  
  62.         /// This method Sorted-Dictionary key values to an array of SqlParameters  
  63.         /// </summary>  
  64.         public static SqlParameter[] GetSdParameter(SortedDictionary < stringstring > sortedDictionary)  
  65.         {  
  66.             SqlParameter[] paramArray = new SqlParameter[]  
  67.             {};  
  68.             foreach(string key in sortedDictionary.Keys)  
  69.             {  
  70.                 AddParameter(ref paramArray, new SqlParameter(key, sortedDictionary[key]));  
  71.             }  
  72.             return paramArray;  
  73.         }  
  74.         public static void AddParameter(ref SqlParameter[] paramArray, string parameterName, object parameterValue)  
  75.         {  
  76.             SqlParameter parameter = new SqlParameter(parameterName, parameterValue);  
  77.             AddParameter(ref paramArray, parameter);  
  78.         }  
  79.         public static void AddParameter(ref SqlParameter[] paramArray, string parameterName, object parameterValue, object parameterNull)  
  80.         {  
  81.             SqlParameter parameter = new SqlParameter();  
  82.             parameter.ParameterName = parameterName;  
  83.             if (parameterValue.ToString() == parameterNull.ToString()) parameter.Value = DBNull.Value;  
  84.             else parameter.Value = parameterValue;  
  85.             AddParameter(ref paramArray, parameter);  
  86.         }  
  87.         public static void AddParameter(ref SqlParameter[] paramArray, string parameterName, SqlDbType dbType, object parameterValue)  
  88.         {  
  89.             SqlParameter parameter = new SqlParameter(parameterName, dbType);  
  90.             parameter.Value = parameterValue;  
  91.             AddParameter(ref paramArray, parameter);  
  92.         }  
  93.         public static void AddParameter(ref SqlParameter[] paramArray, string parameterName, SqlDbType dbType, ParameterDirection direction, object parameterValue)  
  94.         {  
  95.             SqlParameter parameter = new SqlParameter(parameterName, dbType);  
  96.             parameter.Value = parameterValue;  
  97.             parameter.Direction = direction;  
  98.             AddParameter(ref paramArray, parameter);  
  99.         }  
  100.         public static void AddParameter(ref SqlParameter[] paramArray, params SqlParameter[] newParameters)  
  101.         {  
  102.             SqlParameter[] newArray = Array.CreateInstance(typeof (SqlParameter), paramArray.Length + newParameters.Length) as SqlParameter[];  
  103.             paramArray.CopyTo(newArray, 0);  
  104.             newParameters.CopyTo(newArray, paramArray.Length);  
  105.             paramArray = newArray;  
  106.         }#  
  107.         endregion  
  108.     }  
  109. }  
  110. 3. Design Your Form * Design Your Form  
  111. Design your form with search fields and add a panel named as pnlGrid.  
  112. We will be adding the dynamic DataGridView to this panel.  
  113. In form load we will design the DataGridView using our Helper class and add the DataGridView to the Panel.  
  114. private void Form1_Load(object sender, EventArgs e)  
  115. {  
  116.     try  
  117.     {  
  118.         MasterGrid_Initialize();  
  119.     }  
  120.     catch (Exception ex)  
  121.     {}  
  122. }  
  123. public void MasterGrid_Initialize()  
  124. {  
  125.     Helper.ShanuDGVHelper.Layouts(Master_shanuDGV, Color.White, Color.White, Color.White, false, Color.SteelBlue, falsefalsefalse, Color.White, 46, 60, "small");  
  126.     //Set Height,width and add panel to your selected control  
  127.     Helper.ShanuDGVHelper.Generategrid(Master_shanuDGV, pnlGrid, 1000, 600, 10, 10);  
  128.     // Color Image Column creation  
  129.     Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageEditColumn, "Edit""Edit""Edit"true, 60, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  130.     // Color Image Column creation  
  131.     Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageDelteColumn, "Delete""Delete""Delete"true, 60, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  132.     // Color Image Column creation  
  133.     Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageBoundcolumn, "StdImage""Image""Image"true, 60, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  134.     //// BoundColumn creation  
  135.     Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn, "StdNO""StdNO""StdNO"true, 80, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  136.     //// BoundColumn creation  
  137.     Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn, "StdName""StdName""StdName"true, 180, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  138.     //// BoundColumn creation  
  139.     Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn, "Email""Email""Email"true, 180, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  140.     //// BoundColumn creation  
  141.     Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn, "Phone""Phone""Phone"true, 180, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  142.     //// BoundColumn creation  
  143.     Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn, "Address""Address""Address"true, 180, DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter, Color.Transparent, null"""", Color.Black);  
  144.     bindData();  
  145.     Master_shanuDGV.CellFormatting += new DataGridViewCellFormattingEventHandler(MasterDGVs_CellFormatting);  
  146.     Master_shanuDGV.SelectionChanged += new EventHandler(Master_shanuDGV_SelectionChanged);  
  147.     Master_shanuDGV.CellContentClick += new System.Windows.Forms.DataGridViewCellEventHandler(Master_shanuDGV_CellContentClick);  
  148. }  
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.

Circle Image to Display in Grid
  1. #region Image Colukmn  
  2. public static Image MakeCircleImage(Image img)  
  3. {  
  4.     Bitmap bmp = new Bitmap(img.Width, img.Height);  
  5.     using(GraphicsPath gpImg = new GraphicsPath())  
  6.     {  
  7.         gpImg.AddEllipse(0, 0, img.Width, img.Height);  
  8.         using(Graphics grp = Graphics.FromImage(bmp))  
  9.         {  
  10.             grp.Clear(Color.White);  
  11.             grp.SetClip(gpImg);  
  12.             grp.DrawImage(img, Point.Empty);  
  13.         }  
  14.     }  
  15.     return bmp;  
  16. }  
  17. void MasterDGVs_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)  
  18. {  
  19.     try  
  20.     {  
  21.         if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "StdImage")  
  22.         {  
  23.             if (ds.Tables[0].Rows[e.RowIndex]["StdImage"] != "" && ds.Tables[0].Rows[e.RowIndex]["StdImage"] != DBNull.Value)  
  24.             {  
  25.                 byte[] bits = new byte[0];  
  26.                 bits = (byte[]) ds.Tables[0].Rows[e.RowIndex]["StdImage"];  
  27.                 MemoryStream ms = new MemoryStream(bits);  
  28.                 System.Drawing.Image imgSave = System.Drawing.Image.FromStream(ms);  
  29.                 e.Value = MakeCircleImage(imgSave);  
  30.             }  
  31.             else  
  32.             {  
  33.                 System.Drawing.Image imgSave = (Image) DatagridViewCRUD.Properties.Resources.gridUserImage;  
  34.                 e.Value = MakeCircleImage(imgSave);  
  35.             }  
  36.         }  
  37.     }  
  38.     catch (Exception ex)  
  39.     {}  
  40. }  
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.
  1. private void btnSearch_Click(object sender, EventArgs e)  
  2. {  
  3.     bindData();  
  4. }  
  5. private void bindData()  
  6. {  
  7.     try  
  8.     {  
  9.         // Bind data to DGV.  
  10.         SortedDictionary < stringstring > sd = new SortedDictionary < stringstring > ()  
  11.         {};  
  12.         sd.Add("@StudentName", txtName.Text.Trim());  
  13.         ds = bizObj.SelectList("USP_Student_Select", sd);  
  14.         Master_shanuDGV.DataSource = null;  
  15.         if (ds.Tables[0].Rows.Count > 0)  
  16.         {  
  17.             Master_shanuDGV.DataSource = ds.Tables[0];  
  18.         }  
  19.     }  
  20.     catch (Exception ex)  
  21.     {}  
  22. }  
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.

Insert Student Details

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.
  1. private void btnStaffAdd_Click(object sender, EventArgs e)  
  2. {  
  3.    frmSudentAdd obj = new frmSudentAdd("0");  
  4.    obj.ShowDialog();  
  5.    bindData();  
  6. }  
In frmSudentAdd form constructor we will get the Students ID passed from Main form and stored in local variable.
  1. public frmSudentAdd(string StudentID)  
  2. {  
  3.    InitializeComponent();  
  4.    StudentIDS = StudentID;  
  5. }  
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.
  1. private void frmSudentAdd_Load(object sender, EventArgs e)  
  2. {  
  3.     try  
  4.     {  
  5.         isImageCaptuerd = false;  
  6.         // setFont();  
  7.         if (StudentIDS != "0")  
  8.         {  
  9.             displayVisitorDetails();  
  10.         }  
  11.     }  
  12.     catch (Exception ex)  
  13.     {}  
  14. }  
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.
  1. private void displayVisitorDetails()  
  2. {  
  3.     // lblUserType.Text = VisitorTypes;  
  4.     try  
  5.     {  
  6.         SortedDictionary < stringstring > sd = new SortedDictionary < stringstring > ()  
  7.         {};  
  8.         //sd.Add("@searchType", VisitorTypes);  
  9.         sd.Add("@std_ID", StudentIDS);  
  10.         DataSet ds = new DataSet();  
  11.         ds = bizObj.SelectList("USP_StudentID_Select", sd);  
  12.         if (ds.Tables[0].Rows.Count > 0)  
  13.         {  
  14.             txtStudentID.Text = ds.Tables[0].Rows[0]["StdNO"].ToString();  
  15.             txtstdName.Text = ds.Tables[0].Rows[0]["StdName"].ToString();  
  16.             txtEmail.Text = ds.Tables[0].Rows[0]["Email"].ToString();  
  17.             txtphone.Text = ds.Tables[0].Rows[0]["Phone"].ToString();  
  18.             txtAddress.Text = ds.Tables[0].Rows[0]["Address"].ToString();  
  19.             if (ds.Tables[0].Rows[0]["StdImage"].ToString() != "")  
  20.             {  
  21.                 byte[] bits = new byte[0];  
  22.                 bits = (byte[]) ds.Tables[0].Rows[0]["StdImage"];  
  23.                 MemoryStream ms = new MemoryStream(bits);  
  24.                 this.picImage.Image = System.Drawing.Bitmap.FromStream(ms);  
  25.                 ms = null;  
  26.             }  
  27.         }  
  28.     }  
  29.     catch (Exception ex)  
  30.     {}  
  31. }  
Upload Image: In upload Image Button click select the image file using OpenFileDialog and add the selected image to the PictureBox.
  1. private void btnCaptuer_Click(object sender, EventArgs e)  
  2. {  
  3.     try  
  4.     {  
  5.         isImageCaptuerd = false;  
  6.         OpenFileDialog ofd = new OpenFileDialog();  
  7.         ofd.Filter = "JPEG Files (*.jpeg)|*.jpeg|PNG Files (*.png)|*.png|JPG Files (*.jpg)|*.jpg|GIF Files (*.gif)|*.gif";  
  8.         ofd.Title = "Please Upload Image";  
  9.         if (ofd.ShowDialog() == DialogResult.OK)  
  10.         {  
  11.             isImageCaptuerd = true;  
  12.             picImage.Image = Image.FromFile(ofd.FileName);  
  13.         }  
  14.     }  
  15.     catch (Exception ex)  
  16.     {}  
  17. }  
Upload Image
Save Button Click

In Save Button click first we check for New User or Edit the existing user. For New user call the following,
  1. private void btnSave_Click(object sender, EventArgs e)  
  2. {  
  3.     if (StudentIDS != "0")  
  4.     {  
  5.         EditStaffDetails();  
  6.     }  
  7.     else  
  8.     {  
  9.         AddNewStaffDetails();  
  10.     }  
  11. }  
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.
  1. private void AddNewStaffDetails()  
  2. {  
  3.     try  
  4.     {  
  5.         byte[] ImageData = null;  
  6.         string result = "";  
  7.         if (isImageCaptuerd == true)  
  8.         {  
  9.             try  
  10.             {  
  11.                 if (picImage.Image != null)  
  12.                 {  
  13.                     ImageData = imgToByteArray(picImage.Image);  
  14.                 }  
  15.             }  
  16.             catch (Exception ex)  
  17.             {}  
  18.         }  
  19.         SortedDictionary < stringstring > sd = new SortedDictionary < stringstring > ()  
  20.         {};  
  21.         DataSet ds = new DataSet();  
  22.         ds = bizObj.SP_student_ImageInsert("USP_Student_Insert", txtstdName.Text.Trim(), txtEmail.Text.Trim(), txtphone.Text.Trim(), txtAddress.Text.Trim(), ImageData);  
  23.         if (ds.Tables[0].Rows.Count > 0)  
  24.         {  
  25.             result = ds.Tables[0].Rows[0][0].ToString();  
  26.             if (result == "Inserted")  
  27.             {  
  28.                 MessageBox.Show("Student Added Successful, Thank You!""Successfull", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  29.                 this.Close();  
  30.             }  
  31.             else  
  32.             {  
  33.                 MessageBox.Show(result, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
  34.             }  
  35.         }  
  36.     }  
  37.     catch (Exception ex)  
  38.     {}  
  39. }  
Add New Student

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.
  1. private void Master_shanuDGV_CellContentClick(object sender, DataGridViewCellEventArgs e)  
  2.   
  3. if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "Edit")  
  4. {  
  5.     try  
  6.     {  
  7.         string studentID = ds.Tables[0].Rows[e.RowIndex]["StdNO"].ToString();  
  8.         frmSudentAdd obj = new frmSudentAdd(studentID);  
  9.         obj.ShowDialog();  
  10.         bindData();  
  11.     }  
  12.     catch (Exception ex)  
  13.     {  
  14.     }  
  15.   
  16. }  
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.
  1. private void EditStaffDetails()  
  2. {  
  3.     try  
  4.     {  
  5.         byte[] ImageData = null;  
  6.         string result = "";  
  7.       
  8.   
  9.         if(picImage.Image!=null)  
  10.         {  
  11.             try  
  12.             {  
  13.             ImageData = imgToByteArray(picImage.Image);  
  14.             }  
  15.             catch (Exception ex)  
  16.             {  
  17.             }  
  18.         }  
  19.   
  20.         SortedDictionary<stringstring> sd = new SortedDictionary<stringstring>() { };  
  21.   
  22.         DataSet ds = new DataSet();  
  23.         int StudentID = Convert.ToInt32(StudentIDS);  
  24.   
  25.         ds = bizObj.SP_student_ImageEdit("USP_Student_Update", StudentID,txtstdName.Text.Trim(),  
  26.                                                              txtEmail.Text.Trim(),  
  27.                                                              txtphone.Text.Trim(),  
  28.                                                              txtAddress.Text.Trim(),  
  29.                                                              ImageData);  
  30.   
  31.         if (ds.Tables[0].Rows.Count > 0)  
  32.         {  
  33.             result = ds.Tables[0].Rows[0][0].ToString();  
  34.   
  35.             if (result == "Updated")  
  36.             {  
  37.                 MessageBox.Show("Student Updated Successful, Thank You!""Successfull", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  38.                 this.Close();  
  39.             }  
  40.             else  
  41.             {  
  42.                 MessageBox.Show(result, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
  43.             }  
  44.         }  
  45.   
  46.     }  
  47.     catch (Exception ex)  
  48.     {  
  49.     }  
  50.   
  51. }  
  52.   
  53. // Picbox to Byte Convert  
  54. public byte[] imgToByteArray(Image img)  
  55. {  
  56.     using (MemoryStream mStream = new MemoryStream())  
  57.     {  
  58.         img.Save(mStream, img.RawFormat);  
  59.         return mStream.ToArray();  
  60.     }  
  61. }  
edit Student Details

7. Delete Student Details


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. 
  1. private void Master_shanuDGV_CellContentClick(object sender, DataGridViewCellEventArgs e)  
  2. {  
  3.     if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "Edit")  
  4.     {  
  5.         try  
  6.         {  
  7.             string studentID = ds.Tables[0].Rows[e.RowIndex]["StdNO"].ToString();  
  8.             frmSudentAdd obj = new frmSudentAdd(studentID);  
  9.             obj.ShowDialog();  
  10.             bindData();  
  11.         }  
  12.         catch (Exception ex)  
  13.         {}  
  14.     }  
  15.     else if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "Delete")  
  16.     {  
  17.         try  
  18.         {  
  19.             string studentID = ds.Tables[0].Rows[e.RowIndex]["StdNO"].ToString();  
  20.             if (MessageBox.Show("Are You Sure to Delete Student Details ?""Delete Student", MessageBoxButtons.YesNo) == DialogResult.Yes)  
  21.             {  
  22.                 SortedDictionary < stringstring > sd = new SortedDictionary < stringstring > ()  
  23.                 {};  
  24.                 sd.Add("@std_ID", studentID);  
  25.                 DataSet ds1 = new DataSet();  
  26.                 ds1 = bizObj.SelectList("USP_Student_Delete", sd);  
  27.                 if (ds1.Tables[0].Rows.Count > 0)  
  28.                 {  
  29.                     string result = ds1.Tables[0].Rows[0][0].ToString();  
  30.                     if (result == "Deleted")  
  31.                     {  
  32.                         MessageBox.Show("Student Deleted Successful, Thank You!""Successfull", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  33.                         bindData();  
  34.                     }  
  35.                 }  
  36.             }  
  37.         }  
  38.         catch (Exception ex)  
  39.         {}  
  40.     }  
  41. }  
Student Detail

 

Next Recommended Readings