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

How to Insert, Update, Delete and View Student Profile data from SQL database using Dapper ORM, Stored Procedure, Metro Framework in C#

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

crud-ado-net

Select Class Library, then create CRUD.Core and CRUD.DataAccess project

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

install metro framework

Similarly, you need to install dapper to CRUD.DataAccess project

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

insert update delete view data using sql database

Step 4: 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]

Create a stored procedure to insert, update data to student table

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

Step 5: Add a student class to CRUD.Core project

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

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

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

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

Step 6: Create an EntityState to CRUD project

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

Create a MyService class to control your business

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

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>

Step 7: Add code to handle your form as below

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 = "???";
        }
    }
}

VIDEO TUTORIALS