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, dapper -> 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]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 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 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;
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;
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
using(IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
{
if (db.State == ConnectionState.Closed)
db.Open();
//Get data from sql database
studentBindingSource.DataSource = db.Query<Student>("select *from Students", commandType: CommandType.Text);
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 data 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)
{
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
{
if (db.State == ConnectionState.Closed)
db.Open();
//Excute query to delete data
int result = db.Execute("delete from Students where StudentID = @StudentID", new { StudentID = obj.StudentID }, commandType: CommandType.Text);
if (result != 0)
{
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)
{
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
{
if (db.State == ConnectionState.Closed)
db.Open();
if (objState == EntityState.Added)
{
//Insert data with output parameter
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 });
db.Execute("sp_Students_Insert", p, commandType: CommandType.StoredProcedure);
obj.StudentID = p.Get<int>("@StudentID");
}
else if (objState == EntityState.Changed)
{
//Execute stored procedure to update data
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);
}
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
