• .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"];
            }
    
        }
    }
    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
  • 相关阅读:
    JavaScript
    并发编程基础
    基于 TCP & UDP 协议的 socket 通信
    struct 模块 & subprocess 模块
    Python中的异常处理
    网络编程基础
    Json 模块补充
    冒泡排序
    OOP 反射 & 元类
    OOP 内置函数
  • 原文地址:https://www.cnblogs.com/z-s-q/p/5708376.html
Copyright © 2020-2023  润新知