20131207-ADO.NET-第十六天

標籤：datagridview   style   io   ar   color   os   sp   for   on   

[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; }

}

?

?

?

}

}

?

20131207-ADO.NET-第十六天