C#使用SQL Server工具类

class OperateSQL
    {
        private static SqlConnection _conn;
       
        private const string ConnectString = @"server=IP\SQLEXPRESS;uid=XXX;pwd=XXX;database=数据库名";

        /// <summary>
        /// 连接数据库
        /// 知识点：Connection
        /// </summary>
        /// <param name="ConnectString"></param>
        /// <returns></returns>
        public static bool ConnectSQL()
        {
            bool isConnect = true;

            try
            {
                _conn = new SqlConnection(ConnectString);
                _conn.Open();
            }
            catch (Exception)
            {
                isConnect = false;
            }

            return isConnect;
        }

        /// <summary>
        /// 增
        /// 知识点：Command
        /// </summary>
        /// <param name="student"></param>
        public static void InsertStudent(Student student)
        {
            SqlCommand com = new SqlCommand();
            com.Connection = _conn;
            com.CommandText = "insert into Student(name,id,sex) values('" + student.Name+"','"+ student.ID+ "','"+student.SEX+"')";
            com.ExecuteNonQuery();  
        }

        /// <summary>
        /// 删
        /// 知识点：Command
        /// </summary>
        /// <param name="name"></param>
        public static void DeleteStudent(string name)
        {
            SqlCommand com = new SqlCommand();
            com.Connection = _conn;
            com.CommandText = "delete from student where name =" + "'" + name + "'";
            com.ExecuteNonQuery(); 
        }

        /// <summary>
        /// 改
        /// 知识点:存储过程
        /// </summary>
        /// <param name="stu"></param>
        public static void UpdateStudent(Student stu)
        {
            SqlCommand com = new SqlCommand();
            com.Connection = _conn;
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "SQL_UpdateStudent";

            //Input parameter 1
            SqlParameter inparm1 = com.Parameters.Add("@StudentName",SqlDbType.NVarChar);
            inparm1.Direction = ParameterDirection.Input;
            inparm1.Value = stu.Name;

            //Input parameter 2
            SqlParameter inparm2 = com.Parameters.Add("@StudentID", SqlDbType.NChar);
            inparm2.Direction = ParameterDirection.Input;
            inparm2.Value = stu.ID;

            //Input parameter 3
            SqlParameter inparm3 = com.Parameters.Add("@StudentSex", SqlDbType.NVarChar);
            inparm3.Direction = ParameterDirection.Input;
            inparm3.Value = stu.SEX;

            com.ExecuteNonQuery(); 
        }

        /// <summary>
        /// 查
        /// 知识点：DataReader
        /// </summary>
        /// <returns></returns>
        public static Student SelectStudent(string name)
        {
            Student stu = new Student();

            SqlCommand com = new SqlCommand();
            com.Connection = _conn;
            com.CommandText = "select * from student where name = " + "'" + name + "'";

            SqlDataReader rd = com.ExecuteReader();
           
            while(rd.Read())
            {
                stu.Name = rd.GetString(0);
                stu.ID = rd.GetString(1);
                stu.SEX = rd.GetString(2);
            }
            rd.Close();

            return stu;
        }

        /// <summary>
        /// 查
        /// 知识点：DataAdapter，DataSet
        /// </summary>
        /// <returns></returns>
        public static Student[] SelectAllStudents()
        {
            List<Student> stuList = new List<Student>();

            try
            {
                SqlCommand com = new SqlCommand();
                com.Connection = _conn;
                com.CommandText = "select * from student";

                SqlDataAdapter myDA = new SqlDataAdapter();
                myDA.SelectCommand = com;
                DataSet myDS = new DataSet();
                myDA.Fill(myDS, "Student");

                for (int i = 0; i < myDS.Tables["Student"].Rows.Count; i++)
                {
                    Student stu = new Student();
                    stu.Name = myDS.Tables["Student"].Rows[i].ItemArray[0].ToString();
                    stu.ID = myDS.Tables["Student"].Rows[i].ItemArray[1].ToString();
                    stu.SEX = myDS.Tables["Student"].Rows[i].ItemArray[2].ToString();

                    stuList.Add(stu);
                }
            }
            catch (Exception)
            {
                return null;
            }

            return stuList.ToArray();
        }

        /// <summary>
        /// 知识点：取得数据源DataSet,用于绑定DataGridView。
        /// </summary>
        /// <returns></returns>
        public static DataSet SelectStudents()
        {
            List<Student> stuList = new List<Student>();

            try
            {
                SqlCommand com = new SqlCommand();
                com.Connection = _conn;
                com.CommandText = "select * from student";

                SqlDataAdapter myDA = new SqlDataAdapter();
                myDA.SelectCommand = com;
                DataSet myDS = new DataSet();
                myDA.Fill(myDS, "Student");

                return myDS;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 删除连接。
        /// </summary>
        public static void CloseConnect()
        {
            _conn.Close();
        }
    }