چگونه از Dapper برای کار با دیتابیس در سی شارپ استفاده کنیم؟
سلام دوستان
در این آموزش قصد داریم کار با dapper رو به شما عزیزان اموزش بدیم
اول از همه یک پروژه windows forms ایجاد کنید
حالا باید کتاب خانه Dapper را از Manage Nuget Package دانلود کنید
کتاب خانه MetroModernUI را نیز برای ظاهر بهتر برنامه از ناگت منیج پکیجر نصب کنید
کلاس های زیر را به عنوان مدل دیتابیس ایجاد کنید(در واقعه مدل دیتابیس نیست بلکه DTO/ViewModel است که برای مپ کردن استفاده می شود)
[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; }
}حالا sql server را باز کنید یک دیتابیس بسازید و یک نام دلخواه برای آن انتخاب کنید
و بعد کد های زیر را روی دیتابیس اجرا کنید
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]و در ادامه یک procedure برای insert و update و... می سازیم
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()یک enum به شکل زیر بسازید
public enum EntityState
{
Unchanged,
Added,
Changed,
Deleted
}
و در ادامه کد زیر را به فایل app.config پروژه اضافه کنید اگر پروژه این فایل را نداشت یکی بسازید
<configuration>
<connectionStrings>
<add name = "cn" connectionString="Data Source=.;Initial Catalog=dbstudent;User ID=sa;Password=123@qaz;" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>در نهایت از کد های زیر برای انجام عملیات ها استفاده کنید
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 = "???";
}
}
}
پیشنهاد: برای نصب و راه اندازی بانک اطلاعاتی sql server 2022 به مقاله
مراجعه کنید
پایان.
