Insert Update Delete and View data from SQL Database using ORM Lite in C#

Open your 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

In your Visual Studio project, go to Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution.

Search metro framework, orm lite -> Install

ORM Lite is a lightweight ORM (Object-Relational Mapping) framework for .NET that makes it easy to interact with databases.

Metro Framework is a modern UI toolkit for Windows Forms that gives your application a Metro-style interface.

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

Create a simple form that uses Metro UI components to interact with the database.

Drag and drop the MetroGrid, MetroTextBox, MetroLabel and MetroButton controls from the Visual Toolbox onto your form designer, then design your metro form as shown below.

Create a student class to map data return from sql server. This class will represent the data structure of the Student table.

[Alias("Students")]
public class Student
{
    [AutoIncrement]
    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; }
}

Create a database with a Students table like this:

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]

Next, Create an EntityState to manage objects state

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

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>

Add code to handle your form as shown below

How to add an image to picturebox control in C#?

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 to picture box
            pic.Image = Image.FromFile(ofd.FileName);
            Student obj = studentBindingSource.Current as Student;
            if (obj != null)
                obj.ImageUrl = ofd.FileName;
        }
    }
}

Add a Form_Load event handler as shown below.

private void Form1_Load(object sender, EventArgs e)
{
    try
    {
        dbFactory = new OrmLiteConnectionFactory(ConfigurationManager.ConnectionStrings["cn"].ConnectionString, SqlServerDialect.Provider);
        using (var db = dbFactory.Open())
        {
            //Retrieve data from sql database
            studentBindingSource.DataSource = db.Select<Student>();
            Student obj = studentBindingSource.Current as Student;
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.ImageUrl))
                    pic.Image = Image.FromFile(obj.ImageUrl);
            }
        }
    }
    catch (Exception ex)
    {
        MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    pContainer.Enabled = false;
}

How to insert a new student profile in C#?

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

To update a student profile, you can rely on the object status, and insert or update it to the database.

private void btnSave_Click(object sender, EventArgs e)
{
    try
    {
        studentBindingSource.EndEdit();
        Student obj = studentBindingSource.Current as Student;
        if (obj != null)
        {
            using (var db = dbFactory.Open())
            {
                //Save data to sql database
                if (objState == EntityState.Added)
                    obj.StudentID = (int)db.Insert<Student>(obj, true);
                else if (objState == EntityState.Changed)
                    db.Update<Student>(obj);
                metroGrid.Refresh();
                pContainer.Enabled = false;
                objState = EntityState.Unchanged;
            }
        }
    }
    catch (Exception ex)
    {
        MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

How to delete a student profile in C#?

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)
    {
        Student obj = studentBindingSource.Current as Student;
        if (obj != null)
        {
            using (var db = dbFactory.Open())
            {
                //Delete data from sql database, then remove data from data source
                db.Delete<Student>(obj);
                studentBindingSource.RemoveCurrent();
                pContainer.Enabled = false;
                pic.Image = null;
                objState = EntityState.Unchanged;
            }
        }
    }
}

Example form implementation

using ServiceStack.OrmLite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
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
    {
        EntityState objState = EntityState.Unchanged;

        public Form1()
        {
            InitializeComponent();
        }       

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

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

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

VIDEO TUTORIAL