三层架构
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