.NET三層架構例子超鏈接可以點擊顯示內容頁面

在研究了一個星期的三層架構寫出的一個小功能，使用三層架構並實現點擊新聞標題可以跳轉到自己寫的新聞頁面。

首先是一個DBHelper，這個不是我自己寫的，是朋友給我的

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;//引入命名空間
using System.Collections.Generic;

namespace DAL
{
    /// <summary> 
    /// SqlServer數據訪問幫助類 
    /// </summary> 
    public sealed class DBHelper
    {
        //獲取資料庫連接字元串
        public static string connString = ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString;

        /// <summary>
        /// 專門用來執行增、刪、改的方法（非存儲過程）
        /// </summary>
        /// <param name="sql">SQL語句</param>
        /// <param name="para">參數數組</param>
        /// <returns>執行結果</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    string str = sql;
                    return cmd.ExecuteNonQuery();

                }
            }
            //return ExecuteNonQuery(sql, false, para);
        }

        /// <summary>
        /// 專門用來執行增、刪、改的方法
        /// </summary>
        /// <param name="sql">SQL語句</param>
        /// <param name="isStoredProcedure">是否存儲過程</param>
        /// <param name="para">參數</param>
        /// <returns>執行結果</returns>
        public static bool ExecuteNonQuery(string sql, bool isStoredProcedure, params SqlParameter[] para)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    if (isStoredProcedure)
                    {
                        //如果是存儲過程
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    if (para != null)
                    {
                        cmd.Parameters.AddRange(para);
                    }
                    //打開連接
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    int i = cmd.ExecuteNonQuery();
                    return i > 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 此方法專門用來執行sql語句，並且返回一個DataTable對象（非存儲過程）
        /// </summary>
        /// <param name="sql">參數化的sql語句(一般為含有select關鍵字的sql語句)</param>
        /// <param name="para">SqlParameter數組型的參數:如果此sql語句沒有參數則para為null;否則在調用方傳一個SqlParameter[]數組</param>
        /// <returns>DataTable格式的結果數據</returns>
        public static DataTable ExecuteSelect(string sql, params SqlParameter[] para)
        {
            return ExecuteSelect(sql, false, para);
        }

        /// <summary>
        /// 此方法專門用來執行sql語句，並且返回一個DataTable對象
        /// </summary>
        /// <param name="sql">參數化的sql語句(一般為含有select關鍵字的sql語句)</param>
        /// <param name="isStoredProcedure">標誌要調用的是否是存儲過程</param>
        /// <param name="para">SqlParameter數組型的參數:如果此sql語句沒有參數則para為null;否則在調用方傳一個SqlParameter[]數組</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteSelect(string sql, bool isStoredProcedure, params SqlParameter[] para)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, connString);
                if (isStoredProcedure)
                {
                    //如果是存儲過程
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                }
                if (para != null)
                {
                    da.SelectCommand.Parameters.AddRange(para);
                }
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 用於查詢的ExecuteReader方法（不帶存儲過程的）
        /// </summary>
        /// <param name="strSql">查詢的SQL語句</param>
        /// <param name="para">字元串格式化</param>
        /// <returns>返回SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] para)
        {
            return ExecuteReader(sql, false, para);
        }

        /// <summary>
        /// 用於查詢的ExecuteReader方法（帶存儲過程的）
        /// </summary>
        /// <param name="strSql">查詢的SQL語句</param>
        /// <param name="para">字元串格式化</param>
        /// <returns>返回SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string sql, bool isStoredProcedure, params SqlParameter[] para)
        {
            SqlDataReader reader = null;
            SqlConnection sqlConn = new SqlConnection(connString);
            try
            {
                SqlCommand sqlComm = new SqlCommand(sql, sqlConn);
                if (isStoredProcedure)
                {
                    //如果是存儲過程
                    sqlComm.CommandType = CommandType.StoredProcedure;
                }
                if (para != null)
                {
                    sqlComm.Parameters.AddRange(para);
                }
                //打開連接
                if (sqlConn.State == ConnectionState.Closed)
                {
                    sqlConn.Open();
                }
                reader = sqlComm.ExecuteReader();
                return reader;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 用於統計數據
        /// </summary>
        /// <param name="strSql">查詢語句</param>
        /// <param name="para">參數</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] para)
        {

            try
            {
                SqlConnection sqlconn = new SqlConnection(connString);
                SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
                if (para != null)
                {
                    sqlcomm.Parameters.AddRange(para);
                }
                //打開連接
                if (sqlconn.State == ConnectionState.Closed)
                {
                    sqlconn.Open();
                }
                return sqlcomm.ExecuteScalar();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 返回DataTable對象（非存儲過程）
        /// </summary>
        /// <param name="strSql">以Select語句開頭的查詢語句</param>
        /// <param name="para">參數</param>
        /// <returns>返回一個DataTable對象</returns>
        public static DataTable GetTable(string sql, params SqlParameter[] para)
        {
            return GetTable(sql, false, para);
        }

        /// <summary>
        /// 返回DataTable對象
        /// </summary>
        /// <param name="strSql">以Select語句開頭的查詢語句</param>
        /// <param name="para">參數</param>
        /// <returns>返回一個DataTable對象</returns>
        public static DataTable GetTable(string sql, bool isStoredProcedure, params SqlParameter[] para)
        {
            try
            {
                SqlDataAdapter sqlDA = new SqlDataAdapter(sql, connString);
                DataTable dt = new DataTable();
                //如果是存儲過程
                if (isStoredProcedure)
                {
                    sqlDA.SelectCommand.CommandType = CommandType.StoredProcedure;
                }
                //如果參數化不為空
                if (para != null)
                {
                    sqlDA.SelectCommand.Parameters.AddRange(para);
                }
                sqlDA.Fill(dt);//如果這裡出錯一般就是SQL語句的錯誤
                return dt;
            }
            catch
            {
                throw;
            }
        }


        /// <summary>
        /// 主要執行查詢操作
        /// </summary>
        /// <param name="sql">執行的sql語句</param>
        /// <param name="parameters">參數數組</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);

                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }

        public static bool TranSql(List<string> sqlList)
        {
            //實例化資料庫連接對象
            SqlConnection sqlconn = new SqlConnection(connString);
            sqlconn.Open();
            SqlTransaction sqltran = sqlconn.BeginTransaction();
            try
            {
                foreach (string sql in sqlList)
                {
                    SqlCommand sqlcomm = new SqlCommand(sql, sqlconn, sqltran);
                    sqlcomm.ExecuteNonQuery();
                }
                sqltran.Commit();
                sqlconn.Close();
                return true;
            }
            catch
            {
                sqltran.Rollback();
                sqlconn.Close();
                return false;
            }


        }
        public static bool isConnectionOpen(SqlConnection connection)
        {
            if (connection.State == System.Data.ConnectionState.Open)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 利用sql語句查詢數據集
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            bool lastState = isConnectionOpen(conn);
            if (lastState == false)

                conn.Open();

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.Fill(ds, "table");

            if (lastState == false)
                conn.Close();
            return ds.Tables["table"];
        }

    }
}

1、model層，封裝欄位

 public   class NewsModel
    {
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }
        private string title;

        public string Title
        {
            get { return title; }
            set { title = value; }
        }
        private string content;

        public string Content
        {
            get { return content; }
            set { content = value; }
        }
        //private string categories;

        //public string Categories
        //{
        //    get { return categories; }
        //    set { categories = value; }
        //}
        private string type;

        public string Type
        {
            get { return type; }
            set { type = value; }
        }
        private string author;

        public string Author
        {
            get { return author; }
            set { author = value; }
        }
        private DateTime issueDate;

        public DateTime IssueDate
        {
            get { return issueDate; }
            set { issueDate = value; }
        }
    }

2、DAL層，資料庫語句

public static DataRowCollection GetNews()    //首頁前10條新聞
      {
          string selectSql = "select top 10* from News order by issueDate desc ";
          DataTable lb = DBHelper.GetDataTable(selectSql);
          return lb.Rows;
      }

3、BLL層，調用DAL的資料庫語句

 public static DataRowCollection GetNews()  //查詢首頁前10條新聞
       {           
           return NewsDAL.GetNews();
       }

4、在隱藏代碼文件中調用BLL層

  public NewsModel[] model;

        public DataRowCollection drow;  //前10條新聞


protected void Page_Load(object sender, EventArgs e)
        {
            drow = NewsBLL.GetNews();
                
        }

5、在aspx頁面中，在<a>標簽中添加代碼

  <%--右上新聞框--%>
            <div id="newRight">
              
                <div class="contentRight" style="padding-left: 20px; padding-top: 20px;">
                    
                     <%  if (drow != null)
    {
        foreach (var line in drow)
        {
            System.Data.DataRow dr = (System.Data.DataRow)line;
                            %>
                                <a href="newsContent.aspx?id=<%=dr["id"].ToString() %>"><%=dr["title"].ToString() %></a><span class="datetime"><%=dr["issuedate"].ToString() %></span><br><br>
                            <%}
    } %>    
                
                           
                </div>
                   
            </div>

在web.config文件中添加連接資料庫代碼

<connectionStrings>

<add name="SQLConnectionString" connectionString="Data Source=伺服器名;Initial Catalog=資料庫名;Integrated Security=True"

providerName="System.Data.SqlClient" />

</connectionStrings>