牛腩新闻发布系统综合笔记（二）

包括：重构SQLHelper和前端界面的实现

第十四讲-重构SQLHelper类：    

   //重构后的SQLHelper

using System.Configuration;

namespace DAL

{

public class SQLHelper

   
   

    
            {
            private SqlConnection conn=null;
            private SqlCommand cmd=null;
            private SqlDataReader=null;
            public SQLHelper()
            {
                string connStr=ConfigurationManager.ConnectionString["connStr"];
                SqlConnection conn=new SqlConnection(connStr);
            }
            private SqlConnection GetConn()
            {
                if(conn.State==ConnectionState.Closed)
                {
                    conn.Open();
                }    
                return conn;
            }
            //执行不带参数的增删改SQL语句或存储过程
            public int ExecuteNonQuery(string cmdText，CommandType ct)
            {
                int res;
                try
                {
                   cmd=new SqlCommand(cmdText,GetConn());            
   
   
   
   

    
    		   cmd.CommandType=ct;
   
   
   
   

    
    		   res=cmd.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if(conn.State==ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                return res;
            }
   
   
   
   

    
    		 //执行带参数的增删改SQL语句或存储过程
   
   
   
   

    
    	   
    
    	 public int ExecuteNonQuery(string cmdText,SqlParameter[] paras,CommandType ct)
   
   
   
   

    
    		{
   
   
   
   

    
    			int res;
   
   
   
   

    
    			using (cmd=new SqlCommand(cmdText,GetConn()))
   
   
   
   

    
    			{
   
   
   
   

    
    				cmd.CommandType=ct;
   
   
   
   

    
    				cmd.Parameters.AddRange(paras);
   
   
   
   

    
    				res=cmd.ExecuteNonQuery();
   
   
   
   

    
    			}
   
   
   
   

    
    			return res;
   
   
   
   

    
    		}
   
   
    
    

     
     		//执行不带参数的SQL查询语句
    
    

   
   

    
    		public DataTable ExecuteQuery(string cmdText,CommandType ct)
   
   

   
   

    
    		{
   
   

   
   

    
    			DataTable dt=new DataTable();
   
   

   
   

    
    			cmd = new SqlCommand(cmdText,GetConn())
   
   

   
   

    
    			cmd.CommandType=ct;
   
   

   
   

    
    			using (sdr=cmd.ExecuteReader(CommandBehavior.CloseConnection))
   
   

   
   

    
    			{
   
   

   
   

    
    				dt.Load(sdr);
   
   

   
   

    
    			}
   
   

   
   

    
    			return dt;
   
   

   
   

    
    		}
   
   

   
   

    
    		//执行带参数的SQL查询语句
   
   

   
   


    
    
     
     

      
                      public DataTable ExecuteQuery(string cmdText,SqlParameter[] paras,CommandType ct)
     
     
     
     

      
      		{
     
     
     
     

      
      			DataTable dt=new DataTable();
     
     
     
     

      
      			cmd = new SqlCommand(cmdText,GetConn())
     
     
     
     

      
      			cmd.CommandType=ct;
     
     
     
     

      
      			cmd.Parameters.AddRange(paras);
     
     
     
     

      
      			using (sdr=cmd.ExecuteReader(CommandBehavior.CloseConnection))
     
     
     
     

      
      			{
     
     
     
     

      
      				dt.Load(sdr);
     
     
     
     

      
      			}
     
     
     
     

      
      			return dt;
     
     
     
     

      
      		}          
     
     
}
    }
   
   

   
   

    
    该进：增加了存储过程的支持
   
   

   
   

    
    关键代码：cmd.CommandType=CommandType.StoredProcedure;
   
   

   
   

    
    第十五讲-编写剩下的存储过程
   
   

   
   

    
    	--取出十条热点新闻
   
   

   
   

    
    	create procedure news_SelectHotNews
   
   

   
   

    
    	as
   
   

   
   

    
    	begin
   
   

   
   

    
    	 select top 10 n.id, n.title, n.createTime,c.[name],count(com.id) as comCount
   
   

   
   

    
    	 from news n
   
   

   
   
 
    
    	 inner join category c on n.caId=c.Id
   
   

   
   

    
    	 inner join comment com on com newsId=n.Id
   
   

   
   

    
    	 group by  n.id,n.title,n.createTime,c.[name]
   
   

   
   

    
    	 order by comCount desc
   
   

   
   

    
    

   
   

   
   

    
    	--根据类别ID取出该类别下的所有新闻
   
   

   
   

    
    	create procedure news_selectByCaId
   
   

   
   

    
    	@caid int
   
   

   
   

    
    	as 
   
   

   
   

    
    	begin
   
   

   
   

    
    		select n.id,n.title,n.createTime,c.[name] from news n
   
   

   
   

    
    		inner join category c on n.caId=c.id and n.caId=@caid
   
   

   
   

    
    		order by n.createTime desc
   
   

   
   

    
    	end
   
   

   
   

    
    	go
   
   

   
   

    
    	exec news_selectByCaId 10
   
   

   
   

    
    	--根据内容搜索新闻
   
   

   
   

    
    	create procedure news_selectByContent
   
   

   
   

    
    	@content varchar(1000)
   
   

   
   

    
    	as
   
   

   
   

    
    	begin
   
   

   
   

    
    		select top 10 n.id ,n.title,n.createTime,c.[name] from news n
   
   

   
   

    
    		inner join category c on n.caId=c.id
   
   

   
   

    
    	        where n.content like '%'+@content+'%'
   
   
		order by n.createTime desc


   
   

    
    	end
   
   

   
   

    
    	go 
   
   

   
   

    
    	exec news_selectByTitlee 's'
   
   
前端界面设计：

第十八讲：CSS视频网站http://learing.artech.cn
Css+javascript需要测试的浏览器ie6,ie7,ie8,chrome,opera,firefox
         前端设计工具软件：
                  IETester:包含ie6,ie7,ie8
                   fireBug
调试javascript+css
第二十讲：css核心内容
         表主流->盒子模型->浮动->定位
Css：    Clear：left //清楚左边的浮动
                   Margin:5px auto 5px auto  //元素左右居中
                   Vertical-align：middle     //input元素垂直居中
                   Line-height：*px          //元素内文本居中
                   Border-collapse:collapse        /细线表格
                   Text-indent：2em            //段落首行缩进两个字符
                  //相对定位,并向上移动15像素
                   Position:relative;
                   Top:-50px;
Javascript： 自动读秒，完成后返回首页
                   Var i=5;
                   Var intervalid;
                   intervalid=setInterval(“fun()”,1000);
                   Function fun(){
                            if(i==0){
         		Window.location.href=http://www.baidu.com;
        	       ClearInterval(intervalid);
}
         Document.getElementById(“mes”).innerHTML(i);
         i--;
}
五图象圆角框做法