使用SqlHelper

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace TestSQL_Project
{
    public class DBPersonSqlHelper
    {
        
        private static  readonly string  connString = "Data Source=(local);Database=MyTest;Uid=sa;Pwd=123456";
        
        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="person">要添加的用户</param>
        public static void AddPerson(Person person)
        {

            string insertString = "insert into T_Person(p_name,p_age) values(@name,@age)";

            SqlParameter[] cmdParameter = {
            new SqlParameter("@name", SqlDbType.VarChar),
            new SqlParameter("@age", SqlDbType.Int)
            };

            cmdParameter[0].Value = person.Name;
            cmdParameter[1].Value = person.Age;

            try
            {
                SqlHelper.ExecuteNonQuery(new SqlConnection(connString), CommandType.Text, insertString, cmdParameter);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }

        /// <summary>
        /// 通过用户ID删除用户
        /// </summary>
        /// <param name="id">要删除的用户的ID</param>
        public static void DeletePersonByID(int id)
        {

            string deleteString = "delete from T_Person where p_id = @id";

            SqlParameter[] cmdParameter = {
                new SqlParameter("@id", SqlDbType.Int)
            };

            cmdParameter[0].Value = id;

            try
            {
                SqlHelper.ExecuteNonQuery(new SqlConnection(connString), CommandType.Text, deleteString, cmdParameter);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

        }

        /// <summary>
        /// 更新用户
        /// </summary>
        /// <param name="person">要更新的用户</param>
        public static void UpdatePerson(Person person)
        {

            string updateString = "update T_Person set p_name = @name,p_age = @age where p_id = @id";

            SqlParameter[] sqlParameter = {
                new SqlParameter("@id",SqlDbType.Int),
                new SqlParameter("@name",SqlDbType.VarChar),
                new SqlParameter("@age", SqlDbType.Int)
            };

            sqlParameter[0].Value = person.Id;
            sqlParameter[1].Value = person.Name;
            sqlParameter[2].Value = person.Age;

            try
            {
                SqlHelper.ExecuteNonQuery(new SqlConnection(connString), CommandType.Text, updateString, sqlParameter);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }

        /// <summary>
        /// 通过id取得用户
        /// </summary>
        /// <param name="id">要取得的用户的id</param>
        /// <returns>取得的用户</returns>
        public static Person GetPersonByID(int id)
        {
            if (!PersonIsExistById(id))
            {
                return null;
            }

            Person person = new Person();

            string selectString = "select * from T_Person where p_id = @id";

            SqlParameter[] sqlPara = {
                new SqlParameter("@id",SqlDbType.Int)
            };

            sqlPara[0].Value = id;


            SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, selectString, sqlPara);

            try
            {
                while (dr.Read())
                {
                    person.Id = Convert.ToInt32(dr["p_id"]);
                    person.Name = dr["p_name"].ToString();
                    person.Age = Convert.ToInt32(dr["p_age"]);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                return null;
            }

            return person;


        }

        /// <summary>
        /// 取得最大的id
        /// </summary>
        /// <returns>如果返回-1表示数据库中没有数据，否则返回的就是最大的id</returns>
        public static int GetMaxPersonID()
        {

            string selectString = "select max(p_id) as maxID from T_Person";

            object obj = null;

            obj = SqlHelper.ExecuteScalar(new SqlConnection(connString), CommandType.Text, selectString, null);

            if (!TableHasRecord())
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 根据id判断用户是否存在
        /// </summary>
        /// <param name="id">要判断的用户的id</param>
        /// <returns>返回true表示该用户存在，返回false表示该用户不存在</returns>
        public static bool PersonIsExistById(int id)
        {

            string selectString = "select * from T_Person where p_id = @id";

            SqlParameter[] sqlPara = {
                new SqlParameter("@id",SqlDbType.Int)
            };

            sqlPara[0].Value = id;

            SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, selectString, sqlPara);

            while (dr.Read())
            {
                return true;
            }

            return false;
        }

        /// <summary>
        /// 判断表中是否有数据
        /// </summary>
        /// <returns>返回true表示有数据，返回false表示没有数据</returns>
        public static bool TableHasRecord()
        {

            string selectString = "select * from T_Person";

            SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, selectString, null);

            while (dr.Read())
            {
                return true;
            }

            return false;
        }

        /// <summary>
        /// 取得所有用户
        /// </summary>
        /// <returns>所有用户的集合</returns>
        public static List<Person> GetManyPersons()
        {
            string selectString = "select * from T_Person";

            List<Person> lstPerson = new List<Person>();

            Person person = null;

            DataSet ds = SqlHelper.ExecuteDataSet(connString, CommandType.Text, selectString, null);

            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {

                    person = new Person();

                    person.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["p_id"].ToString());
                    person.Name = ds.Tables[0].Rows[0]["p_name"].ToString();
                    person.Age = Convert.ToInt32(ds.Tables[0].Rows[0]["p_age"].ToString());

                    lstPerson.Add(person);
                }
            }

            return lstPerson;
        }

    }
}