Windows Forms: Insert Update Delete View data from SQL Database using 3 Tier Architecture in C#
By FoxLearn 6/18/2017 10:15:24 AM 12.86K
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
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
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
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
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 IStudentRepository interface to process your business
public interface IStudentRepository { List<Student> GetAll(); int Insert(Student obj); bool Update(Student obj); bool Delete(int studentId); }
Create an EntityState to manage your object state
public enum EntityState { Unchanged, Added, Changed, Deleted }
Step 6: Add code to handle access sql database to CRUD.DataAccess project
You should create a Helper class to get connection string from the app.config file, then add a reference to System.Configuration.dll
using System.Configuration; namespace CRUD.DataAccess { public class Helper { public static string ConnectionString => ConfigurationManager.ConnectionStrings["cn"].ConnectionString; } }
Create a StudentRepository class, then implement IStudentRepository interface
using CRUD.Core; using System.Collections.Generic; using System.Linq; 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, then get output parameter 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 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 7: Create a StudentService class to CRUD.Service project
You should install autofac, then add references to CRUD.Core and CRUD.DataAccess project
using Autofac; using CRUD.Core; using CRUD.DataAccess; using System.Collections.Generic; namespace CRUD.Services { public class StudentService { static IContainer _container; static StudentService() { ContainerBuilder builder = new ContainerBuilder(); //Set builder pattern builder.RegisterType<StudentRepository>().As<IStudentRepository>(); _container = builder.Build(); } public static bool Delete(int studentId) { return _container.Resolve<IStudentRepository>().Delete(studentId); } public static List<Student> GetAll() { return _container.Resolve<IStudentRepository>().GetAll(); } public static Student Save(Student obj, EntityState state) { if (state == EntityState.Added) obj.StudentID = _container.Resolve<IStudentRepository>().Insert(obj); else _container.Resolve<IStudentRepository>().Update(obj); return obj; } } }
Step 8: Add a connection string to the app.config file in CURD project, then add code to handle your form as below
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>
Form1
using System; using System.Drawing; using System.Windows.Forms; using CRUD.Core; using CRUD.Services; 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) { //Read image from file, then load to picture box 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 { //Call student service to get data studentBindingSource.DataSource = StudentService.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; //Add a new student to binding source 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) { //Call student service to delete data bool result = StudentService.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) { //Call student service to save data, auto detect insert & update based on entity state obj = StudentService.Save(obj, objState); 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
- 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#
- Windows Forms: Insert Update Delete Search data from local database in C#
- Windows Forms: Insert Update Delete View data in SQL Server using 3 Tiers in C#
- Windows Forms: Insert Update Delete Select in SQL Server in C#