Windows Forms: Insert Update Delete and View data from SQL Database using ADO.Net in C#

How to Insert Update Delete and View Student Profile in C# using ADO.Net, Metro Framework

Step 1Click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "CRUD" and then click OK

crud-ado-netStep 2: Right click on your project select Manage NuGet Packages -> Search metro framework, ado.net -> Install

install metro framework

If you don't see the metro framework in your toolbox, you can view How to download and install metro framework

Step 3: Design metro form as below

insert update delete view data using sql databaseStep 4: Create a student class to map data return from sql server

public class Student
{
    public int StudentID { get; set; }
    public string FullName { get; set; }
    public string Birthday { get; set; }
    public string Email { get; set; }
    public string Address { get; set; }
    public string ImageUrl { get; set; }
    public bool Gender { get; set; }
}

Step 5: You should create a new database, then add a student table to your database

CREATE TABLE [dbo].[Students](
	[StudentID] [int] IDENTITY(1,1) NOT NULL,
	[FullName] [nvarchar](100) NULL,
	[Birthday] [varchar](10) NULL,
	[Gender] [bit] NULL,
	[Email] [varchar](100) NULL,
	[Address] [nvarchar](250) NULL,
	[ImageUrl] [varchar](250) NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
	[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Create a stored procedure to update student table

create procedure [dbo].[sp_Students_Update]
(
	@StudentID int output,
	@FullName nvarchar(100),
	@Birthday varchar(10),
	@Gender bit,
	@Email varchar(100),
	@Address nvarchar(250),
	@ImageUrl varchar(250)
)
as
	update Students set FullName = @FullName, Birthday = @Birthday, Gender = @Gender, Email = @Email, [Address] = @Address, ImageUrl = @ImageUrl
	where StudentID = @StudentID

Step 6: Create an EntityState to manage objects state

public enum EntityState
{
    Unchanged,
    Added,
    Changed,
    Deleted
}

Step 7: Add a connection string to the App.config file

<configuration>
  <connectionStrings>
    <add name = "cn" connectionString="Data Source=.;Initial Catalog=dbstudent;User ID=sa;Password=123@qaz;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Step 8: Add code to handle your form as below

using Net.Code.ADONet;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CRUD
{
    public partial class Form1 : MetroFramework.Forms.MetroForm
    {
        //Set default state
        EntityState objState = EntityState.Unchanged;
        public Form1()
        {
            InitializeComponent();
        }

        //Select an image
        private void btnBrowse_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "JPEG|*.jpg|PNG|*.png", ValidateNames = true })
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    pic.Image = Image.FromFile(ofd.FileName);
                    //Get current object
                    Student obj = studentBindingSource.Current as Student;
                    if (obj != null)
                        obj.ImageUrl = ofd.FileName;
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //Connect to sql database
            using(var db = Db.FromConfig("cn"))
            {
                studentBindingSource.DataSource = db.Sql("select *from Students").AsEnumerable<Student>();
            }
            pContainer.Enabled = false;
            Student obj = studentBindingSource.Current as Student;
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.ImageUrl))
                    pic.Image = Image.FromFile(obj.ImageUrl);//Load image to PictureBox
            }
        }

        //Add new object to binding source
        private void btnAdd_Click(object sender, EventArgs e)
        {
            objState = EntityState.Added;
            pic.Image = null;
            pContainer.Enabled = true;
            studentBindingSource.Add(new Student());
            studentBindingSource.MoveLast();
            txtFullName.Focus();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            objState = EntityState.Changed;
            pContainer.Enabled = true;
            txtFullName.Focus();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if(MetroFramework.MetroMessageBox.Show(this,"Are you sure want to delete this record?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                objState = EntityState.Deleted;
                try
                {
                    Student obj = studentBindingSource.Current as Student;
                    if (obj != null)
                    {
                        using(var db = Db.FromConfig("cn"))
                        {
                            db.Sql("delete from Students where StudentID = @StudentID").WithParameters(new { StudentID = obj.StudentID }).AsNonQuery();
                            studentBindingSource.RemoveCurrent();
                            pContainer.Enabled = false;
                            ClearInput();
                            objState = EntityState.Unchanged;
                        }
                    }
                }
                catch(Exception ex)
                {
                    MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            pContainer.Enabled = false;
            //Reset binding source
            studentBindingSource.ResetBindings(false);
            this.Form1_Load(sender, e);
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                studentBindingSource.EndEdit();
                //Get current object
                Student obj = studentBindingSource.Current as Student;
                if (obj != null)
                {
                    using (var db = Db.FromConfig("cn"))
                    {
                        if (objState == EntityState.Added)
                        {
                            //Execute sql insert query
                            obj.StudentID = db.Sql("insert into Students(FullName, Email, Gender, Address, ImageUrl, Birthday) values(@FullName, @Email, @Gender, @Address, @ImageUrl, @Birthday);select SCOPE_IDENTITY()").WithParameters(new { FullName = obj.FullName, Email = obj.Email, Address = obj.Address, ImageUrl = obj.ImageUrl, Gender = obj.Gender, Birthday = obj.Birthday }).AsScalar<int>();
                        }
                        else if (objState == EntityState.Changed)
                        {
                            //Execute stored procedure update
                            db.StoredProcedure("sp_Students_Update").WithParameters(new { StudentID = obj.StudentID, FullName = obj.FullName, Email = obj.Email, Address = obj.Address, ImageUrl = obj.ImageUrl, Gender = obj.Gender, Birthday = obj.Birthday }).AsNonQuery();
                        }
                        metroGrid.Refresh();
                        pContainer.Enabled = false;
                        objState = EntityState.Unchanged;
                    }
                }
            }
            catch (Exception ex)
            {
                MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void metroGrid_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            Student obj = studentBindingSource.Current as Student;//Get current object
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.ImageUrl))
                    pic.Image = Image.FromFile(obj.ImageUrl);//Load image to PictureBox
            }
        }
    }
}

VIDEO TUTORIALS

 

Related Posts