一.首先打开VS ,创建项目EFTest
二.添加窗体,设计查询界面
三.打开SQLServer ,创建表 [P_Users]
CREATE TABLE [dbo].[P_Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NULL,
[PassWord] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[Addr] [varchar](50) NULL,
[Phone] [nvarchar](11) NULL,
[Remark] [nvarchar](50) NULL,
[Cdate] [datetime] NULL,
[Udate] [datetime] NULL,
[State] [int] NULL,
CONSTRAINT [PK_P_Users] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
四.项目中新建Model类 ,对应数据表 P_Users
public class P_Users
{
/// <summary>
/// Id
/// </summary>
public int Id { get; set; }
/// <summary>
/// UserName
/// </summary>
public string UserName { get; set; }
/// <summary>
/// PassWord
/// </summary>
public string PassWord { get; set; }
/// <summary>
/// Name
/// </summary>
public string Name { get; set; }
/// <summary>
/// Age
/// </summary>
public int? Age { get; set; }
/// <summary>
/// Addr
/// </summary>
public string Addr { get; set; }
/// <summary>
/// Phone
/// </summary>
public string Phone { get; set; }
/// <summary>
/// Remark
/// </summary>
public string Remark { get; set; }
/// <summary>
/// Cdate
/// </summary>
public DateTime? Cdate { get; set; }
/// <summary>
/// Udate
/// </summary>
public DateTime? Udate { get; set; }
/// <summary>
/// State
/// </summary>
public int? State { get; set; }
}
五.创建数据库上下文和 表实体映射
5.1 添加nugut 包
5.2数据库上下文类
public class EFDbContext : DbContext
{
private static readonly string connStr = "Data Source=.;Initial CataLog=TestDB;User ID=sa;PassWord=admin123*;";
public EFDbContext()
{
this.Database.Connection.ConnectionString = connStr;
}
public EFDbContext(string connName) : base()
{
this.Database.Connection.ConnectionString = connName;
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Models.P_Users>().ToTable("P_Users").HasKey(e => e.Id);
}
public override int SaveChanges()
{
return base.SaveChanges();
}
public DbSet<Models.P_Users> P_Users { get; set; }
}
六.添加/编辑用户功能
public partial class FrmAdd : Form
{
public string Action = "";
public int id = 0;
public FrmAdd()
{
InitializeComponent();
this.CenterToParent();
}
private void FrmAdd_Load(object sender, EventArgs e)
{
if (Action.Equals("Edit"))
{
EFDbContext db = new EFDbContext();
var user = db.P_Users.Where(x => x.Id == id).FirstOrDefault();
if (user != null)
{
txtName.Text = user.Name;
txtUser.Text = user.UserName;
txtPwd.Text = user.PassWord;
txtAge.Text = user.Age.ToString();
txtAddr.Text = user.Addr;
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
EFDbContext db = new EFDbContext();
if (Action.Equals("Edit"))
{
var user = db.P_Users.Where(x => x.Id == id).FirstOrDefault();
user.Name = txtName.Text;
user.PassWord = txtPwd.Text;
user.UserName = txtUser.Text;
user.Addr = txtAddr.Text;
user.Age = Convert.ToInt32(txtAge.Text);
db.SaveChanges();
}
else
{
var user = new Models.P_Users();
user.Name = txtName.Text;
user.PassWord = txtPwd.Text;
user.UserName = txtUser.Text;
user.Addr = txtAddr.Text;
user.Age = Convert.ToInt32(txtAge.Text);
user.Cdate = DateTime.Now;
db.P_Users.Add(user);
int r = db.SaveChanges();
if (r > 0)
{
MessageBox.Show("添加成功!");
this.DialogResult = DialogResult.Yes;
}
else
{
MessageBox.Show("添加失败!");
}
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
this.DialogResult = DialogResult.Cancel;
//this.Close();
}
}
七.查询列表
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.AutoGenerateColumns=false;
}
private void btnSearch_Click(object sender, EventArgs e)
{
using (EFDbContext db = new EFDbContext())
{
var list = new List<Models.P_Users>();
if (!string.IsNullOrEmpty(txtWhere.Text.Trim()))
{
list = db.P_Users.Where(x => x.Name.Contains(txtWhere.Text.Trim())).ToList();
}
else
{
list = db.P_Users.ToList();
}
this.dataGridView1.DataSource = list;
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
FrmAdd frmAdd = new FrmAdd();
frmAdd.Action = "Add";
frmAdd.ShowDialog();
}
private void btnEdit_Click(object sender, EventArgs e)
{
var rows = this.dataGridView1.SelectedRows;
if (rows.Count != 1)
{
MessageBox.Show("请选择一行编辑");
return;
}
int id = Convert.ToInt32(rows[0].Cells[0].Value.ToString());
FrmAdd frmAdd = new FrmAdd();
frmAdd.Action = "Edit";
frmAdd.id = id;
frmAdd.ShowDialog();
if (frmAdd.DialogResult == DialogResult.Yes)
{
btnSearch.PerformClick();
}
}
private void btnDel_Click(object sender, EventArgs e)
{
var rows = this.dataGridView1.SelectedRows;
if (rows.Count != 1)
{
MessageBox.Show("请选择一行");
return;
}
int id = Convert.ToInt32(rows[0].Cells[0].Value.ToString());
using (EFDbContext db = new EFDbContext())
{
var user = db.P_Users.Where(x => x.Id == id).FirstOrDefault();
if (user != null)
{
db.P_Users.Remove(user);
int r = db.SaveChanges();
MessageBox.Show("删除成功!");
btnSearch.PerformClick();
}
}
}
}