欢迎使用CSDN-markdown编辑器

三层架构

DAL数据访问层: BooksRatingService各种service类定义 各种使用sqlhelper定义数据库的操作方法将sql字段加载进去各种具体的数据操作的方法
BLL逻辑业务层：BookManager各种manager类调用DAL层类方法定义对model类的操作方法
models实体类：定义实体类 设置 获得方法，数据传输的中间媒介
web层：视图文件，布局 脚本 样式，aspx文件布局+aspx.cs文件进行数据与控件的交互
DAL中的sqlhelper定义数据库操作方法
Bin文件夹中引用dll文件

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()

//Database connection strings
public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings[“BookShop”].ConnectionString;
ExecuteNonQuery
///
/// 执行Sql Server存储过程
/// 注意：不能执行有out 参数的存储过程
///
/// 连接字符串
/// 存储过程名
/// 对象参数
/// 受影响的行数
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
///
/// 执行sql命令
///
/// 连接字符串
/// 命令类型
/// sql语句/参数化sql语句/存储过程名
/// 参数
/// SqlDataReader 对象
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
//set the 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
///
/// 执行Sql 命令
///
/// 连接字符串
/// 命令类型
/// sql语句/参数化sql语句/存储过程名
/// 参数
/// 执行结果对象
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
///
/// 设置一个等待执行的SqlCommand对象
///
/// SqlCommand 对象，不允许空对象
/// SqlConnection 对象，不允许空对象
/// Sql 语句
/// SqlParameters 对象,允许为空对象
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 data to models
public bool AddBookRating(BookRatings bookrating)
{
string sql =
“INSERT BookRatings (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 = Convert.ToInt32(SqlHelper.ExecuteScalar(this.connection, CommandType.Text, sql, para));
        return bookrating.Id > 0;
    }

GetBookRatings
//method：get specially data according to terms
///
/// 根据书的Id得到其评价信息
///
///
///
//get bookRatings list of specially book by id
public List GetBookRatings(int bookId)
{
string sql = “select * from bookratings where bookid=’” + bookId + “’”;
return this.GetBookRatings(sql);
}
//get list of bookRatings by sql
private List GetBookRatings(string safeSql)
{
List list = new List();
DataSet ds = SqlHelper.ExecuteDataset(this.connection, CommandType.Text, safeSql);
if (ds.Tables.Count > 0)
{
DataTable dt = ds.Tables[0];
//cast data to models by traversing dataRow
foreach (DataRow row in dt.Rows)
{
BookRatings brating = new BookRatings();
brating.Id = (int)row[“Id”];
brating.BookId = (int)row[“BookId”];
brating.Rating = (int)row[“Rating”];
int userId = (int)row[“userid”];
brating.User = new UserService().GetUserById(userId);
brating.Comment = (string)row[“Comment”];
brating.CreatedTime = (DateTime)row[“CreatedTime”];
list.Add(brating);
}
}
return list;

    }

GetCategoryById
//cast data to model
public Category GetCategoryById(Int32 id)
{
string sql = “SELECT * FROM Categories WHERE Id = @Id”;
Category category = null;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, sql, new SqlParameter(“@Id”, id)))
{
if (reader.Read())
{
category = new Category();

                category.Id = (int)reader["Id"];
                category.Name = (string)reader["Name"];
                category.PId = (int)reader["PId"];
            }
        }
        return category;
    }

BLL类代码：get and set models List
Get models list
public List GetCategories()
{
//调用DAL类方法返回实体类列表
return new CategoryService().GetCategories();
}
Set Models list
public void AddCategory(Category category)
{
//调用DAL类方法定义添加实体类
new CategoryService().AddCategory(category);
}
Get specially terms of models list by parameter
public List GetBookRatings(int bookId)
{
return new BooksRatingService().GetBookRatings(bookId);
}
 

Web
Common.master 通用视图 用于引用提高代码重用度
Common.master.cs 从数据库读取数据定义视图
Admin.master 管理者通用视图
Admin.master.cs 从数据库读取数据定义视图

region Method_name

代码显示缩进

endregion

注释：说明方法用途，参数，返回对象
///
/// 执行Sql 命令
///
/// 连接字符串
/// 命令类型
/// sql语句/参数化sql语句/存储过程名
/// 参数
/// DataSet 对象

App_code: Category.cs
public class Category
{
public int Id { get; set; }
public string Name { get; set; }
public int Pid { get; set; }
public int SortNum { get; set; }
}
Web.Config