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