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

Step 1: 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

Step 2: Right click on your project select Manage NuGet Packages -> Search metro framework, orm lite -> Install

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

Step 4: Create a student class to map data return from sql server

[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; }
}

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]

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;[email protected];" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Step 8: Add code to handle your form as below

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();
        }

        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;
                }
            }
        }

        OrmLiteConnectionFactory dbFactory;

        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;
        }

        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();
        }

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

        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;
                    }
                }
            }
        }

        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);
        }

        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);
            }
        }
    }
}

VIDEO TUTORIALS