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
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
- How to Insert Update Delete data in Database from DataGridView in C#
- How to Insert Update Delete View data in SQL Server using 3 Tiers in C#
- How to Insert Update Delete Select in SQL Server in C#
- How to Insert Update Delete Search data from local database in C#
- How to Insert Update Delete View and Search data from SQL Server in C#
- How to Insert Update Delete View and Search data from MS Access in C#
- Insert Update Delete and View data from SQL Database using ORM Lite in C#
- How to Insert Update Delete Search Records in C#