asp.net三层架构

三层架构

 

DAL数据访问层: BooksRatingService各种service类定义各种使用sqlhelper定义数据库的操作方法将sql字段加载进去各种具体的数据操作的方法

BLL逻辑业务层：BookManager各种manager类调用DAL层类方法定义对model类的操作方法

models实体类：定义实体类 设置 获得方法，数据传输的中间媒介

web层：视图文件，布局 脚本 样式，aspx文件布局+aspx.cs文件进行数据与控件的交互

DAL中的sqlhelper定义数据库操作方法

Bin文件夹中引用dll文件

 

问题：

为何需要BLL 层?不直接调用DAL中的方法，

 

model类代码：作为数据操作媒介

private DateTime createdTime;

public DateTime CreatedTime

        {

            get { return createdTime; }

            set { createdTime = value; }

        }

 

 

 

 

 

 

 

 

 

 

SqlHelper类代码：定义数据操作方法 

Method:readData DataSet Query sqlcmd

Reader_data:ExecuteReader()

DataSet:ExecuteDataset()

Query_Data: ExecuteNonQuery(),

insertData: ExecuteScalar()

Sqlcmd_execute: PrepareCommand()

 

//Databaseconnection strings

public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["BookShop"].ConnectionString;

ExecuteNonQuery

///<summary>

        ///执行SqlServer存储过程

        ///注意：不能执行有out 参数的存储过程

        ///</summary>

        ///<paramname="connectionString">连接字符串</param>

        ///<paramname="spName">存储过程名</param>

        ///<paramname="parameterValues">对象参数</param>

        ///<returns>受影响的行数</returns>

    public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)

        {

            using (SqlConnection conn = new SqlConnection(connectionString))

            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, conn, spName,parameterValues);

                int val =cmd.ExecuteNonQuery();

                return val;

            }

        }

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ExecuteReader

///<summary>

        ///  执行sql命令

        ///</summary>

        ///<paramname="connectionString">连接字符串</param>

        ///<paramname="commandType">命令类型</param>

        ///<paramname="commandText">sql语句/参数化sql语句/存储过程名</param>

        ///<paramname="commandParameters">参数</param>

        ///<returns>SqlDataReader 对象</returns>

        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[]commandParameters)

        {

 

            SqlConnection conn = new SqlConnection(connectionString);

            try

            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd,commandType, conn, commandText, commandParameters);

                SqlDataReader rdr =cmd.ExecuteReader(CommandBehavior.CloseConnection);

                return rdr;

            }

            catch

            {

                conn.Close();

                throw;

            }

        }

 

 

 

ExecuteDataset

//setthe data to the dataAdapter

public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)

        {

            using (SqlConnection conn = new SqlConnection(connectionString))

            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, conn,spName, parameterValues);

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))

                {

                    DataSet ds = new DataSet();

                    da.Fill(ds);

                    return ds;

                }

            }

        }

 

 

ExecuteScalar

///<summary>

        ///执行Sql命令

        ///</summary>

        ///<paramname="connectionString">连接字符串</param>

        ///<paramname="commandType">命令类型</param>

        ///<param name="commandText">sql语句/参数化sql语句/存储过程名</param>

        ///<paramname="commandParameters">参数</param>

        ///<returns>执行结果对象</returns>

        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[]commandParameters)

        {

            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))

            {

                PrepareCommand(cmd,commandType, conn, commandText, commandParameters);

                object val =cmd.ExecuteScalar();

                return val;

            }

        }

 

 

 

 

 

 

PrepareCommand

///<summary>

        ///设置一个等待执行的SqlCommand对象

        ///</summary>

        ///<paramname="cmd">SqlCommand 对象，不允许空对象</param>

        ///<paramname="conn">SqlConnection 对象，不允许空对象</param>

        ///<paramname="commandText">Sql 语句</param>

        ///<paramname="cmdParms">SqlParameters  对象,允许为空对象</param>

        private static void PrepareCommand(SqlCommand cmd, CommandType commandType, SqlConnection conn, string commandText, SqlParameter[] cmdParms)

        {

            //打开连接

            if (conn.State != ConnectionState.Open)

                conn.Open();

 

            //设置SqlCommand对象

            cmd.Connection = conn;

            cmd.CommandText = commandText;

            cmd.CommandType = commandType;

 

            if (cmdParms != null)

            {

                foreach (SqlParameter parm in cmdParms)

                    cmd.Parameters.Add(parm);

            }

        }

 

 

 

 

 

 

 

 

 

 

 

DAL类代码：cast datas to models

设置/获得参数，传入sqlhelper方法 得到数据映射到实体类 用实体类对数据进行具体操作

Method:

get list of items:GetBookRatings(int bookId),GetBookRatings(string safeSql)

insert Data: AddBookRating()

get Data: GetBookRatings()

 

BooksRatingService:

//database connection strings

string connection =ConfigurationManager.ConnectionStrings["BookShop"].ConnectionString;

AddBookRating

//method: insert data by casting datato models

        public bool AddBookRating(BookRatings bookrating)

        {

            string sql =

                "INSERTBookRatings (BookId,UserId,Rating,Comment)" +

                "VALUES(@BookId, @UserId, @Rating, @Comment)";

 

            sql += " ; SELECT@@IDENTITY";

            SqlParameter[] para = new SqlParameter[]

                {

                    new SqlParameter("@BookId",bookrating.BookId),

                    new SqlParameter("@UserId",bookrating.User.Id),

                    new SqlParameter("@Rating",bookrating.Rating),

                    new SqlParameter("@Comment",bookrating.Comment)

                };

 

            bookrating.Id = Conve