WinFormTb02

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