https://drive.google.com/drive/u/0/folders/1UwS9FZ3ELCOK6SAwirHrkxq3z_RSbxJt
https://www.youtube.com/watch?v=k7IkIeww_U0&list=PLumjEWemDx2ytsN5ILEkDg-kFEyqh9Z9G&index=9&t=926s
SqlServer 新增 View/ Insert/ Update/ Delete
USE [IT4Student]
GO
Create proc SP_Product_View
as begin
Select * from StudentsTable
end
go
USE [IT4Student]
GO
Create proc SP_Product_Insert
@StuName varchar(20),
@StuPhone varchar(20),
@StuGender varchar(10),
@StuBirthday date,
@StuCity varchar(20),
@StuAddress varchar(20)
as begin
insert into StudentsTable
(StuName,StuPhone,StuGender
,StuBirthday, StuCity,StuAddress)
values(@StuName,@StuPhone,@StuGender
,@StuBirthday, @StuCity,@StuAddress)
end
go
USE [IT4Student]
GO
Create proc SP_Product_Update
@StuName varchar(20),
@StuPhone varchar(20),
@StuGender varchar(10),
@StuBirthday date,
@StuCity varchar(20),
@StuAddress varchar(20),
@StudentID int
as begin
Update StudentsTable set
StuName = @StuName,StuPhone = @StuPhone,
StuGender = @StuGender,StuBirthday = @StuBirthday,
StuCity = @StuCity,StuAddress = @StuAddress
WHERE StudentID=@StudentID
end
go
USE [IT4Student]
GO
Create proc SP_Product_Delete
@StudentID int
as begin
DELETE FROM StudentsTable
WHERE StudentID=@StudentID
end
go
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Windows.Forms;
namespace WinFormsStudentTb
{
public partial class StudentsInformation : Form
{
public StudentsInformation()
{
InitializeComponent();
}
SqlConnection con =
new SqlConnection(@"Data
Source=DESKTOP-KOD5HR3;Initial Catalog=IT4Student;Integrated
Security=True");
private void StudentsInformation_Load(object sender,
EventArgs e)
{
GetAllRecord();
radioMale.Checked
= true;
string[] AllCity
= new string[]{"New York", "Denver", "Philadelphia", "Houston", "Phoenix"};
comboxCity.Items.AddRange(AllCity);
}
private void btnSearch_Click(object sender,
EventArgs e)
{
if (txtSearchID.Text
== string.Empty)
{
GetAllRecord();
ResetFormControls();
}
else
{
con.Open();
SqlCommand com =
new SqlCommand(@"SELECT * FROM StudentsTable
WHERE StudentID = @ID", con);
com.CommandType
= CommandType.Text;
com.Parameters.AddWithValue("@ID", txtSearchID.Text);
SqlDataReader readTb
= com.ExecuteReader();
DataTable dt =
new DataTable();
dt.Load(readTb);
SqlDataReader readValue
= com.ExecuteReader();
while (readValue.Read())
{
txtStuName.Text
= readValue["StuName"].ToString();
txtStuPhone.Text
= readValue["StuPhone"].ToString();
if (readValue["StuGender"].ToString()
== "Male")
radioMale.Checked
= true;
else radioFemale.Checked
= true;
dateTimePicker1.Value
= Convert.ToDateTime(readValue["StuBirthday"]);
comboxCity.Text
= readValue["StuCity"].ToString();
txtxStuAddress.Text
= readValue["StuAddress"].ToString();
}
con.Close();
dataGridView1.DataSource
= dt;
}
}
private void btnInsert_Click(object sender,
EventArgs e)
{
if (txtStuName.Text.Length
> 0)
{
con.Open();
string strGender
= "", strBirthday = dateTimePicker1.Value.ToString("yyyy-MM-dd");
if (radioMale.Checked
== true) strGender = "Male";
else strGender
= "Female";
SqlCommand com =
new SqlCommand("exec dbo.SP_Product_Insert
'" + txtStuName.Text + "','" + txtStuPhone.Text
+ "','" + strGender
+ "','" + strBirthday
+ "','" + comboxCity.Text
+ "','" + txtxStuAddress.Text
+ "'", con);
com.ExecuteNonQuery();
MessageBox.Show("儲存成功");
con.Close();
GetAllRecord();
ResetFormControls();
}
}
private void btnUpdate_Click(object sender,
EventArgs e)
{
if (txtSearchID.Text
== string.Empty ||
txtSearchID.Text.All(Char.IsDigit)
== false ||
Convert.ToInt32(txtSearchID.Text)
< 1)
MessageBox.Show("請確認查詢ID?", "更新?"
, MessageBoxButtons.OK, MessageBoxIcon.Error);
else
{
con.Open();
string strGender
= "", strBirthday = dateTimePicker1.Value.ToString("yyyy-MM-dd");
if (radioMale.Checked
== true) strGender = "Male";
else strGender
= "Female";
SqlCommand com =
new SqlCommand("exec dbo.SP_Product_Update
'" + txtStuName.Text + "','" + txtStuPhone.Text
+ "','" + strGender
+ "','" + strBirthday
+ "','" + comboxCity.Text
+ "','" + txtxStuAddress.Text
+ "','" + txtSearchID.Text
+ "'", con);
com.ExecuteNonQuery();
MessageBox.Show("更新成功");
con.Close();
}
GetAllRecord();
ResetFormControls();
}
private void btnDelete_Click(object sender,
EventArgs e)
{
if (txtSearchID.Text
== string.Empty ||
txtSearchID.Text.All(Char.IsDigit)
== false ||
Convert.ToInt32(txtSearchID.Text)
< 1)
MessageBox.Show("請確認刪除ID?", "刪除?"
, MessageBoxButtons.OK, MessageBoxIcon.Error);
else
{
con.Open();
string s =
"exec dbo.SP_Product_Delete " + txtSearchID.Text;
SqlCommand com =
new SqlCommand(s, con);
com.ExecuteNonQuery();
MessageBox.Show("刪除成功");
con.Close();
}
GetAllRecord();
ResetFormControls();
}
private void btnReset_Click(object sender,
EventArgs e)
{
ResetFormControls();
}
private void ResetFormControls()
{
txtSearchID.Clear();
txtStuName.Clear();
txtStuPhone.Clear();
radioMale.Checked
= true;
dateTimePicker1.Text
= string.Empty;
//comboxCity.SelectedIndex
= -1;
comboxCity.Text
= null;
txtxStuAddress.Clear();
}
private void GetAllRecord()
{
con.Open();
SqlCommand com =
new SqlCommand("exec dbo.SP_Product_View", con
);
SqlDataAdapter da =
new SqlDataAdapter(com);
DataTable dt =
new DataTable();
da.Fill(dt);
con.Close();
dataGridView1.DataSource
= dt;
}
}
}