C#運算元據庫進行簡單的增加修改操作

Sweetheartman發表於2017-05-07

1.將專案下的App.config檔案開啟,修改裡面的內容,儲存連線字串

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add connectionString="Data Source=LENOVO-PC;Initial Catalog=MyDatabase;Integrated Security=True" name="strCon" />
  </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6" />
    </startup>
</configuration>

2.右鍵專案下的引用,新增引用,程式集System.Configuration,確定。

3.封裝SqlHelper類,用於資料庫增刪改查操作,一定新增引用System.Configuration

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
namespace 大專案
{
    public class SqlHelper
    {
        private static readonly string str = ConfigurationManager.ConnectionStrings["strCon"].ConnectionString;
        public static int ExecuteNonQuery(string sql,params SqlParameter[] ps)
        {
            using (SqlConnection con=new SqlConnection(str))
            {
                using (SqlCommand cmd=new SqlCommand(sql,con))
                {
                    con.Open();
                    cmd.Parameters.AddRange(ps);
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        public static object ExecuteScalar(string sql, params SqlParameter[] ps)
        {
            using (SqlConnection con =new SqlConnection(str))
            {
                using (SqlCommand cmd=new SqlCommand(sql,con))
                {
                    con.Open();
                    cmd.Parameters.AddRange(ps);
                    return cmd.ExecuteScalar();
                }
            }
        } 
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] ps)
        {
            SqlConnection con = new SqlConnection(str);
            using (SqlCommand cmd=new SqlCommand(sql,con))
            {

                cmd.Parameters.AddRange(ps);
                try
                {
                    con.Open();
                    return cmd.ExecuteReader();
                }
                catch (Exception ex)
                {
                    con.Close();
                    con.Dispose();
                    throw ex;
                }
            }
        }
    }
}

4.封裝Student類,用於資料庫轉物件

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 大專案
{
    public class Student
    {
        //tsid, tsname, tsgender, tsadress, tsage
        private int _tSId;
        private string _tSName;
        private char _tSGender;
        private string _tSAdress;
        private int _tSAge;

        public int TSId
        {
            get
            {
                return _tSId;
            }

            set
            {
                _tSId = value;
            }
        }

        public string TSName
        {
            get
            {
                return _tSName;
            }

            set
            {
                _tSName = value;
            }
        }

        public char TSGender
        {
            get
            {
                return _tSGender;
            }

            set
            {
                _tSGender = value;
            }
        }

        public string TSAdress
        {
            get
            {
                return _tSAdress;
            }

            set
            {
                _tSAdress = value;
            }
        }

        public int TSAge
        {
            get
            {
                return _tSAge;
            }

            set
            {
                _tSAge = value;
            }
        }
    }
}

5.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;
using System.Data.SqlClient;
namespace 大專案
{
    public partial class Form1 : Form
    {
        MyEventArgs mea = new MyEventArgs();//自定義類,儲存傳值的引數(標識和物件)
        public event EventHandler evt;//宣告事件
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadStudent();
        }
        /// <summary>
        /// 窗體載入時,顯示資料庫中的資料
        /// </summary>
        private void LoadStudent()
        {
            List<Student> list = new List<Student>();
            string sql = "select tsid, tsname, tsgender, tsadress, tsage from student1";
            Student stu = null;
            using (SqlDataReader reader =SqlHelper.ExecuteReader(sql))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        stu = ReaderDBtoStudent(reader);
                        list.Add(stu);
                    }
                }
            }
            dgv.AutoGenerateColumns = false;
            dgv.DataSource = list;
            //dgv.SelectedRows[0].Selected = false;
        }
        /// <summary>
        /// 封裝資料庫轉物件的方法
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private Student ReaderDBtoStudent(SqlDataReader reader)
        {
            Student stu = new Student();
            stu.TSId = Convert.ToInt32(reader["tsid"]);
            stu.TSName = reader["tsname"].ToString();
            stu.TSGender = Convert.ToBoolean(reader["tsgender"]) ? '男':'女';
            stu.TSAdress = reader["tsadress"].ToString();
            stu.TSAge = Convert.ToInt32(reader["tsage"]);
            return stu;
        }
        /// <summary>
        /// 新增按鈕
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnAdd_Click(object sender, EventArgs e)
        {
            ShowStudentAddAndUpdate(1);
        }
        /// <summary>
        /// 修改按鈕
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (dgv.SelectedRows.Count>0)//SelectedRows表示選中的行的集合,選一行就是0,選兩行就是【0,1】,以此類推
            {
                Student stu = new Student();
                stu.TSAdress = dgv.SelectedRows[0].Cells[4].Value.ToString();//地址
                stu.TSAge = Convert.ToInt32(dgv.SelectedRows[0].Cells[3].Value);//年齡
                stu.TSGender = Convert.ToChar(dgv.SelectedRows[0].Cells[2].Value);//性別
                stu.TSId = Convert.ToInt32(dgv.SelectedRows[0].Cells[0].Value);//id
                stu.TSName = dgv.SelectedRows[0].Cells[1].Value.ToString();//姓名
                mea.obj = stu;//把物件存起來,傳值的時候用
                ShowStudentAddAndUpdate(2);
            }
            else
            {
                MessageBox.Show("請先選擇要修改的行");
            }
        }
        /// <summary>
        /// 封裝的顯示子窗體的方法
        /// </summary>
        /// <param name="p"></param>
        private void ShowStudentAddAndUpdate(int p)
        {
            StudentAddAndUpdate sau = new StudentAddAndUpdate();
            this.evt += new EventHandler(sau.SetText);//給這個方法註冊事件,
            //意思就是說執行這個方法就會觸發該事件並執行相應的事件處理方法
            mea.Temp = p;//將標識存起來
            if (this.evt!=null)
            {
                this.evt(this,mea);//觸發事件時發生,引數this表示誰觸發的事件,mea表示向事件處理方法傳遞引數
                sau.FormClosed += new FormClosedEventHandler(sau_FormClosed);//重新整理,重新載入
                sau.ShowDialog();
            }

        }
        /// <summary>
        /// 重新整理,點選關閉按鈕時,重新載入新增的或者修改以後的資料
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void sau_FormClosed(object sender, FormClosedEventArgs e)
        {
            LoadStudent();
        }
    }
}

6.Form窗體
這裡寫圖片描述

相關文章