SqlHelper

抄至传智播客的教学视频,做个记录

 public static class SqlHelper
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
       // private static readonly string conrt = ConfigurationManager.ConnectionStrings [ "MySql" ].ConnectionString;
        public static string conrt { get; set; }

       

        /// <summary>
        /// 增删改返回整数,其它为-1
        /// </summary>
        /// <param name="sqltext">sql语句</param>
        /// <param name="cmdType">存储过程</param>
        /// <param name="pms">参数</param>
        /// <returns></returns>
        public static int ExecuteNoQuery(string sqltext,CommandType cmdType,params SqlParameter[] pms )
        {
            //建立sql连接
            using ( SqlConnection con=new SqlConnection (conrt) )
            {
                using ( SqlCommand cmd=new SqlCommand (sqltext,con) )
                {
                    cmd.CommandType = cmdType;
                    if(pms!=null)
                    {
                        cmd.Parameters.AddRange ( pms );
                    }
                    con.Open ( );
                    return cmd.ExecuteNonQuery ( );
                }
            }
        }

        /// <summary>
        /// 返回查找的数据
        /// </summary>
        /// <param name="sqltext">传入sql字符串</param>
        /// <param name="cmdType">传入的类型是存储过程还是普通字符串</param>
        /// <param name="pms">参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqltext,CommandType cmdType,params SqlParameter[] pms)
        {
            //建立sql连接
            using ( SqlConnection con = new SqlConnection ( conrt ) )
            {
                using ( SqlCommand cmd = new SqlCommand ( sqltext , con ) )
                {
                    cmd.CommandType = cmdType;
                    if ( pms != null )
                    {
                        cmd.Parameters.AddRange ( pms );
                    }
                    con.Open ( );
                    return cmd.ExecuteScalar ( );
                }
            }
        }

        public static SqlDataReader ExecuteReader(string sqltext,CommandType cmdType,params SqlParameter[] pms)
        {
            SqlConnection con = new SqlConnection ( conrt );
            using ( SqlCommand cmd=new SqlCommand (sqltext,con) )
            {
                cmd.CommandType = cmdType;
                if(pms!=null)
                {
                    cmd.Parameters.AddRange ( pms );
                }
                try
                {
                    if ( con.State == ConnectionState.Closed )
                    {
                        con.Open ( );
                    }
                    return cmd.ExecuteReader ( CommandBehavior.CloseConnection );
                }
                catch
                {

                    con.Close ( );
                    con.Dispose ( );
                   throw;
                }
            }
        }

        public static DataTable ExecuteDataTable(string sqltext,CommandType cmdType,params SqlParameter[] pms)
        {
            DataTable table = new DataTable ( );
            using ( SqlDataAdapter adapter=new SqlDataAdapter (sqltext,conrt) )
            {
                adapter.SelectCommand.CommandType = cmdType;
                if ( pms != null )
                {
                    adapter.SelectCommand.Parameters.AddRange ( pms );
                }
                adapter.Fill ( table );
            }
            return table;
        }
    }