A complete set of addition, deletion, modification and inspection of C-form student management system

Keywords: SQL Windows Database

C form student management system complete set of add, delete, modify and check main form:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace StudentManageSys
{
public partial class FrmMain : Form
{
public FrmMain()
{
InitializeComponent();
}
///
///Exit button click event
///
///Event source
///Event parameters
private void tsmiExit_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("are you sure you want to exit?" "," prompt ", MessageBoxButtons.OKCancel,MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
Application.Exit();
}
else
{

        }
    }
    /// <summary>
    ///Query student information click event
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void tsmiSearch_Click(object sender, EventArgs e)
    {
        FrmSearch fs = new FrmSearch();
        fs.MdiParent = this;
        fs.Show();
    }
    /// <summary>
    ///Add student information click event
    /// </summary>
    ///< param name = "sender" > event source < / param >
    ///Event parameter < / param >
    private void tsmiAdd_Click(object sender, EventArgs e)
    {
        FrmAdd fa = new FrmAdd();
        fa.MdiParent = this;
        fa.Show();
    }
}

}

**

Increase:

**
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace StudentManageSys
{
public partial class FrmAdd : Form
{
DBhelper db = new DBhelper();
DataSet ds = new DataSet();
SqlDataAdapter adapter;
public FrmAdd()
{
InitializeComponent();
}
///
///Save button click event
///
///
///
private void btnSave_Click(object sender, EventArgs e)
{
//Non empty validation
if (CheckInput())
{
if (AddInfo())
{
MessageBox.Show("successfully added");
}
else
{
MessageBox.Show("failed to add");
}
}
else
{
MessageBox.Show("please fill in the complete information");
}
/ / bind CBO
BindCbo();
}
public void BindCbo()
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from Grade");
adapter = db.GetAdapter(sql.ToString());
adapter.Fill(ds, "Grade");
//Add a row
DataRow row = ds.Tables["Grade"].NewRow();
row[0] = "-1";
row[1] = "all";
ds.Tables["Grade"].Rows.InsertAt(row, 0);

        this.cboGrade.DataSource = ds.Tables["Grade"];
        this.cboGrade.ValueMember = "GradeId";
        this.cboGrade.DisplayMember = "GradeName";
    }
    /// <summary>
    ///Non empty validation
    /// </summary>
    /// <returns></returns>
    public bool CheckInput()
    {
        if (this.txtPwd.Text.Trim().Equals(String.Empty)
            || this.cboGrade.Text.Trim().Equals("whole")
            || this.txtName.Text.Trim().Equals(String.Empty)
            || this.txtPhone.Text.Trim().Equals(String.Empty))
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    /// <summary>
    ///Add information
    /// </summary>
    /// <returns></returns>
    public bool AddInfo()
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendFormat(@"INSERT INTO [dbo].[Student]
       ([LoginPwd]
       ,[StudentName]
       ,[Gender]
       ,[GradeId]
       ,[Phone]
       ,[Address]
       ,[Birthday]
       ,[Email])
 VALUES
       ('{0}'
       , '{1}'
       , '{2}'
       ,{ 3}
       ,'{4}'
       ,'{5}'
       ,'{6}'
       '{7}')",
       this.txtPwd.Text.Trim(),
       this.txtName.Text.Trim(),
       this.rdoMale.Checked == true,
       this.cboGrade.SelectedValue,
       this.txtPhone.Text.Trim(),
       this.txtAddress.Text.Trim(),
       this.txtBirthday.Text.Trim(),
       this.txtEmail.Text.Trim());
      int result = db.ExecuteNonQuery(sql.ToString());
        if (result >0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    /// <summary>
    ///Clear button
    /// </summary>
    ///< param name = "sender" > event source < / param >
    ///Event parameter < / param >
    private void btnClear_Click(object sender, EventArgs e)
    {
        foreach (Control control in Controls)
        {
            if (control is TextBox)
            {
                control.Text = "";
            }
        }
        this.cboGrade.SelectedIndex = 0;
        this.rdoMale.Checked = true;
    }
    /// <summary>
    ///Close button click event
    /// </summary>
    ///< param name = "sender" > event source < / param >
    ///Event parameter < / param >
    private void btnClose_Click(object sender, EventArgs e)
    {
        this.Close();
    }

    private void FrmAdd_Load(object sender, EventArgs e)
    {

    }
}

}

query

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace StudentManageSys
{
public partial class FrmSearch : Form
{
//Database help class
DBhelper db = new DBhelper();
/ / data set
DataSet ds = new DataSet();
/ / adapter
SqlDataAdapter adapter;

    public FrmSearch()
    {
        InitializeComponent();
    }
    /// <summary>
    ///Form load event
    /// </summary>
    ///< param name = "sender" > event source < / param >
    ///Event parameter < / param >
    private void FrmSearch_Load(object sender, EventArgs e)
    {
        //Binding CBO
        BindCbo();
        //Clear extra columns
        this.dgvShow.AutoGenerateColumns = false;
        //Binding Dgv
        BindDgv();
        //Set gender combo box to select the first item by default 
        this.cboGender.SelectedIndex = 0;
    }
    /// <summary>
    //Bind CBO
    /// </summary>
    public void BindCbo()
    {
        StringBuilder sql = new StringBuilder();
        sql.Append("select * from Grade");
        adapter = db.GetAdapter(sql.ToString());
        adapter.Fill(ds, "Grade");
        //Add a row
        DataRow row = ds.Tables["Grade"].NewRow();
        row[0] = "-1";
        row[1] = "whole";
        ds.Tables["Grade"].Rows.InsertAt(row,0);

        this.cboGrade.DataSource = ds.Tables["Grade"];
        this.cboGrade.ValueMember = "GradeId";
        this.cboGrade.DisplayMember = "GradeName";
    }
    /// <summary>
    //Bind Dgv
    /// </summary>
    public void BindDgv()
    {
        if (ds.Tables["StudentAndGrade"] != null)
        {
            ds.Tables["StudentAndGrade"].Clear();
        }
        StringBuilder sql = new StringBuilder();
        sql.Append(@"select * from Grade,Student
        where Grade.GradeId = Student.GradeId");
        adapter = db.GetAdapter(sql.ToString());
        adapter.Fill(ds, "StudentAndGrade");
        this.dgvShow.DataSource = ds.Tables["StudentAndGrade"];
    }
    /// <summary>
    ///Query button click event
    /// </summary>
    ///< param name = "sender" > event source < / param >
    ///Event parameter < / param >
    private void btnSearch_Click(object sender, EventArgs e)
    {
        DataView dv = new DataView(ds.Tables["StudentAndGrade"]);
        if (!this.txtName.Text.Trim().Equals(String.Empty)&&
            this.cboGrade.Text.Trim().Equals("whole")
            && this.cboGender.Text.Trim().Equals("whole"))
        {
            //1
            dv.RowFilter = string.Format("StudentName like '%{0}%'",
                this.txtName.Text.Trim());
        }
        else if(this.txtName.Text.Trim().Equals(String.Empty) &&
            !this.cboGrade.Text.Trim().Equals("whole")&&
             this.cboGender.Text.Trim().Equals("whole"))
        {
            //2
            dv.RowFilter = string.Format("GradeId = {0}",
               this.cboGrade.SelectedValue);
        }
        else if (this.txtName.Text.Trim().Equals(String.Empty) &&
            this.cboGrade.Text.Trim().Equals("whole")&& 
            !this.cboGender.Text.Trim().Equals("whole"))
        {
            //3
            dv.RowFilter = string.Format("Gender = '{0}'",
               this.cboGender.Text.Trim().Equals("male")?1:0);
        }
        else if (!this.txtName.Text.Trim().Equals(String.Empty) &&
            !this.cboGrade.Text.Trim().Equals("whole")&&
            this.cboGender.Text.Trim().Equals("whole"))
        {
            //12
            dv.RowFilter = string.Format("StudentName like '%{0}%' and GradeId = {1}",
                this.txtName.Text.Trim(), this.cboGrade.SelectedValue);
        }
        else if (!this.txtName.Text.Trim().Equals(String.Empty) &&
            this.cboGrade.Text.Trim().Equals("whole")&&
            !this.cboGender.Text.Trim().Equals("whole"))
        {
            //13
            dv.RowFilter = string.Format("StudentName like '%{0}%' and Gender = '{1}'",
                this.txtName.Text.Trim(), this.cboGender.Text.Trim().Equals("male") ? 1 : 0);
        }
        else if (this.txtName.Text.Trim().Equals(String.Empty) &&
            !this.cboGrade.Text.Trim().Equals("whole")&&
             !this.cboGender.Text.Trim().Equals("whole"))
        {
            //23
            dv.RowFilter = string.Format("GradeId = {0} and Gender = '{1}'",
               this.cboGrade.SelectedValue, this.cboGender.Text.Trim().Equals("male") ? 1 : 0);
        }
        else if (this.txtName.Text.Trim().Equals(String.Empty) &&
            this.cboGrade.Text.Trim().Equals("whole")
            && this.cboGender.Text.Trim().Equals("whole"))
        {
            //123
            dv.RowFilter = string.Format("StudentName like '%{0}%' and GradeId = {1} and Gender = '{2}'",
               this.txtName.Text.Trim(),this.cboGrade.SelectedValue, this.cboGender.Text.Trim().Equals("male") ? 1 : 0);
        }
        this.dgvShow.DataSource = dv;
    }
    /// <summary>
    ///Delete click event
    /// </summary>
    ///< param name = "sender" > event source < / param >
    ///Event parameter < / param >
    private void tsmiDelete_Click(object sender, EventArgs e)
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendFormat(@"DELETE FROM [dbo].[Student]
        WHERE StudentNo = { 0}",
        this.dgvShow.SelectedRows[0].Cells[0].Value.ToString());
        int result = db.ExecuteNonQuery(sql.ToString());
        if (result > 0)
        {
            MessageBox.Show("Delete successful");
            BindDgv();
        }
        else
        {
            MessageBox.Show("Delete failed");
        }
    }
    /// <summary>
    ///Modify form
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void tsmiUpdate_Click(object sender, EventArgs e)
    {
        FrmUpdate fu = new FrmUpdate();
        //Pass value
        fu.StudentNo = this.dgvShow.SelectedRows[0].Cells[0].Value.ToString();
        //Specifies that you can use this form method to modify a form
        fu.fs = this;
        fu.Show();
    }
}

}

modify

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace StudentManageSys
{
public partial class FrmUpdate : Form
{
//Query form (scheduled refresh)
public FrmSearch fs;

    DBhelper db = new DBhelper();
    DataSet ds = new DataSet();
    SqlDataAdapter adapter;

    //Value passing variables
    public string StudentNo;


    public FrmUpdate()
    {
        InitializeComponent();
    }
    /// <summary>
    ///Close button click event
    /// </summary>
    ///< param name = "sender" > event source < / param >
    ///Event parameter < / param >
    private void btnClose_Click(object sender, EventArgs e)
    {
        this.Close();
    }
    /// <summary>
    ///Clear button click event
    /// </summary>
    ///< param name = "sender" > event source < / param >
    ///Event parameter < / param >
    private void btnClear_Click(object sender, EventArgs e)
    {
        this.txtAddress.Text.Trim();
        this.txtBirthday.Text.Trim();
        this.txtEmail.Text.Trim();
        this.txtName.Text.Trim();
        this.txtPhone.Text.Trim();
        this.txtPwd.Text.Trim();
        this.cboGrade.SelectedIndex = 0;
        this.rdoMale.Checked = true;
    }
    /// <summary>
    ///Save button click event
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void btnSave_Click(object sender, EventArgs e)
    {
        //Non empty verification
        if (CheckInput())
        {
            //modify
            if (UpdateInfo())
            {
                MessageBox.Show("Modified success");
                fs.BindDgv();//Refresh
                this.Close();
            }
            else
            {
                MessageBox.Show("Modification failed");
            }
        }
        else
        {
            MessageBox.Show("Please fill in the complete information");
        }
        //Binding CBO
        BindCbo();
    }
    public void BindCbo()
    {
        StringBuilder sql = new StringBuilder();
        sql.Append("select * from Grade");
        adapter = db.GetAdapter(sql.ToString());
        adapter.Fill(ds, "Grade");
        //Add a row
        DataRow row = ds.Tables["Grade"].NewRow();
        row[0] = "-1";
        row[1] = "whole";
        ds.Tables["Grade"].Rows.InsertAt(row, 0);

        this.cboGrade.DataSource = ds.Tables["Grade"];
        this.cboGrade.ValueMember = "GradeId";
        this.cboGrade.DisplayMember = "GradeName";
    }
    /// <summary>
    ///Non empty validation
    /// </summary>
    /// <returns></returns>
    public bool CheckInput()
    {
        if (this.txtPwd.Text.Trim().Equals(String.Empty)
            || this.cboGrade.Text.Trim().Equals("whole")
            || this.txtName.Text.Trim().Equals(String.Empty)
            || this.txtPhone.Text.Trim().Equals(String.Empty))
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    /// <summary>
    ///Add information
    /// </summary>
    /// <returns></returns>
    public bool UpdateInfo()
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendFormat(@"UPDATE [dbo].[Student]
        SET [LoginPwd] = '{0}'
        ,[StudentName] = '{1}'
        ,[Gender] = '{2}'
        ,[GradeId] = {3}
        ,[Phone] = '{4}'
        ,[Address] = '{5}'
        ,[Birthday] = '{6}'
        ,[Email] = '{7}'
        WHERE StudentNo = {8})",
       this.txtPwd.Text.Trim(),
       this.txtName.Text.Trim(),
       this.rdoMale.Checked == true,
       this.cboGrade.SelectedValue,
       this.txtPhone.Text.Trim(),
       this.txtAddress.Text.Trim(),
       this.txtBirthday.Text.Trim(),
       this.txtEmail.Text.Trim(),
       StudentNo);
        int result = db.ExecuteNonQuery(sql.ToString());
        if (result > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    /// <summary>
    ///Form load event
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void FrmUpdate_Load(object sender, EventArgs e)
    {
        BindCbo();
        this.txtStudentNo.Text = StudentNo;
        GetInfoByStudentNo();
    }
    /// <summary>
    ///Query student information through student ID
    /// </summary>
    public void GetInfoByStudentNo()
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendFormat(@"select * from Student
        where StudentNo = {0}",
        StudentNo);
        SqlDataReader reader = db.ExecuteReader(sql.ToString());
        while (reader.Read())
        {
            string Loginpwd = reader[1].ToString();
            string StudentName = reader[2].ToString();
            string Gender = reader[3].ToString();
            string GradeId = reader[4].ToString();
            string Phone = reader[5].ToString();
            string Address = reader[6].ToString();
            string Birthday = reader[7].ToString();
            string Email = reader[8].ToString();
        this.txtPwd.Text = Loginpwd;
        this.txtName.Text = StudentName;
            //Gender radio button
            if (Gender.Equals("1"))
            {
                this.rdoMale.Checked = true;
            }
            else
            {
                this.rdoMale.Checked = false;
            }
            //Grade box
            if (GradeId.Equals("1"))
            {
                this.cboGrade.SelectedIndex = 1;
            }
            else if (GradeId.Equals("2"))
            {
                this.cboGrade.SelectedIndex = 2;
            }else if (GradeId.Equals("3"))
            {
                this.cboGrade.SelectedIndex = 3;
            }
            this.txtPhone.Text = Phone;
            this.txtAddress.Text = Address;
            this.txtBirthday.Text = Birthday;
            this.txtEmail.Text = Email;
        }
        reader.Close();
    }
}

}

Database help class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace StudentManageSys
{
class DBhelper
{
private string connString = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";
private SqlConnection conn;

    public SqlConnection Conn
    {
        get
        {
            if (this.conn == null)
            {
                conn = new SqlConnection(connString);
            }
        return conn;
        }
    }
    /// <summary>
    ///Open database
    /// </summary>
    public void OpenConnection()
    {
        if (this.Conn.State == ConnectionState.Broken)
        {
            this.Conn.Close();
            this.Conn.Open();
        }
        if (this.Conn.State == ConnectionState.Closed)
        {
            this.Conn.Open();
        }
    }
    /// <summary>
    ///Close database
    /// </summary>
    public void CloseConnection()
    {
        if (this.Conn.State == ConnectionState.Broken||
            this.Conn.State == ConnectionState.Open)
        {
            this.Conn.Close();
        }
    }
    /// <summary>
    //Additions and deletions
    /// </summary>
    ///< param name = "SQL" > sql statement < / param >
    ///< returns > number of affected rows < / returns >
    public int ExecuteNonQuery(string sql)
    {
        int result = 0;
        try
        {
            this.OpenConnection();
            SqlCommand comm = new SqlCommand(sql, Conn);
            result = comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return -1;
        }
        finally
        {
            this.CloseConnection();
        }
        return result;
    }
    /// <summary>
    ///Query single row and single column
    /// </summary>
    ///< param name = "SQL" > sql statement < / param >
    ///< returns > number of affected rows < / returns >
    public object ExecuteScalar(string sql)
    {
        object result = null;
        try
        {
            this.OpenConnection();
            SqlCommand comm = new SqlCommand(sql, Conn);
            result = comm.ExecuteScalar();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return null;
        }
        finally
        {
            this.CloseConnection();
        }
        return result;
    }
    /// <summary>
    ///Query multiple rows and columns
    /// </summary>
    ///< param name = "SQL" > sql statement < / param >
    ///< returns > number of affected rows < / returns >
    public SqlDataReader ExecuteReader(string sql)
    {
        SqlDataReader reader = null;
        try
        {
            this.OpenConnection();
            SqlCommand comm = new SqlCommand(sql, Conn);
            reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return null;
        }
        return reader;
    }
    /// <summary>
    ///Get adapter object
    /// </summary>
    ///< param name = "SQL" > sql statement < / param >
    ///< returns > adapter object < / returns >
    public SqlDataAdapter GetAdapter(string sql)
    {
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql, Conn);
            return adapter;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            throw;
        }
    }
}

}

Posted by koray on Fri, 18 Oct 2019 11:41:35 -0700

Hot Keywords

©2024 Programmer Group