How to Insert Update Delete View data from SQL Database in C#

By Tan Lee Published on Jul 16, 2024  8.21K
To perform CRUD operations (Create, Read, Update, Delete) on student profile data from an SQL database using Dapper ORM, Stored Procedures, and Metro Framework in a C# Windows Forms Application, follow these steps.

Here's a step-by-step guide on how to Insert Update Delete View data from SQL Database in C#.

This is a basic simple to help you learn c# with windows forms and sql server, we will create a beatiful UI allows us insert, update, delete student record using Dapper ORM, Metro Framework, and stored procedures in C# to perform CRUD operations with a SQL database.

First, 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

Select Class Library, then create CRUD.Core and CRUD.DataAccess project, then right click on your project select Manage NuGet Packages -> Search metro framework, autofac -> Install

install metro framework

Metro Framework provides modern UI controls for WinForms applications. You can design your UI using Metro controls for a sleek and attractive appearance.

Next, Install Autofact

c# install autofac

Similarly, you need to install dapper to your project

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

Drag and drop the metro controls from your Visual Studio toolbox onto your form designer, then you can design a simple metro form as shown below.

insert update delete view data using sql database

Make sure you have a SQL Server database set up with the necessary tables and stored procedures for your application.

You can create a new database, then add a student table to your database by using sql script below.

1
2
3
4
5
6
7
8
9
10
11
12
13
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]

Create stored procedures for inserting, updating, deleting, and viewing data in your SQL Server database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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()

Define C# classes to represent your database tables. These classes will be used to map query results by adding a student class to CRUD.Core project

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace CRUD.Core
{
    public class Student
    {
        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 an interface to process your business

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace CRUD.Core
{
    public interface IStudentRepository
    {
        List<Student> GetAll();
        int Insert(Student obj);
        bool Update(Student obj);
        bool Delete(int studentId);
    }
}

Add a Helper class to CRUD.DataAccess, then add a reference to System.Configuration.dll

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace CRUD.DataAccess
{
    public class Helper
    {
        //Get connection string from app.config file
        public static string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
            }
        }
    }
}

Create a StudentRepository class, then implement IStudentRepository

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
using CRUD.Core;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using System.Data.SqlClient;
 
namespace CRUD.DataAccess
{
    public class StudentRepository : IStudentRepository
    {
        public bool Delete(int studentId)
        {
            using (IDbConnection db = new SqlConnection(Helper.ConnectionString))
            {
                if (db.State == ConnectionState.Closed)
                    db.Open();
                //Execute query to delete data from sql database
                int result = db.Execute("delete from Students where StudentID = @StudentID", new { StudentID = studentId }, commandType: CommandType.Text);
                return result != 0;
            }
        }
 
        public List<Student> GetAll()
        {
            using (IDbConnection db = new SqlConnection(Helper.ConnectionString))
            {
                if (db.State == ConnectionState.Closed)
                    db.Open();
                //Execute query to retrieve data from sql database
                return db.Query<Student>("select *from Students", commandType: CommandType.Text).ToList();               
            }
        }
 
        public int Insert(Student obj)
        {
            using (IDbConnection db = new SqlConnection(Helper.ConnectionString))
            {
                if (db.State == ConnectionState.Closed)
                    db.Open();
                DynamicParameters p = new DynamicParameters();
                p.Add("@StudentID", dbType: DbType.Int32, direction: ParameterDirection.Output);
                p.AddDynamicParams(new { FullName = obj.FullName, Email = obj.Email, Address = obj.Address, Gender = obj.Gender, Birthday = obj.Birthday, ImageUrl = obj.ImageUrl });
                //Execute stored procedure to insert data to sql database, then get output value
                db.Execute("sp_Students_Insert", p, commandType: CommandType.StoredProcedure);
                return p.Get<int>("@StudentID");
            }
        }
 
        public bool Update(Student obj)
        {
            using (IDbConnection db = new SqlConnection(Helper.ConnectionString))
            {
                if (db.State == ConnectionState.Closed)
                    db.Open();
                //Execute stored procedure to update data to sql database
                int result = db.Execute("sp_Students_Update", new { StudentID = obj.StudentID, FullName = obj.FullName, Email = obj.Email, Address = obj.Address, Gender = obj.Gender, Birthday = obj.Birthday, ImageUrl = obj.ImageUrl }, commandType: CommandType.StoredProcedure);
                return result != 0;
            }
        }
    }
}

Create an EntityState to CRUD project

