How to Insert Update Delete and View data from SQL Database using NPoco ORM in C#

This guide walks you through creating a simple student management system using C#, SQL Server, and NPoco ORM.

How to Insert Update Delete and View data from SQL Database using NPoco ORM in C#?

Open Visual Studio, then click 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

To get started, install the NPoco NuGet package by right-clicking on your project select Manage NuGet Packages -> Search npoco -> Install

Next, Search for 'MetroModernUI' and Install it.

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

NPoco is a lightweight Micro-ORM designed for simplicity and ease of use. It supports multiple databases, including SQL Server, MySQL, PostgreSQL, Oracle, SQLite, and SQL Compact Edition (SqlCE)

Design a metro form as shown below.

First, create a new database named dbstudent and add a Students table

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]

This table stores essential details like the student's name, birthday, gender, contact info, and an image URL.

Create a stored procedure to insert, update data to student table as shown below.

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
   
go
   
create procedure [dbo].[sp_Students_Insert]
(
    @StudentID int output,
    @FullName nvarchar(100),
    @Birthday varchar(10),
    @Gender bit,
    @Email varchar(100),
    @Address nvarchar(250),
    @ImageUrl varchar(250)
)
as
    insert into Students(FullName, Birthday, Gender, Email, [Address], ImageUrl)
    values(@FullName, @Birthday, @Gender, @Email, @Address, @ImageUrl)
    set @StudentID = SCOPE_IDENTITY()

Create an EntityState to track object states (e.g., new, modified, or deleted):

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

In your project's App.config file, add the following connection string to connect to the database.

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

Define the Student class to represent your database table.

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

The Form1 constructor initializes the form and sets the default object state:

// Set default object state
EntityState objState = EntityState.Unchanged;

On form load, retrieve data from the database and bind it to the UI:

private void Form1_Load(object sender, EventArgs e)
{
    try
    {
        using (IDatabase db = new Database("cn"))
        {
            //Get students, then add students to binding source
            studentBindingSource.DataSource = db.Fetch<Student>();
            Student obj = studentBindingSource.Current as Student;
            if (obj != null)
            {
                // Load image if URL exists
                if (!string.IsNullOrEmpty(obj.ImageUrl))
                    pic.Image = Image.FromFile(obj.ImageUrl);
            }
            pContainer.Enabled = false;
        }
    }
    catch (Exception ex)
    {
        MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Set the state to EntityState.Added, reset the form, and prepare for data entry:

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

Enable editing mode by setting the state to EntityState.Changed:

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

Confirm deletion and remove the record:

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

Save new or updated data based on the object's state:

private void btnSave_Click(object sender, EventArgs e)
{
    try
    {
        studentBindingSource.EndEdit();
        Student obj = studentBindingSource.Current as Student;
        if (obj != null)
        {
            using (IDatabase db = new Database("cn"))
            {
                if (objState == EntityState.Added)
                {
                    //Execute store to insert data, then get output parameter
                    SqlParameter pId = new SqlParameter("@StudentID", SqlDbType.Int) { Direction = ParameterDirection.Output };
                    Sql result = Sql.Builder.Append("sp_Students_Insert @StudentID out, @FullName, @Birthday, @Gender, @Email, @Address, @ImageUrl", new
                    {
                        StudentID = pId,
                        FullName = obj.FullName,
                        Birthday = obj.Birthday,
                        Gender = obj.Gender,
                        Email = obj.Email,
                        Address = obj.Address,
                        ImageUrl = obj.ImageUrl
                    });
                    db.Execute(result);
                    obj.StudentID = (int)pId.Value;
                }
                else if (objState == EntityState.Changed)
                {
                    //Execute stored procedure to update database
                    db.Execute("sp_Students_Update", CommandType.StoredProcedure, new { StudentID = obj.StudentID, FullName = obj.FullName, Gender = obj.Gender, Email = obj.Email, Address = obj.Address, Birthday = obj.Birthday, ImageUrl = obj.ImageUrl });
                }
                metroGrid.Refresh();
                pContainer.Enabled = false;
                objState = EntityState.Unchanged;
            }
        }
    }
    catch (Exception ex)
    {
        MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Handle image upload and display using the OpenFileDialog:

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)
        {
            // Load image from file to picturebox control
            pic.Image = Image.FromFile(ofd.FileName);
            // Get current student, then set image url
            Student obj = studentBindingSource.Current as Student;
            if (obj != null)
                obj.ImageUrl = ofd.FileName;
        }
    }
}

Implement the Windows Forms Application

using NPoco;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

namespace CRUD
{
    public partial class Form1 : MetroFramework.Forms.MetroForm
    {

        public Form1()
        {
            InitializeComponent();
        }       

        private void btnCancel_Click(object sender, EventArgs e)
        {
            // Disable the input container to prevent further edits.
            pContainer.Enabled = false;
            // Reset the bindings on the form to undo any changes to the data.
            studentBindingSource.ResetBindings(true);
            // Reload the form to restore the original data and UI state.
            this.Form1_Load(sender, e);
        }

        private void metroGrid_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            // Get the current student object selected in the data binding source.
            Student obj = studentBindingSource.Current as Student;
            // Check if the current student object is not null.
            if (obj != null)
            {
                // If the student has an image URL, display the image in the PictureBox control.
                if (!string.IsNullOrEmpty(obj.ImageUrl))
                    pic.Image = Image.FromFile(obj.ImageUrl);
            }
        }
    }
}

VIDEO TUTORIAL