在研究了一个星期的三层架构写出的一个小功能,使用三层架构并实现点击新闻标题可以跳转到自己写的新闻页面。
首先是一个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>