Ado.Net小练习02(小项目CUID
前台界面：
后台代码：
namespace ado.net小项目cuid
{
public partial class Form1 : Form
{
//连接字符串
string str = @"Data Source=.\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//加载餐桌
LoadDeskInfoByDelFlag(0);
}
/// <summary>
/// 该方法是加载所有没被删除的数据
/// </summary>
/// <param name="i"></param>
private void LoadDeskInfoByDelFlag(int i)
{
List<DeskInfo> list=new List<DeskInfo>();
//通过连接字符串连接数据库
using (SqlConnection con=new SqlConnection(str))
{
//拼接sql语句
string sql = "select DeskId,DeskName,DeskNamePinYin,DeskNum from DeskInfo where DeskDelFlag="+i;
//准备执行sql语句的对象
using (SqlCommand cmd=new SqlCommand(sql,con))
{
con.Open();//打开数据库
//准备读取数据 reader.Dispose();可释放
using (SqlDataReader reader = cmd.ExecuteReader())
{
//判断是否有数据
if (reader.HasRows)
{
while (reader.Read())
{
//读取每一行
DeskInfo dk=new DeskInfo();
dk.DeskId = Convert.ToInt32(reader["DeskId"]);//有两个重载，可以用索引reader[0]和名称，名称准确些
dk.DeskName = reader["DeskName"].ToString();//返回的是object类型，需要转换
dk.DeskNamePinYin = reader["DeskNamePinYin"].ToString();
dk.DeskNum = reader["DeskNum"].ToString();
list.Add(dk);
}
}
}
}
}
dgv.AutoGenerateColumns = false;
dgv.DataSource = list;
if (dgv.Rows.Count>0)
{
dgv.SelectedRows[0].Selected = false;
}
}
//添加数据
private void btnAdd_Click(object sender, EventArgs e)
{
int n = -1;
//获取文本框的值
//连接数据库
using (SqlConnection con=new SqlConnection(str))
{
string sql =
string.Format(
"insert into DeskInfo(DeskName, DeskNamePinYin, DeskDelFlag, DeskNum) values('{0}','{1}',{2},'{3}')",
txtName.Text, txtPinYin.Text, 0, txtNum.Text);
using (SqlCommand cmd=new SqlCommand(sql,con))
{
con.Open();
n= cmd.ExecuteNonQuery();
}
}
//判断是否成功
string msg= n > 0 ? "操作成功" : "操作失败";
MessageBox.Show(msg);
//刷新
LoadDeskInfoByDelFlag(0);
}
//删除数据
private void btnDelete_Click(object sender, EventArgs e)
{
//判断下是否选中行
if (dgv.SelectedRows.Count>0)
{
int r = -1;
//选中了，拿到该行数据的id
string strId = dgv.SelectedRows[0].Cells[0].Value.ToString();
int id = Convert.ToInt32(strId);//转换一下
//update DeskInfo set DeskDelFlag=1 where DeskId=
using (SqlConnection con=new SqlConnection(str))
{
string sql = "update DeskInfo set DeskDelFlag=1 where DeskId="+id;
using (SqlCommand cmd=new SqlCommand(sql,con))
{
con.Open();
r= cmd.ExecuteNonQuery();
}
}
string msg= r > 0 ? "操作成功" : "操作失败";
MessageBox.Show(msg);
LoadDeskInfoByDelFlag(0);
}
}
//dgv选中一行数据时，将信息显示到文本框中
private void dgv_CellClick(object sender, DataGridViewCellEventArgs e)
{
//获取这条数据中的每个列中的值
if (dgv.SelectedRows.Count>0)
{
//id name pinyin num
lblId.Text = dgv.SelectedRows[0].Cells[0].Value.ToString();
txtUName.Text = dgv.SelectedRows[0].Cells[1].Value.ToString();
txtUPinYin.Text = dgv.SelectedRows[0].Cells[2].Value.ToString();
txtUNum.Text = dgv.SelectedRows[0].Cells[3].Value.ToString();
}
}
//修改数据
private void btnEdit_Click(object sender, EventArgs e)
{
if (dgv.SelectedRows.Count>0)
{
int r = -1;
//修改
using (SqlConnection con=new SqlConnection(str))
{
string sql =string.Format("update DeskInfo set DeskName='{0}',DeskNamePinYin='{1}',DeskNum='{2}' where DeskId={3}",txtUName.Text,txtUPinYin.Text,txtUNum.Text,lblId.Text) ;
using (SqlCommand cmd=new SqlCommand(sql,con))
{
con.Open();
r=cmd.ExecuteNonQuery();
}
}
string msg = r > 0 ? "操作成功" : "操作失败";
MessageBox.Show(msg);
LoadDeskInfoByDelFlag(0);
}
}
//显示有多少条数据
private void btnDisplay_Click(object sender, EventArgs e)
{
//练习
object obj;
using (SqlConnection con = new SqlConnection(str))
{
string sql = "select count(*) from DeskInfo where DeskDelFlag=0";
using (SqlCommand cmd=new SqlCommand(sql,con))
{
con.Open();
obj = cmd.ExecuteScalar();
}
}
MessageBox.Show(obj.ToString());
}
//回收站，显示被删除的数据
private void btnGs_Click(object sender, EventArgs e)
{
//显示被删除的数据
LoadDeskInfoByDelFlag(1);
}
//彻底删除
private void btnCDelete_Click(object sender, EventArgs e)
{
string id = dgv.SelectedRows[0].Cells[0].Value.ToString();
//还是 更新 该标识 5 恢复
//稍稍修改一下标识
int r = -1;
using(SqlConnection con=new SqlConnection(str))
{
string sql = "update DeskInfo set DeskDelFlag=5 where DeskId=" + id;
using (SqlCommand cmd=new SqlCommand(sql,con))
{
con.Open();
r=cmd.ExecuteNonQuery();
}
}
MessageBox.Show(r > 0 ? "操作成功" : "操作失败");
LoadDeskInfoByDelFlag(1);
}
}
}
