.NET三层架构例子超链接可以点击显示内容页面

2018-06-22 07:32:54来源：未知阅读 ()

在研究了一个星期的三层架构写出的一个小功能，使用三层架构并实现点击新闻标题可以跳转到自己写的新闻页面。

首先是一个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"];
        }

    }
}

View Code

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; }
        }
    }

View Code

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;
      }

View Code

3、BLL层，调用DAL的数据库语句

 public static DataRowCollection GetNews()  //查询首页前10条新闻
       {           
           return NewsDAL.GetNews();
       }

View Code

4、在隐藏代码文件中调用BLL层

  public NewsModel[] model;

        public DataRowCollection drow;  //前10条新闻


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

View Code

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>

View Code

在web.config文件中添加连接数据库代码

<connectionStrings>

<add name="SQLConnectionString" connectionString="Data Source=服务器名;Initial Catalog=数据库名;Integrated Security=True"

providerName="System.Data.SqlClient" />

</connectionStrings>