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

By FoxLearn 12/2/2024 3:04:05 PM   7.65K
How to Insert, Update, Delete and View Student Profile data from SQL database using NPoco ORM, Stored Procedure, Metro Framework 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.

install metro framework

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.

insert update delete view data using sql database

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