Insert Update Delete and View data from SQL Database using ORM Lite in C#
By FoxLearn 11/11/2024 3:03:58 PM 6.36K
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
- 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#
- How to Insert Update Delete and View data from SQL Database using NPoco ORM in C#
- How to Insert Update Delete Search Records in C#
- How to Insert Update Delete From Database in VB.NET
- How to Insert Update Delete and View data from SQL Database in C# using ADO.NET
- How to Insert Update Delete View data from SQL Database in C#
- Windows Forms: Insert Update Delete data in Database from DataGridView in C#