c#开发数据库第三章上级4

来源：互联网发布：淘宝3c证书编号在哪里编辑：程序博客网时间：2024/05/16 15:58
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;using System.Data;namespace WindowsFormsApplication2{    class DBHelper    {        private string connString = @"Data Source=.;Initial Catalog=GoodsMenu;Integrated Security=True";        // 数据库连接 Connection 对象        private SqlConnection connection;        /// <summary>        /// Connection对象        /// </summary>        public SqlConnection Connection        {            get            {                if (connection == null)                {                    connection = new SqlConnection(connString);                }                return connection;            }        }        /// <summary>        /// 打开数据库连接        /// </summary>        public void OpenConnection()        {            if (Connection.State == ConnectionState.Closed)            {                Connection.Open();            }            else if (Connection.State == ConnectionState.Broken)            {                Connection.Close();                Connection.Open();            }        }        /// <summary>        /// 关闭数据库连接        /// </summary>        public void CloseConnection()        {            if (Connection.State == ConnectionState.Open || Connection.State == ConnectionState.Broken)            {                Connection.Close();            }        }    }} private void Form1_Load(object sender, EventArgs e)        {            AddUser();                   }        DBHelper db = new DBHelper();        public void AddUser()        {            string strsql=("SELECT  [StudentNo],[StudentName],[Sex],[GradeId] FROM [Student]");            try            {                SqlCommand comm = new SqlCommand(strsql, db.Connection);                db.OpenConnection();                SqlDataReader reader = comm.ExecuteReader();                while (reader.Read())                {                    string studentNo = reader["studentNo"].ToString();                    string studentName = reader["studentName"].ToString();                    string sex = reader["Sex"].ToString();                    int gradeId = Convert.ToInt32(reader["GradeId"]);                    string grade = string.Empty;                    if (gradeId == 1)                    {                        grade = "S1";                    }                    else if (gradeId == 2)                    {                        grade = "S2";                    }                    else                    {                        grade = "Y2";                    }                    ListViewItem item = new ListViewItem(studentNo);                    item.SubItems.AddRange(new string[] { studentName, sex, grade });                    this.listView1.Items.Add(item);                }                reader.Close();            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);            }            finally            {                db.CloseConnection();            }      }        public string caption = "输入提示";        public void search()        {            if (listView1.Items.Count > 0)            {                listView1.Items.Clear();            }                    StringBuilder sb = new StringBuilder();            sb.AppendLine("SELECT  [StudentNo],[StudentName],[Sex],[GradeId] FROM [Student]");            sb.AppendFormat("  WHERE  [StudentName] LIKE '%{0}%'", this.textBox1.Text.Trim());            SqlCommand comm = new SqlCommand(sb.ToString(), db.Connection);            db.OpenConnection();            SqlDataReader reader = comm.ExecuteReader();            try{            if (!reader.HasRows)            {                MessageBox.Show("没有要查找的记录!", caption, MessageBoxButtons. OK, MessageBoxIcon.Information);            }            else            {                                 while (reader.Read())                {                    string studentNo = reader["studentNo"].ToString();                    string studentName = reader["studentName"].ToString();                    string sex = reader["Sex"].ToString();                    int gradeId = Convert.ToInt32(reader["GradeId"]);                    string grade = string.Empty;                    if (gradeId == 1)                    {                        grade = "S1";                    }                    else if (gradeId == 2)                    {                        grade = "S2";                    }                    else                    {                        grade = "Y2";                    }                    ListViewItem item = new ListViewItem(studentNo);                    item.SubItems.AddRange(new string[] { studentName, sex, grade });                    this.listView1.Items.Add(item);                }                reader.Close();            }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);            }            finally            {                db.CloseConnection();            }                    }        private void button4_Click(object sender, EventArgs e)        {            search();        }                   }}
0 0