20131207-ADO.NET-第十六天
[1]快捷键
工具箱：ctrl+w+x 首字母定位控件范围
属性：F4 或ctrl+w+p Tab跳转 ，home 与end也有效
[2]连接字符串
string str = "Data Source=xy-pc;Initial Catalog=myitcast;Integrated Security=true";
[*]
[3]
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace _06大项目
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnAdd_Click(object sender, EventArgs e)
{
//获取文本框的内容--要判断(你们做)
string name = txtAddName.Text;//姓名
int gender = txtAddGender.Text == "男" ? 1 : txtAddGender.Text == "女" ? 0 : 2;//站着埋-坑//大坑
//判断一下性别的值 是1还是0 如果都不是则告诉用户输入错误请重新输入(留给你们了)
int age = Convert.ToInt32(txtAddAge.Text);//年龄--坑(留给你们了)
string phone = txtAddPhone.Text;//电话号码
//创建连接字符串
string str = "Data Source=xy-pc;Initial Catalog=myitcast;Integrated Security=true";
int count = -1;
//连接数据库
using (SqlConnection con = new SqlConnection(str))
{
string sql = string.Format("insert INTO TblStudent(TSName,TSGender,TSAge,TSPhone,tclassid)VALUES('{0}',{1},{2},'{3}',{4})", name, gender, age, phone, 1);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
//打开数据库
con.Open();
count = cmd.ExecuteNonQuery();//执行sql语句
}
}
//执行sql语句
if (count > 0)
{
MessageBox.Show("添加成功");
}
else
{
MessageBox.Show("添加失败");
}
//结果
}
private void Form1_Load(object sender, EventArgs e)
{
LoadAllStudent();//加载学生
}
private void LoadAllStudent()
{
//创建一个集合,存储每个学生对象
List<Student> list = new List<Student>();
//封装成方法---还要刷新呢
//查询所有的数据
string str = "Data Source=xy-pc;Initial Catalog=myitcast;Integrated Security=true";//创建连接字符串
//连接数据库
using (SqlConnection con = new SqlConnection(str))
{
string sql = "select tsid, TSName,TSGender,TSAge,TSPhone FROM TblStudent";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();//打开数据库
using (SqlDataReader sda = cmd.ExecuteReader())
{
if (sda.HasRows)//如果为true证明至少有一条数据
{
while (sda.Read())//读取到下一条
{
// sda[1]//每一列的数据
Student stu = new Student();//创建一个学生对象
stu.TSId = sda.GetInt32(0);//主键id的值
stu.TSName = sda["tsname"].ToString();
stu.TSGender = sda.GetBoolean(2) == true ? '男' : '女';
stu.TSAge = sda.GetInt32(3);//年龄
stu.TSPhone = sda.GetString(4);
list.Add(stu);
}
}
}
}
}
dgv.DataSource = list;//把集合绑定到控件上
}
private void deletetsm_Click(object sender, EventArgs e)
{
//首先判断用户是否选中行
if (dgv.SelectedRows.Count > 0)
{
int count=-1;
string id = dgv.SelectedRows[0].Cells[0].Value.ToString();
//创建连接字符串
string str = "Data Source=xy-pc;Initial Catalog=myitcast;Integrated Security=true";
//连接数据库
using (SqlConnection con=new SqlConnection(str))
{
string sql = "delete from tblstudent where tsid="+id;
using (SqlCommand cmd=new SqlCommand(sql,con))
{
con.Open();
count =cmd.ExecuteNonQuery();
}// end using
}//end using
if (count>0)
{
LoadAllStudent();
MessageBox.Show("删除成功");
}
else
{
MessageBox.Show("删除失败");
}
}//end if
//获取选中行的id
//MessageBox.Show("删除了");
}
private void dgv_RowEnter(object sender, DataGridViewCellEventArgs e)
{
//是否有选中的行
if (dgv.SelectedRows.Count>0)
{
//获取 id 姓名 性别 年龄
labId.Text = dgv.SelectedRows[0].Cells[0].Value.ToString();//id的值
//姓名
txtUpName.Text = dgv.SelectedRows[0].Cells[1].Value.ToString();
//性别
txtUpGender.Text = dgv.SelectedRows[0].Cells[2].Value.ToString();
//年龄
txtUpPhone.Text = dgv.SelectedRows[0].Cells[3].Value.ToString();
txtUpAge.Text = dgv.SelectedRows[0].Cells[4].Value.ToString();
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
int count = -1;
//首先获取 当前选中行的id
string id = labId.Text;
//int gender = txtUpGender.Text == "男" ? 1 : txtUpGender.Text == "女" ? 0 : 2;
int gender = txtUpGender.Text == "男" ? 1 : 0;
// 创建连接数据库的字符串
string str = "Data Source=xy-pc;Initial Catalog=myitcast;Integrated Security=true";
//连接数据库
using (SqlConnection con=new SqlConnection(str))
{
string sql =string.Format( "update tblstudent set TSName='{0}',TSGender={1},TSAge={2},TSPhone='{3}' where tsid={4}",txtUpName.Text,gender,txtUpAge.Text,txtUpPhone.Text,id);
using (SqlCommand cmd=new SqlCommand(sql,con))
{
con.Open(); //打开数据库
count= cmd.ExecuteNonQuery();
}
}
if (count>0)
{
LoadAllStudent();//刷新
MessageBox.Show("修改成功");
}
else
{
MessageBox.Show("修改失败");
}
//执行
}
private void btnCount_Click(object sender, EventArgs e)
{
string count = "";
//创建连接字符串
string str = "Data Source=xy-pc;Initial Catalog=MyItcast;Integrated Security=True";
//连接数据库
using (SqlConnection con=new SqlConnection(str))
{
string sql = "select count(*) from tblstudent";
using (SqlCommand cmd=new SqlCommand(sql,con))
{
con.Open();//打开数据库
count= cmd.ExecuteScalar().ToString();
}
}
//打开数据库
MessageBox.Show("一共有"+count+"个童鞋");
//执行sql语句
}
}
}
//数据转对象
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace _06大项目
{
public class Student
{
// TSId, TSName, TSGender, TSAddress, TSPhone, TSAge, TSBirthday, TSCardId, TClassId
//用到哪列写哪列
//查询四列
private int _tSId;
public int TSId
{
get { return _tSId; }
set { _tSId = value; }
}
private string _tSName;
public string TSName
{
get { return _tSName; }
set { _tSName = value; }
}
private char _tSGender; //坑
public char TSGender
{
get { return _tSGender; }
set { _tSGender = value; }
}
private string _tSPhone;
public string TSPhone
{
get { return _tSPhone; }
set { _tSPhone = value; }
}
private int _tSAge;
public int TSAge
{
get { return _tSAge; }
set { _tSAge = value; }
}
}
}
