SqlCommand

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

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

namespace TestSQL_Project
{
    public class DBPerson
    {
        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="person">要添加的用户</param>
        public static void AddPerson(Person person)
        {
            SqlConnection conn = Connection.GetSqlConnection();

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

            SqlCommand cmd = new SqlCommand(insertString, conn);

            cmd.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("@age", SqlDbType.Int));

            cmd.Parameters["@name"].Value = person.Name;
            cmd.Parameters["@age"].Value = person.Age;

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

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

           
            SqlConnection conn = Connection.GetSqlConnection();

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

            SqlCommand cmd = new SqlCommand(deleteString, conn);

            cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));

            cmd.Parameters["@id"].Value = id;

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
 
        }

        /// <summary>
        /// 更新用户
        /// </summary>
        /// <param name="person">要更新的用户</param>
        public static void UpdatePerson(Person person)
        {
            SqlConnection conn = Connection.GetSqlConnection();

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

            SqlCommand cmd = new SqlCommand(updateString, conn);

            cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("@age", SqlDbType.Int));

            cmd.Parameters["@id"].Value = person.Id;
            cmd.Parameters["@name"].Value = person.Name;
            cmd.Parameters["@age"].Value = person.Age;

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

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

            Person person = new Person();

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

            SqlCommand cmd = new SqlCommand(selectString, conn);

            cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
            cmd.Parameters["@id"].Value = id;

            SqlDataReader dr = cmd.ExecuteReader();

            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;
            }
            finally
            {
                dr.Close();

                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }  
            }

            return person;


        }

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

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

            SqlCommand cmd = new SqlCommand(selectString, conn);

            object obj = null;

            obj = cmd.ExecuteScalar();

            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)
        {
            SqlConnection conn = Connection.GetSqlConnection();

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

            SqlCommand cmd = new SqlCommand(selectString,conn);

            cmd.Parameters.Add(new SqlParameter("@id",SqlDbType.Int));
            cmd.Parameters["@id"].Value = id;

            SqlDataReader dr = cmd.ExecuteReader();

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

            return false;
        }

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

            string selectString = "select * from T_Person";

            SqlCommand cmd = new SqlCommand(selectString, conn);

            SqlDataReader dr = cmd.ExecuteReader();

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

            return false;
        }
    }
}