1
2
3
4
5
6
7
public enum EntityState
{
    Unchanged,
    Added,
    Changed,
    Deleted
}

Create a MyService class to control your business

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
using Autofac;
using CRUD.Core;
using CRUD.DataAccess;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace CRUD
{
    public class MyService
    {
        static IContainer Container { get; set; }
        static MyService()
        {
            ContainerBuilder builder = new ContainerBuilder();
            //You can easily change business logic
            builder.RegisterType<StudentRepository>().As<IStudentRepository>();
            Container = builder.Build();
        }
 
        public static IStudentRepository Student
        {
            get
            {
                return Container.Resolve<IStudentRepository>();
            }
        }
    }
}

Define your database connection string in your app.config

1
2
3
4
5
<configuration>
  <connectionStrings>
    <add name = "cn" connectionString="Data Source=.;Initial Catalog=dbstudent;User ID=sa;Password=123@qaz;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Implement event handlers in your UI to trigger CRUD operations based on user actions.

For example, clicking a "Save" button can trigger an insert or update operation. Clicking a "Delete" button can trigger a delete operation, then display data retrieved from the database in appropriate UI elements.

Add code to handle your form as below

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Dapper;
using CRUD.Core;
 
namespace CRUD
{
    public partial class Form1 : MetroFramework.Forms.MetroForm
    {
        //Set default state
        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)
                {
                    pic.Image = Image.FromFile(ofd.FileName);
                    Student obj = studentBindingSource.Current as Student;
                    if (obj != null)
                        obj.ImageUrl = ofd.FileName;
                }
            }
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                studentBindingSource.DataSource = MyService.Student.GetAll();
                pContainer.Enabled = false;
                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);
            }
        }
 
        private void btnAdd_Click(object sender, EventArgs e)
        {
            objState = EntityState.Added;
            pic.Image = null;
            pContainer.Enabled = true;
            studentBindingSource.Add(new Student());
            studentBindingSource.MoveLast();
            txtFullName.Focus();
        }
 
        private void btnCancel_Click(object sender, EventArgs e)
        {
            pContainer.Enabled = false;
            studentBindingSource.ResetBindings(false);
            this.Form1_Load(sender, e);
        }
 
        private void btnEdit_Click(object sender, EventArgs e)
        {
            objState = EntityState.Changed;
            pContainer.Enabled = true;
            txtFullName.Focus();
        }
 
        private void metroGrid_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                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);
            }
        }
 
        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)
                    {
                        bool result = MyService.Student.Delete(obj.StudentID);
                        if (result)
                        {
                            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);
                }
            }
        }
 
        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                studentBindingSource.EndEdit();
                Student obj = studentBindingSource.Current as Student;
                if (obj != null)
                {
                    if (objState == EntityState.Added)
                        obj.StudentID = MyService.Student.Insert(obj);
                    else if (objState == EntityState.Changed)
                        MyService.Student.Update(obj);
                    metroGrid.Refresh();
                    pContainer.Enabled = false;
                    objState = EntityState.Unchanged;
                }
            }
            catch (Exception ex)
            {
                MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
 
        private void chkGender_CheckStateChanged(object sender, EventArgs e)
        {
            if (chkGender.CheckState == CheckState.Checked)
                chkGender.Text = "Female";
            else if (chkGender.CheckState == CheckState.Unchecked)
                chkGender.Text = "Male";
            else
                chkGender.Text = "???";
        }
    }
}

By following these steps, you can effectively insert, update, delete, and view data from a SQL Server database using Dapper ORM, stored procedures, and Metro Framework in C# Windows Forms Application.

VIDEO TUTORIAL

Tan Lee

Related

Categories

Popular Posts

Entity Framework Code First vs Database First vs Model First Approach
Entity Framework Code First vs Database First vs Model First Approach
 Dec 19, 2024
11 Things You Didn't Know About Cloudflare
11 Things You Didn't Know About Cloudflare
 Dec 19, 2024
Celestial Responsive Bootstrap 4 Admin Dashboard Template
Celestial Responsive Bootstrap 4 Admin Dashboard Template
 Nov 20, 2024
Gentella Admin Template
Gentella Admin Template
 Nov 14, 2024
Elegant Bootstrap 5 HTML5 Admin Dashboard Template
Elegant Bootstrap 5 HTML5 Admin Dashboard Template
 Nov 17, 2024

Copyright © 2016 - 2025 FoxLearn. All Rights Reserved

Home | Donate | About | Contact | Privacy Policy