你的位置:首页 > ASP.net教程

[ASP.net教程].NET三层架构例子超链接可以点击显示内容页面


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

首先是一个DBHelper,这个不是我自己写的,是朋友给我的

using System;using System.Data;using System.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>

View Code